Chris Dobkins's blog

Creating Dynamics CRM 2011 Reports in SQL Reporting Services with Pre-Filtering and Default Filters

With the release of Microsoft Dynamics CRM 2011 and the rapid proliferation of through cloud deployments, Microsoft had to make some changes to how SQL Reporting Services worked to make it possible to write SRS Reports without physical database access that could be easily published to either On Premise or Through Cloud deployments of Dynamics CRM 2011. They accomplished that with the release of the Dynamics CRM 2011 Report Authoring Extension--a plug in to SQL Business Development Studio that allows you to select FethXML as a data source when authoring a report in SQL Server Reporting Services.

In this blog post, I am going to walk you through creating an SRS Report and publishing that report to Dynamics CRM 2011. I am also going to show you how to enable the report to use Pre-Filtering and Default Filters. What you will see here will work with On Premise and Through Cloud deployments of Dynamics CRM 2011. In my environment, I will be connecting to a Dynamics CRM 2011 Organization in the NjevityToGo CRM Cloud. 

Before we begin, lets talk a little about Pre-Filtering and Default Filters so you have a good understanding of why this is important to your report design.

 

Pre-Filtering

Have you ever run a report in Dynamics CRM 2011 and been prompted to select whether you want the report to use All Applicable Records, The Selected Records, or All Records on all pages in the current view? Or have you opened the Account Form for a specific account and run a report from the Account Ribbon that only displays data for the account you had opened? In both of these cases, you are using Pre-Filtering. 

Pre-Filtering gives you the ability to supply one or more selected records as parameters to the report. This allows the user to select one or more records within the Dynamics CRM 2011 Application and run a report just for those records. It is a much more intuitive way to supply a parameter to the report than using visible paramters on the report. This should be a basic requirement in most of the reports you author for Dynamics CRM 2011.

 

Default Filters

When you run a report in Dynamics CRM 2011 that uses Default Filters, there will be an Edit Filters button in the top left corner of the report. Additionally, if you double click a report in the Report List that uses Default Filters, the Default Filters window will open before you are able to run the report. The Report Filtering Criteria window allows you to apply Advanced Find filter criteria to the report using all of fields and relationships on the entities you are reporting against...regardless as to whether or not those fields and relationships exist in the report.

This is a fantastic way for users to supply parameters to reports. It uses the Advanced Find Query Structures, so it is familiar to the users. And it does not require the report designer to know about every possible filter the user might want to use so they can be part of the report. The user can provide default filters and save them into the report so they are used everytime the report runs. And the user can change the filters at any time. The user can even add new filters that were never part of the original design.

I just wrote a report where the requirement was for the report to always show 'This Month's' data. But sometimes they want to be able to change it to show 'last month's' data. Now I could have put a FROM and TO date parameter into the report and created some functions to default them to the beginning and end of the current month. But that is quite a bit of work. And tomorrow, the requirement could have changed to use the Modified Date instead of the Created On date as the basis of the filter. Or another filter could have been added. In the old way of working with parameters, any of these changes would have required additional report design work. But with using Default Filters, I was able to write the report without any parameters, and then setup the default filters using standard Dynamics CRM 2011 capabillities. And the user can change this at any time on their own.

In the following example, I will show you how to do this. So, lets get started.

 

Authoring a Dynamics CRM 2011 Report that uses Pre-Filtering and Default Filters

Before we being, you will need the following to author and publish a report for Dynamics CRM 2011:

  1. You will need access to a Dynamics CRM 2011 Organization and have the proper security roles to publish reports. This includes:
    • Organization URL
    • Organization Unique Name (Settings >> Customization >> Developer Resources)
    • UserName and Password for that organization
  2. You will need to have SQL Server Business Intelligence Development Studio Installed with the SQL Reporting Services components. (I am using version 2008 R2)
  3. You will need to download and install the Dynamics CRM 2011 Report Authoring Extension. You can download it here.
