I've been trying to create a crosstab with custom intervals and summing some of them.<br />
<br />
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:<br />
<br />
less than 199<br />
200 to 350<br />
351 to 600<br />
601 to 1500<br />
1501 to 3500<br />
more than 3501<br />
<br />
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).<br />
<br />
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:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>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)</pre>
<br />
<br />
<span style='font-size: 14px;'>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)?</span><br />
<br />
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.<br />
<br />
<br />
<br />
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.<br />
<br />
Thanks for trying to look into this!
