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.
Who is Participating?
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
I'll call in ValentinoV to this question, as he knows more about SSRS then I do.

btw Post your entire data source T-SQL into this question, and specificy whether there are any IF blocks or dynamic SQL in it.  SSRS, like SSIS, requires a 'contract' between a source and a target that does not depend on any conditions.  So, if there is any code that may alter the source for whatever reasons, that will need to be changed to a static set.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I'll bite ... copy-paste the CTE into this question.
TClevelAuthor Commented:
With all_folders AS(
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,
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
dbo.Catalog AS CP
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))
ValentinoVConnect With a Mentor BI ConsultantCommented:
Are you sure the report's data source uses the same DB as when you're testing it in SSMS?
TClevelAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 167 points for ValentinoV's comment #a39717423
Assisted answer: 167 points for jimhorn's comment #a39715263
Assisted answer: 0 points for TClevel's comment #a39715431
Assisted answer: 166 points for jimhorn's comment #a39716872

for the following reason:

Thank you all for your help
All Courses

From novice to tech pro — start learning today.