Database write back
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.
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]
Configure the Stored Procedure node and expose both the 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.
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.
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.
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.
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:
7. See also
- Using the script editor
- Edit versus view mode and sandbox view
- Modify a filter / view parameter using scripting
- Passing parameter value from dashboard to stored procedure
- Setting up data input
- Notes (annotations)