Adding a static input to a dashboard

Our original query had date values hard-coded for the row selection. Let's add a little more flexibility by allowing the user to enter a date range using static inputs.

We'll create a dashboard where the user can enter the start and end dates for the date range, and then we'll use those values in the query.

We can use Sheet2 for our dashboard (or create a new worksheet, if necessary). Let's rename it "Dashboard" and change the background color to a light grey.

Enter text in the cells so that your dashboard looks similar to the following:

You can see in this example that we will allow the user to enter a start date in cell B3 and an end date in cell B7. Below the inputs, we specify that we want the dates in the format YYYYMMDD, which is the date format used by 1010data.
Note: There are no restrictions as to what the user can enter in these cells. These are simply cells in which the user can type in any values. Also, though we are specifying that we want the user to enter dates in the YYYYMMDD format for this example, you might want to allow the user to enter dates in a more recognizable format (e.g., MM/DD/YYYY). In this case, you would need to do some type of transformation on these input values to put them in the YYYYMMDD format. This date transformation could be done in Excel before the values are passed to the query, or within the 1010data query itself. (See Date Transformations for examples on how to do such a transformation within the 1010data query.)

Let's also name the cells that contain the start and end dates so that it will be easier to reference them in our q-sheet. We'll give the cell B3 the name startdate and the cell B7 the name enddate.

The q-sheet essentially sends whatever Macro Language code it contains to 1010data to process and return the results. Because of that, we can convert any row in the q-sheet that contains our macro code into an Excel formula that references the contents of other cells. Then, when the Excel Add-in sends that query to 1010data, it will contain the values in the referenced cells.

In our query, the date selection was done using the between(X;Y;Z) function:
<ignore type="base">Applied to table: retaildemo.salesdetail</ignore>
<sel value="between(date;20110101;20110331)"/>
<link table2="retaildemo.products" col="sku" col2="sku" suffix="_prod" type="select">
  <sel value="(dept=19)"/>
<tabu label="Tabulation on Sales Detail" breaks="groupdesc_prod">
  <tcol source="xsales" fun="sum" name="tot_sales" label="Sum of`Extended Sales"/>
<sort col="tot_sales" dir="down"/>

Let's go over to our q-sheet and change the hard-coded date values in our query to reference the new input values, startdate and enddate.

To reference input values from within the 1010data query, we will need to change the line in our macro code into a formula. We do this by:
  • removing any blank spaces from the start of the line
  • inserting an = at the beginning of the line
  • enclosing the macro code in double quotes
  • preceding any double quote within the macro code with another double quote so that Excel will not interpret any of them as the end of the formula
  • replacing each hard-coded value with a reference to its corresponding input cell using the syntax: "&[CELL_REFERENCE]&" (e.g., "&B3&")
Let's go through those steps for our example. There are no blank spaces at the start of the line, so we can move to the next step and add an = to the beginning:
=<sel value="between(date;20110101;20110331)"/>
Next, let's enclose the macro code in double quotes:
="<sel value="between(date;20110101;20110331)"/>"
Then, we'll precede each double quote within the macro code with another double quote so that Excel will not interpret any of them as the end of the formula:
="<sel value=""between(date;20110101;20110331)""/>"
Our last step is to replace each hard-coded value with a reference to its corresponding input cell, so we'll change the start date value to a reference to the startdate cell and the end date to a reference to the enddate cell:
="<sel value=""between(date;"&startdate&";"&enddate&")""/>"

Now, let's test it out!

Change the date range values on the Dashboard worksheet so that the start date is 04/01/2011 and the end date is 06/30/2011. (Remember, we want these input values in the form YYYYMMDD.)

To run the query, click 1010data > Run Queries > In Active Workbook (or press Ctrl+Q).

Then to see the results, click the Sheet1 tab. The values displayed now show the total sales for each department for the new date range:

It's not very convenient to have to go to one worksheet to enter input values and a different one to view the results. It would be better if we could do everything from our dashboard. In the next step, we'll see how to target the results of our query to the dashboard itself.