Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

using CTE in SSRS reports

Posted on 2013-12-12
5
Medium Priority
?
523 Views
Last Modified: 2014-01-31
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.
0
Comment
Question by:TClevel
  • 2
  • 2
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39715263
I'll bite ... copy-paste the CTE into this question.
0
 

Author Comment

by:TClevel
ID: 39715431
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))
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 total points
ID: 39716872
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.
0
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 1000 total points
ID: 39717423
Are you sure the report's data source uses the same DB as when you're testing it in SSMS?
0
 

Author Comment

by:TClevel
ID: 39825321
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
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question