Question about Reports....

Hi all... I am doing Report in Business Intelligence Project. If I preview the report, why is it the data is shown only in 2 pages. It is actually 4 pages if I print it... what should I do to show the 4 pages report if I preview it? Please I need guidance....

thanks.

-Ron-

It is not possible to control the number of pages in any format, either html preview or pdf.

Shyam

|||

On the Layout tab of your report, under the Layout section on the Properties menu, try setting your InteractiveSize to 8.5in, 11in.

|||

triguns wrote:

Hi all... I am doing Report in Business Intelligence Project. If I preview the report, why is it the data is shown only in 2 pages. It is actually 4 pages if I print it... what should I do to show the 4 pages report if I preview it? Please I need guidance....

thanks.

-Ron-

I'm brand new to MS BI and have a similar problem but the even number pages don't have any detail on them.... just header/footer stuff. I've got interactive and page size both set to 8.5X11. Any thoughts?

Thanks!

Leif|||

Do you have any explicit page breaks set?

Another thing that can affect paging is having a control too close to the border of the page. Try leaving a quarter inch or eighth of an inch between the control and the margin.

|||

It is because the data from one page is getting spilled to second page. This happens if the report width in your designer + (left margin+right margin specified in Report Properties -> Layout tab) exceeds the page width that you specify in Report Properties -> Layout tab. For example, if your report is 8 inches wide in your designer and in report properties, you have specified page width=8.5 inch and left margin=1inch, right margin=1inch, data from your pages will spill to next pages because 8 inch (designer report width) + 1 inch (left margin) + inch (right amrgin) > 8.5 inch (page width).

Shyam

Question about reporting services

Is it possible to achieve the following:

First OLAP dataset with two report parameters, first one single- select parameters and second one is multi-select parameters. Second OLAP dataset I want to apply the first single-select parameter and the second multi-select parameters from the first OLAP dataset with an additional parameter from one of the column of the first OLAP dataset. If so, how to do it? e.g. the first OLAP dataset returns the id column with the selected first and second report parameters and I want to apply the same report parameters and the id column to filter out the second OLAP cube.

The first and second dataset are from different OLAP cubes.

You cannot apply parameters from one report to another, by the sounds of it your only solution here is to build your second report as a subreport and pass through the params.

Question about programming reporting services

