SSRS dataset many to many relationship

Anna 21 Reputation points
2021-11-05T19:08:50.587+00:00

I have a report that pulls data from two different datasets. These cannot be combined a query as they are coming from different data sources.
it is a many to many relationship table/recordsets. Since you can have one detail in a tablix, how do you display the other details?
I would like to display the first dataset records then the related records from the other dataset. These two datasets are realated using one key id field.
Suggestions? Any alternative to subreports?

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,102 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Joyzhao-MSFT 15,361 Reputation points Microsoft Employee
    2021-11-08T03:06:23.263+00:00

    Hi @Anna ,
    You could display data from multiple data sources in a single report, merge datasets from different sources, and use Lookup Function/LookupSet Function to obtain matching values of different datasets.
    Or you could consider combining data from different data sources into one dataset in the SSRS report. For more information, please refer to:
    SSRS: Join data from different SSRS data sources into dataset.
    SSRS: Merge data from different datasources into one dataset inside SSRS report.
    Hope this helps.
    Best Regards,
    Joy

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread