Write Comments to Own database from Dashboard

#1

Hello Guys, First I would like to appreciate all help by Dundas BI and forum mebers. It;s really great Forum.
Now I will narrate my question.
I want to write comments from different textboxes filters on dashboard to my custom database. I have followed below link .
https://www.dundas.com/Support/learning/documentation/cleanse-consolidate-modify-data/how-to/passing-placeholder-value-from-dashboard-to-manual-mdx-select
https://www.dundas.com/support/learning/documentation/cleanse-consolidate-modify-data/how-to/passing-parameter-value-from-dashboard-to-stored-procedure

However problem with this is when I try to tab from one comment box to another comment box, it is writing comment that number og time i.e. if I have 2 comment box as shown below Comment1 and Comment2. When I write comment1 and give tab, Comment1 is getting saved in database. When I write Comment2, both Comment1 and Comment2 (both are two columns in my custom table) are getting saved. Hence 2 records getting inserted, instead of 1.
Image as below:

image
image.png878×404 10.6 KB

My SP is as below:

–Use [Honeywell Golden Batch]
ALTER PROCEDURE [dbo].[Comment_Writeback]
– Add the parameters for the stored procedure here
@comment1 nvarchar(200),
@comment2 nvarchar(200)
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]

Insert into [Test_WriteBack] ([Date],[Comment1],[Comment2])
Values (Getdate(),@comment1,@comment2)
SELECT * from [Test_WriteBack]

END

Is there any way to write only on ‘Save Comment’ button click event instead of tab?
Thnx.

We can write back to source data?
#2

Hi Prashant,

Appreciate the feedback!

You can certainly do what you are describing using a stored procedure and parameters in Dundas BI. I’ve done a simple example recently using 4 input parameters. Here is my stored procedure…

CREATE PROCEDURE [dbo].[WriteRiskTargets]
(
@StaffTurnover float = 7,
@ShopLeadership float = 8,
@TotalTTV float = 15,
@WriteOffVolume float = 10
)
AS

BEGIN
IF @StaffTurnover IS NOT NULL
BEGIN
UPDATE [dbo].[RiskTargets]
SET [Target] = @StaffTurnover/100.0
WHERE [Category] = ‘Staff Turnover’
END

IF @ShopLeadership IS NOT NULL
BEGIN
	UPDATE [dbo].[RiskTargets] 
	SET [Target] = @ShopLeadership/100.0
	WHERE [Category] = 'Shop Leadership'
END

IF @TotalTTV IS NOT NULL
BEGIN
	UPDATE [dbo].[RiskTargets] 
	SET [Target] = @TotalTTV/100.0
	WHERE [Category] = 'Total TTV'
END

IF @WriteOffVolume IS NOT NULL
BEGIN
	UPDATE [dbo].[RiskTargets] 
	SET [Target] = @WriteOffVolume/100.0
	WHERE [Category] = 'Write-off Volume'
END

-- Return something
SELECT 1 as [Status]

END

**** Notice how I am NULL checking the input?

You can create your dashboards such that the parameters are all using NULL by default which will prevent accidental write back when the dashboard is run. This will also solve your tab problem because they will both need to be set before the write back can successfully take place.

Hope this helps.

#3

Ok got it however can’t we have option to return all parameters to SP on when user will click on ‘Save Comments’ button only otherwise it will write to own DB table when all 4 parameters have values…so user modifies one comment it will be also written to db which is not correct behavior.
Appreciate your quick reply.

#4

Hi Prashant,

You’re right, if you rely on the updating of the parameters themselves to trigger your stored procedure you’ll get too many write backs. Use an update button instead so that nothing is committed until he user clicks on the update button.

https://www.dundas.com/support/learning/documentation/create-view-content/filters/using-the-parameter-update-button

1 Like