CODEWITHSUNDEEP
reporting-servicesssrs-2012
Dildar Hussain
Dildar Hussain

Reputation: 21

Dynamic 365 SSRS Report : create SSRS Custom Report for dynamic 365 to show reports for selected record of entity and its related records per page

i want to create an SSRS report in visual studio for my dynamic 365 project. so i want SSRS report to show name of Primary entity (in this case ACCOUNTS) and all related records from related entity (in this case CONTACTS). ACOUNTS has 1:N relation with ACCOUNTS.

so when a user select an account record in dynamic 365 and run Report against it then then report should display name from ACCOUNT as text and then show all related CONTACTS to that record in a table.

but if a user select multiple records from ACCOUNT table then same should happen but report should only show record of one ACCOUNT and its related entity on single page and next ACCOUNT name and its related entities on another page. i-e if i select 3 records then it report should create the pages and should related records of each account on a single page

currently i have created a SSRS report (see below image) in Visual studio 2019 and uploaded the .rdl file to dynamic 365 solution. enter image description here so basically what i want is when i select one record from ACCOUNT and then this report it should show only one page, Account name and related records in table.

but if i select multiple records from ACOUNT and run this report then it should show pagination and show single account name and related records to only that account on one page and records for second account name should be shown on second page.

i have been trying to to this from last 3 days but unable to solve the solve please if anyone could help me in this record i will really appreciate that.

Upvotes: 0

Views: 484

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

Read up on subreports.

The short answer is that you create a report that handles a single account passed in as a single parameter.

Once you have that working you then create a 'master' report.

The master report will have a multi-value parameter (AccountNo) for the user to choose from.

Then add a simple dataset that returns a list of the selected accounts, something like

SELECT DISTINCT AccountNo FROM myAccountsTable WHERE AccountNo IN(@AccountNo)

Then add a table to your report and n the details row, insert a subreport into the first cell (right-click then insert subreport).

Now right-click the subreport placeholder , go to properties and choose your single account report created earlier as the report and then in the parameters tab, add a parameter, select the parameter name from the left drop down (parameter name) and then select the accountno field for the value.

When you run the master report, it will produce a row for each entry in the dataset each row contains the same subreport but the parameter passed in will be the account number for that row.

Once it's roughly working you can delete all the empty rows and columns from the table and set a page break on the details group (below the main design window). With a bit of tidying up that should give you what you want.

If you get stuck search SO for "SSRS subreport" and you find similar answers.

Upvotes: 0

Related Questions