Excel Formulas - Helpful in BIRT Reports

 

• BIRT reports are widely used in Maximo and sometimes require to build reports with many columns and field mapping.

• I’ve been using excel formulas to generate scripts for Open, Fetch method

• These are simple text formulas, yet useful for working with reports with many columns.

Open Method:

To Convert 'Select Query' for Open Method

Enter the SQL query as multiple lines in Excel sheet. Example: column A

Apply the formula in column B as below.

For First row only:

=CONCAT("var sqlText=""",A1,"""")

For other rows:

=CONCAT("+ "" ",A2,"""")

=CONCAT("+ "" ",A3,"""")

=CONCAT("+ "" ",A4,"""")

..

...

Note: The above formula does not add/append the params["where"] or other parameters. Manually edit before using it.

Fetch Method:

To Generate Fetch method field mapping scripts.

Copy the BIRT report dataSet field names to column A in excel sheet

Copy the Select Query Field names from 'Open method' to column B in excel sheet

Enter the formulas in Column C as below to generate Script of Fetch method

=CONCAT("row[""",A2,"""] = maximoDataSet.getString(""",B2,""");")

=CONCAT("row[""",A3,"""] = maximoDataSet.getTimestamp(""",B3,""");")

=CONCAT("row[""",A4,"""] = maximoDataSet.getInteger(""",B4,""");")

Extract 'Select Query' from 'Open Method’ sqlText:

Copy the value of sqlText Variable from Open method to 'A1' cell in column in excel sheet.

Apply the below formula in another cell. This will convert the sqlText to executable query.

=SUBSTITUTE(SUBSTITUTE(A1,"+ """,),"""",)

 

Dynamic BIRT Report - Utilizing HTML Text

·        BIRT report has ‘Text’ block control, that supports HTML code. This is generally used in formatting report data but can be improved to work like interactive web page.

·    Here is an example of using this feature to call a function (some calculation) and display the result in output column.

·    Same functionality could be achieved in simpler ways. But this document is meant to explain the ’HTML Text’ + ‘JavaScripting’ features of BIRT report.

Click Here for more full document.