Using a Cycle Group in a Text Object

People usually use Cycle Groups in the definition of dimension for a chart. You can use cycle groups in other places if it helps with understanding your data. Here’s an example showing how you could use a cycle group with a text object to list out top performers at various levels in terms of sales--

First, define a cycle group with aggregation levels that make sense for aggregating sales. For this example I created a cycle group, named xgroup, and defined it as salesperson, sales_region, sales_district, product, and customer_group. The text object won’t provide a place to click on the cycle group and change the level, so I defined a small straight table chart using the cycle group, xgroup, as the dimension and one expression defined simply as 1. Then, I defined the number of rows to show zero rows so that only the totals row is visible and define the totals as sum-of-rows so that the number in the total is the number of distinct values from your cycle group.

You can use any expression that would be of value, but for this example we want to list the top five names in terms of total sales. If the cycle group was set to salesperson then it would list the top five salespersons in terms of total sales. Here’s the text expression I typed into the text object:

='Top 5 ' & GetCurrentField(xgroup)
& ' by Total Sales:
' & concat(
aggr( if(rank(sum(Sales))<=5,
$(=GetCurrentField(xgroup)) ), xgroup)
,', ' & chr(13))

The first line in the expression defines the first line that will appear in the text object and it is a title for the data. It uses the GetCurrentField function to get the current value of the cycle group and use it in the title.

The main part of the expression is an aggr function that will return an array of five values from aggregating the simple if statement over the values as defined by xgroup. I used a concat function to put a delimiter between the values of the array so it will show up in the text object. The delimiter will be comma followed by a space and then the chr(13) function is a carriage return or newline value to put each value on a new line. So, our finished text object might look something like this in the document:

Top 5 salesperson by Total Sales:
S.Hallas,
T.Mehta,
Z.Sui,
P.Esposito,
M.Naverra

Every time you click the cycle group icon on the little straight table chart, the text object changes to show results from a different level: sales_region, sales_district, product, or customer_group. If you place several of these text objects side by side then each could have a different expression. The expressions might be things like top 5 by count of sales orders, or top 5 in profitability, or total promotion budget, or total installation costs, or count of returned products, etc. The idea is to provide some extra information in the document using the flexibility of a cycle group and the simplicity of a text object.

The text objects are most useful and interesting, I think, when several of these expressions are used together in the same object along with text that explains, identifies, and provides meaning.

* * *