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; } } }

Manipulating Data in Crosstab Cell

tripperm

I am currently working with the crosstab and am trying to manipulate the records for a cell. The summary functions available do not do what I need. One of my requirements is that if there are more than one record for a cell, I show all the values for a data field (instead of LAST or FIRST, which do work but don't do what I need).
 
It doesn't look like I can add my own aggregation functions to a crosstab, so I have tried scripting a solution using the onCreate and onRender methods for a cell. I can swap out the text to be displayed (using showDataValue) but can't find any way to get at the actual data from the dataSet/row.
 
I know the data must be somewhere, but querying the available objects in the scripting methods come back with either empty data bindings or empty rows. The data has to be there somewhere, I mean the FIRST and LAST functions clearly operate on it. But where it is, and how to get it, alludes me.
 
Any help would be appreciated.
 
Using Birt Report Designer: 4.3.2

Find more posts tagged with

Comments

PaulCooper

We had the same problem but have solved this using 2 hidden lists (two 1 column tables will do too) that are grouped the same way placed above the crosstab. 1 for the left groupings and the other for the top groupings. The grouping data fields are put in the appropriate group and the data fields put in the details row. Code is added to the initialize script to set up variables: 1 each to hold current value (e.g. currentLeftLevel1) for each grouping and one for and left array (leftArray) and one for top array (topArray). 
In each of the hidden list's grouping data fields add code to the onCreate script to set the appropriate current value variable to "this.value" and a line to initialise the nested array: 
Assuming for 3 left grouping crosstab then for the left level 2: 
currentLeftLevel = this.value; 
leftArray[currentLeftLevel1][currentLeftLevel2] = []; 
In the the data field in the list's detail row add this code to the onCreate script: 
leftArray[currentLeftLevel1][currentLeftLevel2][currentLeftLevel3].push(this.value); 
If you do this correctly for both lists you will have two arrays with at the lowest level containing the values for the rows and columns. The data you then need for each cell in the crosstab is the intersection of the appropriate leftArray entry for the left groupings and the appropriate topArray entry for the top groupings for that cell. This can be found using the javascript Array function ".filter". 
If your grouping fields can be null or an empty string you should create a function to set these to a default value before using. 
I am travelling at the moment but I will try and provide an example rptdesign file as soon as I can. 
Paul

tripperm

Paul,
 
Thanks for your reply. I came up with another solution that is along the same lines. In my onFetch method I added global variables to the reportContext with a name that matched the left hand row groupings and top column groupings groupings. With this "key", I placed the row data for later retrieval. I was then able to look up the data I needed in a dynamic text field in the cross tab (had to delete the aggregation column out, then add a dynamic text field in its place). 
 
It seems to work, although I can't believe there isn't a better way to do this in a cross tab. Guess we work with what we have.
 
Thanks,
 
Tripper

PaulCooper

Tripper, 
I get how you have solved it and I am glad that you managed to find a way. It would have been far easier if they had provided more aggregate support in cubes or the ability to embed other elements such as tables inside crosstab cells in the first place but "c'est la vie". 
I have already started work on an example solution so I shall continue and provide as mentioned for others using this forum. If you get a chance it would good if you could do the same. 
Paul

PaulCooper

As mentioned (if a bit late) here is my example .rptdesign file showing the method I described above though in my data set there are texts that are duplicated between cells (FYI the text is day of month with consultant name) therefore I couldn't use an left array and a top array and then use Javascripts Array.prototype.filter to find those in combination. If I did then if you have two identical texts in cells that do not share the same row or column this method would cause the same text to appear in the other corners of the rectangle created by the true data points. Instead I used one array and had the hidden table populating it grouped by the three left groupings and then by the two top groupings. The table is then grouped by the text field itself to fully dedupicate the data.
 
The function "nvl3" (defined in the initialize event script) is to convert blank/null/empty string values in the grouping fields to "Not Recorded" as those values cannot be used in associative arrays in Javascript. (Not actually need for my data set but good practise).
 
Paul