[Progresso] Adding a parameter to SSRS report that is using shared datasets.

[Ditto]

We are an all-through school but would like two versions of the report "AL_wgt_Roll Call Attendance Summary", one for primary and one for secondary. Advanced support are looking at this for me, but at one point tried to sell consultancy to get to a solution. They initially proposed a solution of setting the Report property 'Adv. Filter Category' to 'Learner'. That isn't compatible with using the report as a widget on a dashboard, but does mean I can get the data I want but have to manually run and filter each time. Ideally, what I'd like to do is add a parameter to the report (year or course would do) so that the saved parameter feature can be used on the dashboard. It'll also allow me to run for primary, secondary and whole school at will. I've tried to follow the instructions given at https://customers.advancedcomputersoftware.com/s/article/SSRS-Adding-a-parameter-to-a-shared-dataset but hit problems. The attempt to use tablix filters got close to a solution but it didn't work in all parts of the report. The issue I hit is the shared dataset behind the gauge did not contain any fields to allow me to filter on year. If there is a 'Lookup' that can be implemeted, so always givers the number for the whole school. Any suggestions as to a solution would be welcomed.

[djrscally]

So you want two versions; one showing just Primary kids data and one Secondary kids data? The simplest method is to replace the filter on Learner Attributes. You could add Dataset filters to the PR_Learners dataset to filter to the right Years for Primary / Secondary, and then edit the "Attribute_Learners" parameter to draw its default values from the LearnerID fields of PR_Learners instead of PR_Learner_Attributes.

 

If you want to maintain the ability to filter on attributes in addition to Year it's slightly more complicated; let me know if that's the case.

[Ditto]

Thanks @djrscally. I'd be happy with two separate reports, but would be interested to understand the steps to additionally have one report, with a 'Course' filter. I have not quite got my head around this style of report yet and whole shared dataset thing versus the model approach that I'm more used too. I see the idea, but can't quite get it to come together yet. I'm generally getting one of two errors:

"The report parameter ‘LearnerList’ has a DefaultValue or a ValidValue that depends on the report parameter “LearnerList”. Forward dependencies are not valid." or

"The 'LearnerList' parameter is missing a value".

[djrscally]

Alright; so start from a fresh copy of that report and do the following:

 

1) Add a new parameter called "Phase" with labels "Primary" and "Secondary" and whatever values you like as long as they're distinct; I'll go with "P" and "S".

2) Select "Phase" in the list of parameters and mash the Up arrow on the UI above the Parameters list until it's higher than "LearnerList" (This deals with the forward dependency thing)

3) Edit the "PR_Learners" dataset, go to Filters. Add a new Filter and set the Expression to =Switch(InStr(" 1 2 3 4 5 6 ", Fields!Year_Code.Value) > 0, "P", InStr(" 7 8 9 10 11 12 13 14 ", Fields!Year_Code.Value) > 0, "S", True, "X"). Set the Operator to "In". Set the Value to [@Phase]

4) Edit the "Attribute Learners" Parameter, go to "Default Values". Set the Dataset to "PR_Learners". Set the Value field to "LearnerID"

 

When you run the report you should now be able to select either "Primary" or "Secondary" or both and have only the appropriate Years come through. Be aware this does break the "Learner Attributes" parameter though, so strip that out (unless you actually want both, in which case it gets a bit more complex). If you want more granular control just call it "Year" instead of Phase and make the Expression in Step #3 simply be [Year_Code] and the Value be [@Year].

[Ditto]

Thanks for the extra detail. I have managed to get it working now. I've had to make a few other tweaks due to having a Year Groups "Reception" with a code value "R". The pie-charts were not picking Reception up at all and the tables tended to put them at the bottom rather than top. There's already some code in there for some on a calculated "Year_Sort" field, but it was a bit back to front. With respect to the 'Attribute Leaners' parameter, it seems a bit suspect anyway even in the live report, so dropping it might not be such a bad thing. I found the error "The 'Attribute_Learners' parameter is missing a value" coming up far too easily - for example selecting 'E' and 'K' SEN codes caused this error.

[Ditto]

I've pretty much got the reports where I need them, but one thing has me baffled with the report table for Reg groups.

In DS_Learners there is a calculated field Year_Sort. When it's coded as =IIF(Fields!Year_Code.Value="R", 0, 99) and I display Fields!Year_Code.Value, I get 0 (zero) for Year_Code "R", which ties to Year.Value = "Reception". When I code it =IIF(Fields!Year_Code.Value="R", 0, CInt(Fields!Year_Code.Value)), a blank displays for Year_Code - all the other years display sensibly as integers. What's going on here?

[Ditto]

Just to add, I switched from CInt (realising Year_Code isn't a numeric) to Val, and whilst it fixes the issue, it doesn't really explain the behaviour in the previous post, but it makes me wonder how you'd ever know the data type of a calculated field when it's added to a dataset.

[djrscally]

Err yeah bit weird. I would have expected it to show #Error to be honest since CInt("R") is not a valid operation.

 

I think there are reports in Progresso that document the shared datasets. Look for ones called AL_tmp_* - if you run those it returns a list of the fields and I think has the data type there