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,"+ """,),"""",)