Transaction Service

Source - https://www2.microstrategy.com/producthelp/Current/AdvancedReportingGuide/WebHelp/Lang_1033/Content/Creating_a_Transaction_Services_report.htm

Enabling transactional functionality in MSTR involves creating transactional reports that write back to the back-end systems, as well as documents and dashboards that expose transactional functionality to the end users.

To create a Transactional application, follow the steps:

1. Configure a database instance that points to data source that you want to use
2. Create a Query report that displays the data in the interface
3. Create a Transaction report that modifies the data in the data source
4. Create a Report services document
   • Use the query report to display data as a grid or as a text box on a panel stack
   • Map query report objects to Transaction input objects
5. Create a selector to submit your changes to data source

Step 3. Creating a Transaction Report

A Transaction report is a Freeform SQL or XQuery Report, defined in the Freeform SQL Editor in MSTR Desktop. Save the Transaction report and associate it with a grid/graph or set of text boxes in a RSD. You then Trigger its execution by clicking or tapping a selector.

Note: You can browse and edit the Transaction reports only from Desktop not from Web

1. In MSTR Desktop, launch the Report editor
2. In new grid window, click Freeform sources tab
3. Click create Transaction report
4. In the list of data sources, select the appropriate database instance
5. Click ok
6. In the Freeform SQL editor, define a Transaction
7. Define the input objects for the Transaction
8. Define the objects for the Transaction
9. Click OK to close the Freeform SQL editor
10. Save and close the report

• Keep the Insert only Distinct Records check box cleared - if you want all records to be inserted when a transaction triggers an update. This behavior is intended for using Transaction services to update fact table that store fact data
• Select the Insert only Distinct Records check box- if you want all records to be inserted when a transaction triggers an update. This behavior is intended for using Transaction services to update lookup table that store attribute information

Drag and drop Attribute to Input Objects Form. Change form field to ID.

Selector Types:

There are Three types of Action Selectors:

• Submit - Use this to Update/Insert/delete data from data source
• Recalculate - Use this to recalculate the values of derived metrics or subtotals, reapply number and date formatting, and update other values calculated by analytical engine when the user clicks the selector. No data is submitted to the transaction services report
• Discard Changes - To discard the changes

exec PROCEDURE2 '1001','administrator' (--Sql Server)

USE [DB1]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROCEDURE2]

(@P_USER_ID INT,

@P_USER_NAME VARCHAR(100))

AS

DECLARE @SQL VARCHAR(2000);

IF OBJECT_ID('tempdb..#final_results') IS NOT NULL

DROP TABLE #final_results;

CREATE TABLE #final_results
(USER_ID INT,
USER_NAME nchar(50));
)