It's a simple LOV prompt which returns me 3 presentation variables (dp_cache{0,1}, dp_seed{0,1} and dp_log{0,1,2}.In the prefix of each report we default add:
(you can find the postfix block in the advanced tab of your report)
When developing your dashboard you can playaround with different settings to find the optimum. Leave the prompt on the dashboard and make in only 'available' for administrators or developers, so that in time of "trouble" your are quickly able to bypass the cache or turn on logging.
Till Next Time
13 comments:
Hi,
Would you please tell me how to get it in more details, I'm not able to get where to add the code to clear the cache or logs
Hi,
You can find the postfix block in the advanced tab of your report. (You might have to scroll down a bit.)
Hi John,
When i use the below syntax
SET VARIABLE LOGLEVEL = @{dp_log}{0,1,2};
in Advanced tab of a report it is giving this error.
[nQSError: 10058] A general error has occurred. [nQSError: 27002] Near <1>: Syntax error [nQSError: 26012]
Also i have a doubt....the LOV's will be displayed in the dashboard prompt, but we are using this syntax in the report, how the dashboard prompt will get these values....
Hi Kumar,
You can only define 1 default value for the variable =>
SET VARIABLE LOGLEVEL = @{dp_log}{0};
Your dashboard prompts gets the values from your LOV table.
regards
John
hi john,
So you mean to say, we need to create a table with column having values like 0,1,2,3.....and then use this table in the dashboard prompt.
Can you be bit more brief on how the variables defined in the report be used with the dashboard prompt.
Hi Kumar,
In all my repository I have a LOV table (a small XLS) with values like YES/NO, RED/GREEN/ORANGE, 1..10. That why I don't have to use a seperated dimension when making a prompt which doesn't have a direct connection to the table used in the report of the dashboard. The result of this prompt I then put in a presentation variable.
Regards
John
Hi John,
Now I got your point. But I feel that you should have added the point about the custom LOV table(with the values 1...10, YES/NO etc) need to be used to achieve this, in your explanation.
Regards,
Kumar
Hi John,
thanks for your post, this is extremely helpful.
Just found it a couple of minutes ago and it solved already one of my problems.
Best regards,
Andreas
John,
Does the variable Disable_cache_hit and Disable_cache_seed affect PS cache and BI Server cache or just one of the two?
@Anonymous,
It's only the BI-server Cache.
regards
John
Hi John,
I set the variable as said in prefix area in Advanced tab and using the variable in the same report for filtering the records:
SET VARIABLE p_s_dt= select max(Booking ."PDate" ) from BK where "PDate"< CAST( '@{s_dt}{2099-01-01}' AS DATE)
but get an error:
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27002] Near < select >: Syntax error [nQSError: 26012] . (HY000)
Not sure what is causing this.
@Venky,
Looks like your missing a subject area
try:
SET VARIABLE p_s_dt= select max(Booking ."PDate" ) from BK where Booking."PDate"< CAST( '@{s_dt}{2099-01-01}' AS DATE)
John,
thanks for the post.
i am getting an error when i try below.
Appreciate any feed back.
when i am setting a variable in Prefix.
SET VARIABLE=SELECT CAST(ABS(Position."Hierarchy Gap") AS Integer) saw_0 FROM "Sales - CRM Pipeline" WHERE Position."Current Base Login" = 'Abcd';
Below is the error:
SQL Issued: {call NQSGetLevelDrillability('SET VARIABLE=SELECT CAST(ABS(Position."Hierarchy Gap") AS Integer) saw_0 FROM "Sales - CRM Pipeline" WHERE Position."Current Base Login" = ''Abcd''; SELECT Employee."Employee Login" saw_0, Opportunity."Opportunity ID" saw_1 FROM "Sales - CRM Pipeline" WHERE Employee."Employee Login" = ''Abcd''')}
Post a Comment