Reporting Services - How to create additional details section

Hi All,

I'm using VS 2005 to build a SQL Server reporting Services report. The wizard works great and prompts me to build my results query and then select fields to group the report by.

I'm using the 3 groups as follows:

Page: Product / Model name
Group: Serial No
Details: Orders

Here's the problem. I also want to show my order lines as there is another linked table connected to my order table which holds all order line items. Classic order - line example.

How can I modify the details section to show my line items, as well as not show empty rows when no line items exist?

Thanks!

Hi.

I myself have only just started using Reporting Services, but from what I have read, I believe that if you switch to ReportDesigner in VS2005, you may be able to add a Sub Report to your Orders group. Then you could filter the data shown in the Sub Report by the ID value of the current Orders row.

Add a Report Parameter to your Orders group and set its value to the Order's ID (that links Orders and Order Lines). Add another Report Parameter to your Sub Report that get it's value from the Orders Report Parameter you just set up. Then set the Sub Report to get your Order Lines data and use a WHERE clause set to the Sub Report Report Parameter value so that the Sub Report displays just the Order Lines that match the current Orders value. The sub report will not show any data if no rows from the Order Lines table match the current Orders ID value.

The SQL Server 2005 BOL has info about using Reporting Services.

I am sure there may be other (better) ways of doing what you ask, but I hope this gives you an idea of what can be done.

HTH.

Best regards.

sql

Reporting off both a Stored Procedure and Table

Hi All,

I am using a Stored Procedure in the report. In the details section,
I have a formula for calculating End_date.This formula works fine and displays the data.But I want to check if this END_Date lies in the list of holidays which is given in another table 'tbHolidays'.
If it does lie in the list of Holidays then I need to add 1 to End Date.

Do I need to write a SQL expression for fetching the data from 'tbHolidays'? Can anybody out there please help me??

Thanks

Rashmias far as i know, if u use stored procedure u can't use SQL Expression.

r u using ref cursor in stored procedure?|||Thanks Raheem...
Yes I am using cursors in the SP. The cursor is used to fetch the records from join of two tables and then calculations are done for the fetched data. Then the data is inserted into a temporary table and a SELECT query is there at the end to fetch the rows.

Is there any other way to fetch the records from the table?Also I am not sure if a simple comparison of End Date with the data from tbHolidays would work...Helppppp!!!

Rashmi

Reporting from a DataWarehouse

Hi.
I'm creating reports from a datawarehouse, I hope that term doesn't
mean something different to every reader.
Anyway...I have a section of the report that gets information by
executing 63 separate sql statements that populate 63 cells in an Excel
spreadsheet.
The queries started out being separate queries, then I changed to
separate stored procedures.
The queries get their data from essentially 1 table with 2 other
suporting tables, such that the query has 2 joins. It also has 3-5
pieces in the where clause.
So I took out many of the where clause fields, put them in the select
section, and made a table out of the results.
Now I have an sp that runs 63 separate queries "inside" 1 sp and it
returns all the results I need. The queries get data from 1 table and
have far fewer parts to the where clause. Also I get to index to my
hearts content. So I'm getting a huge improvement in performance.
(Sorry I'm being so deliberate here.)
Finally! Here's my question.
Instead of making a table should I use a temporary table? Would I get
even more performance from a temp table? What advantages do temp tables
have?You will probably have better results if you can reduce the number of
queries. If you can get the 63 elements in fewer queries by combining the
common elements (JOIN , WHERE) you can probably get down to 10, 5, 2, or
maybe even 1 query. THAT is where I would expect you to see an improvement
in performance.
Keith
"Bob" <Go1369@.Yahoo.Com> wrote in message
news:1111502187.310046.5970@.g14g2000cwa.googlegroups.com...
> Hi.
> I'm creating reports from a datawarehouse, I hope that term doesn't
> mean something different to every reader.
> Anyway...I have a section of the report that gets information by
> executing 63 separate sql statements that populate 63 cells in an Excel
> spreadsheet.
> The queries started out being separate queries, then I changed to
> separate stored procedures.
> The queries get their data from essentially 1 table with 2 other
> suporting tables, such that the query has 2 joins. It also has 3-5
> pieces in the where clause.
> So I took out many of the where clause fields, put them in the select
> section, and made a table out of the results.
> Now I have an sp that runs 63 separate queries "inside" 1 sp and it
> returns all the results I need. The queries get data from 1 table and
> have far fewer parts to the where clause. Also I get to index to my
> hearts content. So I'm getting a huge improvement in performance.
> (Sorry I'm being so deliberate here.)
> Finally! Here's my question.
> Instead of making a table should I use a temporary table? Would I get
> even more performance from a temp table? What advantages do temp tables
> have?
>