Data not getting attached to table in SSRS

Question

Data not getting attached to table in SSRS

sharada.mn8

Say Hey Kid

Points: 674
More actions
January 31, 2012 at 3:11 am

#267473

I am pulling the data from the DB and the query uses CTE.
Sample query is as follows:
; With dataCTE(A, B, C) AS
(
-- Some processing
)
SELECT * FROM TABLE1 LEFT OUTER JOIN dataCTE ON dataCTE.A = TABLE1.X
Now, when I run this query in the Query Window, it is providing me with the correct number of records.
But when I attach this data set to a table or a matrix in SSRS, Only the first record of the data set is getting displayed.
Any idea why?
--
Regards
Sharada

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Dave Morrison SSCrazy Points: 2077 More actions · Answer 1

Ok so lets start with the basics as generally its the simplest things, so please excuse if I am stating the obvious.

Firstly are you 100% sure the two queries are identical and that there is no additional processing being done before either?

If you are sure, when you edit the query and execute it from the query designer do you get just one row or the expected number of rows?

If you get a full set of rows in the query designer the only thing I can think of is that you've dragged the fields into the tablix header or group row rather than a details line?

Have a look at these few bits and let me know how you get one?

Thanks

Dave

sharada.mn8 Say Hey Kid Points: 674 More actions · Answer 2

1. Yeah.. Queries are identical. No processing done before or after that.

2. Yes.. I tried providing the parameters. The query is not providing any rows. I am not sure why.. 🙁

I mean, When I execute the query in Management Studio, data is displayed.

However, when I execute the same in Query designed, as you suggested, there is no data upon execution.

Dave Morrison SSCrazy Points: 2077 More actions · Answer 3

Ok no worries, can you provide the actual SQL as you are running in management studio and also as you are running in the report (this will be slightly different if you're using parameters).

Once you've given me those bits I should be able to help more

Thanks

Dave

sharada.mn8 Say Hey Kid Points: 674 More actions · Answer 4

There is no change in the query execution in Management Studio and the development studio. In Management Studio, I have declared the parameters and provided the parameter values. In the development studio, during the query execution, it prompts me for parameters, where I provide the parameter values. The query is as follows:

;WITH myDateCTE AS

(

SELECT CAST(@FromDate AS DATETIME) DateValue

UNION ALL

SELECT DateValue + 1

FROM myDateCTE

WHERE DateValue + 1 <= @ToDate

)

,CapacityCTE (Date, CountInc)

AS

(

SELECT

CONVERT(VARCHAR(11), DATEADD(SECOND,SUBMIT_dATE,'1/1/1970'), 106) AS [Date]

, COUNT(Inc_Number) AS[Total Inc]

FROM Table1 WHERE CatCol1 = 'X' AND CatCol2 ='Y'

AND Submit_Date BETWEEN DATEDIFF(SECOND, '1/1/1970', @FromDate)

AND DATEDIFF(SECOND, '1/1/1970', @ToDate)

GROUP BY CONVERT(VARCHAR(11), DATEADD(SECOND,SUBMIT_dATE,'1/1/1970'), 106)

)

SELECT

CONVERT(VARCHAR(11), DATEADD(SECOND,SUBMIT_dATE,'1/1/1970'), 106) AS [Date]

, COUNT(Inc_Number) AS[Total Inc] ,

ISNULL(CapacityCTE.CountInc, 0) AS [Total Inc Capacity]

FROM Table1

LEFT OUTER JOIN CapacityCTE ON CapacityCTE.Date = CONVERT(VARCHAR(11), DATEADD(SECOND,SUBMIT_dATE,'1/1/1970'), 106)

WHERE CatCol1 = 'X' AND Submit_Date BETWEEN DATEDIFF(SECOND, '1/1/1970', @FromDate)

AND DATEDIFF(SECOND, '1/1/1970', @ToDate)

GROUP BY CONVERT(VARCHAR(11), DATEADD(SECOND,SUBMIT_dATE,'1/1/1970'), 106), CapacityCTE.CountInc

ORDER BY CONVERT(VARCHAR(11), DATEADD(SECOND,SUBMIT_dATE,'1/1/1970'), 106)

Dave Morrison SSCrazy Points: 2077 More actions · Answer 5

Ah DATES! right, you're probably running into the dreaded language / dateformat / @@DATEFirst issues.

Have a look at my below blog post and it should probably start to make some sense. In a nutshell, the way sql server interprets and converts from strings into dates is VERY subjective and I think this is probably what you're falling foul of

http://blogs.adatis.co.uk/blogs/david/archive/2011/01/28/when-is-english-not-english.aspx

Have a read and then let me know if you have any questions

Thanks

Dave

sharada.mn8 Say Hey Kid Points: 674 More actions · Answer 6

Got It!!!!!!!!!!

Sorry for my inexcusable behavior.. The Row group in the table was deleted.

I recreated the entire structure again and tried to see the difference. That's when I found the Row group deleted :(..

Thanks a lot Dave.. Also, I did read your blog. Learnt some new things 🙂 Thanks again.....

Dave Morrison SSCrazy Points: 2077 More actions · Answer 7

Ah, so I was on kid of the correct lines with where you'd put the data then 🙂

No worries, glad you sorted it and glad if you learnt anything from my blog too 🙂

Cheers

Dave

sharada.mn8 Say Hey Kid Points: 674 More actions · Answer 8

You are right.. That's what made me think in that perspective 🙂