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

Top-N Subtotals

tejusdas

<p>Hi,</p>
<p> </p>
<p>How can I filter to show only the Top-N group subtotals within a Table that bound to a detail data set?</p>
<p> </p>
<p>Please see the attached example - I want to show order totals only for the top 3 cities.</p>
<p> </p>
<p> </p>
<p>Note:</p>
<p>Unfortunately, I do not have the flexibility to change the data-set definition to evaluate subtotals. The binding on the table is to an Actuate Data model, bound to a data-set sourced from an Excel / CSV data-source.</p>
<p> </p>
<p>Thanks,</p>
<p>Tejus</p>

Find more posts tagged with

Comments

micajblock

<p>Edit the group, there is a filter tab there.</p>

tejusdas

<p>Ah! I hadn't looked there... That worked!</p>
<p> </p>
<p>Thanks for the quick response, Mica.</p>
<p> </p>
<p>How would I sort the result, though? When I look at the sorting options within "Edit Group", I do not see the aggregations in the choices.</p>

JFreeman

<p>Instead of just selecting from the drop down, open the expression builder by selecting the Fx button. Then you can select from the available column bindings within the table which will include your aggregations.</p>

JFreeman

<p>As a side note, you can also add the filter at the table level using the IsTopN aggregation you created. Create the filter on the table as row["IsTopN"] Equal to true.</p>
<p> </p>
<p>However, filtering at the group level will mean you can remove that aggregation from the report design.</p>

tejusdas

<blockquote class="ipsBlockquote" data-author="JFreeman" data-cid="133208" data-time="1422637726">
<div>
<p>Instead of just selecting from the drop down, open the expression builder by selecting the Fx button. Then you can select from the available column bindings within the table which will include your aggregations.</p>
</div>
</blockquote>
<p> </p>
<p>Thanks! I should've known that!</p>

tejusdas

