Develop SSRS Reports in Power Apps, Dataverse, or Dynamics 365

How to Develop SSRS Reports in Power Apps, Dataverse, and Dynamics 365

1. Prerequisites

• Microsoft Dynamics 365 instance
• Power Apps environment or Dataverse instance
• SQL Server Reporting Services (SSRS) or Visual Studio (for report creation)
• Access to Dynamics 365 and Power Apps for report deployment and testing

2. Step-by-Step Instructions for SSRS Report Creation

Step 1: Setup Power Apps Environment

Ensure that you have access to your Power Apps environment and are connected to Dataverse (or Dynamics 365) where the data is stored.

• Go to Power Apps and sign in with the correct credentials.
• Ensure you have the necessary privileges for both creating reports and accessing the data (typically, you need System Administrator or custom roles that allow report creation).

Step 2: Download and Set Up SQL Server Data Tools (SSDT)

• Download SQL Server Data Tools (SSDT) from the official site.
• Install SSDT to get the necessary tools to create SSRS reports.

Step 3: Create an SSRS Report Using FetchXML

To create a report, you will use FetchXML queries in your SSRS report. FetchXML is a proprietary query language in Dynamics 365 and Dataverse, similar to SQL.

1. Open Visual Studio with SQL Server Data Tools.
2. Create a new Report Project in Visual Studio (File → New → Project → Report Project).
3. Add a new Report by right-clicking the Reports folder and choosing Add → New Item → Report.
4. Set up the Data Source: For Dynamics 365, use the CRM data source type and connect it to your Dynamics instance. Provide the connection string and ensure it's authenticated.

Step 4: Use FetchXML Queries in the Report

In the Report Data pane, right-click Datasets → Add Dataset. Choose FetchXML as the query type and paste your FetchXML query.

<fetch>
    <entity name="contact">
        <attribute name="firstname" />
        <attribute name="lastname" />
        <attribute name="emailaddress1" />
        <order attribute="lastname" descending="false" />
    </entity>
</fetch>
            

Step 5: Design the Report Layout

Use Table or List components in SSRS to display the results of the FetchXML query. Bind the table or list to the dataset you created from FetchXML. Add fields like firstname, lastname, and emailaddress1 to the table.

Step 6: Deploy the Report to Dynamics 365 or Power Apps

1. Publish the Report from Visual Studio to your Dynamics 365 instance (Right-click the project and choose Deploy).
2. Specify your Dynamics 365 server URL and provide credentials for deployment.
3. Once deployed, go to Settings → Reports in the Dynamics 365 instance, where your report will appear.

Step 7: Add the Report to Power Apps / Power Portal

1. Open Power Apps.
2. Go to the Data section, select Entities, and choose the entity you want the report to be linked with.
3. In the Forms section of the entity, add a new Subgrid or Embedded Report component.
4. Configure the subgrid to show the SSRS report and assign appropriate parameters.

3. Example of FetchXML Queries for Reports

Example 1: Simple FetchXML Query to Fetch Contacts

<fetch>
    <entity name="contact">
        <attribute name="firstname" />
        <attribute name="lastname" />
        <attribute name="emailaddress1" />
        <order attribute="lastname" descending="false" />
    </entity>
</fetch>
            

Example 2: FetchXML with Filter for Active Accounts

<fetch>
    <entity name="account">
        <attribute name="name" />
        <attribute name="telephone1" />
        <attribute name="emailaddress1" />
        <filter>
            <condition attribute="statecode" operator="eq" value="0" />
        </filter>
    </entity>
</fetch>
            

Example 3: FetchXML with Join to Retrieve Contacts Related to Accounts

<fetch>
    <entity name="contact">
        <attribute name="firstname" />
        <attribute name="lastname" />
        <attribute name="emailaddress1" />
        <link-entity name="account" from="accountid" to="contactid">
            <attribute name="name" />
            <filter>
                <condition attribute="name" operator="like" value="Contoso%" />
            </filter>
        </link-entity>
    </entity>
</fetch>
            

Example 4: FetchXML with Grouping and Aggregation

<fetch>
    <entity name="invoice">
        <attribute name="totalamount" alias="total_invoice_amount" aggregate="sum" />
        <groupby>
            <attribute name="customerid" />
        </groupby>
    </entity>
</fetch>
            

Additional Steps: Embedding the Report in a Power Apps Canvas App

If you want to display your SSRS reports in a Power Apps Canvas app:

1. Add a Power BI Tile control (which can display embedded SSRS reports).
2. Set the control properties to link to the report that you have deployed in your Dynamics 365 environment.