Example Report with a Table, Chart and Filters

In this example, we are analyzing revenue balances of different business units of 900 company categorized based on year/period, using a table and column chart. A filter will be used together with some other report visualization features.

Contents

How to get started

  1. Go through Getting Started section
  2. Open IFS Enterprise Explorer, go to Business Reporting & Analysis/My Reports. Click on 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. Make sure 900 company is selected as a dataset parameter and filter in the Query Designer window.

Creating a table using wizard

  1. Open Table Wizard in the Data Regions section of the Insert tab of the Report Builder ribbon.
  2. Select the above created dataset in the Choose a dataset page and click Next.
  3. In Arrange fields page, select data items from Available fields and drag and drop them in Row Groups, Column Groups and Values lists and when you are done, click Next.
  4. If you want the table to display general ledger balances per business units (Code_D in 900 company) categorized year/period wise the field arrangement would look like the screen below.



    Figure 1: Arrange Fields page of table wizard showing the selected items for row, column and values

  5. In Choose the layout page, select how you want the subtotals and expand/collapse to work and click Next.
  6. In Choose a style page, select a style scheme (say Generic), which decides the font and color scheme and click Finish.

For a tutorial from TechNet on creating a basic table report, click here

For details from TechNet on how to create a table report manually click here

Entering a filter

  1. Select the above entered table in the design area.
  2. Find Filters property from the Properties pane. See the screen below.


    Figure 2: Filters property selected in Properties pane

  3. Then add the following filters for company, accounting year, account type and code D (see the screen).

    Note: You need to enter the filter for company in the report only if you have not included it as a dataset parameter when creating the dataset.
    Use of dataset parameters and filters will help to improve the performance of the report when compared with using report parameters and filters.



    Figure 3: Filters added to the table.

For more details from  Microsoft TechNet on filters click here.

Creating a sort order for Accounting Period

  1. Select the table inserted above.
  2. Select Accounting_Period in Column_Groups in the Grouping pane, right-click and then click Group Properties.
  3. In the Sorting section in Group Properties dialog box, add a new Sort by item as Accounting_Period. Click fx button to open Expression dialog box.
  4. In Expression0 dialog box add CInt conversion function which comes under Common Functions to get the expression CInt(Fields!Accounting-Period.Value)

Refer screen below for an illustration:

Figure 4: Conversion to Integer added for Accounting_Period in Expressions

Note: This step is required because Accounting_Period is defined as a string value in IFS database. This affects sorting in Report Builder

Creating a chart using wizard

  1. Open the Chart wizard from the Data Visualization section of Insert tab of the ribbon.
  2. In Choose a dataset page, select the above created dataset and click Next.
  3. In Choose a chart type page, select Column as the chart type.
  4. In Arrange chart fields page, add Balance to Values box, Accounting_Year, Accounting_Period to Categories box, Code D to Series box and click Next. The field arrangement of the chart is given in the screen below.


    Figure 5: Arrange chart fields page of Chart wizard

  5. Choose a style (e.g. Generic) in next page and click Finish. Drag and place the chart in a proper place in the design area. Drag and resize the chart to the required size.

For a detailed tutorial from TechNet on adding a chart to the report click here.

For details from TechNet on charts and entering charts manually click here.

Editing the chart properties

  1. Add filters to the chart in a similar way as explained in  Entering a filter section.
  2. Click on the Click to Add Title text box and enter Revenue Analysis of Business Units as the title. Change font to Verdana and font size to 14.
  3. Click on the chart title and enter Revenue Analysis.
  4. Change Palette property to Pastel.
  5. The following changes should to be made to get absolute values and to avoid presenting credit balances of revenue accounts as negative amounts in table.
    1. Select a text box in the table in which Sum(BALANCE) is displayed, right-click and then click Expression.
      Edit the expression by adding Abs() function.

      Sum(Abs(Fields!BALANCE.Value))

    2. Copy and paste the above expression in other text boxes of the table which has Sum(BALANCE)
    3. Chart Data box will appear on the second click on the chart. Click the arrow in Sum(BALANCE) under the Values section. Use Abs() function in Expression as explained above and displayed below.

    Figure 6: Editing expression by adding Abs() function

  6. Enter axis titles in the chart by clicking axis titles

    X axis – Year/Period

    Y axis – Revenue Balance in EUR

  7. Add tool tips to the chart to show the value of each column.
    1. Select the chart first and then select the columns of the chart
    2. Go to the Tooltip property in Properties pane
    3. Copy and Paste the same expression you entered in the chart data value

      Sum(Abs(Fields!BALANCE.Value))

  8. Change the color scheme of the table and chart using following properties in the Properties pane
    1. BackgroundColor under Fill
    2. BackgroundColorGradientEndColor under Fill
    3. BackgroundColorGradientType under Fill

Saving and running the report

  1. To save the report, click on the Report Builder button and then click Save As
  2. To save the report on the report server, click Recent Sites and Servers. Select or enter the name of the report server and select a folder to save the report in (e.g. My Reports folder).
  3. Type a name for the report as Sample_Revenue_Analysis_Rep and click Save.
  4. Run the report by selecting Run in Home tab of the ribbon. Following report will be the result.

Figure 7: End result of the report