Note: The field view in the sample report below is organized by Category (hierarchies and levels within a dimension). You can organize the field view in your Analysis report alphabetically (A-Z), by Number (measures) - Text (names, categories, etc.) - Time (months, quarters, and years), and by Schema, which displays the dimensions as defined in the Mondrian schema.
Follow the instructions below to start creating the Analysis report.
1. In the Business Intelligence User Console menu, go to File -> New and select Analysis Report. Alternatively, on the Home page, click Create New and select Analysis Report. A Select Data Source dialog will appear.
2. Select your Data Source from the list. In this example we selected Visits.
Note: The list of available Data Sources is provided by your administrator.
3. In the Available Fields panel on the left, click and drag Branch to the Rows panel within the Layout panel. The Branch column appears in the workspace.
4. In the list of fields, click and drag Served to the Measures area within the Layout panel. The Served column appears in the workspace.
5. Right-click the Served column to display the Edit Column dialog box. Select Column Name and Format. The Edit Column dialogue box appears.
6. From the Format drop-down box, select Default so that your values display as a number. Click OK.
7. In the list of fields, click and drag No Shows to the Measures panel within the Layout panel.
The No Showscolumn appears in the workspace.
8. Click and drag the Day of Week column to the Columns panel within the Layout panel.
At this point you have a functioning report and you can view your data in chart form. Click Switch to Chart Format, , in the upper right corner, to examine your report data in a chart format. The default display is a bar chart but if you click Choose Chart Type,, you can select a different chart type to display your data. Here are a couple of examples of what it may look like:
9. Save your report before going on. In the Business Intelligence User Console, click Save As. When the Save As dialog box appears, save your report with a suitable name and click OK.
Adding Filters to an Analysis Report
Filters are used to restrict or limit the data that is presented in a report. For example, a report shows sales by product line. A filter on Quarter restricts the data so that sales for the fourth quarter in 2010 only are shown. If you were to add a regional filter of Europe, the report displays data pertaining to European sales in the fourth quarter of 2010, exclusively. If you were to add a filter on the Product Line field to exclude Surfing, the report displays data pertaining to European sales in the fourth quarter of 2010 that are not in the surfing product line, exclusively.
Follow the steps below to add a filter to your sample report:
1. Open the report you created earlier.
2. Under the report name click (Show/Hide Filters). A workspace for filters appears at the top of the report.
3. Click and drag the Branch field/column into the filter workspace. The Filter on Branch dialog box appears. Notice that the values associated with the Branch field are listed in the dialog box. You can choose one of these values or you can enable Match a specific string to filter the report on a specific string of data.
4. Select B1 from the list of values and click Add Selected, , to move it into the right pane. B1 appears with a green checkmark next to it in the right pane.
5. Click OK to exit the dialog box. Your sample report displays data for the B1 Branch only.
Note: You can click Undo or Reset to return to the previous version of the report.
6. Click OK and save your report.
Adding a Relative Date Filter
When for example using an Analysis Report in a Dashboard and wanting to always show the information for today, without the need to make any manual change of the date even when the dashboard is open and used continuously for many days, it can be very useful to filter on a relative date.
This is done by:
1. Open an already existing Visits Analysis report, or create a new one.
2. Under the report name click (Show/Hide Filters). A workspace for filters appears at the top of the report.
3. Click and drag the Day field/column into the filter workspace. The Filter on Day dialog box appears:
Here, you can either select a specific time period or for example Today.
4. When done, click OK to save your filter.
Adding a Measure Filter
You can restrict your report data by applying conditions such as “Show me all Visits on a certain day that have a waiting time higher than 300 seconds.”
This is done in the following way:
1. Select the wanted Measure, in the filter to the left, right-click on it and select Filter. In this example we selected Average Waiting Time.
2. Select, from the drop-down list, which parameter to filter on.
3. In the drop-down lists, select how your filter should be defined.
4. In the box to the right enter a numeric value, which should be in the unit seconds. In this example, we used 300, i.e 5 minutes.
5. When done, click OK.
The filter in our example now reads out as “Average Waiting Time is greater than 300 for each Day of Week”.
The following applies to Measure Filters:
• If your numeric filter has both a Greater Than and a Top Ten component at the same time, the Greater Than component is applied before the Top Ten component.
• You can have only one numeric filter on a report at any given time.
• When the report is generated, the numeric filter is applied after other filters are applied.
Solving Filter Problems
If you, for example, add Hour as a filter option and you experience that all hours do not show up as selectable in the filter, then you could try the following:
1. In the file <Orchestra system folder>\pentaho-solutions\system\analyzer\analyzer.properties find the property filter.members.max.count=200 and increase the value to for example 10000.
Note: A very high value may slow down your system. Therefore, it may be a good idea to increase the value gradually, until you have made sure that all your data is available.
2. Restart the Qmatic Platform Service.
Adding Query Parameters to Analysis Reports
You must be logged into the Business Intelligence User Console, and have an Analysis report open for editing in order to continue.
Follow the below procedure to parameterize your MDX query in Analysis.
1. Right-click on the dimension member you want to create a parameter for, and select Filter from the context menu.
1. Select the level you want to set as the default parameter value. Then click the right arrow to move it to the list on the right.
2. Click the checkbox at the bottom of the window. Then type in a name for the parameter in the Parameter Name field.
3. Click OK.
Your parameter is now created as a filter in Business Intelligence Analysis. Whenever this Analysis report is run, users will have a selection of columns to filter by.
Note: If you create a dashboard with this Analysis report. Then this filter can be used by Dashboard Designer as a parameter as well.
Exporting the Analysis Report
Business Intelligence Analysis allows you to export your report as a PDF, a Microsoft Excel spreadsheet, or a CSV file.
Follow the instructions below to export your report:
1. In the Business Intelligence Analysis toolbar, click More actions and options,, and select Export. The export options list appears.
2. Select for example To PDF. In the Export to PDF window, select the wanted Page Format and Orientation, and then click Export. A PDF of the report appears.
3. Examine your report.
Note: Business Intelligence Analysis appends metadata about the report itself in the PDF. Information about the report author, the location of the source file, fields used, filter summary, are included.
Special consideration regarding time formats in Excel
As times in Excel are simply numerical values, a specified number of seconds can be converted to an equivalent time value through simple arithmetic.
The decimal value representing 1 second in Excel is calculated as follows:
1 second = 1 / 86400
Example:
After you have performed this time conversion, in Excel, make sure that you format the applicable cells in the wanted time format. The standard Excel time format hh:mm:ss will display times up to 24 hours. However, if the time value is greater than 24 hours, you will need to use a formatting definition that uses square brackets around the hour (eg. [h]:mm:ss or [hh]:mm:ss, etc.)
Filtering by Member Properties
If a dimension has a number in parenthesis next to it in the field list, that means that it has member properties associated with it. To constrain a dimension by controlling its member properties, follow the instructions below.
1. Open the field layout by clicking Show Field Layout button above the grid. Depending on how you have them oriented, you will see your selected dimensions in either the Row Labels or Col Headers fields.
2. Right-click on a dimension in the row label or column header fields. Then select Show Properties from the context menu. A sub-menu with all available member properties will appear.
3. Check or un-check the member property boxes to add or remove them from the report. Then click OK.
The Analysis report will refresh and show the filter choices you've selected.
Configuring Drill-Down Links
To create reports based on specific number value data, you can implement drill-down links in Business Intelligence Analysis. This will turn all non-calculated number fields into links which, when clicked, bring up a configurable data grid that enables you to quickly view more details for that data point without having to reconfigure your report. Follow the directions below to turn on drill-linking.
Note: Calculated members are unavailable for drill-down at this time.
Note: Drill-down links will not work in reports that have filters that are not being used. If you have any filters in an Analysis report, they must be used in the report in order to view drill-down links.
1. Open the Analysis report that you want to add drill links to.
2. Go to the More actions and options,, menu in the top bar of the report, and select Report Options from it.
3. Select the Show drill-through links on Measure cells checkbox. Then click OK. The measure fields in your report will turn into links.
4. Click on a drill-down link to see a data grid that shows all of the available details for that value.
5. To add or remove columns from the grid, click on the down arrow on the right side of any column and select the Columns sub-menu. From there you can select which columns you want to appear. You can also sort by ascending or descending values for any column through this menu.
You now have drill-down links for numeric, non-calculated members.
Disabling Auto Refresh Mode
When you disable the Auto Refresh Report feature in Analysis you can design your report layout first, including calculations and filtering, without querying the database until you are done. Once the report layout is complete, you can re-enable Auto Refresh Report. Data retrieval will occur once and your report will display the requested data.
Follow the instructions below to disable auto refresh and build a report.
1. In the Business Intelligence User Console menu, go to File -> New and select Analysis Report. Alternatively, click Create New on the Home page and select Analysis Report. Business Intelligence Analysis opens.
2. Select your Data Source, for example Visitsfrom the list.
3. Uncheck the Auto Refresh Report box.
Note: Auto Refresh Report is enabled by default.
Click OK. The Analysis workspace appears.
To begin creating your report layout, select a field and drag it to the Layout panel at the left of the workspace. A message appears when you place the first field allowing you to refresh the report. Right-click menus are available for each of the fields you have dragged to the field layout area. You can continue to build your report and only query the database when the layout is complete. You will build your report in the field layout area exclusively while Auto Refresh is disabled. The blue carpenter square icons denote Measure items and the yellow steps icons denote Level items. They also indicate the position of the fields once they are placed in the report. Notice that the Analysis workspace is disabled (greyed out), if you are not in live query mode.
Note: Click the enable/disable Auto Refresh button, , in the toolbar to toggle between the two modes.
4. When your report is complete, click Refresh Report in the pink band of the field layout area. Alternatively, use the toggle button.
The Analysis report is updated, based on the report definition, and displays the results. You can continue to edit the report as needed and click Refresh Report, or enable the Auto Refresh ability to run the query again.
Creating Hyperlinks to Business Intelligence Solution Repository Content
This feature allows you to create hyperlinks in an Analysis report that will link to content in the Business Intelligence Solution Repository.
The instructions below show you how to create hyperlinks to content inside your Business Intelligence Solution Repository (reports, action sequences, etc.). You must adjust the instructions to work with your own data.
Important: Hyperlinks take precedence over the drill down feature in charts. That means that if a hyperlink has been defined and you click on a bar in a bar chart, for example, the data associated with the hyperlink, (not the drill down chart), displays.
1. Create a simple Analysis report.
2. Select a row label or column header, right-click, and choose Hyperlink.
Note: In the example above, Branch is a row label and Day of Week is a column header. Under these, are the individual members that belong to that field/level (for example, B1, B2, B3 and B4 are members of Branch).
The Link on (Name of Field Level) appears.
3. Select Enable Link to activate the hyperlink feature.
You are not creating a hyperlink to the field header itself but rather to the members under the field level. You can disable linking at any time by clearing the Enable Link checkbox.
4. Click the down arrow next to Link To and choose Pentaho Repository File.
5. If you choose Pentaho Repository File, you can link to a report (.prpt), an action sequence, or any other file inside the Business Intelligence Solution Repository which contains a parameter that can be associated with the content in your Analysis report. Click Browse to locate your file in the Business Intelligence Repository. When you have found the file click Open. TheLink on Field Name dialog box appears. If a destination parameter that matches the field name in your Analysis Report is found, field name is associated to that parameter. Click the checkbox next to the destination parameter name to enable it.
If there are more parameters available for linking in the report or other content, they are also listed. You can override the values to the available parameters or leave them as is. You can also define how hyperlink content will display (Open in New Tab, Window, Current Window). Finally, you can enter a Tool Tipthat displays when users hover over the hyperlinks.
6. Click OK. Hyperlinks appear in your Analysis report. Click on the links to make sure the content associated with them appears correctly.
7. Save your Analysis Report.
Creating Hyperlinks to a URL
This feature allows you to create hyperlinks in an Analysis report that will link to a URL for system to system linking, for example.
The instructions below show you how to create hyperlinks to a URL. You must adjust the instructions to work with your own data.
Important: Hyperlinks take precedence over the drill down feature in charts. That means that if a hyperlink has been defined and you click on a bar in a bar chart, for example, the data associated with the hyperlink, (not the drill down chart), displays.
1. Create a simple Analysis report.
2. Select a row label (or column), right-click, and choose Hyperlink. In the example below, Branch is row label and Day of Week is a column header. Under these, are the individual members that belong to that field/level; for example, Newcastle and Durham are members of Branch. The Link on (Name of Field)appears.
3. Click Enable Link to activate the hyperlink feature.
You are not creating a hyperlink to the field header itself but rather to the members under the field. You can disable linking at any time by clearing the Enable Link checkbox.
4. Click the down arrow next to Link to and choose URL. The Link onFieldName appears.
5. Enter the URL path. You must use the {FieldNameInsideCurlyBraces} format to pass a parameter in your URL as shown in the example below where {Branch} is the parameter:
http://www.bing.com/search?q={Branch}
You can also define how hyperlink content will display (Open in New Tab, Window, Current Window). Finally, you can enter a tool tip that displays when users hover over the hyperlinks.
6. Click OK to return to your report. Hyperlinks appear in your Analysis report. Click on the links to make sure the content associated with them appears correctly.
Simple Conditional Formatting of Measures
Conditional formatting in the Analysis data grid means that cells will be physically affected by the data they contain. The most common form of conditional formatting is stoplight reporting, where cell backgrounds are colored red, green, or yellow depending on user-defined thresholds. Analysis offers some simple pre-defined methods of conditionally formatting numeric data. Follow the directions below to implement conditional cell formatting.
1. Right-click a measure in the grid. Then select Conditional Formatting from the context menu. A sub-menu with conditional formatting types will appear.
2. Select your preferred number format from the list. Consult the table below for more information on simple conditional formatting types.
The Analysis report will refresh and apply the formatting choice you specified.
Conditional Formatting Types
Indicator Type
Description
Color scale
The background cell color will be shaded according to the value of the cell relative to the highest and lowest recorded values in that measure. There are several color progressions to choose from.
Data bar
The cell background is partially filled with a solid color proportional to the scale of the cell's value relative to the highest and lowest recorded values in that measure.
Trend arrow
An upward or downward arrow is displayed to the right of the cell value depending on whether it contains a positive or negative value.
At this point you have a functioning report and you can view your data in chart form. Click Switch to Chart Format, 
, in the upper right corner, to examine your report data in a chart format. The default display is a bar chart but if you click Choose Chart Type,
, you can select a different chart type to display your data. Here are a couple of examples of what it may look like: 
(Show/Hide Filters). A workspace for filters appears at the top of the report.
, to move it into the right pane. B1 appears with a green checkmark next to it in the right pane.
(Show/Hide Filters). A workspace for filters appears at the top of the report.
, and select Export. The export options list appears. 
Click OK. The Analysis workspace appears. 
, in the toolbar to toggle between the two modes.
