Create a Simple CRM Online Report in 30 Minutes or Less

This post assumes you have already installed Business Intelligence Studio (BIDS), which can be installed from the SQL media disc, CRM Report Authoring Extensions, and you have a Report project created in BIDS.

The first step you will take is to create the Report.  Using the report Wizard, right-click the Report project folder in BIDS.

image

The Report Wizard will open unless you previously selected not to use it.  Select Next

image

On the next screen give the Data Source a relevant name, select a Type = Microsoft Dynamics CRM Fetch and enter the Connection String in the following format: https://CRMOnlineURL;OrganizationUniqueName

You can find and copy the Organization Unique Name by selecting Settings, Customizations, Developer Resources

FetchConnectionString

Select the Credentials button, enter your CRM online credentials, select OK and Next

ReportCredentials

At this point your credentials are verified and the Query Designer screen will open.  One of the easiest ways to add a query to the report is to open CRM Online and build an Advanced Find query, as your base, and select the Download Fetch XML button from the Ribbon menu.

image

Select the Save file option, save the xml file to your hard drive, open the saved file in Notepad and copy and past the XML in the Query Designer screen.

image

Now that you have your base query, you can tweak it as need to pull the desired data from CRM.  Select the Next button when you are satisfied with your query, select the desired Report type and select Next.

image

Add the fields to your report and select Next

image   image

Select a Report Style and select Next

image

Give your report a Name and select Finish

You can now add any finishing touches to the report like parameters, design changes etc.  When finished with all your changes you can upload the new report to CRM and make it available to the Organization for use.

Additional Resources:

How To: Creating Custom Report with Microsoft Dynamics CRM 2011 BIDS Fetch Extension

Getting Started with CRM 2011 Online Fetch XML Reporting

Create Custom Reports Using Business Intelligence Development Studio

Cheers

So You Want To Create a SSRS Custom Report

 
I frequently come across posts in the Microsoft Dynamics Community Forums from people who have various questions regarding building and modifying custom reports for CRM.  The good news is that, yes, you can do it.  I did it and I have minimal SQL training, no formal SSRS training, and I’m not a programmer or a SQL DB Admin.  With a little reading and perseverance, I’m confident you’ll be up and running in no time. 

When I started writing reports a few years back I purchased a SQL Report Writer book.  Amazon and other online retailers have several books that you can purchase from beginning Report Writing through Programming.   Just look through their lists, read the reviews and find one that you are comfortable with.  The book I purchased was a great reference for getting started but as the custom report requests I received became more complex, I had to dig deeper for specific answers to my ‘how to’ questions.  Once again, the tech community came to my rescue.  Whether I needed to understand how to format my query, manipulate dates to get the return result I needed, use the ‘CRM_AF’ filter feature, etc., I could generally find a post or answer to a posted question that pointed me in the right direction.

Here are some of the free resources I use and suggest for getting started with writing SSRS reports for Microsoft Dynamics CRM.  They are in no particular order.

Nuts to Bolts; everything from best practices to understanding the database model, publishing reports and more.

Great article, quick read that includes all the key points to get up and running quickly.

I wrote this post.  It provides step-by-step instructions for creating a branded invoice or quote that you can upload to CRM and use for delivering these types of documents to your clients.

Terrific step-by-step article by Catherine Eibner on how to build and implement a SSRS report for use in an iFrame.

  • Download existing reports from CRM or the CRM Report Server, open them in Visual Studio

Not for the faint of heart as the default reports in CRM can be quite complex because they are written by expert report writers.

Great place to get your specific questions answered by the experts.

Join SQL groups on LinkedIn.  There are several to choose from.

There are a significant number of SQL experts on Twitter who are happy to help you with a specific question.

The above are just a few of the free resources available to you.  As you begin your journey into the world of SSRS Report Writing & Business Intelligence, I’m sure you will discover more resources and find a few favorites along the way.

One important point to remember when writing custom reports is to write your query against the Filtered Views.  There are a lot of reasons for writing against the Filtered Views rather than the base tables: data is aggregated in a friendly way so you will have significantly fewer joins, lookups are translated to their friendly names, security is applied so users will see only the data they are permitted to access, etc.  Consider the Views your friends and use them.

And, last but not least, if you are new to writing SQL queries, here is a tip that will definitely give you a ‘leg-up’ in understanding how to write queries against the CRM database. 

If you find yourself in a quandary about how to create a join, wondering which table to query for the data, which filter you should apply to generate the desired result set, etc., then………. (drum roll please) …….. if you can build an Advanced find view for what you need or at least a View that is close to what you need, you can have the system build the query for you.  Wow, did you catch that?  That’s right, CRM will generate the query for you, allow you to export the query, copy and paste it into your report, modify as needed and is available to every CRM user by leveraging the Dynamic Worksheet export feature.  Below are the steps. 

For this example, I am going to build an Advanced Find that returns all Orders created this month which includes some fields from the related Account (Customer).

  • Create your Advanced Find query in CRM
    • Open Advanced Find, select Orders from the Look For picklist, select Active Orders from the Used Saved View picklist
    • Select ‘Created on’ from the first available filter field (Select)
    • Select ‘This Month’ as the filter criteria
    • Select Edit Columns from the top menu bar
    • Select Add Columns
    • Select Account(Customer) from the list
    • Select the e-mail field to add to the result set and select OK
    • Run the query by selecting the Find button
  • Export the Data
    • Select the Excel icon from the top menu bar
    • Select Dynamic Worksheet and Export
    • image

      • Select all the options required to open the Excel worksheet and enable the data content
      • Select ‘Data’ from the top menu
      • Select Connections from the Data Ribbon

      image

      • Select Properties from the Connections window

      image

      • Select the Definitions tab from the Connections Property window

      image

      • Look in the Command text box and you will find the query
      • You can copy and paste the query into your report dataset or SQL Management Studio and use it as a base query for your report

      I generally always remove the select ‘top 10000’ record limitation from the query as this is a limitation applied through Advanced Find for performance and other reasons.  One nice discovery of this particular query is the use of the dbo.fn_BeginOfThisMonth(GetUTCDate()) function.  This is a very helpful function that you can leverage for manipulating and filtering date related data.

      The above is an excellent method for gaining a good understanding of how data is queried from the CRM database.  You will definitely learn a lot from reviewing these types of queries so I encourage you to build some complex views using Advanced Find then take a look at the queries running behind the scenes.

      For those of you who are new to SSRS or would like to find resources to help you grow your skills, I hope you find this information useful. 

      Windows Live Tags: Dynamic Worksheet,Business Intelligence,Visual Studio,Catherine Eibner,Report Writer Guide,Microsoft Dynamics CRM Forum,SSRS Custom Report,CRM_AF,LinkedIn,Twitter,GetUTCDate

      CRM Reports – DataSource

      In most environments, CRM reports uses a Shared Data Source for retrieving and viewing report data.    This data source helps to determine the type of data a CRM user can access when viewing a report. 

      There are some scenarios when the CRM System Administrator might want to override security and allow all users access to the data return for a report.  For example, some user’s may only have rights to view Account record data that they own.  The System Administrator might create a report that shows a map of all the Accounts in CRM by State, County, Zip Code, etc.  The Management Team wants all CRM users to be able to view that data ‘On Demand’ and not be restricted by Security Role conditions.

      One way to achieve the above is to modify the CRM report’s datasource.  Here are the steps:

      • Go to your SQL Server Reporting Services Home page.  The URL should be something like http://CRM_SQL_Server_Name/reports/pages/folder.aspx
      • In the top right corner select Show Details
      • Select your CRM Organization folder that contains your list of CRM reports
      • Select the 4.0 folder
      • Find the report that you want to edit
      • Select the Edit icon to the left of the report

      DataSource1 6.10

      • Select Data Sources from the left navigation menu

      DataSource2 6.10

      • After selecting the Data Source link, you’ll see that the report is using the MSCRM data source which is a shared Data Source for the CRM application

      DataSource3 6.10

      • Just below the “shared data source” option, you’ll notice a “custom data source” option.  We’ll use this option to change the data source used by the report.
      • Select the “custom data source option” from the menu
      • Select Microsoft SQL Server from the Connection Type picklist options
      • Enter “Data Source=CRM_SQLServer_Name;Initial Catalog=CRM_Org_Name”
      • In the next option, select, “Credentials stored securely in the report server”.
      • Enter a User Name like the CRM System Administrator or any other user that has full access to all the CRM data
      • Enter the User’s Password
      • Select the check to “Use as Windows credentials when connecting to the data source
      • Select Apply
      • Your Data Source Connection properties should look similar to this

      DataSource4 6.10

      Your changes should be saved and you are ready to test the report from CRM.  You can test the report by having someone who does not have security rights to the data run the report and confirm that he or she can view the data from the report as expected.

      Have a look at the links below if you would like to learn more about SQL Server Reporting Services in relationship to CRM and discover the many possibilities.

      How it Works: SQL Server Reporting Services and Dynamics CRM

      Microsoft Dynamics CRM 4.0 – Report Wizard with John O’Donnell

      Create a report in 15 minutes or less

      Dashboards Made Easy With Reporting Services

      Cheers,

      Donna

      Windows Live Tags: Reports,Data Source,Administrator,MSCRM,Initial Catalog,Report Wizard,Create,Dashboards, Edit Datasource

      Create a Custom Quote, Order or Invoice Report for your Customers

      Many organizations appreciate the ability to generate ‘branded’ Quotes, Orders, Invoices and other Customer related documents from CRM. This post will guide you through the steps required to create a report that will run on one CRM record and show you a few simple formatting tricks like how to add a logo, using page headers and footers, use sub-reports, etc. You will then be able to customize the report and generate a professional and branded document for use in your Organization or delivery to your customer base. The example in this post uses the Quote entity but any entity can be used.

      We have 3 primary objectives for the report: create a document that displays data from two tables (FilteredQuote & FiltereQuoteDetail), ensure the report runs against only one Quote record at a time, and meet the organization’s design requirements. In this case, the only design requirement we have is to add a logo to the report. Let’s get started.

      First, this post assumes that you already know how to create and upload a basic custom report, you are using CRM On-Premise or an installation where you have the ability to upload custom reports, and you have access to the tools and resources needed to generate the report format that you want.

      Open Visual Studio, this example uses VS 2005 but the same principles apply to VS 2008. Create your first report document; we will end up with two reports so let’s name the first one Customer Quote. Use the following query in the Customer Quote report:

      • select quoteid from filteredquote crmaf_filteredquote

      We’ll keep it simple for now. You can build on it later once you confirm everything is working as expected. Save this report and let’s create the second report that will serve as a sub-report to the Customer Quote report. I’ll name the sub-report Customer Quote Subreport. For now, let’s use the following query in the sub-report. You can add additional fields later.

      • select quoteid, productidname, quantity, extendedamount from filteredquotedetail

      Now we’ll add a Parameter to the sub-report;

      • Select Report from the top menu
      • Select Report Parameters

      image

      • A new dialogue window will open
      • Select the Add button from the bottom of the dialogue window to create the new parameter
      • Ensure your parameter matches the following

      image

      • Select Ok and save your changes

      Let’s add a simple table to the sub-report

      • Go to the Layout View
      • Select Table from the Toolbox and drop it onto your report layout

      image

      I added three fields to the table, gave them friendly names in the header columns, added a formula in the footer column that will display the Total Quote Amount and added some borders to the table to improve the display. The important part for testing is to add the three fields to the table. Everything else is cosmetic and can be adjusted to fit the Customer requirement. Here is a view of my sub-report table

      image

      You’ll notice a textbox in the above image. I have the visibility properties of the textbox set to not display and I copied the guid of a quote from CRM into the textbox. I plan to use the value later for testing.

      Let’s go back to the Data view of our sub-report and add the parameter to the quoteid filter column in this format @QuoteId. If you are not sure how to take that action, you can just add it to the end of your query. You query should look like this:

      • SELECT quoteid, productidname, quantity, extendedamount from FilteredQuoteDetail WHERE (quoteid = @QuoteID)

      Now let’s test the query by selecting the Run button and supplying a valid Quoteid guid to the parameter. This is where the hidden guid in the textbox comes in handy. You can copy the quoteid guid into the Value field for the parameter and your return result should be a list of products from the quote associated with the guid.

      image

      Now that we know the sub-report is working as expected, let’s go back to our Customer Quote report and add a sub-report control to the report by selecting the Subreport control from the toolbox and placing it in the report layout.

      image

      After you’ve added the sub-report control, right-click it and select properties. Let’s add the following values:

      • General Tab
        • Change the name or leave the default value, your choice
        • Select the Customer Quote Sub-report from the Subreport drop down list

      image

      • Parameters Tab
        • Select the QuoteID from the Parameter Name list
        • Select the quoteid field from the Parameter Value list and change the word Count to First
          • Since we are always going to run this report on one quote we will only have one quoteid as a value so this change should work for this report

      image

      • Select Ok and save your changes.

      That completes the steps to add the sub-report.  Next, let’s create a Page Header. We’ll use the header area to display a logo and some other information.

      • Select Report from the top menu and select Page Header

      image

      Now, let’s add a logo image to the page header. For this example I’ll embed the image in the report.

      • Select the Image control from the report toolbox and place it on your page header area

      image

      • A new dialogue window will open
      • Leave the default selection “Embedded” and select Next

      image

      • Select the New Image button and browse to the Logo file on your computer
      • Select the logo file and select Open

      QuoteReport1

      • The logo will now appear in your Report header

      I also added a few additional textbox controls, Page Footer, and a textbox in the footer area that will display the report execution date and time. I added these as examples. You can add additional fields to your query like the Account that is associated with the quote, the Sales Representative associated with the Quote and any other data you would like to display in the report. You can then display that data by adding an additional report control like a table, textbox, etc. and add the fields that you want to display to the controls.

      image

      • Save your work and we are ready to upload the reports to CRM.
      • Open your CRM application in a Web browser
      • Go to Workplace Reports
      • Select the New button
        • A new dialogue window will open
      • Select Existing File as the Report Type
      • Browse to your Customer Quote Report file
      • You can leave the default Name or change it to whatever you prefer
      • Select Quote from the Related Record Type
      • Select Forms for Related Records for the Display In
        • Do not select Reports Area as this report is designed to run on one record
      • Select Save and Close
        • After testing, and when you are ready to make the report available to the Organization, you’ll need to edit the report, select the Administration tab and change the ‘Viewable By’ from Individual to Organization

      QuoteReport2

      Now let’s upload the sub-report

      • Follow the steps above with a couple of changes
      • Select the Customer Quote Subreport
        • When you select the sub-report , the Parent Report field should automatically fill in the with the Customer Quote name. If it doesn’t, then this is a sign that something is wrong.
      • Ensure the following fields have no value as the Parent Report will run this report automatically
        • Categories
        • Related Record types
        • Display In
      • Select Save and Close

      To test the report, open a Quote in CRM and select the Report icon from the top menu. You should see the Customer Quote report under the ‘Run on Existing Record’ value. Select the Customer Quote and the report should automatically run.

      QuoteReport3

      Now for the fun part; go back to Visual Studio and add the design elements, additional fields and any other formatting changes you want to give the report a professional, branded look. You can customize both the Main and Sub-report depending on your needs. To upload and test design changes, just highlight the report from your report list in CRM and select the Edit button. Browse to the report “.rdl” file then select the Save and Close button. Be sure to select the ‘.rdl’ file and not the ‘.rdl.data’ file. Open a Quote and run the report to review your changes.

      This is one example of a method for creating a customized Quote, Order or Invoice document that you can use to deliver professional documents to your client base. There are several other methods available for use as well. Below are links to some of the available resources that you can use to find other solutions.

      Overcoming HTML field data display issues in a Report

      If you have a custom report in CRM that displays the description field of e-mails then you know that by default, the description field includes the html tags.  If you have good SQL experience then you probably already have a solution for this issue. 
       
      Although I am not a SQL novice, I’m also not a skilled professional and I don’t spend much of my work week dedicated to SQL so I always have to work a little harder than many to resolve annoying issues like this.  So here is the solution for those that need it.  All you need to do is add a function to your CRM database and call that function in your query.  Here are the details.
       
      First, test this in a development environment before applying it to a production server to ensure it works as expected.  Additionally, backup your production database before you add the function.  A copy of the function appears at the end of this post for your use.  The function was provided by Pinal Dave, which I found on his Blog, Journey to SQL Authority by Pinal Dave.
       
      The steps are provided for SQL 2005 but they are similar if not the same in SQL 2008.
       
      • Open SQL Management Studio
      • Expand your test CRM database table view
      • Expand your test CRM Org table
      • Expand Programmibility
      • Expand Functions
      • Right-Click Scalar-valued Functions
      • Select New Scalar-valued Function
        • I use Scalar in this example because I was able to create a new function in this folder.  You can use a different Function folder if you prefer.

      • Replace the default function with the function below beginning with the word Create Function and ending with the Word Go.
      • Do not replace the verbiage above the Author, Create Date or Description.  See the screen shot below for reference

      Default Function                                                                                   Replaced with New Function

                       

      • Execute the Query
      • Refresh the SQL Data Object view

      • Follow the steps above to expand the Programmability folder
      • Expand the Scalar-valued Function folder
      • Scroll to the bottom of the list
      • Right-click the function named dbo.udf_StripHTML and select Properties

       

      • Select Permissions from the left navigation link
      • Select the Add button, add the ‘CRMReaderRole’ and give the Role execute rights
      • Save your changes

      The function is now ready for use.  To use the function in a query, simply call the function in the select query.  You’ll place the function name before the field that contains the html tags that you want to remove.  The name of the function is dbo.udf_StripHTML.  Here is an example of a simple query using the function"

      • select activityid, activitytypecodename, createdon, dbo.udf_StripHTML(description), owneridname from filteredactivitypointer

      When you run the query, you’ll find that the description field for e-mail type activities now returns the field value minus the html tags, well, almost minus.  I did find that the function misses a couple of tags like ‘&nbsp’ and a few others.  I guess my next step will be to read a quick tutorial on writing SQL functions and I will see if I can modify the function to remove the few remaining html characters.  Better yet, feel free to post an improved function as a response to this post and I will be happy to use yours.

      Function Code

      CREATE FUNCTION [dbo].[udf_StripHTML]

      (@HTMLText VARCHAR(MAX))

      RETURNS VARCHAR(MAX)

      AS

      BEGIN

      DECLARE @Start INT

      DECLARE @End INT

      DECLARE @Length INT

      SET @Start = CHARINDEX(‘<‘,@HTMLText)

      SET @End = CHARINDEX(‘>’,@HTMLText,CHARINDEX(‘<‘,@HTMLText))

      SET @Length = (@End @Start) + 1

      WHILE @Start > 0

      AND @End > 0

      AND @Length > 0

      BEGIN

      SET @HTMLText = STUFF(@HTMLText,@Start,@Length,)

      SET @Start = CHARINDEX(‘<‘,@HTMLText)

      SET @End = CHARINDEX(‘>’,@HTMLText,CHARINDEX(‘<‘,@HTMLText))

      SET @Length = (@End @Start) + 1

      END

      RETURN LTRIM(RTRIM(@HTMLText))

      END

      GO

       

      SRS Case Activity Report in an IFrame

      Recently, I read a post by Microsoft CRM MVP David Jennaway.  You can view his post, Writing Reports to display in IFrames and from ISV.Config, on the Microsoft CRM Team Blog Web site.  There are quite a few posts available explaining how to view the Activity Grid from the Account or other forms, but I have not come across very many posts on displaying a SRS Report from an Iframe on the entity form so I really appreciated David taking the time to share the information.  Also, it just so happened that I recently had a request to display Case related activities on the Case form so I decided to use this method for the request.  I did encounter a couple of items that I had to troubleshoot so I thought I would share what I learned.
       
      First, create your basic SRS report using Visual Studio 2005 or 2008 depending on the version of SQL Server used for your CRM installation.  If you haven’t created a custom CRM report then you might find this a challenging place to start as the instructions assume you are familiar with creating reports in Visual Studio, using parameters and other features.
       
      To begin, I followed David’s instruction and created the report with the parameters id, name, and typename.  I also created the IFrame for the Case checked the option to pass parameters and followed the remaining instructions.  I uploaded the report to my CRM server saved it and opened a Case record.  I encountered the following error when I opened the Case record. 

      An attempt was made to set a report parameter ‘orgname’ that is not defined in this report. (rsUnknownReportParameter)

      I ran a search on the error and found a great post written by Catherine Eibner named "Building a CRM 4.0 Report that will be accessed via an iFrame".  This post has a wealth of information to include the solution I needed which was to add a few more parameters to the report.  After I added a few additional parameters (orgname, userlcid, and orglcid), saved my changes, and uploaded the report to CRM everything worked as expected and now when a user opens a Case, he or she can view all activities related to the Case from the main Case form tab.

      SQL Custom Report – OrElse function

      I consider myself slightly above novice when it comes to writing SQL reports in Visual Studio.  I can generate simple select statements, join entities, create case statements, do some date conversions, etc.  Today, I needed to filter a particular field that I added to a table.  I needed the filter to look at two potential values and then return Yes or No if either of the values were found.  There are potentially several ways to get the result needed and here is the one I used:
       
      There is a nice little Operator in SQL Reports named OrElse.  I selected to use this operator to check two field values in my Expression for the field property in the report.  Below are the steps I took.  I currently use Visual Studio 2005.
      1. Right-click the field on the Report from the Layout tab
      2. Select Expression
      3. Add the IIF statement with a nested OrElse
        1. IIF(castAsString(Field.Value) = "The Desired Value" OrElse (field.value = theDesiredValue), "True Value", "False Value")

        2. Below is the expression from the report

        3. =IIf(cstr(Fields!vair_IsInterface.Value) =

          "True" OrElse (Fields!CFPCategory.Value = 4), "Yes","No")

      One additional function needed for this query was a string conversion for the value retrieved from the SQL statement.  Cstr is the report function to convert a value to a string so I applied that function to the vair_IsInterface to convert the value to a string so I could compare it to the string value of "True"

      I hope you were able to follow this and you find it helpful when writing reports.