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