Contents Diagrams Roles Schemas Tables Views Procedures Functions

Planning Application
for Microsoft Excel and SQL Server

Version 2.5, September 10, 2018

Contents

Introduction

Planning Application for Microsoft Excel and SQL Server

This ready-to-use application allows planning in Microsoft Excel and getting reports from a SQL Server database.

Key features:

- Customizable dimensions and dimension members;
- Customizable calculation rules with multiple hierarchies;
- Customizable edit forms and reports;
- Built-in permission management;
- Built-in change tracking.

Here is an example of a customizable form:

Planning Application - Form Example

The application includes complete source code and a set of user workbooks available through the SaveToDB Application Workbooks wizard.

You may read more in the e-book Planning Application with Excel and SQL Server.

This paper contains the descriptions of the database objects.

The application also uses the following frameworks:

- SaveToDB Framework
- Database Help Framework
- Change Tracking Framework

Feel free to contact us.

We may arrange a meeting to show the application and answer to your questions.

Custom developement and commercial support are also available.

Change History

Version 2.5, September 10, 2018

New Features:

Impovements:

Diagrams

DiagramDescription
Diagram 01Application Architecture
Diagram 02Master Data
Diagram 03Calculation Rules
Diagram 04Member Hierarchies
Diagram 05String Values
Diagram 06Source Cube
Diagram 07Calculated Cube
Diagram 08Form Layouts
Diagram 09Form Rowsets and Parameters
Diagram 10Member Relations
Diagram 11Member Permissions
Diagram 12Form Permissions

Diagram 01. Application Architecture

Application Architecture

The application has the following logic blocks:

- Master data
- Calculation rules
- Source cube
- String values
- Calculated cube
- Form settings
- Forms
- User permissions
- Tracking changes

Master data include dimensions, dimension members and its properties.

The application creates calculation rules to calculate aggregates based on master data and source cube.

The source cube contains number data entered by users or loaded from external systems.

The block of string values contains string values entered by users. The application does not aggregate such values.

The calculated cube allows getting the source and aggregated data in forms.

Form settings define available forms including their layouts, rows, columns, page data, and parameters.

The Forms block is an engine that executes configured forms to allow getting data and saving data back to a database.

User permissions is a block that allows managing user permissions on forms and dimension members.

Tracking changes is an optional block implemented with the Change Tracking Framework.

Diagram 02. Master Data

Master Data

The master data includes:

- Currencies and units in the dbo25.units table;
- Tax rates in the dbo25.tax_rates table;
- Dimensions in the dbo25.dimensions table;
- Dimension members in the dbo25.members table;
- Dimension member parents in the dbo25.parents table.

Use the following procedures to edit master data:
xls25.usp_units, xls25.usp_tax_rates, xls25.usp_dimensions, xls25.usp_members.

The dbo25.calc_types and dbo25.name_formats table contains application defined values.

The dbo25.dimensions table contains seven dimensions.
The first four (Accounts, Times, Categories, and Entities) have specific built-in support and must be active.
Analysts may activate or deactivate the last three dimensions, and change their meaning.

Adding additional dimensions requires changes in the application code.

The dbo25.members table contains members of all dimensions.
It contains columns of the member calculation and display properties.

The application supports multiple hierarchies. Each member can have multiple parents.
The dbo25.parents table contains such relations.
The application allows editing parents and its factors using the xls25.usp_members procedure.

Diagram 03. Calculation Rules

Calculation Rules

Based on child-parent relations, calculation type, and previous periods,
the xls25.xl_actions_update_hierarchies procedure updates the dbo25.factors table.

This table is used to calculate aggregates and balances.

For example, for member parents A1 - A2 and A2 - A3 the factor table contains A1 - A2, A2 - A3, and A1 - A3.

Use the xls25.view_hierarchies view to check the generated rules.

Diagram 04. Member Hierarchies

Member Hierarchies

Based on child-parent relations, the xls25.xl_actions_update_hierarchies procedure also updates the dbo25.hierarchies table.

This table is used to select members of the specified parent in forms.

For example, for member parents A1 - A2 and A2 - A3 the hierarchy table contains A1 - A1, A2 - A2, A3 - A3, A1 - A2, A2 - A3, and A1 - A3.

Use the xls25.view_hierarchies view to check the generated rules.

Diagram 05. String Values

String Values

The application stores string values in the dbo25.strings table.

The application does not aggregate such values.
So, it stores values for members at any hierarchy level.

The dbo25.strings table contains member id values in columns id1-id7.

If the dimension is not active, its column contains the dbo25.dimensions.default_member_id value.

To store string values, specify the string calculation type of the target account (dimension 1).

Diagram 06. Source Cube

Source Cube

The application stores number values in the dbo25.facts table.

The dbo25.facts table contains member id values in columns id1-id7.

If the dimension is not active, its column contains the dbo25.dimensions.default_member_id value.

The table contains source values of the user input, in the account currency or unit, in the source_value column.

The application calculates the cube values, in the system currency or base unit, and updates the value column immediately on input.

It uses the dbo25.currency_rates table values for currency accounts and just a dbo25.units.factor value for other accounts.

Use the xls25.xl_actions_update_facts procedure to update cube values after currency rate changes.

Note that the application uses hard-coded dimension id values:

1 - Accounts
2 - Times
3 - Categories
4 - Entities

Diagram 07. Calculated Cube

Calculated Cube

The application calculates aggregates and balances on the fly.

It uses the source and calculated cube values of the dbo25.facts table and calculation relations and factors from the dbo25.factors table.

You may use the code of the dbo25.view_facts view to learn the calculation rules.

The application uses the dbo25.members.previous_period_id and dbo25.member.same_period_id fields to select values of the referred periods.

Also, the forms output Excel formulas as is. Such values are calculated in Microsoft Excel.

Diagram 08. Form Layouts

Form Layouts

The application allows creating any number of forms in the dbo25.forms table.

Analysts have to define the form layouts including:

- the dimension axis (row, column, page),
- the dimension order in the axis (header_order),
- and the parameter order at the ribbon (parameter_index).

Analysts may test any form using the xls25.usp_run_form and xls25.usp_run_offline_form procedures.

End users use the inherited forms with the predefined sets of the parameters.

They have to use the xls25.view_query_list_forms and xls25.view_query_list_offline_forms views in the SaveToDB Connection Wizard.

By default, the application shows child members of the dbo25.dimensions.root_member_id member in the parameters and axes.

Analysts may configure member lists using the feature described below.

Diagram 09. Form Rowsets and Parameters

Form Rowsets and Parameters

Analysts may customize member lists for parameters, rows, and columns.

The fist option is to redefine dbo25.form_dimensions.root_member_id.

For example, you may set the "Sales" account as a parent for the "Sales" form.

The second option is to limit a member list in parameters by levels using the parameter_start_level and parameter_end_level parameters.

For example, the time dimension has a hierarchy: Times - Years - Year - Month.
If you need to show only years in the parameter, set Years as a root_member_id and 1 as parameter_start_level and parameter_end_level.

You may use the same technique for rows and columns.
Just use the form_start_level and form_end_level parameters instead.

For example, to show years only in columns, set Years as a root_member_id and 1 as form_start_level and form_end_level parameters.

And the final option is to define a member list completely manually.

Use it, for example, if you need to have selected rows from multiple hierarchies, add empty lines, or change codes, names, or Excel look of members.

To do this, create a member with calc_type_id = 9 (rowset) and add rows into the dbo25.form_rows table (you copy and paste rows in Excel).
Then rewrite any property of the underlying member in the row.

The application shows rowsets in the ribbon parameters automatically.
If you need to use the rowset only and to disable other options, set the rowset as the root_member_id value and clear the parameter_index value.

Diagram 10. Member Relations

Member Relations

The dbo25.member_relations table contains relations between members of different dimensions.

The first use case is eliminating rows in forms.

For example, the "Sales" account is not applicable for all entities.
So, you may set the select_permission to 0 for such pairs of the account and the required entities.

The second case is disabling data changes, for example, for closed periods.

In this case, you may set the update_permission to 0 for the pair of time and category members.

These settings are common for all users of the application.

Diagram 11. Member Permissions

Member Permissions

The dbo25.member_permissions table contains user permissions for dimension members.

Administrators (members of the planning_app_administrators role) may allow:
- reading (select_permission = 1, update_permission = 0),
- reading and writing (select_permission = 1, update_permission = 1),
or deny reading and writing (select_permission = 0, update_permission = 0).

These modes have mnemonic letters R, W, and D accordingly.

When an administrator sets the permission, the application applies it to children also with the is_inherited = 1 flag.

For example, if the administrator disables the "Sales" account, the user will not have access to the child members of the "Sales" account also.

The administrator can set different permissions for children in this case.
Such direct child permissions have a higher priority.

Analysts and developers may enable or disable verifying permissions using the dbo25.dimensions.is_protected field.

Also, analysts may define the default permissions for dimension members in the dbo25.dimensions table.

The application uses the USER_NAME() function to get the username.

The application checks the permissions for members of the planning_app_users role only.
So, the developers and analysts always see the complete sets of members.

The application does not implement permission management by roles.

Diagram 12. Form Permissions

Form Permissions

The dbo25.form_permissions table contains user permissions on forms.

The application does not allow executing disabled forms and does not show such forms
in the xls25.view_query_list_forms and xls25.view_query_list_offline_forms views used in the SaveToDB Connection Wizard.

The application uses the USER_NAME() function to get the username.

The application checks the permissions for members of the planning_app_users role only.
So, the developers and analysts always see the complete sets of forms.

The application does not implement permission management by roles.

Roles

RoleDescription
planning_app_administratorsThe role includes permissions for planning application administrators.
planning_app_analystsThe role includes permissions for planning application analysts.
planning_app_developersThe role includes permissions for planning application developers.
planning_app_usersThe role includes permissions for planning application users.

planning_app_administrators

The role includes permissions for planning application administrators.

Assign this role to users who manage business user permissions.

This role members have permissions to execute permission management forms and procedures and do not have permissions to execute business reports.

See actual database permissions in the dbo25.xl_actions_set_role_permissions procedure.

planning_app_analysts

The role includes permissions for planning application analysts.

Assign this role to users who configure dimensions, members, reports, and other business-related objects.

This role members have full control over any business-related objects and do not have permissions to manage user permissions and change the application configuration.

See actual database permissions in the dbo25.xl_actions_set_role_permissions procedure.

planning_app_developers

The role includes permissions for planning application developers.

Assign this role to users who configure or develop the application.

This role members have full read and write permissions on any object in the dbo25 and xls25 schemas.

See actual database permissions in the dbo25.xl_actions_set_role_permissions procedure.

planning_app_users

The role includes permissions for planning application users.

Assign this role to business users who work with configured forms only.

This role members may have additional restrictions configured at the application level.

Do not assign this role to users that are members of the roles listed above.

See actual database permissions in the dbo25.xl_actions_set_role_permissions procedure.

Schemas

SchemaDescription
dbo25The schema contains data tables and technical views and procedures of the planning application.
xls25The schema contains end-user views and stored procedures used in the Excel planning application.

dbo25

The schema contains data tables and technical views and procedures of the planning application.

Only business analysts and developers have select permissions on schema objects.

xls25

The schema contains end-user views and stored procedures used in the Excel planning application.

Business users have access to a limited set of schema objects.

See default permissions in the dbo25.xl_actions_set_role_permissions procedure.

Tables

TableDescription
dbo25.axis_typesThis system table contains axis types.
dbo25.calc_typesThis system table contains calculation types.
dbo25.currency_ratesThis user table contains currency rates.
dbo25.dimension_calc_typesThis system table contains available dimension calculation types.
dbo25.dimensionsThis system table contains dimensions.
dbo25.factorsThis application table contains calculation factors for child-parent relations.
dbo25.factsThis application table contains source cube number values.
dbo25.form_dimensionsThis user table contains form dimension properties.
dbo25.form_permissionsThis user table contains form select permissions by users.
dbo25.form_rowsThis user table contains rows of the custom form rowsets.
dbo25.form_subtotalsThis user table contains form subtotal settings.
dbo25.formsThis user table contains a list of forms.
dbo25.hierarchiesThis application table contains child-parent relations.
dbo25.member_permissionsThis application table contains member permissions by users.
dbo25.member_relationsThis application table contains select and update permissions for cross-dimension members.
dbo25.membersThis user table contains dimension members.
dbo25.name_formatsThis system table contains variants of displaying members.
dbo25.parentsThis application table contains source factors for child-parent relations.
dbo25.stringsThis application table contains source string values.
dbo25.tax_ratesThis user table contains tax rates.
dbo25.unitsThis user table contains units.

dbo25.axis_types

This system table contains axis types.

ColumnDataTypeNullComment
idtinyintThe application uses id values directly. Do not change the id values.
codenvarchar(50)You may change the code. However, keep the axis meaning.
namenvarchar(50)You may change the name. However, keep the axis meaning.

Values:

idcodename
1pagePage
2rowsRows
3columnsColumns

Do not add or delete rows. Do not change the id values.

dbo25.calc_types

This system table contains calculation types.

ColumnDataTypeNullComment
idtinyintThe application uses id values directly. Do not change the id values.
codenvarchar(50)You may change the code. However, keep the calculation type meaning.
namenvarchar(400)You may change the name. However, keep the calculation type meaning.
sort_ordertinyintYou may change the sort order to select rows in the required order in Excel.

Values:

