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

Birt Crosstabs

anir

Hi,

I need to display a report for the last 12 months against vehicle types for the number of cars sold for that month.

I am able to do it using crosstabs as :

2008 | 2009
Oct Nov Dec | Jan Feb Mar Apr May Jun Jul Aug Sep
Car 1
Car 2
Car 3

Now I need to display below these reports a small grid, which will display the average number of cars sold in the last 3 months, last 6 months, last 9 months, and last 12 months.

Please help me how can this be done?

Thanks

Find more posts tagged with

Comments

mwilliams

Hi anir,

In the grid below the crosstab, you'll show the average numbers of each car for the last 3, 6, 9, and 12 months? Or the average number of cars total for each period?

anir

Hi Michael,

I need to display the average number of total cars for the period.

Thanks

mwilliams

anir,

Are you using "column totals" in your crosstab? That'd make it easier. The example I'm currently working on assumes you are not using a totals column in your crosstab.

mwilliams

anir,

Here is the example that is done without using the totals row in the crosstab. Let me know if you have questions. If you use the totals row, there is a little less script needed, but not much different.

anir

Hi Michael,

Can you please explain me the logic that you have used. The result for me comes to 0. I mean you haven't considered the count component in the average calculation. Can we chat sometime on gtalk?

Thanks

mwilliams

anir,

What version of BIRT are you using? I get the correct results when I run the report in 2.3.2.2. Also, what are you talking about when you talk about a count component? You answered my question about individual cars or total cars with an answer of total cars for the last 3/6/9/12 months.

What was done in the report was I set a global variable for each month 1-12 since you're doing the report for 12 months. I also set a variable called "firstMonth" to the value of the first crosstab column encountered for computing the last 3, last 6, etc. Then, on the measure in the crosstab, I called the value of the global variable corresponding with the month group and added the current value to it if it wasn't null and reset the variable. Finally, in displaying the values, I used the (first month + (monthValue - 1)) %12 to determine which global variable belonged to which group. i.e. If you started at Sept. and ended at August, the last 3 months (10, 11, and 12) would actually be global variables 6, 7, and 8. So (9+10-1)%12 = 6 and so on. You add these global variables together for the groupings and then divide them by the appropriate value to get the average. I hope this helps with understanding what I did. If not, I have yahoo chat. Let me know what version of BIRT you're using so I can see if this logic doesn't work in it for me either.

anir

Hi Michael,

Apologies for replying late. I am using, 2.2.2 . Please explain me the steps that you performed for adding the javascripts on the "onCreate" method of the measure? I mean please tell me the steps that you actually performed to add the javascript to teh measure?

Thanks

mwilliams

anir,

You can see the script in the XML source if you aren't able to open the report design with your older version of BIRT. I can create an example in BIRT 2.2.2 as well, if you aren't able to get it.

anir

Hi Michael,

Did this first thing in the morning and got it working. Thanks so much for your help. Hope to learn more in this forum

Please provide me pointers to some online basic BIRT scripting tutorials if possible.

Thanks

mwilliams

anir, 
 
Here are some items that may help: 
 
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/975-webinar-archive-birt-scripting-and-events/#description'>Webinar Archive: BIRT Scripting and Events - Tutorials - BIRT Exchange</a> 
 
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/978-webinar-archive-using-the-birt-design-engine-apis/#description'>Webinar Archive: Using the BIRT Design Engine APIs - Tutorials - BIRT Exchange</a> 
 
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/686-birt-scripting-examples-from-wjax/#description'>BIRT Scripting Examples from WJAX - Designs & Code - BIRT Exchange</a> 
 
<a class='bbc_url' href='http://www.birt-exchange.org/modules/documentation/#currentdocs'>All Products - Documentation - BIRT Exchange</a>

anir

Hi Michael,

Thank you for the links.

Continuing with the same problem statement, i now have a requirement of displaying multiple crosstabs in the report , say one for the Spare parts sold, and another one for the Accessories sold. Now what i did was I brought up all the data in the data cube (all data related to sales of Cars, Spare parts and Accessories). Now i created 3 cross tabs in my .rptdesign file and put that rptdesign file inside a frame in my jsp. And then i put a drop down in the jsp which displays the particular sales figure i want to see in the report (i.e., Cars, Spare Parts and Accessories). Now suppose when i select Cars, i hide the other two crosstabs. This way i am able to achieve what is required, but since (i think) all the cross tabs are getting rendered and then being hidden, so the entire report takes a long time to render. Now in order to remediate this I can take two approaches: one is to make different rptdesign files for each report (which also means making the datacubes again). And the second approach (i feel better one) can be using some javascript event handlers like "beforeRender" or "onCreate" to check the condition about the report type selected before the report gets rendered, which will cut the time for the report to be displayed to user.

Please let me know yuor thoughts and if possible provide me hints as to how to write the script.

P.S: I am facing another problem, that is for this particular .rptdesign file, all the drop downs in the script view are coming as disabled, no matter what report element i select in the outline view of eclipse. Have you faced something like this ?

Thanks

mwilliams

anir,

