Adding a BIRT Report to Rosetta

Rosetta comes with a reporting database schema that can be used to extract data and create custom reports with BIRT. General instructions on how to set up your environment and deploy your report are provided here, and should be consulted before reading this post (or building your own report). To take full advantage of the options BIRT provides, it’s also a good idea to familiarize yourself with BIRT through these tutorials and other literature readily found on the internet. In this post we will demonstrate how to create a new report. For this example we will create a report of intellectual entities (IEs) viewed over the past 30 days.

I’ve set up my environment and I have my dpslib.rptlibrary file as a shared resource. This file contains the connection information to the Rosetta database. I will be using its Reporting_Schema data source in my report.

I’ll select New > Report, name my report ies_viewed_30_days.rptdesign, and use a blank template.

Now, I’ll add the Reporting_Schema data source to my report by right-clicking and selecting Add to Report.

If I open the XML Source view tab, I should be able to see the reference to my data source. It should look like this:

<data-sources>
        <oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Reporting_Schema" id="6" extends="dpslib.Reporting_Schema"/>
</data-sources>

I’ll save the report and move to the Data Explorer tab. Right-click on Data Sets and add a new Data Set.

At this point I am prompted to provide a query. I have two options: If I already know the query I need to retrieve the data for the report, I can paste it in the box. If not, I can browse the reporting schema by selecting the views in the RPT00 schema (more information on these tables is available in the Rosetta Staff User Guide).

In this case I already have my query:

SELECT count(EVENT_ID_NUMBER) AS "NUMBER_OF_VIEWS", value as "IE_PID"
FROM events e, event_keys k, hdecontrol_view v
WHERE e.event_date BETWEEN TRUNC(SYSDATE) -30 AND TRUNC(SYSDATE) AND e.EVENT_ID_NUMBER = k.event_id AND e.event_type = 160 and v.pid = k.value and (v.owner = ? or v.owner like ?)
GROUP BY value ORDER BY number_of_views DESC

Notice the ‘owner’ in my query: this will allow me to filter the IEs by institution so that the report will only contain relevant IEs when accessing from Rosetta’s interface (a filter that you will likely want to apply to any report). Each of these question marks represents one parameter. In order to work with these parameters I’ll need to add more information. BIRT will ask me to provide this information once I save my data set, but before I can do this, I’ll need to add a report parameter. So for now I’ll just click OK, and to add the report parameter I right-click on Report Parameters, and select New Parameter:

I’ll create a report parameter called ‘inst’. The rest of the properties should be set as follows:

Now that I have the report parameter, I need to go back to my data set and add my two parameters. The easiest way to do this is to edit the XML Source directly. Under the line <property name=”dataSource”>Reporting_Schema</property> I insert the following:

<list-property name="parameters">
   <structure>
    <property name="name">param_1</property>
    <property name="paramName">inst</property>
    <property name="dataType">string</property>
    <property name="position">1</property>
    <property name="isInput">true</property>
    <property name="isOutput">false</property>
   </structure>
   <structure>
    <property name="name">param_2</property>
    <property name="dataType">string</property>
    <property name="position">2</property>
    <expression name="defaultValue">if (params !=null && params["inst"] !=null && params["inst"].value != null) {BirtStr.concat(params["inst"].value,'.%');}
else {'%';}</expression>
    <property name="isInput">true</property>
    <property name="isOutput">false</property>
   </structure>
  </list-property>

I can now open my data set and preview my results:

The next step is to build the report layout. In layout view, right-click on the blank page, I select Insert > Table and provide my table details:

Drag and drop each of the data set columns into Detail Row. In the footer, I’ll add a total sum of all views by adding a data field with this expression: Total.sum(row[“NUMBER_OF_VIEWS”]):

I can now preview my report:

Before I can copy this over to Rosetta, I need to add the script and other configuration that will take the institution from the user’s session. In the XML source, I locate the following line:

<list-property name="libraries">

and above I it add the following:

<simple-property-list name="includeResource">
<value>DPSreports</value>
</simple-property-list>
<html-property name="description">Provides instructions for building a customer listing report.</html-property>
<method name="initialize">
<![CDATA[importPackage(Packages.com.exlibris.core.infra.common.security);
    if (UserPrincipalRetriever.getInstitutionPathNoException() !=null){
       params["inst"] = UserPrincipalRetriever.getInstitutionPathNoException();
}else {
       params["inst"] = '%';
}]]>
</method>
<property name="theme">dpslib.defaultTheme</property>

After saving the report, I’ll ask my system admin to add it to the report library and update Rosetta’s BIRT Management Reports mapping table with the report’s name and the area I want it to appear in – in this case data management – as explained in the general instructions.

The rptdesign file is available here.