Crosstab with custom group intervals and aggregation

Crosstab with custom group intervals and aggregation Rate Topic:

#1 jarda-wien

Newbie

Group: Members
Posts: 3
Joined: 25-February 12

Posted 19 March 2012 - 10:58 AM

I've been trying to create a crosstab with custom intervals and summing some of them.

I am querying a database for products and their packaging weight (PWEIGHT) values. I need to count the numbers of products in each group and also a total and a subtotal count. The group intervals are not linear. They're something like:

less than 199
200 to 350
351 to 600
601 to 1500
1501 to 3500
more than 3501

First I tried to specify the individual groups in a static group definition. The order of the groups was wrong, but I found out that I can specify a group level attribute (PWEIGHT) and order on that field. Next I needed for empty intervals to show in my report as well. If there were no products in the 200-350 cathegory in the data set, the group just wouldn't show up. After some digging on the web, I found the solution to this as well (secondary data source with all the groups and a joint data set).

Now for the totals. Adding a grand total is easy. What about adding a total of all packaging weighing more than 351? I tried to create a "dummy" static group and aggregate on it. That worked fine as well, but I cannot seem to remove the group from the crosstab. I don't want to actually see the group, I only want to aggregate on it. If I remove the group and leave the bindings I get an error saying:

ReportDesign (id = 1):
+ An exception occurred during processing. Please see the following message for details:
The binding "dummy_PWEIGHT" refers to invalid levels. (Element ID:1)
+ Can not find the prepared query org.eclipse.birt.data.engine.olap.impl.query.CubeQueryDefinition@7f6cebb. (Element ID:1)

The question is how do I calculate a total of a group that is not actually in the crosstab (it is in the cube though)?

Also, I am starting to think that maybe instead of all of this overhead (multiple join data sets, group level attributes and dummy groups), there may be a more streamlined way of doing this.

I have attached a simplified classic models example using a static crosstab group where only 2 of the 3 groups show up. The order of the groups is wrong as well.

Thanks for trying to look into this!