Hey Guys. Think I've got a tough question for you. Is it possible to format a cell in a matrix depending on the cell left of it?? Example (The Value-Row is like this): 0 6 6 8 4 2 2 0 First cell (0) has no change, from nothing to 0. BackColor = White Second cell (6) is an increase of 6. BackColor = Red Third cell (6) Diff 0. BackColor = White 4th cell (8) increase. BackColor = Red 5th cell (4) decrease. BackColor = Green ...and so on. Is it posible to handle that with RS? If yes, how?? Thank you for your helb guys

Hello, Tobias There may be a couple of different ways to approach this problem by using logic in the query or in the report. In order to help, I'll need to know: How are the columns grouped (on what field)? What do these numbers represent?Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com *Please vote if helpful*

Hello :-) Okay. Try to explain. We are improving our data-quality on our database. For this we design queries to check the logic of the data. For example humans younger than 18 cant open a case in our office. So we check the age of all persons in the tables by Stored Procedure. SP1 (AgeCheck) SP2 (CountryCheck) SP3 (SexCheck) ... We are doing this every three to four weeks. So we have different runs. Run10 (10.01.2011) Run11 (15.02.2011) Run12 (01.03.2011) ... The people who caused the wrong data has to correct it. So we can see that for SP2 Run10 15 (Errors) SP2 Run11 12 (Errors) SP2 Run12 13 (Errors) ... The result is a matrix groupt by Run and SP. We want to visualize by the backcolor "Increase", "Decrease" and "neutral" (red, green, white). We have a version of the report using a table actually. The problem is, after every run we have to add a column and the conditional formatting. I want to automatize this, using a matrix. Is it possible to upload a picture here?? Only by link, right? Thanks for helping, Tobi

Hi Tobi, You can you "skydrive" to upload your pictures/rdl files/etc. Please keep your files under your "public" and paste the links here. Thanks KumarKG, MCTS

I've got a picasa-account. Here some ScreenShots: https://picasaweb.google.com/118133003111695053561/Matrix#5585309791142296690 https://picasaweb.google.com/118133003111695053561/Matrix#5585309903080866994

Please try the below. 1. Add the below in the report code window Public Shared Previous As System.Collections.Generic.Dictionary(Of String, Integer) Public Shared Current As System.Collections.Generic.Dictionary(Of String, Integer) Public Shared Function Total(ByVal Item As String, byval Amount As Integer) As Integer If Previous Is Nothing Then Previous = New System.Collections.Generic.Dictionary(Of String, Integer) End If If Current Is Nothing Then Current = New System.Collections.Generic.Dictionary(Of String, Integer) End If If Not Previous.ContainsKey(Item) Then Previous.Add(Item, Amount) End If If Not Current.ContainsKey(Item) Then Current.Add(Item, Amount) End If If Previous.ContainsKey(Item) And Current.ContainsKey(Item) Then Previous(Item) = Current(Item) Current(Item) = Amount End If Return Amount End Function Public Shared Function Variance(ByVal Item As String) As Integer Return Current(Item) - Previous(Item) End Function 2. Set the below expression in the matrix data cell. Item refers to the field that is unique for the row. Amount represents the field for the value displayed in the cell =Code.Total(Fields!Item.Value,sum(val(Fields!amount.Value))) 3. Set the below expression to the background color property of the matrix data cell. Item refers to the field that is unique to each row. =switch(Code.Variance(Fields!Item.Value)>0,"red",Code.Variance(Fields!Item.Value)=0,"white",Code.Variance(Fields!Item.Value)<0,"green") Note : Please replace the field Item and Amount with the one that is used in your dataset. Hope this helps. Please feel free to discuss if you have any further queries. Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

Hello Bilal, thank you for answering :-) At the moment i am not in the office. Will be back on 4th of april, check it imediately and response here. Thank you very much for helping. Tobi

Hello Bilal First, thank you for this detailed information. It helped a lot and I get an idea how it could work. I tried it today, changed a little bit. The Formatting is not working correct until now. Perhapes you, or somebody else, has a suggestion. I will go on tomorrow. Here is what I changed in the matrix data cell: =Code.Total(ReportItems!Fehlernummer.Value, val(sum(Fields!AnzahlFehler.Value))) And this I changed for the formatting: =switch(Code.Variance(ReportItems!Fehlernummer.Value)>0,"green",Code.Variance(ReportItems!Fehlernummer.Value)=0,"white",Code.Variance(ReportItems!Fehlernummer.Value)<0,"red") And this is how it looks like... https://picasaweb.google.com/118133003111695053561/Matrix#5591744395839817090 Thank you for any help, Tobi

Some new information. Seems the formatting is always shifted one to the right. I put the difference (Variance) into the cell to see the values. Here the ScreenShot https://picasaweb.google.com/118133003111695053561/Matrix#5591748585508389666

It sounds very strange because the correct variance is displayed in the cell. It is the same variance value used in the expression to set the background color conditionally. I checked a sample report here and found it works ok in terms of showing the variance and applying the relevant color. Please check if you can find out any difference in the rdl compared to the solution I provided. Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

I dont get what the problem is. Two ideas: >> I am using an older version of VS and SQL Server, could it be there was bug? Designer für Microsoft SQL Server Reporting Services Version 9.00.4035.00 Microsoft SQL Server 2005 Version 9.00.4035.00 >> In the designer-preview i get an error: [rsRuntimeErrorInExpression] The BackgroundColor expression for the textbox "xx" contains an error: Object reference not set to an instance of an object. Perhapes i should use an array containing the values eg. item"1-50" value"5"; item"1-51" value"7" and so on... What do you think?

You had previously posted two screenshots in picasa. When you had that screen output, did you get this error “Object reference not set to an instance of an object”?Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

In the last ScreenShot: yes https://picasaweb.google.com/118133003111695053561/Matrix#5591748585508389666 The one before I dont know. Think so, too.

With the error message do you get any reference to the line number that is causing the error? Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

Sorry. Line = 0, Column = 0

I set up a new report with the same data and a complete new matrix. Thought, probably there are some changes in the property during the developement processe. But no. Same like in the old report. Again the formatting it shifted one to the right.

Is it possible to share your rdl file with a dummy embedded dataset showing the problem? If you have live account, you can upload and provide the link. or else you may email the file to irshadmohideen at hotmail dot com. Regards Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

I sent you an eMail.

Received your file. Thank you. Upon reviewing, I did not notice any change in behaviour. The output is as expected. I guess this issue is stemming from your version being SSRS 2005. As of now, not sure what is causing the issue; it may require some digging into the compatibility between vb.net codes within SSRS 2005 and 2008. Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

I anticipated that during the last messages. Thank you very much for your help, bilal. I will try to solve that somehow. Tobi

Hi, Did you manage a solution for this? Can you please give some info about your datasource? Is it coming from a table or cube? What is the structure of it? Perhaps a solution can be reached by retrieving the color value from the query itself. Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

Hi Bilal No I didnt found a solution. I set up VS.Net 2008 on the same computer, openened exactly the same file I sent you and it worked. Also the original (complicated) report works in VS.Net 2008. In VS.Net 2005 it even doesnt work with this simple DataSet I sent you. I also thought about changing the query to deliver the "ColorCodes". I will try this in a simple report first. The data is coming from tables. I am using the PIVOT function in it. Does it matter??

If you could use 2008, I would recommend to stay with that as you can leverage load of enhancements and new features available in the 2008 version. Due to any restrictions, if you are compelled to stay with 2005, what you have thought of doing (getting the color code in the dataset), should be an excellent idea. You could use the PIVOT; I dont think there is any issue. Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

I know this is an old post but I was hoping you could help. I'm trying to do the same thing as described here but I'm using a Matrix not a table. While the code you gave works it seems the data in a matrix is added to the report from top to bottom instead of left to right. Do you know of a similar solution that can be utilized with a matrix?