BI Tips: Using Query Designer

If a dataset is thought of as a shopping cart, Query Designer can be thought of as the grocery store.

This is where you go to gather the data needed for your report.

The Query Designer is divided into four sections:

1. Parameters and Filters: All dataset parameters and filters will be added here.

2. Dimension List: A list of available data, categorized by information type.

3. Calculated Members List: All custom calculations will be added here.

4. Dataset window (Query): Any data needed for the report will be added to this window.


1. Filters vs Parameters

When a filter is added to a dataset, it becomes a default view for the report viewer. A filter is a set view, meaning the viewer will not have the opportunity to change this setting at the run of the report.

A parameter is created when the report writer gives the viewer the option to change his/​her view at the run of the report.

For example: I have added a date filter in Query Designer without checking the Parameters” box. This is a filter, now this report is always run for Dec. 3, 2013 and cannot be changed by the user.

NUsingQueryDesigner2.pngow if I check the Parameters” box the report runs for Dec. 3, 2013 by default but will prompt the user with the option to run for a different date when they run the report.


2. Dimensions and Hierarchies

Continuing with the grocery store” metaphor, the Dimension List can be thought of as the aisles in the grocery store.

Each Dimension is categorized by the type of information it holds. Similarly, grocery aisles are categorized by food type.

Inside of each Dimension, there are Hierarchies. A Hierarchy further separates the data into specific arrangements. Each hierarchy is named clearly to help identify the type of data it holds.

For example: Here, I have expanded the Regions and Stores Dimension.

  • The first hierarchy is tagged with “.District Manager Name”. When this hierarchy is expanded, you will find the name of the district managers.
  • “.Region Name” would hold the names of your regions, etc.UsingQueryDesigner4.png

*Note: Whenever you see a hierarchy name that simply repeats the dimension name (e.g. Regions and Stores.Regions and Stores), it fully represents the dimension. When Regions and Stores.Regions and Stores is expanded, it reveals: Region Name > District Name > Store Name (the full company tree).