Pivot view calculation going wrong

User_CEU9I

Hi all,

I have requirement where dashboard prompt have year(Pyear_pv) and  month (Pmonth_pv) as filters.

Report is as follows:

2016, 2015 columns are populating through presentation variables (heading also through PV ).

The script for 2016 column is:

case when "- Shipment Period Dimensions"."Year"=('@{Pyear_pv}{2016}') then count(distinct "- Shipment Dimensions"."Power Unit GID") end

Here "power unit GID" is dimension column and can have duplicate values.

The script for 2015 column is:

case when "- Shipment Period Dimensions"."Year"= ('@{Pyear_pv-1}{2015}') then count(distinct "- Shipment Dimensions"."Power Unit GID") end

Here i need to get data for current year (selected through db prompt) and previous year.

NOTE: I am using obiee 11g, cloud instance (oracle FTI instance), there is no time series feature.

Problem:

In table view i am getting correct value. But when i convert into pivot view then the values are not coming correct.

The report format is pivot format. Please let me know how to achieve the correct result.

Regards,

Ram.

[Deleted User]

You have "Year measures" and a split by Year in terms of dimensional hierarchy/attribute? Why?

User_CEU9I

Hi,

Because of incorporating time series functionality (Current year and previous year).

At my report level i put month as "is prompted".

User can select year and month so that my report will filter based on user selection. If i select year 2016 and December the result as shown below.

Regards,

Ram.

[Deleted User]

Just function-wise the proper function to use is "FILTER" rather than CASE.

Example:

Result:

So as long as the split by dimensional attribute exists (case table) this works but as soon as you drop that it breaks. That's expected.

But what I still don't get conceptually: If you define precisely TWO years in your prompt...why not just use that standard functionality of having the data filtered and the dimensional attribute doing the split??

User_CEU9I

Hi,

Please find the difference for the following two scenarios:

1. I have used measure column : shipment count.

For 2016: FILTER("- Shipment Facts"."Shipment Count" using("- Shipment Period Dimensions"."Year" in ('2016')))

2016 query gives only 2016 data.

For 2015: FILTER("- Shipment Facts"."Shipment Count" using("- Shipment Period Dimensions"."Year" in ('2015')))

2015 query gives only 2015 data (please see the table view).

The result is proper in pivot view.

2. I have used dimension column: Power unit GID.

For 2016: FILTER(count(distinct "- Shipment Dimensions"."Power Unit GID") using("- Shipment Period Dimensions"."Year" in ('2016')))

2016 query is giving both data.

For 2015: FILTER(count(distinct "- Shipment Dimensions"."Power Unit GID") using("- Shipment Period Dimensions"."Year" in ('2015')))

2015 query is giving both data (2016 also).

Pivot view data is not correct.

I am not understand what is going wrong and how to correct this.

regards,

Ram.

User_CEU9I

one more thing ...my dashboard prompt as follows

if user select 2017 year then in my report, i have to show both selected and previous year(2016) data.

regards,

ram.

Thomas Dodds

build your counts as measures in the fact ... that's what they are.

User_CEU9I

Hi all,

Please let me know any possible solution or any workaround solution is most appreciated.

regards,

ram

Mark.Thompson

What happens if you display the Year column going down instead of the measure labels? 

Thomas Dodds

I did give you a solution (not a workaround) ... you know the pivot works when using a fact (it's designed to do just that) ... what you are doing (counting dim attributes) is actually logically creating facts ... so put those in the RPD in your logical fact table (or create a logical fact table at the same LTS grain as your fact).

User_CEU9I

Hi,

It's cloud instance (Oracle FTI instance) and do not have access to RPD. I have to give solution in the report only. Please let me know any other solution.

regards,

ram.