7.11.1  Walkthrough: Creating a Formula Table

The following walkthrough illustrates the ability of a formula table to satisfy complex data layout needs.

Walkthrough

In this example, you will create a crosstab that breaks down sales totals both according to individual 'State' and 'Category' combinations and simultaneously according to 'Order Date' and 'Category' combinations.

If you were to use a regular Crosstab for this purpose, the data will be represented with a nested header structure, such as that below. Note that the aggregate ('Total') is represented for every combination of 'State', 'Date', and 'Category'.

 

Instead, in this example you will create a table where the 'Date' headers are not nested within the 'State' headers, but are rather displayed separately, as shown below.

 

To create this table, you will begin by creating a regular Crosstab that contains just the 'State' and 'Category' combinations. You will then convert this table to a Formula Table, and use basic Formula Table techniques to add the 'Date' headers.

Follow the steps below:

1. Click the 'New' button in the Style Studio toolbar. This opens the 'New Asset' dialog box.

2. In the left panel, select the 'Report' node. In the right 'Types' panel, select 'Blank Tabular Report' and click 'OK' This creates a new report.

3. Select 'Page Setup' in the Style Studio 'File' menu. Make the following changes:

a. Choose the 'Landscape' page option in the 'Orientation' panel

b. In the 'Margin' panel, set the 'Right' margin to 0.5 inches.

c. Click 'OK'.

4. Expand the Style Studio Toolbox panel. (Select 'Toolbox Pane' from the Style Studio 'Window' menu.)

5. Click the 'Crosstab' component in the Toolbox panel. This opens the Data Binding Wizard for a Crosstab.

6. Select the Data tab. Expand the 'Orders' data source, and select the 'Order Model' data model.

7. Select the Crosstab tab. Bind the headers and summary fields in the following way:

a. Drag the 'Product.Category' field from the 'Available Columns' panel to the 'Row Header' panel.

b. Drag the 'Customer.State' field from the 'Available Columns' panel to the 'Column Header' panel.

c. Drag the 'Product.Total' field from the 'Available Columns' panel to the 'Summary' panel

d. Select the Customer.State' field in the 'Column Header' panel, and click the TopN tab in the 'Setting' panel.

e. Select 'Top' in the menu, and enter '10' as the number of groups. This will return the top 10 groups according to the summed total.

 

f. Click 'Finish' to close the Data Binding Wizard. This creates a new Crosstab in the report.

 

8. Expand the Style Studio Properties panel. (Select 'Properties Pane' from the Style Studio 'Window' menu.)

9. Right-click the Table, and select 'Table' > 'Convert to Formula Table' from the context menu.

 

Observe that some of the cells have now changed to display a formula. To edit the formula for a cell, select the cell and view the Data tab of the Properties panel. You will make a simple formula modification later in this example. For more complete information on editing formulas, see Formula Tables.

10. Click the top-left corner cell of the table to select it. Type the text “Category” into the cell.

 

(Optional) Use the Style Studio toolbar buttons to make the text large and bold.

11. Click the top-right corner cell of the table to select it. Select the Format tab in the Properties panel.

12. In the 'Attribute' panel, select a blue color for the 'Background' property.

 

13. Right-click the Table, and select 'Properties' from the context menu. This opens the 'Table Properties' dialog box.

14. In the 'Table Properties' dialog box, select the Table tab. In the 'Layout' panel, select 'Fit Content' and click 'OK'.

15. Click the 'Preview' button in the Style Studio toolbar, and view the resulting table.

 

16. Close the preview and return to the design view.

17. You will now use formula table operations to add the 'Date' headers to the table. Follow the steps below:

a. Right-click the top-right corner cell and select 'Column' > 'Append Column' from the context menu. This adds a new column.

b. In the Style Studio Data Source panel, expand the 'Orders' data source and the 'Order Model' data model.

c. Expand the 'Orders' node, and drag the 'Date' attribute to the new top-right corner cell of the table. This adds the 'Order.Date' attribute as a column header.

 

d. Right-click this new 'Order.Date' cell, and select 'Cell' > 'Group' > 'Year' from the context menu. This will group the values in the 'Date' field by unique year, resulting in a set of year-based headers.

 

e. Select the Data tab in the Style Studio Properties panel. In the 'Expansion' panel, select the 'Horizontal' option. This sets the year groups in the 'Order.Date' field to display horizontally, as multiple columns headers.

f. Select the Format tab in the Style Studio Properties panel. In the 'Attribute' panel, set a yellow 'Background' color for the 'Order.Date'.

18. You will now use formula table operations to add an aggregate 'Total' measure corresponding to the year groups. Follow the steps below:

a. In the Style Studio Data Source panel, expand the 'Orders' data source and the 'Order Model' data model.

b. Expand the 'Product' node, and drag the 'Total' attribute to the bottom-right corner cell of the table. This adds the 'Product.Total' attribute as a measure.

 

c. Right-click this new 'Product.Total' cell, and select 'Cell' > 'Summarize' > 'Sum' from the context menu. This will aggregate the totals for each combination of 'Category' and 'Date'.

 

19. Click the 'Preview' button in the Style Studio toolbar to preview the table.

 

You can make further modifications to the table by editing the underlying formulas. In the design view, simply select the cell that you want to edit, and open the Data tab of the Style Studio Properties panel. In the 'Binding' panel, edit the existing cell formula as desired. For more details on formula syntax see Creating a Formula Table Using Script (Advanced) and Formula Tables in Report Scripting.

For example, observe that the 'Category' row headers are currently sorted alphabetically (e.g., Business, Educational, Games, etc.). If you wish to instead sort them in reverse alphabetical order, you can make a small change to the existing formula. Follow the steps below.

20. Click the bottom-left cell of the table to select it.

21. Select the Data tab in the Style Studio Properties panel

22. In the 'Binding' panel, change the 'Formula' for the cell from

toList(data['Product.Category'],'sort=asc')

to

toList(data['Product.Category'],'sort=desc')

23. Preview the report again, and note the change in the order.

If you wish to instead sort the categories by the value of the corresponding aggregate total, you can do this by making another change to the formula. First, observe that the 'State' row headers are ordered by the aggregate value. You can therefore modify and reuse the 'State' header formula to achieve the same effect for the 'Category' headers.

Follow the steps below.

24. Click on the top-middle ('State' header) cell, and select the Data tab in the Style Studio Properties panel.

25. In the 'Binding' panel, copy the text from the 'Formula' field. The formula should be as follows:

toList(data['*'],'sort=desc,field=Customer.State,sorton=sum(Product.Total),maxrows=10')

26. Click on the bottom-left ('Category' header) cell, and again select the Data tab in the Style Studio Properties panel.

27. In the 'Binding' panel, select the 'Formula' option, and paste the copied formula into the text field.

28. Modify the formula as follows:

toList(data['*'],'field=Product.Category,sort=desc,sorton=sum(Product.Total)')

This orders the categories according to the summed total, from greatest to least (descending order).

29. Click the green 'Apply' button to save the formula.

30. Preview the report again. Observe that the categories are now ordered according to the totals (e.g., Business, Hardware, Personal, etc.).

 

<< 7.11 Formula Tables © 1996-2013 InetSoft Technology Corporation (v11.5) 7.11.2 Combining Multiple Data Sources in a Formula Table >>