Configuring Workbooks

Configuring Workbooks

The SaveToDB add-in allows creating application workbooks using the Application Workbooks wizard.

The wizard contains workbooks for built-in SaveToDB applications that you may install using the Application Installer.

Starting SaveToDB 8.7, you may configure the wizard to allow users to create workbooks for your applications.

This is the cheapest way to distribute workbooks and updates.

Users just run the wizard, specify their connect credentials, and the wizard creates new workbooks with the specified credentials.

If you use the Active Directory authentication, your users specify just a server and a database.

Configuration Specification

The SaveToDB add-in loads custom workbook definitions from tables and views with the following fields:

  1. An optional primary key column like ID
  2. NAME
  3. TEMPLATE
  4. DEFINITION

NAME defines a name shown in the Application Workbooks wizard.

TEMPLATE defines a filename or URL of the workbook used as a template.

DEFINITION defines workbook worksheets and connected tables.

SaveToDB Framework 8.7 includes a ready-to-use table, xls.workbooks, with the following structure:

CREATE TABLE xls.workbooks (
    ID int IDENTITY(1,1) NOT NULL,
    NAME nvarchar(128) NOT NULL,
    TEMPLATE nvarchar(255) NULL,
    DEFINITION nvarchar(max) NOT NULL,
  CONSTRAINT PK_workbooks_xls PRIMARY KEY (ID)
);

CREATE UNIQUE NONCLUSTERED INDEX IX_workbooks_name_xls ON xls.workbooks (NAME);

So, you may install SaveToDB Framework 8.7 to use this feature or just create this table in your database using the framework code.

Workbook Definitions

The workbook definition has the following grammar:

<workbook definition> ::= <sheet definition>
    | <sheet definition> NEWLINE <workbook definition>

<sheet definition> ::= <sheet name> ' = ' <table definition>

<table definition> ::= <query object>
    | <query object> ',' <query list>
    | <query object> ',' <query list> ',' <query list enabled>
    | <query object> ',' <query list> ',' <query list enabled> ',' <address>
    | <query object> ',' <query list> ',' <query list enabled> ',' <address> ',' <query parameters>

<query list> ::= EMPTY | '(Default)' | <query list object>

<query list enabled> ::= EMPTY | 'True' | 'False'

The Application Workbooks wizard shows workbooks that have all the required query objects with the SELECT or EXECUTE permissions.

This feature allows having multiple workbooks in the same database while users see only available workbooks.

You may get the active workbook definition using the Definition tab of the Workbook Information dialog box.

So, you may create the target workbook, point it as a template, and just copy and paste its defininiton using the Workbook Information dialog box.

Below are several examples.

The SaveToDB Framework 8 configuration workbook has the following definition:

objects=xls.objects
handlers=xls.handlers
translations=xls.translations

The SaveToDB Framework 7 configuration workbook has the following definition:

QueryList=dbo01.QueryList,(Default),False
EventHandlers=dbo01.EventHandlers,(Default),False
ParameterValues=dbo01.ParameterValues,(Default),False
ObjectTranslation=dbo01.ObjectTranslation,(Default),False
ColumnTranslation=dbo01.ColumnTranslation,(Default),False

The planning_app_analyst workbook of the Planning Application has the following definition:

data=dbo25.view_data,dbo25.view_query_list,False,$B$3,,{"Parameters":{"id1":null,"id2":53,"id3":17,"id4":"Null","unit_id":null}}
facts=dbo25.view_facts,dbo25.view_query_list,False,$B$3,,{"Parameters":{"id1":"Null","id2":52,"id3":17,"id4":"Null","unit_id":null,"calc_type_id":1}}
dimensions=xls25.usp_dimensions,xls25.view_query_list,False,$B$3,,
accounts=xls25.usp_members,xls25.view_query_list,False,$B$3,,{"Parameters":{"dimension_id":1,"root_id":null}}
times=xls25.usp_members,xls25.view_query_list,False,$B$3,,{"Parameters":{"dimension_id":2,"root_id":null}}
categories=xls25.usp_members,xls25.view_query_list,False,$B$3,,{"Parameters":{"dimension_id":3,"root_id":null}}
entities=xls25.usp_members,xls25.view_query_list,False,$B$3,,{"Parameters":{"dimension_id":4,"root_id":null}}
dim5 (regions)=xls25.usp_members,xls25.view_query_list,False,$B$3,,{"Parameters":{"dimension_id":5,"root_id":null}}
dim6 (products)=xls25.usp_members,xls25.view_query_list,False,$B$3,,{"Parameters":{"dimension_id":6,"root_id":null}}
dim7 (subaccounts)=xls25.usp_members,xls25.view_query_list,False,$B$3,,{"Parameters":{"dimension_id":7,"root_id":null}}
This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.