idcodenamesort_order
0noneNone0
1totalTotal1
2averageAverage2
3openOpening Balance3
4closeClosing Balance4
5ytdYTD5
6same_periodSame Period6
7excelExcel Formula7
8stringString8
9rowsetRowset9
10rateRate10
11revaluationRevaluation11
12differenceDifference12
13percentPercent13

Do not add or delete rows. Do not change the id values.

dbo25.currency_rates

This user table contains currency rates.

ColumnDataTypeNullComment
base_currency_idtinyintdbo25.units.id
quote_currency_idtinyintdbo25.units.id
time_idintdbo25.members.id

This is a time member.
category_idintdbo25.members.id

This is a category member.
currency_ratefloatCurrency rate

The application calculates dbo25.facts.value values using the dbo25.facts.source_value values and currency rates.

Use the xls25.usp_rates procedure to select and edit currency rates.

Use the xls25.xl_actions_update_facts procedure to recalculate facts after currency rate changes.

dbo25.dimension_calc_types

This system table contains available dimension calculation types.

ColumnDataTypeNullComment
dimension_idtinyintdbo25.dimensions.id
calc_type_idtinyintdbo25.calc_types.id
is_activebitSet 1 to activate and 0 to deactivate the calculation type for the dimension.

Do not add or delete rows. The table contains all the available settings.

Change is_active to activate or deactivate calculation types.

dbo25.dimensions

This system table contains dimensions.

ColumnDataTypeNullComment
idtinyintThe application uses dimension id values directly. Do not change the id values.
codenvarchar(50)You may change the dimension code. However, do not change the meaning of the first four dimensions.
namenvarchar(50)You may change the dimension name. However, do not change the meaning of the first four dimensions.
parameter_namenvarchar(50)Use this field to set the name of the ribbon parameter name for the dimension members.
sort_ordertinyintYou may change the sort order to select dimensions in the required order in Excel.
join_ordertinyintThis field defines the JOIN order in the dynamic procedures. Do not change it.
name_format_idtinyintdbo25.name_formats.id

This field defines the name format used in the ribbon parameters and validation lists for dimension members.
For example, you may select id, code, name, or their pairs.
root_member_idintdbo25.members.id

Set this member to the root member of the dimension members.
default_member_idintdbo25.members.id

Set this member to the member used as the default value in the cube.
The default member must not contain children.
is_protectedbitSet 1 to activate checking SELECT AND UPDATE permissions for dimension members.
Note that in this case, you have to set member permissions directly.
Otherwise, users may not see new members.
default_select_permissionbitSet 0 to hide and 1 to show cube values of the dimension members by default.
Keep 1 at least for the Times and Categories dimensions.
default_update_permissionbitSet 0 to disable and 1 to enable updating cube values of the dimension members by default.
Keep 1 at least for the Times and Categories dimensions.
is_activebitSet 1 to activate or 0 to deactivate dimensions 5-7.
external_idintUse it with custom import-export procedures.
external_codenvarchar(50)Use it with custom import-export procedures.

Values:

idcodenameparameter_namesort_orderjoin_ordername_format_idroot_member_iddefault_member_idis_protecteddefault_select_permissiondefault_update_permissionis_activeexternal_idexternal_code
1accountsAccountsAccount15411001
2timesTimesPeriod26121111
3categoriesCategoriesCategory33131111
4entitiesEntitiesEntity44141001
5dim5RegionsRegion5115460110
6dim6ProductsProduct6216480110
7dim7SubaccountsSubaccount7717500110

The application has seven built-in dimensions. Adding more dimensions requires application changes.

The first four dimensions (Accounts, Times, Categories, and Entities) have built-in business logic. Do not deactivate them and keep their meaning.

You may activate and deactivate dimensions 5-7, and change their meaning.

Use the xls25.usp_dimensions procedure to select and edit dimensions in Microsoft Excel.

dbo25.factors

This application table contains calculation factors for child-parent relations.

ColumnDataTypeNullComment
member_idintdbo25.members.id
parent_idintdbo25.members.id
calc_type_idtinyintdbo25.calc_types.id
factorfloatFactor to calculate the parent value

The application uses this table to calculate aggregated cube values.

Use the xls25.xl_actions_update_hierarchies procedure to update this table.
Do not change the table data manually.

Use the xls25.view_hierarchies view to select factors.

dbo25.facts

This application table contains source cube number values.

ColumnDataTypeNullComment
idintIdentity
id1intdbo25.members.id

Accounts
id2intdbo25.members.id

Times
id3intdbo25.members.id

Categories
id4intdbo25.members.id

Entities
id5intdbo25.members.id

Dimension 5
id6intdbo25.members.id

Dimension 6
id7intdbo25.members.id

Dimension 7
valuefloatThis field contains calculated values that depend on unit factors and currency rates.
source_valuefloatThis field contains user input values as is.

Fields id1-id7 must have member id values. The application uses the dbo25.dimensions.default_member_id field value for inactive dimensions.

The application keeps the uniqueness of id1-id7 key values programmatically.

Use the dbo25.view_data view to select the table data.
Use the dbo25.view_facts view to select the calculated cube data.

Use the xls25.xl_actions_update_facts procedure to recalculate cube values after currency rate changes.

dbo25.form_dimensions

This user table contains form dimension properties.

ColumnDataTypeNullComment
form_idintdbo25.forms.id
dimension_idtinyintdbo25.dimensions.id
axis_type_idtinyintdbo25.axis_types.id
parameter_indextinyintUse this field to set the order of the parameter at the ribbon.
The index must be unique within the form scope and have values between 1 and 7.
header_ordertinyintUse this field to set the order of the dimension in the form rows and columns.
The index must be unique within the form axis scope and have values between 1 and 3.
root_member_idintdbo25.members.id

Set this member to replace the dbo25.dimensions.root_member_id value for the form.
For example, if you create a form like "Sales", then you may set the root account member as "Sales".
The application uses this member as a base for the following fields.
parameter_start_leveltinyintSet this optional value to show child members of the root member in the ribbon parameters starting at the specified level.
parameter_end_leveltinyintSet this optional value to show child members of the root member in the ribbon parameters ending at the specified level.
form_start_leveltinyintSet this optional value to show child members of the root member in the form rows and columns starting at the specified level.
form_end_leveltinyintSet this optional value to show child members of the root member in the form rows and columns ending at the specified level.

This table contains dimension settings for forms executed with the xls25.usp_run_form and xls25.usp_run_offline_form procedures.

Use the xls25.usp_form_dimensions procedure to select and edit settings in Microsoft Excel.

dbo25.form_permissions

This user table contains form select permissions by users.

ColumnDataTypeNullComment
form_idintdbo25.forms.id
usernamenvarchar(128)The application uses the USER_NAME() function to get the username.
select_permissionbitSet 1 to allow the user to select the form.

The application applies these form permissions for users of the planning_app_users role only.

Use the xls25.usp_form_permissions procedure to select and edit permissions in Microsoft Excel.

dbo25.form_rows

This user table contains rows of the custom form rowsets.

ColumnDataTypeNullComment
idintIdentity
rowset_idintdbo25.members.id

Rowset members must have the Rowset calculation type.
The application shows only such members.
sort_orderintSet the sort order to select rows in the required order in Microsoft Excel.
member_idintdbo25.members.id

You may omit the member to create a blank row.
codenvarchar(50)Set this value to replace the member code.
namenvarchar(400)Set this value to replace the member name.
decimal_placestinyintSet this value to replace the member decimal_places value.
is_percentbitSet this value to replace the member is_percent value.
row_colortinyintSet this value to replace the member row_color value.
row_boldtinyintSet this value to replace the member row_bold value.
row_indenttinyintSet this value to replace the member row_indent value.

You may create custom form rowsets for any dimension.

Just add a new member to the dbo25.members table and specify the Rowset calculation type. Then specify form rows in this table.

You may specify a member and replace its values to change the member row look in Microsoft Excel.

The application selects the decimal_places, is_percent, row_color, row_bold, and row_indent fields as is.
So, you may customize the form look in Excel using the conditional formatting.

Use the xls25.usp_form_rows procedure to select and edit rows in Microsoft Excel.

dbo25.form_subtotals

This user table contains form subtotal settings.

ColumnDataTypeNullComment
idintIdentity
form_idintdbo25.forms.id
dimension_id1tinyintdbo25.dimensions.id
member_id1intdbo25.members.id
dimension_id2tinyintdbo25.dimensions.id
member_id2intdbo25.members.id
dimension_id3tinyintdbo25.dimensions.id
member_id3intdbo25.members.id
showbitSet 1 to show and 0 to hide subtotals.

The forms may have up to thee dimensions in rows.

The application shows subtotals for every dimension automatically.
Using this table, you may hide subtotals that you do not need.

For example, the form shows accounts ("Sales"), customers, and products. To disable all subtotals, specify the account, customer, and product dimensions in the columns.

To disable specific member subtotals, specify the dimension member.
You may create several rows to combine conditions.

Use the xls25.usp_form_subtotals procedure to select and edit subtotal settings in Microsoft Excel.

dbo25.forms

This user table contains a list of forms.

ColumnDataTypeNullComment
idintIdentity
codenvarchar(50)The code field values must not contain spaces.
namenvarchar(255)Try to keep names short.
sort_orderintSet the sort order to select forms in the required order in the ribbon Query List.
is_activebitSet 0 to exclude the form the ribbon Query List.

You may create and customize any number of editable forms with zero coding.

The SaveToDB add-in shows such forms in the ribbon Query List when you select the xls25.view_query_list_forms or xls25.view_query_list_offline_forms views in the Connection Wizard.

Analysts may add new forms anytime. Users should just update the ribbon Query List.

The application uses the xls25.usp_run_form and xls25.usp_run_offline_form procedures to run forms.

dbo25.hierarchies

This application table contains child-parent relations.

ColumnDataTypeNullComment
member_idintdbo25.members.id
parent_idintdbo25.members.id
levelintThe field contains a number of levels between members.

The application uses this table to select hierarchies and detect levels between related members.

Use the xls25.xl_actions_update_hierarchies procedure to update this table. Do not change the table data manually.

Use the xls25.view_hierarchies view to select the table data.

dbo25.member_permissions

This application table contains member permissions by users.

ColumnDataTypeNullComment
member_idintdbo25.members.id
usernamenvarchar(128)The application uses the USER_NAME() function to get the username.
select_permissionbitSELECT permission values: 1 - yes, 0 - no
update_permissionbitUPDATE permission values: 1 - yes, 0 - no
is_inheritedbitWhen you set the permission for the parent member, the application applies this permission to the children. This field contains 1 for such inherited permissions.
You may set the direct permission for any child to replace the inherited permissions.
permissionvarchar(1)This field contains user input permission values (R - read, W - Read/Write, D - Deny) and inherited member permissions (r - read, w - read/write, d - deny).

The application applies these member permissions for users of the planning_app_users role only.

The application updates this table programmatically. Do not edit it manually.

Use the xls25.usp_member_permissions procedure to select and edit permissions in Microsoft Excel.

dbo25.member_relations

This application table contains select and update permissions for cross-dimension members.

ColumnDataTypeNullComment
member_idintdbo25.members.id
partner_idintdbo25.members.id
select_permissionbitSELECT permission values: 1 - yes, 0 - no
update_permissionbitUPDATE permission values: 1 - yes, 0 - no

This table allows eliminating form rows for pairs of different dimension members.
For example, you may exclude certain accounts for certain entities.

Use the xls25.usp_member_relations procedure to edit relation permissions.

Also, this table allows disabling updating data for the desired pairs.
The most common case is closing the period for changes for the specified category.
To close periods, use the xls25.usp_closed_periods procedure.

dbo25.members

This user table contains dimension members.

ColumnDataTypeNullComment
idintIdentity
dimension_idtinyintdbo25.dimensions.id
sort_orderintSet the sort order to select members in the required order in Microsoft Excel.
codenvarchar(50)The code values are the most important.
The application uses codes in forms and import-export procedures to find member id values.
Try to keep the codes constant.

Do not use spaces in the codes.
namenvarchar(400)You may change member names anytime.
commentnvarchar(1000)Use this field to add extended comments for business users.
tax_rate_idtinyintdbo25.tax_rates.id

This field is for informational purposes only.

Use it to separate the same accounts with different tax rates.
unit_idtinyintdbo25.units.id

The application uses the dbo25.units.factor value to calculate the dbo25.facts.value value based on dbo25.facts.source_value.
calc_type_idtinyintdbo25.calc_types.id

The application uses this field value to calculate member aggregates.
previous_period_idintdbo25.members.id

This field contains a member of the previous period for the Time dimension.
For example, the February member must contain the id of the January member.
same_period_idintdbo25.members.id

This field contains a member of the same period for the Time dimension.
For example, the February member must contain the id of the February member of the previous year.
decimal_placestinyintUse this field to customize form look in Microsoft Excel using conditional formatting.
is_percentbitUse this field to customize form look in Microsoft Excel using conditional formatting.
row_colortinyintUse this field to customize form look in Microsoft Excel using conditional formatting.
row_boldtinyintUse this field to customize form look in Microsoft Excel using conditional formatting.
row_indenttinyintUse this field to customize form look in Microsoft Excel using conditional formatting.
is_activebitSet 0 to hide the member.
external_idintUse this field with custom import-export procedures.
external_codenvarchar(50)Use this field with custom import-export procedures.
excel_formulanvarchar(400)You may specify an Excel formula to calculate member aggregates in Microsoft Excel.

To use this feature, the form must select cells used as arguments in the correct order.
cube_formulanvarchar(max)This field is for informational purposes only.

