Search code examples
jasper-reportsjasperserver

How to query domain in jaspersoft with Dynamic Parameters

I am new to jaspersoft reporting. I am currently designing and developing reports by considering following requirements. I want to create template based reports where all dynamic parameters I need to pass in SQL query. I was going through japsersoft reporting I found that we can create join views and cache data by creating domains. So that it reduces hits at db level.

While creating report I found that I cant execute SQL script on Domain objects.

Please advice whether I am on right track or not. Basically I want to query on cached data such as domain view instead of hitting DB directly.

Please suggest if any workaround is available for this problem.

Solution

  • Please note, although JasperReports Server manages a cache for Ad Hoc Views and Ad Hoc Reports running on Domains, running a JRXML report (e.g. designed in Jaspersoft Studio) on a Domain does not guarantee hitting that cache.

    You also have the option of using a layer that provides caching between JasperReports Server and your database. For example, support has been recently added for TIBCO Data Virtualization (not a free product) in v.7, see https://www.jaspersoft.com/introducing-jaspersoft-7.

    In any case, Domains are not relational databases and therefore do not support straight SQL. You can use the "Domain query language" though, which offers a subset of the features of SQL. The easiest way to write a query is using Jaspersoft Studio and selecting "domain" in the Language dropdown (top-left corner of the Dataset and Query Dialog, indicated by the red arrow in the screenshot below from Studio 6.4.0):

    Screenshot of Dataset and Query Dialog in Jaspersoft Studio

    For example the design above (which uses the Supermart Domain, provided with the sample data) will generate this query and the required "dynamic" parameter as you requested – in this case a Collection as the filter is 'Is One Of' which can take multiple values:

    <query>
    <queryFields>
        <queryField id="sales_fact_ALL.sales__product.sales__product__product_name"/>
        <queryField id="sales_fact_ALL.sales_fact_ALL__store_sales_2013"/>
    </queryFields>
    <queryFilterString>sales_fact_ALL.sales__store.sales__store__region.sales__store__region__sales_country in sales__store__region__sales_country_0</queryFilterString>
</query>

    See here for another example of a query (current version of docs based on 7.1.0 release), in this case for use with the REST API: https://community.jaspersoft.com/documentation/tibco-jasperreports-server-rest-api-reference/v710/queryexecutor-service

    The queryFilterString tag follows the DomEL syntax as documented here (also for 7.1.0): https://community.jaspersoft.com/documentation/tibco-jasperreports-server-user-guide/v71/domel-syntax