Drillthrough Report in SSRS

Hi Guys, Have a Good Day!. Hope you are doing Good.

Today I’m going to share you how to developDrillthrough report in SSRS.

Confused ?

Don’t worry. Let me explain in short manner. Drillthrough report is a report that user can opens by clicking a link within another report. For example, We are listing All the Vendors and want to iterate vendors to all of his Purchase Orders and to their Purchase Order Details.

CONCEPT1

For more Details DrillThrough Reports in SSRS

We are going to illustrates the following tasks:
• Creating a Report Model project. 
• Creating reports 
• Vendor list 
• Purchase order list 
• Purchase order details 
• Providing drill-through action under designs 
• Saving the report to AOT and deploying it to the Report Server.

Prerequisites
To learn and implement the following walkthrough, you must have:
Microsoft Dynamics AX 2012 with sample data.
Microsoft Visual Studio 2010 with Microsoft Dynamics AX reporting extension.
Microsoft Dynamics SQL Server Reporting Services.

Creating a Report Model project
Start Visual Studio; press Ctrl + N to create a new project.
Select Microsoft Dynamics AX under Installed Templates in the left pane, and select
Report Model.
Provide a name for the project as VendPurchaseOrders.

PIC1

Creating reports
Creating a Vendor list report
1. Right-click on Solution and navigate to Report under the Add submenu. Select the report and rename it to VendListRep.
2. Right-click on datasets and click on Add dataset.
3. Modify the following properties for the newly added dataset: Data source to
1.Data source to Microsoft Dynamics AX.
2.Data source type to Query.
3.Default Layout to Table.
4.Name to VendListDS.

PIC2

4. Switch to the Query property and click on the blue button with white dots to open the Query dialog.

5. Select VendTable from the list and press the Next button.
6. Select AccountNum, vendName display methods; BankAccount, Blocked, Invoice fields from VendTable_1 .
7. Drag-and-drop VendListDS to the Design section of the report. This will create a new Auto Design named as AutoDesign1.
8. Select AutoDesign1, go to Properties, and set the following properties:
1. LayoutTemplate to ReportLayoutStyleTemplate.
2. Title to Vendors.

9. Select VendListDSTable under AutoDesign1 and set the following properties: StyleTemplate to TableStyleAlternatingRowsTemplate.
10. Title to List of vendors.

Creating a Purchase order list report 

1. Right-click on Solution; select Report under the Add submenu. Select the report and rename it to VendPurchOrders.
2. Right-click on datasets and click on Add dataset.
3. Modify the following properties for the newly added dataset

1.Data source to Microsoft Dynamics AX.
2. Data source type to Query.
3.Default Layout to Table.
4.Name to PurchOrderListDS.

PIC3

4. Switch to the Query property and press the icon to open the Query dialog.
5. Select PurchTableListPage from the list and press the Next button.
6. Select PurchId, PurchName, OrderAccount, InvoiceAccount,
PurchStatus, PurchaseType fields from PurchaseTable.
7. Drag-and-drop PurchOrderListDS to the Design section of the report. This will create a new Auto Design named as AutoDesign1.
8. Select AutoDesign1, go to Properties, and set the following properties:
1. LayoutTemplate as ReportLayoutStyleTemplate.
2. Title as Purchase orders.
9. Select PurchOrderListDSTable under AutoDesign1 and set the following properties:
1. StyleTemplate as TableStyleAlternatingRowsTemplate.
2. Title as List of purchase orders.

Creating a Purchase order details report

1. Right-click on Solution, select Report under Add submenu. Select report, rename the report to PurchDetails.
2. Right-click on datasets, click on Add dataset.
3. Modify the following properties for the newly added dataset:

1.Data source to Microsoft Dynamics AX.
2.Data source type to Query.
3.Default Layout to Table
4.Name to PurchDetailDS.
PIC4
4. Switch to the Query property, and click on the button at the corner to open the Query dialog.
5. Select PurchTableDocument from the list and click on the Next button.
6. Select PurchId, PurchName, OrderAccount, InvoiceAccount, Email, DeliveryDate fields from PurchTable, and select ItemId, Name, QtyOrdered, PriceUnit, PurchPrice, and CurrencyCode, LineAmount fields from PurchLine.
7. Drag-and-drop PurchDetailDS to the Design section of the report. This will create a new Auto Design named as AutoDesign1.
8. Select AutoDesign1, go to Properties, and set the following properties:
1. LayoutTemplate to ReportLayoutStyleTemplate.
2. Title to Purchase order details.
9. Select PurchDetailDSTable under AutoDesign1, and set the following properties:
1. StyleTemplate to TableStyleAlternatingRowsTemplate.
2. Title to Purchase order details.
10. Right-click on Groupings inside PurchDetailDSTable, and selectAdd Grouping.
11. Rename the new grouping to PurchId.
12. Right-click on Group On under PurchId grouping and select Add Group on.
13. Rename Grouping to PurchIdGrp and set Expression to Fields!PurchId.Value in Properties.