The application updates formulas automatically with the xls25.xl_actions_update_hierarchies procedure.

Use the xls25.usp_members procedure to select and edit dimension members in Microsoft Excel.

Do not use spaces in member codes.

dbo25.name_formats

This system table contains variants of displaying members.

ColumnDataTypeNullComment
idtinyintThe application uses id values directly. Do not change the id values.
codenvarchar(50)You may change the code. However, keep the name format meaning.
namenvarchar(50)You may change the name. However, keep the name format meaning.

Values:

idcodename
0codecode
1namename
2idid
3id_nameid - name
4code_namecode - name

You may change the display format at any time.
Reload data and configuration in Microsoft Excel to reload ribbon parameters and validation lists with new values.

Do not add or delete rows. Do not change the id values.

dbo25.parents

This application table contains source factors for child-parent relations.

ColumnDataTypeNullComment
member_idintdbo25.members.id
parent_idintdbo25.members.id
factorfloatThe field contains source factors specified in the xls25.usp_members form.

This table contains source input values.

The xls25.xl_actions_update_hierarchies procedure updates the dbo25.hierarchies table used in forms. Execute it after each change to update related factors and hierarchies.

Use the xls25.usp_members form to set member parents.
You may check child-parent pairs using the xls25.view_hierarchies view.

dbo25.strings

This application table contains source string values.

ColumnDataTypeNullComment
idintIdentity
id1intdbo25.members.id

Accounts
id2intdbo25.members.id

Times
id3intdbo25.members.id

Categories
id4intdbo25.members.id

Entities
id5intdbo25.members.id

Dimension 5
id6intdbo25.members.id

Dimension 6
id7intdbo25.members.id

Dimension 7
valuenvarchar(4000)This field contains user input string values as is.

To allow input string values, set the account member calculation type as String.

The application does not "aggregate" string values for parents.
So, the table may contain source values for any level of the hierarchy, unlike the dbo25.facts table that contains values for children only.

Fields id1-id7 must have member id values. The application uses the dbo25.dimensions.default_member_id field value for inactive dimensions.

The application keeps the uniqueness of id1-id7 key values programmatically.

Use the dbo25.view_strings view to select the table data.

dbo25.tax_rates

This user table contains tax rates.

ColumnDataTypeNullComment
idtinyintDo not delete the default id 0.
codenvarchar(50)You may change codes.
namenvarchar(50)You may change names.
sort_ordertinyintSet the sort order to select members in the required order in Microsoft Excel.
factorfloatThe field is reserved for future use.
is_activebitSet 0 to hide the member.

Values:

idcodenamesort_orderfactoris_active
0default11

The application shows tax rates for informational purposes only and does not use the values for calculation.

Use separate accounts with appropriate parent factors to calculate taxes.

You may add the required tax rate members in this table directly.

dbo25.units

This user table contains units.

ColumnDataTypeNullComment
idtinyintDo not delete the row with id 0 used as the system currency.
codenvarchar(50)Specify meaningful codes. For example, USD or ton. Do not use spaces in the codes.
namenvarchar(50)Specify meaningful names. For example, USD or ton.
sort_ordertinyintSet the sort order to select members in the required order in Microsoft Excel.
factorfloatSpecify the factor to calculate dbo25.facts.value based on dbo25.facts.source_value.
So, the forms show source values while the cube contains both.
is_currencybitSet 1 for currency units and 0 for others.
is_base_currencybitSet 1 for the base currency and 0 for the quote currencies.
For example, set 1 for EUR and 0 for USD in the EUR/USD pair.
is_activebitSet 0 to hide the member.

Values:

idcodenamesort_orderfactoris_currencyis_base_currencyis_active
0USDUSD11101
1EUREUR21101
11unitsUnits111001

This table is an important part of the calculations.
Add and configure its members in the first steps.

The member with id 0 is the system currency used to calculate values of the dbo25.facts.value column.

You may change the system currency using the xls25.xl_actions_set_system_currency procedure (from the context menu in Excel).

Use the xls25.usp_units form to edit and customize units.

Views

ViewDescription
dbo25.view_dataThis view selects source cube data.
dbo25.view_factsThis view selects calculated facts.
dbo25.view_query_listThis view selects developer objects for the SaveToDB Connection Wizard and SaveToDB Query List.
dbo25.view_stringsThis view selects cube string data.
xls25.view_formatsThis view selects table formats of the forms configured in dbo25.forms.
xls25.view_handlersThis view selects the handler configuration of the forms configured in dbo25.forms.
xls25.view_hierarchiesThis view selects member parents, factors, and hierarchies.
xls25.view_query_listThis view selects user objects for the SaveToDB Connection Wizard and SaveToDB Query List.
xls25.view_query_list_formsThis view selects user forms for the SaveToDB Connection Wizard and SaveToDB Query List.
xls25.view_query_list_offline_formsThis view selects offline user forms for the SaveToDB Connection Wizard and SaveToDB Query List.
xls25.view_translationsThis view selects translations of the forms configured in dbo25.forms.

dbo25.view_data

This view selects source cube data.

Source tables: dbo25.calc_types, dbo25.facts, dbo25.members, dbo25.units

ColumnDataTypeComment
idintdbo25.facts.id
id1intdbo25.facts.id1 - Accounts
id2intdbo25.facts.id2 - Times
id3intdbo25.facts.id3 - Categories
id4intdbo25.facts.id4 - Entities
id5intdbo25.facts.id5 - Dimension 5
id6intdbo25.facts.id6 - Dimension 6
id7intdbo25.facts.id7 - Dimension 7
unit_idtinyintdbo25.members.unit_id value of the id1 member
calc_type_idtinyintdbo25.members.calc_type_id value of the id1 member
code1nvarchar(50)id1 member code
name1nvarchar(400)id1 member name
code2nvarchar(50)id2 member code
name2nvarchar(400)id2 member name
code3nvarchar(50)id3 member code
name3nvarchar(400)id3 member name
code4nvarchar(50)id4 member code
name4nvarchar(400)id4 member name
code5nvarchar(50)id5 member code
name5nvarchar(400)id5 member name
code6nvarchar(50)id6 member code
name6nvarchar(400)id6 member name
code7nvarchar(50)id7 member code
name7nvarchar(400)id7 member name
unitnvarchar(50)Account unit_id code
calc_typenvarchar(400)Account calc_type_id code
source_valuefloatdbo25.facts.source_value
valuefloatdbo25.facts.value

Use this view to check and edit source cube values directly.

dbo25.view_facts

This view selects calculated facts.

Source tables: dbo25.calc_types, dbo25.factors, dbo25.facts, dbo25.members, dbo25.units

ColumnDataTypeComment
idintdbo25.facts.id
The value is NULL for calculated values.
id1intdbo25.facts.id1 - Accounts
id2intdbo25.facts.id2 - Times
id3intdbo25.facts.id3 - Categories
id4intdbo25.facts.id4 - Entities
id5intdbo25.facts.id5 - Dimension 5
id6intdbo25.facts.id6 - Dimension 6
id7intdbo25.facts.id7 - Dimension 7
unit_idtinyintdbo25.members.unit_id value of the id1 member
calc_type_idtinyintdbo25.members.calc_type_id value of the id1 member
code1nvarchar(50)id1 member code
name1nvarchar(400)id1 member name
code2nvarchar(50)id2 member code
name2nvarchar(400)id2 member name
code3nvarchar(50)id3 member code
name3nvarchar(400)id3 member name
code4nvarchar(50)id4 member code
name4nvarchar(400)id4 member name
code5nvarchar(50)id5 member code
name5nvarchar(400)id5 member name
code6nvarchar(50)id6 member code
name6nvarchar(400)id6 member name
code7nvarchar(50)id7 member code
name7nvarchar(400)id7 member name
unitnvarchar(50)Account unit_id code
calc_typenvarchar(400)Account calc_type_id code
source_valuefloatdbo25.facts.source_value
valuefloatdbo25.facts.value
is_formulaint0 - source value, 1 - calculated value

This view selects complete calculated cube values.

You may load the data into your BI tool, for example, PowerPivot.

dbo25.view_query_list

This view selects developer objects for the SaveToDB Connection Wizard and SaveToDB Query List.

Source tables: xls.queries

ColumnDataTypeComment
TABLE_SCHEMAnvarchar(128)xls.queries.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)xls.queries.TABLE_NAME
TABLE_TYPEnvarchar(128)xls.queries.TABLE_TYPE
TABLE_CODEnvarchar(max)xls.queries.TABLE_CODE
INSERT_PROCEDUREnvarchar(max)xls.queries.INSERT_PROCEDURE
UPDATE_PROCEDUREnvarchar(max)xls.queries.UPDATE_PROCEDURE
DELETE_PROCEDUREnvarchar(max)xls.queries.DELETE_PROCEDURE
PROCEDURE_TYPEnvarchar(50)xls.queries.PROCEDURE_TYPE

The view filters output of the SaveToDB Framework xls.queries view to show planning application objects only.

See also Configuring Query Lists.

dbo25.view_strings

This view selects cube string data.

Source tables: dbo25.members, dbo25.strings

ColumnDataTypeComment
idintdbo25.strings.id
id1intdbo25.strings.id1 - Accounts
id2intdbo25.strings.id2 - Times
id3intdbo25.strings.id3 - Categories
id4intdbo25.strings.id4 - Entities
id5intdbo25.strings.id5 - Dimension 5
id6intdbo25.strings.id6 - Dimension 6
id7intdbo25.strings.id7 - Dimension 7
unit_idtinyintdbo25.members.unit_id value of the id1 member
calc_type_idtinyintdbo25.members.calc_type_id value of the id1 member
code1nvarchar(50)id1 member code
name1nvarchar(400)id1 member name
code2nvarchar(50)id2 member code
name2nvarchar(400)id2 member name
code3nvarchar(50)id3 member code
name3nvarchar(400)id3 member name
code4nvarchar(50)id4 member code
name4nvarchar(400)id4 member name
code5nvarchar(50)id5 member code
name5nvarchar(400)id5 member name
code6nvarchar(50)id6 member code
name6nvarchar(400)id6 member name
code7nvarchar(50)id7 member code
name7nvarchar(400)id7 member name
valuenvarchar(4000)dbo25.strings.value

Use this view to check and edit source cube values directly.

xls25.view_formats

This view selects table formats of the forms configured in dbo25.forms.

Source tables: dbo25.forms, xls.formats

ColumnDataTypeComment
TABLE_SCHEMAvarchar(13)xls.formats.TABLE_SCHEMA
TABLE_NAMEnvarchar(50)xls.formats.TABLE_NAME
TABLE_EXCEL_FORMAT_XMLxmlxls.formats.TABLE_EXCEL_FORMAT_XML

This view selects table formats of the configured forms for the SaveToDB add-in.

It uses the format of xls25.usp_run_form for every form that has no format.

Use the SaveToDB Table Format Wizard to save table formats.

See also Configuring Table Formats.

xls25.view_handlers

This view selects the handler configuration of the forms configured in dbo25.forms.

Source tables: dbo25.dimensions, dbo25.form_dimensions, dbo25.forms, dbo25.members, dbo25.parents, xls.handlers

ColumnDataTypeComment
TABLE_SCHEMAvarchar(13)Schema of the configured object: forms, offline_forms, or xls25
TABLE_NAMEnvarchar(50)Name of the configured object
COLUMN_NAMEnvarchar(128)Column or parameter name of the configured object
EVENT_NAMEvarchar(25)xls.handlers.EVENT_NAME
HANDLER_SCHEMAnvarchar(20)Schema of the handler object: xls25
HANDLER_NAMEnvarchar(128)Name the handler object
HANDLER_TYPEvarchar(25)xls.handlers.HANDLER_TYPE
HANDLER_CODEnvarchar(max)SQL code of the handler object
TARGET_WORKSHEETnvarchar(255)xls.handlers.TARGET_WORKSHEET
MENU_ORDERbigintxls.handlers.MENU_ORDER
EDIT_PARAMETERSbitxls.handlers.EDIT_PARAMETERS

This view selects event handler configurations of the configured forms for the SaveToDB add-in.

See also Configuring Event Handlers.

xls25.view_hierarchies

This view selects member parents, factors, and hierarchies.

Source tables: dbo25.calc_types, dbo25.dimensions, dbo25.factors, dbo25.hierarchies, dbo25.members, dbo25.parents

ColumnDataTypeComment
dimension_idtinyintdbo25.members.dimension_id
dimension_namenvarchar(50)dbo25.dimensions.name
table_namevarchar(11)The field shows the source table: dbo25.parents, dbo25.hierarchies, or dbo25.factors.
member_idintChild member ID
parent_idintParent member ID
calc_type_idtinyintdbo25.factors.calc_type_id
membernvarchar(453)Child member display name
parentnvarchar(453)Parent member display name
calc_typenvarchar(400)dbo25.calc_types.name
factorfloatdbo25.parents.factor or dbo25.factors.factor
levelintdbo25.hierarchies.level

Use this view to check member relation properties generated by the application.

xls25.view_query_list

This view selects user objects for the SaveToDB Connection Wizard and SaveToDB Query List.

Source tables: xls.queries

