Intelligence on Demand

Aggregation and Transformation

Navigation and Visualization

Presentation and Delivery

Activation and Automation

Elixir Ad Hoc Designer Tutorial Guide for creating reports on the Ad Hoc Server

Activation and Integration

Table of Contents 1. 2. 3. 4. 5. 6. 8.

Introduction............................................................................................................3 Objectives ..............................................................................................................3 Preparing the Exercise Environment .....................................................................4 Preparing the Foodmart-Derby Database...............................................................4 Accessing Available Ad Hoc Report Templates ...................................................5 Creating Ad Hoc Report Templates.......................................................................5 Ad Hoc Report Walkthrough ...............................................................................14

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 2 of 20

1.

Introduction

The purpose of this document serves as a basic self-tutorial guide for users to familiarise themselves with features of the ad hoc design environment available with Repertoire Ad Hoc Server version 7.5.2 onwards.

2.

Objectives

The objective of these step-by-step exercises will assist users in understanding how to create an ad hoc template from a data source plus creating reports on the fly. Topics covered include: • • •

Creating an ad hoc report template Navigation within the ad hoc environment Design walk through using a sample database

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 3 of 20

3.

Preparing the Exercise Environment

This exercise is part of a series of self-help practices and tutorials to get users and designers familiarised with the ad hoc design environment on the Repertoire Server. Each exercise is categorised based on specific features. To get started, create an “ER_Exercises” folder and a “Ad-HocDesigner” subfolder if you have not done so from pervious exercises. Unzip the “Ad-Hoc-Report” folder into “ER_Exercises/Ad-Hoc-Designer” so that the directory structure would be “ER_Exercises/Ad-Hoc-Designer/Ad-Hoc-Report” as shown in the example. Add the “/Elixir” directory to the server repository if it hasn’t been added. If the “/Ad-Hoc-Report” directory was added while the server is running remember to refresh the file system to reflect the changes.

4.

Preparing the Foodmart-Derby Database

To run this exercise users need to download and install a Foodmart-Derby database if they have not already done so. The database can be retrieved here: http://www.elixirtech.com/~adrian/derby/foodmart-derby.zip The Ad Hoc exercise retrieves data from a Mondrian Foodmart which has been ported to Derby for portability. Unzip “foodmartderby.zip” to the Repertoire Server installation directory if this database has not been set up previously for another tutorial exercise.

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 4 of 20

5.

Accessing Available Ad Hoc Report Templates

Ad Hoc Report and Cube templates that have already been created can be accessed from the web UI depending on how the Repertoire Server has been integrated with the application. For this exercise users will directly access the repository folder which contains the available templates. In this case, go to “/Elixir/ER_Exercises/AdHoc_Designer/Ad-Hoc-Report/solution”.

6.

Creating Ad Hoc Report Templates

1. To create an ad hoc report, go to the repository containing the data source to be used. For this exercise it’s “/Elixir/ER_Exercises/Ad-Hoc-Designer/Ad-HocReport/datasources” Click on the required data source, e.g. “Employees-Parameters”.

2. A set of options will appear. Click on the “Create Report” button to create a new ad hoc report template. ___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 5 of 20

3. The ad hoc template wizard will appear with three tabs: A. Report This tab contains the following options to configure the report template: •

Title : Sets the title of the report

•

Timeout : Sets the timeout for the ad hoc report either by session or by a specified time frame

•

Data Source : The path and location of the data source associated with the report template

•

Parameters : Defines any parameters that is propagated by the data source. Refer to the chapter “Dynamic Parameters” in the Elixir Data Designer document for more information regarding parameters.

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 6 of 20

B. Groups This tab allows users to create groupings for the data columns from the data source

C. Fields This section allows users to configure the data columns retrieved from the data source for presentation at the report template level: •

Field : The name of the data column as inferred by the schema in the data source

•

Group : Enables fields to be grouped under a specified heading as defined under the “Group” tabs. For example, an “ID” group can be created and all the fields containing ID (employee_id, position_id, etc) values can be assigned to this group.

•

DisplayName : Allows users to change the label names of the fields for presentation purposes at the template level. This does not affect the naming conventions of the data column names in the data source.

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 7 of 20

•

Alignment : Enables the values to be formatted to appear either in the left, right or centre of the column.

•

Format : Used to format the values of the data for that data field.

•

Show Values : Checking the options for the various columns will display the values in that data column when users perform a filter. Recommended for columns which have repeated values such as “Job Description” or “Management Role” as compared to “Date of birth”.w

4. Click on “Save” then “Actions” to exit from the wizard.

5. Click on “Open” to access the report template.

