Dashboard Prompt for custom analysis (LSQL based on subject area)

Sa_De_Or

I  have following analysis based on LSQL(SQL attached):

SkillsCustomLogicalSQL.sql

the prompt function working fine but since I have a lot of Prompts I cannot choose a default value for each one, since I cannot ensure to get results with this combination. If you don't choose a value, the reports will show an error.
(HCM, Version 24C (11.13.24.07.0))

Any help is appreciated.

Gianni Ceresa

If you don't set default values for your filters using a variable, then the analysis can't return any data.

I imagine (based on a picture you posted in the other thread) that what you call "show an error" is not really an error but the message that the query didn't return any data. It's an informative message saying that for the selected filters no data was found.

You have 3 options if you don't want to see that message:

1. Set default values in your filters for when the variables aren't set or empty. If you edit your filters in the analysis you should have an option to enter a default value for each one using a variable.
2. Set default values for your prompts. Doesn't matter if they are many, you need to have all your variables populated if you want your analysis to return something. Even if this means that for 95% of the users the 1st load when they open the page is useless because it isn't their values.
3. Set prompt in your dashboard page as mandatory, and configure your page to require the filters to be set before to display, and execute, the analysis needing those filters.

You can pick what works better for you. If all the users of that analysis will need different values, both 1) and 2) will be fairly useless for most of your users, because the default values you will set aren't going to be the values they need. In that case the 3rd option could be a bit useful, at least it will require your users to first pick a minimal set of values in your prompts and then the analysis is executed and will retrieve data.

JohnW-Oracle

Hi @Sa_De_Or ,

Thank you for the question. You can set these prompts so they are 'Required' and not 'Optional'. Prompts do not necessarily have to have hardcoded default values but they should have a default of some sort to prevent performance problems. For example, you could use an Init Block and Session Variable like 'VALUEOF(NQ_SESSION.USER)' to populate the prompts with defaults.

As to why you are getting the error 'Invalid Subject Area', when you do not select a value would probably require a SR and some digging.

Regards,

John

Sa_De_Or

Hi @JohnW-Oracle,

thank you for your quick reply: I have never used Session Variables.

Where do you set this?

like this (server Variable) or like this?:

both options do not work unfortunately.

Thank you!

Sa_De_Or

Hi Gianni,

thank you for your quick reply. The client is expecting a report with runtime prompts which is good to achieve by dashboard prompts. Hence, your report loads and shows all results, even if no prompts are set. Option 3 would therefore be my Plan B.

Thank you,

Sandra

Gianni Ceresa

If what you expect is the analysis to run without any filter the first time you open the page (beware that this could generate a very slow query that everybody execute all the time before to start filtering), you need to add some more logic to the filters of the analysis to handle the case when the variables aren't set (= the prompts don't have values selected).

It's a logical workaround to remove the filters when the value isn't set, the logic is to replace each one of your filters with this kind of expression: (@{p_p_no}{-999} = -999 OR "…"."Person_Number" = @{p_p_no})

The idea is to transform the filters in conditions that become 1=1 when no value is set, therefore using a default value for the variable and testing if it's equal to that same default value. And you then add the piece of logic applying the real filter with a OR (this means that the database, or even the product, should be smart enough to skip the filter fully when it sees it is a 1=1.

For option 3, there should be an attribute in the dashboard page properties to say it requires the prompt to be selected first. Or you can make the same using conditional display of sections in the page. I wrote about that solution ages ago https://www.rittmanmead.com/blog/2014/03/obiee-dashboard-prompt-at-least-one-mandatory/ , but it does still work (the formatting of that post is a bit rubbish because the owner of the blog did migrate to a different platform in the meantime, and because I don't work there anymore I can't edit it to fix the format).

Sa_De_Or

Hi @Gianni Ceresa,

thanks for your feedback!

Just to make sure:

It's a logical workaround to remove the filters when the value isn't set, the logic is to replace each one of your filters with this kind of expression: (@{p_p_no}{-999} = -999 OR "…"."Person_Number" = @{p_p_no})

Where would I need to implement this in order to make it work?

Thank you for your support!

Gianni Ceresa

Hi,

That is a filter, it would go in your analysis in the filters' section.

It currently has 8 filters with a AND condition (at least based on the screenshot posted above). Each one of those filters that require to be applied only if it has a value needs to be replaced with the logical expression posted above as example.

You can build those filters with the GUI, or just transform your 8 filters in LSQL expression and write the new filter instead in there.

Just be aware of one possible downside: sometime the product tries to be smarter than what you need it to be, and there are situation when it does replace filters in an analysis because it believes it will be the same result but faster. In the past it has been seen that the rewriting of the query wasn't at all the same, some filters were just dropped. Therefore maybe have a look at the queries generated when you test it, to see if they are still like you needed them.

Sa_De_Or

Hi Gianni,

I think I am still doing it wrong. Do you maybe have an example for this?

I tried several ways by adapting the little query and using it in different fields within the filter.

I unfortunately still get the message, that there are no results.

Thank you

Gianni Ceresa

Hi,

Sorry for not giving more details and an example. Let's try now.

Let say I have an analysis that is a not really yours but you will probably easily match it with your case.

I have a simple filter of a column = a presentation variable.

To replace that filter with the logic mentioned in previous replies, I edit the filter and check the "Convert this filter to SQL" checkbox.

What you get as a result is the advanced SQL filter window, with your filter converted from the GUI representation to a LSQL expression.

And here you can now change the filter to make it like the formula posted before. You really just need to be careful in respecting the LSQL syntax, and to have the brackets in the right place.

And that should work.

You "just" lose the GUI for the filters and will need to manage them via the advanced SQL filter, but it's hopefully not too much of an issue.

Sa_De_Or

Hi @Gianni Ceresa,

thank you for your support and for the explanation.

I have tried what you showed me and it worked so far that the report was loaded even if no prompt value was selected.

However, if I select a value in the prompt for which I should get results, I encounter an error.

Gianni Ceresa

Is your prompt a real number? Or is that a number but defined as text?

And is "Person_Number" the real name of the column? As you can see from the query visible in the error, what the tool is doing is adding the filters conditions to the LSQL of your analysis. You must make sure that the column is correctly referenced.

That's my guess seeing the error, then you can of course look at the whole query and figure out what is the error in there (you can execute it directly against the BI Server to test it and find the valid syntax if needed).

Sa_De_Or

Hi @Gianni Ceresa,

the prompt is a string. Person Number is a alphanumeric value.

I have tried "Workforce Management - Worker Assignment Real Time"."Worker"."Person Number", "Worker"."Person Number" , "Person Number" and Person_Number and C.s_6.

Unfortunately, this didn't work.

Christian Berg -Oracle

If it's an alphanumeric string then it's a string and not a number.

Your presentation variable is a number. Make it a string and you should be peachy.

'@{pvStringNotNumber}{123}'

That will make 123 be technically a string: '123'

Data type consistency must be observed.

Sa_De_Or

Hi Christian,

thank you for this. I have tried your solution and it still does not seem to work.

Before I tried with the {-999} . Both variants did not work. I get the same error as above.

Christian Berg -Oracle

"does not seem to work"

What does that mean? What does the log file say? What is the exact error? What you are asking is basic standard functionality and works normally, so there must be an issue with either your data or with how you have set up things.

If you do not provide clear details on what is happening this can not be resolved.