If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Trouble with Counts

Started by farmcock, 29 Jan 2015 08:33:51 AM

Previous topic - Next topic

farmcock

I am trying to write a report for an Auto Insurer. The report shows detail claim payments by individual Claim Number on a Detail Report.
My problem comes in when I try to place counts on the Summary Page.
I am trying to count distinct claim number in several ways - # Claims Opened this month, # Claims Closed this Month, Total # of Claims, etc., I don't seem to be able to get the correct answer unless I include the claim number on the Summary Report! This defeats the point of having a Summary Page when you have to show the detail Claim Number.
Does anyone know how to get around this? I have tried everything my limited knowledge of Cognos will allow.

Thanks for your help!

MFGF

Quote from: farmcock on 29 Jan 2015 08:33:51 AM
I am trying to write a report for an Auto Insurer. The report shows detail claim payments by individual Claim Number on a Detail Report.
My problem comes in when I try to place counts on the Summary Page.
I am trying to count distinct claim number in several ways - # Claims Opened this month, # Claims Closed this Month, Total # of Claims, etc., I don't seem to be able to get the correct answer unless I include the claim number on the Summary Report! This defeats the point of having a Summary Page when you have to show the detail Claim Number.
Does anyone know how to get around this? I have tried everything my limited knowledge of Cognos will allow.

Thanks for your help!

Hi,

You haven't told us how you are calculating the counts on the summary page...

MF.
Meep!

farmcock

I'm using Data Items pulled in from the Query toolbox. For the "Opened" object for instance, I have a statement like: If([Open Date] between [ReportMonthBegin] and [ReportMonthEnd]) Then (1) Else (0). This would give a count of all claim numbers that were opened during the month. For those that were Closed it would be the same except that I would use [Closed Date], etc.

In my Summary report, I have a column for 'State' followed by 'District' followed by the Data Items, so I assume these would aggregate to the District Level on a single Row for each District within the State but no such luck so far. - unless I pull Claim Number into the Summary - then I get correct Totals - but I also get hundreds of detail rows.

    :-\                   
                       

MFGF

Quote from: farmcock on 29 Jan 2015 10:19:21 AM
I'm using Data Items pulled in from the Query toolbox. For the "Opened" object for instance, I have a statement like: If([Open Date] between [ReportMonthBegin] and [ReportMonthEnd]) Then (1) Else (0). This would give a count of all claim numbers that were opened during the month.

Hi,

Your assumption isn't quite correct. The expression you have will provide a count of all consolidated rows in the query which have a date within the specified range. That assumes you're referencing items in your expression via their (short) query names (eg [Open Date] rather than [Your Namespace].[Your Query Subject].[Open Date] from the package tree). It's all in the timing. In your case, there are multiple rows for different claims for each date in the data source but these are being consolidated into a consolidated row for each Open Date before your calculation is performed. If you reference items from the query, the calculation is performed after the default grouping and aggregation has been applied. If you reference items from the package tree, the calculation is performed before the default grouping and aggregation is applied. Try substituting the items with their equivalent items from the package - does this give you the correct results?

MF.
Meep!