Posted by I recently modified a report that I downloaded from CRM and began customizing in BIDS. One of the immediate inconveniences that I encountered was that pesky error that says “The Declare cursor SQL construct or statement is not supported.” when you choose the DSMain dataset in the “Data” tab. This error occurs since CRM is using dynamic SQL to generate a select statement. It does this because the CRM filters send their results to a temptable and the name of this temptable is then passed to SSRS as a parameter. Since the name of the temptable is unavailable until the filter is processed the SQL query can’t be generated until runtime. The result of this dynamic SQL is the error that you receive when you choose the DSMain in the “Data” tab.
The Solution:Don’t modify the DSMain. In fact, don’t touch it at all; make sure that you never select it when you go to the data tab since once it’s selected you will be unable to import the report back into CRM. Instead of changing the DSMain take the following steps:
1) Create a report parameter called @IDFromMain. Check the boxes: “Hidden”, “Multi-value”, “Allow blank value”. In the available values choose: “From query” and for Dataset choose “DSMain” and in the value field choose the primary key from DSMain. In the Default values choose: “From query”, choose the same dataset and value field that you did above.
2) Create a new Dataset in the “Data” tab. Create your query to grab the fields that you were considering adding to the DSMain. If you want to get the fields that were in the DSMain then just right click the project and choose “view code”- grab the SQL statement for DSMain out of the XML file and paste it into your new dataset. Feel free to join additional views (use the filtered views!!!!) to get the data you need.
3) In the where statement of the query add “AND ActivityID IN (@IDFromMain)”. Replace ActivityID with the primary key that is being returned by DSMain. Now you can use this dataset for your reports instead of using DSMain.
