SSRS Grouping With Two DataSets

Question

I have a table Project.

  ProjID  Proj_task
   1       NIT 2.0  
   2       SSRS

I have table called Project_Task.

  ProjID  Task_DS   User
   1       task1      User1
   1       task2      User2
   1       task3      User3
   2       task4      User4
   2       task5      User5
   2       task6      User6

I want to generate Report in Below format. (Each project ID inforamtion should be in one Page for which i am using Insert Group )

In Page 1:

 ProjID  : 1
 Proj_task: NIT 2.0 
 --------------------
       User       Task_DS
      User1    task1
      User2    task2
      User3    task3

In Page 2:

 ProjID  : 2
 Proj_task: SSRS    
 --------------------
       User       Task_DS
      User4    task4
      User5    task5
      User6    task6

I have many tables with project Information to be displayed in SSRS. If i make single data set using join and using it in Sinle table in SSRS, i do get duplicate records in few scenario. So i thought of creating multiple datasets and mapping it to different tables (eg: Proj info in One table, Task Information in another Table ..etc) and make Grouping in SSRS..

I want to know if that is possible in SSRS. If so please guide me to do that.

kyooryu kyooryu · Accepted Answer · 2014-02-11T06:43:19

If this is the only table you want to show, then make a single dataset:

SELECT 
    ProjID
    , Proj_task
    , task_DS
    , User 
FROM 
    Project p 
    JOIN Project_Task pt ON p.ProjID = pt.ProjID

Then, make a table with a group in SSRS, and in the group choose grouping on ProjID and Proj_task. After that check page break Between each instance of a group in Page Breaks pane.

SSRS Grouping With Two DataSets

1 Answers