416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

How to write back to a database from the dashboard

Hide navigation
RSS

Overview

Dundas Dashboard is a data visualization solution and the data flow is supported one way. There is no built-in support for writing into the database from the dashboard. However, you can do this by using a workaround involving stored procedures. You need to create a Stored Procedure in your database that will accept parameters that act as values inserted into a table. On your dashboard, you will enter the values that you want to add to the table and pass them to the stored procedure parameters via custom scripting. The stored procedure can be designed to be able to execute both insert and select statements, so that data could be retrieved from or entered into the database.

This article will show you the steps involved with an example on how to add and delete data in your database table from the dashboard. See the Related Topics if you need details or are unclear on how to achieve each step.

Create the Stored Procedure

Create a stored procedure in the database that updates your database table. The one below executes the insertion to a database table as well as deletion from the table. It accepts the values to be inserted/deleted as parameters as well as an additional parameter that defines what action needs to be performed.

CREATE PROCEDURE spSaveToDatabase @action VARCHAR(10), @name VARCHAR(25), @value VARCHAR(25) AS

SET @action = UPPER(@action)

IF @action = 'ADD' BEGIN Logic here... INSERT INTO ReadWriteData VALUES(@name, @value) END

IF @action = 'DELETE' BEGIN
Logic here... DELETE FROM ReadWriteData WHERE Name = @name END

-- Return new results SELECT * FROM ReadWriteData





Create the Virtual Table

Create a virtual table from that stored procedure and make sure all the parameters are made public:

Image

Create the KPI/Dataset

Create the KPI/Dataset based on the Virtual Table above and make sure all the filters are public:

Image

Dashboard

Add the controls

On your dashboard, add a datagrid based on the dataset created in the above step. Add the text boxes and button controls that will perform the action on the database. Name the buttons and set the Text property of one button as Add the other as Delete. The Text Boxes will take the input from the user and the buttons will execute the actions:

Image

Add parameters

Create one dashboard parameter for each of the external child structure filters:

Image

Add script

On the Click Interaction of each button, add the following script that runs a script in the dashboard's Interactions List

services.RunDashboardInteraction("AddDeleteAction", sender, e); //the "sender" determines which button is running the script

Add a script called AddDeleteAction to the dashboard's Interaction List that will pass the values entered in the respective text boxes back to stored procedure filter and execute it.

  1. UNSUPPORTED UNLIMITED

object button = sender; // The button string action = button.Text.ToUpper(); // Use the button's text as the action to perform

// Get the values entered in the respective text boxes string name = Name_TextBox.Text; string value = Value_TextBox.Text;

// Ensure we have values in our fields if(!string.IsNullOrEmpty(name) && !string.IsNullOrEmpty(value)) { // Create our three parameters

// @Action parameter object fvd1 = new DashboardFilterValueData(); object mvd1 = new DashboardMemberValueData(); mvd1.Values.Add(action); fvd1.MemberValues.Add(mvd1); paramAction.FilterValues.Clear(); paramAction.FilterValues.Add(fvd1);

// @Name parameter object fvd2 = new DashboardFilterValueData(); object mvd2 = new DashboardMemberValueData(); mvd2.Values.Add(name); fvd2.MemberValues.Add(mvd2); paramName.FilterValues.Clear(); paramName.FilterValues.Add(fvd2);

// @Value parameter object fvd3 = new DashboardFilterValueData(); object mvd3 = new DashboardMemberValueData(); mvd3.Values.Add(value); fvd3.MemberValues.Add(mvd3); paramValue.FilterValues.Clear(); paramValue.FilterValues.Add(fvd3);

// Trigger the parameters by calling at least one of them to invalidate. // Since all parameters are tied to one KPI/Dataset), // all three will be applied.

paramAction.InvalidateFilter(); }





Related Topics


About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.