Working with Crosstabs Activity

 

 

 

Step

Action

1. 

Configure a new crosstab report.

 

Begin by pointing to the Create menu.

2. 

Click the Ad Hoc View menu item.

3. 

Note: The default demo for adhoc Topic is used in this activity.

4. 

Click the Crosstab button to open the Ad Hoc Editor.

5. 

Note: The Ad Hoc Editor appears.

6. 

Now, configure the crosstab's measures, columns, and rows.

 

In the Measures panel, right-click on Shipping charge and select Add to Rows to add it to the Layout Band.

7. 

Note: Shipping charge is added to the crosstab view.

8. 

Add the Country field to the Columns box.

 

Right-click the Country field and select Add to Columns.

9. 

Note: Countries in the dataset are added as columns in the crosstab view, each displayed with its corresponding shipping charge.

10. 

Next, add City to the Columns box.

 

Right-click the City field and select Add to Columns.

11. 

Note: The crosstab view now displays an expand (+) icon with each country name.

 

Users expand a country to view its cities and their shipping charge values.

12. 

Add the Date shipped field to the crosstab.

 

Right-click Date shipped and select Add to Rows.

13. 

Note: The Date shipped row is added, and the Crosstab view now displays an expand (+) icon in the Measures section of the Crosstab.

14. 

Expand the measure to view more-detailed shipping charge information.

 

Click the + icon adjacent to the label Measures, just below the left end of the title bar.

15. 

Right click the Date shipped field.

16. 

Point to the Change Grouping menu item.

17. 

Select the Quarter menu item.

18. 

Note: Dates now appear as rows, with the corresponding shipping charge displayed for quarters Q3 and Q4, 1996.

19. 

Note: Notice that City data does not appear. To view information for cities, click to expand a country name.

20. 

Expand Brazil to display its city names.

21. 

Note: The three cities of Brazil display in separate columns, and total shipping charges appear at the right.

22. 

Collapse Brazil and display only country data again.

23. 

Note: City columns no longer appear in the display.

24. 

Click the Switch the groups button, left of the A-Z sort button in the menu bar, to view the data differently.

25. 

Note: Columns and rows are switched. Country and City are now the rows, and quarter dates serve as column labels.

 

Task 1: Create a crosstab view, is complete.

26. 

Next, sort the view by Q4 shipping charge.

 

Right-click the Q4 1996 column header to display the context menu.

27. 

Click the Sort Descending list item.

28. 

Note: The caret points down, indicating that the view is sorted by Q4 shipping charge in descending order.

29. 

Now, use in-memory analysis to better understand the data on Germany.

 

Right-click in the cell labeled Germany.

 

If Germany is not showing, make sure you are using Full Data.

30. 

Click the Keep Only menu item to remove all countries but Germany from the view.

31. 

Note: Germany is the only country displayed in the view.

32. 

Click to expand Germany and view shipping data by city.

33. 

Note: The view displays a row for each city.

34. 

Remember: select Full Data from the menu bar drop-down when you need to see the whole dataset.

35. 

Collapse Germany to return to the default view.

36. 

Note: Cities no longer display.

 

37. 

Note: Selecting Keep Only when you right-clicked on Germany created a filter configured to display only Germany in the view.

 

Click on the Country drop-down in the Filters panel at the right.

38. 

Click to select the UK list item.

39. 

Click the Apply button at the bottom of the Filters panel.

40. 

Note: UK replaced Germany in the view.

41. 

Delete the Country filter to display all countries in the view.

 

Point to the menu at the right of the A. Country label and select Remove Filter.

42. 

Click the Apply button at the bottom of the Filters panel.

43. 

Note: The full view appears.

 

Task 2: Sort and filter the crosstab, is complete.

44. 

Next, add a custom field to determine the percent of the total for Q3 and Q4 shipping charges.

 

Right-click on Shipping charge at the bottom of the Measures panel.

45. 

Click the Create Custom Field menu item.

46. 

Note: The Custom Field window appears.

47. 

Click the % of Column Group (value) option at the bottom of the window.

48. 

Click the Create Field button.

49. 

Note: The calculated field appears at the bottom of the Measures panel.

50. 

Right-click the CGP (Shipping charge) field name.

51. 

Click the Add to Columns menu item.

52. 

Note: The new field is added to the crosstab view.

 

Click to expand the % CGP (Shipping charge) column header to view shipping charge percents for Q3 and Q4.

 

Task 3: Add a custom field to the crosstab, is complete.

53. 

Next, save the crosstab view.

 

Point to the Save menu.

54. 

Click the Save Ad Hoc View As menu item.

55. 

Click in the Data View Name field to delete the text.

56. 

Enter the name Custom Crosstab View.

57. 

Specify the folder in which to save the view.

 

Expand the Organization folder.

58. 

Click the Reports folder to select it.

59. 

Click the Save button.

60. 

Note: The crosstab view is saved to the repository.

 

Task 4: Save the crosstab view, is complete.

61. 

Congratulations!

 

You completed the following four tasks:

 

- Create a crosstab view in the Ad Hoc Editor

- Sort and filter the data presented in the crosstab

- Add a custom field to the crosstab

- Save the crosstab

End of Procedure.