On the fly Case Statements in Web Intelligence Reports

| June 16, 2009


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

Tags: ,

Category: Business Objects, SQL

Profile photo of LetsLearnBI

About the Author ()

Comments (1)

Trackback URL | Comments RSS Feed

  1. snatarajan says:

    Good Work!. Many People don't know that any error occurs in data, it should be resolved at source side rather than the viewer side. I agree that the best solution is to fix it at the database level. Always people try to fix errors on the fly because the other guys don't want to fix at source.

    sNatarajan