Database Write Back

Contents[Hide]

1. Overview

As a visual data discovery tool. Dundas BI is primarily focused on querying data from existing data sources and reporting on top of it. The data flow is supported one way - from data sources to Dundas BI. In addition, it also allows business analysts (power users) to blend in their local data simply by dragging and dropping their files (Excel/CSV) onto Dundas BI.

That being said, there are cases where it may be helpful to go in the other direction and write back to a database. For example, you may want to capture users' actions or create a simple form to collect some information that you will later want to visualize as well. The following article explains the steps required in order to write back to database using a sample showing how to add long comments into a table text column, creating a different experience compared to the built in notes functionality of Dundas BI.

Write back sample:

Dundas BI allows users to provide more context or communicate with other users viewing the same dashboard by adding notes or annotations to a data point.

However, instead of viewing notes by hovering over the red triangle like in the image above, the user can view or add live comments in an adjoining column of the table visualization creating a different experince for the business users.

1.1. Overview of the Write Back Flow

  • Define a procedure or a manual query that will capture and write back to the data to a database.
  • Create a data cube using the procedure or the 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 (drop down list, Textbox, HTML Label, Slider, etc.).
  • Pass that captured information back to procedure or query by updating the public parameter values with the captured info. That will effectively execute the procedure or query and will create the write back to the database.

2. Set up the data

The sample setup below uses SQL as the datasource. 

If it does not exist already, create a new column in the SQL database table that fetches the comments entered by the user on the dashboard. Use a placeholder value for the comments such as '..', but do not leave it empty.

SQL database table
SQL database table

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

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

2.2. Create Data Cube

Create a new data connector with the database table modified above. 

Create a new data cube using the stored procedure [dbo].[Notes]

Create a data cube using the stored procedure
Create a data cube using the stored procedure

Configure the Stored Procedure node and expose both the parameters as public.

Set the transform parameters as public
Set the transform parameters as public

3. Design the dashboard

Drag and drop the above created Data cube on the dashboard canvas. The data appears as a table visualization named Table1.

3.1. Add a Drop Down List

Next, go to the Toolbar, and select Drop Down List from Components.

Select the drop down list and click the Properties tab.

Under Main\Common\Items, you'll see that the drop down list is configured with a single item by default. Click the item in order to modify it.

Click the Main tab and set the Value property to the name of the Project as it is in the database. Click on the Text tab and set the Caption property to the name of the Project.

Add new items corresponding to the Unique Id (in this case the Project Name).

Add new items in the Dropdown list
Add new items in the Dropdown list

3.2. Add an HTML label

Go the toolbar, and from the Components select HTML Label.

Select the HTML Label and under Properties\Text, add the following to the HTML Label Text:

<textarea id=myArea rows=5></textarea>
<script language="javascript">
var today = new Date();
document.getElementById('myArea').innerHTML=today;
</script>

The Javascript in the HTML code above is optional. It is used to add current time in the comment.

3.3. Add View Parameters

Click on Parameters tab at the bottom of the dashboard designer.

Click on Add New to add a new view Parameter. 

Rename this View parameter to something meaningful. For example, Project, for ease of use.

Assign this to the @project parameter defined in the stored Procedure.

Assign the view Parameter to the @project parameter
Assign the view Parameter to the @project parameter

Click on Parameters to go back to the list of All View Parameters.

Add another View Parameter and assign it to the @comment parameter defined in the stored procedure.

Assign the view Parameter to the @comment parameter
Assign the view Parameter to the @comment parameter

4. Add the Script

Click on Components from the Toolbar, and add a new Button Component.

In the Click event of the button, add the following script in the Script Editor:

var baseViewService = this.getService("BaseViewService");
var view = baseViewService.currentView;

var vpProject = view.control.viewParameters.first(function(vp) {
  return vp.name === "viewParameter1";});

vpProject.parameterValue.clearTokens();
vpProject.parameterValue.clearValues();

//populate the @project parameter with the value that is chosen from the drop down list.
vpProject.parameterValue.value = dropDownList1.value;
vpProject.invalidateParameterValueLastModifiedTime();

var vpComment = view.control.viewParameters.first(function(vp) {
  return vp.name === "viewParameter2";});

//fetches the name of the current user logged in. this is optional
var currentLogin = dundas.context.currentSession.accountDisplayName;

vpComment.parameterValue.clearTokens();
vpComment.parameterValue.clearValues();

//populate the @comment parameter with the comments written by the user in the HTML Label section.
vpComment.parameterValue.value = currentLogin + " " + myArea.value;

vpComment.invalidateParameterValueLastModifiedTime();

//refresh the table control to view the comments in the table.    
table1.loadData();

Build the Script.

5. Bypass Data Cache

Right click on the visualization to open the Data Binding Panel.

Click on the Edit icon. From Configure The Metric Set Binding panel, check the Bypass Data Cache checkbox.

Bypass Data Cache
Bypass Data Cache

6. Test the Dashboard

Open the dashboard in View Mode. 

Choose a Project from the Drop Down List.

Add a comment in the HTML Label and click on the button.

The comment is added to the table: 

Testing the dashboard
Testing the dashboard

7. See Also

 

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri