In addition to aggregating total sales by either group description or brand for a specified
department and date range, we would also like to aggregate the total sales by date for the
same department and date range and display those results in our dashboard.
We have already created the framework to easily add this new query to our dashboard.
Basically, we need to perform the following steps:
- Determine where on the dashboard we want our results to go
- Create a new q-sheet for our new query and target the results to the dashboard
Determine the result destination on the dashboard
First let's figure out where we want our results to go. If we look at our dashboard, we can
put our results right next to the other aggregation if we move our chart over to the right a
Note: Since the first query will return results of varying length, it makes more sense
to place the results of the second query next to, not beneath, the first query in the
Let's create a defined name for where the results of our query will be placed. On the
Formulas tab, click Define Name. In the
New Name dialog, enter query_2_results for the
Name and enter the following for Refers to:
=Dashboard!$G$2, then click OK.
Let's also create a defined name for the range to be cleared before pasting the results
from a subsequent query: Click Define Name
. In the New
dialog, enter query_2_results_range
and enter the following for Refers to
, then click
Note: This assumes that our result data will be less than 500
rows, which is probably safe for this example; however, you would want to specify a range
that makes sense for you.
Create a new q-sheet
Now let's create a new q-sheet for the query we want to run on 1010data. From the _1010q Sheet
menu, click . This will open a q-sheet in a new tab labeled
We need to enter some other information in the q-sheet:
- For the Query Description, enter "Total Sales by
- In the To be Applied to Table field, enter
retaildemo.salesdetail, since we want this query to be applied
to the same table as our first query.
- In the Result Destination field, enter
query_2_results so that the results will be placed where we
decided on our dashboard.
- From the Column Headers drop-down, select "Column
- In the Clear range before pasting field, we'll enter:
In the 1010data Macro Code
box, enter the following Macro Language
<note type="base">Applied to table: retaildemo.salesdetail</note>
<note type="link">The following link is to table: All Databases/Retail Demo Data/Product Master</note>
<link table2="retaildemo.products" col="sku" col2="sku" type="select" suffix="_prod">
<willbe name="date_formatted" value="date" format="type:date" label="Date"/>
<tabu label="Tabulation on Sales Detail" breaks="date_formatted">
<break col="date_formatted" sort="up"/>
<tcol source="xsales" fun="sum" name="tot_sales" label="Sum of`Extended`Sales"/>
Our new q-sheet should look similar to the following:
Note: The lines containing the Excel formulas display the values of the cells that they
are referencing. For example, department number 20 appears for the
reference to department_selected in cell B16
within the 1010data Macro Code section.
Now let's test this out. Click
(or press Ctrl+Q).
You can see the results from both queries next to one another in the dashboard:
You can format the column headings and the dollar amounts, which appear in the new
Sum of Extended Sales
column, so that the look across the
dashboard is consistent. (See Basic Formatting
It would be even more convenient if we could run the queries directly from our dashboard.
The next step will show how to add a button to the dashboard that will allow you to do just