Subreport advice - multiple details with multiple records
I'm quite new to SSRS with SQL Server 2005. I need some advice on the right approach to the following problem. I need to create a report which displays many records, each of which may display multiple details. Each record has, in effect, a header record that I neeed to display only once, and below it, display certain fields from multiple rows. Here's an example: case_nbr attorney_name case_opened_date event_group event_type_description event_start_date event_end_date event_group event_type_description event_start_date event_end_date event_group event_type_description event_start_date event_end_date case_nbr attorney_name case_opened_date event_group event_type_description event_start_date event_end_date event_group event_type_description event_start_date event_end_date case_nbr attorney_name case_opened_date event_group event_type_description event_start_date event_end_date event_group event_type_description event_start_date event_end_date event_group event_type_description event_start_date event_end_date I am really not sure how to do this. At one point I had set up a design that I thought should work, but I suspected that the subreport query was being executed for each detail record. After a couple of minutes, I got an error saying that the system had run out of memory. At this point I am just looking for advice on the best way to go about a proper implementation. Do I need to pass a parameter from the main report to the subreport? I'm currently calling a stored procedure to develop the data for the main report and the subreport. Is there a better way to do this? Is a subreport even the best way to do it, or perhaps should I use a list and a subreport, or some other combination? I've only been using SSRS for a couple of weeks, as part of a project to move our reports from BusinessObjects to SSRS. I've created and deployed a few reports already but now I'm moving to a more complex requirement. Now I'm depending on the knowledge and experience of the SQL commnuity to point me in the right direction. I appreciate the help. Thank you!
May 20th, 2012 8:43am

Hi There You do not need a sub report in this scenario what you need is just to create a group report. Please do the following Run a report wizard to create a report Create your query which will bring your header records as well as all the details recordsChoose report type as tabular and click nextOn the next screen please put your field/column which you would like as header into the group sectionAnd all the fields which you would to appear under that group please put it inside detailsYou can add subtotal and drilldown in the next screen depending on your requirementYou are done I am putting screenshot for your help If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful
Free Windows Admin Tool Kit Click here and download it now
May 20th, 2012 9:32pm

Thank you very much, Syed. This really helped. The overall technique worked very well, but I could not use the Report Wizard because I'm using a stored procedure as the data source. I could not have typed in a SQL query as required by the Wizard. My group expression looks like this: = Felds!MyField1. value = Fields!MyField2.value = Fields!MyField3.value = Fields!MyField4.value The problem now is that if I try to add subtotals, the subtotals are at the detail level, not the group level. I tried adding another group that included only MyField1 and MyField2, but the subtotals still occur at the detail level. I would be very grateful if you could give me an idea of how to get around this.
May 21st, 2012 3:15pm

Hi There You do not need to add another group inside your report the easiest way is right click on your group and click on edit group and if you would like to add group total on the top just click on include group header or if you would like to add at the bottom just check include group footer. And in the group footer or header just above youre field which you would like to take sub total put expression like this Sum(Fields!onhand.Value) Where Fields!onhand.Value is the filed which you would like to take subtotal on the group level If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. Sum(Fields!onhand.Value)
Free Windows Admin Tool Kit Click here and download it now
May 21st, 2012 4:35pm

Thanks, Syed. This is what I am doing already. I have a group footer, in which I have the expression: = Count(Fields!MyField1.value) This subtotal, however, still gets the count of the details. This is not what I want. Maybe I can explain this a little better, since I might not have made this clear at the beginning. I want to show what looks like details and a header record, which I am now doing, but I need to show subtotals on the first two fields only. The grouping is on this: Fields!MyField1.value & Fields!MyField2.Value & Fields!MyField3.Value & Fields!MyField4.value I want to show subtotals on: Fields!MyField1.Value & Fields!MyField2.Value Fields!MyField1.Value In other words, I don't want to show subtotals at the detail level - I want to show subototals at the outer grouping levels. It should look something like this: Group field 1 Group field 2 Group field 3 Group field 4 Field 5 Field 6 Field 7 Detail field 1 Detail firld 2 Detail field 3 Detail field 4 Subtotal for (Group fields 1 & 2) (In the field 1 & 2 group footer) Subtotal for Group field 1 (In the ouytermost group's footer) It is the last part that I am having trouble with. No matter what I do, including adding separate groups based only on the first two group fields, the subtotal appears at the detail level. I hope that this makes sense and that you can understand it. I know that this is very involved, so I really appreciate your help with this. Thank you.
May 22nd, 2012 7:28am

Hi There If you can put a screenshot then I might be able to help you out because I am bit confused when you say group filed 1 group field 2... Have you defined 7 group or you just have one group with seven fields in it and you would like to get total based on group first field and second field. This could be achieve able but if I understand it well Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.
Free Windows Admin Tool Kit Click here and download it now
May 22nd, 2012 6:30pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics