Friday, October 3, 2008

Expression Knows Which Cycle Group Field is Active

Someday you may have a chart like this: there's a cycle group in the chart dimension and all of the expressions work great except that one expression needs to work differently when one particular field of the cycle group is active. Here's an example-- your chart for current month sales is working ok but the expression for allocated sales revenue must work differently when the cycle group field Division is used. Maybe the accounting department has decreed that a complex, ever changing formula must be used to show allocated sales revenue by Division so your report needs simply to show 'Not Defined' in the allocated sales revenue column when Division is used for the dimension.
You can write the expression something like this:
If(GetCurrentField(CorpCycle)='Division','Not Defined',Sum(alloc_sales))

In this example, CorpCycle is the name of the cycle group. Division is the name of the cycle group field. Use the actual field name not the label that might be used in the chart. If you're not familiar with using a cycle group in the chart dimension then it will be worth your time to find the reference manual or tutorial and read about it. Being able to change the chart dimension instantly with a mouse click is a powerful feature.

If you build your expressions using the wizard available on the Edit Expression window, you will find the GetCurrentField function along with several other interesting functions listed under System Functions.
Another common place to use this function is in the chart title. For example, you might use this for a chart title:
='CURRENT MONTH SALES SUMMARY BY ' & GetCurrentField(CorpCycle)
That will use the currently active field name from the cycle group in the chart title. As a user clicks on the cycle group the chart title will automatically change along with the dimension.