Hi there,
We have ASP.NET application and we want to use SQL Server Reporting Services
to display the report links to the end user. Report Server however won't be
accessible from the internet for security reasons. The server side code in
ASP.NET pages on the IIS server can access this report server on intranet. I
was exploring the choices I have to build this functionality.
I think the obvious choice of using URL addressability of SQL RS won't work
because it would mean report server can be accessed from internet. For the
same reason I think ReportServer ASP.NET control that Microsoft ships as
samples also won't work because it uses IFRAME which in turn depends on URL
addressability. So it seems programming using Report Server web service is
the only choice. This means writing code for pagination, laying out report in
landscape/portrait manner as defined in RDL, ability to export the report
etc. Is there a better alternative that anyone can suggest?
Thanks
YogeshYou are understanding it correctly. One point though, are you aware that
Report Server and SQL Server can be on different boxes, so your SQL Server
can be behind the firewall and RS can be accessed from the internet, then
URL integration will work. You would need another license though. Otherwise
until V2 comes out with the web controls your only other option is web
services.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"yogeshprabhu" <yogeshprabhu@.discussions.microsoft.com> wrote in message
news:BE238509-4BBC-46E6-B4FC-6765C4A114F0@.microsoft.com...
> Hi there,
> We have ASP.NET application and we want to use SQL Server Reporting
Services
> to display the report links to the end user. Report Server however won't
be
> accessible from the internet for security reasons. The server side code in
> ASP.NET pages on the IIS server can access this report server on intranet.
I
> was exploring the choices I have to build this functionality.
> I think the obvious choice of using URL addressability of SQL RS won't
work
> because it would mean report server can be accessed from internet. For the
> same reason I think ReportServer ASP.NET control that Microsoft ships as
> samples also won't work because it uses IFRAME which in turn depends on
URL
> addressability. So it seems programming using Report Server web service is
> the only choice. This means writing code for pagination, laying out report
in
> landscape/portrait manner as defined in RDL, ability to export the report
> etc. Is there a better alternative that anyone can suggest?
> Thanks
> Yogesh|||Thanks Bruce. I was aware that Report Server and SQL Server can be on
different boxes, but our organization has decided that they don't want report
server available over internet. Do you know of any books or articles that
explain handling pagination or ability to export the report using web
services?
Yogesh
"Bruce L-C [MVP]" wrote:
> You are understanding it correctly. One point though, are you aware that
> Report Server and SQL Server can be on different boxes, so your SQL Server
> can be behind the firewall and RS can be accessed from the internet, then
> URL integration will work. You would need another license though. Otherwise
> until V2 comes out with the web controls your only other option is web
> services.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>|||I have a couple at work and on Monday will give you the titles to them.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"yogeshprabhu" <yogeshprabhu@.discussions.microsoft.com> wrote in message
news:7246EF1E-0174-4CFA-A7AC-478ADF0AAEEC@.microsoft.com...
> Thanks Bruce. I was aware that Report Server and SQL Server can be on
> different boxes, but our organization has decided that they don't want
> report
> server available over internet. Do you know of any books or articles that
> explain handling pagination or ability to export the report using web
> services?
> Yogesh
>
> "Bruce L-C [MVP]" wrote:
>> You are understanding it correctly. One point though, are you aware that
>> Report Server and SQL Server can be on different boxes, so your SQL
>> Server
>> can be behind the firewall and RS can be accessed from the internet, then
>> URL integration will work. You would need another license though.
>> Otherwise
>> until V2 comes out with the web controls your only other option is web
>> services.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>|||Besides web services you will also need to be implementing your own
authentication too. Two books I use (both have some on Web services, enough
to get you going but are also good for other topics as well) are: Microsoft
Reporting Services in Action by Teo Lachev and Hitchhiker's Guide to SQL
Server 2000 Reporting Services by Peter Blackburn and William Vaughn.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"yogeshprabhu" <yogeshprabhu@.discussions.microsoft.com> wrote in message
news:7246EF1E-0174-4CFA-A7AC-478ADF0AAEEC@.microsoft.com...
> Thanks Bruce. I was aware that Report Server and SQL Server can be on
> different boxes, but our organization has decided that they don't want
report
> server available over internet. Do you know of any books or articles that
> explain handling pagination or ability to export the report using web
> services?
> Yogesh
>
> "Bruce L-C [MVP]" wrote:
> > You are understanding it correctly. One point though, are you aware that
> > Report Server and SQL Server can be on different boxes, so your SQL
Server
> > can be behind the firewall and RS can be accessed from the internet,
then
> > URL integration will work. You would need another license though.
Otherwise
> > until V2 comes out with the web controls your only other option is web
> > services.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
>

Question about Parameter

