AnsweredAssumed Answered

Problem with using a SQL script / query in Dashboard Designer

Question asked by a.pietersen on Jun 24, 2015
Latest reply on Jul 22, 2016 by alberto.h.r

We experience some problems with using a MS SQL query shown below.


Running the query direct on the sql server or in the Service Level Manager gives the results I as expected.



The sql query looks like this:


use NimsoftSLM
Create Table #Temp1
var1 char(30),
var2 char(20),
var3 char(20),
var4 int,
var5 int,
var6 int,
var7 int,
var8 int,
var9 int
-- some variables to the declare
DECLARE @setpoint0 INT;
DECLARE @setpoint1 INT;
DECLARE @setpoint2 INT;
SET @TAG = 3227
Set @setpoint0 = 7800;
Set @setpoint1 = @setpoint0 + 5000;
Set @setpoint2 = @setpoint1 + 10000;
--loop while
While @TAG <= 3249 
SELECT Top 1 Substring(, 20,50) as Target,
CONVERT(VARCHAR(10), DATEADD(day, DATEDIFF(day, 0, rn_qos_data_0001.sampletime) , 0), 105)  AS Datum,
Datename(dw,DATEADD(day, DATEDIFF(day, 0, rn_qos_data_0001.sampletime) , 0)) as Dag,
Max(rn_qos_data_0001.samplevalue)-Min(rn_qos_data_0001.samplevalue) AS Urenperdag,
Max(rn_qos_data_0001.samplevalue) AS Totaal,
@setpoint0 AS offset,
@setpoint1 AS maint,
@setpoint2 AS repl,
rn_qos_data_0001.table_id AS id
FROM rn_qos_data_0001 JOIN s_qos_data ON (s_qos_data.table_id=rn_qos_data_0001.table_id)
WHERE (rn_qos_data_0001.table_id=@TAG and rn_qos_data_0001.samplevalue>0)
GROUP BY  DATEADD(day, DATEDIFF(day, 0, rn_qos_data_0001.sampletime), 0) ,, rn_qos_data_0001.table_id
Order by, Datum desc



Select * from #Temp1
Drop Table #Temp1



Using the table widgets in both Dashboard Designer (old and new) gives this error:


In Old Dashboard Designer:



In New Dashboard Designer: (besides, number of columns appears to be limited to 5)





My Questions:


- What do I wrong?

- Is there an other way to produce this table and show it in UIM/Nimsoft?


Kind regards

Ashley Pietersen