SSRS Drill Down Report T1

Task 1: Create a drill down 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.

User Story: Create a drill down report, in first report show the account name and phone number. If you click on the account name then it should show the account name, phone number, address, website and logo.

Entity: 

Account (company)

Fields:

Logo (image data type)

LongBase64 (Multi Line Text)

 

For image field go to Power apps and create it.

Fetch XML:

Go to Advance find and get the fetch XML and use in SSRS report.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

  <entity name="account">

    <attribute name="name" />

    <attribute name="primarycontactid" />

    <attribute name="telephone1" />

    <attribute name="accountid" />

    <attribute name="address1_composite" />

    <attribute name="websiteurl" />

    <attribute name="new_longbase64" />

    <order attribute="name" descending="false" />

    <filter type="and">

      <condition attribute="address1_composite" operator="not-null" />

      <condition attribute="telephone1" operator="not-null" />

      <condition attribute="name" operator="not-null" />

      <condition attribute="websiteurl" operator="not-null" />

      <condition attribute="new_longbase64" operator="not-null" />

    </filter>

  </entity>

</fetch>

 

Now create a SSRS report for that go to visual studio

Step1: Open Visual studio Click on create project and search for the Reports 
Step2: Select Report Server Project in second Option and Click on Next   

Step3: Rename and click on create Then you see your SSRS reports screen

Step4: Right side we can see Reports Right click on Reports and then we can see some options in that click on Add ==> New Item. Then we can see Add Items Window in That we Have Three Options 1. Report 2. Data Source 3. Data Set .Now select Report because we are Doing for Reports. 

Change the Report name and Add it. By this we are getting a .rdl File (Report Definition Language)

 

Step5: Then we can see in Left Navigation some Options Like 1.Built in Fields, 2.Parameters, 3.Images, 4.Data Source, 5.Data Sets.

Now Right click on Data Source and click On Add Data Source. Then Data Source Properties page will be Visible.

 

Step6: In Data Source Properties we have two table 1. General 2. Credentials

1. General

Select General and Give Name, Click on Embedded Connection

Select Type as MD365 Fetch and provide the dynamics URL in Connection string

Step7: Next Click on Credentials and click on the option use this user name and password in that provide our Dynamics CRM 365 Credentials and click on ok Button.

Step8: Now for Data Set Right Click on Datasets and click on add Datasets

 

Step9: Then Data Set Properties in that we have many options like

1.      Query 2. Fields 3. Options 4. Filters 5. Parameters

Ø  Now Select Query and Name the Dataset after that click on option Use a Data Set Embedded in my report.

Ø  Then give Data Source which you created.

Ø  Next the query Type select as Text.

Ø  Now In query past your Fetch XML code and click on Query Designer.

Step10: Now Query Designer page Will Be Open Click on (!) Run and check the Result.

Now in Result you Can See the Account Related Information.

Step11: Right click in then add an Items In the report i.e, which is visible in center a small empty box.

Add Table the drag and drop the Data Set Fields which was required.

 

Step12: Drag the name data and insert in Row Groups for grouping.

Step13: Delete the old name field.

Step14: Now hide the website, address, logo (long base64) and give the visibility to name1 filed.

Select the website field and right click on it and then click on the Text Box Properties. Opens a popup

Step15: In Text Box Properties popup. Now select hide and in display toggled by the report item select the name toggle click on Ok. Do the same thing for address, logo (long base64) filed also.

In long base64 filed to show the Logo Right click on the long base64 filed and click on Insert and select the image.

Then Image properties popup will be visible in select the Database for image source, in field select new longbase64 field and in MMIM Type select image/jpeg for .jpg format.

 

Step16: Now click preview and check the result.

Step17: Now Build the Solution 

Step18: Now Create Report in CRM For that Open CRM, go to advance settings 

click on setting savron ==> click on Customization area ==> click on Customization sub area 

Inside the Customization click on Customize the System ==> click on Components ==> click on Reports

Create a New Report 

Step19: Now Select The Report Type as Existing File and Name the report in name text box.

Click on choose file, go to path of report and select . rdl file (Task1DrillRepo.rdl)

Step20: Now in Categorization we have 4 option 

1. Categorize 2.Related Record Type 3.Display in 4.Languages 

In Categorize Select modules Marketing, Sales, Service reports

Related Record Type select Record as Accounts 

For Display In select Forms for related records, List For Related Record, Report Area

For Language select English 

Step21: Now Open CRM, Go to Account Record and click on three dots on top then click on Run Report.

There you can find the given report.

Click on the Task1DrillRepo then you can view the report.

Output:

Click on account the drill down will open and then it will show the account name, phone number, address, website and logo.