Hi,
I need to design a Sales Order (SO) report for my company. In the SO
report, I need to print out the company name, company address, Telephone no.
... etc in report header. For this design, it serve 5 sub-company under my
company group. Therefore, I store the company data in a table - name
CompanyInfo. Everytime I need to select correct company name to print it out.
But I know that it cannot print "Field" in report header in RS. I try to
put it into parameter and print it out. It OK but all of the parameter will
be show to user. For this point, I don't want our user to see such
information.
Any idea for me?
Thank You!
TonyYou can have multiple datasets in a report. If you put a list at the top of
the report and your query has only one record, then the data will look for
all intents and purposes as the report header. Then following that have your
table or matrix or whatever. I do this all the time and it looks exactly the
same.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
> Hi,
> I need to design a Sales Order (SO) report for my company. In the SO
> report, I need to print out the company name, company address, Telephone
> no.
> ... etc in report header. For this design, it serve 5 sub-company under
> my
> company group. Therefore, I store the company data in a table - name
> CompanyInfo. Everytime I need to select correct company name to print it
> out.
> But I know that it cannot print "Field" in report header in RS. I try to
> put it into parameter and print it out. It OK but all of the parameter
> will
> be show to user. For this point, I don't want our user to see such
> information.
> Any idea for me?
> Thank You!
> Tony
>|||Hi Bruce,
Thank you for your reply!
I already try this way and it look OK. But all parameters will be shown at
the top of the report! It looks no good! Can I hidden the parameter? It is
because there are 6 parameters for such report header.
Thank You!
Tony
"Bruce L-C [MVP]" wrote:
> You can have multiple datasets in a report. If you put a list at the top of
> the report and your query has only one record, then the data will look for
> all intents and purposes as the report header. Then following that have your
> table or matrix or whatever. I do this all the time and it looks exactly the
> same.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
> > Hi,
> >
> > I need to design a Sales Order (SO) report for my company. In the SO
> > report, I need to print out the company name, company address, Telephone
> > no.
> > ... etc in report header. For this design, it serve 5 sub-company under
> > my
> > company group. Therefore, I store the company data in a table - name
> > CompanyInfo. Everytime I need to select correct company name to print it
> > out.
> >
> > But I know that it cannot print "Field" in report header in RS. I try to
> > put it into parameter and print it out. It OK but all of the parameter
> > will
> > be show to user. For this point, I don't want our user to see such
> > information.
> >
> > Any idea for me?
> > Thank You!
> >
> > Tony
> >
> >
>
>|||You can have hidden parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:7FABFC55-8149-4484-9C80-AD70FC318BCF@.microsoft.com...
> Hi Bruce,
> Thank you for your reply!
> I already try this way and it look OK. But all parameters will be shown
> at
> the top of the report! It looks no good! Can I hidden the parameter? It
> is
> because there are 6 parameters for such report header.
> Thank You!
> Tony
>
> "Bruce L-C [MVP]" wrote:
>> You can have multiple datasets in a report. If you put a list at the top
>> of
>> the report and your query has only one record, then the data will look
>> for
>> all intents and purposes as the report header. Then following that have
>> your
>> table or matrix or whatever. I do this all the time and it looks exactly
>> the
>> same.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
>> > Hi,
>> >
>> > I need to design a Sales Order (SO) report for my company. In the SO
>> > report, I need to print out the company name, company address,
>> > Telephone
>> > no.
>> > ... etc in report header. For this design, it serve 5 sub-company
>> > under
>> > my
>> > company group. Therefore, I store the company data in a table - name
>> > CompanyInfo. Everytime I need to select correct company name to print
>> > it
>> > out.
>> >
>> > But I know that it cannot print "Field" in report header in RS. I try
>> > to
>> > put it into parameter and print it out. It OK but all of the parameter
>> > will
>> > be show to user. For this point, I don't want our user to see such
>> > information.
>> >
>> > Any idea for me?
>> > Thank You!
>> >
>> > Tony
>> >
>> >
>>|||Thank You! I try it!
"Bruce L-C [MVP]" wrote:
> You can have hidden parameters.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:7FABFC55-8149-4484-9C80-AD70FC318BCF@.microsoft.com...
> > Hi Bruce,
> >
> > Thank you for your reply!
> >
> > I already try this way and it look OK. But all parameters will be shown
> > at
> > the top of the report! It looks no good! Can I hidden the parameter? It
> > is
> > because there are 6 parameters for such report header.
> >
> > Thank You!
> > Tony
> >
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You can have multiple datasets in a report. If you put a list at the top
> >> of
> >> the report and your query has only one record, then the data will look
> >> for
> >> all intents and purposes as the report header. Then following that have
> >> your
> >> table or matrix or whatever. I do this all the time and it looks exactly
> >> the
> >> same.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> >> news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
> >> > Hi,
> >> >
> >> > I need to design a Sales Order (SO) report for my company. In the SO
> >> > report, I need to print out the company name, company address,
> >> > Telephone
> >> > no.
> >> > ... etc in report header. For this design, it serve 5 sub-company
> >> > under
> >> > my
> >> > company group. Therefore, I store the company data in a table - name
> >> > CompanyInfo. Everytime I need to select correct company name to print
> >> > it
> >> > out.
> >> >
> >> > But I know that it cannot print "Field" in report header in RS. I try
> >> > to
> >> > put it into parameter and print it out. It OK but all of the parameter
> >> > will
> >> > be show to user. For this point, I don't want our user to see such
> >> > information.
> >> >
> >> > Any idea for me?
> >> > Thank You!
> >> >
> >> > Tony
> >> >
> >> >
> >>
> >>
> >>
>
>

Question about paging?

Good Day!

i have a problem regarding on paging.

in my Report i want to view the record 1 record per page.

Example:

I have a textboxes where i view my data the

- Firstname

- organization

-Department

I want it to view it 1 page per records.

How to do that? Sorry coz im a new bie in the reporting services

can anybody help me?

I got the already the paging per records but the problem is there is a blank page in next page. Example page 1 has a records then page 2 is a blank page then page 3 has a record?

Why is that? what is the cause?

Canany body help me? Your help is greatly appreciated.

|||

Hey,

Is it ok if you view like

First Name Organization Department

Then use table, to see the records and create a group and give your field like first name , department. and check page break at the end so it gives you in 1 record per page.

Any doubts let me know.

Amarnath

|||Make sure that there are no report items after the group that you have set to break.

Question about Null value in cross tab

Dear all,
I have a question about formula in crystal report.
I have created a cross tab. In the group field, the null value will display blank.
How can it display other text if it is null instaed of blank?
Thanks alotHi,

Right Click the summary field, select "Format Field", go to "Common" Tab.