ColumnDataTypeComment
TABLE_SCHEMAnvarchar(128)xls.queries.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)xls.queries.TABLE_NAME
TABLE_TYPEnvarchar(128)xls.queries.TABLE_TYPE
TABLE_CODEnvarchar(max)xls.queries.TABLE_CODE
INSERT_PROCEDUREnvarchar(max)xls.queries.INSERT_PROCEDURE
UPDATE_PROCEDUREnvarchar(max)xls.queries.UPDATE_PROCEDURE
DELETE_PROCEDUREnvarchar(max)xls.queries.DELETE_PROCEDURE
PROCEDURE_TYPEnvarchar(50)xls.queries.PROCEDURE_TYPE

The view filters output of the SaveToDB Framework xls.queries view.

See also Configuring Query Lists.

xls25.view_query_list_forms

This view selects user forms for the SaveToDB Connection Wizard and SaveToDB Query List.

Source tables: dbo25.dimensions, dbo25.form_dimensions, dbo25.form_permissions, dbo25.forms

ColumnDataTypeComment
TABLE_SCHEMAvarchar(5)Configured form schema: forms
TABLE_NAMEnvarchar(50)dbo25.forms.code
TABLE_TYPEvarchar(4)Type of the configured object: CODE
TABLE_CODEnvarchar(max)SQL code to execute the xls25.run_form procedure with the form parameters
INSERT_PROCEDUREnvarchar(max)NULL
UPDATE_PROCEDUREnvarchar(max)NULL
DELETE_PROCEDUREnvarchar(max)NULL
PROCEDURE_TYPEnvarchar(128)NULL

Use this view to connect to configured forms in the SaveToDB Data Connection Wizard in the business user workbooks.

The forms of this list use cell change event handlers to update the underlying cube values. So, this feature requires the SaveToDB Enterprise edition.

This view shows configured forms with actual select permissions.

See also Configuring Query Lists.

xls25.view_query_list_offline_forms

This view selects offline user forms for the SaveToDB Connection Wizard and SaveToDB Query List.

Source tables: dbo25.dimensions, dbo25.form_dimensions, dbo25.form_permissions, dbo25.forms

ColumnDataTypeComment
TABLE_SCHEMAvarchar(13)Configured offline form schema: offline_forms
TABLE_NAMEnvarchar(50)dbo25.forms.code
TABLE_TYPEvarchar(4)Type of the configured object: CODE
TABLE_CODEnvarchar(max)SQL code to execute the xls25.run_offline_form procedure with the form parameters
INSERT_PROCEDUREvarchar(33)xls25.usp_run_offline_form_insert
UPDATE_PROCEDUREvarchar(33)xls25.usp_run_offline_form_update
DELETE_PROCEDUREvarchar(33)xls25.usp_run_offline_form_delete
PROCEDURE_TYPEvarchar(9)Type of edit procedures for information purposes: PROCEDURE

Use this view to connect to configured forms in the SaveToDB Data Connection Wizard in the business user workbooks.

The forms of this list use edit procedures to update the underlying cube values. Users must click the Save button to save changes.

This feature works with the SaveToDB Standard edition.

This view shows configured forms with actual select permissions.

See also Configuring Query Lists.

xls25.view_translations

This view selects translations of the forms configured in dbo25.forms.

Source tables: dbo25.dimensions, dbo25.form_dimensions, dbo25.forms, xls.translations

ColumnDataTypeComment
TABLE_SCHEMAvarchar(13)xls.translations.TABLE_SCHEMA
TABLE_NAMEnvarchar(50)xls.translations.TABLE_NAME
COLUMN_NAMEnvarchar(128)xls.translations.COLUMN_NAME
LANGUAGE_NAMEvarchar(2)xls.translations.LANGUAGE_NAME
TRANSLATED_NAMEnvarchar(255)xls.translations.TRANSLATED_NAME
TRANSLATED_DESCnvarchar(1024)xls.translations.TRANSLATED_DESC
TRANSLATED_COMMENTnvarchar(max)xls.translations.TRANSLATED_COMMENT

This view selects translations of the configured forms for the SaveToDB add-in.

See also Configuring Data Translation.

Procedures

ProcedureDescription
dbo25.usp_export_allThis procedure exports all data.
dbo25.usp_export_currency_ratesThis procedure exports currency rates.
dbo25.usp_export_dimensionsThis procedure exports dimensions.
dbo25.usp_export_factsThis procedure exports source numbers.
dbo25.usp_export_formsThis procedure exports forms and form settings.
dbo25.usp_export_membersThis procedure exports members.
dbo25.usp_export_stringsThis procedure exports source strings.
dbo25.usp_export_tax_ratesThis procedure exports tax rates.
dbo25.usp_export_unitsThis procedure exports units.
dbo25.usp_import_clear_all_dataThis procedure clears all data before importing data.
dbo25.usp_import_currency_ratesThis procedure imports currency rates.
dbo25.usp_import_dimensionThis procedure imports dimensions.
dbo25.usp_import_dimension_calc_typesThis procedure imports calculation types by dimensions.
dbo25.usp_import_dimension_propertiesThis procedure imports dimension properties.
dbo25.usp_import_factThis procedure imports source numbers.
dbo25.usp_import_formThis procedure imports forms.
dbo25.usp_import_form_dimensionThis procedure imports form dimension properties.
dbo25.usp_import_form_permissionsThis procedure imports form permissions.
dbo25.usp_import_form_rowThis procedure imports custom form rowsets.
dbo25.usp_import_form_subtotalsThis procedure imports form subtotal settings.
dbo25.usp_import_memberThis procedure imports dimension members.
dbo25.usp_import_member_parentThis procedure imports member parents.
dbo25.usp_import_member_permissionsThis procedure imports member permissions.
dbo25.usp_import_member_propertiesThis procedure imports member properties.
dbo25.usp_import_member_relationsThis procedure imports member relations.
dbo25.usp_import_stringThis procedure imports source strings.
dbo25.usp_import_tax_ratesThis procedure imports tax rates.
dbo25.usp_import_unitThis procedure imports units.
dbo25.xl_actions_create_standard_membersThis procedure creates standard dimension members.
dbo25.xl_actions_set_role_permissionsThis procedure grants permissions to the planning application roles.
xls25.usp_calc_typesThis procedure is an Excel form for editing calculation types.
xls25.usp_closed_periodsThis procedure is an Excel form for editing closed periods.
xls25.usp_closed_periods_changeThis procedure updates a database on cell changes of xls25.usp_closed_periods.
xls25.usp_currency_ratesThis procedure is an Excel form for editing currency rates.
xls25.usp_currency_rates_changeThis procedure updates a database on cell changes of xls25.usp_rates.
xls25.usp_data_managementThis procedure is an Excel form for data management.
xls25.usp_dimensionsThis procedure is an Excel form for editing dimensions.
xls25.usp_dimensions_changeThis procedure checks user input on cell changes of xls25.usp_dimensions.
xls25.usp_dimensions_deleteThis procedure deletes rows of xls25.usp_dimensions.
xls25.usp_dimensions_insertThis procedure inserts rows of xls25.usp_dimensions.
xls25.usp_dimensions_updateThis procedure updates rows of xls25.usp_dimensions.
xls25.usp_form_dimensionsThis procedure is an Excel form for editing form dimension properties.
xls25.usp_form_dimensions_deleteThis procedure deletes rows of xls25.usp_form_dimensions.
xls25.usp_form_dimensions_insertThis procedure inserts rows of xls25.usp_form_dimensions.
xls25.usp_form_dimensions_updateThis procedure updates rows of xls25.usp_form_dimensions.
xls25.usp_form_permissionsThis procedure is an Excel form for editing form permissions.
xls25.usp_form_permissions_changeThis procedure updates a database on cell changes of xls25.usp_form_permissions.
xls25.usp_form_rowsThis procedure is an Excel form for editing form rowsets.
xls25.usp_form_rows_deleteThis procedure deletes rows of xls25.usp_form_rows.
xls25.usp_form_rows_insertThis procedure inserts rows of xls25.usp_form_rows.
xls25.usp_form_rows_updateThis procedure updates rows of xls25.usp_form_rows.
xls25.usp_formsThis procedure is an Excel form for editing a list of forms.
xls25.usp_forms_deleteThis procedure deletes rows of xls25.usp_forms.
xls25.usp_forms_insertThis procedure inserts rows of xls25.usp_forms.
xls25.usp_forms_updateThis procedure updates rows of xls25.usp_forms.
xls25.usp_member_permissionsThis procedure is an Excel form for editing member permissions.
xls25.usp_member_permissions_changeThis procedure updates a database on cell changes of xls25.usp_member_permissions.
xls25.usp_member_relationsThis procedure is an Excel form for editing member relations.
xls25.usp_member_relations_changeThis procedure updates a database on cell changes of xls25.usp_member_relations.
xls25.usp_membersThis procedure is an Excel form for editing dimension members.
xls25.usp_members_deleteThis procedure deletes rows of xls25.usp_members.
xls25.usp_members_insertThis procedure inserts rows of xls25.usp_members.
xls25.usp_members_updateThis procedure updates rows of xls25.usp_members.
xls25.usp_rowsetsThis procedure is an Excel form for editing rowset members.
xls25.usp_rowsets_deleteThis procedure deletes rows of xls25.usp_rowsets.
xls25.usp_rowsets_insertThis procedure inserts rows of xls25.usp_rowsets.
xls25.usp_rowsets_updateThis procedure updates rows of xls25.usp_rowsets.
xls25.usp_run_formThis procedure executes forms configured using dbo25.forms and dbo25.form_dimensions.
xls25.usp_run_form_changeThis procedure updates a database on cell changes of xls25.usp_run_form.
xls25.usp_run_json_formThis procedure returns calculated cube data requested with JSON parameters.
xls25.usp_run_offline_formThis procedure executes forms configured using dbo25.forms and dbo25.form_dimensions.
xls25.usp_run_offline_form_deleteThis procedure deletes rows of xls25.usp_run_offline_form.
xls25.usp_run_offline_form_insertThis procedure inserts rows of xls25.usp_run_offline_form.
xls25.usp_run_offline_form_updateThis procedure updates rows of xls25.usp_run_offline_form.
xls25.usp_tax_ratesThis procedure is an Excel form for editing tax rates.
xls25.usp_unitsThis procedure is an Excel form for editing units.
xls25.xl_actions_add_cash_flow_exampleThis procedure adds members and settings of the cash flow example.
xls25.xl_actions_add_cost_center_exampleThis procedure adds members and settings of the cost center example.
xls25.xl_actions_add_quartersThis procedure adds quarter members.
xls25.xl_actions_add_yearThis procedure adds year members.
xls25.xl_actions_copy_dataThis procedure copies data from a category to category.
xls25.xl_actions_delete_dataThis procedure deletes source cube data from a category.
xls25.xl_actions_delete_yearThis procedure deletes year members.
xls25.xl_actions_run_form_cell_dataThis procedure selects underlying cube data for a reporting cell.
xls25.xl_actions_set_system_currencyThis procedure changes the system currency of the application.
xls25.xl_actions_update_factsThis procedure updates cube values after currency rate changes.
xls25.xl_actions_update_hierarchiesThis procedure updates required tables after member changes.
xls25.xl_actions_update_member_permissionsThis procedure updates member permissions after member changes.
xls25.xl_parameter_values_0_or_1This procedure selects 0 or 1 for Excel ribbon parameters.
xls25.xl_parameter_values_calc_type_idThis procedure selects calculation types for Excel ribbon parameters.
xls25.xl_parameter_values_calc_type_id_or_nullThis procedure selects calculation types for Excel ribbon parameters.
xls25.xl_parameter_values_currency_idThis procedure selects currencies for Excel ribbon parameters.
xls25.xl_parameter_values_dimension_idThis procedure selects dimensions for Excel ribbon parameters.
xls25.xl_parameter_values_dimension_id_or_nullThis procedure selects dimensions for Excel ribbon parameters.
xls25.xl_parameter_values_form_idThis procedure selects forms for Excel ribbon parameters.
xls25.xl_parameter_values_form_id_or_nullThis procedure selects forms for Excel ribbon parameters.
xls25.xl_parameter_values_member_id_by_dimension_idThis procedure selects members for Excel ribbon parameters.
xls25.xl_parameter_values_member_id_dim1This procedure selects 1st dimension members for Excel ribbon parameters.
xls25.xl_parameter_values_member_id_dim2This procedure selects 2nd dimension members for Excel ribbon parameters.
xls25.xl_parameter_values_member_id_dim3This procedure selects 3rd dimension members for Excel ribbon parameters.
xls25.xl_parameter_values_member_id_dim4This procedure selects 4th dimension members for Excel ribbon parameters.
xls25.xl_parameter_values_member_id_dim5This procedure selects 5th dimension members for Excel ribbon parameters.
xls25.xl_parameter_values_member_id_dim6This procedure selects 6th dimension members for Excel ribbon parameters.
xls25.xl_parameter_values_member_id_dim7This procedure selects 7th dimension members for Excel ribbon parameters.
xls25.xl_parameter_values_relation_dimension_idThis procedure selects dimensions for Excel ribbon parameters.
xls25.xl_parameter_values_relation_partner_idThis procedure selects members for Excel ribbon parameters.
xls25.xl_parameter_values_root_member_idThis procedure selects possible root members for Excel ribbon parameters.
xls25.xl_parameter_values_rowset_idThis procedure selects rowsets for Excel ribbon parameters.
xls25.xl_parameter_values_rowset_id_or_nullThis procedure selects rowsets for Excel ribbon parameters.
xls25.xl_parameter_values_run_form_pThis procedure selects values for the ribbon parameters of the configured forms.
xls25.xl_parameter_values_run_form_p1This procedure selects values for the 1st ribbon parameter of the configured forms.
xls25.xl_parameter_values_run_form_p2This procedure selects values for the 2nd ribbon parameter of the configured forms.
xls25.xl_parameter_values_run_form_p3This procedure selects values for the 3rd ribbon parameter of the configured forms.
xls25.xl_parameter_values_run_form_p4This procedure selects values for the 4th ribbon parameter of the configured forms.
xls25.xl_parameter_values_run_form_p5This procedure selects values for the 5th ribbon parameter of the configured forms.
xls25.xl_parameter_values_run_form_p6This procedure selects values for the 6th ribbon parameter of the configured forms.
xls25.xl_parameter_values_run_form_p7This procedure selects values for the 7th ribbon parameter of the configured forms.
xls25.xl_parameter_values_unit_idThis procedure selects units for Excel ribbon parameters.
xls25.xl_parameter_values_usernameThis procedure selects usernames for Excel ribbon parameters.
xls25.xl_validation_list_axis_type_idThis procedure selects axis types to use as an Excel validation list source.
xls25.xl_validation_list_calc_type_id_by_dimension_idThis procedure selects calculation types to use as an Excel validation list source.
xls25.xl_validation_list_default_member_idThis procedure selects possible default members to use as an Excel validation list source.
xls25.xl_validation_list_dimension_idThis procedure selects dimensions to use as an Excel validation list source.
xls25.xl_validation_list_member_idThis procedure selects members to use as an Excel validation list source.
xls25.xl_validation_list_member_id_by_dimension_idThis procedure selects members to use as an Excel validation list source.
xls25.xl_validation_list_member_id_by_rowset_idThis procedure selects members to use as an Excel validation list source.
xls25.xl_validation_list_name_format_idThis procedure selects name formats to use as an Excel validation list source.
xls25.xl_validation_list_previous_period_id_by_dimension_idThis procedure selects previous period members to use as an Excel validation list source.
xls25.xl_validation_list_root_member_idThis procedure selects possible root members to use as an Excel validation list source.
xls25.xl_validation_list_root_member_or_rowset_idThis procedure selects possible root members to use as an Excel validation list source.
xls25.xl_validation_list_rowset_idThis procedure selects rowsets to use as an Excel validation list source.
xls25.xl_validation_list_same_period_id_by_dimension_idThis procedure selects same period members to use as an Excel validation list source.
xls25.xl_validation_list_tax_rate_id_by_dimension_idThis procedure selects tax rates to use as an Excel validation list source.
xls25.xl_validation_list_unit_id_by_dimension_idThis procedure selects units to use as an Excel validation list source.

