Filtering crosstab measures

[dpetersen]

Hi all,

I'm running into an issue with filtering a crosstab and hoping someone can provide some guidance.  My crosstab looks like the below:

                              Fraud                                Genuine
                   Count          Amount          Count          Amount        False/Positive
Rule 1             5              $100                6                 $95               1.200
Rule 2             4              $545                9                 $422              2.250
Rule 3             2              $687                45               $258              22.500
Rule 4             6              $11                 20                $147              3.333

The False/Positive is a calculated field (count of genuine/count of fraud).  I want to filter the crosstab to only show rules (rows) with a False/Positive above 2.  I've added a detail filter to my query False/Positive > 2 and set the application to "After Auto Aggregation".  When I run the report with this filter, I get "No Data Available".

Am I applying this filter incorrectly?  This is using a relational database.

[MFGF]

Hi all,

I'm running into an issue with filtering a crosstab and hoping someone can provide some guidance.  My crosstab looks like the below:

                              Fraud                                Genuine
                   Count          Amount          Count          Amount        False/Positive
Rule 1             5              $100                6                 $95               1.200
Rule 2             4              $545                9                 $422              2.250
Rule 3             2              $687                45               $258              22.500
Rule 4             6              $11                 20                $147              3.333

The False/Positive is a calculated field (count of genuine/count of fraud).  I want to filter the crosstab to only show rules (rows) with a False/Positive above 2.  I've added a detail filter to my query False/Positive > 2 and set the application to "After Auto Aggregation".  When I run the report with this filter, I get "No Data Available".

Am I applying this filter incorrectly?  This is using a relational database.

Hi,

Can you share the calculation expression you used for your False/Positive calculated item? Assuming Count is a single item in your source, how are you isolating the count for False vs the count for Positive? Are there separate calculations for these (and if so, can you share the expressions)?

Cheers!

MF.

[dpetersen]

Sure.  I have a field in my dataset that indicates whether a record is Valid or Fraud.  Then I've created two calculated fields for "Valid Transaction Count" and "Fraud Transaction Count" with the following case expressions:

Valid Transaction Count
Case
When [Fraud/Genuine Indicator = 'Valid']
Then 1 else 0
END

Fraud Transaction Count
CASE
WHEN [Fraud/Valid Indicator] = 'Valid'
THEN 1 else 0
END

These query calculations have their detail aggregation set to "Count", though I get the same result if I use "Total" as well.

The False/Positive calculation is total([Valid Transaction Count])/total([Fraud Transaction Count]).  Or just [Valid Transaction Count]/[Fraud Transaction Count] if the detail aggregation on the counts is set to "Total".

The report generates as expected without a filter...all counts and the False/Positive are correct.  Only after adding the filter does it produce no data.

[cognostechie]

Hi

Count is a semi-additive measure and using the Case statement by adding 1, a semi-additive measure can produce unpredictable results (wrong at certain times and correct at certain times).

Use something like this:

Case
  When [Fraud/Genuine Indicator] = 'Valid'
  Then [Rule1]
End

Set the aggregation property to count distinct or count depending on how you want to count the duplicates.

[dpetersen]

Thank you for the advice.  I made the recommended change to the counts and adding the filter still results in No Data Available.

I did notice as I've been trying to troubleshoot this, when I add the Detail Filter and try to view the Generated SQL I get the message: "The SQL is not available for this query. Errors may have occurred.  To view the errors, click the Validate button in the Report Overview."

Of course when I validate the report I don't get any errors, and once I remove the filter I'm able to view the generated SQL.

I can add a filter with Rule ID for example and still view the Generated SQL.

Is this normal behavior?  I haven't experienced it before.

[cognostechie]

Hi

I don't really know if your package is dimensional or relational and whether Rule1, Rule2 etc. as well as Fraud/ Genuine are what type of fields/data items so I will provide a scenario which might explain to you how counts (semi additive measures) are calculated correctly.

Let's say you want to count Customers for a certain condition (and not count if that condition is not met) so you would normally use this in a data item:

count ( distinct Case When <condition> = <whatever> Then [Customer_ID] End )
Set the aggregation property to 'calculated'

In the recent versions of Framework Manager and Report Studio they have deprecated this so we have to use:

Case When <condition> = <whatever> Then [Customer_ID] End
Set the aggregation property to 'count distinct'

If Rule1, Rule2 is one data item/field then try a simple count with no conditions:

count ( distinct [data item] ) - The data item is the one whose values are shown in Rows (Rule1, Rule2 etc.)
set the aggregation property to 'calculated'

Cognos will automatically regenerate the counts depending on what you put in the rows and columns so you
don't have to specify the counts separately for Fraud and Valid.

If you want to see only those rows where the count is greater than 2 then create a summary filter because the
query has to calculate the count first to determine whose count is more than 2. It's like ranking something.

Cognos does not necessarily use the SQL or the expression you write and it rewrites the SQL/MDX depending on the layout of the report.