master detail with grand totals (overall) on each detail report possible?
-
I am attempting to create a master report that uses separate subreports as detail because each PrGrp has a number of separate detail reports. If we call the top (overall across all offices) group phOrg and the second grouping PrGrp and the detail (within the subreport) PCP.
The master report has a header and one field (PrGrp) only. The rest of this report will have 27 subreports. Each subreport in based on a separate metric. For testing and development I'm only using two subreports in the master for speed and simplicity.
I am stumped by the requestor's need to have the grand total , across all PrGrps within the PhOrg to be displayed in each subreport under the PrGrp totals. In other words, the master should produce one PrGrp's 27 detail reports before it moves to the next PrGrp. I am essentially trying to enclose a higher level grouping (PhOrg) within a lowed level grouping (PrGrp).
The grandtotals just print as a duplicate row of the PrGrp totals. I have been trying to solve this for days. If I make a single report thatgroups by PhGrp, the PrGrp, with PCP, the grand total overall will print under the PrGrp. I accomplished this by SUM(metricname.Value) outside of any grouping and then above it I use ReportItems!TextBoxnn.Value to refer to the cell right below it. I'm including a screen print of a single report (no master-detail) that works (gives grand total across all practices but I had to trick it by using ReportItems! as described above and hiding the external values).
The needed report with master detail would have Practice(PrGrp) produce all the detail (provider/PCP) for that PrGrp for each metric (Asthma shown in example. (very doable) BUT I need the grand total across all PrGrps (The whole PhOrg) under each grid. In the example it's the last printed row that is 1,475 and 25.070, and 5.88%, ....,12.2%
Is this possible? Thank you for reading and any insight you can provide.
-
If all you want to do is divide your counts over a fixed population size, then it's easy. Create a variable and populate it with a COUNT from your population table. Then use it as the divisor in your percentage calculations.
-
pietlinden (6/25/2015)
If all you want to do is divide your counts over a fixed population size, then it's easy. Create a variable and populate it with a COUNT from your population table. Then use it as the divisor in your percentage calculations.Thank you very much for your reply Ten Centuries. The percentages, dividing stuff isn't done in the table. I do use the sum function. I have a problem of scope or something else perhaps. I am trying to sum at the level of PhOrg (the highest group that includes every value in our whole population for this metric). I have been unable to do it. I believe the crux of the problem is that I feel that I am doing something rather "artificial". I'm trying to add the grand total (that normally would appear once at the end of a report) to each PrGroup/metric tablix.
I have been asked to produce one long report that groups by PrGrp within each metric (condition) and paste a grandtotal (for the metric/condition across practices) to the bottom of each tablix (under the PrGroup totals.
-
Okay, got some nice fake consumable data? Doesn't have to be real, just has to be representative of your table structure(s) ... or the output of your stored procedure...
And please include what the report should look like.
Then someone can probably sort out what you're missing in your report.
Count across the entire dataset...
=COUNT(Fields!PersonID.Value, "Referrals_dset")
If you put that into a textbox on your report outside your table, you'll get a count for the entire dataset. if you need to get a count against all the records in an unfiltered dataset, you could create a totals query in a stored procedure and stuff the value into a variable.
-
pietlinden (6/25/2015)
Okay, got some nice fake consumable data? Doesn't have to be real, just has to be representative of your table structure(s) ... or the output of your stored procedure...And please include what the report should look like.
Then someone can probably sort out what you're missing in your report.
Count across the entire dataset...
=COUNT(Fields!PersonID.Value, "Referrals_dset")
If you put that into a textbox on your report outside your table, you'll get a count for the entire dataset. if you need to get a count against all the records in an unfiltered dataset, you could create a totals query in a stored procedure and stuff the value into a variable.
Thank you again pietlinden (sorry I called you "Ten Centuries" before. I didn't realize that that was your experience level), I appreciate your continued help! I have tried the =SUM(Fields!PersonID.Value, "datasetname") earlier today but I must be doing something incorrectly.
I have been able to get the grand totals in a separate stored procedure (separate from the existing sprocs) that sum across all the PrGrps. I can paste those into textboxes in a separate tablix just below the existing Tablix. It works but I wondered if this was a "kludge" that I shouldn't be doing. But if you say that this is an acceptable workaround, that's more than good enough for me. Is that what you mean by stuffing the value(s) in variable(s)?
Thanks!
-
Couple things: this...
=SUM(Fields!PersonID.Value, "datasetname")
doesn't really make sense. PersonID.Value is essentially a random meaningless number. why not COUNT?
The other thing you may need to look into is either LOOKUP or LOOKUPSET
-
I used your PersonID to jibe with your example in my reply. Of course I used my field name instead. I want to sum the totals. I don't understand why I would use count. I'm summing values. I get the right totals that way. If I counted I would only get a count of how many values. My example must not be clear. I tried hard to describe it but I realize it's not necessarily clear.
I have a solution using the totals returned by a separate stored procedure. It works. I just thought it was a kludge. I thought that was what you meant in your reply before this last one. I said if you thought it wasn't a kludge I would use it knowing that it was right because you have a high ("ten centuries") experience level.
Now that you are suggesting different functions and that I use count, I'm confused.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply