asked on

using CTE in SSRS reports

I am using a CTE in a SSRS report. the CET works fine when I run it out side of the report. When I run it in the report the data does not display. I see not error message or anything.

Jim Horn

I'll bite ... copy-paste the CTE into this question.

TClevel

ASKER

With all_folders AS(
SELECT
CASE WHEN C.Name = '' THEN 'Home' ELSE C.Name END AS ItemName,
C.Description as Report_Description,
LEN(C.Path) - LEN(REPLACE(C.Path, '/', '')) AS ItemLevel,
CASE
WHEN C.type = 1 THEN '1-Folder'
WHEN C.type = 2 THEN '2-Report'
WHEN C.type = 3 THEN '3-File'
WHEN C.type = 4 THEN '4-Linked Report'
WHEN C.type = 5 THEN '5-Datasource'
WHEN C.type = 6 THEN '6-Model'
WHEN C.type = 7 Then '7-ReportPart'
WHEN C.type = 8 Then '8-Shared Dataset'
ELSE '9-Unknown' END AS ItemType,
CASE WHEN C.Path = '' THEN 'Home' ELSE C.Path END AS Path,
ISNULL(CASE WHEN CP.Name = '' THEN 'Home' ELSE CP.Name END, 'Home') AS ParentName,
ISNULL(LEN(CP.Path) - LEN(REPLACE(CP.Path, '/', '')), 0) AS ParentLevel,
ISNULL(CASE WHEN CP.Path = '' THEN ' Home' ELSE CP.Path END, ' Home') AS ParentPath, c.ItemID
FROM
dbo.Catalog AS CP
RIGHT OUTER JOIN
dbo.Catalog AS C ON CP.ItemID = C.ParentID)
select ItemName AS "Report Name", ItemType, ParentPath, ItemID,
case WHEN ParentLevel = 1
THEN STUFF(ParentPath, 1, 1, '') ELSE
SUBSTRING(STUFF(ParentPath, 1, 1, ''), 1, CHARINDEX('/', STUFF(ParentPath, 1, 1, '')) - 1)END AS Applications
from all_folders r INNER JOIN ExecutionLog e ON r.ItemID = e.ReportID
where ItemType = '2-Report' AND (CONVERT(varchar, e.TimeStart, 101) >= CONVERT(varchar,'11/27/2013 12:00:00 AM', 101))
AND (CONVERT(varchar, e.TimeStart, 101) <= CONVERT(varchar, '12/12/2013 12:00:00 AM' , 101))

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ValentinoV