CODEWITHSUNDEEP
reporting-servicesssrs-2008reporting
Irish Yobbo
Irish Yobbo

Reputation: 433

Reporting Services - List of lists

I have a report which displays the area, a list of diagnoses for the area, how many diagnoses for each diagnosis, and a percentage. This works fine, however I want to choose and display the data for many areas.

Currently, I have one choice for the area, and it shows all the diagnoses for the chosen area in a table. Essentially, I want to make a list of this tables, so it can be repeated for as many areas as required.

I think that this can be performed by putting the table in a listbox, and setting the tablix to filter for each area, but if I try this, I get:

Error 1 [rsInvalidDetailDataGrouping] The tablix ‘Tablix1’ has a detail member
with inner members. Detail members can only contain static inner members.

It's hard to explain exactly what I need. At the moment, all the diagnoses are displayed, but they are all listed as being from the same area. I need a list of diagnoses from area 1, then a list from area 2, then area 3 etc. The percentages need to be the percentage from that area, currently they are the percentage of all areas.

What is the best way to do this?

Upvotes: 0

Views: 1714

Answers (2)

Chris Latta
Chris Latta

Reputation: 20560

Don't put the table in a list - just use grouping on the table.

Let's say you now want a multiple vaue parameter called @Areas to choose the areas and then print them all out, say separated by a page break. I assume you have something like a table for Diagnoses and a table DiagnosisItems for the items performed in a diagnosis. So your SQL looks something like this:

SELECT Diagnoses.Area, Diagnoses.DiagnosisDate, Diagnoses.Description, 
    COUNT(*) AS TotalDiagnoses
FROM Diagnoses 
INNER JOIN DiagnosisItems ON Diagnoses.DiagnosisId = DiagnosisItems.DiagnosisId
WHERE Diagnoses.Area IN @Areas
GROUP BY Diagnoses.Area, Diagnoses.DiagnosisDate, Diagnoses.Description
ORDER BY Diagnoses.Area, Diagnoses.DiagnosisDate, Diagnoses.Description

Click on your table then right-click the grey handle that appears on the left hand side in the Detail row. Choose Add Group and group by Area. Add a group header and a group footer. In the table, put the Area in the group header and in the group footer put Area and the total diagnoses for that area:

=SUM(Fields!TotalDiagnoses.Value)

Now on your Detail row for the percentage you'll use an expression like this:

=Fields!TotalDiagnoses.Value / SUM(Fields!TotalDiagnoses.Value, "table1_Group1")

Right-click on the group handle, select Row Group-Group Properties-Page Breaks and check page breaks between each instance of a group.

Upvotes: 2

Nathan Griffiths
Nathan Griffiths

Reputation: 12766

I don't think you need to put a filter on the tablix. Just set the grouping on the list and add the tablix.

Upvotes: 0

Related Questions