First, we will need to open the Business Intelligence Development Studio and create a new project using the Business Intelligence Project Type and the Report Server Project Template.
Next, we will create the report. To do this, right click on the Reports folder in the Solution Explorer and select Add >> New Item (do not choose Add New Report as this will create the report with the Report Wizard). 
From the Add New Item window, select the Report Template, give your Report a name (I am using Account Report with PreFiltering) and click ADD.
Next, we need to create the data source for the report. With Dynamics CRM 2011 Reports, you should use a Data Source embedded in the report, not a shared data source. To do this, in the Report Data pane on the left side of Visual Studio, right click on the Data Sources Folder and choose, Add Data Source.
In the Data Source Properties window:
  1. Give the Data Source a Name (I am using CRMData)
  2. Select Embedded Connection of Type: Microsoft Dynamics CRM Fetch
  3. In the connection string box, enter the url of your CRM Organization folowed by a semicolan and your organization unique name. You should be able to copy and paste the first part of this from your web browser. I am using https://test01.crm.njevitytogo.com;test01
  4. Click the Credentials tab
  5. Select 'Use this user name and password' and enter the user credentials that you use when connecting to your CRM Organization.
  6. Then click OK
Next, we need to create the dataset for the report. Because we chose to use CRM Fetch in the previous step, we will be using FetchXML as the query language. Fortunately, for those of us who do not know how to write FetchXML queries, there is a very simple way to do this: we will let Advanced Find create the FetchXML for us!
To do this, we will create an Advanced Find that contains the columns we want on our report as well as the filters that we want always applied to the report. For this example, we will create an advanced find that returns ACTIVE Accounts and displays a few relevant columns. So, in Dynamics CRM 2011, navigate to an account list and click the Advanced Find button.
Because I had the Active Accounts view displayed on the Account List when I opened Advanced Find, the Advanced Find query automatically opens with the Status = Active filter applied. Because we ONLY want active accounts and we do not want the user to be able to edit this filter, we will keep this filter in place. We could add additional filters here, just keep in mind that the users cannot change these filters when they run the report
Next we will select the columns that we want to work with on our report. To do this, click the Edit Columns button and add and remove columns until you have the ones that you want on the report. The order of the columns in the Advanced Find is not relevant.
Click the OK button to close the Edit Columns window and return to the Advanced Find window. At this point, you might want to run the Advanced Find to make sure that the data set looks right. Once you are comfortable with the dataset, click the Download Fetch XML button in the Advanced Find Ribbon. This will download an xml file that contains the FetchXML query we just created. Save this on your computer.
Then locate the file, right click and open the file in Visual Studio. It should look like the file below. Now, add the enableprefiltering parameter and set its value to "1" as indicated below. This will enable Pre-Filtering and Default Filters on the report. If you do not add this parameter, you can still run the report in Dynamics CRM 2011, but you will not be able to apply filters to it in Dynamics CRM 2011.
Next, copy the xml to your clipboard and return to the report in Visual Studio. Right click the Datasets folder in the Report Data pane on the left side of the Visual Studio window and select Add Dataset.
On the Dataset Properties window:
  1. Give your dataset a name. I am calling mine AccountData. 
  2. Select the radio button labeled 'Use a dataset embedded in my report'.
  3. In the Data Source drop down, select the data source you created in a previous step.
  4. Click the Query Designer button
In the text box at the top of the query designer, paste the FetchXML you copied from the XML File. Then click the Red Exclamation Point button to execute the query. When the Define Query Parameters window opens, leave the parameter value for the CRM_account parameter blank and click OK. This will return the same set of records that were in the advanced find query.
If you are happy with the results, click OK to close the Query Designer, then click OK to close the Dataset Properties window. If you expand the Dataset, you should see the list of fields just like below. Next, we need to edit the Parameter that was automatically created for the Pre-Filtering. In this case, the parameter is called CRM_account. To edit this, right click the parameter and select Parameter Properties.
On the Report Parameter Properties window, select the radio button labeled 'Internal'. This will prevent the user from being prompted for a value for this parameter when they run the report. Since we are getting this paramater value directly from CRM, there is no need for the user to interact with this parameter. Click OK to close this window.
At this point, you can design this report just like you would any other SRS Report. In this example, I have added a title and table to the report and put a few of the fields into the table. When you are satisfied with the report, save your changes. 
Now, we need to upload the report to Dynamics CRM 2011. To do this, we will log in to Dynamics CRM 2011 and navigate to the Report List (Workplace >> Reports) and click the NEW button.
From the new report window:
  1. Report Type: Existing File
  2. File Location: Navigate to the RDL file that you created in Visual Studio. Mine is called 'Account Report with PreFiltering.RDL'
  3. Name: This defaults from the RDL file name, but you can change it if you wish
  4. Related Record Types: This is the CRM Entity the report is linked to. In this case, Accounts
  5. Display In: Select:
    • Reports Area
    • Forms for related record types
    • Lists for related record types