6. If the data source contains parameter input the report template will display the parameter dialog upon loading. Input the parameters accordingly and click on “Ok” to access the main reporting environment.

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 8 of 20

7. At the top there is the “Actions” button which will bring users back to the template options. Below that is the “Options” button for the implementation of SWF charts in the report template.

There is also an “Export” button to enable users to export the ad hoc report to various output formats. This feature is only enabled when an ad hoc report has been created. Invoking it will call up the following dialog box enabling users to export their ad hoc report to a hard copy. Current export formats include exporting as HTML webpage, an Adobe Acrobat PDF document or a Microsoft Excel (.xlsx) worksheet. Once a format is selected, users can select the path of the exported output. Examples of the various output locations can be found on the following page. Except for HTML exports, both PDF and XLSX outputs can also be sent to either a directory on the server repository or sent out via email.

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 9 of 20

Output Embedded in Browser

Output Embedded in Designer

Output to New Browser Window

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 10 of 20

Send Exported Output to a Repository Folder

Send Exported Output to an Email Recipient

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 11 of 20

Following both buttons are three fields which users can drag and drop the data column labels onto: •

Filters Filters the dataset based on the specified value from the data column. Filter can be a specific value, a range or conditions such as less than, more than, matches, does not match, etc.

•

Groups Groups the dataset based on a specified field or a set of fields.

•

Fields The data columns that will populate the report template.

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 12 of 20

8. On the bottom left are the data columns which can be dragged and dropped onto the above fields. The columns are categorised into three sections: •

Tables Contains the data columns from the data source schema. Columns may be grouped based on the design specified at the wizard level.

•

Derivatives Enables users to implement sets of predefined business logic such as calculating the difference between two columns. These are then saved as derivatives which can be easily dragged and dropped into the “Fields” section.

•

Pre-defined Ad hoc layouts and designs with user specified filters, groupings and fields can be saved as a pre-defined design which can be invoked the next time when the ad hoc template is opened.

9. On the right are three options: • • •

Layout Theme Live Preview When checked, the report template is rendered on the fly as data columns are dropped into their respective fields.

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 13 of 20

8.

Ad Hoc Report Walkthrough

Using the report template created with “/Elixir/ER_Exercises/Ad-Hoc-Designer/AdHoc-Report/datasources/Employees-Parameters.ds”, the following walkthrough will cover the following: • • • • • • •

Drag and drop data fields Filtering Grouping Domain aggregation Creating derivatives Implementing charts Saving pre-defined reports

1. Open the report template. Select “Ok” to accept the default parameters. Expand the “Table” node to access all the data columns from the data source. Observe that there is no “age” column. 2. Go to the left hand panel and expand the “Derivatives” button.

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 14 of 20

3. Click on the “Add” button.

4. Input ”Age” in the “Name” field. In the “Expression” field, enter the following javascript function that calculates a person’s age based on their birthdate: elxfn.dateDiff( new java.util.Date(), birth_date, 'd') / 365 Refer to the javascript functions in both Repertoire and Remote designer for more details and available classes. For “Data Type” select “Integer” from the drop-down menu and select “0” as the “Format”. Lastly, select “Left” for the alignment.

5. Click on ‘Ok’ when done. Observe that “Age” is now available under the “Derivatives” node.

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 15 of 20

6. Expand the “Tables” panel and drag and drop the following datasets onto the “Fields”: - employee_id - full_name - position_title - Age (from ‘Derivatives’) - salary The report template renders accordingly.

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 16 of 20

7. Drag and drop “education_level” into “Groups”.

8. Drag and drop “store_id” into “Filters”. Select the “Range” tab and input the range as from “6” to “17”. Observe the report rendering accordingly.

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 17 of 20

9. Click on the “…” button next to salary. In the pop-up dialog select “Ascending” under “Sort” and “Sum” as “Footer Value”.

10. The report now shows the total sum for each grouping. Click on “Options” again.

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 18 of 20

11. Click on the “Chart” tab. - For “Location”, select “Report Header”. - Click on the “search” button to select the “BarChart.swf” file from the “/Elixir/ER_Exercises/Ad-Hoc-Designer/Ad-Hoc-Report/resources/” folder in the exercise directory. - Select “education_level” as the “Data Key”. - Select “Salary” in the “Values” tab. 12. Click on “Ok” when done.

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 19 of 20

13. If configured correctly the graph will render like the above example. Click to expand the “Predefined” button.

14. Click on the “Add” button. Input “Employee Wage Report” as the title and click “Ok” when done. The report should now appear under the “Pre-defined” section.

___________________________________________________________________________________ Copyright © 2009 Elixir Technology Pte Ltd Page 20 of 20