Advanced FetchXML SSRS reports for Dynamics CRM

Using the report wizard somebody can create quite useful reports, but as soon as the requirements go just beyond the capabilities of this wizard, one will need to take that report and start modifying the report in SSRS. As the reports are built with FetchXML, this is another topic to think about.

Luckily there are good tutorials online on how to create and author FetchXML based reports. Some of the links that I found particularly interesting are mentioned below. Based on these I have been able to create reports existing out of half a dozen data sets (yes, all FetchXML) and pulling data from each of the datasets to show in the Report Wizard generated report.

Gareth Tucker's "Getting Started with CRM 2011 Online Fetch XML Reporting"

The Microsoft Dynamics CRM Blog's "Fetch-Xml based Reports: Bits & Pieces"

As mentioned in the Microsoft blog, you can hook up multiple datasets. Another approach would be to pull data from another data source. Have a look at the Lookup and LookupSet Functions for this. Note, that perfectly combines with the FetchXML aggregation features.

Good luck with the next level of reporting!



Microsoft Online Services Sign-in Assistant: Download failed

When installing the Microsoft Dynamics CRM Reporting Authoring Extension, on the machine where BIDS is installed, 9 out of 10 times the SQL Server, you might be facing a problem around the prerequisites. This software requires the following components:
- Microsoft Application Error Reporting
- Windows Live ID Sign-in Assistant
- Microsoft Online Services Sign-in Assistant

In some occasions it is not possible to get past this screen since the last one of the requirements is showing the status "Download failed". Looking in the BIDSExtensionsSetup log file (C:\Users\administrator.DEV\AppData\Roaming\Microsoft\MSCRM\Logs), you will see:

08:48:37|  Error| Microsoft Online Services Sign-in Assistant did not download successfully.  To try again, ensure your internet connection is working, then try again.
08:48:37|  Error|   hr=0xc0040202

This error is pretty easy to fix. After double checking that you are actually online, you can download the Microsoft Online Services Sign-in Assistant directly from the Microsoft Website. Make sure to download the 64 bit version and install this on the BIDS machine.

When running the Report Authoring Extensions Setup again, you'll see that this requirement is not even showing up in the list anymore and you can safely install the other two required components.



Can't remove licenses in Office365 yourself? Yes, you can!

So you've figured out which users are not active in Office365 anymore. You've removed their licenses and deleted the account, so you've got a license available for reassignment to another user. But what if there is no other user and you want to stop paying for the license? Most blogs say that you'll need to create a Microsoft support call, but this actually is not necessary. You can do this yourself by following the next steps:

- Open the office365 administration portal
- In the left navigation go to "Billing"
- The pick "Subscriptions"
- Next is clicking on the link for the subscription where you wish to return licenses for
- Under the text indicating the amount of users, there's a link "Change quantity"
- Pick "Remove licenses" and click "Next"
- Enter the amount of licenses you wish to remain (you can't do this wrong, the system won't let you remove licenses that are still assigned)
- Press "Place Order"

Good luck!




Checking active users in Office365

When an administrator logs in on Office365 a dashboard tells the user that there are x amount of users who haven't logged for more than 30 days. Clicking on this link allows you to view the information in graphs, tables, large graphs, but no way it shows you the names of the inactive users.

Venus Mi has given the answer to this in his forum post, but refers to another page as well for connecting to Office365 via Powershell. Following these instructions will lead you to the following error:
cannot be loaded because the execution of scripts is disabled on this system. Please see "get-
help about_signing" for more details.

Luckily this can be solved as well, thanks to the powershell owners manual. Finally you'll run in the last error which is answered by spiritX:
Access to the registry key 'HKEY_LOCAL_MACHINE\SOFTWARE\M icrosoft\PowerShell\1\ShellIds\Microsoft.PowerShell' is denied

So, how can one find out which office365 users haven't logged in recently?
- start Powershell AS ADMINISTRATOR (right click and "run as administrator")
- Execute the following script:

Set-ExecutionPolicy RemoteSigned
$LiveCred = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri
https://ps.outlook.com/powershell/ -Credential $LiveCred -Authentication Basic -AllowRedirection
Import-PSSession $Session
Get-mailbox -resultsize unlimited| Get-MailboxStatistics | select displayname, lastlogontime
Remove-PSSession $Session

This will give you a popup where you should enter your Office365 credentials. After this you'll get a list of all users with their last logon date.

Enjoy!



CRM Blog World Updated

One of the most viewed pages on my blog is still the Blog World. When Dynamics CRM wasn't called Dynamics CRM yet, I have started to gather a list of blogs which targeted this software. Over the years this list has grown and I have just updated the list with the blogs which I have been requested to add. I guess soon I'll need to do a full review of this list as I've noticed that some people have moved on and stopped blogging and new people have stepped in these gaps to fill the missing info.

For now the list is updated and available here:
CRM Blog world



Quick Yammer & CRM Demo

Most, if not all of you, know that Microsoft has acquired Yammer in June 2012. Now Microsoft has released the integration between Yammer and Dynamics CRM as part of the Polaris release (formerly known as December 2012 Service Update). However, in order to demo this, you’ll need to sign up for a Yammer Enterprise account*. If you don't have such an account, then the integration will not work as expected.

But! There is a very easy way to incorporate some of the functionality of Yammer into CRM without having a Yammer Enterprise account. You can go to any group on Yammer and incorporate the feed of that group as a web resource and include this on any page in CRM. To do this, follow these steps:

1. Go to the Yammer website
2. Create or select a group in the left column
3. In the right side click "</> Embed This Feed"
4. Copy the code
5. Create an HTML file with the copied code pasted between <html><body> and </body></html>
6. Upload the HTML file as a web resource to CRM
7. Add the web resource to a CRM form

And that's it. You now have a live Yammer feed inside CRM. Though it doesn't show the collaboration on account or opportunity level, in a demo the could be just enough. If not? Then get yourself a Yammer Enterprise account and configure the integration between both products.



It's been a while

At the moment I'm at the MVP Summit spending time with a bunch of great minds in the industry. One of the guys made me realize that it's been a while since my last post and asked if I could pick up posting blogs again. As my role has changed over time, I'm not spending too much time on coding anymore. There are a bunch of guys out there which are way more technical than I am, so I'm handing over the baton to fellow MVP's and anybody out there in the community. I do suggest you to post the challenges that you find and especially the solutions to these challenges. Your solutions will help hundreds of other users fix their problems even years after the post. Based on the feedback I got this week, I can guarantee that people will be eternally grateful!

That said, I am finding myself in many interesting scenario's which require me to think of creative solutions around CRM after all. Not around coding so much, but around solution architecture, effective demos and related items. With that, I'll start posting again items regarding these, hopefully helping you become more successful once again.

Cheers!
Ronald