Once this is done, click SAVE AND CLOSE to upload and save the report to Dynamics CRM 2011.
Now, lets go run the report. Navigate to the Account List. Select a few accounts and click the Run Report button in the Account Ribbon. You should see the Account Report with PreFiltering report in the list of reports. Click it.
Choose to run the report on the selected records.
As you can see, the only Accounts that are included on the report are the ones that you selected.
This is a successful implementation of Pre-Filtering on the report.
Now, lets look at setting a default filter on the report. To do this, return to the Reports List (Workplace >> Reports) and find the Account Report with PreFiltering report on the list. Select it and click the EDIT DEFAULT FILTER button in the Reports List Ribbon.
On the Report Filter Criteria window, enter your filters just like you would in Advanced Find. Notice that you have access to every field and every relationship in CRM--not just the ones included in the report. In this case, I am selecting to only display Accounts that were created This Month and are in the City of Redmond. Click the SAVE DEFAULT FILTER button to save this as the default filter on the report. When you do this, this will be the filter applied by default when you run the report from the Reports List or from the Account List when you select to run the report on applicable records. If you run the report on the selected records as we did above, that filter will replace the default filter.
Users may change the filter each time they run the report by clicking the Edit FILTER button in the top left corner of the report. 
This is how you can create a report and allow the users to specify any filters (parameters) when they run the report. And as you can see, making these changes does not require any report development work. It can all be done by the end user. Simple. Easy. Hard not to love it!

Learn how to eliminate GL to Sub-Leger Reconciliation in Dynamics GP

On Wednesday, February 29 at 10am Mountain Time, we will be hosting a Webinar with Reporting-Central to review two of their award winning products:

  1. The Closer is an add-on solution for Dynamics GP that automates the process of reconciling the various sub-ledgers to the General Ledger. With The Closer, month-end and year-end processes are completely streamlined and your books will balance to the penny faster than you ever thought possible. 
  2. The Validator will ensure that your Dynamics GP system is optimized and maintained to reduce overall business risk. From automatically validating TIN’s for faster processing of 1099’s to running a 100 plus point inspection of your Dynamics GP System Set-Up and Master Data Files, you will pre-emptively be able to find issues, errors and potential set-up problems that are impacting the performance and data integrity of Dynamics GP environment.
If you use Dynamics GP, you will not want to miss this! You can register here or if you cannot make it, check out our On Demand Library after the event.

The Cloud is for large companies with a full time IT staff, too

in

I met with a very large Financial Services firm today to talk about helping them move from a tier 1 ERP product to Dynamics GP. You might think that odd...going backwards and all...but it happens much more than you migth think. After all, most companies can buy and implement Dynamics GP for a fraction of one year's annual maintenence with their Tier 1 publisher. And these guys said that they "needed a hammer but bought a jack hammer"...so their Tier 1 product is really much more than they need.

Anyhow, these guys are a pretty good sized company and they have a full time, dedicated IT staff to support their user community. They had attended our webinar series on The Cloud and are very interested in evaluating our cloud solution for Dynamics GP as well as the traditional On Premise option. Now, what is a company with plenty of financial resources and a healthy IT staff doing looking at The Cloud, you might ask.

Well, these guys are being asked to do more with the same IT resources, and they are getting stretched. So, the idea of letting someone else (us) manage the infrastructure for the ERP systems is pretty attractive. Plus, they not only don't have to support the new Dynamics GP system, but they also no longer need to support the old Tier 1 system. So, their IT team will free up some bandwidth (or maybe just stop working EVERY Saturday).

But in addition to using The Cloud to free up their IT resources, they are also quite interested because going through cloud means that they don't have to worry about the infrastructure for their ERP system. They don't have to worry about backups. They don't have to worry about maintenance, service packs, hot fixes. They just don't have to worry about any of it. They just plug in, turn on and get to work. Pretty simple. And who doesn't like simple?

 

Dynamics CRM 2011 and SharePoint Online - Installing the List Component for Document Management

After completing our transition to Office 365, I was very excited to start using the integration between Microsoft Dynamics CRM 2011 (in the NjevityToGo cloud) with the SharePoint Online piece of Office 365 (in the Microsoft Cloud). This is a fantastic feature of Dynamics CRM and SharePoint that allows you to manage documents for an account directly within a SharePoint Site. You can add/update/view documents in either CRM or SharePoint and you can take advantage of the powerful search capabilities of SharePoint. Here is a great blog article that explains how this all works.

