Change dynamically background color of cells

Hello,
 
I have the following problem: I would like to illustrate a table in an report, which says, who already paid for something, who hasn't paid yet, and who paid too late. So I have, for example, the following result from my data set (query on db):
 
Name Month DayPaid LastChance
Lisa January 0 20150131
Laura January 0 20150131
Tim January 20150108 20150131
Lisa February 20150301 20150228
Laura February 0 20150228
Tim February 20150315 20150228
 
If someone paid punctual, the cell should be green. If someone hasn't paid yet (DayPaid is 0), the cell should be red. If someone paid, but too late (DayPaid is higher than the LastChange mark), the cell should be yellow. 
 
This should be the result:
 
Name January February
Lisa red yellow
Laura red red
Tim green yellow
 
 
 
How can I do that? the problem is, that the range of month should be dynamically over report parameters, it can be 2 months, but it can also be 6 months (but not more than 12 months, i checked this before with a plausibility, otherwise the page would be to small). It depends on how the user tunes it. 
 
How can I implement the range of month above and underneath it the cells with the colors??
I am at a loss.
 
Thanks already for your help!

Find more posts tagged with

Comments

Miss_Anonymus

Hello,
 
I have the following problem: I would like to illustrate a table with persons, who have already paid for something, who haven't paid yet, and who paid, but too late. From my db query I have the follwowing result from my DataSet:
 
 
Name Month DayPaid LastChance
Lisa January 0 20150131
Laura January 0 20150131
Tom January 20150601 20150131
Lisa February 20150304 20150228
Laura February 20150304 20150228
Tom February 0 20150228
 
 
The cells of the ones who paid correctly (DayPaid < LastChance, DayPaid != 0) should be green.
The cells of the ones who didn't pay yet (DayPaid = 0) should be red.
The cells of the ones who paid, but too late (DayPaid > LastChance) should be yellow.
 
So, the result should be the table on the report as following:
 
 
Name January February
Lisa red yellow
Laura red yellow
Tom green red 
 
 
How can I implement this in a table? the problem is, that the number of months can be variable, it can be 2 Months, but, if the user want it so, also 6 months. This is given by report parameter. Maximum are 12 Months (already checked before with a plausability check), otherwise the sheet would be too small and the table too big.
 
I am at a loss.
 
Thanks already for your help!

shamo

Hi, you can achieve that by clicking on the cell and under the properties click on highlight - add - and apply your conditions and colors. Hope this helps

JFreeman

My first thought is to use a crosstab.
 
Make the months columns, the names rows (assuming the names are unique) and the summary field will be where you set the colors. Then you will use scripting in the summary field to determine the appropriate color and then set the value/highlighting color accordingly.
 
Edit: it looks like there were two separate threads created for this question. I have merged them together.

Clement Wong

There are several ways that we can transform your original table to the desired table. As Jesse mentioned, one of them is a cross tab, in which you'll need to use a Data Cube.
 
Another method, and depending on your database, you can transform your table in your database. For example, SQL Server supports the <a data-ipb='nomediaparse' href='https://technet.microsoft.com/en-US/library/ms177410(v=SQL.105).aspx'>PIVOT </a>function. And so does <a data-ipb='nomediaparse' href='http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html'>Oracle</a>.
 
A third method, if your database cannot support pivoting/transposing, would require some scripting in your report design. You can make a copy of the original table to a global array, and then use a scripted data source to pivot the table.
 
Finally, you can use Highlighting to color in the cells in the final table.

Miss_Anonymus

I wanted to post this, but then, my browser told me that my request failed. I tried it later and then I also saw, that it got posted anyway. So thanks for merging :-)
 
But how can I set the value/highlighting color accordingly? Didn't found the property in my eclipse editor :-( And how can I do that with an Script?
 
Thanks for your help!

Clement Wong

Highlighting is available by first selecting your report item or cell. Then, go to Layout > Property Editor > Highlights. See attached screenshot:

Miss_Anonymus

thank you very much for your detailed instruction... it works fine!