Topic: ‘SSRS Reports’

For simple exports to Microsoft Excel, you can use the Advanced Find functionality to find the records, and then export. But keep in mind that you can only get one data set per Excel file. What about when the requirement is to export data from CRM to a multiple-tabbed Excel file where each tab is a different data set? Read the rest of this entry »

I recently deployed an SRS Dashboard into an environment that had some interesting restrictions. Users inside the network could access through the usual http://crmserver:5555/ URL. However, users outside of the DMZ had to access CRM through a special URL, such as http://crm.companyname.com:5555. The problem with this is that the URL that you place in the sitemap to point to the dashboard report is static. To get around this and account for the different access methods we did the following.

Note: This post is contingent upon already knowing how to display a Dashboard as an option on the Navigation Bar or as your home page.

1)      Get the URL of the Dashboard report you are referencing. To do this, run the dashboard report from the reports area of CRM. Once rendered, hit CTRL-N to open the address bar. Copy the contents of the address bar. It will look something like this:

http://CRMORGNAME:5555/crmreports/viewer/viewer.aspx?id={4821DECB-EDA9-DE11-A944-000C291643D3}&helpID=SALES%20DASHBOARD.rdl&action=filter

2)      Open Notepad and paste the following text into it. What this does is examine what URL is being invoked and uses that specific URL. Be sure to note the following:

1. In the strReport variable the port is stripped out and only the organization name is used. The rest is exactly what you copied in step 1.
2. Replace any text that is in all caps with your deployment specific information.

<%@ Page AutoEventWireup=”true”  %>

 <%

            string strReport = “/CRMORGNAME/crmreports/viewer/viewer.aspx?id={4821DECB-EDA9-DE11-A944-000C291643D3}&helpID=SALES%20DASHBOARD.rdl&action=filter”;

            string strHost = Request.Url.Host;

            string strUrl = “”;

            if (strHost.ToLower() == “CRM.COMPANYNAME.COM”)

            {

                //Response.Redirect(strUrl + strReport);

                strUrl = “http:// CRM.COMPANYNAME.COM ” + strReport;

            }

            else

            {

                //Response.Redirect(“http://crmserver:5555″ + strUrl);

                strUrl = ” http://crmserver:5555″ + strReport;

            }

            Response.Redirect(strUrl);

        %>

3)      Save this file as DashboardRedirect.aspx in the \ISV folder underneath the installation folder of your CRM deployment.

4)      Export the Sitemap.xml from CRM.

5)      Open the file in a text editor.

6)      Find where you want to have the Dashboard link displayed on the navigation bar and paste the following text:

          <SubArea Url=”/ISV/DashboardRedirect.aspx” Icon=”/_imgs/ico_16_9102.gif” Title=”Sales Dashboard” />

7)      Save the Sitemap.xml and import back into CRM.

Now, when clicking on the Sales Dashboard link from the navigation pane, the redirect page will be called and the correct URL for the dashboard report will be invoked.

There’s an interesting post on the MSCRM Team Blog by Inna Agranov that verbalizes how to pre-filter your data in CRM, allowing you to query results much quicker. The post also contains a link to another article, located in the MSDN library. It describes in more detail how to filter CRM reports through SQL Reporting Services.

Click here to go to the Team Blog and read the post, or click here to go directly to the MSDN article.

I was checking out the MSCRM Team Blog today, and I found a new post surrounding the CRM Data Connector. It includes information about what the Data Connector is, how it’s used, and a common issue that can emerge when utilizing the connector – definitely an informative read. Access it on the MSCRM Team Blog site by clicking here.

When developing SQL Reporting Services Reports, you surely are aware of the checkbox to allow the “Page break at start” option when grouping data. However, you cannot allow a user to toggle whether or not they want a page break after each group. I didn’t like the idea of creating a second report that had the Page break option checked, so here is another solution starting with an existing report.

Our starting point is a simple Contact report that groups Contacts by the Account name. The initial report layout would look something like the one below. Please note that the group we currently have does not have the “Page break at start” option checked.

The first thing we need to do is to create a report parameter for the user to set as they please. Click the Report menu and select Report Parameters. Create a parameter similar to the one below. Click the Add button and name it “PageBreak.” Set the Data type to “Boolean” and enter a Prompt of “Put a Page Break after each group?” Set the Default Values to “Non-queried” and type in “False” without any punctuation. Click OK.

Next, we need to add another group above our existing group. This group will contain a formula based on our parameter and display the page break or not. Right click the left most cell of the header row (left of the cell showing FullName) and choose Insert Group. Enter values as in the screen shot below.

Name it PageBreak, Expression value is: =IIF(Parameters!PageBreak.Value,Fields!SomeField.Value,”")

Make sure to check the “Page break at start” checkbox. Lastly, click the Sorting tab and be sure to sort on Account Name (=Fields!accountidname.Value). This should be the same sorting as on the original group in your report.

Your report should now look like the screen shot below. The new group is highlighted.

Now, preview your report. You will see your Page Break parameter at the top of the report defaulted to false. Take a look at the page count.

Now, change the parameter to True, and click View Report. You will notice the page count will increase quite a bit.

That will do it.