I have a clarion date I need to have converted contained within a pivot. Is their an expression for converting the clarion date with report builder or do I need to convert it within the query? I am using SQL 2008 R2 report builder. I have attached my query below with the clarion portion marked Blue: ;with cte as (SELECT CAT2.Personnel.EMPLOYEENAME AS [Employee Name], CAT2.CCPsamples.MEASURE, CAT2.CCPsamples.SAMPLEVALUE AS Result, DATEADD(second,1 * (DATEDIFF(second, '20000101', F.[Date/Time]) /1), '20000101')as [Date_Time], CAT2.CCPsamples.SAMPLECOMMENT AS [Comments],CAT2.CCPsamples.DATASETNUMBER AS [Data],CAT2.CCPsamples.SAMPLEDESCRIPTION AS [Root Cause] FROM CAT2.Personnel INNER JOIN CAT2.CCPsamples ON CAT2.Personnel.EMPLOYEENO = CAT2.CCPsamples.EMPLOYEENO INNER JOIN CAT2.Activities ON CAT2.CCPsamples.ACTIVITY_NAME = CAT2.Activities.ACTIVITY_NAME CROSS APPLY (select dbo.SQLDateTime(CAT2.CCPsamples.SAMPLEDATE, CAT2.CCPsamples.SAMPLETIME) AS [Date/Time]) F WHERE F.[Date/Time] >= @MyStartDate AND F.[Date/Time] <= @MyEndDate AND F.[Date/Time] <>0 AND CAT2.Activities. DEPARTMENT = 343) select [Root Cause] as [Root Cause], [Employee Name] as [Employee Name], [Date_Time] as [Date_Time],[New Label Date] as [New Label Date],[New Product #] as [New Product #],[Comments] as [Comments],[Data] as [Data],[New Serial #] as [New Serial #],[Original Label Date]as [Original Label Date],[Original ProductCode] as [Original ProductCode],[Original Serial #] as [Original Serial #] from cte pivot (sum(result) FOR Measure IN ([New Label Date],[New Product #],[New Serial #],[Original Label Date],[Original ProductCode],[Original Serial #])) AS pvt ORDER BY [Date_Time] David Donovan

Try expressions like: convert(datetime, [New Label Date] - 36161) as [New Label Date] The simpler the solution the stronger it is If this post answers you, please mark it as answer.. If this post is useful, please vote it as useful..

Well when I insert the above syntax I recieve an error: Incorrect syntax near '-'. Below I have attached the syntax that I am using. FOR Measure IN ([convert(datetime,[New Label Date]-36161] as [New Label Date]) Any thoughts?David Donovan

Hi DeDonovan, I suggest you post your question on the Analysis Services forum, because it seems you are now facing a MDX issue rather than a Reporting Services issue. Hope that helps, Eileen