toray,
If you only want summary data in your report, a better solution is to create a summary-only report. This is done by forcing a 'group by' in the SQL statement generated. To do this, you need to include only data elements that participate in the grouping, and elements that are aggregated using a database function. For example, select year, period, and project from a report table. Then select measures of hours or dollars, and use the Summary buttion in the middle of the Report | Data |Query screen to add the measures as aggregates. Then go to the grouping tab and make sure that the Auto-group box is checked. Lastly, go to the Profiles tab, check on SQL and verify that there is a 'group by' clause in the SQL.
The advantage of this approach is that the database is very efficient in performing these types of aggregations, and the number of rows returned to Impromtpu is very small, making the report extremely fast. The limitations are that every element must be in the grouping or an aggregate, and that only database supported functions can be used.
The method you have indicated actually causes a higher workload on Impromptu than just including the totals in a footer, as Impromptu does a two pass approach, called an XSUM, in order to present the aggregate data
before the presentation of the detail.
Regards,
Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!