If you have a parameter that tells you which crosstab the user wants to see, you could drop the other crosstabs in the beforeFactory event. This would cut down the time to render your report.

anir

Yes, that is infact the case Michael. I have a report parameter based on which i want to display the crosstabs. 
 
Tried working with the beforeFactory event, unable to retrieve the crosstabs and drop it. 
 
 
P.S: The problem of the script view coming as disabled was solved by removind a .rptconfig files which got created automatically. Can you also explain a bit on this. 
 
Many thanks

Update: Was able to retrieve and drop the crosstabs, still the performance is not upto the mark. Its used to take 2minutes, now its taking 1 minute to load, but the stored proc brings the data in just 2-3 secs. Where can be the bottleneck? 
 
Thanks

anir

Hi Michael,

Going through various links that you had provided to someone else in the forum (performance ppt) I understand that generating totals may take up some time. But other than that what can be the reason for the reports taking that long? Is using the report context another reason? Can we avoid this in some way? We are using this to calculate the quaterly averages.

You had suggested using the quarterly average feature present in crosstabs. Can you let me know the steps to do such average calculation in crosstab?

Thanks

mwilliams

anir,

I had suggested that if you used the total row for the month, you could just set each month total global variable once rather than adding each "car's" total yourself. You do this by clicking on the icon next to the row dimension and choose the totals option. You then choose the total that shows as a row at the bottom of the crosstab. Then, you would delete the script where we add up the other values and just use this new total value to set each month's PGV. Hope this makes sense. Let me know if you have questions.

anir

Do you think this can reduce the time taken by the crosstabs to load?

Thanks

mwilliams

anir,

It may take some time off, but it may not be a significant amount. Testing it would be the only way to find out for sure.

anir

Hi Michael,

Not able to get the script working. Please send me the script if possible.

Though my main pain point is now the time taken. In order to reduce the time, can we switch to newer Birt versions? Will it remain compatible with the reports that we created using 2.2.2? Or else it will mean a lot of regression testing for us for which we may not have the bandwith immediately.

Thanks

mwilliams

anir,

Here is the report design modified to use the totals row. Again, this report is from 2.3.2, so you'll just have to look at the XML Source.

As for increasing speed by upgrading to a newer version of BIRT, I don't think you'll see much increase in any by doing this. The older version reports should open in the newer version of BIRT with no issues for the most part. There may be some changes you'll see that will need to be made, but nothing drastic that I can think of. To migrate back in versions, you'd have to change the version info in the XML Source to the designer version to open the report. If new features from newer versions are used in the report, you'll have issues, otherwise, most reports will work going backwards.

anir

Hi Michael,

Again a big thanks for the script. It's working fine. But the issue with the time taken by the report is still there. Its taking almost the same time. Can you please tell me :

1.) the approach to take to debug through the report to find exactly where is the time taken. I am using 2.2.2 as designer and 2.2.0 as the viewer.

2.) how to implement caching in the reports? Say I am able to bring all the data once in the datacube, i want to cache it so that for that run, when i change the report type, the reports dont get refreshed and re-run and just show me the data from the cache.

3.) Or if you can suggest me the steps to take to reduce the time taken.

P.S: I also tried to work on the 2.5.0 report designer. Looks better, but its giving many errors while displaying the preview of the reports and hence I am a bit skeptical to take such a big step

Thanks

Update : I was able to halve the time by deploying it to my server. Now its taking 20 secs

. Is this time ok?? Or should I aim to achive a lesser turnaround. (I know this is difficult question to answer but still would love to hear your perspective).

anir

Hi Michael,

One more thing I would be delighted to grab on is the list of features that birt 2.5.0 provides. We are currently on 2.2.0 deployment and would love to move into something newer. So my questions will be :

1.) Shall we go for 2.5.0. Is it stable and does it provide ample support (asking this becuase the example reports that you sent me were made not on 2.5.0

)

2.) 2.2.0 to 2.5.0 - what will be the significant advantage?

As always many thanks for you kind and patient response,

Happy Weekend and Happy Dusshera (its a very auspicious festival in India

)

Thanks

mwilliams

anir, 
 
Glad the time is down to 20 seconds. As you said, answering whether that is a good time or not is a tough question to answer. It really depends on the amount of data that you're putting into the crosstab. I believe that the newer versions have better caching, so upgrading may help a little. What are the errors you're getting by trying to run the report in 2.5.0? 
 
Here are the New & Notable feature upgrades for 2.3 and 2.5 to show you what has changed since your 2.2 version: 
 
<a class='bbc_url' href='http://www.eclipse.org/birt/phoenix/project/notable2.3.php'>New and Notable Features within BIRT 2.3</a> 
<a class='bbc_url' href='http://www.eclipse.org/birt/phoenix/project/notable2.5.php'>New and Notable Features within BIRT 2.5</a> 
 
2.5.0 is a stable release. The reason I still make most of my examples in 2.3.2 is because 2.5.0 has only been out for 3 months, so many people have not upgraded yet. So, if you don't say a version and I don't think to ask for one before I make an example, I usually just pick 2.3.2. Hope this explains that.

Let me know if you have further questions. You have a great weekend and holiday as well!