You can find X-2 for "Display" String option. Click that button and write the following formula

If IsNull(CurrentFieldValue) = True then
0.00
else
CurrentFieldValue

This will display 0 if that field contains Null and the actual value if it is not null.

Hope this will work.|||Hi
Thank you for your reply
I have modify the formula from you and place it in the right place.
However, it doesn't work.
The blank group title has not be changed to the text that I expected.
Do you have any suggestion?|||Is that field is blank or null? If it is blank, just check with blank space instead of checking null.|||I have checked both of it
but still cannot work
Here is my code
for the null value:
if IsNull({table.field}) then
"Unspecific"
else
CurrentFieldValue

for checking space
if {table.field}=" " then
"Unspecific"
else
CurrentFieldValue

both of them are shown nothing is the cross tab field
Do u have any other suggestion?
Thanks for your help!!!!|||I have an other question to ask you
I want to change the subtotal to other currency
and the exchange rate is store in other table
I don't know how to get the exchange rate for each of the countries
How can i do this by the formula?|||you are so nice and very helpful~~~|||Thanks,

For the first question, Hope you have written the formula in "Display String" (Format)section.

Try to check CurrentFieldValue instead of {table.field}.

I checked with zero, it worked.

For the second question, if you have any linking field like country code, country name use that field to link with the other table which contains the exchange rate. Write the formula by using that field.|||Thank you for reply!
I also check it with non-null value
it also works!
however, when I check it with null value, it shows nothing~
I have check it with sql statement, it is null value in the database

How come this happens??

Thanks~|||I have a new question again.

The crystal report have not show the record that is zero
How can I show the zero record??

Thanks very muchsql

Question about if condition in ReportBuilder

I have a question about table layout report. I want to do that:

I have a table report and on that report i have a Total field of money. And i want to do that if condition on that field, so if the prize is less then 150, the color of the text it should be red otherwise it should be other color. So how can i achieve that. I know that this is possible in Reporting services in Report Designer is like =IIF(field.?.Value<150;"red";"gree")

But if i make that same in report builder, the what it does, it displays not the value in color, but it displays the text "red" od "green".

I will be very happy and gratefull if somebody can help me.

thank you in advance.

Hello,

You need to place your expression in the color property, not the textbox.

=Iif(Fields!?.Value < 150, "Red", "Green")

Jarret

|||

Hello,

yes this works in Report designer, when you work on reports RDL, but what if you want to do that in Report Builder?

I know that this work in Color property in report designer. But when you want to make that in Report Builder (the client app od RS), then it just get the word out, it doesn't set the color.

Andrej

