On the fly Case Statements in Web Intelligence Reports

Problem: Due to having a little flaw in the data (in database) duplicate rows are being produced in (WebI) reports.

(1) Solution: Use CASE statements


> 122 Unique WorkItem subtypes

One of the the WorkItem SubTypes has a duplicate

'Problematic Payment - Overage '
'Problematic Payment - Overage'

One with and an extra space.

In the WebI Report - it is the following Dimension: (click view SQL)

WORKITEM.WORKITEMSUBTYPE, (here WORKITEMSUBTYPE is the Dimension - i.e. Column)

RESOLUTION: Go to

CASE WHEN WORKITEM.WORKITEMSUBTYPE NOT IN ('Problematic Payment - Overage ' ,'Problematic Payment - Overage')
> anything other than the two types above

THEN WORKITEM.WORKITEMSUBTYPE
> use the original value from database

ELSE 'Problematic Payment - Overage'
> otherwise, use 'Problematic Payment - Overage' - meaning, combine/sum the corresponding values of those two cells into one cell

END WORKITEMSUBTYPE,
> finish this (Case statement) logic and name this column as WORKITEMTYPE

-- WORKITEM.WORKITEMSUBTYPE
> I commented out the existing Dimension

This way, my column name is Identical to the Dimension name and thus the report runs fine and groups those two types into a single cell and eliminates the duplicate row.

Note: when you hit 'validate' - make sure, it prompts you with new LOVs (lis of values); else, it doesn't gets rid of the prompts. I've seen the exact same behavior in Crystal Reports too.

2nd solution: though this is a temporary fix, the same exact Case needs to be applied to our Universe so that it the duplicate problem will be eliminated in all WebI reports.

3rd / best solution: request DBAs so they permanently cleanse this data

On the fly Case Statements in Web Intelligence Report: Image Walkthrough

1) Duplicate row located



2) Go to Edit Query > SQL (view SQL) : Locate the corresponding dimension / column



3) Insert your Case statement and comment out the dimension



4) Validate




5) Make sure, it prompts you with new LOVs (list of values); else, it gets rid of the prompts (and runs off of cached LOVs, I think). I've seen the exact same behavior in Crystal Reports too.




6) Verify validation



7) Duplicate row is summed to original row


8) Before and after shots