1

I have a table with 2 columns, TutorInitials and TargetGrade. Where a user does not have a grade, we need to replace it with the grade given to a different user. e.g:

TutorInitials    TargetGrade
1                A
2                B
3       
4                C

TutorInitial 3 has no grade, so wherever they pop up in a report when showing 'TargetGrade' we need to replace the empty box with the 'TargetGrade' result for TutorInitial 2 ('B'), so it shows up like this:

TutorInitials    TargetGrade
1                A
2                B
3                B
4                C

Apologies if I have not explained it very well, I am very new to SSRS.

I have tried using IIF and WHERE expressions but cannot get it to work.

Thanks Rob

Edit: What has happened is we have subjects at a school where the teacher has changed halfway through the year. The new teachers need to have a students target grade pulled through from the previous teacher. So rather than just grabbing the grade from the cell above, it needs to come from a specific tutor (the subjects first teacher), so I might need to replace the grade with the grade for 1,2 or 4.

Rob
  • 59
  • 8
  • Why 2,B? How did we decide it? Why not C instead of B? Always one prior's grade? – Eray Balkanli Feb 07 '18 at 15:14
  • Try this-> IIF(IsNothing(Fields!TargetGrade,Value),Previous(Fields!TargetGrade,Value),Fields!TargetGrade,Value) – Eray Balkanli Feb 07 '18 at 15:19
  • What if the previous row also has no grade? – Mazhar Feb 07 '18 at 15:21
  • Sorry I should have been more clear. It is not trying to specifically grab the previous line's grade. I am wanting to fill its with another specific users grade. So I know TutorInitials 3 has no grade and I know TutorInitials 1,2 and 4 do have the grades. I could have picked any of them, but if I specifically want to insert TutorInitials 2's data. Is there a way of using the same code, but just replace the TutorInitials so if I wanted to bring up TutorInitial 4's grade when TutorInitial 1 comes up, can I do that? – Rob Feb 07 '18 at 15:47
  • Based on your edit, more information is needed about your dataset(s) now. In order to accomplish what you are looking to do, you will need some way to reference and connect the previous teacher with the current teacher. But with only the given information, it is impossible to go any further. – C Black Feb 07 '18 at 19:41
  • 1
    I think this should be addressed when retrieving the dataset (SQL) and not in SSRS.. – Harry Feb 08 '18 at 00:36

2 Answers2

0

You can use the built in SSRS Previous function. This will work if you data is sorted the way you need.

=Previous(Fields!TargetGrade.Value)

However, what would happened to tutor 4 in the following scenario (using previous would cause the target grade to be blank) :

TutorInitials    TargetGrade
1                A
2                B
3       
4                
5                C

You can get around this in one of two ways.

  1. Look into using lookups.

  2. I would prefer to store the variable and recall it when needed.

In your report code add a variable (note code may not compile as it is off the top of my head).

Dim lastGrade As String

public Function ProcessAndReturnGrade(ByVal targetGrade AS String) As String
Begin
    If (targetGrade<>DbNull.Value) Then
       lastGrade = targetGrade
    End If
    return lastGrade       
End Function

Then in your TargetGrade Expression 

=Code.ProcessAndReturnGrade(Fields!TargetGrade.Value)
Ross Bush
  • 14,648
  • 2
  • 32
  • 55
  • Thanks for this. I'm not sure either solution would work in our situation though. What has happened is we have subjects at a school where the teacher has changed halfway through the year. The new teachers need to have a students target grade pulled through from the previous teacher. So rather than just grabbing the grade from the cell above, it needs to come from a specific tutor (the subjects first teacher). I'm not sure if I am reading the second part right, but if there is a way of pointing lastGrade to our original tutors grade then that might work. – Rob Feb 07 '18 at 15:37
  • You should update your question with that tidbit. My answer does not address the requirement in your comment above. – Ross Bush Feb 07 '18 at 15:53
0

We have had a good look at this issue our end and, as Harry said yesterday, it looks like this would actually require some extensive work in SQL in the dataset. We have found it is going to be a lot simpler to reopen the old reports to users and manually add in the target grades.

Thanks everyone for looking into this.

Rob
  • 59
  • 8