Tag Archive: Business Intelligence

To limit the data displayed on the report you can use a filter.

  • Select the Filter Data icon from the BI ribbon.

The Filter screen opens. You can enter a filter against the data displayed on either the Row or Column axis.

  • Select to Include or Exclude data based on the filter criteria that you enter.
  • Specify the Data that you want to Include/Exclude

The filter options available are:

Bottom (count)

The bottom number of rows specified, this could be used to show the 10 worse customers or products.

Top (%)

The rows or columns that comprise the top specified percentage of a column.

Bottom (%)

The rows or columns that comprise the bottom specified percentage of a column.

Top (sum)

The rows with the largest values that, when summed, comprise at least the value we specify.

Bottom (sum)

The rows with the smallest values that, when summed, comprise at least the value we specify.


The rows above a value we specify.


The rows below a value we specify.


The rows between two values the we specify.

  • Specify the number or percentage that you want to display.
  • Finally choose the Measure/Value that you want to base your filter on.

You are able to add a second filter level i.e filter by X then by Y.

The ‘Supress Empty Rows/Columns’ option will remove any Rows/Columns that have no data. This means NULL values and not Zero values.

The data in a BI report can be Sorted using the Values/Measures selected.

Select the Sort Data icon from the BI ribbon

You can either sort the data on the Rows or on the Columns.

Click ‘Select’ and choose from the available Measures that have been added to the Report Layout.

You can then choose whether to Sort in Ascending or Descending order.

Finally decide whether to ‘Preserve Peer Grouping’.

Preserve Peer Grouping

This is useful if the report has nested dimensions on the area that is being sorted (i.e.Row or Column). Selecting this option means the sort will be applied to the second level whilst keeping the first level in order.

E.g. If you have Product Groups and Stock Items on the Rows of a report. The Stock Items will be sorted for each Product Group.

Each BI report is limited to the the number of data rows displayed on the report, and the number of dimension members displayed within the Report Layout pane. To view additional rows on a report you have to use the Page arrows on the BI ribbon. To view further Dimension members you are able to select an option to display the next few.

The default is to display the first 100 rows and 100 Dimension members. The default settings can be changed so more, or less, rows and/or dimension members display.


Within a BI report, click on the in the bottom corner of the ‘File’ section on the BI ribbon.

The Report Layout Options window opens and there is a setting to ‘Limit rows to’, which by default is set to 100. The setting can then be set to the required number of rows and when saved this will force the report to display the equivalent number of data rows on the spreadsheet.

NB. There is also a ‘Limit Columns to’ option within the Column tab.

Dimension Members

Within a BI report Personalise from the Sage BI ribbon. The Personalise window will open where there is a setting for ‘Paging size’ in the Report Layout Panel Paging section. This will be set to 100 but can be changed as required. Once the setting is changed the number of dimension members shown in the Report Layout pane will be up to the entered value.

When BI is installed it is set by default to only show basic functions. This means that you are limited to what you are able to achieve.

To enable full functionality select ‘Personalise’ from the Sage BI Toolbar.

Select Advanced functionality within the following screen

N.B. The changes will only take effect when Excel is relaunched.

When creating a BI report it is automatically given a name/title, for example Report 1. The almost natural thing to do is to select the cell where the title is displayed and type over it. However if you do this it will make the report disappear.

The reason for this is because the title is generated by an MDX expression (MDX is the cube language that reports are written in).

To rename a report properly select the ‘Rename Report’ icon    

Enter your title in the screen that displays

Click Ok.

Alternatively, if creating a report using the ‘New Report’ icon you are able to enter a title when selecting the the connection

Its a basic tip but one that catches out many!!!!

An issue was found with Sage 200 BI when Sage 200 2011 SP1 was released. The problem related to the SQL collation settings on either the Sage 200 Database or the SQL Server instance. A hotfix has now been released for Sage 200 2011 SP2 to fix this problem.

It should be noted that the SQL collation for Sage 200 SHOULD be Latin1_General_CI_AS.

Details from Sage, about the fix are below.

Issue no. 4437: Customer receives the following error when trying to Refresh BI Cube (or create a new Data Warehouse): “Error 468 : Cannot resolve the collation conflict between ‘SQL_Latin1_General_CP1_CI_AS’ and ‘Latin1_General_CI_AS’ in the equal to operation.”
This looks to be due to 2 different causes:1) The Collation of the main Sage 200 database is set to ‘SQL_Latin1_General_CP1_CI_AS’, instead of the recommended ‘Latin1_General_CI_AS’
2) The Collation of the Sage 200 / BI databases are correct but the overall Collation held against the SQL Server instance is set to ‘SQL_Latin1_General_CP1_CI_AS’.Reason: The Collation of a database can be compared to a language and when the program finds 2 database with different collations it is like comparing English to Chinese. The problem seems to have surfaced with v2011 SP1 as we are now using more temporary tables and, especially in point 2) above, these temporary tables will be stored in the TempDb system database which uses the Collation from the overall SQL Server instance.
This file will fix both sets of issues.
The customer must have upgraded to Sage 200 v2011 SP2.
The Sage.BI.Database.SQL.dll file will need to be copied onto each Client machine.
[Note: The new File version will become 16.0.12019.4]Note: This file needs to be copied to the folder path C:\Program Files\Sage\Sage200
This is confirmed within the “Installation guide for v2011 SP2 Hotfix 3.doc” within the Zip files.Noticeable Changes to the Application:No changes.

If your system is installed on a Windows Server 2008 (64 bit) one thing to check is the Business Intellegence scheduled task. If the ‘Configure For’ is set to ‘Windows Vista or Windows Server 2008’ , try recreating the task again and set the ‘Configure For’ to ‘Windows Server 2003, Windows XP or Windows 2000’.

The Scheduled Task should now run and start refreshing the Cubes.