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!