SSRS Report - Specific Format

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 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

score 2 · Answer 1 · answered Dec 02 '13 at 11:11

2

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

answered Dec 02 '13 at 11:11

Ian Preston

38,816
8
95
92

Great walkthrough - must find out how to embed images. – Steph Locke Dec 02 '13 at 11:12
Your image is not visible. I again tried, and not able to come up with required format. Can u pls tell me the step by step process in 2005? It would be really helpful. – sk7730 Dec 02 '13 at 12:20
Are you generating Report Shown in Updated part of my question? Sorry, I am not able to see your image? – sk7730 Dec 02 '13 at 12:30
Thanks lan. Working fine now. – sk7730 Dec 02 '13 at 13:12

score 1 · Accepted Answer · answered Dec 02 '13 at 11:09

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

Create a dataset that has project and project_task joined
Create a table and set projID as the row group but do not add it in as text visible on the table for now
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
Drop another table into the cell below the ProjID
Fill this table with the task values
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.

SSRS Report - Specific Format

2 Answers2