<blockquote class="ipsBlockquote" data-author="JFreeman" data-cid="133209" data-time="1422637940">
<div>
<p>As a side note, you can also add the filter at the table level using the IsTopN aggregation you created. Create the filter on the table as row["IsTopN"] Equal to true.</p>
<p> </p>
<p>However, filtering at the group level will mean you can remove that aggregation from the report design.</p>
</div>
</blockquote>
<p> </p>
<p>That aggregation was left in there from a previous attempt at solving the problem. The issue, was that, when I apply a table level filter on the IsTopN aggregation within my report, I get the following exception:</p>
<p> </p>
<div>eclipse.buildId=M20130204-1200</div>
<div>java.version=1.7.0_67</div>
<div>java.vendor=Oracle Corporation</div>
<div>BootLoader constants: OS=win32, ARCH=x86_64, WS=win32, NL=en_US</div>
<div>Command-line arguments: -os win32 -ws win32 -arch x86_64</div>
<div> </div>
<div>Error</div>
<div>Fri Jan 30 12:17:28 EST 2015</div>
<div>The query filter refers to aggregation is not supported. com.actuate.birt.data.linkeddatamodel.LinkedDataModelException: The query filter refers to aggregation is not supported. org.eclipse.birt.report.engine.api.EngineException: The query filter refers to aggregation is not supported.</div>
<div>at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1245)</div>
<div>at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1224)</div>
<div>at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:96)</div>
<div>at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:62)</div>
<div>at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46)</div>
<div>at org.eclipse.birt.report.engine.internal.executor.emitter.ReportItemEmitterExecutor.execute(ReportItemEmitterExecutor.java:46)</div>
<div>at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43)</div>
<div>at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46)</div>
<div>at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:34)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:140)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.resumeLayout(HTMLInlineStackingLM.java:111)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.layoutNodes(HTMLInlineStackingLM.java:160)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:140)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:70)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLRepeatHeaderLM.layoutChildren(HTMLRepeatHeaderLM.java:46)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:140)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:70)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:140)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.resumeLayout(HTMLInlineStackingLM.java:111)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLInlineStackingLM.layoutNodes(HTMLInlineStackingLM.java:160)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:140)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:70)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLRepeatHeaderLM.layoutChildren(HTMLRepeatHeaderLM.java:46)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:140)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:70)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:92)</div>
<div>at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:100)</div>
<div>at org.eclipse.birt.report.engine.presentation.ReportDocumentBuilder.build(ReportDocumentBuilder.java:249)</div>
<div>at org.eclipse.birt.report.engine.api.impl.RunTask.doRun(RunTask.java:272)</div>
<div>at org.eclipse.birt.report.engine.api.impl.RunTask.run(RunTask.java:115)</div>
<div>at com.actuate.reportapi.engine.birt.ReportGenerationTask.runTask(ReportGenerationTask.java:1094)</div>
<div>at com.actuate.reportapi.engine.birt.ReportGenerationTask.generateReport(ReportGenerationTask.java:206)</div>
<div>at com.actuate.reportapi.engine.ReportGenerationTaskBase.doTask(ReportGenerationTaskBase.java:157)</div>
<div>at com.actuate.reportapi.engine.Task.execute(Task.java:318)</div>
<div>at com.actuate.reportapi.enginemanager.ThreadPool$ControlRunnable.run(ThreadPool.java:808)</div>
<div>at java.lang.Thread.run(Unknown Source)</div>
<div>Caused by: com.actuate.birt.data.linkeddatamodel.LinkedDataModelException: The query filter refers to aggregation is not supported.</div>
<div>at com.actuate.birt.data.linkeddatamodel.impl.TabularQueryExecutor.populateFilters(TabularQueryExecutor.java:572)</div>
<div>at com.actuate.birt.data.linkeddatamodel.impl.TabularQueryExecutor.execute(TabularQueryExecutor.java:123)</div>
<div>at com.actuate.birt.data.linkeddatamodel.api.LinkedDataModelQueryExecutor.executeFlatQuery(LinkedDataModelQueryExecutor.java:59)</div>
<div>at com.actuate.birt.data.linkeddatamodel.service.tabular.query.QueryResults.getResultIterator(QueryResults.java:247)</div>
<div>at org.eclipse.birt.report.engine.data.dte.QueryResultSet.<init>(QueryResultSet.java:98)</div>
<div>at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:168)</div>
<div>at org.eclipse.birt.report.engine.data.dte.DataGenerationEngine.doExecuteQuery(DataGenerationEngine.java:83)</div>
<div>at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:275)</div>
<div>at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1947)</div>
<div>at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80)</div>
<div>... 40 more</div>
<div> </div>
<div>java.lang.Exception</div>
<div>at java.util.logging.Logger.log(Unknown Source)</div>
<div>at java.util.logging.Logger.doLog(Unknown Source)</div>
<div>at java.util.logging.Logger.log(Unknown Source)</div>
<div>at java.util.logging.Logger.severe(Unknown Source)</div>
<div>at com.actuate.reportapi.engine.birt.BirtUtil.logBirtEngineError(BirtUtil.java:938)</div>
<div>at com.actuate.reportapi.engine.birt.BirtUtil.checkTaskError(BirtUtil.java:963)</div>
<div>at com.actuate.reportapi.engine.birt.ReportGenerationTask.runTask(ReportGenerationTask.java:1119)</div>
<div>at com.actuate.reportapi.engine.birt.ReportGenerationTask.generateReport(ReportGenerationTask.java:206)</div>
<div>at com.actuate.reportapi.engine.ReportGenerationTaskBase.doTask(ReportGenerationTaskBase.java:157)</div>
<div>at com.actuate.reportapi.engine.Task.execute(Task.java:318)</div>
<div>at com.actuate.reportapi.enginemanager.ThreadPool$ControlRunnable.run(ThreadPool.java:808)</div>
<div>at java.lang.Thread.run(Unknown Source)</div>
<div> </div>
<div> </div>
<div>The filter at the group level works just fine though.</div>

JFreeman

<p>That is interesting as, when I apply the filter based on your IsTopN aggregation at the table level using your first example, I do not get an error and the filter applies properly.</p>
<p> </p>
<p>I think filtering at the group level is going to be the better solution anyways though.</p>

tejusdas

<blockquote class="ipsBlockquote" data-author="JFreeman" data-cid="133213" data-time="1422639522">
<div>
<p>That is interesting as, when I apply the filter based on your IsTopN aggregation at the table level using your first example, I do not get an error and the filter applies properly.</p>
<p> </p>
<p>I think filtering at the group level is going to be the better solution anyways though.</p>
</div>
</blockquote>
<p> </p>
<p>You are right. The sample report works fine.</p>
<p> </p>
<p>The exception comes up when I use the Table Filter on the actual official report that I am building, in which the table has a binding to an Actuate Data Model object.</p>

JFreeman

<p>That makes more sense, I should have caught it was a data model from the stack trace. Data models tend to behave different that a standard data set.</p>
<p> </p>
<p>Therefore, it looks like Mica's suggestion of filtering at the group level is going to be your solution when working with a data model.</p>
<p> </p>
<p>Let us know if you have more questions.</p>