dbo25.usp_export_all

This procedure exports all data.

It calls other export procedures in the right order to prepare the complete import SQL script.

dbo25.usp_export_currency_rates

This procedure exports currency rates.

Source table: dbo25.currency_rates

dbo25.usp_export_dimensions

This procedure exports dimensions.

ParameterDataTypeModeComment
@parttinyintIN1 - dbo25.usp_import_dimension
2 - dbo25.usp_import_dimension_calc_types
3 - dbo25.usp_import_dimension_properties

Source table: dbo25.dimensions

dbo25.usp_export_facts

This procedure exports source numbers.

Source table: dbo25.facts

dbo25.usp_export_forms

This procedure exports forms and form settings.

ParameterDataTypeModeComment
@parttinyintIN1 - dbo25.usp_import_form
2 - dbo25.usp_import_form_dimension
3 - dbo25.usp_import_form_row
4 - dbo25.usp_import_form_permissions
5 - dbo25.usp_import_form_subtotals

Source table: dbo25.forms

dbo25.usp_export_members

This procedure exports members.

ParameterDataTypeModeComment
@parttinyintIN1 - dbo25.usp_import_member
2 - dbo25.usp_import_member_properties
3 - dbo25.usp_import_member_parent
4 - dbo25.usp_import_member_relations
5 - dbo25.usp_import_member_permissions

Source tables: dbo25.members, dbo25.parents, dbo25.member_relations, dbo25.member_permissions.

dbo25.usp_export_strings

This procedure exports source strings.

Source table: dbo25.strings

dbo25.usp_export_tax_rates

This procedure exports tax rates.

Source table: dbo25.tax_rates

dbo25.usp_export_units

This procedure exports units.

Source table: dbo25.units

dbo25.usp_import_clear_all_data

This procedure clears all data before importing data.

ParameterDataTypeModeComment
@confirmnvarchar(50)INyes - to confirm deleting data

You may clear all the data to create a complete copy of the exported application.

The procedure does not delete dimensions.

dbo25.usp_import_currency_rates

This procedure imports currency rates.

ParameterDataTypeModeComment
@base_currency_codenvarchar(50)INdbo25.rates.base_currency_id
@quote_currency_codenvarchar(50)INdbo25.rates.quote_currency_id
@time_codenvarchar(50)INdbo25.rates.time_id
@category_codenvarchar(50)INdbo25.rates.category_id
@currency_ratefloatINdbo25.rates.currency_rate

Target table: dbo25.currency_rates

dbo25.usp_import_dimension

This procedure imports dimensions.

ParameterDataTypeModeComment
@idtinyintINdbo25.dimensions.id
@codenvarchar(50)INdbo25.dimensions.code
@namenvarchar(50)INdbo25.dimensions.name
@parameter_namenvarchar(50)INdbo25.dimensions.parameter_name
@sort_ordertinyintINdbo25.dimensions.sort_order
@join_ordertinyintINdbo25.dimensions.join_order
@name_format_idtinyintINdbo25.dimensions.name_format_id
@is_protectedbitINdbo25.dimensions.is_protected
@default_select_permissionbitINdbo25.dimensions.default_select_permission
@default_update_permissionbitINdbo25.dimensions.default_update_permission
@is_activebitINdbo25.dimensions.is_active
@external_idintINdbo25.dimensions.external_id
@external_codenvarchar(50)INdbo25.dimensions.external_code

Target table: dbo25.dimensions

dbo25.usp_import_dimension_calc_types

This procedure imports calculation types by dimensions.

ParameterDataTypeModeComment
@dimension_idtinyintINdbo25.dimension_calc_types.dimension_id
@calc_type_idtinyintINdbo25.dimension_calc_types.calc_type_id
@is_activebitINdbo25.dimension_calc_types.is_active

Target table: dbo25.dimension_calc_types

dbo25.usp_import_dimension_properties

This procedure imports dimension properties.

ParameterDataTypeModeComment
@dimension_idtinyintINdbo25.dimensions.id
@root_member_codenvarchar(50)INdbo25.dimensions.root_member_id
@default_member_codenvarchar(50)INdbo25.dimensions.default_member_id

Target table: dbo25.dimensions

Call this procedure after importing dimension members.

dbo25.usp_import_fact

This procedure imports source numbers.

ParameterDataTypeModeComment
@valuefloatINdbo25.facts.source_value, dbo25.facts.value
@code1nvarchar(50)INdbo25.facts.id1
@code2nvarchar(50)INdbo25.facts.id2
@code3nvarchar(50)INdbo25.facts.id3
@code4nvarchar(50)INdbo25.facts.id4
@code5nvarchar(50)INdbo25.facts.id5
@code6nvarchar(50)INdbo25.facts.id6
@code7nvarchar(50)INdbo25.facts.id7

Target table: dbo25.facts

The procedure sets the same value into the value and source_value fields.
Use the xls25.xl_actions_update_facts procedure to calculate values that depend on unit factors and currency rates.

dbo25.usp_import_form

This procedure imports forms.

ParameterDataTypeModeComment
@codenvarchar(50)INdbo25.forms.code
@namenvarchar(255)INdbo25.forms.name
@sort_orderintINdbo25.forms.sort_order
@is_activebitINdbo25.forms.is_active

Target table: dbo25.forms

dbo25.usp_import_form_dimension

This procedure imports form dimension properties.

ParameterDataTypeModeComment
@form_codenvarchar(50)INdbo25.form_dimensions.id
@dimension_idtinyintINdbo25.form_dimensions.dimension_id
@axis_type_idtinyintINdbo25.form_dimensions.axis_type_id
@parameter_indextinyintINdbo25.form_dimensions.parameter_index
@header_ordertinyintINdbo25.form_dimensions.header_order
@root_member_codenvarchar(50)INdbo25.form_dimensions.root_member_id
@parameter_start_leveltinyintINdbo25.form_dimensions.parameter_start_level
@parameter_end_leveltinyintINdbo25.form_dimensions.parameter_end_level
@form_start_leveltinyintINdbo25.form_dimensions.form_start_level
@form_end_leveltinyintINdbo25.form_dimensions.form_end_level

Target table: dbo25.form_dimensions

dbo25.usp_import_form_permissions

This procedure imports form permissions.

ParameterDataTypeModeComment
@form_codenvarchar(50)INdbo25.form_permissions.form_id
@usernamenvarchar(128)INdbo25.form_permissions.username
@select_permissiontinyintINdbo25.form_permissions.select_permission

Target table: dbo25.form_permissions

dbo25.usp_import_form_row

This procedure imports custom form rowsets.

ParameterDataTypeModeComment
@rowset_codenvarchar(50)INdbo25.form_rows.rowset_id
@sort_orderintINdbo25.form_rows.sort_order
@member_codenvarchar(50)INdbo25.form_rows.member_id
@codenvarchar(50)INdbo25.form_rows.code
@namenvarchar(400)INdbo25.form_rows.name
@decimal_placestinyintINdbo25.form_rows.decimal_places
@is_percenttinyintINdbo25.form_rows.is_percent
@row_colortinyintINdbo25.form_rows.row_color
@row_boldtinyintINdbo25.form_rows.row_bold
@row_indenttinyintINdbo25.form_rows.row_indent

Target table: dbo25.form_rows

dbo25.usp_import_form_subtotals

This procedure imports form subtotal settings.

ParameterDataTypeModeComment
@form_codenvarchar(50)INdbo25.form_subtotals.form_id
@showbitINdbo25.form_subtotals.show
@dimension_id1tinyintINdbo25.form_subtotals.dimension_id1
@dimension_id2tinyintINdbo25.form_subtotals.dimension_id2
@dimension_id3tinyintINdbo25.form_subtotals.dimension_id3
@member_code1nvarchar(50)INdbo25.form_subtotals.member_id1
@member_code2nvarchar(50)INdbo25.form_subtotals.member_id2
@member_code3nvarchar(50)INdbo25.form_subtotals.member_id3

Target table: dbo25.form_subtotals

dbo25.usp_import_member

This procedure imports dimension members.

ParameterDataTypeModeComment
@dimension_idtinyintINdbo25.members.dimension_id
@sort_orderintINdbo25.members.sort_order
@codenvarchar(50)INdbo25.members.code
@namenvarchar(400)INdbo25.members.name
@calc_type_idtinyintINdbo25.members.calc_type_id
@decimal_placestinyintINdbo25.members.decimal_places
@is_percentbitINdbo25.members.is_percent
@row_colortinyintINdbo25.members.row_color
@row_boldtinyintINdbo25.members.row_bold
@row_indenttinyintINdbo25.members.row_indent
@is_activebitINdbo25.members.is_active
@external_idintINdbo25.members.external_id
@external_codenvarchar(50)INdbo25.members.external_code
@excel_formulanvarchar(400)INdbo25.members.excel_formula

Target table: dbo25.members

dbo25.usp_import_member_parent

This procedure imports member parents.

ParameterDataTypeModeComment
@member_codenvarchar(50)INdbo25.parents.member_id
@parent_codenvarchar(50)INdbo25.parents.parent_id
@factorfloatINdbo25.parents.factor

Target table: dbo25.parents

dbo25.usp_import_member_permissions

This procedure imports member permissions.

ParameterDataTypeModeComment
@member_codenvarchar(50)INdbo25.member_permissions.member_id
@usernamenvarchar(128)INdbo25.member_permissions.username
@select_permissiontinyintINdbo25.member_permissions.select_permission
@update_permissiontinyintINdbo25.member_permissions.update_permission

Target table: dbo25.member_permissions

dbo25.usp_import_member_properties

This procedure imports member properties.

ParameterDataTypeModeComment
@member_codenvarchar(50)INdbo25.members.id
@previous_period_codenvarchar(50)INdbo25.members.previous_period_id
@same_period_codenvarchar(50)INdbo25.members.same_period_id
@tax_rate_codenvarchar(50)INdbo25.members.tax_rate_id
@unit_codenvarchar(50)INdbo25.members.unit_id

Target table: dbo25.members

dbo25.usp_import_member_relations

This procedure imports member relations.

ParameterDataTypeModeComment
@member_codenvarchar(50)INdbo25.member_relations.member_id
@partner_codenvarchar(50)INdbo25.member_relations.partner_id
@select_permissiontinyintINdbo25.member_relations.select_permission
@update_permissiontinyintINdbo25.member_relations.update_permission

Target table: dbo25.member_relations

dbo25.usp_import_string

This procedure imports source strings.

ParameterDataTypeModeComment
@valuenvarchar(4000)INdbo25.strings.value
@code1nvarchar(50)INdbo25.strings.id1
@code2nvarchar(50)INdbo25.strings.id2
@code3nvarchar(50)INdbo25.strings.id3
@code4nvarchar(50)INdbo25.strings.id4
@code5nvarchar(50)INdbo25.strings.id5
@code6nvarchar(50)INdbo25.strings.id6
@code7nvarchar(50)INdbo25.strings.id7

Target table: dbo25.strings

dbo25.usp_import_tax_rates