|||=IIF(FEILD.TEXTBOX.VALUE < 150,"RED","BLUE"

I AM YOGESH FROM HYD IF THIS DOES'NT WORKS JUST CALL ME AT 09391323274 I AM FROM HYDERABAD INDIA

Question about Hyperlink

Hi!

We have over 500 reports working with report server. In most of those reports we want to use the Hyperlink option to link those reports to some pages of our web application. So i've added javascript code to do a window.open. My problem is that i have to put all the URL for example www.myApp.com/page1.aspx. Our application will be install to different server (for different client) with different name so the adresse www.myApp.com will change with the server. In our application we use the webconfig file so when the server change all our page still working because there are using the connection in the webconfig. Is it possible to do something like that for the reports ? I know that i could create a dll then add a reference in our report. I,ve tried to do it using this http://msdn2.microsoft.com/en-us/library/ms155034.aspx but it never really work and I found this solution a bit complicated. What i mean is when we have a new client and that we want to install the application on his server we want to have a solution that will be quick and easy. We don't want to recreate the a new dll then change all config file to allow this dll.

Is there a simple way to change the reference of hyperlink in all our report. ? Right now the best solution i've think is to make a script that will read the xml file of the report and change all the name for the new one.

Thanks and sorry about my English !

There is value in the global collection that should give you what you are looking for. Try using the following expression in your hyperlink field.

=Globals!ReportServerUrl & "page1.aspx"

|||Thanks !! This is exactly what i was looking for !!!

Question about How Data can be rendered on Report

I have a dataset that is comprised of several records of employees leave
types and hours. Each leave type record is unique(sicks on record and
vacation another). 2 fields of each record,I group on (Employee and
TimePeriod) and the Leave type hours I want to display in separate columns.
What I am trying to do is group by Employee with a page break after each
employee(this was easy), then group by period(same page, this was done too),
but I also need to put the hours in different columns depending on the Leave
type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO IN
THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW ROW
CREATED.
i.e)
NAME Time Sick Vacation
Employee Name
1/1/2005-1/8/2005 8 8
1/8/2005-1/15/2005 4 0
BUT INSTEAD I AM GETTING THIS
NAME Time Sick Vacation
Employee Name
1/1/2005-1/8/2005 8 0
1/1/2005-1/8/2005 0 8
1/8/2005-1/15/2005 4 0
1/8/2005-1/15/2005 0 4
I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?Create the two text boxes for Sick and Vacation... For the values use an
expression like
for Sick
=Iif(Fields!LeaveType.Value="Sick",Hours,0)
for Leave textbox
=Iif(Fields!LeaveTypeValue="Vacation",Hours,0)
Hope this helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate
columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done
too),
> but I also need to put the hours in different columns depending on the
Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>|||I used the Iif statement you wrote. It works fine, but what I don't want it
to do is to put the next record in the dataset onto the next row. I want the
next record to go into the next column as in previous post example. I only
want the next dataset record to go into the next row if the group changes.
See the example for clarification.
"Wayne Snyder" wrote:
> Create the two text boxes for Sick and Vacation... For the values use an
> expression like
> for Sick
> =Iif(Fields!LeaveType.Value="Sick",Hours,0)
> for Leave textbox
> =Iif(Fields!LeaveTypeValue="Vacation",Hours,0)
> Hope this helps...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> > I have a dataset that is comprised of several records of employees leave
> > types and hours. Each leave type record is unique(sicks on record and
> > vacation another). 2 fields of each record,I group on (Employee and
> > TimePeriod) and the Leave type hours I want to display in separate
> columns.
> > What I am trying to do is group by Employee with a page break after each
> > employee(this was easy), then group by period(same page, this was done
> too),
> > but I also need to put the hours in different columns depending on the
> Leave
> > type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
> IN
> > THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
> ROW
> > CREATED.
> > i.e)
> > NAME Time Sick Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 8
> > 1/8/2005-1/15/2005 4 0
> >
> >
> > BUT INSTEAD I AM GETTING THIS
> > NAME Time Sick Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 0
> > 1/1/2005-1/8/2005 0 8
> > 1/8/2005-1/15/2005 4 0
> > 1/8/2005-1/15/2005 0 4
> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
> >
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01AB_01C4F4BE.37F23400
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Create a grouping on Employee and DateRange and drop the Employee name, =date range (week, whatever it's called), and the Sum() for Vacation and =Sick in the Group Footer. Make sure you use the group name as the scope =for the Sum (IOW, =3DSum(Fields!SickTime.Value, "GroupName") where ="GroupName" is the name fo the group).
You won't have any values dropping into the detail rows and you can =place the Employee Name in the group header row if you don't want it to =repeat for each row. Make sure you also sort the data set by Employee =and Time.
HTH.
-- TIM ELLISON
"dillig" <dillig@.discussions.microsoft.com> wrote in message =news:1C382976-466F-4AF5-9688-CCCAEB1460AB@.microsoft.com...
I used the Iif statement you wrote. It works fine, but what I don't =want it to do is to put the next record in the dataset onto the next row. I =want the next record to go into the next column as in previous post example. I =only want the next dataset record to go into the next row if the group =changes. See the example for clarification.
"Wayne Snyder" wrote:
> Create the two text boxes for Sick and Vacation... For the values =use an
> expression like
> for Sick
> =3DIif(Fields!LeaveType.Value=3D"Sick",Hours,0)
> for Leave textbox
> =3DIif(Fields!LeaveTypeValue=3D"Vacation",Hours,0)
> > Hope this helps...
> > -- > Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> > I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> > "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> > I have a dataset that is comprised of several records of employees =leave
> > types and hours. Each leave type record is unique(sicks on record =and
> > vacation another). 2 fields of each record,I group on (Employee =and
> > TimePeriod) and the Leave type hours I want to display in separate
> columns.
> > What I am trying to do is group by Employee with a page break =after each
> > employee(this was easy), then group by period(same page, this was =done
> too),
> > but I also need to put the hours in different columns depending on =the
> Leave
> > type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT =RECORD TO GO
> IN
> > THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I =WANT A NEW
> ROW
> > CREATED.
> > i.e)
> > NAME Time Sick =Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 8
> > 1/8/2005-1/15/2005 4 0
> >
> >
> > BUT INSTEAD I AM GETTING THIS
> > NAME Time Sick =Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 0
> > 1/1/2005-1/8/2005 0 8
> > 1/8/2005-1/15/2005 4 0
> > 1/8/2005-1/15/2005 0 4
> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE =HELP'?
> >
> > >
--=_NextPart_000_01AB_01C4F4BE.37F23400
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Create a grouping on Employee and =DateRange and drop the Employee name, date range (week, whatever it's called), and the =Sum() for Vacation and Sick in the Group Footer. Make sure you use the =group name as the scope for the Sum (IOW, ==3DSum(Fields!SickTime.Value, "GroupName") where "GroupName" is the name fo the =group).
You won't have any values dropping into =the detail rows and you can place the Employee Name in the group header row if you =don't want it to repeat for each row. Make sure you also sort the data set by =Employee and Time.
HTH.
-- TIM ELLISON
"dillig" wrote in message news:1C3=82976-466F-4AF5-9688-CCCAEB1460AB@.microsoft.com...I used the Iif statement you wrote. It works fine, but what I don't want =it to do is to put the next record in the dataset onto the next row. =I want the next record to go into the next column as in previous post =example. I only want the next dataset record to go into the next row if the =group changes. See the example for clarification."Wayne Snyder" wrote:> Create the two text boxes for Sick and Vacation... =For the values use an> expression like> for Sick> =3DIif(Fields!LeaveType.Value=3D"Sick",Hours,0)> for Leave =textbox> =3DIif(Fields!LeaveTypeValue=3D"Vacation",Hours,0)> > =Hope this helps...> > -- > Wayne Snyder, MCDBA, SQL Server MVP> Mariner, Charlotte, NC>">http://www.mariner-usa.com">www.mariner-usa.com> =(Please respond only to the newsgroups.)> > I support the =Professional Association of SQL Server (PASS) and it's> community of SQL =Server professionals.>">http://www.sqlpass.org">www.sqlpass.org> > ="dillig" wrote in message> news:889=F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...> > I have a dataset that is comprised of several records of =employees leave> > types and hours. Each leave type record is =unique(sicks on record and> > vacation another). 2 fields of each record,I =group on (Employee and> > TimePeriod) and the Leave type hours I want =to display in separate> columns.> > What I am trying to =do is group by Employee with a page break after each> > =employee(this was easy), then group by period(same page, this was done> =too),> > but I also need to put the hours in different columns depending =on the> Leave> > type. BUT I DONT WANT IT TO CREATE A =NEW ROW. I WANT THE NEXT RECORD TO GO> IN> > THE NEXT COLUMN =UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW> ROW> => CREATED.> > i.e)> = > =NAME &nb=sp; =Time &nb=sp; Sick Vacation> > Employee Name> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =8 = 8> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =4 = 0> >> >> > BUT INSTEAD I AM GETTING =THIS> > =NAME &nb=sp; =Time &nb=sp; Sick Vacation> > Employee Name> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =8 = 0> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =0 = 8> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =4 = 0> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =0 = 4> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. =CAN ANYONE HELP'?> >> > =>

