After a long time starting to write blog again and I hope that I will continue….
We all know OBIEE is not a data extraction tool having said that I know how many times in real world have been asked to generate excel output from dashboard directly and especially in tabular reports which shows suppliers ,brands, products , orders or anything which go into a line item level and brings back few thousand records. These days with database and with high end servers the capability of fetching the records from backend is not an issue even with multiple parallel users but displaying in a browser is still challenge and in most of the case it is useless to display more than few hundred records.
Also I do not want the users to go in extract 100’s of thousand records from BI if that is the case there are many other best ways available. Still, I prefer to do this for <50k records with <50 columns and will give filters to get the data restricted.
I will show how we can extract records from OBIEE to excel by passing filters.
User will choose the product name and based on their selection extract will be generated for that product.
- Build original report let’s call it “Report 1” which needs to be extracted.
- Create “Report 2” that needs to be shown in the dashboard as a button and will trigger “Report 1” to be extracted to Excel.
“Test Excel Extract” (Report 1) – Created with the required columns
“Test Excel Extract” (Report 2) – Created with one Dummy column which contains following GO URL formula. Go URL guides the link to the main report with the parameters mentioned in the links.
'<a href=saw.dll?Go&path=%2Fshared%2FTest%2FTest%20Excel%20Extract&Action=Navigate&col1=%22Products%22.%22P3%20%20LOB%22&val1='||'"@{PV_Product}{Communication}"'||'&Format=Excel2003&Extension=.xls><button>'||'Download to Excel'||'</button></a>'
I displayed the result in direct Narrative that way by default the button will be well formatted as shown below.
Built a dashboard prompt which can pass on the presentation variable as mentioned in the URL.
Now, in dashboard add the prompt and Report 2.
Now based on the prompt values extract will be generated.
For more info on GO URL refer Oracle documentation
We all know OBIEE is not a data extraction tool having said that I know how many times in real world have been asked to generate excel output from dashboard directly and especially in tabular reports which shows suppliers ,brands, products , orders or anything which go into a line item level and brings back few thousand records. These days with database and with high end servers the capability of fetching the records from backend is not an issue even with multiple parallel users but displaying in a browser is still challenge and in most of the case it is useless to display more than few hundred records.
Also I do not want the users to go in extract 100’s of thousand records from BI if that is the case there are many other best ways available. Still, I prefer to do this for <50k records with <50 columns and will give filters to get the data restricted.
I will show how we can extract records from OBIEE to excel by passing filters.
User will choose the product name and based on their selection extract will be generated for that product.
- Build original report let’s call it “Report 1” which needs to be extracted.
- Create “Report 2” that needs to be shown in the dashboard as a button and will trigger “Report 1” to be extracted to Excel.
“Test Excel Extract” (Report 1) – Created with the required columns
“Test Excel Extract” (Report 2) – Created with one Dummy column which contains following GO URL formula. Go URL guides the link to the main report with the parameters mentioned in the links.
'<a href=saw.dll?Go&path=%2Fshared%2FTest%2FTest%20Excel%20Extract&Action=Navigate&col1=%22Products%22.%22P3%20%20LOB%22&val1='||'"@{PV_Product}{Communication}"'||'&Format=Excel2003&Extension=.xls><button>'||'Download to Excel'||'</button></a>'
I displayed the result in direct Narrative that way by default the button will be well formatted as shown below.
Built a dashboard prompt which can pass on the presentation variable as mentioned in the URL.
Now, in dashboard add the prompt and Report 2.
Now based on the prompt values extract will be generated.
For more info on GO URL refer Oracle documentation
