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 cell formatting?

hevi · 2013-04-25T05:47:48+00:00

Hi, I have report with several graphs and one crosstab. The layout of the crosstab is as follows ColumnName[colIndex] DataFrom | DataType[rowIndex] || Data It should look…

hevi

Hi, 
 
I have report with several graphs and one crosstab. The layout of the crosstab is as follows 
 
ColumnName[colIndex]
DataFrom | DataType[rowIndex] || Data 
 
It should look something like 
 
Monday Tuesday ......
Paris Furniture 1200 2000 
Candies 120 220
ROI 5.5% 7.0%
 
London Furniture 2200 2700 
Candies 220 320
ROI 6.5% 8.0% 
etc 
 
The data is produced by a database query and all computation is done (in Java) before pushing the data to the 
report. 
 
A line of data to the report looks like: 
 
{dataFrom:sales, dataType:candies, columnName:Monday, columnIndex:1, data:1200,.....} 
 
Everything works okay, for the exception of the rows (and a specific columns) containing percentages. All data, including percentage is pushed in the double datatype from Java, i.e. 0.055 for 5.5%. The report design data type is decimal. 
 
The requirement of the rows and columns containing percentages is that its shows the %-sign and if value is positive the color of the cell/number should be green, if negative then red. 
 
I have managed to get the colors on the correct row and column cells containing the percentages. However I am unable to get the % sign into those same cells. 
 
In the crosstab script I have 
<pre class='_prettyXprint _lang-auto _linenums:0'>
function onCreateCell( cellInst, reportContext )
{
if( cellInst.getDataValue("columnName")=="ComparedToPreviousMonth" ||
cellInst.getDataValue("dataType")=="ROI")
{

// cellInst.getStyle().setNumberFormat("##.##%");

if(cellInst.getDataValue("data_TypeGroup/dataType_columns/columnName") < 0)
cellInst.getStyle().setColor("RGB(253,120,120)");

if(cellInst.getDataValue("data_TypeGroup/dataType_columns/columnName") > 0)
cellInst.getStyle().setColor("RGB(0,139,8)");

}

}
</pre>
As I said the coloring works correctly for the given rows and columns. But if I remove the comment on the line with setNumberFormat, then every cell in the whole crosstab gets formatted with % and extra zeros. 
 
It appears that setNumberFormat somehow affects every cell while the coloring works as expected, for the specified row and column cells. 
 
How should I solve this problem?

Find more posts tagged with

Comments

kclark

It looks like you are wanting to add the number format to every third row, is the correct? You could try adding a counter in onCreateCell() and check to see 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>if (counter == 3) {
// Add number formatting
counter = 1;
}else{
counter++;
}</pre>

hevi

Thanks for your reply, but I don't think that makes a difference. 
 
Let me try to clarify. This is how the rows for a DataFrom object with 5 dataTypes looks like without setNumberFormat in the if-block (here the percentage row is the first, and the percentage column is the last):

messiii

This really answered my problem, thank you!

mutzbraten

Hi,
 
I know, it's an old post, but I run into the same problem. Depending on the group inside a crosstab I want to format the numbers.
If I format the background color it works fine but formatting the numbers just works once.
 
And this is the script
var y = reportContext.evaluate("dimension");
 
// Cells of group 0Bestand should be formatted red with number format '#.###'
// This works fine 
if (y == '0Bestand') { 
 this.getStyle().backgroundColor = "red"; 
 this.getStyle().setNumberFormat("#,###"); 
}
 
// Cells of group 1Anteil should be formatted yellow with number format '#.###,00'
// Coloring the cell does work fine, but formatting the number doesnt 
if (y == '1Anteil') { 
 this.getStyle().backgroundColor = "yellow"; 
 this.getStyle().setNumberFormat("#,###.00"); 
} 

The cells background is changed depending on the current crosstab group but the number format is'nt.
 
Any ideas?
 
Thanks in advance,
Dirk