April 11, 2012 at 6:55 am
Hi All
I've got a requirement that I'm sure will be simple but I'm struggling to decide how to go about it.
I'm generating a report that will show one product per page. A product consists of a single product header, a dataset of forecast sales and a separate dataset of actual sales. The forecast and actual datasets do not relate to each other except that they share the same product header parent. They have completely different structure and layouts. At present I've done this by asking the user for the product id and only allowing them to run the report for one product at a time. No problem then becaause the header will always be a single record and I just drop a couple of tablices (what the hell is the plural of tablix anyway?) onto the report, each tied to their own dataset. However, the user would like to be able to just run it for all products at once and have it produce the same output as if it were run for each product individually.
Nesting one of these datasets in the contract header would be easy. I'd write my select so that it returned, for example, a row for each actual sale and the contract header data would repeat for each actual sale row. I'd then group it by the header record, add a page break to the group and Bob's yer Mother's Brother. Basically a standard Master-Detail aproach.
Having a sibling detail dataset really mucks me up though. I can't see a way to query for all the relevant data as a single dataset that will actually be usable in the SSRS client. I could phrase the query so that I get a cartesian product of actual and forecast records but that's going to be server resource hungry as well as being difficult to collapse back into groups in the SSRS client
April 12, 2012 at 7:08 am
Solved it using a sub report. I knew the answer would be obvious. I think I'd just got it into my head that I wanted to use a single dataset and had difficulty letting go of the idea.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply