July 2, 2012 at 1:56 am
Hello,
Here my table qurey result
Product1 2012-06 Customer1 10
Product1 2012-06 Customer2 20
Product1 2012-06 Customer3 5
Product2 2012-06 Customer1 12
Product2 2012-06 Customer2 14
Product2 2012-06 Customer3 84
I have a tablix with
Period
Product n (grouped) and group columns (grouped) like
Product Customer1 Customer2 Customer3
Product1 10 20 5
Product2 12 14 84
While Products rows cells are OK with this
=iif (RunningValue( Fields!Product.Value , CountDistinct, Nothing) mod 2, "White", "Gainsboro")
the columns (customer1 Customer2 Customer3) are sometimes not OK. (and this is "expected" as columns are grouped...)
Is it possible to retrieve the background color of the first column of each line ?
Tks for your help !
July 3, 2012 at 9:23 pm
Hi,
The query results you've provided are not enough to reproduce your problem. When I set up a report like this, there are only two rows and the second row background is Gainsboro, and it works for all the cells. Can you provide data that does reproduce the "sometimes not ok" ?
To answer your question, no you can't access the background color property of another cell. You can access the value of a cell by using ReportItems!Textboxname.Value but that's all. You could use group variables assuming SSRS 2008 or newer but I don't think that is appropriate here.
Looking at the formula you are using to control the background colour of cells,
=iif (RunningValue( Fields!Product.Value , CountDistinct, Nothing) mod 2, "White", "Gainsboro")
The scope [Nothing] in the RunningValue function is something you could try changing. Replacing Nothing with "Customer" or whatever the name of your customer column group is might help, although I suspect I see the cause of this.
In your results, if Product2 Customer1 has no value, then it doesn't increment the CountDistinct, and the CountDistinct will still be 1 at that point and so the cell will stay white. That empty cell still counts as part of the previous RunningValue. Interestingly it's only a problem when the first column's cell is empty. If any of the columns to the right are empty then it doesn't matter that they don't increment the product count because the distinct product count is still correct. Technically those cells aren't empty, they don't exist at all. They are holes in the tablix that are only there so the tablix doesn't collapse, that's the nature of all cross-tabs.
Assuming your Tablix is called Tablix1, try this formula for the background color:
=iif(RowNumber("Product") = 0
,iif((Cint(RunningValue( Fields!Product.Value , CountDistinct, Nothing)) + 1) mod 2
, "White"
, "Gainsboro")
,iif (RunningValue( Fields!Product.Value , CountDistinct, Nothing) mod 2
, "White"
, "Gainsboro"))
The Rownumber of the group "Product" resets for every new member of the Product group and will be zero in the first column if that product doesn't exist for customer1. It doesn't matter how many empty cells are in the columns because as soon as you get one that isn't empty, the CountDistinct is incremented and you are covered for the rest of the row. If the entire row is empty then it won't display and you won't have a problem.
I'm not sure if this completely covers your case because I wasn't able to reproduce your problem with the data provided. If you give me data that does show the issue then I'll try and help.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply