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.

SQL database table

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.

CREATE PROCEDURE [dbo].[Notes]
	-- Add the parameters for the stored procedure here
	@project nvarchar(50),
	@comment nvarchar(MAX)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
     
	UPDATE [dbo].[Table_1]
	SET LatestUpdate = @comment
	WHERE Project = @project
	SELECT * from [dbo].[Table_1]
   
END

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.

Create a data cube using the stored procedure

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.

Set the parameters as public

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 Value to the name of the Project as it is in the database. Set the Caption property to the same for this example, to display the same name.
Add new items and repeat these steps for other values to select.

Add new items in the drop down list

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 Items property option in Filter Values.
Select Hide Token Menu under Tokens & Menus.
In the Design tab, under Font & Text, select Multi-Line.
Clear the Label Text and Tooltip Text properties.

(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.

Assign the view parameter to the @project parameter

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.

var view = this.parentView.control;
var vpProject = view.getViewParameterByName("project");
  
vpProject.parameterValue.clearTokens();
vpProject.parameterValue.clearValues();
  
// Populate the @project parameter from the drop down list.
vpProject.parameterValue.value = dropDownList1.value;
vpProject.invalidateParameterValueLastModifiedTime();
  
var vpComment = view.getViewParameterByName("comment");
  
// Access the current logged in user name - optional.
var currentLogin = dundas.context.currentSession.accountDisplayName;
  
vpComment.parameterValue.clearTokens();
vpComment.parameterValue.clearValues();
  
// Populate the @comment parameter from the textbox.
vpComment.parameterValue.value = currentLogin + ": " + parameterTextBox1.control.value;
vpComment.invalidateParameterValueLastModifiedTime();
  
//refresh the table control to view the comments in the table.
table1.loadData();

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:

Testing the dashboard

For more information, see:

SYM-ZenDesk-v25.4.log-161407050.zip
50 KB Download