If two columns match then one column needs to be hide dynamically in obiee 11g?

kavya-Oracle

Hi,

I am working following issue.

"If two columns match then one column needs to be hide dynamically in obiee 11g?".

Is it possible in obiee report. No prompts and no filters in my report.

for example , In my report total 53 columns.

if year is leap year then 53 week columns display in my week report.

If year is non leap year  then 52 week column display and one column needs to be  hide dynamically.

in our report year is non leap year. 52 week column displaying twice. One column needs to be hide dynamically.

Please help me on this,

Thanks in adavnce,

A,kavya.

Robert Angel

If your report is just displaying one year (you do not say) and there is a week number column then you could use some function based logic to determine if the year is a leap year and generate 52 if not 53 if so and put that in conjunction with <= logic on the week number, to filter the report to 52 or 52 weeks.

Week No <= (case when leap_year is true then 53 else 52 end)

Make sense?

Gianni Ceresa

Hi,

If you use a pivot it's done by default: as the attribute "week 52" can't exist twice ...

kavya-Oracle

Hi,

if apply your logic, it wil display either 53 or 52 week. It wont hide entire column. In my report 53 columns in report ( week data display in 53 columns) .

in every column , some logic applied.

week column:filter (sales measure) using week=1.. ,

week2 column formula: filter (sales measure) using week=2.

::

.and week 53 column formula : filter (sales measure) using week=53..

if year is non leap year . last column should be disappear dynamically. (i.e 53 column ). it should be hide.

I am trying to hide column dynamically  in my report

Thanks in advance,

Kavya.

Robert Angel

Hi,

I don't think you are understanding me, I am saying to put a filter on your entire report content, which is the equivalent of; -

Week No <= 52 for non-leap years, Week No <= 53 for leap years.

So week 53 would only appear when appropriate, you only need one filter!

So when 1 is true you will see weeks 1 to 52, other 1 to 53.

Gianni Ceresa

Why do you use FILTER for 53 times?

A pivot does that all by itself (or your model/data is just unusable and you maybe want to fix that one before looking for exotic workarounds in the UI).

Robert Angel

I was not suggesting an 'exotic workaround' - I was suggesting that suppressing the data at source would resolve a requirement that seemed on the face of it fairly odd, why is week 53 populated with data if it does not exist in the years in question??

kavya-Oracle

Hi Sir,

Apology for this,

customer wants to see all 53 weeks sales data in report (for example year =2000).

so that we created 53 columns in report.

Now year 2017, upto 52 weeks data should be display in report. 53 week column should be hide dynamically.

In future, if year= 2020  then 53week columns should be display dynamically

Please help me sir,

Thanks in advance,

Kavya.

Joel

As @Gianni Ceresa said, a pivot table will do this dynamically. It just depends on how the data is stored. If there are no week 53 facts for non leap years then there won't be a week 53 column.

Thomas Dodds

Pivot won't show the 53rd week if it doesn't exist in the data ...

kavya-Oracle

Hi ,

week  is dimension, sales data is measure.

we have historical data in data base. 2000 to 2017 years data.

If year is leap year then no problem we wil dispaly 53 weeks .

every year is not a leap year. we are facing issue with non leap years. that is why i am trying hide 53 column for non leap year.

Thanks,

Kavya.