Adding a simple chart to the dashboard

Leverage Excel's charting capabilities to visually represent the results from your query.

Let's combine Excel's easy-to-use charting functionality with the power and speed of running queries on 1010data. Let's create a simple bar chart that shows the total sales for each group returned by our query.

We'll start by creating variables that will represent the x-axis and y-axis values for the chart.

For the x-axis, we will specify the list of groups returned by our query. Go to the Dashboard worksheet, then on the Formulas tab, click Define Name. In the New Name dialog, enter total_sales_category for the Name. For the value of this variable, we'll specify the range of cells containing the groups returned by our query. For our example, in the Refers to field, we'll enter the following:
=Dashboard!$D$3:$D$9

Click OK to create the variable.

Now let's create a variable for the y-axis, which will correspond to the range of cells containing the summarization results for each group. Click Define Name and in the New Name dialog, enter total_sales_sum for the Name. In the Refers to field, we'll enter the following:
=Dashboard!$E$3:$E$9

Click OK to create the variable.

Now that we've created our variables, we can create our chart. Click in any empty cell in the dashboard. Then, on the Insert tab, click Column and select the first 2-D Column chart (Clustered Column):

Position the chart where you want it to appear in the dashboard and resize it as desired:

Now let's incorporate the x-axis and y-axis variables we created earlier.

Let's start with the y-axis:

  1. Right-click anywhere on the chart and click Select Data... from the context menu.
  2. In the Select Data Source dialog, click the Add button under Legend Entries (Series).
  3. In the Edit Series dialog, enter Total Sales for the Series name.
  4. In the Series values field, enter Dashboard!total_sales_sum, which is the name of the y-axis variable we created earlier.
  5. Click OK.

And now, for the x-axis:

  1. In the Select Data Source dialog, under Horizontal (Category) Axis Labels, click the Edit button.
  2. In the Axis Labels dialog, enter Dashboard!total_sales_category, which is the name of the x-axis variable we created earlier.
  3. Click OK.

The Select Data Source dialog should look similar to the following:

Click OK.

We now have a bar chart showing the total sales for each group:

which appears on our dashboard:

The chart will dynamically update when the query is run with different parameters. For instance, if we change the start date and the end date and then run the q-sheet, the dashboard (including the chart we just created) will update with the results.

Note: When we created the x-axis and y-axis variables for our chart, we specified a set range of cells for each (e.g., =Dashboard!$D$3:$D$9), so that our chart displayed the extended sales for the seven groups in department 19. This is fine if we know the results of our query will always be the same number of rows, or if we know that we just want to chart the top seven items returned by our query. But what if our query returns more than seven values and we want our chart to display all of them? For example, in the next section, we're going to give the user the choice to summarize extended sales either by the group description or the brand. Since there are more than seven brands in our data set, not all of them will be displayed as our chart is currently defined. Later in this tutorial, we'll see how to modify this chart to be more dynamic so it can handle a varying number of rows returned by the query.