Qualifacts Systems, Inc.

Ad Hoc Report Designer Proprietary Information – For Use by Qualifacts and Our Customers Only

Table of Contents Introduction ................................................................................................................................ ................................ .................................... 1 Accessing Report Designer ................................................................................................ ................................ .................................................. 1 Report Structure ................................................................................................ ................................ ................................................................. 1 Report Designer Metadata Model ................................................................................................ ....................................... 2 Metadata Documentation ................................................................................................ ................................ ................................................... 3 Object Model PDF................................ ................................................................................................ ............................................................ 3 Data Dictionary ................................................................................................ ................................ ............................................................... 4

Report Designer Installation ................................................................................................ ................................ ........................................... 6 Using the Report Wizard ................................................................................................ ................................ ................................................. 7 Accessing Report Wizard ................................................................................................ ................................ ..................................................... 7 Choosing a Layout ................................................................................................ ................................ ............................................................... 7 Set the Data Source................................................................................................ ................................ ............................................................. 8 Creating the Report Query ................................................................................................ ................................ .................................................. 8 Choosing Data to be available in the Report ................................................................ .................................................... 8 Create the Report Layout ................................................................................................ ................................ .................................................. 10 Create Title Elements ................................................................................................ ................................ ........................................................ 11

Report Designer Structure ................................................................................................ ................................ ............................................ 12 The Report Designer Main Toolbar ................................................................................................ .................................... 12 Report Designer's Tabbed Views ................................................................................................ ....................................... 12 The Report Workspace ................................................................................................ ................................ ...................................................... 13 The Structure Pane................................................................................................ ................................ ............................................................ 14 Master Report or sub-report report ................................................................................................ .......................................... 14 Page Header ................................................................................................ ................................ ................................................................. 14 Report Header ................................................................................................ ................................ ............................................................... 14 Groups ................................................................................................................................ ................................ .......................................... 14 Details................................................................................................................................ ................................ ........................................... 14 No Data ................................................................................................................................ ................................ ........................................ 14 Report Footer ................................................................................................ ................................ ................................................................ 14 Page Footer................................................................................................................................ ................................ ................................... 14 Watermark ................................................................................................................................ ................................ ................................... 15 The Data Pane ................................................................................................................................ ................................ ................................... 15 Data Sets................................ ................................................................................................................................ ....................................... 15 Functions ................................................................................................................................ ................................ ...................................... 15 Function Reference................................................................................................ ................................ ........................................................ 15 Common Functions................................................................................................ ................................ ........................................................ 15 Report Functions ................................................................................................ ................................ ........................................................... 15 Summary Functions ................................................................................................ ................................ ....................................................... 16 Running Functions ................................................................................................ ................................ ......................................................... 16 Advanced Functions ................................................................................................ ................................ ...................................................... 17 Chart Data Functions................................................................................................ ................................ ..................................................... 17 Image Functions ................................................................................................ ................................ ............................................................ 18

Creating a Sparkline Chart ................................................................................................ ................................ ............................................. 20 Script Functions ................................................................................................ ................................ ............................................................. 20 Deprecated Functions ................................................................................................ ................................ .................................................... 21 Parameters ................................................................................................................................ ................................ ................................... 21 Adding a Parameter ................................................................................................ ................................ ...................................................... 21 The Style Pane................................ ................................................................................................................................ ................................... 22 Style Properties Reference ................................................................................................ ................................ ............................................. 22 Font Styles................................................................................................................................ ................................ ..................................... 22 Text Styles ................................................................................................................................ ................................ ..................................... 22 Text Spacing Styles ................................................................................................ ................................ ........................................................ 23 Padding Styles ................................................................................................ ................................ ............................................................... 23 Object Styles ................................................................................................ ................................ ................................................................. 23 Size & Position Styles ................................................................................................ ................................ ..................................................... 24 Links Styles ................................................................................................................................ ................................ .................................... 24 Excel Styles ................................................................................................................................ ................................ .................................... 25 Sparkline Styles ................................................................................................ ................................ ............................................................. 25 Page Behavior Styles ................................................................................................ ................................ ..................................................... 25 Border Styles ................................................................................................ ................................ ................................................................. 26 The Attributes Pane ................................................................................................ ................................ .......................................................... 28 The Palette ................................................................................................................................ ................................ .................................... 28

Working with a Report ................................................................................................ ................................ .................................................. 29 Adding Report Elements................................................................................................ ................................ .................................................... 29 Adding Standard Design Elements ................................................................................................ ..................................... 30 Aligning Elements................................................................................................ ................................ .............................................................. 31 Adding Bands ................................................................................................................................ ................................ .................................... 31 Creating Subreports ................................................................................................ ................................ .......................................................... 31 Adding Hyperlinks ................................................................................................ ................................ ............................................................. 32 Paste Formatting ................................................................................................ ................................ ............................................................... 32 Morphing an Element................................ ................................................................................................ ........................................................ 33 Implementing Row Banding ................................................................................................ ................................ .............................................. 33 Performing Calculations ................................................................................................ ................................ .................................................... 33 Using the Formula Editor ................................................................................................ ................................ ............................................... 33 Customize Reports: Common Tasks ................................................................................................ ................................... 34 Adjusting the Column Headings (Details Header) ................................................................ ........................................... 34 Format the Details Band ................................................................................................ ................................ ................................................ 34 Adjusting the Report Title ................................................................................................ ................................ .............................................. 35 Insert Page Breaks................................................................................................ ................................ ......................................................... 36 Add Report authorship ................................................................................................ ................................ .................................................. 36 Adjust Heading & Data Width ................................................................................................ ....................................... 37 Add a Function (example: e: Running Count) ................................................................ ..................................................... 37 Add an additional Data Element to the Report Report................................................................ ............................................... 38 To add a Summary (subreport) ................................................................................................ ...................................... 38 To show and report by a specific Data Element................................................................ .............................................. 39 Edit Grouping ................................................................................................ ................................ ................................................................ 39 Create Function ................................................................................................ ................................ ............................................................. 39 Edit a Subreport ................................................................................................ ................................ ............................................................ 39 Add another condition ................................................................................................ ................................ ................................................... 39 ii

|

Page

Creating a Date Parameter................................................................................................ ............................................ 40

Using the Report ................................................................................................ ................................ ........................................................... 41 Run the Report ................................................................................................................................ ................................ .................................. 41 Save the Report................................ ................................................................................................ ................................................................. 41 Publishing and Managing Access to Reports ................................................................ ...................................................... 41 Running Reports via User Console ................................................................ ................................................................. 42 Manage Access to the Report ................................................................................................ ........................................ 42

User Console ................................................................................................................................ ................................ ................................. 43 Accessing User Console ................................................................................................ ................................ ..................................................... 43 Via web browser ................................................................................................ ................................ ........................................................... 43 Via CareLogic ................................................................................................ ................................ ................................................................ 43 The Quick Launch Bar ................................................................................................ ................................ ........................................................ 44 Build a Report with User Console ................................................................................................ ...................................... 44 Share Report ................................................................................................................................ ................................ ..................................... 47

Reference Materials ................................................................................................ ................................ ...................................................... 48 Pentaho Website................................ ................................................................................................ ............................................................... 48 Date Formatting ................................................................................................ ................................ ................................................................ 48 Functions ................................................................................................................................ ................................ .......................................... 49

iii

|

Page

Introduction CareLogic provides a powerful reporting tool to help you answer specific questions questi using the data it contains. The Ad Hoc Report Builder provides basic, intermediate, and advanced methods for identifying, collecting, filtering, constraining and laying out o information nformation based on your needs. Reports that you create can be run time and time again to help you identify and develop information about trends The Report Designer application supports complex report layouts, complex queries, and publishing for use via the User Console. This guide is an introduction to Report Designer and provides users with all the basic tools needed to begin creating and generating Adhoc reports. This guide demonstrates: • • • • • •

Understanding the Report Designer layout. Accessing Report Wizard. izard. Selecting data to include in the report. Creating the report eport layout. Running basic reports. Sharing reports with other CareLogic users.

