Calculations in Master Detail Report

[dpccgf]

I have 10 exception reports with varying degrees of filters and calculations to highlight a given exception on a given ID and those exceptions are reported (one per ID).  I have a summary report that takes data from each of these IDs and populates cells in a row with these exceptions using master detail relationships from the Summary to the Exception reports.  I have removed (hidden or cut) fields in the detailed reports so that in the Summary Report only the except data shows.

Each ID will have 0-10 exceptions and I have to figure out how to calculate the total number of exceptions in the Summary report.  Example, if ID = 1 and that ID is on 3 exception reports the data will have 7 empty exception cells and 3 populated exceptions via Master Detail.  Something like a fact table surrounded by exception tables using outer joins would be perfect, but I do not have that.

How can I count the number of exceptions coming from the "detail" exception reports into the Master?  It would be something like "If detail contains exception, then put a number 1 in the master exception" then total this field by Report ID.

For the life of me cannot figure it out!

[dougp]

I'm getting hung up on the term "exception".  I don't know what this is in SQL or Cognos.  Maybe this will help.

See the picture.  Are you asking something like, "How do I get the count of invoices per customer into the yellow box?"

[dpccgf]

Exceptions may not have been correct.  Let's call them Audit Flags.  So on Detail Report one I have a report that contains Accounts with only one type of Audit Flag (Report 1).  On Report 2 I have another type of Audit flag that contains Accounts if they meet this Audit Flag.  And so forth through 10 Audit reports in different lists as they all have specific audit guidelines.  So some accounts that I pick based on a date are either on the Audit Detail or not.  So an Account might have no audit flags at all which means they have 0 flags.  Others might have anywhere between 1-10.  On a "Master Report" I have all accounts and the flag show on the row of the account from the detail using a Master-Detail relationship.  What I need to do is now have another column to COUNT how many of these flags from the detail there are.  Anywhere from 0-10.  So if I could just count how many times I see a result (flag) from the Detail report that would work. 

[dougp]

I'll take that as a "No."  Can you provide an example of your expected output in a picture or PDF?

[dpccgf]

Attached is an example.  Basically the list report is built off of the Master (Account and actually other data) then the flags built off of Detail lists via a Master/Detail.  Each field from the detail is the actual flag "data" itself and then has a conditional style to highlight the List Cell from the detailed report in which an audit exception occurs .  So each Account has an associated 10 flags from 10 different Flag reports. 

So I am trying to summarize the number of flags that meet the "exception" rule, in the example all that are highlighted orange.