Installation guide

1. Open folder with downloaded xll file and click on it once by right mouse button:

2. Click on Show more options menu and then Properties:

3. Click on Digital Signatures menu, select Spreadym certificate and then Details:

4. Click View Certificate:

5. Click Install Certificate:

6. If you are installing the add-in for yourself, select the Current User option and click Next:

7. Select Place all certificates in the following store option, click Browse…, select Trusted Root Certification Authorities and click Ok:

8. Click Finish.

1. Open Excel, go to Options and select Add-ins:

2. Click Go in the Manage bottom menu, click Browse and select xll file:

3. Click Ok. Spreadym tab will show up in the top bar:

Use the built-in tool to create a table. Specify the dimensions that apply to the table you are creating and distribute which of them will be in the rows, columns, and/or filters of the table.

Click the Open table rules button. Rules and formatting area will open on a new sheet.

Connect to the server using your username and password.
After successful authorization, a list of models associated with your user account will become available. A list of tables for the selected model will appear.

You can expand the list to see what dimensions the table consists of.
The absence of tables means that tables have not yet been created in this model or you do not have access to existing tables.

You can create your own table using the following steps.

When you first open the sheet, the Measures dimension is added automatically. Specify in the Sheet column the name of the table for which the measures will be applicable.
Example. If you create a "sales" table where the main measures are the amount of sales, price and quantity, then the measures dimension will look like this:

Define the field type for columns with elements*:
EL - Elements
PAR - Parents of elements for consolidations
ELTYPE - Level of elements for formatting.
"c" - consolidated element. "l" - leaf element.

Submit the data to apply changes.

*It is very important to specify the "EL", "PAR" and "ELTYPE" values only once in one dimension for the most basic and complete fields with elements.

The MD sheet contains all the dimensions of the selected model. Dimensions are used to form the structure of a table and give context to cell values.

To open a sheet with dimensions, click the corresponding button on the Management panel.

In the Dimension name field, enter the name of the dimension. The specified dimensions will be used to create a table.
In the Dimension field name, enter the name of the subset or attribute of the specified dimension. Subsets are used when you need to refine the elements for a particular table. Attributes are the properties of elements. For example, it can be an alias of the element, the unit of measure, or its level.

Selected model:

Tables of the model and their dimensions:

If you are using the Measures dimension, make sure that the data in the Sheet field of the Measures dimension on the MD sheet matches the name of the table you are creating.

Enter values in the table cells.

Click Submit and select the Submit current table option. If the table does not exist in the database yet, you will be prompted to create it.

To get data from the server, use the Receive button and select the Receive current table from the server option. All data saved to the server will be pulled into the table.

You need to specify the intersection of the elements for which the formula is applied. If the rule applies to all members of a dimension, this can be specified by writing "#all". You can define a rule for a particular attribute. Formulas and formatting will be applied to all elements with the selected attribute.

*Keep in mind that if more than one rule matches the same cell, then the topmost matching rule will be applied.

Click the Commit rules button. Formatting and formulas apply to the entire table.

Input tables are special forms of data entry. They can be used to enter transactions or drill down on values in the main table. The input table is linked to the main table, when saving the input table, the data for the same slices will be summarized and sent to the main table.

To create an input table, right click on the parent table and click Create input table.

You will be able to choose the dimensions for the filters, columns and rows area. The remaining dimensions of the main table will be available for input. Items for these dimensions can be entered or selected from a selection list for each input form block.

You can set the required number of blocks for the input table. To do this, go to the input table settings and set the new quantity of input blocks.
If there are too many blocks, you can display only the necessary part on the sheet. In this case, the data in the hidden blocks will remain in memory and will also be sent to the main table.
If a value in a cell in the main table does not match the value in the input table, the conflict will be shown in the conflict area. You can select the conflicts you want to resolve, after that the information in the main table will change according to the input table. If you reject a value, it will be deleted from the input table.

You can view and change the security of your system. This feature is only available to users with the administrator role.
You can see your role in the security settings.
There are 4 levels of user rights:
1. ADMIN - can regulate access rights for users. All functions of other groups are available to him: creating, deleting tables, entering and reading data.
2. MODELER can add new elements and dimensions to the DM sheet and create new tables within its model. It has read and write access to all tables in the model.
3. USER - the rights to read and write to the tables are distributed by the Model Administrator.
4. VIEWER - can view the tables available to him, but he will not be able to enter new data.

