Dummy report for Dashboard section condtional format

aPsikus

Hi,

1. Dashboard with dashboard prompt which contains few entries.

2. Based on selection in this prompt, single dashboard section should be displayed.

3. Sections has Condition options which work on rowcount from additional report.

4. Additional report need to have CASE for each selection from prompt, to display one of the dimensions for which rows will be counted.

But I would like to have clear CASE build by my own, and not to worry about the future where it will be possible that additional values for dimension will be created, and conditional formatting will stop working.

Has any one know if it is possible to create own CASE in this dummy report which will return specific number of rows? Or as in tutorials, you need to use different dimensions?

Robert Angel

Hi,

a case statement works on one or more fields in one row.

case when 'A' then 1 when 'B' then 2 else 3 end

So I do not understand your question; - " if it is possible to create own CASE in this dummy report which will return specific number of rows"?

On 'not worry about the future' you can have logic in your case statement, which provided your future metadata adheres to the logic will work.

Or you can have an else statement which will tie up all data you were not expecting.

Or you can have a combination of the above.

But short of writing AI I do not see how you could cater for all future scenarios?

Can you illustrate your requirement with a brief worked example?

Teresa Modesta

Clear?

No!

Screenshot please?

aPsikus

Dashboard prompt has 3 values for variable PV_VIEW:

Workforce Trend

Hire Trend

Exit Trend

Dummy report has formula:

CASE

WHEN '@{PV_VIEW}' = 'Workforce Trend' THEN 'Male'

WHEN '@{PV_VIEW}' = 'Hire Trend' THEN "Person"."Generation group"

WHEN '@{PV_VIEW}' = 'Exit Trend' THEN "Diversity"."Ethnicity"

ELSE  'Male'

END

Result of the CASE:

By default and for Workforce Trend - 1 row

For Hire Trend - 2 rows

For Exit Trend - 12 rows

On Dahsboard we have prompt mentioned at the beginning, and three sections which contains separate reports. Based on prompt selection, we need to display one of the sections.

Conditions for sections:

Section for Workforce - ROWCOUNT('Dummy_report_for_view') == 1

Section for Hire - ROWCOUNT('Dummy_report_for_view') == 2

Section for Exit - ROWCOUNT('Dummy_report_for_view') > 2

Now: is it possible to update CASE in dummy report to display specific number of rows for each option, not based on dimension?

I need to add additional value to the prompt, and to have it working properly, I need to find dimension which will return more rows than 12, and in conditions for section write formula:

Section for New Entry - ROWCOUNT('Dummy_report_for_view') > 13

I want to avoid that, and be sure, that if for example there will show up new Ethnicity, and it will start to return for example 14 rows, one of the conditions will stop working, and instead of one section, two will be displayed.

As clear as possible

Robert Angel

I might be misunderstanding, but don't you just need ranges for this?

Section for Workforce - ROWCOUNT('Dummy_report_for_view') == 1

Section for Hire - ROWCOUNT('Dummy_report_for_view') == 2

Section for Exit - (ROWCOUNT('Dummy_report_for_view') > 2 and ROWCOUNT('Dummy_report_for_view') < 13)

Section for New Entry - ROWCOUNT('Dummy_report_for_view') >= 13

Teresa Modesta

Or

case rowcount('Dummy_report_for_view')

when 1

when 2

when 3

when 3

when 4

when 5

....

aPsikus

But can I have own/specified row numbers, or they can base only on dimensions from data model?

Teresa Modesta

The row numbers are entirely 'yours', they come from whatever you populate in the case statement.

Or do I not understand you correctly?!

Martin van Donselaar

Why don't you just keep it simple and place each view on a separate dashboard (sub) page? Seems like a lot of (complex) effort for a minor requirement?

If you really think you need the prompt selector way, Is this what you are looking for?: https://www.rittmanmead.com/blog/2014/10/obiee-how-to-a-view-selector-for-your-dashboard/

Jerry Casey

If the objective here is to open specific sections based on the rowcount in the dummy query, then I have done something similar.

My dummy query was a single column date query (Advertise Actual, below) with a filter like this:

You'll have to add clauses to the filter as new options are added to the prompt.

Teresa Modesta

Thanks for translating what was being asked for!!