Birt mongodb param

eric2000

<div>
I installed Birt 4.3 (latest) as Eclipse plugin. JDBC as data source works perfectly for me. I define a param in query. I can define a param in data set via Report Design and link it to a report param.
But I have multiple issues with MongoDB
1) When I open "edit data set" dialog I cannot find the menu "Parameters" in left side. I only see Data Source Query Output Columns Computed Columns Filters properties Binding Setting Review Results
No Parameter any more. I can see it in JDBC not in MongoDB Could anyone please how to define a parameter in data set while Data source is MongoDB? How to link the param to the param of report?
In earlier version I could see the menu "Parameters" in left side for MongoDB as JDBC
 
The attached file shows there was a menu "Parameters" in left side for MongoDB in earlier version. But now there isn't anymore. Why? How to input params?
 
2) In MongoDB (Birt) how to define a parameter in the expression of "Run Database Command" or in query In JDBC we can use "?" as a param holder like
select * where id=?
param will replace "?"
For MongoDB
<pre>
{
runCommand : {
aggregate : COLLECTION_NAME,
.....
}
}
</pre>
COLLECTION_NAME is vary. How can I represent it?
 
I used Jasper who allows us to define some params in query of MongoDB. At run-time it replaces the params with real values. I cannot find the similar way in Birt.
 
Any help will be appreciated.
Thanks
</div>

Find more posts tagged with

Comments

Clement Wong

In OS BIRT 4.3 or commercial iHub3 BIRT Designer Professional's MongoDB Data Source, the Parameters (like for JDBC) was not exposed in the UI. There is an enhancement to add Parameters (TED 75061).
 
Your attached DOC points to a community version of the MongoDB ODA. If you are using that version, you may want to contact the developer regarding that particular ODA.
 
In the meantime, you can still use input parameters with the out-of-the-box MongoDB Data Source. For example, if your Aggregate Expression was looking for a total greater than a given parameter, you can first define the Expression as follows where the 8888 will be replaced in the beforeOpen event.
<pre class="_prettyXprint">
{ $group : { _id : "$state",
 totalPop : { $sum : "$pop" }
 }
},
{ $match :
 {totalPop :
 { $gte : 8888}
 }
}</pre>
Then in the Data Set's beforeOpen, you can read the parameter value and replace the 8888 text in your expression like this:
<pre class="_prettyXprint">
pPopulation = params["PopulationTotal"].value;
this.queryText = this.queryText.replace("8888", pPopulation);
</pre>
Note that if you are using dates,in the $query Expression, builder, the out-of-the-box ODA uses a JSON parser in a strict mode. To use dates in a strict mode (<a data-ipb='nomediaparse' href='http://docs.mongodb.org/manual/reference/mongodb-extended-json'>http://docs.mongodb.org/manual/reference/mongodb-extended-json</a>), we need to use the format:
<pre class="_prettyXprint">
{ "$date": <date> }</pre>
where <date> is the JSON representation of a 64-bit signed integer for milliseconds since epoch UTC (unsigned before version 1.9.1). 
Example:
<pre class="_prettyXprint">
{"orderDate":{"$gt": { "$date": 1369266840962 }}}</pre>
To parameterize this, you can add a Report Parameter such as a integer for relative time period, last 30 days (30), one year (365), five years, etc.... Then, update the $query Expression to hold a substring that will be replaced such as â€œ99999â€. Example: {"orderDate":{"$gt": { "$date": 99999 }}} 
In the beforeOpen of the Data Set, add code that will read the Report Parameter value, compute the integer for milliseconds since epoch UTC, and replace the query expression with the user requested value. 

<pre class="_prettyXprint">
timePeriodParam = params["timePeriodParam"].value;
theMongoDate = (new Date().getTime() - 86400000* timePeriodParam).toString();
this.queryText = this.queryText.replace("99999", theMongoDate);</pre>

eric2000

Thanks for your replay.
According to you, BIRT 4.3 doesn't expose parameters in UI for MongoDB. Could you tell me which version exposed it? 
String replacement is not a good ideal. Do you have other way to do it?
 
Thanks

Clement Wong

There is no version that exposes it. There is an enhancement to add Parameters (TED 75061) for a future version.
 
Currently, string replacement with the parameter value(s) is the only way in the MongoDB Data Source.

eric2000

Hi Clement,
 
Thanks for your reply.
 
You mentioned how modify MongoDB query dynamically in Script.
But I want to do it in Java. I have two questios about it
 
1) In dataSet's beforeOpen, how Java gets the query
 
2)In my report Engine, I don't want to modify query in dataSet level in each report. I want to do it in generic way.
I have 
IReportEngine engine;
IReportRunnable design;
How to get query from here?
 
Any help will be appreciated.
 
Thank you in advance

Clement Wong

You will need to use the Design Engine API (DEAPI) to open the report design file:
<a data-ipb='nomediaparse' href='http://developer.actuate.com/be/documentation/ihub3/help/aig/aig24/index.html#page/aig/Programming.1.19.html'>http://developer.actuate.com/be/documentation/ihub3/help/aig/aig24/index.html#page/aig/Programming.1.19.html</a>
 
Then, you can access the report item via name (in your case, it would be the Data Set):
<a data-ipb='nomediaparse' href='http://developer.actuate.com/be/documentation/ihub3/help/aig/aig24/index.html#page/aig/Programming.1.21.html'>http://developer.actuate.com/be/documentation/ihub3/help/aig/aig24/index.html#page/aig/Programming.1.21.html</a>
 
And then use the setter method of the DataSetHandle, setQueryText(), to update the query text. For reference, the getter method is getQueryText().

eric2000

Hi Clement,
 
Thanks for your help.
 
Do you have any way to find all Data Source and Data set (I don't know their name) based on 
IReportEngine engine;
IReportRunnable design;
 
I have a way:
<div> SlotHandle dataSetHandle = design.getDesignHandle().getDesignHandle().getDataSets();</div>
<div> SlotIterator iter = (SlotIterator) dataSetHandle.iterator();</div>
<div> while (iter.hasNext()) {</div>
<div> DesignElementHandle dsHandle = (DesignElementHandle) iter.next();</div>
<div> </div>
<div> if (dsHandle instanceof OdaDataSetHandle){</div>
<div> OdaDataSetHandle odadataSetHandle = (OdaDataSetHandle)dsHandle;</div>
<div> String query = (String)odadataSetHandle.getProperty("queryText");</div>
<div> ...</div>
<div> odadataSetHandle.setProperty("queryText", newquery);</div>
<div> }</div>
<div> }</div>
<div> </div>
<div>but the second getDesignHandle in design.getDesignHandle().getDesignHandle() is deprecated.</div>
<div> </div>
<div>Thank you again</div>

Clement Wong

Here's a snippet of listing all of a given designs' Data Sources and Data Sets. You should be able to use this to modify the elements per your requirements.
<pre class="_prettyXprint">
SessionHandle session = engine.newSessionHandle( ULocale.ENGLISH ) ;

ReportDesignHandle design = null;

try {
 design = session.openDesign("MyReport.rptdesign" );

 //Get and list all Data Sources
 SlotHandle sh = design.getDataSources();
 Iterator it = sh.iterator();
 while( it.hasNext() ) {
 DesignElementHandle de =(DesignElementHandle)it.next();
 System.out.println(de.getName());
 }

 //Get and list all Data Sets
 sh = design.getDataSets();
 it = sh.iterator();
 while( it.hasNext() ) {
 DesignElementHandle de =(DesignElementHandle)it.next();
 System.out.println(de.getName());
 }
}</pre>