Using 1010data libraries and blocks

You can make the Excel Add-in even more dynamic by accessing query code in libraries and blocks on 1010data.

One of the most powerful features of 1010data is the use of libraries and blocks to encapsulate Macro Language code. Libraries consist of one or more blocks, each of which contains Macro Language code that can be inserted within other queries. This allows you to keep your query code in a central location and give multiple users access to it. If you need to change the code for any reason (e.g., one of the calculations needs to change), you can simply change the block. Whenever anybody inserts the block into their query, they will get the most up-to-date code.

To see how this works, we'll log into the 1010data web interface to create a library. The library will consist of one block that contains the code for the second query on our dashboard. As you'll recall, this query calculates the total sales by date for a given department.

Note: If you are not already logged into the 1010data web interface, when you attempt to log in, you will see a message prompting you to either re-enter or end your existing session. You must select End existing session, which will log you out of your Excel Add-in session. Consequently, you will need to log in via the Excel Add-in again before you can run your q-sheets.

Our <library>, which consists of a <block> named sales_by_date, would look something like the following:

<ignore type="base">Applied to table: retaildemo.salesdetail</ignore>
<library>
  <block name="sales_by_date" start="20110101" end="20110331" department="19">
    <sel value="between(date;{@start};{@end})"/>
    <ignore type="link">The following link is to table: All Databases/Retail Demo Data/Product Master</ignore>
    <link table2="retaildemo.products" col="sku" col2="sku" type="select" suffix="_prod">
      <sel value="(dept={@department})"/>
    </link>
    <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"/>
    </tabu>
  </block>
</library>

You'll notice that the code within the <block> is almost identical to the code that we used in Adding another query to the dashboard.

You'll also notice that the <block> takes three parameters: start, end, and department. The parameters are then referenced in the query using the {@PARAM_NAME} syntax (e.g., {@start}).

Note: We specify default values for these parameters in the <block> definition (e.g., start="20110101").

The library is then saved as a Quick Query in a folder on 1010data.

Note: Users who want to use any of the blocks in the library must be given the appropriate permissions to the requisite folders and tables. See How to Share Quick Queries and Folders in the Quick Start Guide for more information.

For this example, let's say that the path to the Quick Query containing this library is: uploads.t632564076_rd_jtaylor.

We can then go to the _1010q Sheet (2) tab, where the q-sheet containing our second query is located, and replace all of the query code in the 1010data Macro Code section with a call to our block:

<import path="uploads.t632564076_rd_jtaylor"/>
<insert block="sales_by_date" start="20110101" end="20110331" department="19"/>

We use the <import> operation to make the <library> available to our current query, and then we use the <insert> operation to insert the sales_by_date block code in our query.

Note that in the <insert> statement, we have provided hard-coded values for the start, end, and department parameters, but since we want our block to be called with the input values from the dashboard, we need to change the hard-coded values to references to the appropriate cells in the Excel workbook.

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&")

So, the line containing the <insert> command would change to:

="<insert block=""sales_by_date"" start="""&startdate&""" end="""&enddate&""" department="""&department_selected&"""/>"

Note: There are three sets of double quotes around each of the variables (e.g., """&startdate&"""). Although this may look odd, it is correct syntax for this Excel formula to work properly.

To see step-by-step examples of how to do this, see the sections Creating a static drop-down in the dashboard and Incorporating a dynamic drop-down in the dashboard.

Our q-sheet would look similar to the following:

You can see in the 1010data Macro Code section that the actual values corresponding to the start, end, and department_selected inputs on our dashboard appear in the code:

If we change the values on the dashboard:

The values in our <insert> call in the q-sheet will change as well:

Note: Before you run the q-sheets, you may have to log in once again. From the Add-Ins tab, click 1010data > 1010data Login..., enter your credentials, and then click Secure Login.

Now, if we go to the dashboard and click the RUN button, the second query will run the code from our <block>, and the dashboard will update accordingly:

Libraries and blocks give you flexibility in providing your users with the most up-to-date query code. In this way, you can provide templates to your business users and change metric calculations without needing to update or send new Excel templates. If something within the block changes, the Excel Add-in will automatically leverage the new definition going forward.