This lesson is a guide on how to use groups in the Vizubi database created from the AdventureWorks DW database that Microsoft ships with SQL Server 2008 R2.
You can download the Vizubi database which is the output from the final tutorial in the series here.
What are groups?
A group, like a slicer is a set of fields. You can create reports with groups in the columns instead of fields. Each time you double click on the column header in which there is a group, the data in that column will cycle through each field in the group, allowing you to see the same report for a whole series of fields. You cycle through fields in a group in the same order as they appear in the in the slicer (and changing their order in the slicer will change the order you cycle through them). You can also see how to select a field to display without cycling through the entire group later in this tutorial.
(1) To create a slicer, create a group and leave the flag on the Create Group check box (it is selected by default).
View the How to create a Slicer or VizubiAdventureWorks 3: create a panel tutorials for more details on how to create a slicer.
Load the AdventureWorksDW SQL tables.ndb
Load the Vizubi database called "Vizubi AdventureWorksDW.ndb" which you created in the previous tutorial:
Open Excel.
(1) Click on the Vizubi tab.
(2) Click on the Open button in the Database group of the ribbon and open the .ndb file.
Open the Number of order and sales by country report
(1) Click on the upper part of the Report button in the Reports group of the ribbon. The Select report window opens.
(2) Double click on the Number of orders and sales by country report.
Remove a field from the report
(1) Click on the Customize button in the Reports group of the ribbon.
(2) Click on the SalesTerritoryCountry field in the Row Labels pane.
(3) Click on Remove Field in the menu. The SalesTerritoryCountry column is removed from the report.
Add a group to the report
Like fields which are organized by table in the Fields Section, all the groups in the database are located under the Groups node which is always the first node in the Fields Section.
(1).To add a group to the report, drag and drop it from the Fields Section to the Row Labels Pane, or check the box to the left of the group you want to add. Drag and drop the Country group in the Row Labels pane.
(2) A new column is created with data from the group.
(3) The column header of the group shows the first field of the group which in this case is EnglishCountryRegionName.
Cycling through the fields
You can cycle through the fields of the group in the following ways:
(1) Double clicking on the column header of the group.
- or -
(2) Clicking on the upper part of the Fields button in the Group group of the ribbon.
- or -
(3) Clicking on the down arrow below the Fields button opens a window with all the fields in the active group. Click on the field you want to view.
(4) Cycle through the fields in the group and at the end, cycle until EnglishCountryRegionName is selected.
Filter and cycle
If you double click directly on a cell in a column which is a group, Vizubi will filter and cycle at the same time: it will filter by the value selected and cycle to the next field. This is useful for drill down when you have fields in the group organized in hierarchies with the top level categories first and subcategories last. When you double click on a value, Vizubi shows all the values of the next field in the group that match with the value selected in the previous field.
Double click on Germany in the EnglishCountryRegionName.
(1) The report shows the StateProvinceName field and only the provinces of Germany are displayed because we filtered by Germany.
(2) Click on the Clear button in the Selections group of the ribbon to remove the filter and view all the values for the field.
(3) Use the down arrow below the Fields button to select the EnglishCountryRegionName field.
Work with more groups in the report
Vizubi supports multiple groups in a single report. The active group (the column where you have your active cell) is always dominant over the other groups.
(1) Add the Product group to the report by dragging it to the Row Labels pane.
(2) The new column appears to the right of the first group.
To cycle through the fields of the active column, click on the upper part of the Fields button; if none of the groups in the report is active, the report will cycle through the fields of the first group in the report (the one on the left hand side of the report).
When you click on the down arrow below the button the list that opens always shows the fields from the active group in the report.
Save the new report as we did in the previous tutorial by clicking on save as in the Reports group in the ribbon. Name it SalesbyCountryandProduct and add Number of order and sales by country and product to the label text box.
NB: You can download the Vizubi database which is the output from the final tutorial in the series here.
This lesson is a guide on how to use groups in the Vizubi database created from the AdventureWorks DW database that Microsoft ships with SQL Server 2008 R2.
You can download the Vizubi database which is the output from the final tutorial in the series here.
What are groups?
A group, like a slicer is a set of fields. You can create reports with groups in the columns instead of fields. Each time you double click on the column header in which there is a group, the data in that column will cycle through each field in the group, allowing you to see the same report for a whole series of fields. You cycle through fields in a group in the same order as they appear in the in the slicer (and changing their order in the slicer will change the order you cycle through them). You can also see how to select a field to display without cycling through the entire group later in this tutorial.
(1) To create a slicer, create a group and leave the flag on the Create Group check box (it is selected by default).
View the How to create a Slicer or VizubiAdventureWorks 3: create a panel tutorials for more details on how to create a slicer.
Load the AdventureWorksDW SQL tables.ndb
Load the Vizubi database called "Vizubi AdventureWorksDW.ndb" which you created in the previous tutorial:
Open Excel.
(1) Click on the Vizubi tab.
(2) Click on the Open button in the Database group of the ribbon and open the .ndb file.
Open the Number of order and sales by country report
(1) Click on the upper part of the Report button in the Reports group of the ribbon. The Select report window opens.
(2) Double click on the Number of orders and sales by country report.
Remove a field from the report
(1) Click on the Customize button in the Reports group of the ribbon.
(2) Click on the SalesTerritoryCountry field in the Row Labels pane.
(3) Click on Remove Field in the menu. The SalesTerritoryCountry column is removed from the report.
Add a group to the report
Like fields which are organized by table in the Fields Section, all the groups in the database are located under the Groups node which is always the first node in the Fields Section.
(1).To add a group to the report, drag and drop it from the Fields Section to the Row Labels Pane, or check the box to the left of the group you want to add. Drag and drop the Country group in the Row Labels pane.
(2) A new column is created with data from the group.
(3) The column header of the group shows the first field of the group which in this case is EnglishCountryRegionName.
Cycling through the fields
You can cycle through the fields of the group in the following ways:
(1) Double clicking on the column header of the group.
- or -
(2) Clicking on the upper part of the Fields button in the Group group of the ribbon.
- or -
(3) Clicking on the down arrow below the Fields button opens a window with all the fields in the active group. Click on the field you want to view.
(4) Cycle through the fields in the group and at the end, cycle until EnglishCountryRegionName is selected.
Filter and cycle
If you double click directly on a cell in a column which is a group, Vizubi will filter and cycle at the same time: it will filter by the value selected and cycle to the next field. This is useful for drill down when you have fields in the group organized in hierarchies with the top level categories first and subcategories last. When you double click on a value, Vizubi shows all the values of the next field in the group that match with the value selected in the previous field.
Double click on Germany in the EnglishCountryRegionName.
(1) The report shows the StateProvinceName field and only the provinces of Germany are displayed because we filtered by Germany.
(2) Click on the Clear button in the Selections group of the ribbon to remove the filter and view all the values for the field.
(3) Use the down arrow below the Fields button to select the EnglishCountryRegionName field.
Work with more groups in the report
Vizubi supports multiple groups in a single report. The active group (the column where you have your active cell) is always dominant over the other groups.
(1) Add the Product group to the report by dragging it to the Row Labels pane.
(2) The new column appears to the right of the first group.
To cycle through the fields of the active column, click on the upper part of the Fields button; if none of the groups in the report is active, the report will cycle through the fields of the first group in the report (the one on the left hand side of the report).
When you click on the down arrow below the button the list that opens always shows the fields from the active group in the report.
Save the new report as we did in the previous tutorial by clicking on save as in the Reports group in the ribbon. Name it SalesbyCountryandProduct and add Number of order and sales by country and product to the label text box.
NB: You can download the Vizubi database which is the output from the final tutorial in the series here.