nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Crosstab conditional formatting

Rainer

Hi all,

I´ve got some data in a crosstab (6 dates (changing by users parameter selection) and observations per date). My Customer wishes a conditional format in this report. If a value on one date differs > 10% from the nearest date in the list, this value gets highlighted. If the difference is less than 10% theree is no extra format needed.

Is this possible with birt 2.3.1?

Thanks Rainer

Find more posts tagged with

Comments

mwilliams

Hi Rainer,

Can you post a screenshot of a crosstab and describe on it what you want. I think I understand, but would like to see an example to be sure. Thanks.

Rainer

here it is... (in the attachment..)

Rainer

I think here you can watch it better...

mwilliams

Rainer,

So, you'd want the cell to be highlighted if it is 10% different than the value in the same row in the month before it? Not highlighted if the value is 10% different than the value above it in the same month? Or both? Just trying to get a better idea so I can do some testing.

Rainer

Hi,

"So, you'd want the cell to be highlighted if it is 10% different than the value in the same row in the month before it?"

This is what I´m trying to do!! Nothing else

Thanks

mwilliams

Rainer,

Is the attached screenshot a representation of what you'd like to see? I used the data (as close as I could read it) from your screenshot in a flat file database. Let me know and I can tell you how I did it.

Rainer

Michael,

