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

How to change Xtab date dimension aggregation level dynamicaly

RCirne

Hello all,

I have a report with a XTab based on an existing Data Cube. This Data cube has a Group based on a datetime field. Nothing special there.

My requirement is to change the aggregation level for this dimension (year, month, week or day) based on an user input parameter. Any tips?

Thanks.

Find more posts tagged with

Comments

RCirne

I suspect the afterFactory event would be the place to put the change script, but I have no ideia how to get the cross tab handle at that point or how to change the aggregation level once I get it.

Does someone with more scripting experience on BIRT have a opinion about this?

Thanks

mwilliams

Hi RCirne,

What determines what level it needs to be at? A parameter?

RCirne

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="70851" data-time="1291133064" data-date="30 November 2010 - 09:04 AM">
Hi RCirne, 
 
What determines what level it needs to be at? A parameter? </blockquote>
 
Correct. The user would select start and end dates and the level which it would like to break down the data. All these would be parameters. 
 
Thanks for the reply!

mwilliams

What is your BIRT version? I'll try to come up with an example for you. At worst case, you can create a crosstab of each type and drop the ones that are not needed based on the parameter so they don't bog down your generation time.

RCirne

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="70972" data-time="1291317598" data-date="02 December 2010 - 12:19 PM">
What is your BIRT version? I'll try to come up with an example for you. At worst case, you can create a crosstab of each type and drop the ones that are not needed based on the parameter so they don't bog down your generation time. </blockquote>
 
Hi Michael, 
 
thanks for taking the time to work on this. I have been struggling with it for some time now and haven't been able to find a solution. 
 
I am using version 2.3.2, which is the one used by IBM's Maximo. I would appreciate any help with this issue, and some examples would be great. 
 
Thanks again.

mwilliams

This report should give you an idea on how to drop the ones you don't want based on a parameter.

RCirne

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="71060" data-time="1291669730" data-date="06 December 2010 - 02:08 PM">
This report should give you an idea on how to drop the ones you don't want based on a parameter. </blockquote>
 
Thanks a lot Michael! 
 
I used this approach and managed to get the desired effect. I have 12 crosstabs in the design but only show 3 at a time depending on the user input. 
 
I still have some issues with the crosstabs, though. I added a Grand Total column to them, but I also need to have an additional column with the row total percentage relative to the overall total. Any tips on how to do that? 
 
Finally, I have charts that display data from the same Data Cube as the crosstabs, but in the charts I don't get the date dimension formatting (for example day-of-year as Dec 7, 2010). Am I missing anything in the chart configuration? 
 
Thanks again.

mwilliams

You can add a grid to the grand totals column and make one of the grid columns for the total and one for the percent of total. If the crosstab grand total row value isn't available to you here, you may have to sum the values in your dataSet and store them in a variable to use in your calculations. I helped someone with something like this a while back, but I don't remember which approach I had to use.

As for the chart, what format is the date coming in?

RCirne

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="71092" data-time="1291732866" data-date="07 December 2010 - 07:41 AM">
You can add a grid to the grand totals column and make one of the grid columns for the total and one for the percent of total. If the crosstab grand total row value isn't available to you here, you may have to sum the values in your dataSet and store them in a variable to use in your calculations. I helped someone with something like this a while back, but I don't remember which approach I had to use. </blockquote>
 
Hmmm, I'll try these and see if I can make it work. 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="71092" data-time="1291732866" data-date="07 December 2010 - 07:41 AM">
As for the chart, what format is the date coming in? </blockquote>
 
The chart uses the Data Cube, with the time dimension as category series. As default the type in the X-Axis is Linear. I tried changing it to DateTime but got an error ("Unable to use a non-datetime data to render a datetime axis"). Is this what you asked? 
 
For day-of-year level I am getting 340, 341, etc and I would like to get "December 6, 2010", "December 7, 2010", etc

mwilliams

So, you're getting just the integer value of the day of year. You could replace the label in script using the day of year value to figure out the date string that you can put in the label. Or, you could edit your dataSet adding a field of type string that formats the date how you'd like it in string form. Then, you can add that to your dataCube as a dimension and use that.

RCirne

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="71117" data-time="1291743609" data-date="07 December 2010 - 10:40 AM">
So, you're getting just the integer value of the day of year. You could replace the label in script using the day of year value to figure out the date string that you can put in the label. Or, you could edit your dataSet adding a field of type string that formats the date how you'd like it in string form. Then, you can add that to your dataCube as a dimension and use that. </blockquote>
 
Since I just got the report working like I need, I prefer not to mess with the dataSet, so I think I would prefer the former approach. I have created a javascript function to calculate the date from the day of year. In which point should I insert it?

mwilliams

In the before draw axis label function in your chart script, you'd grab the value, compute the date formatted how you'd like, convert it to a string, and then replace the label caption text with your new value.