Accessing Report Designer CareLogic’s Ad Hoc Report Builder is a reporting tool based on the Pentaho Business Intelligence (BI) Platform. There are two modes of access access. The following table illustrates the capabilities of each mode: User Console

Report Designer

Web-based based wizard driven

Desktop application

Simple layout, limited options

Highly customizable layout layou

Simple to very complex queries

Simple to very complex queries

Preview function

Preview function

Publishes for use via User Console, or as standalone PDF, XLS, CSV, HTML

Publishes for use via User Console, or as standalone PDF, XLS, CSV, HTML

Executes reports ts published in Report Designer FIG 1:

Comparison table for Report Designer modes. modes

Report Structure The relative placement of data elements on the page is a function of the report elements. Some report elements can repeat on following pages but most only appear in one place. Here is an illustration of how the report elements display:

FIG 2:

Report elements

Report element definitions: Element Page Header: Report Header: Group Header: No Data: Details:

Details Footer: Group Footer: Report Footer: Page Footer:

Watermark:

Definition Prints on every page. Prints before any content is printed on the page Prints only on the first page Prints as the header for a group Prints when the report returns no data only Prints data rows. If no details/items band is defined, only printing is disabled. All calculations are performed regardless less of the appearance of the items. Accepts the same attributes es as all other bands. Prints as the footer after each details section Prints as the footer for a group Prints only on the last page Prints on every page. Prints after the last content for the page is printed. The page footer is always positioned at the bottom of a page, regardless how much space of the page is filled The watermark is a page element that is rendered behind behi all other elements. This band can consume the complete space of the page and will never trigger a page-break. The watermark watermark-band is intended to fill the page background FIG 3:

Report element definitions

Report Designer Metadata Model Understanding the Metadata ata model is key to fully utilizing Report Designer. CareLogic information is contained in tables organized around related information. Tables are also relationally linked to provide a robust database with easy input procedures. Accessing database information ation is done through queries. The Metadata etadata model allows visual building of the query, acting as a translation layer between the report information and the database language.

2

|

Page

` CareLogic

Internet

Internet Production

Metadata

Report

` User Console

Publish Report