You can copy the data to any cell or array in the table. To do this, right-click on the cell where you want to copy the data and click Spreadym - Copy data. You will be able to select the data source and data destination. If you select "#all" for the dimension, then the data of all elements of the dimension will be copied. You can also select a subset, then data will be copied only for elements included in the selected subset.

The system allows you to create several versions of the model. You can copy data from one version to another, make changes, and check versions against each other.

To create a version, click the Add new version button and select the tables to copy from the previous version.
The data of the selected tables will be migrated to the new version. The remaining tables in the new version will remain empty. Changing values in table cells after copying will not affect other versions.

On the control panel the general copying of data is available. You can select tables to copy and specify a source and destination element for each dimension.

It is also possible to add rules automatically from a table. To do this, set the formula and formatting for the cells to which you want to apply it, right-click on the cell and select the option Spreadym - Edit rule.
You will be able to select a slice for which to apply the data. If the cell/array already has a formula that you want to change, select the Edit an existing rules line option.

Once confirmed, the rule will automatically appear in the rules pane.

With Spreadym you can use all formulas available in Excel. However, to make working with tables easier, the add-in provides additional features for working with data:

SDB function
SDB is used to return values from a table in a database.
It is not necessary to store the table in the current file to get values from a table.

Syntax

=SDB (table, p1, p2, […p20])

Parameters
table
The name of the table from which to retrieve the value.
p1,…pn
Dimension element names that define the intersection containing the value to be retrieved.
Arguments p1-pn are sequence-sensitive. p1 must be an element from the first dimension of the table, p2 must be an element from the second dimension, and so on.
Parameters can be set both by absolute values and by cell references containing the required values.

Example
To get data from the sales table, the SDB function would look like this:

SDB function for consolidations
You can use the SDB function to consolidate data. For example, if data is sent to the server from January to December, then the sum of data for this year can be pulled using the following SDB function:

"#sum:Total":
"#sum:" - consolidation function
"Total" - element, which consolidates it's child elements. Can be specified as an absolute value or as a cell reference.

This function consolidates the values of the child elements specified in the MD sheet. Make sure that for this dimension, all required elements have their parent element specified.

If you are at a higher level of consolidation than one, use multiple summation pointers, like so:
=SDB("sales";D2;"#sum:Total product";D4;"#sum:Total month")

SDBA function
SDBA is used to return the value of a specified element attribute.
SDBA gets the value from the database and can be used as a replacement for the traditional Excel VLOOKUP function.

Syntax
=SDBA(dimension, element, attribute)
Parameters
dimension
Name of the dimension in which the element is located.
element
Name of the element. Can be set both by absolute values
and by cell references containing the required values.
attribute
Name of the attribute. On the MD sheet the field is called
«Dimension field name (subsets or attributes)».

Example
To get previous month value for October, the SDBA function would look like this:
=SDBA(“Months”, “October”, “Months_Previous”)
The value "September" will be returned.

SDBW function
SDBW is used to send a cell value to a table with any number of dimensions.
SDBW is a complex function that places a heavy load on the system. Its use is advisable only in exceptional situations and on a small scale.
Syntax
=SDBW(value, table, p1, p2, […p20])
Parameters
value
The value of the cell.
table
The name of the table where you want to send the value.
p1,…pn
Dimension element names that define the intersection containing the value to be retrieved.
Arguments p1-pn are sequence-sensitive. p1 must be an element from the first dimension of the table, p2 must be an element from the second dimension, and so on.
Parameters can be set both by absolute values and by cell references containing the required values.

Advanced use of the SDB function:

Using the sdb function in the following format helps to significantly reduce the number of rules for the table.

Syntax

=SDB (#db(#table=tablename|d1=elementname))

Parameters
tablename
The name of the table from which to retrieve the value.
d1
The name of the dimension which you want to specify.
elementname
Element name of the specified dimension that define the cell containing the value to be retrieved. Values of unspecified dimensions will be taken from the current cell to which the formula is applied

Example:
Using the SDB function in the following format summarizes all consolidations of all dimensions for NET CASH FLOW element: