SSRS Select permission was denied on the object

Note to self: When I create a SSRS report for CRM, always use "filtered" views in the query for the report - never use the other tables or views. Here is the situation:

I had created a report for use in an Iframe of CRM, and it was getting the error "Select permission was denied on the object ..." and it named one of my tables in CRM. The query ran just fine when I was logged in as the administrator, but when the users got it, it gave me that error. Permissions for objects (entities) in MS CRM are applied to the filtered views in order to enforce access controls to the data. When you view a CRM entity form and the browser tries to render your report in the Iframe, SSRS is using your network credentials to determine which records you are allowed to see.

Dont try to take a shortcut and use base entities or non-filtered views when creating a report - you will be wasting your time. If your database is optimized properly, the report should run just as fast when using the filtered views.

SSRS query execution failed for dataset

I had written a nifty new report that I was implementing for a client in CRM 4.0 to be displayed in an Iframe in the Account entity form. After uploading the report using SSRS report manager (http://server_name/reports) and customizing the account form, it worked great for me, but the users got the error "Reporting Services Error...An error has occured during report processing. Query Execution failed for dataset 'DataSource1' "

The first thing to do is turn on the "Enable Remote Errors" feature of SSRS.
1. Open SQL Management Studio and connect to your instance of Reporting Services
2. Right click on the server name in the object explorer and choose properties
3. Click on the Advanced option (on left side) then change EnableRemoteErrors to True

Open your browser and re-run the report to see the actual error you are getting.

SSRS security with CRM 4.0

So I am knocking my brans out trying to add a couple security groups to a custom report folder I created on SSRS and the answer is deceivingly simple.

The problem was that after I created the folder, it needed permissions for a couple additional groups: ReportingGroup and SQLAccessGroup. when I typed one of those names in the New Role Assignment for my folder security, it said the group did not exist. The solution is to copy the group name _and_ the GUID that follows it and paste it into the new group name field.

CRM Auto Filtering

I have been reading up on MS CRM auto filtering which allows you to create a report that you can associate with an entity and then use it on a view or a single record (http://blogs.javista.com/2009/03/18/microsoft-dynamics-crm-pre-filtering-tips/). I started creating some reports this way, but then decided that I also wanted to call the reports programatically and for that I would need parameters. My solution is to make my actual report work with parameters, then create a 'wrapper' report that uses the auto filtering capability, then calls the real report as a sub-report.