Tivoli Common Reporting: Customizing Reports

1. Tivoli Common Reporting: Customizing Reports TCR Enablement

2. Customize/Create ITM Agent Reports • Install BIRT designer • Download product report package from DVD/OPAL • Import the reports into BIRT • Import TCR style package (for creating new reports only) • Modify BIRT settings • Configure data source • Edit/Create reports • Export reports into zip file • Import back into TCR TCR BIRT Designer

3. BIRT Designer • BIRT is an open source tool, based on the popular Java based Eclipse development platform. • BIRT 2.2.1 is the base reporting engine for TCR v1.2 • Minimum requirements: • Java Runtime Environment (JRE) 1.5 • Eclipse 3.3.1 • BIRT 2.2.1 • Obtaining BIRT: • Download BIRT 2.2.1 from OPAL http://www-01.ibm.com/software/brandcatalog/portal/opal/details?catalog.label=1TW10OT02&S_TACT=105AGX01&S_CMP=LP • BIRT Education Materials: • Books: • BIRT: A Field Guide to Reporting, by Diana Peh, Alethea Hannemann and Nola Hague. Addison-Wesley, 2006. ISBN 0-321-44529-8. • Integrating and Extending BIRT, by Jason Weathersby, Don French, Tom Bondur, Jane Tatchell and Iana Chatalbasheva. Addison-Wesley, 2006. ISBN 0-321-44385-3. • Forum: http://www.eclipse.org/birt/phoenix/community.php • Tutorial: http://www.eclipse.org/birt/phoenix/tutorial/ • Demo: http://www.eclipse.org/birt/phoenix/examples/

4. Getting TCR and the Report Package • TCR • Standalone (included in their product DVD by updating their BOM like ITM 6.2.1) • Embedded component in TIP (by adopting TIP like TBSM 4.2) • Out of the box reports • OPAL • Bundled with the GA’d product • In product DVD • On product’s support site • Unzip the report package into the file system. • It will create two folders : <product name> and tcr_metadata Report Catalog on TCR website

5. Importing Reports into BIRT Designer • Create two new BIRT Report Projects (<product name> and tcr_metadata) Note: tcr_metadata is required if you want globalization and the product’s report set structure to be preserved in the TCR report set navigation tree • Switch to Report Design perspective • Right click on <product name> -> Import -> File System • Browse to the <product name> folder in the unzipped reports package • Check the <product name> box in the Import dialog and click Finish • All report designs (.rptdesign files) and resources folder will be imported • Right click on “tcr_metadata” -> Import -> File System • Browse to the “tcr_metadata” folder in the unzipped reports package • Check the “tcr_metadata” box in the Import dialog and click Finish After import Navigator view

6. Import TCR Style Package (required only if creating new reports) • If you are going to create new reports the TCR style package needs to be imported to avail the in-built templates, styles and commonly used scripts • TCR Style Package can be found in <tip-install>\products\tcr\style folder along with the style guide • Unzip the TCR Style Package v1.2.zip file into your local machine • Create a new report project called TCR Style Package • Right click on this project and select Import -> File System • Browse to select the unzipped TCR Style Package folder • Check the boxes for all (templates, samples, resources) in the Import dialog and click Finish

7. TCR Style Package Outline (TCR Style Package obtained from Style folder of product DVD) Library (reusable report items) • Styles • Images (standard icons, logos etc) • Message dialogs • Master Page (header, footer) Samples • CascadingParameters • ChartsWithDrillthrough • DateRangeParameters • DynamicChartDimensions • DynamicColumnSort • DynamicQuery • ExpandCollapseSections • MessageDialogs • ParameterValidation • QuickLinks • StatusBasedColoringOfCharts • TableFooterWithTotals • TableUsingStatusIcons • TableWithDrillthrough Templates • TCR_Table • TCR_Chart • TCR_ChartTableCombo • TCR_GroupTable(1_group) • TCR_GroupTable(2_groups) • TCR_GroupTable(3_groups)

8. Modify BIRT Settings • Modify Resources to point to resource folder in Product Reports project • Go to Window -> Preferences • Expand Report Design • Select “Resource” and point resources to the resources folder in your project in the workspace. E.g., “<workspace_location> /ITM Reports/resources” • Modify Templates to point to TCR Style Package (only needed if creating new reports) • Go to Window -> Preferences • Expand Report Design • Select “Template” and point to the templates folder in your TCR Style Pacakge project in the workspace. E.g., “<workspace_location> /TCR Style Package v1.2/templates”

9. Open a report design in Layout View to edit Layout View: Shows graphical outline of the opened report Outline View: Shows outline of the opened Report Navigator: Browse through various report design projects

10. Common BIRT Terminologies • Use BIRT v2.2.1 only • Report Design (rptdesign) • Data source: JDBC, scripted, web services, XML, flat file • Data set: Result set of a query • Report Parameter: textbox, radio button, combo box, list box (dynamic lists supported) • Body – Report Items: Data, Text, Label, Dynamic Text, Image, Grid, Table, List Chart • Master Page: Layout of every page of a report • Style/Theme: Colors, fonts, margins etc • Library: Stores reusable report items • Script: Javascript functions to control report item generation/modification dynamically • Template: Static structure for a standard report layout • rptdesign, rpttemplate, rptlibrary, rptconfig, rptdoc

11. Anatomy of a TCR Report • A header for company branding (part of Master Page) • A primary report title • Optional secondary titles • Parameters used to generate the report • Information sections which contain the content of the report in tables and charts • A description • A footer containing the generation date and time of the report and a page number (part of Master Page)

