DataSource - Excel workbook with multiple worksheets

HI, I am using open source BIRT and trying define a data source from an Excel workbook with multiple worksheets. I can setup a data source for a single worksheet but I would like to somehow use the worksheet names as a report parameter to select the worksheet for report processing. The data in each worksheet has the same column layout and same data type for each column.
 
In addition to processing an individual excel workbook, I have new excel workbooks being created weekly and would like to find a way to select the workbook to be used as the data source.
 
I have only been working with BIRT for a few weeks. I have some experience programming in python, ruby and php but not java.
 
Can someone please help this novice?
 
Thanks,
Paul

Find more posts tagged with

Comments

Clement Wong

As for the Worksheet name, this one is easy if your worksheets have the same layout as the others.
 
In the Edit Data Set dialog, select Property Binding in the left pane.
 
Then in the Worksheet(s): text box, you can enter (for example if your parameter name was theSheet):
params["theSheet"].value
 
Attached is a sample (created in OS BIRT 4.5.0) if you need it.
 
 
For your second question about selecting the Excel file, you can, in the Edit Data Source dialog, select Property Binding in the left pane. Then for the URI value, you can point that to your other parameter or build your own JavaScript string expression.

telco-schilling

Clement, thanks for the quick reply. I see what you are doing but that assumes the worksheet names are static. In my case the workbook is generated by a program and the worksheet names and number of worksheets in the workbook can vary from week to week. I was looking for a way to generate a list of sheet names in code and dynamically set these in the report parameter.
 
I did come across some code on this website that extracts the sheet names but I'm unsure how to use this within BIRT:
<a data-ipb='nomediaparse' href='http://stackoverflow.com/questions/6183245/how-to-get-names-of-worksheets-in-excel-file'>http://stackoverflow.com/questions/6183245/how-to-get-names-of-worksheets-in-excel-file</a>
 
thanks,
Paul

telco-schilling

Hi Clement,
I have been reading a lot of postings in this forum but I am still not able to connect the dots. Here is my report design and two sample excel workbooks. I have managed to put some javascript into a text box at the top of the report that allows me to select the excel file. It then puts on the screen the names of the tabs in the workbook.
 
I just can't see how to update the data source with the files name of the chosen file and then to populate the report parameter with the tab names.
 
thanks,
Paul

Clement Wong

Paul,
 
Sorry for the late reply, I was out on vacation last week.
 
As mentioned before, "For your second question about selecting the Excel file, you can, in the Edit Data Source dialog, select Property Binding in the left pane. Then for the URI value, you can point that to your other parameter or build your own JavaScript string expression."
 
So to build the worksheet (or filename) dynamically is in your Excel data source ("Yoni-Week11"), double click on it, or right-click and select Edit. The "Edit Data Source" dialog box appears. 
 
Then, in the left pane, click on "Property Binding".
 
In the right pane, for URI:, select the drop down option JavaScript Syntax under the fx.
 
Here's where you can build your string dynamically. For example:
 
'Y1731_Stats-' + BirtDateTime.year(BirtDateTime.today()) + '-' + BirtDateTime.week(BirtDateTime.today()) + '.xlsx'

telco-schilling

Hi Clement,
thanks again for that information but that isn't allowing me to select the Excel file I want to use. I'm looking for some way to have a Browse button that would allow selecting any Excel file. I tried to put a Browse button in the text field of the layout but that doesn't show until after the Parameters selection box occurs.
 
Is it possible to make a browse button occur within the Parameters box as I have mocked up in the attached picture?
 
 
Than the next concern is reading the tab names from the selected Excel file and putting those in the Service parameter listbox. I did supply code in my earlier submission that provided a list of the tab names. I just don't know how to reference this into the Service parameter.
 
Thanks,
Paul

Clement Wong

A Browse button will require customization to the parameter page.
 
However, let me understand your requirements... Did you want to have a Browse button so that the end user can select a local file on the parameters page? And then have it show the worksheets of that local file? How are you deploying BIRT?

telco-schilling

Hi Clement,
Yes I want the end user to select the Excel file from the local file system. Once the file is selected the tab names need to be read from the file and used as the values for the Service report parameter.
 
The 'Browse' button could be in the report parameters dialog box or in any other construct that allows end users to select the file before the parameters dialog appears.
 
Currently I am working with Open Source BIRT. For deployment I was looking at ReportServer. I have install the Bitnami ReportServer package on my linux(centos 6.7) server.
 
thanks,
Paul

Clement Wong

Paul,
 
Unfortunately, the default parameter page doesn't work that way if you have deployed the .rptdesign to an app server or to the commercial BIRT iHub. That is, the .rptdesign does not have the context to the local user's drive.
 
