Voted Best Answer
Oct 24, 2022 - 09:38 AM
The dashboard cannot handle recursive tables ie a table that references itself.
A database view cannot be used as we need to use recursion (looping) to get the data we need which views cannot do.
A Stored Procedure can be used within the database to create the desired output BUT the dashboard cannot consume stored procedures !
The way around this is to create a database ‘Table Function’, ie a function that returns a table which the dashboard can consume.
Step 1 - Create a table that emulated recursive data
Step 2 - Create Table Function:
CREATE FUNCTION [dbo].[ufnPigsEar_Recursive]()
RETURNS @TBL TABLE
( [ID] [int],
[Name] varchar(200) NULL,
[ParentID] INT NULL,
[Depth] INT NULL
)
AS
BEGIN
DECLARE @Id int
DECLARE @TEMP TABLE ( [ID] [int],
[Name] varchar(200) NULL,
[ParentID] INT NULL)
DECLARE @RESULTS TABLE ( [ID] [int],
[Name] varchar(200) NULL,
[ParentID] INT NULL,
[Depth] INT NULL
)
INSERT INTO @TEMP
Select * From PigsEar where ParentID is Null
While (Select Count(*) From @TEMP) > 0
Begin
Select Top 1 @Id = Id From @TEMP
begin
with ItemPath as
(
select a.[Id], cast(a.[Name] as nvarchar(max))as Name, a.ParentID ,1 as Depth
from PigsEar a
where a.ID = @id
union all
select a.[Id], parent.[Name] + '/' + a.[Name], a.ParentID, 1 + Depth
from PigsEar as a
inner join ItemPath as parent on parent.id = a.parentID
)
insert into @RESULTS
select *
from ItemPath
end
Delete @TEMP Where Id = @Id
End
INSERT INTO @TBL
Select * From @RESULTS
RETURN
END
GO
Step 3 - Test the Table Function:
Step 4 - Add to Dashboard:
Go into the Dashboard Configuration – Data Connections – Add New Table (Manually)
In Table Name Enter (SELECT Name, Depth FROM [dbo].[ufnPigsEar_Recursive] () )
In the Alias enter [PigsEar]
Step 5 - Create Data Objects.
You must also create the table objects manually….
Add [Name] and Depth. NB The Name object to has been renamed to Level
Step 6 - Create a Table Chart in the Dashboard
Add New Comment