Reporting services, Stored Procedures, "Nested" tables

Question

Reporting services, Stored Procedures, "Nested" tables

Vikka

Hall of Fame

Points: 3715
More actions
July 26, 2007 at 3:22 pm

#177686

I was given a task to convert existing reports from classical ASP.NET into Reporting Services. One report actually contains data from three different tables:
ORDER_HEADER
order_id | customer_name | order_date
1           Vika                     07-26-2007
2           Peddie                  07-26-2007
---------------------------------------
ORDER_DETAIL
order_id | item_id |
1               1
1               2
1               3
2               4
--------------------------------------
ORDER_CHOICE
order_id | item_id | item_choice
1             1           1
1             1           2
1             1           3
2             4           1
--------------------------------------
So in ASP it was easy, one query loops thrue the first table and for each order_id gets all record from the second tableand for each item_id and order_id gets choices from the third table so report looks like that:
Order ID | Name | Date
1           Vika      07-26-2007
item_id:
1
choices 1,2,3
2
3
Order ID | Name | Date
2             Peddie 07-26-2007
item_id
4
choices 1
Then I tried to re-write it with Reporting Services because user wants to be able to export data into the Excel format. The only way to create such a nested report is make a report and two subreports one for each table. Then when I deploy it, it looks very ugly, too much space between report and subreports and Reporting Services do not export subreports into Excel, only main report.
Then I thought maybe I can build three stored procedures, build some temporary table in the above format and in the forth procedure I would select that table into Reporting Services?
But I am not familiar with stored procedures, how to loop thrugh data there like in the regular programming language?
For example in the first stored procedure I want to get order_ids from ORDER_HEADER table one by one. I find an order, I want to insert a header into a temporary table:
Order ID | Name | Date
1              Vika      07-26-2007
After that I want to pass order_id to the second procedure to find items for this order.
I find item_id, I would insert details.
item_id:
1
Then for order_id and each item I want to find all item choices and insert records below. But when I run a query, I cannot get order_id by order_id, I get whole dataset. In the regular programming lanquage I would put all order_ids into array and loop through it. How to do it in stored procedures?
Maybe somebody knows a good example of a report for such "nested" tables, or maybe my whole approach is wrong.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

steve smith-401573 SSChampion Points: 10889 More actions · Answer 1

In a nutshell, if you want to use a stored procedure, change your paradigm. Work it as set-based, without loops.

Regarding the final output - i'm not clear on what you want to have. The display, in my Mozilla browser, seemed a little muddied. But, where there are choices, you have a rank ordering, which means that you can "ORDER BY item_choice". So, a result set that can be (re)formatted into the reports the way you want them is all you need SQL Server to provide in the stored procedure.

sairam-257016 SSC Journeyman Points: 95 More actions · Answer 2

I am suggesting this as per my understanding of the problem

Write a stored procedure or a query inner joining all the three tables OrderHeader, OrderDetails and Orderchoices. Bind the stored procedure to Table layout in the report and then create groups on the OrderHeaderId, OrderDetailId one below the other. Then you will get a nice tree view kind of the report layout where you will be able to see a order header and all order details below that and all choices of that order at the third level liek this:

+Order1

+OrderDetails

OrderDetailChoices

Hope this helps.

Vikka Hall of Fame Points: 3715 More actions · Answer 3

I tried that and unfortunately it didn't work. OrderDetails and OrderDetailChoices tables do not have primary keys to group by. I tried in the reporting services, didn't work. I didn't design those tables. I guess there no solution for this problem. It's actually a question of the formatting nicely.

lconsiglio Old Hand Points: 353 More actions · Answer 4

You can get around this by creating a temp table or table variable in your stored procedure and loading it up with the raw data i.e. order_id, detail_id, item (one row for each item). Then you can order or group the data how ever you want when you output it. Then in the report you group on the order_id and detail_id. If you set the lower groups to toggle visible/invisible it provides a nice stepped effect when you drill down.