This procedure imports tax rates.

ParameterDataTypeModeComment
@idtinyintINdbo25.tax_rates.id
@codenvarchar(50)INdbo25.tax_rates.code
@namenvarchar(50)INdbo25.tax_rates.name
@sort_ordertinyintINdbo25.tax_rates.sort_order
@is_activebitINdbo25.tax_rates.is_active

Target table: dbo25.tax_rates

dbo25.usp_import_unit

This procedure imports units.

ParameterDataTypeModeComment
@idtinyintINdbo25.units.id
@codenvarchar(50)INdbo25.units.code
@namenvarchar(255)INdbo25.units.name
@sort_orderintINdbo25.units.sort_order
@factorfloatINdbo25.units.factor
@is_currencybitINdbo25.units.is_currency
@is_activebitINdbo25.units.is_active

Target table: dbo25.units

dbo25.xl_actions_create_standard_members

This procedure creates standard dimension members.

Use it to initialize the empty cube.

The procedure uses the dbo25.get_standard_members function to select standard members.

dbo25.xl_actions_set_role_permissions

This procedure grants permissions to the planning application roles.

Execute this procedure if you recreated any table of the dbo25 schema.

xls25.usp_calc_types

This procedure is an Excel form for editing calculation types.

Underlying table: dbo25.calc_types

xls25.usp_closed_periods

This procedure is an Excel form for editing closed periods.

Underlying table: dbo25.member_relations

Editing data requires the SaveToDB Enterprise edition.

Example:

Planning Application - Closed Periods

xls25.usp_closed_periods_change

This procedure updates a database on cell changes of xls25.usp_closed_periods.

ParameterDataTypeModeComment
@column_namenvarchar(128)INdbo25.member_relations.category_id
@cell_number_valueintINdbo25.member_relations.update_permission
@idintINdbo25.member_relations.member_id

xls25.usp_currency_rates

This procedure is an Excel form for editing currency rates.

ParameterDataTypeModeComment
@base_currency_idtinyintINdbo25.units.id
@quote_currency_idtinyintINdbo25.units.id

Use the xls25.xl_actions_update_facts procedure to recalculate facts after currency rate changes.

Underlying table: dbo25.rates

Editing data requires the SaveToDB Enterprise edition.

xls25.usp_currency_rates_change

This procedure updates a database on cell changes of xls25.usp_rates.

ParameterDataTypeModeComment
@column_namenvarchar(128)INdbo25.rates.category_id
@cell_number_valuefloatINdbo25.rates.rate
@idintINdbo25.rates.time_id
@base_currency_idtinyintINdbo25.rates.base_currency_id
@quote_currency_idtinyintINdbo25.rates.quote_currency_id

xls25.usp_data_management

This procedure is an Excel form for data management.

Use the Excel context menu to delete and copy data.

Underlying table: dbo25.facts

Actions: xls25.xl_actions_copy_data, xls25.xl_actions_delete_data

Example:

Planning Application - Data Management

xls25.usp_dimensions

This procedure is an Excel form for editing dimensions.

Underlying table: dbo25.dimensions

Example:

Planning Application -Dimensions

xls25.usp_dimensions_change

This procedure checks user input on cell changes of xls25.usp_dimensions.

ParameterDataTypeModeComment
@column_namenvarchar(255)INExcel cell column name
@cell_valuenvarchar(255)INExcel cell object value
@cell_number_valuefloatINExcel cell number value
@cell_addressnvarchar(255)INExcel cell address
@idintINdbo25.dimensions.id
@root_member_idintINdbo25.dimensions.root_member_id
@default_member_idintINdbo25.dimensions.default_member_id

The procedure just checks the changes. It does not save any data.

xls25.usp_dimensions_delete

This procedure deletes rows of xls25.usp_dimensions.

ParameterDataTypeModeComment
@idtinyintINdbo25.dimensions.id

The procedure blocks deleting dimensions and raises an exception.
Use an SQL command to delete a dimension.

xls25.usp_dimensions_insert

This procedure inserts rows of xls25.usp_dimensions.

ParameterDataTypeModeComment
@idtinyintINdbo25.dimensions.id
@codenvarchar(50)INdbo25.dimensions.code
@namenvarchar(50)INdbo25.dimensions.name
@parameter_namenvarchar(50)INdbo25.dimensions.parameter_name
@sort_ordertinyintINdbo25.dimensions.sort_order
@name_format_idtinyintINdbo25.dimensions.name_format_id
@root_member_idintINdbo25.dimensions.root_member_id
@default_member_idintINdbo25.dimensions.default_member_id
@is_protectedtinyintINdbo25.dimensions.is_protected
@default_select_permissiontinyintINdbo25.dimensions.default_select_permission
@default_update_permissiontinyintINdbo25.dimensions.default_update_permission
@is_activetinyintINdbo25.dimensions.is_active

The procedure blocks inserting dimensions and raises an exception.

Use an SQL command to insert a dimension.
However, note that you have to modify the application code also.

xls25.usp_dimensions_update

This procedure updates rows of xls25.usp_dimensions.

ParameterDataTypeModeComment
@idtinyintINdbo25.dimensions.id
@codenvarchar(50)INdbo25.dimensions.code
@namenvarchar(50)INdbo25.dimensions.name
@parameter_namenvarchar(50)INdbo25.dimensions.parameter_name
@sort_ordertinyintINdbo25.dimensions.sort_order
@name_format_idtinyintINdbo25.dimensions.name_format_id
@root_member_idintINdbo25.dimensions.root_member_id
@default_member_idintINdbo25.dimensions.default_member_id
@is_protectedtinyintINdbo25.dimensions.is_protected
@default_select_permissiontinyintINdbo25.dimensions.default_select_permission
@default_update_permissiontinyintINdbo25.dimensions.default_update_permission
@is_activetinyintINdbo25.dimensions.is_active

xls25.usp_form_dimensions

This procedure is an Excel form for editing form dimension properties.

The form selects dbo25.forms cross dbo25.dimensions.

Underlying tables: dbo25.forms, dbo25.dimensions, dbo25.form_dimensions

Example:

Planning Application - Form Dimensions

xls25.usp_form_dimensions_delete

This procedure deletes rows of xls25.usp_form_dimensions.

ParameterDataTypeModeComment
@form_idintINdbo25.form_dimensions.form_id
@dimension_idtinyintINdbo25.form_dimensions.dimension_id

xls25.usp_form_dimensions_insert

This procedure inserts rows of xls25.usp_form_dimensions.

ParameterDataTypeModeComment
@form_idintINdbo25.form_dimensions.form_id
@dimension_idtinyintINdbo25.form_dimensions.dimension_id
@axis_type_idtinyintINdbo25.form_dimensions.axis_type_id
@parameter_indextinyintINdbo25.form_dimensions.parameter_index
@header_ordertinyintINdbo25.form_dimensions.header_order
@root_member_idintINdbo25.form_dimensions.root_member_id
@parameter_start_leveltinyintINdbo25.form_dimensions.parameter_start_level
@parameter_end_leveltinyintINdbo25.form_dimensions.parameter_end_level
@form_start_leveltinyintINdbo25.form_dimensions.form_start_level
@form_end_leveltinyintINdbo25.form_dimensions.form_end_level

xls25.usp_form_dimensions_update

This procedure updates rows of xls25.usp_form_dimensions.

ParameterDataTypeModeComment
@form_idintINdbo25.form_dimensions.form_id
@dimension_idtinyintINdbo25.form_dimensions.dimension_id
@axis_type_idtinyintINdbo25.form_dimensions.axis_type_id
@parameter_indextinyintINdbo25.form_dimensions.parameter_index
@header_ordertinyintINdbo25.form_dimensions.header_order
@root_member_idintINdbo25.form_dimensions.root_member_id
@parameter_start_leveltinyintINdbo25.form_dimensions.parameter_start_level
@parameter_end_leveltinyintINdbo25.form_dimensions.parameter_end_level
@form_start_leveltinyintINdbo25.form_dimensions.form_start_level
@form_end_leveltinyintINdbo25.form_dimensions.form_end_level

xls25.usp_form_permissions

This procedure is an Excel form for editing form permissions.

Underlying table: dbo25.form_permisssions

Editing data requires the SaveToDB Enterprise edition.

Example:

Planning Application - Form Permissions

xls25.usp_form_permissions_change

This procedure updates a database on cell changes of xls25.usp_form_permissions.

ParameterDataTypeModeComment
@column_namenvarchar(128)INdbo25.member_permissions.username
@cell_number_valueintIN1 - select_permission = 1
0 - select_permission = 0
@idintINdbo25.member_permissions.member_id

xls25.usp_form_rows

This procedure is an Excel form for editing form rowsets.

ParameterDataTypeModeComment
@rowset_idintINdbo25.form_rows.rowset_id

Note that you may create rowsets to use in rows and columns.

Underlying table: dbo25.form_rows

The following example shows configuring columns to compare budget vs. actuals:

Planning Application - Form Rows

xls25.usp_form_rows_delete

This procedure deletes rows of xls25.usp_form_rows.

ParameterDataTypeModeComment
@idintINdbo25.form_rows.id

xls25.usp_form_rows_insert

This procedure inserts rows of xls25.usp_form_rows.

ParameterDataTypeModeComment
@rowset_idintINdbo25.form_rows.rowset_id
@rownumintINdbo25.form_rows.sort_order
@rownum is a predefined SaveToDB add-in parameter
@member_idintINdbo25.form_rows.member_id
@codenvarchar(50)INdbo25.form_rows.code
@namenvarchar(400)INdbo25.form_rows.name
@decimal_placestinyintINdbo25.form_rows.decimal_places
@is_percenttinyintINdbo25.form_rows.is_percent
@row_colortinyintINdbo25.form_rows.row_color
@row_boldtinyintINdbo25.form_rows.row_bold
@row_indenttinyintINdbo25.form_rows.row_indent

xls25.usp_form_rows_update

This procedure updates rows of xls25.usp_form_rows.

ParameterDataTypeModeComment
@idintINdbo25.form_rows.id
@rowset_idintINdbo25.form_rows.rowset_id
@rownumintINdbo25.form_rows.sort_order
@rownum is a predefined SaveToDB add-in parameter
@member_idintINdbo25.form_rows.member_id
@codenvarchar(50)INdbo25.form_rows.code
@namenvarchar(400)INdbo25.form_rows.name
@decimal_placestinyintINdbo25.form_rows.decimal_places
@is_percenttinyintINdbo25.form_rows.is_percent
@row_colortinyintINdbo25.form_rows.row_color
@row_boldtinyintINdbo25.form_rows.row_bold
@row_indenttinyintINdbo25.form_rows.row_indent

xls25.usp_forms

This procedure is an Excel form for editing a list of forms.

Underlying table: dbo25.forms

Example:

Planning Application - Forms

xls25.usp_forms_delete

This procedure deletes rows of xls25.usp_forms.

ParameterDataTypeModeComment
@idintINdbo25.forms.id

xls25.usp_forms_insert

This procedure inserts rows of xls25.usp_forms.

ParameterDataTypeModeComment
@codenvarchar(50)INdbo25.forms.code
@namenvarchar(400)INdbo25.forms.name
@sort_orderintINdbo25.forms.sort_order
@is_activetinyintINdbo25.forms.is_active

xls25.usp_forms_update

This procedure updates rows of xls25.usp_forms.

ParameterDataTypeModeComment
@idintINdbo25.forms.id
@codenvarchar(50)INdbo25.forms.code
@namenvarchar(400)INdbo25.forms.name
@sort_orderintINdbo25.forms.sort_order
@is_activetinyintINdbo25.forms.is_active

xls25.usp_member_permissions

This procedure is an Excel form for editing member permissions.

ParameterDataTypeModeComment
@dimension_idtinyintINDimension filter
@root_idintINRoot member filter
@usernamenvarchar(128)INUsername filter

Underlying table: dbo25.member_permissions

Editing data requires the SaveToDB Enterprise edition.

Example:

Planning Application - Member Permissions

Type "r", "w", or "d" to change permissions.

The application applies for new permissions immediately after changes.

xls25.usp_member_permissions_change

This procedure updates a database on cell changes of xls25.usp_member_permissions.

ParameterDataTypeModeComment
@column_namenvarchar(128)INdbo25.member_permissions.username
@cell_valuenvarchar(255)IND - deny (select_permission = 0, update_permission = 0)
R - read (select_permission = 1, update_permission = 0)
W - write (select_permission = 1, update_permission = 1)
@idintINdbo25.member_permissions.member_id

xls25.usp_member_relations

This procedure is an Excel form for editing member relations.

ParameterDataTypeModeComment
@dimension_idintINDimension filter
@root_idintINRoot member filter
@partner_idintINPartner dimension filter
@fieldnvarchar(50)INupdate_permission, fixed value

Underlying table: dbo25.member_relations

Editing data requires the SaveToDB Enterprise edition.

The example below shows the relations between accounts and regions:

Planning Application - Member Relations

xls25.usp_member_relations_change

This procedure updates a database on cell changes of xls25.usp_member_relations.

ParameterDataTypeModeComment
@column_namenvarchar(128)INdbo25.member_relations.partner_id
@cell_number_valueintINdbo25.member_relations.update_permission

1 - update_permission = 1
0 - update_permission = 0
@idintINdbo25.member_relations.member_id
@fieldnvarchar(128)INupdate_permission, fixed value

xls25.usp_members

This procedure is an Excel form for editing dimension members.

