| Tweet |
This blog talks about a hack to achieve this in OBIEE.
Note that I am on OBIEE version 11.1.1.7.140527. I haven't tested it on higher or lower versions. I also wish to state that this feature is not documented and hence Oracle may not support it.
Let us first create a report and associated dashboard page prompts as shown below.
Our objective is to enable the user to download a file with the selected prompt values in its name. So in this case, we want the file name to be 'Waterfall Bridge Report 02_03_2010-to-08_03_2010.xls'
OBIEE's default export feature puts the name of the analysis as the name of the downloaded file. So in this case, the name of the exported report will be 'Waterfall Bridge Report.xls'
If we are to use the OBIEE's default export mechanism then we will have to put the variables in the name of the analysis and that is not possible.
goURL has a mechanism to download the analysis but without tweaking it, we will get the name of the analysis as the name of the output file.
To achieve what we want, we frame the following link using goURL.
<a href="saw.dll?Go&path=/users/vishal/Blog+code/Prompt+value+in+file+name/Waterfall+Bridge+Report&Action=download&P0=1&P1=bet&P2=%22Time%22.%22T00 Calendar Date%22&P3=2+@{pv_start_date}+@{pv_end_date}&Format=excel2003&Extension=%20@{pv_start_date}-to-@{pv_end_date}.xls">Excel 2003</a>
We use the goURL to filter the analysis using the values selected by the user. We use P0 through P3 arguments for this purpose. This is documented @ https://docs.oracle.com/middleware/11119/biee/BIEIT/apiwebintegrate.htm#BIEIT365P0 through P3 arguments have been highlighted in blue above.
Note that pv_start_date and pv_end_date are the presentation variables linked to the prompts shown in the screenshot above.
What is not documented is the extension which can be added to the goURL. You can find this by opening an OBIEE dashboard in IE and then using the developer tools. You can select any element on the OBIEE page and then look at the HTML that forms the element as shown below.
This screenshot shows that OBIEE puts the extension as .xls if we select an excel2003 download. Now, that we have found this, we will use it to our advantage by modifying it.
This modification is highlighted in red in the URL shared above. We replace .xls with %20@{pv_start_date}-to-@{pv_end_date}.xls. This helps us add the selected values to the downloaded file name.
We add some beauty to the code by putting the excel image next to the link using the code below.
<img src="res/sk_blafp/catalog/exporttoexcel_ena.png"></img>So the final code becomes
<img src="res/sk_blafp/catalog/exporttoexcel_ena.png"></img><a href="saw.dll?Go&path=/users/vishal/Blog+code/Prompt+value+in+file+name/Waterfall+Bridge+Report&Action=download&P0=1&P1=bet&P2=%22Time%22.%22T00 Calendar Date%22&P3=2+@{pv_start_date}+@{pv_end_date}&Format=excel2003&Extension=%20@{pv_start_date}-to-@{pv_end_date}.xls">Excel 2003</a>The end result looks like the following. Note the link at the bottom with excel logo.I get the following when I click on this link
This process works with xls, mht, csv and tab delimited download formats. The links for all of these are shared below.<img src="res/sk_blafp/catalog/exporttoexcel_ena.png"></img><a href="saw.dll?Go&path=/users/vishal/Blog+code/Prompt+value+in+file+name/Waterfall+Bridge+Report&Action=download&P0=1&P1=bet&P2=%22Time%22.%22T00 Calendar Date%22&P3=2+@{pv_start_date}+@{pv_end_date}&Format=excel2003&Extension=%20@{pv_start_date}-to-@{pv_end_date}.xls">Excel 2003</a> <img src="res/sk_blafp/catalog/exporttomhtml_ena.png"></img><a href="saw.dll?Go&path=/users/vishal/Blog+code/Prompt+value+in+file+name/Waterfall+Bridge+Report&Action=download&P0=1&P1=bet&P2=%22Time%22.%22T00 Calendar Date%22&P3=2+@{pv_start_date}+@{pv_end_date}&Format=mht&Extension=%20@{pv_start_date}-to-@{pv_end_date}.mht">Web Archive.mht</a> <img src="res/sk_blafp/catalog/exporttocsv_ena.png"></img><a href="saw.dll?Go&path=/users/vishal/Blog+code/Prompt+value+in+file+name/Waterfall+Bridge+Report&Action=download&P0=1&P1=bet&P2=%22Time%22.%22T00 Calendar Date%22&P3=2+@{pv_start_date}+@{pv_end_date}&Format=csv&Extension=%20@{pv_start_date}-to-@{pv_end_date}.csv">CSV</a> <img src="res/sk_blafp/catalog/exporttocsv_ena.png"></img><a href="saw.dll?Go&path=/users/vishal/Blog+code/Prompt+value+in+file+name/Waterfall+Bridge+Report&Action=download&P0=1&P1=bet&P2=%22Time%22.%22T00 Calendar Date%22&P3=2+@{pv_start_date}+@{pv_end_date}&Format=txt&Extension=%20@{pv_start_date}-to-@{pv_end_date}.csv">TAB delimited Format</a> However, it does not work for Excel 2007 format. So we use a different hack for it. We use the ItemName property. This property has been highlighted in red below. <img src="res/sk_blafp/catalog/exporttoexcel_ena.png"></img><a href="saw.dll?Go&Action=Download&ItemName=Waterfall%20Bridge%20Report%20@{pv_start_date}-to-@{pv_end_date}&path=/users/vishal/Blog+code/Prompt+value+in+file+name/Waterfall+Bridge+Report&P0=1&P1=bet&P2=%22Time%22.%22T00 Calendar Date%22&P3=2+@{pv_start_date}+@{pv_end_date}&Format=excel2007">Excel 2007</a>So the final result of this exercise is the following code: <img src="res/sk_blafp/catalog/exporttoexcel_ena.png"></img><a href="saw.dll?Go&path=/users/vishal/Blog+code/Prompt+value+in+file+name/Waterfall+Bridge+Report&Action=download&P0=1&P1=bet&P2=%22Time%22.%22T00 Calendar Date%22&P3=2+@{pv_start_date}+@{pv_end_date}&Format=excel2003&Extension=%20@{pv_start_date}-to-@{pv_end_date}.xls">Excel 2003</a> <img src="res/sk_blafp/catalog/exporttoexcel_ena.png"></img><a href="saw.dll?Go&Action=Download&ItemName=Waterfall%20Bridge%20Report%20@{pv_start_date}-to-@{pv_end_date}&path=/users/vishal/Blog+code/Prompt+value+in+file+name/Waterfall+Bridge+Report&P0=1&P1=bet&P2=%22Time%22.%22T00 Calendar Date%22&P3=2+@{pv_start_date}+@{pv_end_date}&Format=excel2007">Excel 2007</a> <img src="res/sk_blafp/catalog/exporttomhtml_ena.png"></img><a href="saw.dll?Go&path=/users/vishal/Blog+code/Prompt+value+in+file+name/Waterfall+Bridge+Report&Action=download&P0=1&P1=bet&P2=%22Time%22.%22T00 Calendar Date%22&P3=2+@{pv_start_date}+@{pv_end_date}&Format=mht&Extension=%20@{pv_start_date}-to-@{pv_end_date}.mht">Web Archive.mht</a> <img src="res/sk_blafp/catalog/exporttocsv_ena.png"></img><a href="saw.dll?Go&path=/users/vishal/Blog+code/Prompt+value+in+file+name/Waterfall+Bridge+Report&Action=download&P0=1&P1=bet&P2=%22Time%22.%22T00 Calendar Date%22&P3=2+@{pv_start_date}+@{pv_end_date}&Format=csv&Extension=%20@{pv_start_date}-to-@{pv_end_date}.csv">CSV</a> <img src="res/sk_blafp/catalog/exporttocsv_ena.png"></img><a href="saw.dll?Go&path=/users/vishal/Blog+code/Prompt+value+in+file+name/Waterfall+Bridge+Report&Action=download&P0=1&P1=bet&P2=%22Time%22.%22T00 Calendar Date%22&P3=2+@{pv_start_date}+@{pv_end_date}&Format=txt&Extension=%20@{pv_start_date}-to-@{pv_end_date}.csv">TAB delimited Format</a>The result looks like the following. Newly added links are highlighted below.
These links give the following result.
Till next time ..
12 comments:
good job dude...
Really interesting! Thanks for sharing
Hi,
nice hack. Do you know how to use it in an agent? I would like to send daily report to customers who don't have access to OBI by e-mail. E-mail attachment name should contain date of report generation.
Thank you,
Tomas
You can write s script and invoke it using an action to achieve it using an agent.
Hi Vishal,
Thank you for the post..
I am using this concept is my requirement but unable to use the presenation variable as you have mentioned. Can you pls explain which object(Action or text object etc) u have used to create the link.
Thanks,
Shilpa
Hi Shilpa,
I haven't mentioned about actions anywhere in the blog. This is for online download of reports.
This has been tested in version 11.1.1.7.1.
If you are working on a different version then things might work differently.
As mentioned, this is a hack, its not a feature.
Regarfs,
Vishal
Hi Vishal,
Thank you for the reply.
I was able to make it work in my OBIEE system. There was issue with the sytax i had given.
Very good hack.. just what i needed!
Thanks,
Shilpa
My pleasure, Shilpa.
I suggest that you comment about your incorrect syntax. I say this because this might help others to avoid the same mistake. Please also mention the OBIEE version you are on. That will help us know that the hack works on versions other than 11.1.1.7.1
Regards,
Vishal
Hi Vishal,
The issue was with the presentation variable syntax. I had to give '@{DateVar}' instead of @{DateVar}.
I have a requirement to call stored procedures in OBIEE which creates temp tables in the database. Do you have any experience to share on this. I was wondering how should I create the temporary table in the physical layer of the RPD.
I should not be adding this q in this thread as it is not related...
Any help will be helpful.
Thanks,
Shilpa
this is fantastic! It brings me one step closer to using an agent to package a dashboard using a saved customization, or creating a link to subscribe to an agent with their current dashboard prompts!
Very good post and work in the obiee 11.1.1.7.1 version. Not work in then version 11.1.1.9, :(
Not for excel2007.
Any idea on how to do this in version 11.1.1.9?
Thanks.
Paulo Roberto.
Hi Vishal,
Users at my end have a similar requirement where they want to export the selected parameters into a file at a separate location. There could be multiple instances of file in a day.
Thanks,
Mohit
Post a Comment