Straight-forward XLS data export in CDA

Posted Mon, 15/08/2011 - 22:11 by udid
, , , , , ,

In previous post we have developed a dashboard with CDE, the dashboard needs a parameterized and secured data access layer to provide the data for the graphs, selectors and texts on the screen while encapsulating the data access complexity - the CDA.

 

Once the dashboard is complete and users find valuable information or spot insightful patterns they would definitely want and ask you to provide data export functionality to the dashboard, which is easier done than said.

 

All you need is a Text Component with this expression:

function() {
  return "<a href='/pentaho/content/cda/doQuery?solution=mySolution&path=%2FmyFolder&file=myDashboard.cda&dataAccessId=myQueryName&outputType=xls&parammyParam="+myParam+"&parammySecondParam="+mySecondParam+"' target='_blank' title='Export to xls'><img src='/pentaho/public/export.png'/>Export</a>";
}

 

This will create a link (image+text) which when clicked would open a new tab or window and would start a dowload for the data in xls format.

 

You need to replace my variables with your cda name and path, and to pass the required parameters as paramXXX=YYY.

 

CDA Documentation is available here

Comments

by nliendo - 18/08/2011 - 21:01

Hi!

 

Congratulations for this post, i think is a very useful tool to add in the dahsboard.

 

Just a questions, this parameters should be created in the dahsboard as a simple parameter, what they should contain?

 

Thanks in advance

 

NL

by udid - 20/08/2011 - 11:22

nliendo - In many cases the dashboard itself needs to be parameterized, that means that there would be one or more selectors that would enable the user to select one or more parameters that control the dashboard displayed information, for example, many dashboards are parameterized by time, so users can select to see yesterday/last week/last month, or they can select a product line or a geographical area etc.

In order to implement parameterized dashboard in CDE we use a parameter to hold the current status of user selection, the parameter is usually controled with a select-component (combo-box), usually I prefer to construct the parameter to hold a cube member or a set.

In CDE, all components, in specially chart components, can be parameterized with the declared parameter and can listen to it. Each time the parameter changes the listener components would refresh. The data-source behind the component must also be parameterized with the same parameter so the component can call the data-source while passing the paramenter to it and get back the result set of the parameterized query.

 

The parameter can hold any value you choose and the data source query has to know how to use the parameter to return the appropriate data, when you integrate export functionality such as in this post, you have to pass the parameter to the export query, so you would get the data currently displayed.

 

If you want to see an example of how to tie together parameters, selctors, charts and data sources please refer to the example in my previous post linked from the top of this post.

 

Thank you and happy dashboarding...