Edit

Share via

Create a new report using SQL Server Data Tools

  • Article

SQL Server Data Tools is a report authoring environment that lets you create or edit SQL Server Reporting Services reports in Visual Studio. The end result is a report definition .rdl file that contains the report definition that you can publish to view reports.

You can also author reports by using a common text editor. To reduce the effort to create a custom report, modify an existing .rdl file that provides most of the desired functionality. For more information about the format of the XML elements in an .rdl file, see Report Definition Language Reference. The modified report XML can be verified by using the specified XML schema. Reporting Services will also verify the report definition and reject a report if the definition is invalid when you try to upload the report.

Note

If the .rdl file contains a FetchXML query, the query in the RDL is validated by Microsoft Dynamics CRM 2016 Report Authoring Extension, which internally validates it against the FetchXML schema.

Create a custom Fetch-based report

To create a custom Fetch-based report:

  1. Make sure that you have a supported version of Visual Studio, SQL Server Data Tools, Microsoft Dynamics CRM 2016 Report Authoring Extension, and the necessary privileges. More information: Report writing environment using SQL Server Data Tools

  2. Open Visual Studio, and then create a report server project.

  3. In Solution Explorer, right-click the Reports folder, and then select Add New Report.

  4. Select Next.

  5. On the Select the Data Source page, select New Data Source, and then specify the following details:

    • Name: Type a name for the data source.

    • Type: Select Microsoft Dynamics 365 Fetch.

    • Connection String: Specify the connection string. The connection string must be specified in the following format:

      ServerURL;OrganizationName;HomeRealmURL

      In this connection string, only ServerURL is mandatory. If OrganizationName isn't specified, the first organization that the user running this query belongs to is used. HomeRealmURL is the Home Realm URL of the Identity Provider used by your organization and is needed when your organization uses Federation for identity management. Contact your network administrator to determine the Home Realm URL.

      Select Credentials to specify the credentials to connect to Dynamics 365 for Customer Engagement apps or Dynamics 365 for Customer Engagement apps, and then select Next.

  6. On the Design the Query page, type the FetchXML query in the Query box. To get this query, you can do one of the following:

    • Get the FetchXML from an Advanced Find query. To do this, open a Customer Engagement (on-premises) app, select Advanced Find, create the query that you want, and then on the Advanced Find tab, select Download Fetch XML. Copy the FetchXML into the Query box of the Dataset Properties in Visual Studio.

    • Manually enter the FetchXML query. This example shows how to create a report that displays all accounts with 5,000 or more employees.

      <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">  
  <entity name="account">  
    <attribute name="name" />      
    <attribute name="numberofemployees" />  
    <attribute name="accountnumber" />  
    <order attribute="name" descending="false" />  
    <filter type="and">  
      <condition attribute="numberofemployees" operator="gt" value="5000" />  
    </filter>  
  </entity>  
</fetch>

    Select Next.

  7. Verify the fields that will be included in the report, and then select Next.

    • The Report Authoring Extension adds extra fields depending on the data type. Numbers, dates, lookups and optionsets have an extra fieldNameValue field. For example, if the FetchXML includes the createdon attribute, there are two fields available: createdon and createdonValue.
      • fieldName (example: createdon)
        • This field is a string formatted according to the user's format settings. It's intended for display only. Converting the value to its original data type might result in an error. For example, an expression such as =CDate(Fields!createdon.Value) might fail or give an incorrect date if the date format doesn't match the locale settings on the report server.
        • For optionset fields the value is the display text of the optionset value.
        • For lookup fields the value is the primary name field of the linked record.
      • fieldNameValue (example: createdonValue)
        • This field is the raw unformatted value of the field according to the data type. The fields can be adjusted and formatted for user-specific display using the hidden CRM_ format parameters.
        • For datetime fields, it's the UTC value.
        • For number or currency fields, it's the unformatted numeric value.
        • For optionset fields it's the option number value.
        • For lookup fields it's the Guid of the lookup field.

  8. Select a style to apply to the report, and then select Next.

  9. Verify the fields that will be included in the report and enter a name for the report, such as Accounts With More Than 5,000 Employees. Select Finish.

  10. If you’d like to see how the report appears when it’s run, select the Preview tab.

    This generates an .rdl file with the specified report name. You can use this file to publish your custom report in Dynamics 365 for Customer Engagement apps using the Report Wizard. More information: Publish reports

See also

Report Writing Environment