Unable to use multiple CTEs which are having counts....

Question

Unable to use multiple CTEs which are having counts....

deepthik

Mr or Mrs. 500

Points: 523
More actions
August 3, 2010 at 10:44 am

#224970

Hi, can anyone help me in this situation....
I will breif you the problem, if you need any further information pleas elet me know...
We are using more than 7 CTEs in a report. Each cte consists a calculation. When I am uploading the report, getting the following error message.
"Cannot read the next data row for the data set DataSource1."
Sample CTEs we are using...
With MinEnrolSt as
(
Select Count(IsNull(Fact.UserID,0)) as [RegCount],Act.MinCapacity as [MinCapacity],Act.ID as [Activity_ID]
From FactAttempt Fact
Inner Join dimAct Act on Act.ID = Fact.ActivityID
Where Fact.RegStatusID=0 and Act.MinCapacity >0
group by Act.ID,Act.MinCapacity
), RegCount as
(
Select count(IsNull (Fact.UserId,0)) as [Count],Fact.ActivityID as [ActivityID]
From FactAttempt Fact
inner join dimuser emp on emp.id = fact.userid
inner join dimAct act on act.id = fact.activityid
where Fact.RegStatusID=0 and emp.primaryDomfk in (@LearnerRegion)
group by Fact.ActivityID
), RegCancelCount as
(
Select count(IsNull (Fact.UserId,0)) as [CountCancel],Fact.ActivityID as [ActivityID]
From FactAttempt Fact
inner join dimuser emp on emp.id = fact.userid
inner join dimAct act on act.id = fact.activityid
where Fact.RegStatusID=1 and emp.primarydomfk in(@LearnerRegion)
group by Fact.ActivityID
),
...
When I am using only one CTE there is no problem. If i am using more than one it is displaying the above error message 🙁
thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

I use common table expressions in SSRS queries all the time and I have not seen this problem.

What do you mean by uploading the report? Deploying from BIDS to Report Manager or running from within BIDS? Does the report work in BIDS?

deepthik Mr or Mrs. 500 Points: 523 More actions · Answer 2

We need to upload the report from BIDs to report Manager. At that time am facing such a problem.

After anayzing the issue we came to this conclusion. But am not sure of the exact problem

deepthik Mr or Mrs. 500 Points: 523 More actions · Answer 3

this report is working fine in BIDs environment.

Even it uploads with no issue. But when we select single option in parameter which is of multi select, It is displaying the above mentioned error message.

Is there any limitation for CTEs, if we are using more than one CTE and performing calculations in them?

Any help appreciated.

thanks in advance.

deepthik Mr or Mrs. 500 Points: 523 More actions · Answer 4

A small correction in the above explanation...

this report is working fine in BIDs environment.

Even it uploads with no issue. But when we select single option in parameter which is of multi select, it is working fine. It is displaying the above mentioned error message when trying to select all.

Is there any limitation for CTEs, if we are using more than one CTE and performing calculations in them?

Any help appreciated.

thanks in advance.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 5

I have not used a multi valued parameter with cte in SSRS, although I don't see why it would create a problem. I would look at the area where the parameters are in your code. Are they properly set up in your code? If it is working in BIDS, it seems it should be working in the Report Manager environment. This doesn't seem like a permissions issue.

deepthik Mr or Mrs. 500 Points: 523 More actions · Answer 6

In BIDs environment everything is working properly. Only in the RM, it is giving error.

Also this multi select I have used in previous reports, they are working fine with out issuees. Now, in this report I am using counts. May be this is the problem?

But when I upload without multi select Parameter, it is working fine.

Am confused with the situation. 🙁