12. Configure Data Source • A data source is the database from which data is pulled to generate a report. Data sources are defined in the product library and referenced in the report design. The advantage of placing data sources in the library instead of in the individual report designs is that changes made to the library will be reflected across all report designs. • To edit the data source: • Navigate to <product name>/resources/<product name>/lib/ and double-click the <product name>.rptlibrary file. The contents of the library open in the Outline view. • In the Outline view, expand Data Sources and double-click to open the primary data source i.e., ITM • Select or add the appropriate driver. Click on “Manage Drivers”. If you jar is not available, click the “Add” button. Browse to pick up the jdbc driver jar for your database. Click OK to finish. Now select your driver from the Driver Class drop down select. • Enter the database URL and authorization credentials, or the JNDI URL • Click the Test Connection button verify communication with the database.

13. JDBC Configuration Example

14. Create/Edit Reports • Edit existing report: Set a filter/Change logo • Create new report based on a TCR template with data from the warehouse • Right click on the project and select New Report • Enter a Report name and click Next • Select a pre-defined TCR template. Click Finish • Once the report is open in the layout view, right click on “Libraries” in the Outline View and click Use Library. Select the ITM library from the resource folder • From the Library Explorer view browse to ITM under data sources in the ITM library. Drag this and drop it in Data Sources in the Outline View. The data source is now available in the report, referencing the one in the library to query for generating reports. • Right click on Data Set in the outline view and click New Data Set. Select ITM for the Data Source and click Next • The tables in the Warehouse will show up. Browse through the tables and columns to construct a SQL query. Preview the results • Drag and drop the data set into the report layout to create a table • Edit the chart to point to this data set

15. Create/Edit Reports • Converting candle timestamps to date time objects: • Open the data set and browse to Computed Columns in the data set editor • Click on the New button and enter the following: • Column Name: TimestampC • Data Type: Date Time • Expression (click the fx button to enter the expression): new Date( ( candleTime=row["WRITETIME"], (parseInt(1900) + parseInt(candleTime.substr(0,3))) + "/" + candleTime.substr(3,2) + "/" + candleTime.substr(5,2) + " " + candleTime.substr(7,2) + ":" + candleTime.substr(9,2) + ":" + candleTime.substr(11,2) )) • Filtering queries with date ranges: • From the Library Explorer browse to Shared Libraries/tcr_common/lib/Report Parameters. Drag and drop the “Parameter Group - Date Range” from there to the Report Parameters in the Outline view. • Open the data set which you want to filter by date and add the following to the query: • where "WRITETIME" between '0000000000000000' and'9999999999999999' • Save it and close the data set. Click on it from the outline view and go to the Scripts tab. Enter the following code in the scripts tab: ModifyQueryCandleDateRangeFromGroupValues(this,params["Report Period"].value,params["Begin Date"].value, params["End Date"].value); This method is defined in tcr_common/scripts/ModifyQuery.js. It will take the input parameters, do the appropriate calculations, convert them into candletime and substitute the '0000000000000000' and '9999999999999999' in the query

16. Modify TCR MetaData (optional) • TCR Metadata defines where a report set will be placed in the navigation tree, set mappings, globalized names and descriptions etc • Either you can import with metadata or you can import without metadata and edit the names, descriptions and reorganize the reports into sets through the TCR GUI (preferred) • To edit metadata, open reportdata.xml in eclipse and edit through the design view

17. Exporting Report Project into Zip File • Right click on your project in the Navigator View • Click Refresh to update project. • Right click on your project again and Click on Export (if exporting with tcr_metadata, select both projects to export) • In the next dialog select “Archive File” under “General”. Click Next • Browse to enter the location where you want to export the file to. Enter filename with extension. Click Finish.

18. Importing Report Package into TCR Errors during import may be caused due to incorrect packaging structure. Check SystemOut logs for more details Importing Single Report Design into TCR Through the CLI • Import resources (if new resources) trcmd –import –resources .. • Import design, define report and add to set trcmd –import –design .. –designRef.. -resourceDir.. –designType birt .. –report.. –reportSets *use –force to overwrite

19. Start Importing Report(s) back into TCR after Customization Export BIRT report project into zip file (as explained in previous slide) Import single report? no yes Resources modified? Import resources trcmd –import –resources .. –location .. –resourceDir .. -force Want to include meta-data information? yes yes no no Add tcr-metadata folder to the exported zip file New rptdesign has same name as old rptdesign? Replace design directly in datastore OR trcmd –import –design .. –designRef .. –designType birt -force yes no Replace an existing report set ? yes no Create new report set trcmd –add –reportSet .. Add new report to new report set? yes Import zip file into TCR using Import Package Menu through GUI with overwrite or CLI using the following command: trcmd –import –bulk .. -force no Import design, define report and add to set trcmd –import –design .. –designRef.. -resourceDir .. –designType birt –report.. –reportSets .. Import zip file into TCR using Import Package Menu through GUI or CLI using foll. Command” trcmd –import –bulk .. Stop

20. Resources • Tivoli Common Reporting Website (search online for “Tivoli Common Reporting”) • http://www.ibm.com/developerworks/spaces/tcr • Forums • Articles • Report Catalog • TCR Users Guide • Part of the TCR DVD • How to use TCR • Complete command reference • TCR Style Guide • Present in the “Style” folder of the TCR DVD • Using TCR Style Package to create reports • Sample reports and templates

21. Thank You!