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 combine dynamic and static grouping

KaiWeing

Hello,

I want to create a report that compares planned sales and acutal sales for all quarters of the year.
The much simplified but sufficient data is attached.

The table should have a dynamic grouping by year at the top, with a sub grouping of the quarters. However, the grouping to the left must be static, consisting of the sums of planned and acutal sales. Below that I need to calculate the delta between plan and actual.

In Ascii-Art this would look like this (the spaces are messed up, but you get the idea):
| 2009 | 2010 | 2011 |
| 1 | 2 | 3 | 4 | 1 | 2 | ...

Plan |
Actual|
Delta | [Plan]-[Acutal]

I'm not sure how to do this, do I need a regular tab or a cross tab for this?
A design example would be very helpful!

Thanks in advance!

Kai

Find more posts tagged with

Comments

mwilliams

Hi Kai,

What version of BIRT are you using?

KaiWeing

<blockquote class='ipsBlockquote' data-author="mwilliams">What version of BIRT are you using?</blockquote>
 
I've been using a current trial of the BIRT Report Studio, but the solution should also (or rather) work with the current Eclipse Designer. 
I am not restricted to any version (library or desiner wise) if that's what you mean. 
 
Kai

mwilliams

Kai,

A crosstab definitely looks like the way to go here. I've got a way to figure the difference between the Plan and the Actual, but it seems to only work when there is actually a number for both Plan and Actual for a given quarter. In your sample data, the last several quarters didn't have a value for Actual and the way I did it didn't work. I haven't figured that reason out yet. Will you always have an actual and a plan value?

KaiWeing

<blockquote class='ipsBlockquote' data-author="mwilliams">A crosstab definitely looks like the way to go here. I've got a way to figure the difference between the Plan and the Actual, but it seems to only work when there is actually a number for both Plan and Actual for a given quarter. In your sample data, the last several quarters didn't have a value for Actual and the way I did it didn't work. I haven't figured that reason out yet. Will you always have an actual and a plan value?</blockquote>
 
 
First, thank you for your effort! 
 
As I need to be able to plan into the future, I won't always have actual data I'm affraid. But I could cope with some default text or value if that makes it easier (what good is a delta of one number anyway)? 
 
When you are out of ideas, I'd like to see your try, perhaps I can work from there. 
 
Kai

mwilliams

Kai,

So, do you still want the delta for the days in the future that won't have actual data? Or would displaying something like "N/A" or something like that work?

KaiWeing

<blockquote class='ipsBlockquote' data-author="mwilliams">So, do you still want the delta for the days in the future that won't have actual data? Or would displaying something like "N/A" or something like that work?</blockquote>
 
Displaying a default text would be an option if it is easier to produce. 
 
Kai

mwilliams

Kai,

Nevermind. It was a simple fix when I thought about it. Here's the report design I used. I used your sample data as the datasource. The way I got the extra line was by using the total option and deleting the items and replacing them with my own. Let me know if you have any questions.

KaiWeing

That's what I was looking for. Thank's a lot!

I don't quite understand the formula though (I'm new to that). Did you set that persistent global variable anywhere?

Where can I find a description of the API you are using (reportContext, parseInt, ...)? What data is available when writing such formula?

Thanks again, Kai

mwilliams

Kai, 
 
I set the pGVs in the onCreate script of the "Amount" crosstab element with the following code: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if(dimension["Group1"]["Type"] == "Plan"){
if(measure["Amount"] == null){
reportContext.setPersistentGlobalVariable("Plan" + dimension["Group"]["year"] + dimension["Group"]["quarter"], "0");
}
else{
reportContext.setPersistentGlobalVariable("Plan" + dimension["Group"]["year"] + dimension["Group"]["quarter"], measure["Amount"].toString());
}
}
if(dimension["Group1"]["Type"] == "Actual"){
if(measure["Amount"] == null){
reportContext.setPersistentGlobalVariable("Actual" + dimension["Group"]["year"] + dimension["Group"]["quarter"], "0");
}
else{
reportContext.setPersistentGlobalVariable("Actual" + dimension["Group"]["year"] + dimension["Group"]["quarter"], measure["Amount"].toString());
}
}
</pre>
 
You can view the API documentation <a class='bbc_url' href='http://www.birt-exchange.com/modules/documentation/birt-report-designers.php#currentdocs'>here</a>.

elvis_tu

Hi, Michael:

How could I insert a new row such as Delta row into the crosstab?

And could I insert a new (static) column into the crosstab?

Thanks a lot

mwilliams

Hi elvis_tu,

I used the derived measure option to add this delta column. What do you mean by adding a static column to a crosstab?

Saikanth

Tip: click inside this box to load the editor

Saikanth

Hey how can i access the group that i have created using script

mwilliams

Can you explain further?

Saikanth

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="83380" data-time="1317135015" data-date="27 September 2011 - 07:50 AM">
Can you explain further? </blockquote>
hi man 
 
i have resolved the issue. thank you for responding.