---

--=_NextPart_000_01AB_01C4F4BE.37F23400--|||Have you tried using a Matrix instead of a table? Put time in the rows and
leave type in the columns.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
>I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate
> columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done
> too),
> but I also need to put the hours in different columns depending on the
> Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
> IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
> ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>|||Thanks to all who replied to this post. Creating a second grouping from
timeperiod and putting the totals for the inside group in group2's footer
worked great.
Your guys are awesome.
"dillig" wrote:
> I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done too),
> but I also need to put the hours in different columns depending on the Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>

Question about getting data from multiple tables

I have a report that receives a Soc. Sec. No. parameter. The first table I
need data from is keyed on SSN. This table also has Id. column.
I need to use the value in the ID column to retreive data from a different
data table for another part of the report.
From a design point of view how should I approach this.
--
DonThis will most likely work, depending on the nature of your report:
Simply join your tables in your source query (data tab). You can group your
layout based on data in the first table. Or you can base different parts of
the report on different source queries, but make both queries filtered on
the same data.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A2E283F5-EFDA-487A-B03E-D5469582871A@.microsoft.com...
>I have a report that receives a Soc. Sec. No. parameter. The first table I
> need data from is keyed on SSN. This table also has Id. column.
> I need to use the value in the ID column to retreive data from a different
> data table for another part of the report.
> From a design point of view how should I approach this.
> --
> Don

Question about Exporting to Excel and Caching

If you export a report to Excel and then the data changes and you export it
again you will bring up your last export and it will not reflect the new
data. Is their away to turn this caching off?It seems like it is downloading a cached copy from the server, I think. Any
sugegstions or ideas?
"Cooper" wrote:
> If you export a report to Excel and then the data changes and you export it
> again you will bring up your last export and it will not reflect the new
> data. Is their away to turn this caching off?

