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.
Open IFS Enterprise Explorer, go to Business
Reporting & Analysis/My Reports. Click on
Report Builder, to start the reporting tool.
Create a new blank report using the New Report or
Dataset dialog box.
Create a data source and a dataset as described in
Data sources and Datasets section
and set up the GL_Balance_Datasetas mentioned there. Make sure
900 company is selected as a dataset parameter and filter in the
Query Designer window.
Open Table Wizard in the
Data Regions section of the
Insert tab of the Report Builder ribbon.
Select the above created dataset in the Choose a
dataset page and click Next.
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.
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
In Choose the layout page, select how you
want the subtotals and expand/collapse to work and click
Next.
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
Select the above entered table in the design area.
Find Filters property from the Properties pane. See the screen below.
Figure 2: Filters property selected in Properties pane
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.
Open the Chart wizard from the
Data Visualization section of
Insert tab of the ribbon.
In Choose a dataset page, select the above
created dataset and click Next.
In Choose a chart type page, select
Column as the chart type.
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
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.
Add filters to the chart in a similar way as explained in
Entering a filter section.
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.
Click on the chart title and enter
Revenue
Analysis.
Change Palette property to
Pastel.
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.
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))
Copy and paste the above expression in other text boxes of the table
which has Sum(BALANCE)
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
Enter axis titles in the chart by clicking axis titles
X axis –
Year/Period
Y axis – Revenue Balance in EUR
Add tool tips to the chart to show the value of each column.
Select the chart first and then select the columns of the chart
Go to the Tooltip property in
Properties pane
Copy and Paste the same expression you entered in the chart data value
Sum(Abs(Fields!BALANCE.Value))
Change the color scheme of the table and chart using following properties
in the Properties pane
To save the report, click on the Report Builder button and then click
Save As
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).
Type a name for the report as Sample_Revenue_Analysis_Rep and
click Save.
Run the report by selecting Run in Home tab of the ribbon. Following report
will be the result.