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.
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 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.
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.
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".
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.
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.
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.
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.
Select Add and the XLCubedBusinessRules sheet is added with the 'expense' rule.
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:
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.
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.
The chart will then recognise FY 2017 as an earlier year and format it with a lighter grey fill colour.
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.
The chart now displays the forecast scenario with a hatched fill pattern.