Databoard – Parameters and script

Parameters and script

Databoards will need some directions on where to pull data from the database, determined by the script and parameters, and these are subject to a few restrictions.

Step 1 – Choosing the source type

Creating a Databoard Source allow users to extract just about any information from their WorkBook database in a custom way, provided they have the required access rights and know how to program in SQL.

Go to ‘Settings’ and start by choosing the source type, there are 2 types: SQL or StoredProcedure.

SQL:
Will let you write your own script. More difficult due to the fact that you need to know what you are doing and you should have knowledge about the database structure

Custom MS SQL Scripts are executed as explained in the “General” section. Parameters defined in the Databoard are passed as “@1”, “@2” … “@n”, where the number refers to the “Index” of the different parameters. Also, two “special” paramters are passed to the script, “@DataboardId” and “@UserId”, which are both of the Int32 type, and hold the id of the Databoard and the user currently accessing it, respectively. None of these parameters should be explicitly declared in the script, as they will automatically be dynamically declared during execution.

Notice that all columns returned by the script need to have a unique alias (possible defined by the “AS” keyword). Also, the script need to return the same number of columns with the same aliases, regardless of input parameters; if no data is available (f.ex. if parameters are unset), the script still needs to return an empty data set with the fixed columns (but no rows). This is not strictly enforced, but the Databoard behaviour is undefined if the columns change between executions.

The main difference from “generic” MS SQL is that all scripts are executed in a read-only context; any command that would result in a write to the permanent data store will fail. It IS, however, possible to create temporary data tables and write to those, as usual. Therefore, the only permanent data tables available to the Databoard designer are those predefined by WorkBook (see the appendix for a full list).

StoredProcedure: 
Will give you the ability to use one of the StoredProcedures already created in the system. But keep in mind that the system will not display all StoredProcedures, so one should write them by hand in the field.

Stored Procedures are predefined in the WorkBook system; they cannot be created or edited, and therefore their internal workings will not be explained any further here. A full list of Stored Procedures suitable for use as Databoard Sources is provided in the appendix, along with information on their required parameters.

Databoard Sources are either predefined Stored Procedures or custom SQL scripts. Both are written in standard Transact-SQL (MS SQL). A full reference or programming course is beyond the scope of this article, as it will be assumed that the reader knows SQL already, and only the peculiar aspects of the WorkBook Databoard implementation will be explained (for a general reference on MS SQL, see https://msdn.microsoft.com/en-us/library/dn198336.aspx).

Step 2 – Parameters

Databoard Parameters are defined in the Parameters tab. Every parameter has a UTF-8 encoded string “Name” that will be displayed to the Databoard user, but is internally referenced by its unique integer “Index”. A parameters “Type” can take on one of the following shapes and optional “Default value”:

There are plenty of possibilities when it comes to parameters and you can inculcate your data pull as you pleases.

In the setup grid you will be able to see 5 columns; Index, Name, Type, Default value and Item source.

Index:
The index will be unique number and will also be the number you should point at if you will be creating dependent parameters.

Name:
This is the name of the parameter, and will also be the one that is displayed in the filter when viewing the databoard.

Type:
This option will determine which kind of parameter you have, there are 5 types; Textbox, Check, AutoComplete, Date and MultiSelect AutoComplete.

Textbox: A simple text field. Accepted default values are any UTF-8 encoded string. The parameter value passed to the Databoard Source script is of a String type.

Check: A checkbox that can be either checked (true) or unchecked (false). Accepted default values are either the lower case strings “true”/”1” (checked) or “false”/”0” (unchecked). The parameter value passed to the Databoard Source script is of a Boolean type.

Date: A calender date picker. Accepted default values are either an ISO 8601 encoded date string, or one of the “FlexDate” single letter strings described in the appendix. The parameter value passed to the Databoard Source script is of a DateTime type.

AutoComplete or MultiAutoComplete: Dropdown boxes containing a number of predefined items. These items are generated by the (required) MS SQL script in the “Items source” field, returning a data set with exactly two colums (Id and Name), as explained below. The paramter fields “Items source” and “Parent index” are used exclusively for these parameter types. The default value can be any value from the Id column of the data set returned by the “Items source”. The parameter value passed to the Databoard Source script is of a String type.

The difference between AutoComplete and MultiAutoComplete is that the latter allows the Databoard user to select multiple items from the dropdown list; the (possibly) multiple Id values are still passed in a single string, separated by commas (“,”).

The “Items source” for AutoComplete and MultiAutoComplete parameters is a required custom MS SQL script with the same restrictions as for a custom SQL Databoard Source, returning a data set with exactly two columns; these can have any alias (they MUST have an alias, as explained in the “Databoard Scripts” section), but the first column will always be the designated Id column, and the second column will be the designated Name column, regardless of alias.
The “Items source” script will not be passed the “@DataboardId”, “@UserId”, or “@1” … “@n” parameters, but will instead be passed the Int32 “@InterfaceLCID” parameter with the language code id of the user running the Databoard script. The “Parent index” field refers to the “Index” of a “parent” AutoComplete, and if it is set, the “Items source” script will be passed a String “@ParentId” parameter containing the currently selected Id value of the “parent” AutoComplete

Default value:
This field will put the written value into the parameter. If nothing should be default this filed should be left blank

Item source:
This is where you put in you source, this is written by SQL

settings

 

SQL limitations

There are two limitations to the SQL statements you can write in your statements. You only have readonly access to the database, which means that all statements that will change the permanent data store will be rejected.

Also, all scripts are run with a 2 second timeout limit. This means that if the script takes longer than 2 seconds, it will be terminated and a timeout message will be sent to the user. If this happens often, try to limit the amount of logic done by the script.

 

Appendix:

a. “FlexDate” values can be either set to a fixed date, or dynamic flex dates. The value will be converted to a regular DateTime parameter when passed to the Databoard Source script, so the difference is only relevant when chosing default values.
The possible flex date values are:
“d”: Today
“p”: Primo current month
“u”: Ultimo current month
“l”: Last date last month
“n”: First date next month
“e”: Last day current year
“s”: First day of current year
“k”: Last date next month
“o”: Last workday current month
“y”: First workday of current month
“w”: Last workday current year

(b. List of Stored Procedures suitable for use as Databoard Sources along with their required paramter definitions)
(c. List of WorkBook database tables and a brief explanation of their columns)

 

Was this article helpful? Useful Useless 0/9 found this article helpful.