Home > MSDN, SSRS > SSRS MultiValue Lookup

SSRS MultiValue Lookup

This Question I got from MSDN, MSDN link here

User Comments:-

I have a situation in SSRS to get the common values between the two columns where the values are sored coma separated as below.

Ex: ColumnA : abc,cde,efg ColumnB : cde,xyz,abc the result in ColumnC should be : cde,abc

similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.

Sample Dataset Query with Sample data as below:-

select ‘Jan,feb,mar,apr’ ColumnA,’mar,apr,may’ ColumnB

union

select ‘nov,dec’,’sep,oct’

union

select ‘may,jun,jul,aug,sep,oct’,’jun,jul,nov,dec’

Create sample report and add one more column where we write expression to fulfill the requirement

Write below Code in Report -> Properties -> Code

Public Function LookupMultiValue(ByVal value As String,ByVal value2 As String, ByVal ch As Char) As String

Dim var As Integer
Dim var2 As Integer
Dim cnt As Integer = 0
Dim cnt2 As Integer = 0
Dim input As String=””
Dim input1 As Integer=0
Dim i As Integer =0
Dim i2 as Integer=0
Dim firstVal As Integer =0
Dim SecondVal as Integer=0
For Each c As Char In value
If c = ch Then cnt += 1
Next
For Each c As Char In value2
If c = ch Then cnt2 += 1
Next
cnt=cnt
cnt2=cnt2

For var = i to cnt
For var2 = i2 to cnt2
If split(value ,”,”)(var )=split(value2 ,”,”)(var2 ) then input =input+”,”+split(value ,”,”)(var )

Next var2
Next var

Return input
End Function

In report column add below expression:-

=code.LookupMultiValue(Fields!ColumnA.Value,Fields!ColumnB.Value,”,”)

As shown in below screen:-

MultiLookup

Run the report, report output look like below:-

ReportOutput

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: MSDN, SSRS Tags: ,
  1. Praveen
    May 13, 2015 at 10:48 am

    How to avoid comma in the beginning of the expected result column. example: ,mar,apr – how to avoid comma before mar?

  2. Praveen
    May 13, 2015 at 12:49 pm

    Hoe to avoid comma in the beginning of the expected column value.

    • May 14, 2015 at 9:46 am

      Modify the Function as below:-
      Public Function LookupMultiValue(ByVal value As String,ByVal value2 As String, ByVal ch As Char) As String
      Dim var As Integer
      Dim var2 As Integer
      Dim cnt As Integer = 0
      Dim cnt2 As Integer = 0
      Dim input As String=””
      Dim input1 As Integer=0
      Dim i As Integer =0
      Dim i2 as Integer=0
      Dim firstVal As Integer =0
      Dim SecondVal as Integer=0
      For Each c As Char In value
      If c = ch Then cnt += 1
      Next
      For Each c As Char In value2
      If c = ch Then cnt2 += 1
      Next
      cnt=cnt
      cnt2=cnt2
      For var = i to cnt
      For var2 = i2 to cnt2
      If split(value ,”,”)(var )=split(value2 ,”,”)(var2 ) then If input=”” then input =split(value ,”,”)(var ) else input =input+”,”+split(value ,”,”)(var )
      Next var2
      Next var
       
       
      Return input
      End Function

  3. May 14, 2015 at 9:46 am

    Modify the Function as below:-
    Public Function LookupMultiValue(ByVal value As String,ByVal value2 As String, ByVal ch As Char) As String

    Dim var As Integer
    Dim var2 As Integer
    Dim cnt As Integer = 0
    Dim cnt2 As Integer = 0
    Dim input As String=””
    Dim input1 As Integer=0
    Dim i As Integer =0
    Dim i2 as Integer=0
    Dim firstVal As Integer =0
    Dim SecondVal as Integer=0
    For Each c As Char In value
    If c = ch Then cnt += 1
    Next
    For Each c As Char In value2
    If c = ch Then cnt2 += 1
    Next
    cnt=cnt
    cnt2=cnt2

    For var = i to cnt
    For var2 = i2 to cnt2
    If split(value ,”,”)(var )=split(value2 ,”,”)(var2 ) then If input=”” then input =split(value ,”,”)(var ) else input =input+”,”+split(value ,”,”)(var )

    Next var2
    Next var
     
     
    Return input
    End Function

  4. Praveen
    May 14, 2015 at 1:45 pm

    Kool, it worked. thank you!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: