RDBMS Reporting
From OpenI Wiki
| Table of contents |
[edit]
Features
- users must be able to upload jasper report configuration files (designed outside of openi)
- openi must be able to display the jasper reports, using the above uploaded configuration files.
- multiple configurable jdbc datasources
- system validates the driver classname (class.forName, trap exception) - give the user a warning, but allowed to save
- System tests the username/password - again provide a warning, but allow to save
- System provides default text box values for new jdbc datasources
- default text box contents to jtds driver (net.sourceforge.jtds.jdbc.Driver)
- default text box url to std jtds settings (jdbc:jtds:sqlserver://hostname/yourdatabasename)
- System integrates configuration of jdbc as well as xmla datasources
- System provides UI to Configure RDBMS Mapping (mapping of jrxml files to jdbc datasources)
- OpenI ships with jtds driver
[edit]
Sub-Report and parameter support
Report parameter as well as sub report should be managed by OpenI so that the user can use OpenI+Jasper more effectively.
- How paramter is used:
- parameters are declared in jrxml file at design time. e.g.
<parameter name="some name" isForPrompting="true/false" class="java.lang.String"> <parameterDescription>some description</parameterDescription> <defaultValueExpression >some value</defaultValueExpression> </parameter>
- parameter can be used in report SQL. e.g.
select somefields from atable where afield LIKE '$P!{nameoftheparameter}'
- Parameter's values can be specified programatically at run time
- How sub-report is used
- Sub-Report can be seperate report files
- Sub-Report are used as follows from master report
<subreport .....> ........ ........ <subreportExpression class="java.lang.String">subreportname.jasper</subreportExpression> </subreport>
- Sub report needs to be compiled jasper file
[edit]
Implementation thoughts
- OpenI should be aware of the parameter that needs to be specified while executing the report
- Can check the available parameter list in jrxml and show the option for user to change its values before executing the report. This approach is simpler to implement. use this one
- Can ask user to setup required parameter when user uploads jrxml and ask at the execution time for its values. But where we store the paramterts - another mapping complexity
- OpenI needs to know about the Sub Report if it is used
- The simpler approach is : use this simple approach
- User uploads master report
- User upload sub repports as well in same location where master report is uploaded
- some restriction should be made while naming subreport so that openi knows about it. eg. masterreportname_subreportname.jrxml
- while executing master report , openi searches the subreport based on its naming rule. It compiles the subreport along with master report if any match is found
- Sub report should not be executed independently(not sure). If this is the case, we should hide it from navigation menu tree
- Another approach is to use mapping system. but creates complexity
- The simpler approach is : use this simple approach
Any Sugestions..
JimP on Parameters: Simple dialog box to ask user parameter value is great way to start (& is what iReport does in it't report viewer - it allows pick of default, but doesn't show what it is - I would display it).
More user friendly versions: a)data type dependent dialog box (eg date picker, numeric, logical, ...), b) pick from previous entries, c) allow report designer to spec SQL query to fill allowable value pick list, eg "SELECT campaign_name FROM campaign ORDER BY campaign_key"
JimP on SubReports: Using nameing convention for sub reports is just fine. In general, sub-reports are not run independently in production (they are, obviously, in development), so they should be hidden in tree.
Question: Does it help if report developer (eg me) uploads compiled report instead of .jrxml? iReport's report viewer does th e compile & saves in it's directory tree. (eg I have CampaignSummary_master.jasper) May also need .jrmxl of main report to figure out parameter stuff - a .jasper file makes interesting reading in TextPad!)
[edit]
Quickstart
- Configure a jdbc datasource:
- Configure Datasources
- new
- JDBC
- Create a report using a jasper report creator (such as iReport), create a jrxml file. See jaspersoft for more info
- Upload the jrxml report file
- map the jrxml to a jdbc datasource:
- Configure Datasources
- Map JDBC Datasource to report
- click on the jrxml file to run the report
[edit]
Jasper Report DTD Issue
Since OpenI generates report parameter input UI by transforming jrxml report file. However, in case of no connectivity, it throws an UnknownHostFoundException, since transformer tries to read dtd file 'jasperreport.dtd' from sf.net server. To solve this problem (Only if you do not have the net connection available):
- edit jrxml file and goto to the dtd entry in top of the report
- modify the entry 'http://jasperreports.sourceforge.net/dtds/jasperreport.dtd' as 'http://openideployedhost/openi/jasperreport.dtd'- e.g 'http://localhost/openi/dtds/jasperreport.dtd'
[edit]
Use case
- user designs jasper report using any of the various jasper report designing front ends, result is a jasper xml configuration file
- user uploads jasper xml file to any directory
- jasper xml shows up in left navigation
- when user clicks on the jasper xml file
- openi compiles the file into a .jasper file (into java.io.tmpdir)
- Note: we should compile on the fly per request, so that the user can download the jasper xml file for customization
- openi displays report
[edit]
nice to have
- later iterations, we should consider how to integrate with connection pooling
- later iterations, we need to specifically allow downloading the jasper xml configuration file
[edit]
Implementations
- JDBC datasource and XMLA datasource both are configurable from 'configure datasource page'
- Datasource are map object - key belongs to datasource name and object belongs to either JDBC or XMLA datasource
- Datasource name are unique- should not have two or more datasource with same name
- Datasource is serialized as follows in project.xml:
<dataSourceMap>
<entry>
<string>mondrian</string>
<org.openi.analysis.Datasource>
<server>http://localhost:8080/openi/xmla</server>
<xmlaDatasource>MondrianFoodMart</xmlaDatasource>
<catalog>FoodMart</catalog>
</org.openi.analysis.Datasource>
</entry>
<entry>
<string>msas</string>
<org.openi.analysis.Datasource>
<server>http://server:80/xmla/msxisapi.dll</server>
<xmlaDatasource>Local Analysis Server</xmlaDatasource>
<catalog>FoodMart 2000</catalog>
</org.openi.analysis.Datasource>
</entry>
<% Sample JDBC Datasource for ODBC connection %>
<entry>
<string>jdbc_odbc</string>
<org.openi.project.JdbcDatasource>
<driverClassName>sun.jdbc.odbc.JdbcOdbcDriver</driverClassName>
<url>jdbc:odbc:your_dsn_name</url>
<username>your_username</username>
<password>your_password</password>
</org.openi.project.JdbcDatasource>
</entry>
<% Sample JDBC Datasource for SQL Server using TDS JDBC Driver %>
<entry>
<string>jdbc_sqlserver</string>
<org.openi.project.JdbcDatasource>
<driverClassName>net.sourceforge.jtds.jdbc.Driver</driverClassName>
<url>jdbc:jtds:sqlserver://your_server;DATABASENAME=your_database;INSTANCE=your_sql_instance_if_any<url>
<username>your_username</username>
<password>your_password</password>
</org.openi.project.JdbcDatasource>
</entry>
<% Sample JDBC Datasource for MySql using MySql JDBC Driver %>
<entry>
<string>jdbc_mysql</string>
<org.openi.project.JdbcDatasource>
<driverClassName>com.mysql.jdbc.Driver</driverClassName>
<url>jdbc:mysql://your_server:your_server_port/your_database</url>
<username>your_username</username>
<password>your_password</password>
</org.openi.project.JdbcDatasource>
</entry>
</dataSourceMap>
- org.openi.analysis.Datasource class belongs to XMLA Datasource
- org.openi.project.JdbcDatasource belongs to JDBC Datasource
- each Jasper template(.jrxml) needs to be mapped to use JDBC Datasource
- jdbcMap property of Project serves this purpose. Here is a sample map serialized in project.xml:
<jdbcMap>
<entry>
<string>public/Samples/list_of_customers.jrxml</string>
<string>jdbc</string>
</entry>
<entry>
<string>public/Samples/another_customers.jrxml</string>
<string>mysql</string>
</entry>
</jdbcMap>
- Datasource for uploaded template can be set by upload file UI. It will prompt to set up datasource, if .jrxml file is found uploaded
- RDBMSController uses jdbcMap to find the correct datasource for Jasper report
- RDBMSController uses default datasource 'jasper', if no datasource is assocated for the selected template
- RDBMSController throws an exception if there is no datasource available
- Jasper report template datasource can also be configured via Datasource configuration page
- Datasource configuration page lists out all available .jrxml file path under project directory and gives the option for user to configure their JDBC datasource.
- JDBC datasource for each analysis is also configurable. Analysis contains a property 'drillthroughDatasource' and can be configured as follows in xml:
<drillthroughDatasource>datasourcename</drillthroughDatasource>
[edit]
Sub Report and Parameter User Guidelines
- Parameter that requires user input should be declared as follows:
<parameter name="param_name" isForPrompting="true" class="java.lang.String">
<defaultValueExpression ><![CDATA[some default value]]></defaultValueExpression>
</parameter>
- Subreport expression should be the jasper file with no path as follows:
<subreport ...> .......... .......... <subreportExpression class="java.lang.String"><![CDATA["subreportname.jasper"]]></subreportExpression> </subreport>
[edit]
Note:-
- Subreport and master report must be in the same folder
- Subreport file, ends with '_sub.jrxml', is not displayed in menu list(updated). This feature is used to hide subreport in menu. eg. foo_sub.jrxml
- Subreport name needs to be specified in master page as 'subreportname.jasper'. For subreport file 'foo_sub.jrxml', it should be specified in master page as 'foo_sub.jasper'.
- Parameter, that requires user input, must be declared with attribute 'isForPrompting' as true
- OpenI shows 'defaultValueExpression' text as parameter default value in 'Report Parameter' page, if available.
- OpenI, by default, uses default parameter value, if value is not altered by 'Report Parameter' page.
- Current solution supports following parameter type:
- java.lang.String
- java.lang.Integer
- java.lang.Float
- java.lang.Double
- java.lang.Byte
- java.lang.Long
- java.lang.Short
- java.lang.Boolean
- java.util.Date - using system default locale
[edit]
Need to implement
How to manage static images used by jasper report? currently, full uri needs to be used for static image in report file
