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;
DECLARE @TAG INT;
SET @TAG = 3227
Set @setpoint0 = 7800;
Set @setpoint1 = @setpoint0 + 5000;
Set @setpoint2 = @setpoint1 + 10000;
--
--loop while
--
While @TAG <= 3249 
Begin
INSERT INTO #Temp1
--
SELECT Top 1 Substring(s_qos_data.target, 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) ,  s_qos_data.target, rn_qos_data_0001.table_id
Order by s_qos_data.target, Datum desc

SET @TAG= @TAG+1

Continue

END
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

Outcomes