0

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.

sk7730
  • 636
  • 3
  • 9
  • 32

1 Answers1

0

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.

kyooryu
  • 1,469
  • 3
  • 23
  • 48
  • The scenario what i given here is example. I have almost more than 10 tables to be joined which is causing duplicate tasks. That is why i am looking for multiple data set with grouping... – sk7730 Feb 11 '14 at 06:46
  • Adding grouping to your table will eliminate duplicates - give it a try, it is better for SSRS to do some grouping than executing several datasets. – kyooryu Feb 11 '14 at 06:59
  • I agree adding grouping will eliminate duplicates. But in this scenario, Grouping ID is Project ID. But tables involved in this report are (Task, tableA, tableB, Table3, Table4 etc)...For these tables How Project ID Grouping will work...Example, For Task table, Task ID & ProjID together will act as a clustered primary key....Same applicabe to all reports....How will it work in such scenario – sk7730 Feb 11 '14 at 07:08
  • 1
    Add grouping to the fields in which you want to remove duplicates - for other fields use aggregation function, or if you don`t care which value is shown function like `First(SomeField)` will do. I can't really give you more specific directions unless you post what tables you want to show in the report. – kyooryu Feb 11 '14 at 07:12
  • Do you mean below points. 1) For Project Table Grouping ID will ProjID. 2) Inside This table i placed another table for which Grouping ID will be Task ID. – sk7730 Feb 11 '14 at 07:19
  • I think, i should have signle DataSet Even if i do have duplicate values for any tables. I should have one Table in SSRS with ProjID as Grouping ID. Inside this table i can place the tables which i want by merging rows. For these, i have to select the Gruoping ID accordingly... Am i Right? – sk7730 Feb 11 '14 at 07:24