Question about exporting to Excel

Hi,

I have a report with a table that has a few columns that have visibility property set to hidden and other visible columns. I would like to export the report to Excel but I need to be able to export the hidden columns as well and unhide that from Excel file generated if needed. Is this possible to do or only the visible columns can be exported?

Thank you.

Yes, toggle state is preserved and exported as Excel outlines. So you should be able to export the hidden columns and unhide them through Excel outlines. Note there are some Excel limitations on outlines, e.g. the maximum number of levels for nexted outlines is 7, and if the toggle item is not in the previous or next row/column of the toggled item, outline is disabled.|||

Hi I am having trouble with the toggle item functionality being exported to excel. Could you please expand on the point you made about the toggle item functionality being disabled "if the toggle item is not in the previous or next row/column of the toggled item" or even better provide a pointer to some documentation on this.

Thanks in advance Tony

|||

Thanks - i have figured out what i was doing wrong - the problem seems to be that i had rows that were not part of the toggled group. rows like a detail row and group footer rows ocne i removed these (they were initially there but i just made them not visible) the toggle functionality worked perfectly

cheers Tony

Question about exporting to Excel

Hi,

I have a report with a table that has a few columns that have visibility property set to hidden and other visible columns. I would like to export the report to Excel but I need to be able to export the hidden columns as well and unhide that from Excel file generated if needed. Is this possible to do or only the visible columns can be exported?

Thank you.

Yes, toggle state is preserved and exported as Excel outlines. So you should be able to export the hidden columns and unhide them through Excel outlines. Note there are some Excel limitations on outlines, e.g. the maximum number of levels for nexted outlines is 7, and if the toggle item is not in the previous or next row/column of the toggled item, outline is disabled.|||

Hi I am having trouble with the toggle item functionality being exported to excel. Could you please expand on the point you made about the toggle item functionality being disabled "if the toggle item is not in the previous or next row/column of the toggled item" or even better provide a pointer to some documentation on this.

Thanks in advance Tony

|||

Thanks - i have figured out what i was doing wrong - the problem seems to be that i had rows that were not part of the toggled group. rows like a detail row and group footer rows ocne i removed these (they were initially there but i just made them not visible) the toggle functionality worked perfectly

cheers Tony

Question about dynamic report

Hi:

I have a question of create a dynamic the report.

For example: At the beginning I have 3 columns in the report, build by VS. After couple weeks, the same report needs to have more columns. And i don't want to use the VS to modify the layout manually, instead, when adding new columns, I can call a function to modify the rdl file add attributes into the xml file automatically. But after that which way I can go to publish the modified report into the report server, then when user clicks on that report again, there will be more columns show up.

thanks

LuZ,

You can use the Reporting Services web service to publish the new report. See this link for documentation:

http://msdn2.microsoft.com/en-us/library/ms154697.aspx

-Ryan

Question about dynamic report

Hi:

I have a question of create a dynamic the report.

For example: At the beginning I have 3 columns in the report, build by VS. After couple weeks, the same report needs to have more columns. And i don't want to use the VS to modify the layout manually, instead, when adding new columns, I can call a function to modify the rdl file add attributes into the xml file automatically. But after that which way I can go to publish the modified report into the report server, then when user clicks on that report again, there will be more columns show up.

thanks

LuZ,

You can use the Reporting Services web service to publish the new report. See this link for documentation:

http://msdn2.microsoft.com/en-us/library/ms154697.aspx

-Ryan

Question about architecting applications with Report Designer

I'm very new to Report Designer and .Net and someone told me it was best if I just used Stored Procedures to get data for the reports. In my previous work experience, we always avoid tying the presentation layer directly to the Data Access layer. My thought is that it would have been a far better architecture to have the Report Designer pass a request to a Service class which would then invoke the appropriate business object. The C# business object would then access one or many DAOs (Data access objects) to bundle the data from various tables into a value object. The Report Designer could then get it's data from the value object. Our data model is very complex and the Stored proc solution is using far too many intermediate temp tables to hold intermediate results. So, what is the best approach to use when data on a single report page can come from as many as 12 different tables? The report layout requirements are complex too.

Have you tried creating a Report Model?

|||

I'm in the same situation. Did report model help you? Please let me know what decision you have made?

I'm thinking of Custom Data Extensions that retreive entity from BLL and attaches it to the report for display. Did you look at that option of accessing custom .NET code (which will be BLL in our case) from within the reports using assembly references and embedded code. The custom .NET code will return a list or array for display within the reports. I have not tried this and not sure whether this will work.

|||

Hi,

