Example Report with a matrix, pie chart and parameters

In this example, you will analyze cost balances categorized based on cost centers and account groups using a matrix and a pie chart. Parameters will be used to filter data for company, business unit, year and period.

Contents

 

How to get started

  1. Go through Getting Started section
  2. Open Business Intelligence/My Reports page in IFS Enterprise Explorer and click Report Builder to start the reporting tool.
  3. Create a new, blank report using the New Report or Dataset dialog box.
  4. Create a data source and a dataset as described in Data sources and Datasets section and set up the GL_Balance_Dataset as mentioned there.

Defining Dataset Parameters

  1. Select the dataset, right click and then click Query to open the Query Designer dialog box.
  2. Drag and drop Company from the Company dimension to the upper-right pane of Query Designer dialog where filters and parameters are defined.
  3. Select Equal for the Operator field, and 900 for the Filter expression field (default value when used as a parameter). Select Parameter checkbox.

    Note: You might already have Company as a filter/parameter, if you entered that when initially creating the dataset.

  4. Similarly define the following as parameters too:

    Accounting Year: 2006 as default value,

    Accounting Period: 1, 2, 3 as default values.

  5. The filter and parameters defined in the dataset query designer will look like the screen below:

    Figure 1: Parameters added to the dataset in Query Designer window  

  6. The dataset parameters entered above become report parameters and they can be viewed under Parameters in the Report Data pane (see below) in design view.

       

    Figure 2: Parameters viewed from Report Data pane

Defining Report Parameters

  1. Select Parameters folder in the Report Data pane, right-click and then click Add Parameter.
  2. Under General Properties in the Report Parameter Properties dialog box, enter values for the Name and Prompt (BUS_UNIT, Business Unit:); Select Text for the Data Type field and Visible for the Select Parameter Visibility field.
  3. Select the Get values from a query option in the Available Values section. Select GL_Balance_Dataset for Dataset, Code_D (for Business Unit) for the Value field, and Code_D_Description for the Label field.
  4. Select the Specify Values option in the Default Values section; click Add and enter RAE (for Racing Engine business unit). Click Ok.

    Note that BUS_UNIT report parameter is also added to the Parameters folder in Report Data pane.

Note: Use of dataset parameters and filters is preferred over use of report parameters and filters because dataset parameters and filters result in better performance in the reports

Click here for more details on TechNet about parameters.

Click here for a detailed tutorial about parameters on TechNet.

Adding a Matrix using Wizard

  1. Click Matrix in the Data Regions section of the Insert tab of the ribbon. Then click Matrix Wizard.
  2. Select the GL_Balance_Dataset in the Choose a dataset page and click Next.
  3. Select BALANCE from the Available Fields field in the Arrange Fields page and drag and drop it to the Values field. Drag and drop Code_B (for cost center) to the Row Groups field, and Code_D (for business unit) to the Column Groups field. Click Next.
  4. In Choose the layout page select Show subtotals and grand totals option and click Next.
  5. Select Generic in Choose a style page and click Finish.The matrix is added to the design area.

Click here for a detailed tutorial on TechNet about creating a matrix report.

Editing the Matrix to add an Indicator

  1. Change first column header to Cost Center.
  2. Select the second column and right-click and then click Insert Column/Inside Group - Right to insert a new column in the Code_D column group.
  3. Merge second and third column header cells and enter Business Unit: [Code_D_Description] as the column header. Code_D_Description has to be dragged from the dataset and dropped into the column header.
  4. Select Indicator from Data Visualizations section of the Inserttab and click on the second cell of the third column to insert an indicator. Select 3 Flags indicator type under Symbols category.
  5. For indicator values, select BALANCE.
  6. Select the indicator, right click and then click Indicator properties. SetupValues and Statesin Indicator Properties dialog box as displayed below:

    Figure 3: Values and States of Indicator in the Matrix

Adding Report Parameter as a Filter

  1. Select the matrix you previously entered.
  2. Find Filters property for matrix in the Properties pane and go to details to change filters.
  3. Click Add. Select Code_D for the Expression field and = for Operator field.
  4. To enter a value for Values field, click fx to go to Expression dialog. Select Parameters in the Category section in the Expressions dialog box and double click BUS_UNIT to add an expression for the parameter value in Set expression for: field and click OK. Use the screen below for reference:

    Figure 4: Adding parameter value for the expression of a filter.

  5. Add Account Type = COST as a filter to restrict balances to cost account balances.

    Note: The Company, Year and Period dataset parameters need not be added to the report as filters since they are applied to the dataset and hence will be effective in a report that uses the dataset.

Adding a Pie Chart

  1. Click Chart in the Data Visualizations section of the Insert tab and then click Insert Chart.
  2. Click and drag the chart in the design area to position and resize it as required.
  3. In the Select Chart Type dialog that opens, select a pie chart type (e.g. Exploded pie in the Shape category) and click OK.
  4. Click on the chart and select the dataset in DataSetName property in the Properties pane.
  5. Click again on the chart to open the Chart Data pane. Using + mark on Values section add BALANCE. Select Account Group for the Category Groups section.
  6. Select Category Group Properties for the Account_Group category group. Select Account_Group_Description for the Label field in the General section and click OK.
  7. Click on chart title and enter Cost Breakdown.

Click here for a detailed tutorial on TechNet about adding a pie chart.

Edit Chart Properties

  1. Add filters to the chart, by selecting chart and going to Filter property. Enter filters for Code_D and Account_Type as displayed below.

    Figure 5: Filters added to the pie chart

  2. To change color scheme of the chart, select the chart, go to Palette property in the Properties pane. Select a preferred color palette, for e.g. Pastel.
  3. Use properties BackgroundColor, BackgroundGradientEndColor, and BackgroundGradientType in the Properties pane to change the background colour.
  4. To add tooltip for chart, select the pie of the chart and go to ToolTip property. For expression enter =Sum(Fields!BALANCE.Value).
  5. Enter Cost Breakdown as the title of the chart. Use font Verdana and font size 12.

Save and Run the Report

  1. Enter Cost Analysis as the title of the report. Use font Verdana and font size 14.
  2. Save the report in required folder for e.g. My Reports folder in the report server
  3. Run the report by clicking Run in Home tab.
  4. The report will be displayed with default parameter values. Change the parameter values as required and click View Report to view a report for different parameters.

    The resultant report is displayed below.

    Figure 6: Resultant report