Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Welcome to my blog. I work as Database lead at Synaptic Digital. Hope you find some interesting stuff here.
Browse by Tags · View All
BRH 17
SQL Server 15
#SQL Server 11
#BI 10
#TSQL 8
TSQL 8
BI 7
SSRS 6
#SQLServer 6
SSRS 2008R2 5

Archive · View All
January 2011 6
December 2010 5
September 2012 4
May 2012 4
March 2011 4
November 2012 2
October 2012 2
January 2012 2
February 2011 2
November 2010 2

String Concatenation/Aggregation in SSRS column or row group

Jan 8 2011 6:23AM by Chintak Chhapia   

Sometimes, we need to aggregate strings in SSRS designer. In situations, when we are using Report Model as data source this is very much needed.

There are many Aggregation functions available in SSRS, but to implement string concatenation/aggregation, we can’t directly use any of the provided function directly.  But, by using SSRS custom code and RunningValue aggregate function we can achieve this as shown below.

For reference the same report is attached herewith. You can find the report below.

Step-1 Create a dataset.
Note:-  This example is just created to simulate the situation.

DECLARE @tab TABLE ( id INT, name NVARCHAR(100), phoneNumber NVARCHAR(100)) 
INSERT INTO @tab (id,name,phonenumber) 
VALUES 
(1,'Sachin','9824512345') 
, (1,'Sachin','26612345' ) 
, (2,'Rahul','99922299922') 
, (2,'Rahul','1234567890') 
, (2,'Rahul','234561234') 

SELECT a.id ,a.name ,a.phoneNumber FROM @tab a

Step-2 Create a report with RowGrouping on ID.

Step-3 Add custom code

Dim Num As Integer = 0 
Dim tempstr As String = "" 
Function AggregateString(ID as Integer, PhoneNumber as String) 
If ID = Num Then 
    tempstr = tempstr + "," + PhoneNumber 
Else 
    Num = ID 
    tempstr = PhoneNumber 
End If 
Return tempStr 
End Function

Step-4 Add colum with following expression

=RunningValue(Code.AggregateString(Fields!id.Value,Fields!phonenumber.Value),Max,"id")

Step-5 Run Report.
Here, we can see the concatenated rows as shown in below.

 string aggregation

References:-
http://msdn.microsoft.com/en-us/library/ms159136.aspx
http://blogs.msdn.com/b/suryaj/archive/2007/08/11/string-aggregation.aspx 

Tags: BRH, #SQL Server, TSQL, BI, #BI, #SSRS, SSRS 2008, String Concatenation, group, String Aggregation, SSRS 2005, SSRS 2008R2,


Chintak Chhapia
40 · 5% · 1470
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • Hi, This was a nice read. Hit on the nail for my requirement. However I have a question, as in the example you gave suppose if we have duplicate numbers for a person then how do we deal with this?

    commented on Jun 15 2012 3:44AM
    gautam.monga
    3070 · 0% · 2
  • In that case it will display number two time, however I think we can tweak the function to ignore duplicates..

    commented on Jul 25 2012 7:58AM
    Chintak Chhapia
    40 · 5% · 1470

Your Comment


Sign Up or Login to post a comment.

"String Concatenation/Aggregation in SSRS column or row group" rated 5 out of 5 by 2 readers
String Concatenation/Aggregation in SSRS column or row group , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]