Excel Sales Dashboard: more functions

Download the Excel_Dashboard_Vizubi.zip from: http://www.vizubi.com/dwnld/Excel_Dashboard_Vizubi.zip
Extract the .xlsx and the .ndb files into the same directory.
Open the Excel_Dashboard_Vizubi.xlsx
You should see the dashboard in the image above.

The GetVizubiFilters() function shows filters currently applied to a workbook (report specific filters are NOT shown by the getvizubifilters functions see the How to create and use report specific filters tutorial for more details on report specific filters). This function is useful when you want to print your report (in hard copies or in pdf format) and you with the filters visible or you want to work without the filter box opened.

In our example:
1) insert a row under the title dashboard.
2) Merge the cells from B2 to G2.

1) Click on the Panel button in the Panels group of the ribbon.
2) Click on the Geography panel then on Confirm.
3) The panel opens on the left. Select the United States value in the CountryRegion field by clicking on the down arrow to the right of CountryRegion.

1) Select the merged cell under the title and insert the =GetVizubiFilters() function.
2) The active filters are shown under the title.

If time dimensions have been used in one or more report in Excel, the function will also show the values of the related variables.

The GetVizubiVariableValue(Variable[,Type]) function returns all the values of a specific variable.

The Variable is the variable to show.

The Type parameter can assume 2 values:
If type = 0 shows the value of the variable.
If type = 1 shows the values of the variable flags.

In our example:
1) substitute the GetVizubiFilters() function with the GetVizubiVariableValue(Variable[,Type]) as follows: GetVizubiVariableValue("Order Date Now",1)
2) The cell shows the flags of the variable.

1) Set type = 0 as follows: GetVizubiVariableValue("Order Date Now",0)
2) The cell shows the value of the variable.

The GetVizubiFieldValues(Field [,Status [,Filter [,Sort]]]) function returns a combination of data according to Status, Filter and Sort state for the values of a field.

The Field parameter: is the field name for which you want the information.

The Status parameter: is a filter which you can use to see only the data for some field values. The status can assume the following values:
0 or null: All the values (default)
1: Selected
2: Selected And Included
3: Selected And Excluded
4: Possible
5: Excluded

The Filter parameter: is the filter to apply before the function returns the result (if omitted it shows all values)

The Sort parameter: is the type of sort to apply to the result:
0: ascending (Default)
1: descending

Suppose you want to view all the possible provinces\states when you apply a filter.
In our example:
1) set the filter CoutryRegion = Australia.
2) substitute the GetVizubiVariableValue("Order Date Now",0) function with the GetVizubiFieldValues(Field [,Status [,Filter [,Sort]]]) set as follow: =GetVizubiFieldValues("StateProvince",4,"",0)
3) The cell shows the possible provinces available for Australia sorted alphabetically.

Download the Excel_Dashboard_Vizubi.zip from: http://www.vizubi.com/dwnld/Excel_Dashboard_Vizubi.zip
Extract the .xlsx and the .ndb files into the same directory.
Open the Excel_Dashboard_Vizubi.xlsx
You should see the dashboard in the image above.

The GetVizubiFilters() function shows filters currently applied to a workbook (report specific filters are NOT shown by the getvizubifilters functions see the How to create and use report specific filters tutorial for more details on report specific filters). This function is useful when you want to print your report (in hard copies or in pdf format) and you with the filters visible or you want to work without the filter box opened.

In our example:
1) insert a row under the title dashboard.
2) Merge the cells from B2 to G2.

1) Click on the Panel button in the Panels group of the ribbon.
2) Click on the Geography panel then on Confirm.
3) The panel opens on the left. Select the United States value in the CountryRegion field by clicking on the down arrow to the right of CountryRegion.

1) Select the merged cell under the title and insert the =GetVizubiFilters() function.
2) The active filters are shown under the title.

If time dimensions have been used in one or more report in Excel, the function will also show the values of the related variables.

The GetVizubiVariableValue(Variable[,Type]) function returns all the values of a specific variable.

The Variable is the variable to show.

The Type parameter can assume 2 values:
If type = 0 shows the value of the variable.
If type = 1 shows the values of the variable flags.

In our example:
1) substitute the GetVizubiFilters() function with the GetVizubiVariableValue(Variable[,Type]) as follows: GetVizubiVariableValue("Order Date Now",1)
2) The cell shows the flags of the variable.

1) Set type = 0 as follows: GetVizubiVariableValue("Order Date Now",0)
2) The cell shows the value of the variable.

The GetVizubiFieldValues(Field [,Status [,Filter [,Sort]]]) function returns a combination of data according to Status, Filter and Sort state for the values of a field.

The Field parameter: is the field name for which you want the information.

The Status parameter: is a filter which you can use to see only the data for some field values. The status can assume the following values:
0 or null: All the values (default)
1: Selected
2: Selected And Included
3: Selected And Excluded
4: Possible
5: Excluded

The Filter parameter: is the filter to apply before the function returns the result (if omitted it shows all values)

The Sort parameter: is the type of sort to apply to the result:
0: ascending (Default)
1: descending

Suppose you want to view all the possible provinces\states when you apply a filter.
In our example:
1) set the filter CoutryRegion = Australia.
2) substitute the GetVizubiVariableValue("Order Date Now",0) function with the GetVizubiFieldValues(Field [,Status [,Filter [,Sort]]]) set as follow: =GetVizubiFieldValues("StateProvince",4,"",0)
3) The cell shows the possible provinces available for Australia sorted alphabetically.