Database write back

Contents[Hide]

1. Overview

Dundas BI includes built-in data input features for storing data entered by end users in Dundas BI's 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 Dundas BI instead. The following article outlines an example of how you can use Dundas BI to write data to your own database.

Write back sample:

Dundas BI 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.

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, 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 Server 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 a 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 from Components select Drop Down List.

With the drop down list selected, open the Properties window.

In the Main tab under Items, you'll see that the drop down list is configured with a single item by default. Click the item to modify it.

In the Main tab, set the Value property to the name of the Project as it is in the database. In the Text tab, set the Caption property to the same.

Add new items and repeat these steps for other values.

Add new items in the drop down list
Add new items in the drop down list

3.2. Add a textbox

With nothing selected on the canvas, go the toolbar, and from Filters select Textbox.

If the filter was automatically connected, disconnect it and open the Properties window to change its settings:

  • Check the Manual Items property
  • In the Look tab, check Hide Token Menu
  • In the Layout tab, check Multi-Line
  • In the Text tab, clear the label and tooltip 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.)

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.getViewParameterByName("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.getViewParameterByName("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 textbox. 

vpComment.parameterValue.value = currentLogin + " " + parameterTextBox1.value; 
vpComment.invalidateParameterValueLastModifiedTime(); 

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

Build the script.

5. Bypass caching

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

Click on the Edit icon and check the Bypass Data Cache checkbox.

Bypass Data Cache
Bypass Data Cache

6. Test the dashboard

Click 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
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