CODEWITHSUNDEEP
reporting-services
Lee
Lee

Reputation: 41

SSRS 2012 Dynamic Column Number

I got a request from the client when using SSRS reporting Service. I am kind of new to the SSRS. hope that someone can help me out with the problem below.

I use a common example to show the problem.

In SSRS dataset1, the data is like follows

StuId           StuSubject  
--------------------------------
 1                "Math"
 1                "Geography"
 2                "Science"
 3                "Math"
 3                "History"
 3                "Music"

In the SSRS dataset2, the data is like this one

StuId          StuName    
------------------------ 
  1            "Tom"
  2            "Joseph"
  3            "Linda"

In the SSRS DataSet3 , it would be like 

StuId             StuInt
--------------------------
 1                "Swim"
 2                "Chess"
 2                "Swim"
 2                "Running"
 3                "Game"

What i want in the SSRS report would be like this one

StuId StuName StuSubject StuSubject StuSubject StuInt StuInt StuInt

1 "Tom" "Math" "Geography" NULL "Swim" NULL NULL

2 "Joseph" "Science" NULL NULL "Chess" "Swim" "Running"

3 "Linda" "Math" "History" "Music" "Game" NULL NULL

The tricky part is that I don't know what is the maximum number of StuSubject for all these students, or more precisely, I don't like to set the limit of the 'StuSubject' columns because there could be hundred columns in the real case. I have thought of LookupSet function, but seems using LookupSet, you can only join multiple StuSubjects values with "," in one cell.

Any advice / suggestion would be much appreciated and thanks in advance

Edit : I could use Matrix control for one "join" situation, but is this possible to "join" multiple datasets into one finale one ?

Upvotes: 0

Views: 1244

Answers (2)

Pintu Kawar
Pintu Kawar

Reputation: 2156

Follow the below steps:

Use your DataSet1 in your matrix as suggested approach from LINK.

Your query would be similar to below:

 Select StuId,StuSubject
'StuSubject' + Cast(row_number() over (partition by id order by score) as INT) StuSubjectVal
 from table

You should get the result like below (after using matrix).

StuId     StuSubject1     StuSubject2     StuSubject3......

Add a column to your matrix, and use the Lookup from DataSet1 to DataSet2.

=Lookup(Fields!StuId.Value, Fields!StuId.Value, Fields!StuName.Value, "DataSet2")

Have not tested it, but it should work.

Upvotes: 0

grafgenerator
grafgenerator

Reputation: 819

The solution for you is to use Matrix report item - it allows to perform data grouping by rows and columns. Your data, returned from DB, should be aggregate of those two datasets that you have - it should be set of rows in format (StuId, StuName, StuSubject). You can then add row grouping by StuId (and child row grouping by StuName, but this is not necessary), and add column grouping by StuSubject. Details cell will simply output StuSubject.

enter image description here

Notice that although Lookup* functions allow you to do join data while processing the report, they are run while processing report by SSRS and thus are certainly less efficient (from my experience expressions almost always have really bad impact on report performance). Also, doing join whikle processing report is not a canonical way to develop the report, and not the best way to use SSRS engine, which works good when you need simple grouping or do not need grouping at all. The best approach is to generate SQL result as much close to what you want to display, as it is possible, taking into account context of report and common sense of course.

Upvotes: 1

Related Questions