CTE’s (Common Table Equivalent) are very useful when writing complex SQL queries. I recently used one in a dataset in a report. This was a secondary dataset that I was appending to an existing report which was already in CRM and worked correctly. Designing and testing the reports in Visual Studio worked as well. Only after uploading and running the report from within Microsoft CRM did I get an error naming the dataset as the culprit.
I suspected the CTE to be culprit as the dataset query was working fine. Thanks to a bright and esteemed colleague of mine, the answer was provided to me. A semicolon ( ; ) must be inserted before your WITH expression.
Incorrect
WITH RepIDs as (Select * FROM SystemUser …)
Correct
;WITH RepIDS as (Select * FROM SystemUser …)
I updated my dataset, saved the report to Microsoft CRM, and ran the report successfully.
Post by: Mark Weilandt, Customer Effective
