Matrix - Change background colour of cell depending on...

[djnickwatt]

Hi

I'm pretty new to reporting services but so far have managed to produce plenty of reports using it. However, I have come to a sticking point. I could create a stored procedure in SQL Server to hack together what I want but hopefully someone here can help so I don't have to go down that route.

The scenario. I currently work in a school. I have been asked to produce a report showing students targets and predictions per subject. Using a very simple select query I get my data out in the following format:

Name         Subject   Result Type    Grade    Value
====================================================
Student1     EN        Target         A        5
Student1     EN        Prediction     A        5
Student1     IT        Target         B        4
Student1     IT        Prediction     A        5
Student2     EN        Target         C        3
Student2     EN        Prediction     B        4
Student2     IT        Target         B        4
Student2     IT        Prediction     B        4

*The value column is how much a grade is worth - A = 5 points, B = 4 points etc

Using a matrix I have presented the information exactly as they want it by using column grouping on Subject then a group below that on Result Type finally presenting the Grade. So the matrix basically looks like.

           EN    IT
          T  P  T  P
Student1  A  A  B  A
Student2  C  B  B  B

All fine and dandy so far. Well now they want colour coding on the predicted grade background depending on the target. If the target grade is the same as predicted then the background would be light green, if it is better then dark green, one worse would be amber and anything else red.

I know how to form IIF statements for comparing values. But how do I go about doing this? I assume the easy way is comparing the Value columns but do I need to include this on the grid? If I put the IIF statement in the background color of the cell how do I reference the value from the target and prediction to carry out the comparison and display the necessary color.

I hpe that sounds clear! Any help would be greatly appreciated.

Thanks in advance.

Nick

 

[xlbo]

As far as I know, SSRS uses contextual aggregation so you may well simply need to use somethig like:

=iif(Fields!Target.Value = Fields!Expected.Value,"Light Green",iif(Fields!Expected.Value - Fields!Target.Value >0,"Dark Green",iif(Fields!Target.Value - Fields!Expected.Value = 1,"Light Orange","Red")))

in the background colour property

SSRS should do the rest

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[djnickwatt]

Thanks for your reply.

Unfortunately the Target and Predicted grade values are not in the same row from the recordset. They are each returned on a different line and then using the matrix grouping displayed next to each other so their field name is actually the same. Therefore I believe this won't work.

 

[xlbo]

ah I see - apologies for not spotting that

Can you manipulate your recordset so that the data is returned on the same row? otherwise I'm not sure how this could be dealt with...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[djnickwatt]

That's exactly what I've had to do. In fact what I did was return the recordset with both grades and the value comparison on the row. Meant deciding on the colour was EXTREMELY easy ha ha. Just means the Stored Procedure needs to run an extra select. Aw well, can't have over efficient queries all the time!

Thanks for getting back.

 

[MDXer]

I don't remember the syntax but I remember once I was able to call the cell value as it existed in the report as opposed to working with data in the dataset.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[xlbo]

That's the "ReportItems" collection I would've thought - not sure how that'd work in a matrix though...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question