Presentation variable in dashboard prompt

663424

Hi All

I have a report on a dashboard page which navigates to another report on a different dashboard page (on the same dashboard). The
first dashboard page has a prompt which needs to be passed as a filter to the navigated report i.e. Report1 on dashpage1 has Level1 prompt
whose value must be passed to Report2 on dashpage2 which has Level2 (hence Report2 must show Level2 values for the passed Level1).
1) I tried setting prompt1 on first page scope to Dashboard but it didn't help.
2) I am trying to set a presentation variable 'level1_val' in the prompt1 and then in report2 say Level2 = @{level1_val} in Answers (filter->add presentation variable, I did not provide a default value). But when I navigate, Report2 shows no results. On adding the filter value, I noticed that the condition in Report2 is Level1=level1_val (Its taking level1_val presentation variable as a string and matching it to the Level1 prompt value). Level1 prompt value is a string. Is there a format say '@{level1_val}' that I should use in the column filter (I've tried this too)?

Thanks
Ananth

Goran Ocko

'<--a href=http://.../saw.dll?Dashboard&PortalPath=/shared/Yourfolder/_portal/Yourdashboard/&Page=Yourdashboardpage&
Action=Navigate&col1=%22Level%20Hierarchy%22.level1&val1='||REPLACE(REPLACE('@{pv_level1}', ' ', '+'), '+', '%2B')||'>Link<--/a>'

For the presentation attribute replace " with %22 and spaces with %20, this is required for DASHBOARD URL syntax for target report or dashboard prompt attributes.

col1=%22Level%20Hierarchy%22.level1

where "Level Hierarchy".level1 is the presentation table.column real name in the report 2 set to is prompted.

Regards
Goran
http://108obiee.blogspot.com

David_T

There are several things that come to mind, but let's start with this one. You say in report 2 you filter on the column clicking the filter button, then Add>Variable>Presentation Variable. OK, if you use this method, you only type level1_val in the field, not @{level1_val}. Did you do that? You only use the "at sign" @, when you "convert the filter to SQL." If you use the latter method and the column is a CHAR, then you would type '@{level1_val}' in the window.

See if that's what's causing your "no results."

663424

Hi David

In the add Presentation variable window, I just add the level1_val name, thats it (without @ or {). The filter list now shows
Level2 is equal to/is in @{level1_val}. So I guess the right way is to just type in the variable name in the add presentation variable window.
I didn't get the second part: do u mean because level1 is a char prompt, I have to add '@{level1_val}' in the add presentation variable window?

And the Level1 prompt on the first page is a drop down list (not multi-select).

I added the name of the variable in the add variable->presentation as level1_val and then clicked on convert this filter to sql.
Strangely, it actually converts to Table.Level2='leve1_val' (taking the variable name as a string).

I then tried to add it directly to the convert filter to sql. Table.Level2 = '@{level1_val}' and also tried Table.Level2 = @{level1_val}
Still shows no results. Still seems to consider the variable name as a string.

Thanks
Ananth

Edited by: Ananth V on Nov 29, 2009 2:16 AM

Goran Ocko

I have a report on a dashboard page which navigates to another report on a different dashboard page (on the same dashboard). The first dashboard page has a prompt which needs to be passed as a filter to the navigated report i.e. Report1 on dashpage1 has Level1 prompt whose value must be passed to Report2 on dashpage2 which has Level2 (hence Report2 must show Level2 values for the passed Level1).

The first dashboard page has dashboard prompt (level 1) and the report 1 that shows level 1 and the level 1 is prompted?

The second dashboard page has report 2 that shows level 2 and has level 1 is prompted?

No dashboard prompt in the second dashboard page?

How do you make navigation from the report 1, using column value interaction navigate in the level 1 column on the report 1 and calling report 2 or calling dashboard page with report 2?

Regards
Goran
http://108obiee.blogspot.com

663424

Hi Goran

Report1 on Page1 has Level1 prompt and Level1 is prompted in the report.
Report2 on Page2 has Level2 prompt which should display values of Level2 based on the value of Level1 (from the previous report Report1). Report2 has Level1 and Level2 is prompted filter.

The navigation from Report1 to Report2 is thorough a link (using html code that calls the second dashboard page) in the report section that says 'Click here to go to next level'. There is no column value interaction on Report1.

Thanks
Ananth

Goran Ocko

You are using dashboard url option in the code from the report 1 to call dashboard page 2 (with report 2).

You have only dashboard prompt for level 2 in the dashboard page 2?

1. If you put in the filter of the second report column is equal to presentation variable that you set in the first dashboard prompt this will not work, you'll get COLUMN is equal to pres_var. When you are using DASHBOARD URL you need to pass presentation variable to the filter of the target (dashboard page prompt). If the target page prompt does't exist the parameter is parsed to the filter of all reports (or just one) that has this COLUMN set to is prompted.

2. Go to the report 2 and set level 1 is prompted.

3. Go to the report 1 and write dashboard url correctly with passing presentation variable like:

'<--a href=http://.../saw.dll?Dashboard&PortalPath=/shared/.../_portal/Yourdashboard/&Page=Yourdashboardpage
&Action=Navigate&col1=TABLE.COLUMN&val1='||REPLACE(REPLACE('@{pres_var}', ' ', '+'), '+', '%2B')||'>Link
<--/a>'

TABLE.COLUMN is a target column to which you want to pass parameter (presentation variable), it can be on the prompt or just set to is prompted in the report 2.

But if you want to pass presentation variable to the filter of the dashboard page 2 prompt which is level 2 this could be a problem (if you are using SQL result option in the dashboard prompt in the second page to show level 2 and want to filter it by pres_var that you set in the first page prompt with level 1 this is not possible, because you can only pass parameter to the page prompt field, but you can set dummy hidden prompt in the second page and pass level 1 to it and then set new presentation variable that is available only for the second page and then use this presentation variable as the filter in the second page prompt for level 2 in the filter to show only level 2 values for the level 1 value, this method is explained in the link below).

Try to fix it now.

You may want to read this:
http://108obiee.blogspot.com/search/label/DASHBOARD%20URL

Regards
Goran
http://108obiee.blogspot.com

Edited by: Goran O on 2009.11.29 10:23

663424

Hi Goran

Thanks a lot for the tip. I tried it out, its almost working , I changed the dashboard page url and now when I click on 'Next Page'
I see the url change to the dashboard prompt value. But the dataset in the Report2 is not filtering based on the value being passed.

I have Level1 as prompt in Report1 and Level2 as prompt in Report2 (Report2 has Level1 is prompted). So in the dashboard url where should col1 point to, since
I dont have Level1 prompt on the second page.
But you said 'you need to pass presentation variable to the filter of the target (dashboard page prompt). If the target page prompt does't exist the parameter is parsed to the filter of all reports (or just one) that has this COLUMN set to is prompted'. So it should work right because Level1 does not exist in report2 and so the report should be filtered for the Level1 value because Report2 has Level1 is prompted.

Thanks
Ananth

Goran Ocko

Where do you want to pass presentation variable from the first page (level 1)?

You cannot pass it to the second page prompt because it has level 2 in the prompt, so this doesn't make any sense.

You can pass it directly in the report 2 filter (set to level 1 is prompted in the report 2) and then col1=your_level1_column_from_the_second_report.

What is the meaning of that, you have level 1 and level 2 set to is promped in the second report and have only level 2 dashboard prompt on the second page?

Each time when you navigate to second page the report 2 will show values (level 2) for the level 1 that has been transferred and then you can do some filtering on the dashboard prompt on the page 2 (level 2).

Is this correct or you may think of something else?

Regards
Goran
http://108obiee.blogspot.com

663424

Hi Goran

You are right. The second page has only Level2 prompt whereas Report2 has Level1, Level2 and measures (Level1 coming from first report). I have Level1 is prompted in the Report2 filter.
col1=your_level1_column_from_the_second_report. I think col1 is a keyword right so it should be &col1=level1&val1='@{variable_name}'.

The reason why Level1 should be 'is prompted' in the second report is that there is no Level1 dashboard prompt on the second page. So when the value of Level1 is passed on the url, there is no Level1 in the second report dashboard prompt, but because there is a Level1 is prompted in the report filter, the report should filter based on the value of Level1. Is this correct? If yes, I think I'm still missing something because its not filtering.

Thanks
Ananth

Goran Ocko

'<--a href=http://.../saw.dll?Dashboard&PortalPath=/shared/Yourfolder/_portal/Yourdashboard/&Page=Yourdashboardpage&
Action=Navigate&col1=%22Level%20Hierarchy%22.level1&val1='||REPLACE(REPLACE('@{pv_level1}', ' ', '+'), '+', '%2B')||'>Link<--/a>'

For the presentation attribute replace " with %22 and spaces with %20, this is required for DASHBOARD URL syntax for target report or dashboard prompt attributes.

col1=%22Level%20Hierarchy%22.level1

where "Level Hierarchy".level1 is the presentation table.column real name in the report 2 set to is prompted.

Regards
Goran
http://108obiee.blogspot.com