Business Rules

Business rules map members in your data to a type, determining how they should be plotted and formatted in Business Charts. This is inline with the IBCS rules for unifying the visualisation of scenarios.

  • Actuals are formatted in a solid dark grey
  • Plan values are outlined
  • Forecast values are hatched (diagonal fill pattern)
  • Expenses are plotted in the negative direction
  • Previous year fill colour is a lighter grey


Creating Business Rules

Business rules are added from the Business Chart dropdown menu from the XLCubed ribbon.

Business-Rules-Ribbon.png

This opens a dialog allowing you to select which rules to apply to which members. The first time you add a rule, a new sheet called XLCubedBusinessRules will be added to the workbook. Any subsequent additions will be made to the same sheet.

  • Connection: select which connection this rule applies to.
  • Rule: select which rule to create.
  • Applies to connection: apply the rule to just the cube, any cube in the database, or any match.
  • Hierarchy: select which hierarchy this rule will apply to.
  • Members: drag and drop members into the right plan to add them to the current rule. Rules applied to a parent member will also be applied to its children, unless the child member has a different rule of its own. Specify whether the unique name or caption should be used on the Business Rules sheet.


Business-Rules-Insert-Dialog.png

Business Rules Sheet

Business rules can be added via the dialog or directly inputted on to the business rules sheet. The main input area starts at row 51. It has 5 columns - these columns are automatically populated when using the inserter dialog or they can entered manually.

Business-Rules-Sheet-2.png

The area at the top of the sheet lists all the accepted 'types' and explains what should be inputted in each column.

A rule applied to a 'Parent' member is automatically applied to it's children, unless the children are explicitly defined with a different rule.

Business-Rules-Sheet-1.png

Wildcards

Use the wildcard "*" in the Database or Cube columns to apply to the rule to all. Wildcards can also be used to match partial member names, e.g. "Profit*" or "*Expenses".

Expected Sign

There are some additional rules that can be added to the business rules sheet to specify how values are entered in the cube.

For example, in the Adventure Works Finance cube, all expense accounts are inputted as positive values. If one month one of these accounts gets a refund added to, it might then have a negative value. We then have an account structure with some positive and some negative values. We would then need to specify on the business rules sheet that expenses typically have a positive sign. Therefore if it finds an expense with a negative sign, it should actually consider that a gain to the total.

These are entered with "ExpectedExpenseSign", "ExpectedRevenueSign" in the Type column. The For column is left blank and then a "+" or "-" should be entered in the Match column.

Expected-Sign.png

Reusing Business Rules

If you need to reuse the same rules in multiple workbooks, simply save the sheet to a new workbook, then whenever you need to use it, right click on the sheet tab and copy it to the desired open workbook. In future versions it will be possible to centrally store the business rules so that they are automatically retrieved with the data connection.

Business-Rules-Copy-Sheet.png

Example

Setting Expenses and Totals

Consider the waterfall chart below. It is showing the account contributions to the Net Income. However, accounts that are expenses have a positive value in the cube and so are not plotted in the negative direction as we would want, and members which are actually totals are not being treated as such, so we are getting an incorrect total.

Account-Waterfall.png

We could right click on each bar and configure whether it should be negated or a total. However, by adding business rules, this will be done automatically.

We'll start by adding some rules using the inserter dialog. The rule we will create will be for expenses, and it will apply to the Account hierarchy. We select all the values which have a negative contribution to the Net Income. This rule will also be applied to all children of Operating Expenses, so we don't need to add those individually.

Business-Rule-Inserter-Expenses.png

Select Add and the XLCubedBusinessRules sheet is added with the 'expense' rule.

Business-Rule-Sheet-Expenses.png

Refreshing the chart shows that the expenses are now plotted correctly.

We now just need to correct the totals. We could continue to use the inserter, or go back to business rules sheet and add the rules shown below:

Business-Rule-Sheet-All.png

Refreshing the chart now shows the totals plotted and formatted correctly. In this example, we need to alter the subtotal count on the Operating Expenses. After this, the resulting chart in now correct.

Account-Waterfall-Complete.png

Adding Previous Year

We can develop the chart further in order to compare previous year or budget/forecast scenarios.

All business chart types can be made into Small Multiple Charts by adding a hierarchy on to columns or rows in the taskpane. In this case, we will add the date hierarchy and display two years, FY 2017 and FY 2018.

On the business rules sheet we add a rule to set FY 2018 as the current year.

Business-Rule-Sheet-Current-Year.png

The chart will then recognise FY 2017 as an earlier year and format it with a lighter grey fill colour.

Waterfall-PY.png

Adding Forecast

We can now do the same with our scenario hiearchy. We first need to moving the date hierarchy on to filters and select the current year, FY 2018. We can then place the Actual and Forecast scenarios on columns.

Add a rule to the business rules sheet to set the forecast member.

Business-Rule-Sheet-FC.png

The chart now displays the forecast scenario with a hatched fill pattern.

Account-Waterfall-FC.png