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
