Basic Programming - How to count types of records in Impromptu 7.0 1

[turnerjs]

I have numerous records in 1 table. I have many different record types and each record may be 1 of two different status values - 'A' or 'B'. I want to print 1 line for each record type. I want that line to show the type and the total # of records for each type and the total # of status values found within each record type (grouped).

I can easily build this report if I show each record status value count on a separate print line below the grouped record type.

The user would like to see 1 line only - I have tried using a number of different CALCULATE fields definitions, but have not found the proper combination.

this is a sample of output required

RecType RecTypeDesc TotalRecs TotalStatusA TotalStatusB

I can get a total of either statusA or satusB per rectype, but not a total for both.

Thank you for help

 

[griffindm]

turnerjs,

The method to do this depends on the database type. In either case you would group only on the RecType, not on the status. The basic heuristic would be to create a total summary for a comparison of the contents of status.

For Oracle, you would use the Decode function. The TotalStatusA column would be defined as:

Total (Decode(Status,'A',1,0)) ...

This returns a 1 for rows with status of 'A' and zero for all others. The total function returns an effective count of Status = 'A'.

For other database types, use the If-then-else function. Rewritten this way, the above column would be:

Total (If (Status = 'A') then (1) else (0) ) ...

This will get you the counts for StatusA and StatusB on a single grouped line of RecType.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[turnerjs]

Dave, Thanks for the quick response. Sorry, I took so long getting back, I was trying another solution that was suggested prior to yours.

We are using oracle and the Decode function was what I needed. The report works fine.

Thanks again

Steve