this seems to be perfect. Can you please tell me, how you did it?`I mean how to get the value of one specific point in the matrix and compute the difference?

Many Thanks

Rainer

mwilliams

Rainer, 
 
Because of the way a crosstab is rendered, I used global variables for each row category and checked the last value compared to the new value in the expression for a highlight rule. 
 
In the onFetch method of the dataSet, I put the following to initialize global variables for each possible category to "". 
 
reportContext.setPersistentGlobalVariable(row["Category"], ""); 
 
Then, on the measure data item in the crosstab, I created the following highlight rule expression and used the "Is True" condition. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
temp = reportContext.getPersistentGlobalVariable(data["Category"]);
temp = parseInt(temp);
if (temp == ""){
false;
}
else {
tenpercent = temp * 0.1;
diff = data["Value_Group/Category_Group1/Month"] - temp;
reportContext.setPersistentGlobalVariable(data["Category"], data["Value_Group/Category_Group1/Month"].toString());
if (Math.sqrt(diff * diff) >= tenpercent){
true;
}
else{
false;
}
}
</pre>
 
If you would like a further explanation on this, let me know. Hope this helps.

Rainer

Michael,

I think I´ve learned a lot about the interals. I will try to integrate your solution in my report. Many thanks for your help!!

Rainer

mwilliams

Rainer,

No problem. Let me know if you have any issues. I'll try to help.

Rainer

Michael,

it works well in my report. I modified your solution that way, that background color only turns red when the positve difference is greater than 10 percent. Would it be possible to add a highlight rule which turns background to another color, if negative difference is greater 10 percent. I tied this with another rule but it seems not to work as expected.

Rainer

mwilliams

Rainer,

All you'd need to do is put an if statement inside the main else statement checking whether "diff" if greater than 0. You do this in both highlight rules and in one if "diff" is greater than 0, you continue with your check and the other if "diff" is less than 0 you continue with your check. Let me know if I'm not explaining this well enough.

Rob77

Hi,
I have found your message in google, looking for very similar answer. However I must say I don't get all the ideas (I try the BIRT for few days only).
What do you mean by categories? How can I know all possible categories?
In my case I use cross tab with 3d data.
I organized them in two groups:
- group a, with header values for columns
- group b, with subgroup c, with header values for rows
(those are dimensions)
And my measures are like: select avg(something) from tables.
I put everything into cross tab (drag&drop). I would like to highlight measure, when it's value differs from "previous" measure (previous in group a).
Could you help me understand this issue?
Regards,
Robert

mwilliams

Robert,

"Category" in the example for Rainer was what his row dimension was. If you're wanting to check for changes in your columns, you could do it this way only storing the values in PGVs named for the column headers. There may be an easier way to do this if you're doing it by column rather than row because of the way a crosstab is rendered though, but this would work.

Rob77

Thank you for your answer.
However I expected amount of work to do that to be "symmetrical". I mean I have three data dimensions and going to prepare some data sheets with different data assignment, like:
1. Columns: a, rows: b/c
2. Columns: b, rows: a/c
3. Columns: b/c, rows: a
And I expected to have an easy way to extract other cell data, sometimes referring to previous columns, sometimes to previous row...
What site (or source) would you recommend to learn this topic more deeply?

Rob77

In other words (days spent on various forums): 
which properties can I use inside cross tab? It appears to be different that table in such way that I can't get row data (returns null). 
I would like to know in what way cross tab is constructed, how are events generated (on <a class='bbc_url' href='http://www.eclipse.org/birt/phoenix/deploy/reportScripting.php'>Report Scripting</a> there is no mention of cross tab nor onCellCreate ...). 
How can I pass global parameter from report (setPersistentGlobalParameter?) to cell (onCreate, I can see no way to obtain previously set value)? 
And last question, which came to me ... In BIRT example demonstrating how to use cross tab, measures and dimensions are dropped directly from cube to row/column headers and summary field. Instead of dropping group elements I dropped whole groups. Is that correct? In the end I got no data binding on given cells. It works, but I wonder whether I missed something. 
Would you point me to more documentation (if it exists)?

mwilliams

Robert, 
 
You can find the documentation for the report designer <a class='bbc_url' href='http://www.birt-exchange.com/modules/documentation/birt-report-designers.php#currentdocs'>here</a>. I'll look into your other questions. If you can set up a flat file or multiple flat files of data (however yours is set up) and a report design that uses these flat files as datasources to show what you're looking for and a description of the highlighting and what not that you're wanting to do, I can see if I can figure a way to help you out.

Rob77

Thank you for the link. However it appears that help is the same I have in eclipse. For the api, though, I would have too dig into.

As for the data ... I will try to prepare it, but for now I will simplify it. Let's assume I have several stores in several cities. I collect monthly income from every one of them. And I would like to see when income drops or increases significantly.
I will attach png file with expected result as well as openoffice chart (XLS format).
I would use a table for that, but I don't know columns and rows (they are selected by user in report).

Oh, I have just spotted a bug in posted screenshot and xls - months are from left to right, whereas income comparison is from right to left. But the idea remains the same.

Regards,
Robert

mwilliams

Robert,

So, you want to show white as the normal background, show red if it drops a certain amount, and show green if it rises by a certain amount? For each different store? If so, this should be able to be done the same as the code posted earlier in this thread, only for the vertical direction and not the horizontal direction. I'll try to put together an example with the data I see here. If it's not what you're looking for, let me know.

mwilliams

Robert,

I'm not sure what your highlighting pattern was in your screenshot. Here is the report design and flat file I created with your data from your screenshot. I also included a screenshot of what the output looks like. Let me know if you're looking for something else. Thanks.

This design was made in BIRT 2.3.1

Rob77

Hello,
thank you very much for your time.
Actually I was looking for something different.
Sorry for not being precise.
I would like to color cells in that way:
for current shop:
if (income from previous month was bigger than current month)
color current month cell as red; /* income lower */
else
color current month cell as green; /* income higher */
Months would be sorted and first month would be colored white, because there is no "previous" month.

Please let me know if I was clear enough. Feel free to ask for any additional data.

mwilliams

Robert,

Oh, well that's even easier. I'll set that up in the report and send it to you. Then, you'll be able to see how I used the PGVs and highlight rules on the crosstab measure to do it.

mwilliams

Robert,

Here's the modified design. I just had to comment out the script that figured the 10% change. Let me know if this is what you're looking for.

Rob77

Respect. I am really impressed, because that's exactly what I was looking for.
I have been trying to achieve the same using cell.onCreate or crosstab.onCellCreate. I haven't thought of highlight rules (which appear to be designed for that purpose). After looking at your code it appears simple (shame). I will try that on Monday on my data sets, but I'm pretty confident it will fulfill my expectations.
Thank you.

Rob77

Although I think I have understood your solution, I'm still getting problems accessing current cell data. This is relevant part of my xml file:
<list-property name="highlightRules">
<structure>
<property name="operator">is-true</property>
<property name="backgroundColor">#0000FF</property>
<expression name="testExpr">(data["avg(samples_value)_Group1/platform_id_Group/build_id"] == "")
|| (data["avg(samples_value)_Group1/platform_id_Group/build_id"] == null)
|| (data["avg(samples_value)_Group1/platform_id_Group/build_id"] == 0)
|| (data["avg(samples_value)_Group1/platform_id_Group/build_id"] > 0)
|| (data["avg(samples_value)_Group1/platform_id_Group/build_id"] < 0)</expression>
</structure>
</list-property>

(please note that I use different names for my columns. The syntax should be valid, though, because it has been clicked in highlight expression builder)
Other difference is that I use aggregation (average). average is measured in database, then in data cube I choose FIRST.

When I add || true to above rule, my cells (all) are correctly colored as blue. In above form, no cell is colored.

Have you got any suggestions?

Regards,
Robert

Rob77

Ok, I got it.
Data binding created by BIRT for cross tab specified type of avg to String. It didn't play well with integer type of that cell. After changing data type in binding to int it started behaving well.
And real data are fine. Thank you again.

Rob77

One more thing came to my mind.
The cells appear to be created from left to right.
What if I want to reverse order, in other words start from right to left?
Current code works well for the following layout:
Jan Feb Mar Apr May Jun ...
What if it is like that:
Jun May Apr Mar Feb Jan ...? (In your code there were rows, in my data those are columns). Is that possible to keep the logic (compare current month with previous month with reversed presentation order)?

Rob77

Would I have then to create internal representation of entire table?
Like:
onFetch():
reportContext.setPersistentGlobalVariable(row["City"]+row["Shop"]+row["Month"], "");

crosstab onCreateCell:
reportContext.setPersistentGlobalVariable(data["City"]+data["Shop"]+data["Month"], currentData.toString());

highlight rules:
reportContext.getPersistentGlobalVariable(data["City"]+data["Shop"]+previous(data["Month"]));

where previous would be function, which returns previous month, something like:
function previous(String month) {
if (month == "Jul-2008")
return "Jun-2008";
else if (month == "Jun-2008")
return "May-2008";

or something similar using arrays.

What do you think?

Regards,
Robert

mwilliams

Robert,

I'm not sure you could achieve this with the same logic if you tried to go from right to left because of the way in which reports are rendered. You'd have to have the values figured out for each cell and assigned to their PGV prior to creating the highlight rules for the crosstab.

You could give that idea a try. I haven't done anything like that, so we're in the same boat there, testing.

Another way that might work could be to create two identical crosstabs and hide the first one. Then use it to assign all the values to the PGVs and then call those in the second crosstab for your highlighting. I have not attempted this either, so I'm not sure.

infips00

Hi,

I'm new using birt, and need help with the cell's formattting in a crosstab. In the report I'm developing, I'm using a script datasource that receives a list of javabeans from a java application. Each java bean has the next attributes (dataset idem):

private String tipoInforme;
private String concepto;
private Integer ejercicio;
private String formato;
private String mes;
private Double valor;

The report use a crosstab with the next dimensions:

tipoInforme
concepto
ejercicio
mes

and the last attribute (formato) I need to use to format each cell. The attribute 'formato' can use the next values: 'decimal' and 'percentage'. I need to apply a format to the cell depending on the value of the attribute 'formato' (important: 'formato' IS NOT A DIMENSION).

Is it possible?

It's urgent!!!!

Thank you