SSRS Report - Specific Format

118 views Asked by At

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 tried creating two tables, subreports, Joining two tables & creating single datasets to achive the same. I am not able to arrive at the above mentioned format. Can Anyone please help me how can i do that?

I am getting below report foramt. I am mentioning for projectID 1 alone. It is same for ProjectID 2 also.

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

UpDated

Page:1

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

page 2

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

There are 2 answers

0
Steph Locke On BEST ANSWER

What you can do which might be easier is nesting tables:

  1. Create a dataset that has project and project_task joined
  2. Create a table and set projID as the row group but do not add it in as text visible on the table for now
  3. Add a row inside group and add ProjID into the new data cell created or you can do the various bits of text concatentation via an expression here
  4. Drop another table into the cell below the ProjID
  5. Fill this table with the task values
  6. Delete excess cells as appropiate and format

This will generate an outer grouping for each project that can have page breaks etc after, whilst allowing you to generate a nice table of project tasks for each one.

4
Ian Preston On

Based on your data, use the following query to populate the dataset:

select p.ProjID
  , p.Proj_task
  , pt.Task_DS
  , pt.[User]
from Project p
  inner join Project_Task pt on p.ProjID = pt.ProjID

SQL Fiddle with demo.

Create a table like this:

enter image description here

You can see there is a group with three group header rows.

The group is just based on the ProjID column:

enter image description here

There is also a page break set for the group.

Results look good. Page 1:

enter image description here

Page 2:

enter image description here