ParameterDataTypeModeComment
@dimension_idtinyintINDimension filter
@root_idintINRoot member filter

Use the Actions menu to add typical members.

After saving data click Actions, Update Hierarchies, then Reload, Reload Data and Configuration.
These actions update member hierarchies and reload new members into Microsoft Excel.

Underlying tables: dbo25.members, dbo25.parents

Actions: xls25.xl_actions_update_hierarchies

Example:

Planning Application - Dimension Members

xls25.usp_members_delete

This procedure deletes rows of xls25.usp_members.

ParameterDataTypeModeComment
@idintINdbo25.members.id

xls25.usp_members_insert

This procedure inserts rows of xls25.usp_members.

ParameterDataTypeModeComment
@dimension_idtinyintINdbo25.members.dimension_id
@codenvarchar(50)INdbo25.members.code
@namenvarchar(400)INdbo25.members.name
@commentnvarchar(1000)INdbo25.members.comment
@sort_orderintINdbo25.members.sort_order
@tax_rate_idtinyintINdbo25.members.tax_rate_id
@unit_idtinyintINdbo25.members.unit_id
@calc_type_idtinyintINdbo25.members.calc_type_id
@previous_period_idintINdbo25.members.previous_period_id
@same_period_idintINdbo25.members.same_period_id
@decimal_placestinyintINdbo25.members.decimal_places
@is_percenttinyintINdbo25.members.is_percent
@row_colortinyintINdbo25.members.row_color
@row_boldtinyintINdbo25.members.row_bold
@row_indenttinyintINdbo25.members.row_indent
@is_activetinyintINdbo25.members.is_active
@excel_formulanvarchar(400)INdbo25.members.excel_formula
@parent1intINdbo25.parents.parent_id
@parent2intINdbo25.parents.parent_id
@parent3intINdbo25.parents.parent_id
@parent4intINdbo25.parents.parent_id
@parent5intINdbo25.parents.parent_id
@parent6intINdbo25.parents.parent_id
@parent7intINdbo25.parents.parent_id
@factor1floatINdbo25.parents.factor
@factor2floatINdbo25.parents.factor
@factor3floatINdbo25.parents.factor
@factor4floatINdbo25.parents.factor
@factor5floatINdbo25.parents.factor
@factor6floatINdbo25.parents.factor
@factor7floatINdbo25.parents.factor

xls25.usp_members_update

This procedure updates rows of xls25.usp_members.

ParameterDataTypeModeComment
@idintINdbo25.members.id, dbo25.parents.member_id
@dimension_idtinyintINdbo25.members.dimension_id
@codenvarchar(50)INdbo25.members.code
@namenvarchar(400)INdbo25.members.name
@commentnvarchar(1000)INdbo25.members.comment
@sort_orderintINdbo25.members.sort_order
@tax_rate_idtinyintINdbo25.members.tax_rate_id
@unit_idtinyintINdbo25.members.unit_id
@calc_type_idtinyintINdbo25.members.calc_type_id
@previous_period_idintINdbo25.members.previous_period_id
@same_period_idintINdbo25.members.same_period_id
@decimal_placestinyintINdbo25.members.decimal_places
@is_percenttinyintINdbo25.members.is_percent
@row_colortinyintINdbo25.members.row_color
@row_boldtinyintINdbo25.members.row_bold
@row_indenttinyintINdbo25.members.row_indent
@is_activetinyintINdbo25.members.is_active
@excel_formulanvarchar(400)INdbo25.members.excel_formula
@parent1intINdbo25.parents.parent_id
@parent2intINdbo25.parents.parent_id
@parent3intINdbo25.parents.parent_id
@parent4intINdbo25.parents.parent_id
@parent5intINdbo25.parents.parent_id
@parent6intINdbo25.parents.parent_id
@parent7intINdbo25.parents.parent_id
@factor1floatINdbo25.parents.factor
@factor2floatINdbo25.parents.factor
@factor3floatINdbo25.parents.factor
@factor4floatINdbo25.parents.factor
@factor5floatINdbo25.parents.factor
@factor6floatINdbo25.parents.factor
@factor7floatINdbo25.parents.factor

xls25.usp_rowsets

This procedure is an Excel form for editing rowset members.

ParameterDataTypeModeComment
@dimension_idtinyintINDimension filter

The form shows members of the dbo25.members table with calc_type_id = 9 (rowset).

You may add new rowsets here.

Use the xls25.usp_form_rows procedure to edit rowset rows.

Underlying table: dbo25.members

The example below shows the rowsets of the time dimension that contains years only and quarters only:

Planning Application - Rowsets

We recommend adding the asterisk at the end of codes and names to distinguish rowsets from regular hierarchy members.

xls25.usp_rowsets_delete

This procedure deletes rows of xls25.usp_rowsets.

ParameterDataTypeModeComment
@idintINdbo25.members.id

xls25.usp_rowsets_insert

This procedure inserts rows of xls25.usp_rowsets.

ParameterDataTypeModeComment
@dimension_idtinyintINdbo25.members.dimension_id
@codenvarchar(50)INdbo25.members.code
@namenvarchar(400)INdbo25.members.name
@sort_orderintINdbo25.members.sort_order

xls25.usp_rowsets_update

This procedure updates rows of xls25.usp_rowsets.

ParameterDataTypeModeComment
@idintINdbo25.members.id
@dimension_idtinyintINdbo25.members.dimension_id
@codenvarchar(50)INdbo25.members.code
@namenvarchar(400)INdbo25.members.name
@sort_orderintINdbo25.members.sort_order

xls25.usp_run_form

This procedure executes forms configured using dbo25.forms and dbo25.form_dimensions.

ParameterDataTypeModeComment
@form_idintINdbo25.forms.id
@form_p1intINdbo25.members.id of the 1st ribbon parameter
@form_p2intINdbo25.members.id of the 2nd ribbon parameter
@form_p3intINdbo25.members.id of the 3rd ribbon parameter
@form_p4intINdbo25.members.id of the 4th ribbon parameter
@form_p5intINdbo25.members.id of the 5th ribbon parameter
@form_p6intINdbo25.members.id of the 6th ribbon parameter
@form_p7intINdbo25.members.id of the 7th ribbon parameter

This procedure is a form engine.

It returns the form layout configured using the dbo25.forms, dbo25.form_dimenstions, dbo25.form_rows, and dbo25.form_subtotals tables.

The form may have up to thee dimensions in rows and up to three in columns.
Other dimensions must have member id values at the ribbon.

Ribbon parameters are dynamic and configured using dbo25.form_dimensions.

The Excel form has the cell change handler, xls25.usp_run_form_change, that updates underlying data.

This form requires the SaveToDB Enterprise edition.

You may use the offline form, xls25.usp_run_offline_form, with the SaveToDB Standard edition.

This is the most complicated procedure. If you need new features, you may contact us.

Example:

Planning Application - Budget Form

xls25.usp_run_form_change

This procedure updates a database on cell changes of xls25.usp_run_form.

ParameterDataTypeModeComment
@column_namenvarchar(255)INExcel cell column name.
Data column names contain dimension member codes separated by spaces.
@table_namenvarchar(255)INActive form database object name
@cell_valuenvarchar(255)INExcel cell object value
@cell_number_valuefloatINExcel cell number value
@cell_addressnvarchar(255)INExcel cell address
@changed_cell_countintINTotal number of changed cells
@changed_cell_indexintINCell index in the changed cells, starting 1.
The last cell has the index equal to @changed_cell_count.
@idintINdbo25.form_rows.id
@member_idintINdbo25.members.id of the 1st row dimension
@member_id2intINdbo25.members.id of the 2nd row dimension
@member_id3intINdbo25.members.id of the 3rd row dimension
@form_idintINdbo25.forms.id
@form_p1intINdbo25.members.id of the 1st ribbon parameter
@form_p2intINdbo25.members.id of the 2nd ribbon parameter
@form_p3intINdbo25.members.id of the 3rd ribbon parameter
@form_p4intINdbo25.members.id of the 4th ribbon parameter
@form_p5intINdbo25.members.id of the 5th ribbon parameter
@form_p6intINdbo25.members.id of the 6th ribbon parameter
@form_p7intINdbo25.members.id of the 7th ribbon parameter
@quiet_modetinyintIN0 - raise exceptions on errors
1 - return on errors without exceptions

Note that this feature is available in the SaveToDB Enterprise edition only.

xls25.usp_run_json_form

This procedure returns calculated cube data requested with JSON parameters.

ParameterDataTypeModeComment
@requestnvarchar(max)INForm parameters in the JSON format.

You may use this procedure to create web forms.

See usage examples in the procedure code.

xls25.usp_run_offline_form

This procedure executes forms configured using dbo25.forms and dbo25.form_dimensions.

ParameterDataTypeModeComment
@form_idintINdbo25.forms.id
@form_p1intINdbo25.members.id of the 1st ribbon parameter
@form_p2intINdbo25.members.id of the 2nd ribbon parameter
@form_p3intINdbo25.members.id of the 3rd ribbon parameter
@form_p4intINdbo25.members.id of the 4th ribbon parameter
@form_p5intINdbo25.members.id of the 5th ribbon parameter
@form_p6intINdbo25.members.id of the 6th ribbon parameter
@form_p7intINdbo25.members.id of the 7th ribbon parameter

This procedure is an offline form engine.

It calls xls25.usp_run_form internally and has the same output features.

However, it has a different saving configuration.

A user must click the Save button to save changes.
So, the user may edit data offline and save the changes when required.

Example:

Planning Application - Offline Budget Form

xls25.usp_run_offline_form_delete

This procedure deletes rows of xls25.usp_run_offline_form.

ParameterDataTypeModeComment
@idintINdbo25.form_rows.id

The procedure does nothing as users cannot delete the form rows.

xls25.usp_run_offline_form_insert

This procedure inserts rows of xls25.usp_run_offline_form.

ParameterDataTypeModeComment
@idintINdbo25.form_rows.id

The procedure does nothing as users cannot add form rows.

xls25.usp_run_offline_form_update

This procedure updates rows of xls25.usp_run_offline_form.

ParameterDataTypeModeComment
@table_namenvarchar(255)INActive form database object name
@form_idintINdbo25.forms.id
@form_p1intINdbo25.members.id of the 1st ribbon parameter
@form_p2intINdbo25.members.id of the 2nd ribbon parameter
@form_p3intINdbo25.members.id of the 3rd ribbon parameter
@form_p4intINdbo25.members.id of the 4th ribbon parameter
@form_p5intINdbo25.members.id of the 5th ribbon parameter
@form_p6intINdbo25.members.id of the 6th ribbon parameter
@form_p7intINdbo25.members.id of the 7th ribbon parameter
@member_idintINdbo25.members.id of the 1st row dimension
@member_id2intINdbo25.members.id of the 2nd row dimension
@member_id3intINdbo25.members.id of the 3rd row dimension
@json_columnsnvarchar(max)INAn array of column names in the JSON format
@json_valuesnvarchar(max)INAn array of row values in the JSON format

xls25.usp_tax_rates

This procedure is an Excel form for editing tax rates.

Underlying table: dbo25.tax_rates

xls25.usp_units

This procedure is an Excel form for editing units.

Use the Excel context menu to change the system currency (the unit with id 0).

Underlying table: dbo25.units

Example:

Planning Application - Units

xls25.xl_actions_add_cash_flow_example

This procedure adds members and settings of the cash flow example.

ParameterDataTypeModeComment
@set_1_to_confirmtinyintIN1 - to create example members

Target tables: dbo25.members, dbo25.parents

xls25.xl_actions_add_cost_center_example

This procedure adds members and settings of the cost center example.

ParameterDataTypeModeComment
@set_1_to_confirmtinyintIN1 - to create example members

Target tables: dbo25.members, dbo25.parents

xls25.xl_actions_add_quarters

This procedure adds quarter members.

ParameterDataTypeModeComment
@yearintINYear

Use this procedure if you need only years and quarters but not months.

Target tables: dbo25.members, dbo25.parents

xls25.xl_actions_add_year

This procedure adds year members.

ParameterDataTypeModeComment
@yearintINYear

Use this procedure if you need years, quarters, and months.

Target tables: dbo25.members, dbo25.parents

xls25.xl_actions_copy_data

This procedure copies data from a category to category.

ParameterDataTypeModeComment
@codenvarchar(128)INTime dbo25.members.id
@source_category_codenvarchar(128)INSource category dbo25.members.id
@target_category_codenvarchar(128)INTarget category dbo25.members.id
@set_1_to_copytinyintIN1 - to confirm copying data

For example, you may use this procedure to copy BUDGET data to FORECAST, or just create a copy of the budget data.

The procedure does not copy string data.

Target table: dbo25.facts

xls25.xl_actions_delete_data

This procedure deletes source cube data from a category.

ParameterDataTypeModeComment
@codenvarchar(128)INTime dbo25.members.id
@category_codenvarchar(128)INCategory dbo25.members.id
@set_1_to_deletetinyintIN1 - to confirm deleting data

Target tables: dbo25.facts, dbo25.strings

xls25.xl_actions_delete_year

This procedure deletes year members.

ParameterDataTypeModeComment
@yearintINYear
@set_1_to_deletetinyintIN1 - to confirm deleting data

Delete the cube data of the required year before deleting the members.

Target tables: dbo25.members, dbo25.parents

xls25.xl_actions_run_form_cell_data

This procedure selects underlying cube data for a reporting cell.

