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.

Adobe Flex Grid Column Sort

Lately I have been doing some work with Adobe Flex because it has some very nice data manipulation features, such as the DataGrid. One item that came to my attention was that when I sorted a column, I wanted the grid to jump to the top of the data set.

Here is my solution:

1. add a "headerRelase" attribute to the DataGrid tag that will call an event handler

<mx:DataGrid id="ratesGrid" headerRelease="ratesGrid_headerRelease(event)" >

2. create the new event handler

private function ratesGrid_headerRelease(evt:DataGridEvent):void {
ratesGrid.validateNow(); // allow sort to occur
ratesGrid.selectedIndex=-1; // turn off selected/highlighted row
ratesGrid.scrollToIndex(0); // move to top of data set
}

Need more women in IT management roles

When someone told me a few years ago that Microsoft SharePoint and CRM were disruptive technologies, I was a bit hesitant to believe it, but I can see it now.

Historically many computer jobs grew out of the engineering fields dominated by men such as computer science. According to some recent polls (http://www.ncwit.org/pdf/BytheNumbers09.pdf), women only occupy 24% of jobs in the IT sector. I believe that this statistic will be changing for the better.

The "disruptive" part of these technologies is that it is changing how IT departments (and consultants like me) will be supporting the clients. In order to make implementations work, it now requires people to have good business process and people skills as compared with the “days of yore” when programming skills were central to an implementation. If you think of an IT worker as just a programmer, then you need to re-think how you are managing your IT services.

Since I have been working with clients on implementing Microsoft CRM and SharePoint technologies, the majority of people I have worked with were women, and all of their implementations have been successful. In the end, my clients were able to extend the usefulness of the product without having to engage other IT resources.

The pre-registration statistics at the June 2009 SUGDC.ORG SharePoint conference indicates the workforce is changing with over 40% of the attendees to be women. I believe this is because these two products (and others like them) give the client the ability to customize the product to fit a particular business process without the help of typical IT staff, and thus giving women more opportunities to engage in the implementation process and join the ranks of IT professionals.

MS CRM Address Sync Workflow

In Microsoft CRM, you can have many contacts connected to a single account. When you initially create the contact, it takes the address from the account record, but the user has the option to change the address. This is a nice feature because it supports companies that have employees who are not all located in the same building. But what happens when you change the account address? All those contacts end up staying at the old address. You could manually change them, but there is no need because you can automate this process. The Microsoft CRM Team blog has some suggestions to remedy this problem, but it requires building a custom program (which they provide) and installing it on the CRM server. This could be problematic for users of CRM On-line, so I have a solution using the workflow tool.

In the Contact entity, create a couple new attributes: one will be called “Sync with Account Address” as a bit field (yes/no), and the other is wfDateOfAddressSync (Date & Time). Allow me to digress on the name of the second attribute. I use the “wf” in front of attributes that will not be visible on a form, but are used by workflow to control actions. This makes it easy for me to distinguish them from user attributes, and serves as a reminder so that in the future I don’t delete it because it appears to be unused. Place the new bit attribute on the form in a conspicuous location in the address section and publish the changes.

In the Account entity, create a new field attribute wfDateOfAddressChange (Date & Time) and publish the changes.

Now create a workflow called Trigger Address Sync for the Account entity. This workflow should execute when a new Account is created, or when one of the address fields changes. It will update the account record and place the Workflow Execution time in the wfDateOfAddressChange attribute. Make sure the workflow Scope is set to Organization and Publish it.

Next, create a workflow for the Contact entity. Set this one up to “Start When” the a Contact is created, or if either the “Sync with Account Address” attribute or wfDateOfAddressSync attribute changes. Now add a condition to only execute the rest of the workflow if the Sync with Account Address attribute is Yes, then add a timeout condition that will wait for the wfDateOfAddressSync on the Account entity to not be equal to the Contact entities wfDateOfAddressChange attribute. The final step is to update the contacts’ address fields to be the same as the Parent (Account) address fields, and update the wfDateOfAddressSync attribute to take on the value of the Parent (Account) wfDateOfAddressChange attribute. This last item will create a loop by triggering this workflow to start again (by changing the date attribute) and wait for the next Account address change. Make sure the workflow Scope is set to Organization and Publish it.

Remember, this new Contact workflow will not start synchronizing until they have either been manually applied to existing contacts, or someone updates one of the Start When fields (mentioned above) fields.

Now go test it and have fun!