Theoretically, it is possible to have a custom parameter page that would upload the user's file to the app server, or BIRT iHub and then have the .rptdesign use that file as input and the worksheet selected. The Excel file and the worksheet name would be parameters that would be passed and the report can be executed with those parameters. Clean up of those users' uploaded files can occur via cron, or a job after the report has been executed.

telco-schilling

Hi Clement,
Let's say I have an automated process that moves files each week to the same server as the ReportServer, how would I create the list of available files in a listbox for the Workbook report parameter. I would place the files in the same directory each time. The file names and number of files would grow each week and I want to have the list be updated whenever the report is run.
 
Currently I am using a Report Parameters - Parameters Group that has Workbook and Service as the two report parameters. So let's say I have files in a directory /reports, and let's assume I have a javascript that can create an array of the file names in this directory. how do I assign or pass this array to the Workbook report parameter? Also where would I place my javascript, in the report parameter javascript or in does this go in page layout script like clientscripts, onPrepare or beforeRender?
 
Thanks,
Paul
 
Thanks,
Paul

Clement Wong

This is one option... Does your "ReportServer" serve up JSPs? You can have a custom parameter JSP where you can read in a list of Excel files, and then use Apache POI to read in the sheets of that selected file, and pass the two selected choices to execute a report with those parameter values.
 
When your automated process moves the files each week, it can dump the contents of the directory to the text file your report design is referencing. For example "ls > /path/to/ExcelFilesList.txt" or "dir /B > /path/to/ExcelFileList.txt"
 
Then your JSP page can read that text file of the Excel files to be displayed in a form drop down ("Workbook" parameter). And then you can use AJAX to populate the worksheet dropdown ("Service" parameter). Finally, pass these two values into a /exectutereport.do (if using iHub), or /frameset (if using BIRT Engine default viewer).
 
 
Another option that may not work, where you would not need a custom parameter page is to use BIRT's cascading parameters. It's not tested and I'm not sure if the events will fire correctly. In theory, one data source is for the first cascading parameter is the list of Excel files using a Flat File data source. The second data source will list the worksheets of an Excel file using Apache POI in a scripted data source / scripted data set. These two would be part of the cascading parameters.
 
The final data source that populates your report with content will be an Excel data source that will take two parameters of the Excel file chosen ("Workbook") and the worksheet selected ("Service").

telco-schilling

Hi Clement,
I am still working on this and have looked at implementing your second option from the last posting. I now have Cascading Parameter Group with parameters Workbook and Service. I now have a flatfile with the names of the available Excel files that is used to provde the data for Workbook. My problem is the Service parameter.
 
I have some dummy defaults for the Service parameter that show up in the dialog box for report parameters when I launch the report.
 
Using developer tools in my browser I can see that each time I change the Workbook selection and Ajax request and a SOAP response is received. The problem is the SOAP response is always returning my defaults. 
 
I have tried debugging the events processing by putting a little code in each available event script to log to a file but I don't see any of the events making log entries during this Ajax/SOAP exchange.
 
Is there an event that occurs when the Ajax request is made? Can I put my code that returns the returns the worksheet names in the SOAP response so the Service drop down gets updated with this list and not the dummy defaults?
 
Thanks,
Paul

Clement Wong

It's hard to understand without looking at what you've implemented. Are you able to attach either a sample, or the actual files you are working on?

telco-schilling

Hi Clement,
I have tried to simplify this and currently have a flatfile with the names of the excel files and another flatfile with the names of the worksheets in the excel files. Attached is my current report design.
 
When I run this it report can't filnd worksheet {0}. Maybe you can look at this and explain what I have done wrong.
 
Thanks,
Paul

telco-schilling

Hi Clement,
Looks like I have most everything working. I created three Data Sources, a flatfile with list of excel files, another flatfile with list of service names (worksheet tab names) and the third and excel datas source with a reference file showing the layout of all excel file worksheets. This last Data Source has a property binding to my report parameter 'Workbook'.
 
I then created three Data Sets. FileList and Services link to the flatfile Data Sources. The third called Multi_Worksheets connects to the Excel Data Source (Yoni-Week11). This Data Set has a property binding for Worksheets to params["Svc"].value.
 
I setup Cascading Report Parameters with Multiple Data Sets and used parameter Workbook linked to FileList and Svc linked to Services.
 
Now I am able to select the Excel file and the worksheet. This used static files to contain Excel file names and worksheet names and not what I hoped for but its workable for now.
 
The only issue I have remaining is to get the report parameter for Svc to be used as the Title in the Value Series legend. I tried using {Svc} and params["Svc"].value but it just says 'null'. aybe you can tell me the right way to get the selected report parameter value to show up here.
 
