[Progresso] SSRS Parental Report challenge

[Ditto]

I am working on a modified version of the system Parental Report 1 (Assessment grid - no target column). The main report is selecting values from the Year8 template/screen fine and one end of year value from the previous Year7 template. The issue I have is that for one subject, there is no previous data (effectively we have a new subject). For all other subjects we are displaying the a EndofYear7 value fine, but where the value is a missing value, I want to show an assessment value from the current Year8 instead. A similar situation may arise (for any subject in fact) where a pupil joined in Year8 and again as no Year7 data, I want to show instead the Year8 value.

 

I'm interested in suggestions for an approach. I've tried a options of routes but not yet successfully yet.

[Mic_Impero]

From memory I think I'd have a parameter to set the value for that column and use an IIF (potentially nested) to set the default to the one containing the grade. If you don't want that parameter to be visible it can be hidden.

I'm sure a current user of Progresso will be along soon to help out

 

Mic

[Ditto]

I think I may have come up with a solution which does not require editing the SSRS report too much, if at all. The idea is to create a new 'Formula' field on the template which basically says, If Y7 value exists, then use that value, else use the Y8 value. Getting the syntax right has been a struggle, but in the sandbox I think I've cracked it. I'm still interested to here opinions on other solutions too, and perhaps also if anyone else is using data from a previous years template for a current years report, or whether this approach it's best avoided.

[Mic_Impero]

The only potential downside to your idea with the formula in the template is if you're looking at % complete of the assessment template, or a teacher is looking at what is left to do by looking at the screen and they see a grade already in the cell, will they think it's already done and not bother to correct it?

I think if it were I, from a data analysis point of view I'd do it in the report and possible put an IIF on the formatting of the background too so that the shading is a different colour if the grade is from a different year / collection.

[djrscally]

I would do it similarly to Mic I think - probably just edit the text box of the field and use IIf(IsNothing()) to fetch the current year's data if the previous year's is blank

[Ditto]

@Mic_Impero, @djrscally ,thank you both for your input. On the %complete, it's an interesting point which I'll need to look at more closely. It may not be too much of an issue as firstly, last year’s data will have already gone through the checking process for all bar one subject and will be 100% complete. For this year’s data, that will need to be 100% too.

 

Your comments have made me rethink the approach. As the report was being driven by last year’s data, the fact it was missing in some cases made it nigh on impossible to look up this years as there was no record to drive from. However, and perhaps obviously in hindsight(!), if I drive off this year’s data, it should be straight forward to look up this year’s data as @djrscally suggest using (Isnothing). I will need to get my head around the lookup, as I'm still relatively new to this aspect of coding, but will get there. One of the biggest challenges of SSRS is tracking what's going on at runtime - sort of debugging. It's generally a case of it works or it doesn't. I have managed to get the calculate field approach to work by countnig whether I have one or 2 objects and setting the value accordingly. A fortunate side impact is that I am now familiar with this part of the system. I would observe that it is VERY fussy on syntax - why so many braces!

[Rebeca]

Yes, we do include templates from previous year for current years reports, especially in the Autumn parental report . We do not use Parental Report 1 but we do not encounter any issues if we use it.

It show a blank cell for the subjects that were no taken in the previous year, i.e. Spanish.

We had more problems with Year 10, His, Soc, Geo, Textiles.