Framework:Macros

A macro is a group of actions, each of which carries out a certain operation such as the opening of a form or the execution of a server command. With macros, you can automate tasks that need to be performed frequently. For instance, you can create a macro that executes an import, i.e. deletes data, reimports them and then opens a report if a user calls it up through a menu item.

To give properties to the macro, open the macro by clicking the “Design“ button, or by clicking on the context menu with the right mouse button, or by double-clicking the batch designer. With the “New” menu item, you can also create a new macro here. Several actions can be defined in the batch designer. Additionally, you can assign a comment to these actions.

Available actions:

• NavAction
• ServerCommand
• Message

The created actions will then be executed in the batch designer one after the other. The order of execution can be changed with the and buttons.

Framework, Batch designer

Depending on the chosen action, the user has different configuration options available.

NavAction

With NavActions, you can open masks, dimensions etc. In addition, NavActions can also execute formulae, imports etc. A description of all NavActions you find here.

Framework, NavAction

ServerCommand

Various different server commands are available for choice

Command	Description
DBC Execute Procedure	Stored Procedures can be executed. The name of the Stored Procedure must coincide precisely with the name of the Stored Procedure of the SQL Server (for instance sp_calc_table). In addition, parameters of the Stored Procedure can be transferred. The individual parameters are separated with “;”, and they all start with an “@” (for instance: @param1=FirstParameter;@param2=SecondParameter). The Database Connection String looks as follows: Server=ServerName;Initial Catalog=DatabaseName;user id=MyUserId; password=MyPassword
Delete plan datasets	The ServerCommand ‘Delete datasets’ deletes all datasets of the selected plan of all plan-dependant tables. If the plan is set to “Current plan”, only datasets of the plan where the user is logged in are deleted.
Formula activate	This ServerCommand can activate or deactivate formulae. In addition, the formula “Macro active” can be set.
Activate formulae	Activates the CoPlanner formulae.
Deactivate formulae	Deactivates the CoPlanner formulae.
Reload PlanSecurity	Reloads the security for the current plan.
Copy plan	Copies all datasets from one plan to another plan.
Copy table datasets	All datasets within the selected plan of a table are copied into another table. In addition, XML filters for the datasets can be specified here.
Delete table datasets	All datasets within the selected plan of a table are deleted. In addition, XML filters for the datasets can be specified here. Apart from the selection of the table, it is also necessary to select a plan for deletion. This can be done by explicitly selecting a certain plan, or with the “Current plan” function, which automatically selects the plan of the login. If the option “independent of plan” is chosen, the data of the selected table are deleted from all plans! See example below.
Recalculate table of this plan	All datasets within the selected plan of a table are recalculated. In addition, XML filters for the datasets can be specified here..
Start import on the Server	Starts an import object on the Server. As each import object returns a ‘successful’ message, this ‘successful’ message can be suppressed with the “Activate feedback” parameter.
Synchronize database	Forces the synchronization of the CoPlanner server and the SQL server before further CoPlanner ServerCommands are executed.
Synchronize dataobject	(9.5 HF 1.8)Forces the synchronization of an CoPlanner table or dimension and the SQL server before further CoPlanner ServerCommands are executed.
Complete pending calculations	Forces the execution of all pending calculations (formulae). Then a database synchronization is forced before further ServerCommands are executed.
Execute formula	Executes the selected formula.
Change session parameter	Changes the session properties. Here it can be specified, for instance, that the session context is executed in macro mode so that subsequently further formulae will be executed whose property is set to ‘Execute in macro mode’. For this purpose, please specify the following parameters: To change over to macro mode: MacroMode=1 To leave the macro mode: MacroMode=0
OLAP refresh	Macro command to update OLAP connections (connections from CoPlanner server to SSAS will be closed and rebuilt if necessary). In addition to updating the dimensions this command is directed to delete any possible ADOMD caches (Security). After restarting the client changed dimension members in CoPlanner should be obvious to the user.
Set Log Level	For development purposes, all messages that are generated in the log can be shown in the console/Telnet in the Console. In real operation, it is not recommended because it can cause performance degradations. Configuration: Framework, Macro - Loglevel configuration

Example: Delete table datasets

The task is to use one macro in order to delete all datasets from a table that belong to the logged in plan. For this purpose, create a new macro and select “ServerCommand” as the action and perform the settings as shown in the following Figure.

Framework, Makro – Delete table rows

Call up the filter editor by clicking the button.

Framework, Macro – Filter - Editor

From the combination list field, select the entry “BAB_Plan” and “Bike GmbH” in the “Enterprise” filter. By clicking the ‘Add’ button, this setting is applied. By replacing the criterion with the @Session_Enterprise_ID@ session variable, this filter always refers to the logged in enterprise.

If you need this filter several times, you can save it with the “Save filter” command and reinsert it with “Load filter”.

Example: Delete table datasets depending on an dimension attribute

The task is to use one macro in order to delete all datasets from a table that belong to an attribute of a dimension. In this example all records will be deleted in table "Tab1" which contains in the attribute field the value "True" in the dimension.

Framework, Macro – Filter - Editor

Column	Expression
Feld	Standard Dimension Column Name
Comparison type	Has to match with the data type(me ist =, not_me ist <>, usw.)
Criterion	Has to match with the data type
Type	BY_ATTRIBVAL_FeldNameInDim (In this example the attribute field is named "AttribFeld")

Message

Information about the execution of the macro at the client can be shown to the user with messages. For this purpose, enter the message text, message title and message type.

Framework, Macro - Message

Types of messages

Error Framework, Macro - Message - Error	Exception Framework, Macro - Message - Exception	Information Framework, Macro - Message - Information	Question Framework, Macro - Message - Question
No Symbol Framework, Macro - Message - No Symbol	Stop Framework, Macro - Message - Stop	Warning Framework, Macro - Message - Warning

Continuation condition

The continuation condition is available to influence the continuation of the macro. If it is activated, the condition must be fulfilled to continue with the macro. If the condition is not fulfilled, the macro is stopped at exactly this point. It is possible to display a message to indicate that the macro has been aborted.

Framework, Macro - Continuation condition - Inaktiv

Framework, Macro - Continuation condition - Aktiv

For the condition, both expression fields must be filled in. An expression can be a query to the CoPlanner Server through the functions provided by the totality of business economics formulae, an SQL statement to the CoPlanner system database or a constant value. The query to the CoPlanner Server is identified with Transform=, an SQL server query with Sql=. A constant value is entered without special identification.

Examples:

Framework, Macro - Continuation condition – SQL combined with CoPlanner query

Framework, Macro - Continuation condition - SQL combined with constant value

In case of constant values, the data type must be observed. If the SQL or CoPlanner query returns a sequence of characters as a result, the value must be surrounded by single quotation marks. If the query returns a numeric value as a result, the quotation marks are omitted.

If an SQL query is defined that does not return a value but a results table, then the value of the left top corner is used for the comparison.

Framework, Macro - Continuation condition - Result of a query with table as a result

Whether the SQL query does not return a single line can be queried with the ‘ZERO’ constant.

Framework, Macro - Continuation condition - Query whether datasets are supplied

Expressions can also receive CoPlanner session variables. Before the evaluation of the expression proper, these are replaced with the corresponding value. Session variables can also be used as a constant value.

Framework, Macro - Continuation condition - Query with session variable

If the option “Report in case of non-fulfillment” is activated, the user is shown the message with the corresponding text if the macro is aborted. If the option is not active, the macro will be aborted without further message.

Framework, Macro - Continuation condition - Message in case of non-fulfillment