SSRS User story 1: Create a drill through report, in first report show the account name and phone number, if click on the account name it should show the account name, phone number, address, website and logo.

A DrillThrough report is a report that a user opens by clicking a link within another paginated report. DrillThrough reports commonly contain details about an item that is contained in an original summary report.

The data in the drillthrough report is not retrieved until the user clicks the link in the main report that opens the drillthrough report must be retrieved at the same time, consider using a sub report.

https://learn.microsoft.com/en-us/sql/reporting-services/report-design/drillthrough-reports-report-builder-and-ssrs?view=sql-server-ver16

Entities:

·         Account

Fields:

o   Account Name

o   Phone number

o   Address1_City

o   Account logo (Image)

o   Logobase 64(Multiline text)

 

If you created Image datatype field in dynamics 365 CRM you can’t able to add this field into form.

That’s why go to the https://make.powerapps.com

you can go to the Solution your Form/Fields are put in.

Select the form from Account entity

Then add Created image field into Form

It seems that the Image column type in dataverse does not save the content of the image as base64 and to display the image in the report requires this content.

Do I have to create a "Text Area" field hidden to convert the uploaded image to base64 and make the report rely on this field.

Here I created one multiline text field with the named as LongBase64 (Maximize the field length.)

 Write Java script code: 

var validator = false;

async function changeImage(executionContext){

    if(validator){

        return;

    }

    var formContext = executionContext.getFormContext();

    var image = formContext.getAttribute("new_accountlogo").getValue();

    formContext.getAttribute("cr030_longbase64").setValue("");

    if(image){

         var base64 = await toBase64(image["_file"]);

       formContext.getAttribute("cr030_longbase64").setValue(base64.replace("data:image/jpeg;base64,",""));

       validator = true;

    }

}

const toBase64 = file => new Promise((resolve, reject) => {

    const reader = new FileReader();

    reader.readAsDataURL(file);

    reader.onload = () => resolve(reader.result);

    reader.onerror = error => reject(error);

});

 

Create onchange event on your Image field

SSRS Report Creation:

To do SSRS first we need to install SSDT

SQL Server Data Tools (SSDT) is a modern development tool for building SQL Server relational databases, databases in Azure SQL, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports. With SSDT, you can design and deploy any SQL Server content type with the same ease as you would develop an application in Visual Studio.

Link for download SSDT: https://go.microsoft.com/fwlink/?linkid=2132817

Open the ISO image

Run the SSDTSetup.exe file

When the download is completed, run the SSDTSetup.exe (as administrator) program to install the tool. When prompted, choose to install SQL server Reporting services and click on next as shown below

 

 

After that need to install the Report Authoring Extension tool

Link: https://www.microsoft.com/en-us/download/details.aspx?id=56973

 

Now Total setup is ready then we will create a SSRS Report from scratch.

Open the visual studio and search Report then choose Report Server Project

Here Right click on Reports à Add à New Item

Here choose Report and give the Report Name

In this scenario need to create 2 reports (Parent report and child report)

Right click on the Data Sources

choose Type as Microsoft Dynamics 365 Fetch

Connection String: Enter your CRM URL and click on ok

Click on the Credentials

Enter User name and Password click on Ok

Then Data Source is created

Right click on the Data Source and choose Add Data Set  

 

Fetch xml for Report 1 (Child report):

After clicking the Add Dataset below window will be opened.

Here paste the fetch xml code in Query section area.

And pass the Account as dynamically by using @ symbol.

Then Parameter is created automatically in Child report.

 

Child report Design:

In SSRS report image is not showing

If you want to show image in SSRS report then follow below steps

Right click on image field schema name and choose Image Properties

After clicking on the Image properties below window will be opened.

Here choose General tab

Choose Image source from Database

Choose field schema name

Report-2 (Parent Report):

Download the Account related fetch XML   

After clicking the Add Dataset below window will be opened.

Here In query section Add the fetch xml code and click on OK

Parent Report Design:

Here Right click on Account name schema name à Text box properties.

After clicking on Text Box properties below window will be opened

Here choose ActionàGo to report à choose Child report from dropdown listà Add parameter name (from child report) and choose value from dropdown list (Account guid)àclick on Ok.

Then link will be created from parent to child reports (Hyperlink link created on account Name)

Choose Parent Report and click on Preview then below accounts list will be shown.

In above list click on any account name then below window will be open.

If you want to show this report in CRM

Output:

Click on Parent report (Task1) then below window will be opened

Here click on Any account name then below window will be opened.