nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Can we use Stored Procedure dataset in BIRT for Maximo?

adrianfcc

I need multiple steps of data processing before getting what the report should display. As I am more familiar to have the database rather than javascript to do complicated data works, I would like to write a stored procedure to have all those done by SQL Server and return a simple result set for the report. The IBM BIRT Designer 2.3.2 for Maximo 7.1.1.5 seems only can create "Scripted Data Set". Is it possible to create "Stored Procedure Data Set"?

Find more posts tagged with

Comments

Virgil Dodson

Hi adrianfcc,

If you create a new JDBC data source you should be able to create a new Data Set then select Stored Procedure on the first screen... at least in Eclipse version of BIRT 2.3.2.

adrianfcc

Thanks vdodson. But the "maximoDataSource" is the only datasource that is supposed to be used for Maximo. I haven't tried to create another one because even that works it will only work in the report designer. Once the report has been published and run in Maximo, it will only be provided a datasource from Maximo at runtime. Have you tried it before?

SpaceMan

I have used mulitple stored procedures in my reports. Just use a scripted data source and call the procedure from it.

select [procedure] from dual

that should probably work.

If you need more help let me know.

siva_birt

I tried this. No data is returned. No errors. I have a store proc called rfqreview(@rfqnumber (varchar(15)).
Here is my code in dataset open method.
spDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
spDataSet.open();
var sqlText = new String();
sqlText = " select rfqreview '" + rows[0]["rfqnum"] + "' from dual"
;
spDataSet.setQuery(sqlText);
Can you please correct this code.

In Fetch method
if (!spDataSet.fetch())
return (false);

// Add a line for each output column
// The specific get method should match the data type of the output column.
row["status"] = spDataSet.getString("status");
row["transdate"] = spDataSet.getTimestamp("transdate");
row["modifiedby"] = spDataSet.getString("modifiedby");
row["memo"] = spDataSet.getString("memo");

return (true);

SpaceMan

What does rows[0]["rfqnum"] return?

It looks like your syntax is wrong.

sqlText = " select rfqreview '" + rows[0]["rfqnum"] + "' from dual"

Also, in your fetch method, you try to grab fields you did not select from your SQL Text. You need to select all the fields you plan to get in your fetch method.

Make sure to place these fields in the Output columns too.

If you want, you can post your report (if it's in 2.1.2) and i'll try to look at it.

siva_birt

In fetch method, those are the values from stored procedure called rfqreview(@rfqnumber). rows(0)["rfqnum"] is the input parameter to the procedure.

SpaceMan

Are you missing the parenthesis for your parameter?

What I do for my debugging on the query is either look at the log or I create a dynamic text box on the report and make it my query text.

To create a log place the following statement in the Initialize Script (click on the report from the navigator then click on script).

mxReportScriptContext = MXReportScriptContext.initialize(reportContext);
mxReportScriptContext.setDefaultLogLevel("DEBUG");
mxReportScriptContext.setDefaultLogFile("C:/temp/myreport.log");

siva_birt

Thanks. It seems my datasource is looking into different database. Now it is working.

Preethisha

Hi!

I got the steps to create Library in BIRT 2.1.2. I could insert a dataset in that library. After that howhere to enter the query? Usually, the select query will be added in the open method of the
dataset. But here no open method only. I need the clear steps for creating a dataset
and using it in a report design.

Thanks & Regards,
Preethisha