Database Write Back
This applies to: Managed Dashboards, Managed Reports
Symphony includes built-in data input features for storing data entered by end users in your warehouse data storage area, as well as notes functionality to allow users to add and reply to notes tagged to data points.
In some scenarios, there may be a specific requirement to store input data outside of Symphony instead. The following article outlines an example of how you write data to your own database.
Write Back Sample
Symphony allows users to provide more context or communicate with other users viewing the same data cube data by adding notes or annotations to a data point.
Instead of viewing notes by hovering over the red triangle like in the image above, we will allow users to view or add live comments in another column of the table visualization and store them in our own database.
Here is an overview of how to write back data from a dashboard:
Define a procedure or write a manual query that will capture and write back to the data to a database (and also return some data).
Create a data cube using that procedure or manual query that will capture and write back the data to a database using public parameters passed from the dashboard.
Design a dashboard that will provide the user option to input the information to write back using different components or filters, such as a drop down list or textbox.
Connect view parameters and/or filters to the data cube parameters to pass them the captured information for executing the procedure or query and writing back to the database.
Set Up the Data
The sample setup below uses SQL Server as the data source.
In this case, a new column in the SQL database table is used to store the comments entered by users. A placeholder value for the comments such as '..' allows it to be intentionally shown as empty for this example.
Create the Stored Procedure
Create a stored procedure that receives two parameters: the comment column (LatestUpdate) and a unique ID such as the Project name in the sample data, which identifies one specific row to be updated in the database.
This example also returns the updated data from the table to be displayed using the same stored procedure.
Important: There are various ways your stored procedure could get called again with the same parameter values when used on a dashboard. An ID that can uniquely identify each update can prevent unwanted duplicates.
Create a Data Cube
Create a new data connector with the database table modified above. Create a new data cube using the stored procedure or query.
Configure the Stored Procedure transform, or the manual query's placeholder parameters, and expose the parameters as Public so they can be used outside of the data cube.
Design the Dashboard
Drag and drop the data cube on the dashboard canvas. The data appears as a table visualization.
In our example, which returns all of the updated table data from the same stored procedure, continue adding the rest of the data to be displayed as columns in the table.
To prevent old data from being returned if the same parameter values are passed later but are meant to execute the procedure/query each time, you can bypass the data cache on this metric set.
Add a Drop Down List
This example uses a drop down list to pass parameter values, although you can use other controls instead.
From Components in the toolbar, select Drop Down List.
With the drop down list selected, open the Properties window.
You'll see that the drop down list is configured with a single item by default. Click the item to modify it.
Under Animation & Interaction, set the
Valueto the name of the Project as it is in the database. Set theCaptionproperty to the same for this example, to display the same name.Add new items and repeat these steps for other values to select.
Add a Textbox
With nothing selected on the canvas, go the toolbar, and from Filter select Textbox.
If the filter was automatically connected, disconnect it and open the Properties window to change its settings:
Select the
Manual Itemsproperty option in Filter Values.Select
Hide Token Menuunder Tokens & Menus.In the Design tab, under Font & Text, select
Multi-Line.Clear the
Label TextandTooltip Textproperties.
(If the textbox displays a value from when it was connected to data, it will instead display the value from its Value property the next time the dashboard is opened.)
Add View Parameters
Open the Parameters window and select Add New to add a new view parameter.
Rename this view parameter to something meaningful for ease of use, such as Project.
Assign this to the @project parameter defined in the stored procedure to pass a value to it.
Select the Parameters button to return to the list of All View Parameters.
Add another view parameter and assign it to the @comment parameter defined in the stored procedure.
Add the Script
This example users a button with script to pass the values to the parameters, but it's also possible to connect filters to data cube parameters and use an update button.
In the toolbar, select Components and choose Button.
In the Click event of the button, add the following script in the script editor, and update it to refer to the Script Name you chose for each of your view parameters, etc.
Test the Dashboard
Select Sandbox View in the toolbar.
Choose a project from the drop down list.
Add a comment in the textbox and click on the button.
The comment is added to the table:
For more information, see:
Comments
0 comments
Please sign in to leave a comment.