Here is the group of files and the report design I have working now.
 
Paul

telco-schilling

Hi Clement,
After getting my report with line charts working I tried adding a table using the same data set the charts are using. I trying to use the same fields as are used in the line chart but after selecting my report parameters the report throws an exception. Attached is file with exception output.
 
Paul

telco-schilling

Hi Clement,
I found the excption was caused by the javascript I had in all the event handlers to attempt to debug. After removing all that the report generation with chart and table is working.
 
I just need the answer to issue from post #15.
 
thanks,
Paul

Clement Wong

You don't need to use a report "Variables", and you can delete the "Service" variable.
 
In the onRender of the chart, you can just reference the parameter as "Svc". For example:
<pre class="_prettyXprint">
var title = icsc.getExternalContext().getScriptable().getParameterValue("Svc");
</pre>
For the chart, you may want to move the legend to Below Position (and use Horizontal Orientation. The longer text (first selection) gets clipped on the right.
 
 
Also, after looking at how you've set up the parameters for the selection of the Excel filename, and then the worksheet, you don't need to have it as a cascading parameter because the first choice doesn't affect the second.

telco-schilling

Hi Clement,
Thank you for all your help. I am almost at the solution I seek. It is correct that the current implementation doesn't need cascading report parameters but I want to be able to load a different list of choices for the Svc parameter based on the Workbook that is selected.
 
Can you explain how or where I would put code that would allow different files to be used for the ServicesList data source and the Services data set?
 
If I select Y1731_Stats-2016-14.xlsx as the Workbook parameter I want to use Y1731_filelist-14.txt for the values in the Svc list but if I select Y1731_Stats-2016-12.xlsx I want to use Y1731_filelist-14.txt for the values in the Svc list.
 
Thanks,
Paul

Clement Wong

In the Edit Data Source dialog box for "ServiceList", select the option in the left pane "Property Binding".
 
Then in the right pane, go to the "File URI", and click on the fx button. Here you will be able to dynamically build the .txt file based on the parameter selected for "Workbook".
 
For example, you can use:
params["Workbook"].value.split("_")[0] + '_filelist-' + params["Workbook"].value.split("-")[2];
 
Or you can name your filename for the Svc list to a name that is easier to build.
 
 
Edit: BTW, you can use Apache POI to dynamically read-in the worksheet names from the XLSX workbook selected. And populate a scripted data source / data set using these values. This would be suitable with cascading parameters.

telco-schilling

Thanks Clement,
I am taking small steps to better understand this complex application. I do hope to use POI at some point to read worksheet names from the selected workbook.
 
Paul

Clement Wong

Apache POI will be a excellent fit as part of your application/solution. I have used it (specifically POI version 3.9) recently in BIRT 4.5.0 report.
 
When you're ready, I can share an example.

telco-schilling

Hi Clement,
I have made great progress and now use POI to read the worksheet names from the selected Excel workbook. This dynamically updates the Svc report parameter. I am attaching the report design where everyone can see the code in both in Data Source 'Scripted' (open and close scripts) and Data Set 'Scripted DSet' (open and fetch scripts).
 
This is working fine when I run this from birt-rpc-report-designer-4.5.0 for windows. However when I try to run this in linux using eclipse-reporting-mars-R-linux package I am getting error "Reference Error: XSSFWorkbook not defined".
 
Please help me resolve this error in my linux setup.
 
regards,
Paul

Clement Wong

In my test, when I Apache POI version 3.9 in a BIRT 4.5.0 report, I needed to specify all of the JARs for Apache POI in the report design (Resources).
 
Here is the .rptdesign XML of that definition:
<pre class="_prettyXprint _lang-">
<list-property name="scriptLibs">
<structure>
<property name="name">stax-api-1.0.1.jar</property>
</structure>
<structure>
<property name="name">xmlbeans-2.3.0.jar</property>
</structure>
<structure>
<property name="name">dom4j-1.6.1.jar</property>
</structure>
<structure>
<property name="name">junit-3.8.1.jar</property>
</structure>
<structure>
<property name="name">log4j-1.2.13.jar</property>
</structure>
<structure>
<property name="name">poi-ooxml-3.9-20121203.jar</property>
</structure>
<structure>
<property name="name">poi-ooxml-schemas-3.9-20121203.jar</property>
</structure>
<structure>
<property name="name">commons-codec-1.5.jar</property>
</structure>
<structure>
<property name="name">commons-logging-1.1.jar</property>
</structure>
<structure>
<property name="name">poi-3.9-20121203.jar</property>
</structure>
</list-property>
</pre>
Where do you have those JARs in your Linux setup?