ssrs 2008 final report totals

Question

ssrs 2008 final report totals

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Total Students - COUNT() of StudentID (DISTINCT if you have to).
Total Letters Produced - COUNT()... not sure for what?
# Spanish-Speakers = CASE WHEN [SpeaksSpanish] = True THEN 1 ELSE 0 END
#Non-Spanish Speakers CASE WHEN [SpeaksSpanish] = False THEN 1 ELSE 0 END

You could just create a dataset of Students, and then create a variable @StudentCount and then just divide.
Basically, FILTER in the stored procedure, then do the Counts and % in the report.

That whole stored procedure you posted seems like overkill. Take a step backward and build a simple query to get the base data you need and go from there.

dianerstein 8713 SSCrazy Points: 2197 More actions · Answer 2

Can you tell me what you mean by, "Basically, FILTER in the stored procedure, then do the Counts and % in the report?
You are saying to concatenate the results of the query by left joins to the existing result dataset correct?
.

pietlinden SSC Guru Points: 63503 More actions · Answer 3

I meant that...
1. Don't use that diabolical stored procedure to do something this simple. You just don't need anything near that complex. (with lots of NO LOCK / READ UNCOMMITTED hints).

2. should filter for the schools you wanted to include in your stored procedure. Pass a delimited string of values and then use DelimitedSplit8K to filter the dataset in the database. Then you return these columns:
School,
Calendar,
StudentID,
NativeLanguage (whatever that is)
... Not sure where TotalLetters comes from.

3. Then in your SSRS report, you could create a report variable and grab the count of [unique] studentID's and maybe a total number of letters.
Then you just divide the counts by PopulationSize. @PopulationSize could be something like = COUNT(DISTINCT([StudentID]))

The # of Spanish-Speaking and Non-spanishSpeaking... You'd have to split that up in the Stored Procedure... Something along the lines of
SpeaksSpanishAtHome = IIF(Household.HomeLanguage = 'Spanish', 1, 0)
SpeaksEnglishAtHome = IIF(Household.HomeLanguage = 'English', 1, 0)
Then you can divide the totals of those (calculated in your matrix, not in the stored procedure) by @PopulationSize.

dianerstein 8713 SSCrazy Points: 2197 More actions · Answer 4

In your last response your mentioned, 'in your SSRS report, you could create a report variable'. Can you tell me how to create this report variable? Is this a report parameter that is hidden? If not, please explain what you mean by the 'report variables? Is this some kind of hidden object in ssrs? Would you explain what you mean?

pietlinden SSC Guru Points: 63503 More actions · Answer 5

I'm using SSRS 2016, but I'm pretty sure this is still the same.
Open your report, and switch to design mode.
Then go under the Report menu to Then go under the Report menu to Report Properties.
Click Variables on the left window. Click Add. Name it something like PopulationSize then use a formula similar to this:
CountDistinct(Fields!PatientID.Value,"DataSet1")

DataSet1 in my case consists of (PatientID, SymptomName, Grade), so there are duplicates of everything.

in your case, you'd use StudentID instead. Then you can get your count and divide it by the population Size variable.

=COUNT(Fields!PatientID.Value) / Variables!UniquePatientCount.Value

Variables are really handy when you have to calculate something once and reuse it all over the place - like when you're doing relative frequency, Count(reported) divided by the population size.

Hope it helps.