SQL Reporting Services 2008 - Formatting subgroup row
Hi, I am looking for your help to format group rows. what I am trying to achieve is to set cell background color to red or green. the business rule is as follows: if B is greater than A then 'Red' else 'Green.' set background color only on B. on the screenshot below A = 'Baseline Budget' and B = 'Current Forecast.' the only section to set background color is on B. Baseline Budget and Current Forecast are cost types. these cost types are hierarchical. So, part of the business requirment is to compare the value for all rows. for instance: UCDHS -> Baseline Budget -> Jul 2011 = 22,600 UCDHS -> Current Forecast -> Jul 2011 = 3,104,000 based on the business rule the background color of cell showing 3,104,000 should be red. UCDHS -> Baseline Budget -> Capital -> Jul 2011 = 8,600 UCDHS -> Current Forecast -> Capital -> Jul 2011 = 4,000 based on the business rule the background color of cell showing 4,000 should be green. Thank you in advance for your help. this output will show multiple projects Project Cost Type
October 19th, 2012 12:44am

Hi Nando12345, Based on your scenario, I don't think this cannot be done in Reporting Sevices currently. The months (Jul, Aug, and Sep) are three instances of a field rather than three different fields, right? If so, I am afraid that you have to add an extra column into your database table to calculate the difference between the "Baseline Budget" value and the corresponding "Current Forecast" value. Then, we can set the background color of the textbox by using an expression like: =IIf(Fields!CostType="Current Forecase" AND Fields!Difference.Value>0, "Red", "Green") If the three months are three different fields, we can create another dataset to return the data for "Baseline Budget" only, and then use the Lookup() function to calculate the difference between "Baseline Budget" and "Current Forecast". Reference: Lookup Function Regards, Mike YinMike Yin TechNet Community Support
Free Windows Admin Tool Kit Click here and download it now
October 22nd, 2012 10:56pm

Hi Nando12345, Based on your scenario, I don't think this cannot be done in Reporting Sevices currently. The months (Jul, Aug, and Sep) are three instances of a field rather than three different fields, right? If so, I am afraid that you have to add an extra column into your database table to calculate the difference between the "Baseline Budget" value and the corresponding "Current Forecast" value. Then, we can set the background color of the textbox by using an expression like: =IIf(Fields!CostType="Current Forecase" AND Fields!Difference.Value>0, "Red", "Green") If the three months are three different fields, we can create another dataset to return the data for "Baseline Budget" only, and then use the Lookup() function to calculate the difference between "Baseline Budget" and "Current Forecast". Reference: Lookup Function Regards, Mike YinMike Yin TechNet Community Support
October 22nd, 2012 11:19pm

Hi Mike, Yes, the months are instances of a field where the user selects the month range. I added the extra column as you suggested and calculated the difference on the sql query. after that I was able to set the proper background color. I appreciate your help. Thanks!!! regards, Nando
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2012 1:21pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics