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!
Number of downloads: 4