Summerise List in Impromptu Header

[toray]

To summerise Totals using Impromptu I have found a method of moving the data into the report header and supressing the body of the report. However, I have come across a problem building another report in this manner.Firstly I built a list report with totals of a grouped category. Next I moved one of the totals up into the header and re-associate this total to the grouped category, which worked fine.I then continued to move the rest of the totals using this same procedure and found that the only total that could be seen in the header was the first total I moved, and the rest could not be seen.I moved then back into the body and again the totals are visible.Any comments will be greatly appreciated.

 

[griffindm]

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!

 

[nagrajm]

Dave is absolutely right. Following his note, an example of a "Summary Only" report would be;

YEAR ------>GROUPED DATA ITEM
REGION ------>GROUPED DATA ITEM
COUNT(ORDER NO) ------>SUMMARY FUNCTION
TOTAL(SALE AMOUNT) ------>SUMMARY FUNCTION

Make sure

1) there are no un-grouped data items in the report
2) the only other element in your report is one or more summary functions.

Nagraj