PIC5

14. Right-click on Header under PurchId grouping, and select Add row.
15. Rename the row to PurchTableRow.
16. Right-click on PurchTableRow and select Add | Field.
17. Rename the field to PurchId and set Expression to Fields!PurchId.Value.
18. Right-click on PurchTableRow and navigate to Add | Field.
19. Rename the field to Name and set Expression to Fields!PurchName.Value.
20. Right-click on PurchTableRow and select Add | Field.
21. Rename the field to OrderAcc and set Expression to Fields!OrderAccount.Value.
22. Right-click on PurchTableRow and select Add | Field.
23. Rename the field to InvoiceAcc and set Expression to Fields!InvoiceAccount.Value.
24. Right-click on PurchTableRow and select Add | Field.
25. Rename the field to Email and set Expression to Fields!Email.Value.
26. Right-click on PurchTableRow and select Add | Field.
27. Rename the field to DelDate and set Expression to Fields!DeliveryDate.Value.

PIC6

28. Go to the Data node under PurchDetailDSTable and verify that the following fields were correctly added automatically.

PIC7

29. Save all changes done.
Providing a drill-through action under designs
Adding a parameter – the Purchase order list report
1. Right-click on Parameters and select Add | Parameter.
2. Rename the parameter to VendAccount.
3. Right-click on filters, and select Add Filter under VendPurchOrders report.
4. Rename the filter to VendAccount and set the following properties:

1.Expression to Fields!OrderAccount.Value.
2. Name to VendAccount.
3. Operator to Equals.
4. Value to Parameters!VendAccount.Value.
PIC8

Adding a parameter – the Purchase order details report
1. Right-click on Parameters, and select Add | Parameter.
2. Rename the parameter to PurchId.
3. Right-click on filters, and select Add Filter under PurchDetails report.
4. Rename the filter to PurchId and set the following properties:
1. Expression as Fields!PurchId.Value.
2. Name as PurchId.
3. Operator as Equals.
4. Value as Parameters!PurchId.Value.

PIC9

Drill-through action
The Vendor list report
1. Right-click on AccountNum, and select Add Report Drill Through Action under AutoDesign1 | VendListDSTable | Data.
2. Select ReportDrillThroughAction and go to Properties.

PIC10

3. Set the ReportDesign property to AutoDesign1 of VendPurchOrders report.
4. Select the AX_CompanyName parameter under ReportDrillThroughAction and set the value to Parameters!AX_CompanyName.Value.
5. Select the VendAccount parameter under ReportDrillThroughAction and set value to Fields!AccountNum.Value.

The Purchase order list report
1. Right-click on PurchId, and select Add Report Drill Through Action under
AutoDesign1 | PurchOrderListDSTable | Data.
2. Select ReportDrillThroughAction and go to Properties.
PIC11

3. Set the ReportDesign property to AutoDesign1 of PurchDetails report.
4. Select the AX_CompanyName parameter under ReportDrillThroughAction and set the value to Parameters!AX_CompanyName.Value.
5. Select the PurchId parameter under ReportDrillThroughAction and set the value to Fields!PurchId.Value.

Saving the report to AOT and deploying it to the Report Server
1. In the Solution Explorer, right-click on the Report Model project, and select Build.
2. Once the report is successfully built, right-click on the Report Model project, and select Add VendPurchaseOrders to AOT.
3. Once the report is added to AOT, we can deploy the same to the Report Server.
4. To deploy the report to the Report Server: Right-click on the Report Model project and select Deploy.

Final Result
The Vendor list will be as follows:
FINAL RESULT

I hope you have enjoyed… 🙂

Advertisements

One thought on “Drillthrough Report in SSRS

  1. Reblogged this on Microsoft Dynamics AX 2012 and commented:
    Delete all parameters except AX_CompanyName and the Parameter you created. If any other parameter remains, you definitely got following error when you call Sales Order report form drill through option.

    “The Microsoft Dynamic AX parameter ID should be a RECID. The exception is System.FormatException: Input string was not in a correct format.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s