I never had a chance to create the Report Model because of lots of pre-production stuff to do. The project is ending in 2 days now and then I'm off to another client. For the benefit of everyone else, if anyone has ideas on flexible architecture for reports, please post.

|||

Perfect example for using web services.
Create a web service that would expose methods to query data from your complex data storage. Internally your webservice will use your business objects for data access then serialize results into xml and return to the caller.
Then you can use Xml Data Provider in your reports to call SOAP APIs and convert returned xml back into rows and columns. Xml DP will let you pass parameters into soap calls, so it's fully flexible. You can search msdn articles and this forum for more info on XmlDP.

|||

Thanks for your response.

I'm planning on using the Custom Data Extensions. The custom DataReader implementation will instantiate a domain class within the business layer and the class will have method which will return the relevant business entity list or some collection of objects. The collection of business entities will be consumed by the DataReader and iterated within the 'Read' method to display the contents within the report. This way i can leverage my business layer and produce the reports.

Do you see any issues with this approach? Please let me know

|||looks good to me

Question about architecting applications with Report Designer

I'm very new to Report Designer and .Net and someone told me it was best if I just used Stored Procedures to get data for the reports. In my previous work experience, we always avoid tying the presentation layer directly to the Data Access layer. My thought is that it would have been a far better architecture to have the Report Designer pass a request to a Service class which would then invoke the appropriate business object. The C# business object would then access one or many DAOs (Data access objects) to bundle the data from various tables into a value object. The Report Designer could then get it's data from the value object. Our data model is very complex and the Stored proc solution is using far too many intermediate temp tables to hold intermediate results. So, what is the best approach to use when data on a single report page can come from as many as 12 different tables? The report layout requirements are complex too.

Have you tried creating a Report Model?

|||

I'm in the same situation. Did report model help you? Please let me know what decision you have made?

I'm thinking of Custom Data Extensions that retreive entity from BLL and attaches it to the report for display. Did you look at that option of accessing custom .NET code (which will be BLL in our case) from within the reports using assembly references and embedded code. The custom .NET code will return a list or array for display within the reports. I have not tried this and not sure whether this will work.

|||

Hi,

I never had a chance to create the Report Model because of lots of pre-production stuff to do. The project is ending in 2 days now and then I'm off to another client. For the benefit of everyone else, if anyone has ideas on flexible architecture for reports, please post.

|||

Perfect example for using web services.
Create a web service that would expose methods to query data from your complex data storage. Internally your webservice will use your business objects for data access then serialize results into xml and return to the caller.
Then you can use Xml Data Provider in your reports to call SOAP APIs and convert returned xml back into rows and columns. Xml DP will let you pass parameters into soap calls, so it's fully flexible. You can search msdn articles and this forum for more info on XmlDP.

|||

Thanks for your response.

I'm planning on using the Custom Data Extensions. The custom DataReader implementation will instantiate a domain class within the business layer and the class will have method which will return the relevant business entity list or some collection of objects. The collection of business entities will be consumed by the DataReader and iterated within the 'Read' method to display the contents within the report. This way i can leverage my business layer and produce the reports.

Do you see any issues with this approach? Please let me know

|||looks good to me

Question about a SQL Reporting Tutorial Example

In the BOL "Walkthrough - Using a Dynamic Query in a Report" it says:
In Data view, select the Employees dataset, and then use the generic
query designer to replace the original query with the following
expression:
="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
Parameters!Department.Value & ")") & " ORDER BY LastName"
This expression
If I put that in the generic query designer it will not validate the
SQL.
Can anyone help with this?Expression-based queries cannot be validated and executed in the generic
query designer, because it does not evaluate the VB.NET expressions. They
are only evaluated at report runtime.
The general recommendation for dynamic queries using VB.NET expressions is
to start with a static query, then add a textbox to the report with the
dynamic query expression you want to use. Run the report to verify that the
expression-based commandtext evaluates to valid SQL, and then in the last
step replace the static query with the dynamic expression-based query. In
addition, the fields list is only auto-detected from non expression-based
queries.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <cmay@.walshgroup.com> wrote in message
news:44f6e099.0409051123.191910b6@.posting.google.com...
> In the BOL "Walkthrough - Using a Dynamic Query in a Report" it says:
> In Data view, select the Employees dataset, and then use the generic
> query designer to replace the original query with the following
> expression:
> ="SELECT FirstName, LastName, Title FROM Employee" &
> IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
> Parameters!Department.Value & ")") & " ORDER BY LastName"
> This expression
> If I put that in the generic query designer it will not validate the
> SQL.
> Can anyone help with this?