However, as I started working on setting this up, I realized that all of the documentation on installing the SharePoint List Component assumes that you are using SharePoint ON PREMISE, not SharePoint Online. And, the instructions for performing the installation DO NOT WORK with SharePoint Online. AND most of the articles in the blogosphere state that SharePoint Online is not compatible with the Dynamics CRM 2011 List Component or Document Management features.

I am happy to report, however, that these articles are out of date. In November 2011, Microsoft released an update to SharePoint Online that allows it to work with Dynamics CRM 2011. This blog post from the Dynamics CRM Blog explains how to install the Dynamics CRM 2011 list component with SharePoint Online. Here is another blog post from the Office 365 Team on how to get this working.

I followed these instructions and am thrilled to report that IT ALL WORKS! I have this up and running right now with our NjevityToGo Cloud deployment of Dynamics CRM 2011 (using the November 2011 Service Update) in our cloud and SharePoint Online in the Office 365 Cloud. 

In a nut shell, you can do the following:

 

  1. Download the Microsoft Dynamics CRM 2011 List Component for Microsoft SharePoint Server 2010
  2. Disregard the instructions provided with the download (use the following instructions instead) 

 

Install the Microsoft Dynamics CRM List Component Solution to SharePoint Online

  1. Navigate to the folder where you downloaded CRM2011-SharePointList-ENU-amd64.exe, and double-click it. The install will extract the files mentioned below AND THAT IS ALL. It might as well be a .zip file. There is no real installation here. So, you can run this on a 32-bit or 64-bit machine. It matters not.
  2. In the Open File - Security Warning dialog box, click Run.
  3. To accept the license agreement, click Yes.
  4. Select a folder to store the extracted files, and click OK.
    The following files are extracted:

AllowHtcExtn.ps1 (NOT required for SharePoint Online) 

crmlistcomponent.wsp

  1. Open your browser.
  2. In the address bar, type the URL of the site collection on which you want to install the Microsoft Dynamics CRM List component.
  3. Click Site Actions, and then click Site Settings.
  4. Under Galleries, click Solutions.
  5. On the Solutions tab, in the New group, click Upload Solution.
    Click Browse, locate the crmlistcomponent.wsp file, and then click OK.
  6. On the Solutions tab, in the Commands group, click Activate.

Configure Data Management in Microsoft Dynamics CRM Online

  1. Navigate to Settings and click on Document Management in the left Navigation bar
  2. Click On Document Management Settings
  3. Select the CRM entities to enable for Document Management and then enter your SharePoint Online URL, click Next
  4. Select how you would like the Document Libraries organized in SharePoint Online (Account – B2B or Contact – B2C), click Next
  5. Document Libraries will now be created in SharePoint Online to support the hierarchy selected in the previous step, click Finish

Document Management with SharePoint Online is now configured within Microsoft Dynamics CRM. When a Microsoft Dynamics CRM user click on Documents on the CRM form, the List Component will look for a corresponding Document Library and if one does not exist create one.





Setting up an Android Phone with Microsoft Office 365

To setup your Android Phone with your existing Office 365 email account using Exchange ActiveSync, follow these steps:

  1. From the Applications menu, select Email. This application may be named Mail on some versions of Android.
  2. Type your full e-mail address, for example tony@contoso.com, and your password, and then select Next
  3. Select Exchange account. This option may be named Exchange ActiveSync on some versions of Android.
  4. Enter the following account information and select Next.
    • Domain\Username Type your full e-mail address in this box. If Domain and Username are separate text boxes in your version of Android, leave the Domain box empty and type your full e-mail address in the Username box. 
    • NOTE: On some versions of Android, you need to use the domain\username format. 
    • Password Use the password that you use to access your account. 
    • Exchange Server Use the address of your Exchange server. To find this address, see “Finding the Server Name” later in this topic. 
  5. As soon as your phone verifies the server settings, the Account Optionsscreen displays. The options available depend on the version of Android on your device. The options may include the following:
    • Email checking frequency The default value is Automatic (push). When you select this option, e-mail messages will be sent to your phone as they arrive. We recommend only selecting this option if you have an unlimited data plan.
    • Amount to synchronize This is the amount of mail you want to keep on your mobile phone. You can choose from several length options, including One day, Three days, and One week. 
    • Notify me when email arrives If you select this option, your mobile phone will notify you when you receive a new e-mail message. 
    • Sync contacts from this account If you select this option, your contacts will be synchronized between your phone and your account. 
  6. Select Next and then type a name for this account and the name you want displayed when you send e-mail to others. Select Done to complete the e-mail setup and start using your account. You may need to wait ten-to-fifteen minutes after you setup your account before you can send or receive e-mail.
 

