Archive for March, 2012

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.

I came accross the following known issue during a Sage 200 upgrade from v5.1 to 2011.

Having done the upgrade on the server, I noticed that the system was slow to load after selecting the data set. It took about 3 minutes. I then noticed that modules were slow to load if they had the List view selected (around 2 mins).

The problem is due to the ‘ConfigurableName’ table not being populated. This can occur when you migrate/update financials-only data.

The following script will correct the problem.

NB. Scripts should not be run without a full backup being taken, or without the assistance of your Business Partner.

The issue number is 4143.

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.

Sage have found and fixed an issue with Sage 200 Manufacturing, when substituting a traceable component within Works Order Processing (WOP).

The issue is described by Sage as follows:

If you use the “Substitute Traceable Component” feature to amend an already complete Works Order, either to add extra components or to remove components, the issue quantity on the Works Order Allocation/Issue does not get updated to reflect the correct quantity that has been issued. This has the effect of making the Works Order total cost not being correct based on the actual costs incurred.

Having identified an issue with the original release Sage have now re-released CRM Patch D for Sage 200 Service Pack 2.

Please see my post from when originally released: