Database write back
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.
2. 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.
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.
3. 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.
3.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, 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
3.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 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.
4. 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.
4.1. Add a drop down list
From Components in the toolbar, select Drop Down List, as one example of how users can choose a value to pass as a parameter.
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 to select.
4.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.)
4.3. Add view parameters
Open the Parameters window, and click 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.
Click 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.
5. Add the script
This example will use 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, click 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();
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:
