OBIEE Union report having issues

DB_Home

Hi,

I have a union report with two instances and first instance calculating based on cob date and second instance calculating difference b/w cob date & comparison date.

Issue: Suppose if I don't have data for cob date and have data in comparison date then in both the tables it's picking comparison date data. But my requirement is it should not display any comparison date data in the first table as I'm passing cob date only. How can we fix this issue?

TIA,

Madhav

Robert Angel

Hi,

your filters are definable by you on each part of the union query and can be wholly independent of each other, so it is up to you to ensure you have wholly appropriate filters on the sources to get the data set that you want, and if there are two kinds of date that you are putting into the same field via the union then your pivot table will have no means of differentiating them.

Perhaps you also need two date fields where currently you only have one in the union?

DB_Home

Hi Robet,

Thanks for quick response, I have cob date field on the first union and I have cob date and comparison date fields on the second unions.

DB_Home

Suppose if I have data for cob date and comparison date then it's working perfectly, issue is with when we don't have data for cob date and have data for comparison date then only arising this issue.

Robert Angel

Ok,

can in your description you talk about calculating the difference between two dates - but this will be a number not a date so you cannot be union'ing that into the same field?!

Can you clearly define the logic for what you want to see, and like the sql forums provide a small but perfectly formed piece of illustrative data so I can fully understand your requirement and begin to suggest your best way forward?

i.e. follow this kind of presentation please, stating only the relative fields and a small illustration of data

Union 1

Field company is 'Corporate'

Cob Date put in field Date, with value '03-Jan-2019'

Union 2

Field company is 'Local'

Comparison Date put in field Date with value '03-Jan-2019'

Desired output is??

DB_Home

Sure!

Union 1:

Field company1 is 'Book Value USD'

Filter: Cob Date='30-Nov-2018'

Formula: "Measures"."Book Value USD" USING "Date"."COB Date" IN (@{pv1}{date '2018-11-30'})

Union 2:

Field company is 'Book Value USD'

Filter: Cob Date='30-Nov-2018'

        or Comparison Date='31-Oct-2018'

Formula: ("Measures"."Book Value USD" USING "Date"."COB Date" IN (@{pv1}{date '2018-11-30'}))-

("Measures"."Book Value USD" USING "Date"."Comparison Date" IN (@{pv2}{date '2018-10-31'}))

Here my requirement is if I ran report then in the first table it should display only available dollars for Cob Date, In 2nd table it should display difference dollars b/w cob date and comparison date.

Note:

1.If I have data(dollars) for both the dates then my report is working fine.

2.If I have data only for cob date then it's working fine.

3.If I have data for Comparison Date and don't have for Cob Date then facing issue like in both the tables it's displaying Comparison Date data(Dollars)

Robert Angel

One more question, you talk about data(Dollars) and you give me the formula;

Formula: "Measures"."Book Value USD" USING "Date"."COB Date" IN (@{pv1}{date '2018-11-30'})

and

Formula: ("Measures"."Book Value USD" USING "Date"."COB Date" IN (@{pv1}{date '2018-11-30'}))-

("Measures"."Book Value USD" USING "Date"."Comparison Date" IN (@{pv2}{date '2018-10-31'}))

Is the formula = data(Dollars)?

If you problem is when the first half of the below is null then don't you need an additional case  logic or NullIf  equivalent to zero that result out?

Formula: ("Measures"."Book Value USD" USING "Date"."COB Date" IN (@{pv1}{date '2018-11-30'}))-

("Measures"."Book Value USD" USING "Date"."Comparison Date" IN (@{pv2}{date '2018-10-31'}))

DB_Home

Yup,

Data means 'Book value USD' column in dollars,

I already tried with ifnull function but no luck.

DB_Home

If I don't have data for cob date and have data for comparison date then the 2nd table showing correct results, but in first table also picking same data like 2nd table. Ideally it shouldn't happened.

Robert Angel

My thought with ifnull was when your COB date is null then use ifnull to replace it with the Comparison date formula (second half of your formula), this would mean that all where you comparison date is populated but your cob date is not would become zero, as any result minus itself would yield zero.

DB_Home

Hi Robert!

But as per my requirement I don't want to replace cob date with comparison date if cob date is null, I have issue with first table which is having only cob date filter and it should display data for cob date only and if cob date is null then it shouldn't display my first table in the above screenshot...but this functionality is not working in my case.