` Report Designer

FIG 4:

The Role of the Data Model

Both the User Console and Report Designer access data da via Metadata files. The files include real-time real data from the Production database, and the structure of the data is based on the Data Models.

Metadata Documentation Using the Metadata effectively requires an understanding of data elements and table relationships. rel Since Qualifacts is continually updating the database, the most current information is made available in the documents on Qualifacts Connect. There are 2 key files for referencing the structure of the Metadata to locate data elements, elements the Object Model PDF and the Data Dictionary Excel file. file

Object Model PDF The Object Model PDF shows all tables and columns in a graphical and relational format. The relationships between data elements are also shown. Use the Object Model to determine which tabl table a desired data element is in and if it can be referenced with another element in a different table.

3

|

Page

FIG 5:

Object Model

The steps to use the Object Model: Step

Action

1

Open the PDF file in a reader with search capabilities

2 3

Enterr the name of the data you need in the search field (Ex: “Client co-pay”) pay”) Press Enter to find the incident of the data

4

Use the forward and backward arrows to locate other instances

5

As you review the graphical map and relationships, make note of the data needed FIG 6:

Using the Object Model file

Data Dictionary The Data Dictionary is used for both explanation and reconciliation. First, any field that includes logic is documented – for example, Mailing Address Line 1 for a Client. In addition, the CareLogic location can be used to determine where any data element in the user-interface user interface is located in the model.

4

|

Page

FIG 7:

Data Dictionary

The steps to use the Data Dictionary: Step

Action

1 2 3 4

Open the XLS file Click Edit, Find Enter the search term for the data Review the instances. instances Note the Table, Column, Format,, CareLogic Location and any comments and links Verify the data is the data required for the query When you are working in User Console or Report Designer, knowing where information is located in CareLogic helps you select the correct table and column

5 6

FIG 8:

Using the Data Dictionary

5

|

Page

Report Designer Installation There are steps that must be performed to install the Report Designer locally on your machine. Once installed, the connection to CareLogic CareLo will happen automatically. Note: Java must be installed for Report Designer to work. Contact your IT staff to verify you already have it or to get it installed. The CareLogic AdHoc Report Designer is accessible to two users on the account, unless other arrangements are made. Access Access is controlled through Qualifacts’ secure FTP site, which requires a login. If you do not have a login, begin the process by contacting [email protected]. [email protected] They will send you a request form to fill out and return. After you return the request form they will set up access and email your sftp access credentials. Upon receipt follow the steps steps below to download and install: Step 1

2 3

Action Retrieve the ZIP file - Go to https://sft.qualifacts.org.. This is the Qualifacts site for secure file transfers. Login with your credentials – provided via separate ee-mail. Click on the Folders link in the top left list and navigate to the folder Home/Clients/Distribution Download the ZIP file locally, noting the file location Home/Clients/Distribution. Unzip the ZIP file - Access the local ZIP file. Extract the contents of the file a directory. Qualifacts recommends C:\Program C: Files Create Shortcut & Start Report Designer - Right-click click on the *.bat file and create a shortcut. Find the shortcut file. Cut & paste to your preferred location. Qualifacts recommends your Desktop. Double-click click on the shortcut to start Report Designer FIG 9:

Installing Report Designer

6

|

Page

Using the Report Wizard The wizard builds a report in five steps. These se steps will take you through the process of choosing a template, selecting a data source, configuring the data for the report, and then finalizing the report layout.

Accessing Report Wizard When Report Designer is first opened a welcome screen appears. Selecting Report Wizard starts the wizard sequence.

FIG 10:

Report Designer Welcome screen

Report Wizard can be accessed at any time through the File menu.

Choosing a Layout The first step in the wizard is to choose a layout. The layout layout formats colors, fonts, and other characteristics of the report. Template selections are previewed automatically next to the template selection pane.

FIG 11:

Wizard template selection

7

|

Page

Select the template closest to the desired report layout, then click Next.

Set the Data Source In the second step the report needs to set up access to the CareLogic database. The steps: Step 1

Action Select the JBDC data source - the icons in the upper right are now active

2 3

Click the icon with the to remove “JDBC” Click the icon to view all of th the Data Sources available to you Note:: Only the Data sources related to your CareLogic Systems will display Select CareLogic

4

5

6 7 8

Enter your Username and Password Username: [email protected] Password: CareLogic production password Click Next Select your Data Source Click Next FIG 12:

Data source selection

Creating the Report Query The third wizard step is to build a query. The query determines which data populates populate the report. The steps: Step 1 2 3 4 5

Action Click the icon to add a query for the report Note: “Query 1” is added automatically In the Query Name field, enter the name of your query Click the pencil icon to edit the query Preview the query to ensure the desired data is pulled and there are no conflicts Click Next FIG 13:

Creating a query

Choosing Data to be available in the Report When the query is first built it is blank and must be populated. Selections are made from the th Categories/Columns pane and moved to Selected Columns, Conditions, and/or Order By panes by clicking the corresponding arrow. Multiple selections can be made in the Categories/Columns pane.

8

|

Page

Note: Data elements must be included in the Selected Columns pane ane to be used in the other panes.

FIG 14:

Query Editor

The steps: Step 1 2

3 4

5 6 7 8 9

Action Locate the data required for your report in the Categories/Columns Categories/Columns section of the Query Editor Move the data elements element you need to Selected Columns Note:: Data selected should be data to be (1) included on the actual report (2)) data that filters the report (3) data that may be used as a report parameter Move data elements to be used as a condition to the “Conditions” area Create the desired Condition Note: • Condition labels are a case-sensitive • To view valid values, add the data elements to the “Selected columns” list and preview the query, or create a 2nd query to look up valid values as needed • Date is formatted YYYY YYYY-MM-DD; DD; formatting can be changed in the report Create any Order By requirements Click OK Click the Preview button to ensur ensure the data is returned Click the Close lose button to exit the preview Click Finish FIG 15:

Building a query

9

|

Page

Create the Report Layout The fourth wizard step organizes the report. Display order order is set and any desired groupings can be selected at this point. The steps: Step 1 2 3 4

6 7

Action Select your Report Query from the list li Click Next Note: The Next button may take time to enable. Please be patient. Move the data element by which to Grou Group into Group Items By box Move the report elements to the Selected Items box Note:: Move them in the order in which you want them to appear— appear—in the example below, the goal is to view upcoming activities by organization, sorted by the details in the order shown

Review your entries to verify that the Group Items By and Selected Items are accurate, and that Selected Items appear in the order required by your design Click Next FIG 16:

Organizing report layout

10

|

Page

Create Title Elements The final wizard step is used to add labels to the various elements. These labels will be displayed in the report in various areas.

FIG 17:

Adding element labels

Selecting any item in the Groups: or Details: panes will enable the associated label to be added or changed in the Format pane. ane. Once all desired labels are entered click Finish.. This will exit the wizard and display the wizard results in the workspace in design view.

FIG 18:

Design view

11

|

Page

Further customizations can be accomplished at this point. The highlighted icon can be used to preview the report. It will toggle between design view and preview.

Report Designer Structure This section covers the layout and elements of the Report Designer application. The overall layout is a canvas on the left side and customization on the right. Open files create workspaces in the canvas and are accessed by tabs at the top of the canvas. A palette runs down the right side of the canvas.

The Report Designer Main Toolbar The toolbar at the top of the Report Designer window is for file, data, publ publishing, ishing, and cut-and-paste cut operations. The toolbar makes some of the most frequently used features available. available There are no unique data, publishing, or file operations in the toolbar; every icon represents a feature that is also available through one of the panes or menus in Report Designer. To discover what each icon does, mouse over it to see a tooltip description.

FIG 19:

Main toolbar

Report Designer's Tabbed Views Each report and subreport is opened in its own tab in Report Designer, much like in modern Web browsers and text editors. The currently selected report's tab will always be highlighted in blue, as shown in the graphic below. Click the X in the corner of a tab to close the open report it represents, or right-click click the tab to see a context menu that offers o more advanced close operations.

FIG 1:

Workspace tabs

The button bar below the tab area offers font and preview options. The eye icon switches to preview mode, which shows you approximately how the report, as currently arranged, will display when published. d. When you are in preview mode, the eye turns into a pencil icon; click it to return to design mode. The rest of the functions in this bar are standard font controls found in most text editors and word processors. The list of font types is pulled from you yourr Java Runtime Environment's fonts directory and from the TrueType fonts registered with your operating system or desktop environment.

12

|

Page

The Report Workspace The workspace is dominated by the layout bands, which define each individual portion of the report. repor The currently selected band's label will always be highlighted in gray, as shown with the page header band in the graphic below.

FIG 2:

Workspace area

The top band is the page header, which represents the top of each report page. On the first page of a multi-page page report, the page header is at the absolute top, above the report header. Page headers are repeated on each page of the report. The next band is the report header, which contains report elements just below the page header, but only on the first page of the report. The report header only appears once per report; it is not repeated on subsequent pages in the same report. The details band is next, and it contains middle middle-of-the-page page report elements. This is where most of your report data should go, and ordinarily represents the largest portion of your report pages. Next is report footer, which appears at the bottom of the last page of the report, just above the page footer. Like the report header, it only appears once per report. The last band is the page age footer, which appears at the absolute bottom of every page in a report. You can also create groups for various report elements, with each group having its own header and footer bands in the workspace. All of the report bands can be resized by draggin draggingg their resize handles, or by moving report elements down past the bottom border. For this reason, report elements cannot be dragged from one band to another; they must be cut from the first band and pasted into the second. If you'd like to change the size size of the layout bands to give yourself more area to work in without changing the dimensions of the published report, you can click and drag the percentage number in the 13

|

Page

upper left corner of the workspace. By default it says 100%,, but if you click and drag it diagonally toward the upper left or lower right corners, the view will zoom in or out. If you want to reset the view to 100%, double-click click the upper left corner where the percentage shows.

The Structure Pane The Structure tab shares a pane with the Da Data ta tab in the upper right section of Report Designer. The Structure pane shows the exact hierarchy of every element included in a report. If you add an element to the workspace, it will show up in the Structure pane; when selected there, all of its fine-grained fine details can be viewed and modified through the Style and Attributes panes in the bottom right section of the screen. In addition to the standard drag drag-and-drop drop method using the palette and the workspace, you can also add an element to a report by righ right-clicking clicking on any of the report sections in the Structure list, then selecting Add Element from the context menu. You can delete any element in the list by clicking on it, then pressing the Delete key, or by right-clicking right it and selecting Delete from the context menu.

Master Report or sub-report report This is the top-level level category under which all other report bands are listed. For standalone or master reports, this will be Master Report; Report for subreports, it will be sub-report.

Page Header All of the elements shown in the page header band will be listed in this category.

Report Header All of the elements shown in the report header band will be listed in this category.

Groups If you've created any groups for your report elements, they will show up here. You can add or delete a group by right-clicking clicking the Groups heading, then selecting the appropriate action from the context menu. You can also delete a group by selecting it, then pressing the Delete key. Your Details band is considered a group, and is explaine explained below.

Details All items you place in the Details band will appear in the Details heading under the Group section. There are also Details-specific specific Header and Body bands which are not displayed in the workspace by default, but are available in the Structure ture pane to add to. You can add these extra bands to your workspace by selecting each of them in the Structure pane, then clicking the checkbox next to hide-on-canvas in the Attributes pane.

No Data In the event that your query does not return any data, whatever content you put into the No Data band will appear in your report. You can add a no data band to your workspace by selecting No Data Band in the Structure pane, then clicking the checkbox next to hide-on-canvas in the Attributes pane.

Report Footer All of the elements shown in the report footer band will be listed in this category.

Page Footer

14

|

Page

All of the elements shown in the page footer band will be listed in this category.

Watermark You can add a watermark to your report by clicking Watermark in the Structure pane, then either rightright clicking it and adding an element directly through the Structure pane, or by clicking the checkbox next to hide-on-canvas in the Attributes pane and dragging an element to the new Watermark band.

The Data Pane Thee Data pane enables you to add data sources and view the individual queries, functions, and parameters in each report. The three buttons at the top of the pane will add a new data source, function, or parameter when clicked, respectively.

Data Sets All off the data sources and queries you have defined for the current report will be listed here. If you want to add a new data source, click the leftmost icon (the yellow cylinder) and select the data source type from the ensuing drop-down down menu. To add a new query query to an established data source, right-click right the data source and then select Edit DataSource from the context menu. To delete a data source, select it, then press the Delete key, or right-click right it and select Delete from the context menu.

Functions All of the mathematical functions and conditional elements that you add to a report will be listed in this category. Click the fx button in the upper left corner of the pane to add a new function. You can delete a function by clicking it, then pressing the Delete key, or by right-clicking clicking it and selecting Delete from the context menu.

Function Reference Every function available in Report Designer is defined below and categorized according to the group it belongs to.

Common Functions The Common category contains contains functions that handle page numbering, and a generic Open Formula feature that you can use to create your own custom function. Function Name Open Formula

Page Total Page Count Page of Pages

Purpose Enables you to create your own custom Open Formula function using the builtbuilt in Formula Editor. This function will run according to its placement in the report. If you need a custom function to run before all other report actions, use the Open Formula function in the Advanced category instead Counts the number of pages rendered thus far in a report Lists the total number of of pages in the rendered report Prints the current page number in comparison to the total number of o pages in the rendered report FIG 3:

Common functions

Report Functions The Report category contains ntains functions that modify the layout of the rendered report. Function Name

Purpose 15

|

Page

Function Name Is Export Type Row Banding Hide Repeating Hide Page Header & Footer Show Page Footer

Purpose Tests whether the given export type has been selected selected for this report Alternates the background color color of each item band in a group Hides equal values in a group. Only the first changed value is printed Hides the page header and footer bands when the the output type is not page type Only shows the page footer on the la last st page rendered in i the report FIG 4:

Report functions

Summary Functions The Summary category contains mathematical functions that count, add, and divide report data in groups Function Name Sum Count Count by Page Group Count Minimum Maximum Sum Quotient Sum Quotient Percent Calculation Count For Page Sum For Page

Purpose Calculates the sum of the selected numeric column. column. This produces a global total Counts the total number of items contained in a group. If no group is specified, all items in the entire report are counted Counts the total number of items contained in a group on one rendered page. If no group is specified, aall items on the entire page are counted Counts the total number of items in the selected groups. If no group is specified, all items in all groups are counted Identifies the lowest lowes or smallest value in a group Identifies the highest or largest value in a group Performs simple division on the sum totals from two columns and returns a numeric value Performs simple division on the sum totals from two columns and returns a percentage value Stores the result of a calculation. This function can be used to convert a group of Running functions into a single total Summary Summary function Counts items on a page according to the specified criteria. This value is reset to zero ero when a new page is reached Adds all of the specified items on one page and produces a total. This value is reset to zzero when a new page is reached FIG 5:

Summary functions

Running Functions The Running category contains mathematical functions that dea deall with running totals, as opposed to global or summary totals. Function Name Sum Count Group Count Count Distinct Average Minimum Maximum

Purpose Calculates a running total sum of the specified specified column Counts the items in a group or report Counts the th number of groups in a report Counts the distinct occurrences of a certain value in a column Calculates the average value in a given column Identifies the lowest or smallest value in a column Identifies the highest highes or largest value in a column

16

|

Page

FIG 6:

Running functions

Advanced Functions The Advanced category contains functions that deal with developer-centric developer centric actions. Function Name Message Format Resource Message Format Lookup

Indirect Lookup

Resource Bundle Lookup Open Formula

Purpose Formats text according to the Jav Javaa Message Format specification Formats text from from a resource bundle according to the Java Jav Message Format specification Maps a string from one column to another string. The possible mappings are given as (key, text) pairs. If the string from the column is null or matches none of the defined keys keys, a fallback value is returned Returns a value from a mapped field. The field's value is used as a key to the field--mapping. mapping. The expression maps the value to a new column name and returns the t value read from this column Performs a resource-bundle resource bundle lookup using the value from the defined field as a key in the resource bundle. This expression behaves like a resource field Enables you to create your own custom OpenFormula function using the builtbuilt in Formula Editor. This function will run before any other action in the report FIG 7:

Advanced functions

Chart Data Functions The Chart Data category contains functions that create datasets for JFreeChart elements. Function Name Category Set Data Collector

Pie Data Set Collector

Pivoting Category Set Data Collector

Time Series Collector

Purpose To generate categorical charts, you need a three three-dimensional dimensional (three column) dataset. The first dimension is the category column, which defines the columns that contain the category names. The category can be considered a grouping of the bars (in a bar chart, chart, for example). The next dimension is the series. In bar charts for example, you'll have one bar for each series. Finally, series. the numerical value being plotted is the third dimension Pie datasets require two and only two dimensions (columns) ( -- one for the pie piece names (the series) and one for the values that are used to calculate size of each pie piece. There are two chart types that work with w Pie datasets: Pie and Ring The PivotCategorySet d data ata collector is an extension of the CategorySet data collector that will pivot the data to use a column of values as series, rather than individual columns themselves. This means that at a minimum, you will need a column from which the collector will retr retrieve ieve the series names, and any number of additional numeric data columns that will serve as categories. The data values to be plotted for each series ar aree the category columns' values This collector builds a dataset that is compatible compatible with JFreeChart XY charts that have one dimension that is time time\date date data. XY date\time date based datasets have three dimensions or columns. The first column is the series values. The next is the date column (typically the xx-axis axis or domain values), and the last la column has the numeric values to be plotted on the yy-axis (range)

17

|

Page

Function Name XY-Series Collector

Purpose An XY dataset plots pairs of values as points on the chart. The XY charts have two numeric axes. If the dataset is arranged by column, each row is expected to have tthree hree columns. Each row is assumed to represent one data point on the chart, ordered by the series name and domain value. The first column's values contain the name of the series. The second column is numeric, containing the domain value of the data point. The third column is numeric, containing the range value of the data point. If the dataset is arranged by row, each row is expected to contain all the data points for the series. The first column's values contain the name of the series. It is assumed the o other ther columns in the row consist of a collection of x/y data points (i.e. columns 2, 4, 6 etc. will be domain values, and columns 3, 5, 7 etc will be range values) This collector builds a dataset that is compatible with JFreeChart XYZ charts that have three three numeric dimensions or columns. XYZ datasets have four dimensions or columns. The first column represents series values. The next is the domain numeric column (typically the xx-axis axis values or domain values), and the third column has the numeric values to be be plotted on the y-axis, y commonly referred to as the range axis. The fourth column determines a relative size value for the points plotted by the domain and range values FIG 8:

Chart data functions

Image Functions The Image category contains functions. Function Name Area Chart

Bar Chart

Purpose Area charts are useful for discovering trends over time, where the values you are comparing are typically hierarchical. If one or more of the chart areas will dip below other areas, then the resulting chart may not be very useful, use and you would be better served by a line or bar chart. Area charts are much like line charts, except the area between the lines and the X axis is filled in with either solid, non-overlapping; non overlapping; or transparent, overlapping colors. Related data sets can be be assembled in groups (series) for easy comparison. If you need to make comparisons between X and Y values that are not directly related in your data source, you must must use an XY area chart instead Bar charts are useful for discovering trends over over time by displaying data in thin, solid columns. Related data sets can be assembled in groups (series) for easy comparison. If you need to show time-oriented time oriented changes in data, or if you want to make comparisons between X and Y values that are not directly related in your data source, you must use an XY Bar chart instead. XY Bar charts cannot be created through action sequences with the JFreeChart (Chart Component) engine at this time, but you can create one through Report Designer. The XY Step and XY StepAr StepArea ea chart types, which can be generated through action sequences, are similar to XY Bar and may be suitable substitutes for the data relat relationships ionships that you want to show

18

|

Page

Function Name Bar Line Chart

Bar Code Bubble Chart

Extended XY Line Chart

Line Chart

Multi Pie Chart Pie Chart

Radar Chart

Scatter Plot Chart

Sparkline Survey Scale

Purpose Bar Line charts are useful for spotting trends and comparing items against agai one another as well as showing comparisons between metrics. For instance, you might have bars that represent the number of employees per department, and a line that indicates productivity; or bars that represent software product sales, and a line that represents the number of evaluation downloads. You cannot have more than one line per bar line chart, so if you need to compare more than one set of metrics, you will have to create creat multiple charts to show them A simple barcode chart available tthrough ugh the Report Designer palette Bubble charts are useful for spotting relationships between metrics and comparing specific data points. In terms of functionality and purpose, a bubble chart is similar to a bar line chart, but offers more specific visual cues for certain data sets. Each bubble represents a plotted XY point at its center, and the Z axis controls the diameter of the bubble. For example, a sales chart might have the top 5 bestselling product names for the X axis, number of units sold as the Y axis, and total sales revenue for each prod units product for the Z axis There are three possible variations of the XY extended line chart: Step Chart, Step Area Chart, and Difference Chart. Passing these values to the ext-charttype parameter in Report Designer, or the chart-type type variable in an action sequence will determine which chart you will create. All three types share the same properties. XY extended line charts are useful for showing how multiple data points change over over time while also showing how each compares against the others Line charts are useful for discovering trends over time by displaying data in thin, usually horizontal lines. Related data sets can be assembled in groups (series) for easy compa comparison. rison. If you need to make comparisons between X and Y values that are not directly related in your data source, you must mus use an XY line chart instead Pie grid charts are useful for comparing multiple data points in a group. The group (series) (series) items will display as multiple pie charts in one chart area Pie charts are useful for comparing multiple data points. A single pie slice can be "exploded" out from the rest of the chart to bring attention to the value it represents. If yo you u need to compare related data sets in groups, you must mus use a pie grid chart instead A radar chart is useful for showing how two or more volume-related volume data points compare against one another, using a third related data point as a basis for comparison. comparison. For instance, you may want to show how sales dollar amounts compare among product lines lines across multiple territories. XY dot (scatter plot) charts are useful for showing trends for many individual exact data points over time. The plotted points show data trends in groupings; where the dots are most concentrated, the trend is most prevalent. If there are very few data points, an XY line or bar chart may be a more appropriate appr chart type than XY dot See Creating a Sparkline Spa Chart A sliding scale chart element

19

|

Page

Function Name Waterfall Chart XY Area Chart

XY Bar Chart

XY Line Chart

Purpose A Waterfall chart is useful for showing the length of each ea specific portion of a trend XY area charts are useful for comparing multiple related data sets over time, especially in zero-sum zero sum situations where you want to show how much of a part each data set has of the total XY bar charts are useful for showing data trends over time, where values tend to change after reasonably long intervals. An XY step cha chart is essentially a horizontal bar chart where the bars are segmented vertically whenever there is a change in value XY line charts are useful for showing how multiple data trends relate to one another over time. It is essentially multiple lline ine charts interposed over one another, and using data sets that are closely related and similar enough to share the same Y axis scale FIG 9:

Image functions

Creating a Sparkline Chart Sparkline charts require comma-separated comma values for input, so if your data iss not in that format, you must create a function to pull it from your data source and put commas between each data point. You can also create data points by hand and enter them into a formula directly. To add a sparkline chart to your report, follow this process. Step 1 2 3 4 5

Action Drag and drop a Sparkline Pie, Sparkline Bar, or Sparkline Line element into a layout band Using the resize handles, change the size of the chart to fit your specifications Click the round green + icon in the Value row. A formula mula field will appear In the formula field, select the function that formats your sparkline data, or type in comma-separated separated values by hand directly, then click Close Click Preview to verify that your chart appears as intended FIG 10:

Building a sparkline chart

Script Functions The Script category contains functions that enable you to directly type in code from a supported scripting language. • • • •

Bean-Scripting Scripting Framework (BSF) Bean-Scripting Scripting Host (BSH) JavaScript Single Value Query

20

|

Page

The only unique object Pentaho entaho offers in Report Designer for a scripting language is get Value for the Bean-Scripting Scripting Framework, which retrieves the current record or row, as shown below:

Deprecated Functions The Deprecated category contains functions that had to be included in this version of Report Designer to provide backwards compatibility for files created with older Report Designer versions. You should never use any of these functions in new reports; there are no Deprecated functions that are not more sensibly implemented in other functions in other categories.

Parameters If your query is properly formed, you can add a parameter to your report, which enables report readers to customize the content of the output. To add a new parameter, click the rightmost icon in the uppe upper left corner of the pane. You can delete parameters by selecting the parameter you want to eliminate and pressing the Delete key, or by right-clicking right the parameter and selecting Delete from the context menu.

FIG 11:

Parameters

Adding a Parameter You can add dynamic ynamic interactivity to a published report such that when a user executes or views it, he can specify how to constrain certain parts of the query data. This is called parameterization. parameterization Follow the procedure below to parameterize a Metadata-based Metadata report. Step 1 2 3 4 5

6

Action Open the report you want to parameterize Right-click the Parameters item in the Data pane, then select Add Parameter... from the context menu. The Add Parameter dialogue will appear Select or change the options according accordi to the definitionss specified Edit your query and add the columns you want to parameterize to the Conditions field Create a parameter token in the Value field of each row in the Conditions area, and a valid default value in the Default field. Parameter tokens are in {braces} { and do not contain spaces Click OK to save the query

21

|

Page

Step 7 8

Action Include the parameterized fields in your report by dragging dragging them onto the canvas Publish or preview the report Note: When a user runs this report, he will be presented with an interactive interacti field that specifies an adjustable constraint for the column or columns you specified FIG 12:

Adding a parameter

The Style Pane The Style pane displays all of the visual and positional style options for any given item in the Structure pane. Click on any Structure ture element, and the composition of the Style pane will adjust to show all of the available style properties, listed by group.

Style Properties Reference Every report element can be customized according to the below-listed below listed parameters. To access style properties, click the Style tab in the lower right pane.

Font Styles Font styles control the font and font properties pertaining to the text of the selected element. Property Name family font-size bold italics

Data Type Selection Integer Boolean Boolean

underline strikethrough

Boolean lean Boolean

smooth

Selection

Purpose The name of the font or font family The size of the font, in points (1/72 of an inch) A flag indicating whether a bold-type type face should be used A flag indicating whether a italic or oblique type face should be used A flag indicating whether the text should be underlined A flag indicating whether the text should be rendered stricken through A flag indicating whether text-aliasing aliasing should be activated FIG 13:

Font details

Text Styles Font styles control the font and font properties pertaining to the text of the selected element. Property Name h-align

Data Type Selection

v-align v-align-in-band

Selection Selection

text-wrap

Boolean

text-color bg-color line-height

Selection Selection Integer

Purpose Horizontally aligns the selected content within this element The sizee of the font, in points (1/72 of an inch) A extended text-alignment alignment that allows fine control on how inline-text is aligned within a line A flag indicating whether text will automatically wrap at the end of the line The foreground color Specifies the element's background color Defines the height of a single text line. Is always greater or equal to the font size

22

|

Page

Property Name overflow-text

Data Type String

trim

Boolean

trim-whitespace bg-ext encoding

Selection String Boolean

h-align

Selection

v-align v-align-in-band

Selection Selection

Purpose A text quote that is printed if the given text does not fully fit into the element bound A flag indicating whether leading and trailing white spaces will be removed Controls how the renderer treats white spaces spac An extended foreground paint property. Expert option Specifies the target text-encoding encoding for the given field, in case the output supports per-field field encodings Horizontally aligns the selected content ntent within this element The size of the font, in points (1/72 of an inch) A extended text-alignment alignment that allows fine control on how inline-text is aligned within a line FIG 14:

Text format

Text Spacing Styles Text spacing styles control the amount of space between letters and words in a textual element. Property Name character word preferredcharacter max-character

Data Type Integer Integer Integer

Purpose The minimum space between two letters Defines additional spacing between words The preferred space between two letters

Integer

The maximum space between two letters FIG 15:

Text spacing

Padding Styles Padding styles control the space around the selected element. Property Name top bottom left right

Data Type Decimal Decimal Decimal Decimal

Purpose Defines the padding on the top edge of the element Defines the padding on the bottom edge of the element Defines the padding on the left edge of the element Defines the padding on the right edge of the element FIG 16:

Padding styles

Object Styles Object styles control the appearance of shape elements. Property Name fill fill-color

Data Type Boolean Selection

draw-outline

Boolean

stroke

Selection

Purpose A flag indicating whether thee given shape should be filled Provides es a way to define alternative fill-colors. fill If undefined,, the foreground color is used Defines whether the shape-outline outline should be drawn in the foreground color Defines the stroke (pen type and width) that should sh be used to render a shape

23

|

Page

Property Name anti-alias

Data Type Boolean

aspect-ratio

Boolean

scale

Boolean

Purpose Defines whether drawable content should be rendered rende with anti-aliasing enabled A flag indicating whether the scaling should sh preserve the aspect ratio A flag indicatingg whether the content printed in the element should be scaled to o fit the element's boundaries FIG 17:

Object styles

Size & Position Styles Size & Position styles control the size and position of the selected element. Property Name height

Data Type Decimal

width

Decimal

x y visible invisibleconsumes-space dynamic-height

Decimal Decimal Boolean Boolean

min-height max-height min-width max-width x-overflow

Decimal Decimal Decimal Decimal Boolean

y-overflow

Boolean

fixed-position

String

layout

String

box-sizing

Selection lection

Boolean

Purpose The element's preferred height. If defined, this overrides all other height definitions including the dynamic-height dynamic flag The preferred width. If defined, this width overrides all other layout rules The X-Coordinate where the element should be placed The Y-Coordinate Coordinate where the element should be placed A flag indicating whether the element will be printed A flag indicating whether an element set to visible maintains its space A flag indicating whether the field should expand its height based on the content it contains The element's minimum height The element's maximum height The element's minimum width The element's maximum width Defines whether content is allowed to overflow the element's layouted box to the right Defines whether content iss allowed to overflow the element's layouted box to the bottom (Group elements only) Shifts the band to a fixed position on the page (Band elements only) The layout strategy for elements in bands Specifies the border-model to use FIG 18:

Size & position styles

Links Styles Links styles control the properties of any HTML links created from any report element. Property Name pdf-bookmark html-anchor

Data Type String String

Purpose (Band elements only) Adds bookmark okmark to pdf outputs The name of an anchor (link-target) target) embedded in the html outputs

24

|

Page

Property Name url url-tool-tip url-window-title

Data Type String String String

Purpose The destination URL Hyperlink title that is displayed as tooltip The window where re the link should be opened FIG 19:

Links styles

Excel Styles Excel styles control XLS output options. Property Name sheet-name format-override formula-override

Data Type String String String

wrap-text

Boolean

Purpose The title of the sheet/table generated in table-exports table A override settingg that provides a Excel-specific Excel cell-formats A override setting that provides a formula that should be printed in the generated Excel-cell cell instead of the original content A override setting that defines whether ether Excel-Cells Excel should have text-wrapping enabled FIG 20:

Excel styles

Sparkline Styles Sparkline styles control the colors of the various sparkline chart types. Property Name low-color medium-color high-color

Data Type Selection Selection Selection

last-color

Selection

Purpose Defines the color of the lower value on o a pie sparkline Defines the color for midrange values on a pie sparkline Defines the color of the higher value on pie and bar sparklines Defines the color of the last value in a series on a bar sparkline, and the line color on a line FIG 21:

Sparkline styles

Page Behavior Styles Page behavior styles control page display and rendering properties of the selected element when publishing to a page-aware aware file format. Property Name display-on-firstpage display-on-lastpage repeat-header

Data Type ype Boolean

page-break-after page-break-before sticky

Boolean Boolean Boolean

Boolean Boolean

Purpose (Band elements only) If true, only displays this band on the first page (Band elements only) If true, only displays this band on the last page (Header and footer elements only) If true, repeats this header or footer on every printed page If true, a page break will occur before this element If true, a page break will occur after this element If true, imports page-header/footer header/footer and the repeated group-header/footer header/footer from the master report into sub reports

25

|

Page

Property Name avoid-page-break

Data Type ype Boolean

orphan

Integer

widows

Integer

Purpose If true, cancels a predefined (through a formula or function) page break Defines the minimum number of elements or lines at end of the page before a page break can occur within the band or paragraph Defines the minimum number of elements or lines at the beginning of the page before a page break can occur within the band or paragraph FIG 22:

Page behavior styles

Border Styles Border styles control the color, texture, and size of the border around the selected element. Property Name top-size top-style top-color top-left-roundheight top-left-roundwidth top-right-roundheight top-right-roundwidth bottom-size bottom-style bottom-color bottom-leftround-height bottom-leftround-width bottom-rightround-height bottom-rightround-width left-color left-size left-style right-color right-size right-style break-color

Data Type Integer Selection Selection Integer Integer Integer Integer Integer Selection Selection Integer Integer Integer Integer Selection Integer Selection Selection Integer Selection Selecti Selection

Purpose Specifies the size (in pixels) of the top border segment s Specifies the line style of the top border segment Specifies the color of the top border segment Specifies the degree of vertical roundness (in pixels) of the top left border corner Specifies the degree of horizontal roundness (in pixels) of the top left border corner Specifies the degree of vertical roundness (in pixels) of the top right border corner Specifies the degree of horizontal roundness (in pixels) of the top right border corner Specifies the size (in pixels) of the bottom border segment Specifies the line style e of the bottom border segment Specifies the color of the bottom border segment Specifies the degree of vertical roundness (in pixels) of the bottom left border segment Specifies the degree of horizontal roundness (in pixels) of the bottom left border segment Specifies the degree of vertical roundness (in pixels) of the bottom right border segment Specifies the degree of horizontal roundness (in pixels) of the bottom right border segment Specifies the color of the left border segment Specifies the size (in pixels) of the left border segment Specifies the line style of the left border segment Specifies the color of the right border segment Specifies the size (in pixels) of the right border segment Specifies the line style of the right border segment Specifies the color of the border segment that closes an element that is cut short by a page break

26

|

Page

Property Name break-style

Data Type Selection

break-size

Integer

top-size top-style top-color top-left-roundheight top-left-roundwidth top-right-roundheight top-right-roundwidth bottom-size bottom-style bottom-color bottom-leftround-height bottom-leftround-width bottom-rightround-height bottom-rightround-width left-color left-size left-style right-color right-size right-style break-color

Integer Selection Selection Integer

break-style

Selection

break-size

Integer

Integer Integer Integer Integer Selection Selection Integer Integer Integer Integer Selection Integer Selection Selection Integer Selection Selection

Purpose Specifies the color of the border segment that closes c an element that is cut short by a page break Specifies the color of the border segment that closes an element that is cut short by a page break Specifies the size (in pixels) of the top border segment Specifies the line style of the top border segment Specifies the color of the top border segment Specifies the degree of vertical roundness (in pixels) of the top left border corner Specifies the degree of horizontal roundness (in pixels) of the top left border corner Specifies the degree of vertical roundness (in pixels) of the top right border corner Specifies the degree of horizontal roundness (in pixels) of the top right border corner Specifies the size (in pixels) of the bottom border segment Specifies the line style of the bottom tom border segment Specifies the color of the bottom border segment Specifies the degree of vertical roundness (in pixels) of the bottom left border segment Specifies the degree of horizontal roundness (in pixels) of the bottom left border segment Specifies the degree of vertical roundness (in pixels) of the bottom right border segment Specifies the degree of horizontal roundness (in pixels) of the bottom right border segment Specifies the color of the left border segment Specifies the size (in pixels) of the left border segment Specifies the line style of the left border segment Specifies the color of the right border segment Specifies the size (in pixels) of the right border segment Specifiess the line style of the right border segment Specifies the color of the border segment that closes an element that is cut short by a page break Specifies the color of the border segment that closes an element ment that is cut short by a page break Specifies the color of the border segment that closes an element that is cut short by a page break FIG 23:

Border styles

27

|

Page

The Attributes Pane The Attributes pane displays all of the low-level low properties, es, and input and output options for any given item in the Structure pane. Click on any Structure element, and the composition of the Attributes pane will adjust to show how all of the possible ways it can be customized. Note: You cannot edit any Style or Attributes Attributes options for any selected report elements in the workspace while the Data tab has focus. Click the Structure tab to see the Style and Attributes panes for selected elements.

The Palette The Palette contains all of the elements that you can use to build a report. To add an element, click on a layout band to select it, then drag and drop an element from the Palette to the selected band. Each of the report elements is described in detail below.

FIG 24:

Palette

Label A static text string. You can set and cchange hange it by hand as many times as you like, but it cannot be changed dynamically through a query or function.

Text Field A textual report element that is dynamically changed through a query or function.

Number Field Displays dynamic numerical data from a query.

Date Field Handles date information from a query.

Message Field An element that combines multiple static and dynamic report elements such as labels and text fields, etc.

Resource Label Static text string that maps to a resource bundle, allowing allowing you to localize a label element based on locale.

Resource Field Dynamic text string that maps to a resource bundle, allowing you to localize any database field. This is particularly useful when you have multiple fields for multiple languages and need nee to implement some kind of logic to choose among them based on locale.

Resource Message Dynamic text string that concatenates data from multiple types, and dynamically maps to a resource bundle, allowing you to localize a report based on locale.

Image Field

28

|

Page

A reference to an image stored in a database.

Image A static image embedded into a report from an accessible location.

Ellipse A vector graphical element with no angles.

Rectangle A vector graphical element in the shape of a rectangle.

Horizontal Line A vector graphical line segment, drawn horizontally.

Vertical Line A vector graphical line segment, drawn vertically.

Survey Scale A simple sliding scale chart element.

Chart A chart or graph that shows your query results graphically.

Simple Barcodes A barcode chart element.

Bar Sparkline A bar sparkline chart element.

Line Sparkline A line sparkline chart element.

Pie Sparkline A pie sparkline chart element.

Band A method of separating report elements.

Sub Report An element that references ferences another report.

Working with a Report Report Designer can produce a powerful report, either starting from scratch or starting with the wizard and adding/adjusting feature features after the wizard is finished.

Adding Report Elements

29

|

Page

Most report elements can easily be added by dragging and dropping them from the Palette or the Data pane to one of the layout bands. In some cases, there are a few extra details that you should know before you dive into report creation. See the sections below that apply to your you project.

Adding Standard Design Elements Follow this process to add design elements to a report. Step 1 2

3 4

5

6 7

Action If you have not already done so, click the Structure tab in the upper right pane. If the Data tab is selected, you will be unable to edit tthe attributes or styless of any report elements Click the design element you want to add, then drag it into the report band that you want to add it to, roughly in the position where you want it to appear. Once the element is placed, it will change from a grey shape to a transparent element with an inline label and blue resize handles Click the resize handles and drag them out to the desired dimensions If necessary, click the center of the element and drag it to a different location within the layout band. You cannot drag an element from one band to another. If you want to move something to a different band, you must cut and paste it. Dragging an element toward the bottom of the band will increase the size of the band With the new report element selected, selected, examine the options in the Attributes and Style panes and make any necessary changes or customizations. Any changeable aspect of a report element can be changed hanged through these two panes To delete an element, click to select it, then press the Delete key, or right-click right the element and select Delete from the context menu You should now have a properly sized and placed report design element containing the data and options you specified in the Style and Attributes panes. Any of the changes you mad made in this process can be revisited to further further customize the new element FIG 25:

Adding elements

30

|

Page

Aligning Elements Report Designer has several features to help you easily align your report elements. All can be found in the View menu. Grids show a graph-paper-likee grid on the report canvas. This can make it easier to evenly space elements by counting the exact number of hash marks between them. Grids can also make it easier to line up elements, but you may find it easier to rely on guides instead. Guides are markers ers you create by clicking on the rulers on the top and left of the report canvas. Once you have guides in place, it's easier to align report elements vertically and/or horizontally. To turn off guides, go to the Guides submenu in the View menu, then un-check the Show Guides item. You can remove individual guides by right-clicking right clicking them on the ruler, then selecting Delete from the context menu. Perhaps the most useful alignment feature in Report Designer is Element Alignment Hints. When you enable this option, on, each report element's outer borders will extend to the edges of the canvas, allowing you to easily line up multiple elements. The Snap to Elements feature will add a kind of magnetism to elements so that they are easier to align with adjacent elements.

Adding Bands Follow this process in order to create a band (formerly called a sub-band sub band in older versions of Report Designer), which allows you to group several report elements into a single area. Step 1 2 3 4 5

Action Drag and drop a band element into a layout band Using the resize handles, change the size of the b band and to fit your specifications Drag and drop an existing report element into the new band and position iit according to your preference Drag and drop other elements into the band as necessary necessar You should now have a layout band containing several report elements. It can be resized or moved anywhere in its parent band, or cu cut-and-pasted pasted into another band FIG 26:

Adding bands

Creating Subreports You can create other reports in your current one by creating subreports. reports. This enables you to display data from multiple sources. Step 1 2

Action Drag and drop a Sub-Report element from the Palette to the layout band ban where you want to display it A Sub-Report Report dialogue will ask if you would like to make this an inline or banded element. Choose one. Inline Sub Sub-Reports can be placed side-by-side side with other elements (even other Sub--Reports). Banded Sub-Reports Reports occupy a variable height, but 100% of the report page width, so they cannot be on the same line with other er elements

31

|

Page

3 4

5

Double-click click the Sub-Report Sub element. A new report tab will open n and capture the window focus In the new report tab, establish a data source and create a report as your ordinarily would, keeping in mind that this will be included in the parent parent report. When you are finished, switch ch back to the main report tab You should now have a report with a separate data source embedded within your current report FIG 27:

Creating a subreport

Adding Hyperlinks You can make any object in a report into a clickable clickable link. Follow this process to create a hyperlink: Step 1 2

Action Select the element you want to create a hyperlink for Go to the Format menu and select Hyperlinks.... The Format Element dialog will appear, with the Hyperlinks tab selected 3 Type a URL into the Hyperlink-Target field, or choose Formula for this field and select a predefined d formula that generates a URL 4 Type your preferred alt text into the Title field, or choose Formula for this field and select a predefined formula formu that generates a string 5 Click the drop-down down box in the Window field and select a URL target, or choose Formula for this field and select a predefined predefined formula that generates a URL 6 Click OK to complete the process Note: When you generate report output that supports supports inline hyperlinks, the URL you specified will be linked to the element you specified. This also applies to preview scenarios that support hyperlinks; however, the built built-in in Report Designer preview mode (the eye icon) does not allow for or clickable links FIG 28:

Adding hyperlinks

Paste Formatting Report Designer has the ability to copy the formatting properties of a certain element and apply them to other elements. Follow this procedure to paste formatting: Step Action 1 Click on the element you want to copy c formatting properties from 2 Copy the element to the clipboard by either pressing Ctrl-C,, or by right-clicking right the element and selecting Copy from the context menu 3 Right-click click the element you want to paste the formatting to, then press Ctrl-Shift-V, or right-click click the target element and select Paste Formatting from the context menu FIG 29:

Paste formatting

32

|

Page

Morphing an Element Any data-driven driven element can be transformed into another type of data data-driven driven element. For instance, if you created and configured a date field field and you later realize that it actually needs to be a number field, you can easily change the element type with the morph feature by following this process: Step 1 2 3

Action Select the element you want to morph Go to the Format menu, then select the Morph sub-menu In the Morph sub sub-menu, select the element type pe you would like to change to Note: The element type should now be changed changed to the one you selected FIG 30:

Morph an element

Implementing Row Banding Sometimes report data can be difficult to read from left to right, especially if there isn't much space between rows. Report Designer has a row banding property that allows you to add alternately colored backgrounds to each row. Follow the process below to implement row banding. Step 1 2 3 4

Action In the Data pane, ane, click on Add Function.... The Add Function window will appear Double-click the Report function category, then select Row Banding, Banding then click Add. A Row Banding functio function will appear in your Data tab Select the new Row Banding function in the Functions section In the Properties pane, select colors for the Active Banding Color and Inactive Banding Color properties, and set any other options according to your preference Note: Row banding is now implemented for each distinct rendered line in your Details band. Row banding makes it easier to read reports, but if you need to go one step further, you can override it with conditional formatting FIG 31:

Row Banding

Performing Calculations There is much you can do with multiple data-driven data elements in Report Designer. esigner. This section explains how to group, summarize, and associate multiple report elements.

Using the Formula Editor When adding conditional formatting or other constraints on data data-driven driven report elements, you have the option of using a built-in in Formula Formula Editor program to help you build an expression with a graphical interface. Follow this process to use Formula Editor: Step Action 1 Click on the element you want to add ad a condition or constraint to 2 In the Style pane, select the property you want to add a constraint to, then click the round green + icon on the right side of the field 3 Click the ... button. The For Formula Editor window will appear 4 Select a function category from the drop drop-down down box. The default category is All

33

|

Page

5 6 7

8

Select a function from the Functions list. If you click on a function, a description of what it does will appear in the tan-colored tan field ld at the bottom of the window Double-click click on a function to bring up the option fields Erase the default values in the option fields, an and d replace them with your own settings. If you need to associate a column with a function, click the Select Field button to the right of the field, then select the data or function you want to use. Follow proper SQL syntax in your options; all values must be be in quotes, and all column names must be in uppercase letters and nd enclosed in square brackets When you're done, click OK, then click Close FIG 32:

Formula editor

Customize Reports: Common Tasks There are a number of common tasks many users perform on reports. These are the instructions for many of those tasks. Note that all instructions on common tasks reference the report you have begun to build.

Adjusting the Column Headings (Details Header) Step 1 2 3 4 5 6 7

Action Click in the Details Header band Click on the first irst column heading In the “Attributes” tab, set the Value to update the text in the actual report Set Name to update the text in the Structure of the report. Uncheck the “data “data-format” option. Uncheck the “style “style-format” option. Repeat for remaining maining columns: Name, Service Date, Begin Time, End Time, Staff, Activity, and Status FIG 33:

Format the Details Band

FIG 34:

Formatting details band

34

|

Page

Step 1 2 3 4 5 6 7

Action Click the Details label In Attributes>Name rename it In Attributes>Format enter the desired format format (see the reference guide at the end of this manual) De-select data--format and style-format Complete for all Details labels Preview the report to verify the changes Run and save the report FIG 35:

Formatting steps

Adjusting the Report Title

FIG 36:

Adjusting report title

35

|

Page

Step 1 2

Action Click the label box in the “Report Header” band Change it to read Upcoming Appointment Report FIG 37:

Adjustment steps

FIG 38:

Insert page break

Insert Page Breaks

Step 1 2 3 4

Action Locate the box representing the area for the page break break this case, Group Header) in Structure Click the Style tab Scroll to “page-behavior” “page Select “pagebreak “pagebreak-before” under Value FIG 39:

Insert page break steps

Add Report authorship Step 1 2 3 4

5 6 7 8

Action In the “Page Header” section, update the left-hand left page header der title to have Value = Run By: Copy & paste the label to Value>Name Move the new label after Run By Update the field value to “env::username” Go to the “Data” tab Find the env::username option in the Environment group. Drag-and-drop drop that option onto the report in the correct location. For the right--hand hand page header, copy and paste it. Update the header title to “Run On:” Update the field value to $(report.date, date, MMMM dd, yyyy @ hh:mm ) Align the 4 boxes appropriately appropriately—remember to accommodate ccommodate longer names and months Preview and save the report FIG 40:

Add authoring

36

|

Page

Adjust djust Heading & Data Width Step 1

2 3 4

Action

Click the green arrow icon to preview in PDF format in a new window Using the PDF preview as a guide, work from left to right right adjusting the width of the column headings and actual data Close the PDF preview Save and preview the report FIG 41:

Adjust width

Add a Function (example: Running Count) Step 1

Action On the Data tab, right click Functions

2 3 4

Choose Add Function Expand thee Running folder Choose Count(Running)

37

|

Page

Step 5

6

7 8

Action

In the Data pane, set options for the Function Ex: to create a count by Organization, set “Reset on Group Name” = name and “Dependency Level” = 0 Drag and drop the function to the place where where you wish it to appear on your report (ex: Group Footer) Note: Subtotal counts need to be placed in the Group Footer rather than the Detail Footer to avoid the count resetting after each page In the Structure Tab, right right-click on Group and select Editt Group In the Name field in the name of your group. In the example, we will enter “name” in this field FIG 42:

Add function

Add an additional Data Element to the Report Step 1 2 3 4 5 6 7 8 9 10 11 12

Action Validate/screen shot Click on the Data tab Right click the report to wh which to add more columns Click on Edit Query Navigate to the Query Editor Add the needed columns to Selected Columns Return to Report Layout Drag-and-drop drop the new fields to the report Note:: new data elements are on the right side of the report rep Adjust the sequence of the data columns using the up/down icons on the Structure tab Adjust Name, Value, date date-format and style-format Copy labels for each column to the Details Header Choose “Paste Formatting” for consistency Save, preview, eview, and run the report FIG 43:

Add data element

To add a Summary (subreport) report) Step 1 2 3 4

Action Determine where to place the summary Create a Sub-report report for the summary Drag the subreport icon from the palette to the location to place the summary Group thee information as needed 38

|

Page

Step 5 6

Action Edit the Grouping as needed Create the Function FIG 44:

Add summary

To show and report by a specific Data Element Example: To display summary numbers by Data Element (such as Organization) each Organization needs to be in a group and d a Group Footer Step 1 2 3 4

Action In the Structure tab, expand Groups and select Group Footer In the Attributes tab, uncheck the “hide “hide-on-canvas” option In the Data tab, drill-thru drill the “Inherited Data-Factories” Factories” option to get ge the Organization data element ele Drag-and-drop drop the Organization Org into the Group Footer FIG 45:

Report by element

Edit Grouping Step 1 2 3 4

Action In the Structure tab, right right-click the Groups option Choose Edit group Set the Name Field Move …Organization to the desired column FIG 46:

Edit group

Create Function Step 1 2 3

Action In the Data tab, add a Function Set the options for the Function Drag-and-drop drop the function into the desired location FIG 47:

Create function

Edit a Subreport Example: Adding a Grand Total which appears once. Step 1 2 3 4 5 6 7

Action Double-click click the sub-report sub Add a “Grand Total” To label to the Report Footer Add a function for Summary > Count Add the function to the Re Report Footer Preview the report Make any necessary form formatting and/or alignment changes Save & run the th report FIG 48:

Edit subreport

Add another condition Step 1

Action Click on the Data tab

39

|

Page

2 3 4 5

Double click the “Upcoming Activities” option and click Next Choose the Data Source and click Next Click the Edit icon to edit the query Set a condition where Type T = Client FIG 49:

Add condition

Creating a Date Parameter (Begin/Start Date Parameter)) Step 1 2 3 4

Action Select the Data tab Scroll croll down and select Parameter Right-click click and select Add Parameter Enter parameters: Name – Begin Label – Begin Date Value Type – Date Display Type – Date Picker FIG 50:

Create start date parameter

(End/Last Date Parameter) Step 1 2 3 4

Action Select the Data tab Scroll croll down and select Parameter Right-click click and select Add Parameter Enter parameters: Name – End Label – End Date Value Type – Date Display Type – Date Picker FIG 51:

Create end date parameter

(Edit Query - Include Begin/Start and End/Last Date parameters in the conditions area.) area. Step 1 2 3 4 5

6

Action Select the Data tab Click on your query name. Right-click click and select Edit Query. Navigate to the Query Editor. Create a Condition: Condition Move the appropriate date field into the Conditions area. Select >= (greater-than/equal-to) (greater in the Comparison field. Enter {Begin} in the Value field. Note: The curly brackets { } are used to wrap a parameter name. ____________________________________________ Move the appropriate date field into the Conditions area. Select - "AD" "yy" -> > "03" "yyyy" -> "2003" "M" -> > "7" "M" -> > "12" "MM" -> "07" "MMM" -> "Jul" "MMMM" -> "December" "d" -> > "3" "dd" -> - "03" "h" -> > "3" "hh" -> - "03" "H" ->> "15" "HH" -> - "15" "k" -> > "3" "kk" -> > "03" "K" -> > "15" "KK" ->> "15" "m" -> > "7" "m" -> > "15" "mm" -> "15" "s" -> > "15" "ss" ->> "15" "SSS" -> "007"

48

|

Page

Symbol

Meaning

Type

E

Day in week

Text

W

Day in year (1-365 365 or 1-364) 1 Day of week in month (1-5) Week in year (1-53) Week in month (1-5) (1

a

AM/PM

Text

z

Time zone

Text

' ''

Escape for text Single quote

Delimiter Literal

D F w

Number

Example "EEE" -> "Tue" "EEEE" -> "Tuesday" "D" ->> "65" "DDD" -> "065"

Number

"F" -> > "1"

Number

"w" ->> "7"

Number

"W" -> > "3" "a" -> > "AM" "aa" ->> "AM" "z" -> > "EST" "zzz" -> - "EST" "zzzz" -> "Eastern Standard Time" "'hour' h" -> "hour 9" "ss''SSS" -> "45'876"

FIG 63:

Date formats

Functions The following functions are useful in reporting. The Pentaho website includes documentation for All Functions and Expressions.. For easy reference, see All Formulas. Function Symbol DATEDIF

NOW() TODAY()

Description / Syntax / Additional Information / Example Meaning Differences between 2 dates DATEDIF( DateParam StartDate ; DateParamEndDate ; Text Format ) Text Format Options: Options y Years m Months. Less than a month between dates returns 0. d Days md Days, ignoring Months and Years ym Months, ignoring Years yd Days, ignoring Years Ex: =DATEDIF([BC_RPT_CLIENT_PAYER_PLAN_BEGIN_DATE];TODAY(); "m") Return the serial number of the current date and time. Ex: =NOW() Returns the current date in the t format yyyy-mm-dd. Ex: =TODAY() FIG 64:

49

|

Page