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 .............................................................................................................. 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 (Draft, not verified to work as indicated) ........................................................ 17 Image Functions ................................................................................................................................. 18
Creating a Sparkline Chart .................................................................................................................. 20 Script Functions .................................................................................................................................. 20 Deprecated Functions ......................................................................................................................... 21 Parameters ......................................................................................................................................... 21 Adding a Parameter (Draft, not finished, steps are wrong) ............................................................... 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 (Draft, not verified) ................................................................................................... 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 (Draft, not verified) .......................................................................................................... 31 Creating Subreports (Draft, not finished) ............................................................................................... 31 Adding Hyperlinks (Draft, not verified) .................................................................................................. 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: Running Count) ......................................................................................... 37 Add an additional Data Element to the 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 ........................................................................................................................................... 46
Reference Materials ...................................................................................................................... 46 Date Formatting ..................................................................................................................................... 46 Functions ................................................................................................................................................ 47
iii
|
Page
Introduction CareLogic provides a powerful reporting tool to help you answer specific questions using the data it contains. The Ad Hoc Report Builder provides basic, intermediate, and advanced methods for identifying, collecting, filtering, constraining and laying out information 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. Selecting data to include in the report. Creating the report 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. The following table illustrates the capabilities of each mode: User Console
Report Designer
Web‐based wizard driven
Desktop application
Simple layout, limited options
Highly customizable layout
Simple to very complex queries
Simple to very complex queries
Preview function
Preview function
Publishes for use via User Console, or as Publishes for use via User Console, or as standalone PDF, XLS, CSV, HTML standalone PDF, XLS, CSV, HTML Executes reports published in Report Designer FIG 1:
Comparison table for Report Designer 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 of the appearance of the items. Accepts the same attributes 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 all other elements. This band can consume the complete space of the page and will never trigger a page‐break. The watermark‐band is intended to fill the page background FIG 3:
Report element definitions
Report Designer Metadata Model Understanding the Metadata 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 is done through queries. The Metadata 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 via Metadata files. The files include real‐time 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. 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, the Object Model PDF and the Data Dictionary Excel 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 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
Enter the name of the data you need in the search field (Ex: “Client co‐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 is located in the model.
4
|
Page
FIG 7:
Data Dictionary
The steps to use the Data Dictionary: Step
Action
1
Open the XLS file
2
Click Edit, Find
3
Enter the search term for the data
4
Review the instances. Note the Table, Column, Format, CareLogic Location and any comments and links Verify the data is the data required for the query
5 6
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 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 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 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]. 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 below to download and install: Step
Action
1
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 e‐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 Unzip the ZIP file ‐ Access the local ZIP file. Extract the contents of the file a directory. Qualifacts recommends C:\Program Files Create Shortcut & Start Report Designer ‐ Right‐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 on the shortcut to start Report Designer
2 3
FIG 9:
Installing Report Designer
6
|
Page
Using the Report Wizard The wizard builds a report in five steps. These 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 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
4
icon to view all of the Data Sources available to you Note: Only the Data sources related to your CareLogic Systems will display Select CareLogic
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 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 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 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 section of the Query Editor Move the data elements 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 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‐MM‐DD; formatting can be changed in the report Create any Order By requirements Click OK Click the Preview button to ensure the data is returned Click the Close 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 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 Click Next Note: The Next button may take time to enable. Please be patient. Move the data element by which to 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—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. 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, publishing, and cut‐and‐paste operations. The toolbar makes some of the most frequently used features 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 the tab to see a context menu that offers 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. 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 your 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. 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 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‐of‐the‐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 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 dragging 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 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 the upper left corner where the percentage shows.
The Structure Pane The Structure tab shares a pane with the Data 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 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‐and‐drop method using the palette and the workspace, you can also add an element to a report by right‐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 it and selecting Delete from the context menu.
Master Report or sub‐report This is the top‐level category under which all other report bands are listed. For standalone or master reports, this will be Master 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 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 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 Header and Body bands which are not displayed in the workspace by default, but are available in the Structure 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 right‐ 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 The 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 of 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 menu. To add a new query to an established data source, right‐click 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 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 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 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 built‐ 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 pages in the rendered report Prints the current page number in comparison to the total number of pages in the rendered report FIG 3:
Common functions
Report Functions The Report category contains 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 for this report Alternates the background 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 output type is not page type Only shows the page footer on the last page rendered in 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. 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, all 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 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 function Counts items on a page according to the specified criteria. This value is reset to zero when a new page is reached Adds all of the specified items on one page and produces a total. This value is reset to zero when a new page is reached FIG 5:
Summary functions
Running Functions The Running category contains mathematical functions that deal 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 column Counts the items in a group or report Counts the 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 or largest value in a column
16
|
Page
FIG 6:
Running functions
Advanced Functions The Advanced category contains functions that deal with developer‐centric actions. Function Name Message Format Resource Message Format Lookup
Indirect Lookup
Resource Bundle Lookup Open Formula
Purpose Formats text according to the Java Message Format specification Formats text from a resource bundle according to the Java 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, 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. The expression maps the value to a new column name and returns the value read from this column Performs a 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 built‐ in Formula Editor. This function will run before any other action in the report FIG 7:
Advanced functions
Chart Data Functions (Draft, not verified to work as indicated) 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‐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, for example). The next dimension is the series. In bar charts for example, you'll have one bar for each series. Finally, 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 Pie datasets: Pie and Ring The PivotCategorySet data 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 retrieve 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 are the category columns' values This collector builds a dataset that is compatible with JFreeChart XY charts that have one dimension that is time\date data. XY date\time based datasets have three dimensions or columns. The first column is the series values. The next is the date column (typically the x‐axis or domain values), and the last column has the numeric values to be plotted on the y‐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 three 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 other 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 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 x‐axis values or domain values), and the third column has the numeric values to be plotted on the y‐axis, 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, 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; or transparent, overlapping colors. Related data sets can 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 use an XY area chart instead Bar charts are useful for discovering trends 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 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 StepArea chart types, which can be generated through action sequences, are similar to XY Bar and may be suitable substitutes for the data relationships 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 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 multiple charts to show them A simple barcode chart available through 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 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‐chart‐ type parameter in Report Designer, or the chart‐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 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 comparison. If you need to make comparisons between X and Y values that are not directly related in your data source, you must use an XY line chart instead Pie grid charts are useful for comparing multiple data points in a group. The group (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 you need to compare related data sets in groups, you must use a pie grid chart instead A radar chart is useful for showing how two or more volume‐related data points compare against one another, using a third related data point as a basis for comparison. For instance, you may want to show how sales dollar amounts compare among product 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 chart type than XY dot See Creating a Sparkline 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 specific portion of a trend XY area charts are useful for comparing multiple related data sets over time, especially in 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 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 line 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 values for input, so if your data is 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 field will appear In the formula field, select the function that formats your sparkline data, or type in comma‐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 Framework (BSF) Bean‐Scripting Host (BSH) JavaScript Single Value Query
20
|
Page
The only unique object Pentaho offers in Report Designer for a scripting language is get Value for the Bean‐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 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 the parameter and selecting Delete from the context menu.
FIG 11:
Parameters
Adding a Parameter (Draft, not finished, steps are wrong) You can add dynamic 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. Follow the procedure below to parameterize a Metadata‐based 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 to the definitions 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 them onto the canvas Publish or preview the report Note: When a user runs this report, he will be presented with an interactive 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 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 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 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 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 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 size of the font, in points (1/72 of an inch) A extended text‐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 An extended foreground paint property. Expert option Specifies the target text‐encoding for the given field, in case the output supports per‐field encodings Horizontally aligns the selected content within this element The size of the font, in points (1/72 of an inch) A extended text‐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 preferred‐ character 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 the given shape should be filled Provides a way to define alternative fill‐colors. If undefined, the foreground color is used Defines whether the shape‐outline should be drawn in the foreground color Defines the stroke (pen type and width) that should 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 with anti‐aliasing enabled A flag indicating whether the scaling should preserve the aspect ratio A flag indicating whether the content printed in the element should be scaled to 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 invisible‐ consumes‐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
Boolean
Purpose The element's preferred height. If defined, this overrides all other height definitions including the dynamic‐height 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 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 is 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 to pdf outputs The name of an anchor (link‐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 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 A override setting that provides a Excel‐specific cell‐formats A override setting that provides a formula that should be printed in the generated Excel‐cell instead of the original content A override setting that defines whether Excel‐Cells should have text‐wrapping enabled FIG 20:
Excel styles
Sparkline Styles (Draft, not verified) 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 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 file format. Property Name display‐on‐first‐ page display‐on‐last‐ page repeat‐header
Data Type Boolean Boolean Boolean
page‐break‐after Boolean page‐break‐before Boolean sticky 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 and the repeated group‐header/footer from the master report into sub reports
25
|
Page
Property Name avoid‐page‐break
Data Type 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‐round‐ height top‐left‐round‐ width top‐right‐round‐ height top‐right‐round‐ width bottom‐size bottom‐style bottom‐color bottom‐left‐ round‐height bottom‐left‐ round‐width bottom‐right‐ round‐height bottom‐right‐ round‐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 Selection
Purpose 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 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‐round‐ height top‐left‐round‐ width top‐right‐round‐ height top‐right‐round‐ width bottom‐size bottom‐style bottom‐color bottom‐left‐ round‐height bottom‐left‐ round‐width bottom‐right‐ round‐height bottom‐right‐ round‐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 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 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 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 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 properties, 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 all of the possible ways it can be customized. Note: You cannot edit any Style or 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 change 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 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 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 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 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 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 the attributes or styles of any report elements Click the design element you want to add and 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, 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 through these two panes To delete an element, click to select it, then press the Delete key, or right‐click 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 made in this process can be revisited to 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‐like 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 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 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, 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 (Draft, not verified) Follow this process in order to create a band (formerly called a 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 band to fit your specifications Drag and drop an existing report element into the new band and position it according to your preference Drag and drop other elements into the band as necessary You should now have a layout band containing several report elements. It can be resized or moved anywhere in its parent band, or cut‐and‐pasted into another band FIG 26:
Adding bands
Creating Subreports (Draft, not finished) You can create other reports in your current one by creating subreports. 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 where you want to display it A Sub‐Report dialogue will ask if you would like to make this an inline or banded element. Choose one. Inline Sub‐Reports can be placed side‐by‐side with other elements (even other Sub‐Reports). Banded Sub‐Reports occupy a variable height, but 100% of the report page width, so they cannot be on the same line with other elements
31
|
Page
3 4
5
Double‐click the Sub‐Report element. A new report tab will open 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 report. When you are finished, switch 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 (Draft, not verified) You can make any object in a report into a 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 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 that generates a string 5 Click the drop‐down box in the Window field and select a URL target, or choose Formula for this field and select a predefined formula that generates a URL 6 Click OK to complete the process Note: When you generate report output that 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‐in Report Designer preview mode (the eye icon) does not allow for 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 formatting properties from 2 Copy the element to the clipboard by either pressing Ctrl‐C, or by right‐clicking the element and selecting Copy from the context menu 3 Right‐click the element you want to paste the formatting to, then press Ctrl‐Shift‐V, or right‐click the target element and select Paste Formatting from the context menu FIG 29:
Paste formatting
32
|
Page
Morphing an Element Any data‐driven element can be transformed into another type of data‐driven element. For instance, if you created and configured a date 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‐menu, select the element type you would like to change to Note: The element type should now be 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, click on Add Function.... The Add Function window will appear Double‐click the Report function category, then select Row Banding, then click Add. A Row Banding 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 elements in Report Designer. 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‐driven report elements, you have the option of using a built‐in 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 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 Formula Editor window will appear 4 Select a function category from the drop‐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 field at the bottom of the window Double‐click on a function to bring up the option fields Erase the default values in the option fields, and 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 in quotes, and all column names must be in uppercase letters and 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 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‐format” option. Uncheck the “style‐format” option. Repeat for remaining 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 (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 this case, Group Header) in Structure Click the Style tab Scroll to “page‐behavior” Select “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 page header 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 that option onto the report in the correct location. For the right‐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—remember to accommodate longer names and months Preview and save the report FIG 40:
Add authoring
36
|
Page
Adjust Heading & Data Width Step 1
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 adjusting the width of the column headings and actual data Close the PDF preview Save and preview the report
2 3 4
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 the 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 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‐click on Group and select Edit 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 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 the new fields to the report Note: new data elements are on the right side of the report Adjust the sequence of the data columns using the up/down icons on the Structure tab Adjust Name, Value, date‐format and style‐format Copy labels for each column to the Details Header Choose “Paste Formatting” for consistency Save, preview, and run the report FIG 43:
Add data element
To add a Summary (subreport) Step 1 2 3 4
Action Determine where to place the summary Create a Sub‐report for the summary Drag the subreport icon from the palette to the location to place the summary Group the 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 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‐on‐canvas” option In the Data tab, drill‐thru the “Inherited Data‐Factories” option to get the Organization data element Drag‐and‐drop the Organization into the Group Footer FIG 45:
Report by element
Edit Grouping Step 1 2 3 4
Action In the Structure tab, 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 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 the sub‐report Add a “Grand Total” label to the Report Footer Add a function for Summary > Count Add the function to the Report Footer Preview the report Make any necessary formatting and/or alignment changes Save & run the 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 = Client FIG 49:
Add condition
Creating a Date Parameter (Begin/Start Date Parameter) Step 1 2 3 4
Action Select the Data tab Scroll down and select Parameter Right‐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 down and select Parameter Right‐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.) Step 1 2 3 4 5
6
Action Select the Data tab Click on your query name. Right‐click and select Edit Query. Navigate to the Query Editor. Create a Condition: Move the appropriate date field into the Conditions area. Select >= (greater‐than/equal‐to) 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"
46
|
Page
Symbol
Meaning
Type
m
Minute
Number
s
Second
Number
S
Millisecond (0‐999)
Number
E
Day in week
Text
W
Day in year (1‐365 or 1‐364) Day of week in month (1‐5) Week in year (1‐53) Week in month (1‐5)
a
AM/PM
Text
z
Time zone
Text
' ''
Escape for text Single quote
Delimiter Literal
D F w
Example "m" ‐> "7" "m" ‐> "15" "mm" ‐> "15" "s" ‐> "15" "ss" ‐> "15" "SSS" ‐> "007" "EEE" ‐> "Tue" "EEEE" ‐> "Tuesday" "D" ‐> "65" "DDD" ‐> "065"
Number 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 62:
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: 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 format yyyy‐mm‐dd. Ex: =TODAY() FIG 63:
47
|
Page
48
|
Page