Finding the Server Name

To determine your server name, use the following steps:

  1. Sign in to your account using Outlook Web App. 
  2. On the Outlook Web App toolbar, click Help > About.
  3. On the About page, under the External POP Settings line, use the Server name value to help you determine your server name:
  4. If the External POP Settings > Server name value includes your organization’s name, for example, pop.contoso.com, then your server name is the same as your Outlook Web App server name, without the /owa. For example, if the address you use to access Outlook Web App is https://mail.contoso.com/owa, your Exchange ActiveSync server name is mail.contoso.com.
  5. If the External POP Settings > Server name value is in the format podxxxxx.outlook.com, your Exchange ActiveSync server name is m.outlook.com. In some cases, Android mobile devices may experience connection problems using m.outlook.com as the server name. If you are having problems connecting, go to the Host name line on the About page. Use the value shown under Host name for the Exchange ActiveSync server name for your device.

What else do I need to know?

If your e-mail account is the type that requires registration, you must register it the first time you sign in to Outlook Web App. Connecting to your e-mail account through a mobile device will fail if you haven't registered your account through Outlook Web App. After you sign in to your account, sign out. Then try to connect using your mobile phone. For more information about how to sign in to your account using Outlook Web App, see How to Sign In to Outlook Web App. If you have trouble signing in, see FAQs: Sign-in and Password Issues or contact the person who manages your e-mail account.

When you use the Host name as the Exchange server name in your e-mail account settings, you should be aware that this setting may change over time. For example, the Host name for your mailbox will change if your user mailbox is moved to a different server or if it is temporarily moved during a server upgrade. 

If you are an IT professional or e-mail administrator, read the blog post Cross-site redirection of Exchange ActiveSync clients in Office 365 for detailed information about connectivity issues you may experience. 

NOTE: When I setup my phone, the instructions provided in step 3 above did not work. I tried m.outlook.com, I also tried the external pop server name (podXXXXX.outlook.com) and that did not work. What finally DID work was using the server name specified in my Outlook Web Access URL. My URL is https://snXXXXXXXX.outlook.com/. In the server box on my iPhone, I typed snXXXXXXXX.outlook.com and all of a sudden, my phone started downloading from Exchange.

We also had a few Android devices that had a checkbox called 'Verify Certificate' that was checked by default. We had to uncheck this setting to get the email account to setup.

 

Feb 13, 5:31pm - After further review: All day today we have been testing Office 365 on our mobile devices. I discovered on my iPhone that if I put in a server address of m.outlook.com, it would eventually work. It just takes about 5 minutes. After my iPhone started downloading email, I went back to the server settings and discovered that the server had been automatically changed from m.outlook.com to the snXXXXXXXX.outlook.com address I originally used. But m.outlook.com is easier to type. :)

I also noticed that if I deleted the exchange account and attempted to re-add it, the iPhone would default the server to the podxxxxx.outlook.com address that I found following the steps above. If I just left that server name in the account, it would also work. But again, when I went back to settings after the email started downloading, the server had been changed automatically to the snXXXXXXXX.outlook.com address.

These server addresses did not seem to work last night. But today they do work. It may be that we just needed a little bit more time for these addresses to propagate to wherever they need to propagate to. 

I should also note that I had originally thought that the snXXXXXXXX.outlook.com address was specific to our organization's email account on Office 365. However, we discovered today one of our employees gets directed to BLXXXXXXXX.outlook.com when they go to Outlook Web Access. And this was the only server address that would work on his phone--he could not use the address that the rest of us use. So, we cannot just send out a single server name to everyone. It appears to be mailbox dependent. But, after the rest of the testing we did, I believe that m.outlook.com would work from any of our devices.

 

Once this is done, your Android Phone should start downloading your Mail, Contacts and Calendar items from Office 365.

If you have another device, want to setup your iPhone email using POP or IMAP, or just need more help, check out this Microsoft Office 365 Mobile Phone Setup Wizard.

This applies to: Office 365 for professionals and small businesses, Office 365 for enterprises, Microsoft Exchange, Live@edu.

Syndicate content