ParameterDataTypeModeComment
@column_namenvarchar(255)INExcel cell column name.
Data column names contain dimension member codes separated by spaces.
@table_namenvarchar(255)INActive form database object name
@member_idintINdbo25.members.id of the 1st row dimension
@member_id2intINdbo25.members.id of the 2nd row dimension
@member_id3intINdbo25.members.id of the 3rd row dimension
@form_idintINdbo25.forms.id
@form_p1intINdbo25.members.id of the 1st ribbon parameter
@form_p2intINdbo25.members.id of the 2nd ribbon parameter
@form_p3intINdbo25.members.id of the 3rd ribbon parameter
@form_p4intINdbo25.members.id of the 4th ribbon parameter
@form_p5intINdbo25.members.id of the 5th ribbon parameter
@form_p6intINdbo25.members.id of the 6th ribbon parameter
@form_p7intINdbo25.members.id of the 7th ribbon parameter

This procedure is used in the Excel context menu to drill-down cell values.

Underlying table: dbo25.facts

Output example:

Planning Application - Cell Drill-Down

xls25.xl_actions_set_system_currency

This procedure changes the system currency of the application.

ParameterDataTypeModeComment
@idintINdbo25.units.id

Target table: dbo25.units

xls25.xl_actions_update_facts

This procedure updates cube values after currency rate changes.

Target table: dbo25.facts

xls25.xl_actions_update_hierarchies

This procedure updates required tables after member changes.

Target tables: dbo25.hierarchies, dbo25.factors

xls25.xl_actions_update_member_permissions

This procedure updates member permissions after member changes.

Target table: dbo25.member_permissions

xls25.xl_parameter_values_0_or_1

This procedure selects 0 or 1 for Excel ribbon parameters.

Use this procedure in the xls.handlers table for parameters that accept 0 or 1 only.

You do not need to use for parameters with the bit datatype.
The SaveToDB add-in suggests 1 (yes) and 0 (no) values by default.

xls25.xl_parameter_values_calc_type_id

This procedure selects calculation types for Excel ribbon parameters.

Underlying table: dbo25.calc_types

xls25.xl_parameter_values_calc_type_id_or_null

This procedure selects calculation types for Excel ribbon parameters.

Underlying table: dbo25.calc_types

xls25.xl_parameter_values_currency_id

This procedure selects currencies for Excel ribbon parameters.

Underlying table: dbo25.units

xls25.xl_parameter_values_dimension_id

This procedure selects dimensions for Excel ribbon parameters.

Underlying table: dbo25.dimensions

xls25.xl_parameter_values_dimension_id_or_null

This procedure selects dimensions for Excel ribbon parameters.

Underlying table: dbo25.dimensions

xls25.xl_parameter_values_form_id

This procedure selects forms for Excel ribbon parameters.

Underlying table: dbo25.forms

xls25.xl_parameter_values_form_id_or_null

This procedure selects forms for Excel ribbon parameters.

Underlying table: dbo25.forms

xls25.xl_parameter_values_member_id_by_dimension_id

This procedure selects members for Excel ribbon parameters.

ParameterDataTypeModeComment
@dimension_idtinyintINdbo25.members.dimension_id

Underlying table: dbo25.members

xls25.xl_parameter_values_member_id_dim1

This procedure selects 1st dimension members for Excel ribbon parameters.

Underlying table: dbo25.members

xls25.xl_parameter_values_member_id_dim2

This procedure selects 2nd dimension members for Excel ribbon parameters.

Underlying table: dbo25.members

xls25.xl_parameter_values_member_id_dim3

This procedure selects 3rd dimension members for Excel ribbon parameters.

Underlying table: dbo25.members

xls25.xl_parameter_values_member_id_dim4

This procedure selects 4th dimension members for Excel ribbon parameters.

Underlying table: dbo25.members

xls25.xl_parameter_values_member_id_dim5

This procedure selects 5th dimension members for Excel ribbon parameters.

Underlying table: dbo25.members

xls25.xl_parameter_values_member_id_dim6

This procedure selects 6th dimension members for Excel ribbon parameters.

Underlying table: dbo25.members

xls25.xl_parameter_values_member_id_dim7

This procedure selects 7th dimension members for Excel ribbon parameters.

Underlying table: dbo25.members

xls25.xl_parameter_values_relation_dimension_id

This procedure selects dimensions for Excel ribbon parameters.

Underlying table: dbo25.dimensions

xls25.xl_parameter_values_relation_partner_id

This procedure selects members for Excel ribbon parameters.

Underlying table: dbo25.dimensions

xls25.xl_parameter_values_root_member_id

This procedure selects possible root members for Excel ribbon parameters.

ParameterDataTypeModeComment
@dimension_idtinyintINdbo25.members.dimension_id

Underlying table: dbo25.members

xls25.xl_parameter_values_rowset_id

This procedure selects rowsets for Excel ribbon parameters.

Underlying table: dbo25.members

xls25.xl_parameter_values_rowset_id_or_null

This procedure selects rowsets for Excel ribbon parameters.

Underlying table: dbo25.members

xls25.xl_parameter_values_run_form_p

This procedure selects values for the ribbon parameters of the configured forms.

ParameterDataTypeModeComment
@form_idintINdbo25.form_dimensions.form_id
@parameter_indexintINdbo25.form_dimensions.parameter_index

Underlying table: dbo25.members

xls25.xl_parameter_values_run_form_p1

This procedure selects values for the 1st ribbon parameter of the configured forms.

ParameterDataTypeModeComment
@form_idintINdbo25.form_dimensions.form_id

Underlying table: dbo25.members

xls25.xl_parameter_values_run_form_p2

This procedure selects values for the 2nd ribbon parameter of the configured forms.

ParameterDataTypeModeComment
@form_idintINdbo25.form_dimensions.form_id

Underlying table: dbo25.members

xls25.xl_parameter_values_run_form_p3

This procedure selects values for the 3rd ribbon parameter of the configured forms.

ParameterDataTypeModeComment
@form_idintINdbo25.form_dimensions.form_id

Underlying table: dbo25.members

xls25.xl_parameter_values_run_form_p4

This procedure selects values for the 4th ribbon parameter of the configured forms.

ParameterDataTypeModeComment
@form_idintINdbo25.form_dimensions.form_id

Underlying table: dbo25.members

xls25.xl_parameter_values_run_form_p5

This procedure selects values for the 5th ribbon parameter of the configured forms.

ParameterDataTypeModeComment
@form_idintINdbo25.form_dimensions.form_id

Underlying table: dbo25.members

xls25.xl_parameter_values_run_form_p6

This procedure selects values for the 6th ribbon parameter of the configured forms.

ParameterDataTypeModeComment
@form_idintINdbo25.form_dimensions.form_id

Underlying table: dbo25.members

xls25.xl_parameter_values_run_form_p7

This procedure selects values for the 7th ribbon parameter of the configured forms.

ParameterDataTypeModeComment
@form_idintINdbo25.form_dimensions.form_id

Underlying table: dbo25.members

xls25.xl_parameter_values_unit_id

This procedure selects units for Excel ribbon parameters.

Underlying table: dbo25.units

xls25.xl_parameter_values_username

This procedure selects usernames for Excel ribbon parameters.

The procedure selects usernames of members of the planning_app_users role only.

To manage users, grant the VIEW DEFINITION permission. Other permissions are not required.

Underlying tables: sys.database_principals, sys.database_role_members

xls25.xl_validation_list_axis_type_id

This procedure selects axis types to use as an Excel validation list source.

Underlying table: dbo25.axis_types

xls25.xl_validation_list_calc_type_id_by_dimension_id

This procedure selects calculation types to use as an Excel validation list source.

ParameterDataTypeModeComment
@dimension_idtinyintINdbo25.dimension_calc_types.dimension_id

Underlying table: dbo25.calc_types

xls25.xl_validation_list_default_member_id

This procedure selects possible default members to use as an Excel validation list source.

Underlying table: dbo25.members

xls25.xl_validation_list_dimension_id

This procedure selects dimensions to use as an Excel validation list source.

Underlying table: dbo25.dimensions

xls25.xl_validation_list_member_id

This procedure selects members to use as an Excel validation list source.

Underlying table: dbo25.members

xls25.xl_validation_list_member_id_by_dimension_id

This procedure selects members to use as an Excel validation list source.

ParameterDataTypeModeComment
@dimension_idtinyintINdbo25.members.dimension_id

Underlying table: dbo25.members

xls25.xl_validation_list_member_id_by_rowset_id

This procedure selects members to use as an Excel validation list source.

ParameterDataTypeModeComment
@rowset_idintINdbo25.members.id

Underlying table: dbo25.members

xls25.xl_validation_list_name_format_id

This procedure selects name formats to use as an Excel validation list source.

Underlying table: dbo25.name_formats

xls25.xl_validation_list_previous_period_id_by_dimension_id

This procedure selects previous period members to use as an Excel validation list source.

ParameterDataTypeModeComment
@dimension_idtinyintINdbo25.members.dimension_id

Underlying table: dbo25.members

xls25.xl_validation_list_root_member_id

This procedure selects possible root members to use as an Excel validation list source.

Underlying table: dbo25.members

xls25.xl_validation_list_root_member_or_rowset_id

This procedure selects possible root members to use as an Excel validation list source.

Underlying table: dbo25.members

xls25.xl_validation_list_rowset_id

This procedure selects rowsets to use as an Excel validation list source.

Underlying table: dbo25.members

xls25.xl_validation_list_same_period_id_by_dimension_id

This procedure selects same period members to use as an Excel validation list source.

ParameterDataTypeModeComment
@dimension_idtinyintINdbo25.members.dimension_id

Underlying table: dbo25.members

xls25.xl_validation_list_tax_rate_id_by_dimension_id

This procedure selects tax rates to use as an Excel validation list source.

ParameterDataTypeModeComment
@dimension_idtinyintINdbo25.members.dimension_id

Underlying table: dbo25.tax_rates

xls25.xl_validation_list_unit_id_by_dimension_id

This procedure selects units to use as an Excel validation list source.

ParameterDataTypeModeComment
@dimension_idtinyintINdbo25.members.dimension_id

Underlying table: dbo25.units

Functions

FunctionDescription
dbo25.get_json_array_valuesThis function returns a table of array values from the JSON string.
dbo25.get_json_object_valuesThis function returns a table of name-value pairs from the JSON string.
dbo25.get_quarter_membersThis function returns a table of quarter members for the specified year.
dbo25.get_standard_membersThis function returns a table of standard members to initialize the application.
dbo25.get_year_membersThis function returns a table of time members for the specified year.

dbo25.get_json_array_values

This function returns a table of array values from the JSON string.

ParameterDataTypeComment
@cnvarchar(max)JSON array
ColumnDataTypeComment
idintValue index
valuenvarchar(max)Value

You may use this function to parse the @json_columns and @json_values parameters of stored procedures.

The SaveToDB add-in passes table headers and row values into these parameters in the JSON format.

You may use the native JSON functions of SQL Server instead.

dbo25.get_json_object_values

This function returns a table of name-value pairs from the JSON string.

ParameterDataTypeComment
@cnvarchar(max)JSON object
ColumnDataTypeComment
namenvarchar(100)Property name
valuenvarchar(max)Property value

You may use the native JSON functions of SQL Server instead.

dbo25.get_quarter_members

This function returns a table of quarter members for the specified year.

ParameterDataTypeComment
@yearintYear
ColumnDataTypeComment
codenvarchar(50)dbo25.members.code
namenvarchar(400)dbo25.members.name
dimension_idtinyintdbo25.members.dimension_id
sort_orderintdbo25.members.sort_order
calc_type_idtinyintdbo25.members.calc_type_id
row_colortinyintdbo25.members.row_color
previous_periodnvarchar(50)dbo25.members.previous_period_id
same_periodnvarchar(50)dbo25.members.same_period_id
parent1nvarchar(50)dbo25.parents.parent_id
parent2nvarchar(50)dbo25.parents.parent_id
parent3nvarchar(50)dbo25.parents.parent_id

The function is used in the xls25.xl_actions_add_quarters procedure.

dbo25.get_standard_members

This function returns a table of standard members to initialize the application.

ColumnDataTypeComment
codenvarchar(50)dbo25.members.code
namenvarchar(400)dbo25.members.name
dimension_idtinyintdbo25.members.dimension_id
sort_orderintdbo25.members.sort_order
calc_type_idtinyintdbo25.members.calc_type_id
row_colortinyintdbo25.members.row_color
previous_periodnvarchar(50)dbo25.members.previous_period_id
same_periodnvarchar(50)dbo25.members.same_period_id
parent1nvarchar(50)dbo25.parents.parent_id
factor1floatdbo25.parents.factor

The function is used in the dbo25.xl_actions_create_standard_members procedure.

dbo25.get_year_members

This function returns a table of time members for the specified year.

ParameterDataTypeComment
@yearintYear
ColumnDataTypeComment
codenvarchar(50)dbo25.members.code
namenvarchar(400)dbo25.members.name
dimension_idtinyintdbo25.members.dimension_id
sort_orderintdbo25.members.sort_order
calc_type_idtinyintdbo25.members.calc_type_id
row_colortinyintdbo25.members.row_color
previous_periodnvarchar(50)dbo25.members.previous_period_id
same_periodnvarchar(50)dbo25.members.same_period_id
parent1nvarchar(50)dbo25.parents.parent_id
parent2nvarchar(50)dbo25.parents.parent_id
parent3nvarchar(50)dbo25.parents.parent_id

The function is used in the xls25.xl_actions_add_year and xls25.xl_actions_delete_year procedures.

^