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 report with string as measure and grand totals

mactuate · 2015-05-09T22:11:41+00:00

Hi Everyone, In my application, i have to build a crosstab report for the given sample data. Name Issues_count duration_in_mins date xyz 30 450 2015-05-01 abc 64 900 2015-05-01 lmn 22 200 2015-05-02 xyz 25 150 2015-05-02 Whereas…

mactuate

Hi Everyone,
 
 In my application, i have to build a crosstab report for the given sample data.
 
 Name Issues_count duration_in_mins date

xyz 30 450 2015-05-01
 abc 64 900 2015-05-01
 lmn 22 200 2015-05-02
 xyz 25 150 2015-05-02
 
 Whereas the cross tab report measure should be the combination of issues_count and duration_in_mins as string like [ count / hh:mm] format. 
 
Expected crosstab report is like
 
 Name 2015-05-01 2015-05-02 Grand Total

xyz 30 ( 07:30) 25 (02:30) 45 (10:00)
 abc 64 ( 15:00) 0 (00:00) 64 (00:00)
 lmn 22 ( 03:20) 0 (00:00) 22 (03:20)
 
Please help me in solving this report. Thanks in advance.

Find more posts tagged with

Comments

mwilliams

If your data is all like this (meaning each row of your data set corresponds to its own cell in the crosstab matrix, you could create a computed column that gave you the string value, then you'd simply use the "first" aggregation for the measure in the cube. Now, the grand total column would be a little tougher. You'd have to use persistent global variables or something named by the row dimension field and keep track of the proper values yourself since you won't be able to do calculations across strings. OR, you could do these calculations in the data set script, store them in PGVs, named by the name field, and then recall them in the grand total column based on the name dimension. If you provide your BIRT version, I could make you a simple example using your data above. Let me know.

mactuate

Thanks Michael, i'm using BIRTversion 4.4.2

mwilliams

Okay. Take a look at this example. I used a scripted data set to use the data you posted in the first post, so there isn't a computed column option since this is all done in script. However, if you look in the "fetch" script of the data set, I've marked off the script that you'd use to create your computed column. There is also script in the onFetch script that you'll use to calculate the totals for each group. In the data cube, you'll see we used the computed column with "FIRST" selected as the aggregation. Make sure it's type string before you create your crosstab. Once you've created your crosstab, add the grand total column and delete the data element that's created. Drag a new data element from the palette into the grand total cell, select type STRING, and use the expression like I did in this example. That should get you what you're wanting. The computed column difference with not using a scripted data set should be the only change you'd need to make. Hope this helps. Let me know if you have questions.

mactuate

Great! Thank you very much Michael, this helps me a lot. How to handle if there is an additional dimension say "Project" and uniqueness is on (Name, Project)
 
 Name Project Issues_count duration_in_mins date

xyz p1 30 450 2015-05-01
 xyz p2 65 540 2015-05-01
 abc p2 64 900 2015-05-01
 lmn p1 22 200 2015-05-02
 xyz p1 25 150 2015-05-02

mwilliams

You'd name your PGVs with the project name as part of the name rather than just the Name field.

mwilliams

There's also a possibility of doing the calculations in the crosstab. I'll set something up with your new data in the morning and try it out to be sure it'll work. Either way, what I said in my previous post will definitely work if you know the dimensions used.