I will describe step by step how to create a transactional report Microstrategy to give the user the possibility to insert, update or delete information. Let us place ourselves in a scenario in which we have to define a small data-entry able to enter values as coefficients or weights that will be part of the business indicators .
1. First we define a table in our database with the fields we think necessary.
CREATE TABLE IF NOT EXISTS `lu_coeficientes` ( `Periodo` int(11) NOT NULL, `SLA` varchar(100) COLLATE utf8mb4_bin NOT NULL, `VAR` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL, `TRANS` decimal(10,2) NOT NULL, `RETEN` decimal(10,2) NOT NULL, `SALUD` decimal(10,2) NOT NULL, `AUTOS` decimal(10,2) NOT NULL, `MMRR` decimal(10,2) NOT NULL, `VENTAR` decimal(10,2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Script Sql de MySQL exportado a traves de HeidiSQL
2 . Then insert a number of records that will serve us as a data source categories to insert new records through transactional report
3. Now in Microstrategy create a new Freeform SQL report called Coefficients and in the report go through the menu ‘Data’ to ‘SQL definition by user’ and define a SELECT with fields that future show in transactional document and the will map one to one indicating whether dimension or metric and what type they are.
4. Then create the object for transaction report and as in the previous step go to ‘Data’ and then ‘SQL definition by user’
SELECT PERIODO, SLA, VAR,TRANS,RETEN,SALUD,AUTOS,MMRR,VENTAR, '1' as DUMMY FROM LU_COEFICIENTES
In the upper text area will place the mouse will click the right button and choose the option ‘Insert transaction’ Enter ‘Begin Transaction and End Transaction define our SQL to execute’
At this point we define the input objects to execute this transaction as shown and output objects, which in this case is a dummy metric since it is mandatory to have an output object and store our object.
5.Once created these objects create the document that will serve as data-entry to the user. In the insert the following objects that will serve as an example for this demo
- As dataset we insert our report object called Freeform Coefficients
- Inside the document we’ll insert our report object. Through this we’ll see the data we will be able to insert by means of textbox.
- In this point, we’ll create the textbox objects inside another panel that I m going to call ‘Input Coeficientes’that will serve as data entry in the report by the object ‘ text ‘ (It is recommended to name these objects) and we will design the document we think appropriate.
In the moment we have all the objcts created,through editable mode, on one of them, right click and select ‘Configure Transactionss‘.In this place we can see all the objects will be part of the transaction. At the top we will chouse the transaction object we had defined in the Developer, and next design the function of each object.
- Finally, through a selector we’ll be able to execute the transaction choosing in the selector’s propiertiers ‘submit’ event.
If you’ve followed this tutorial correctly, to make it work is essential to execute the document mode EXPRESS