locked
Trying to group two separate columns in 1 group (in other words use two source columns as source for one group) RRS feed

  • Question

  • Hi All,

    For the case of my question I created a simplified example table. The question is about splitting names in reporting services: so 1 field contains two names, and I want to show all of these names extracted from this field alphabetically in one group.

    The problem is I can not union my dataset, because I need to run the query once. I will give you an example for clarity

    The table contains sales, and the account manager(s) responsible for the sales. You notice two names in the account manager column

    In the report I want to group all the account managers because the list needs a sign off by the account managers

    So I created two fields in the dataset which split the account managers

    This is the formula of the first one split(Fields!Account_Manager.Value,",")(0)+","+split(Fields!Account_Manager.Value,",")(1)

    and the second one This is the formula of the first one split(Fields!Account_Manager.Value,",")(2)+","+split(Fields!Account_Manager.Value,",")(3) 

    I can use both field as group sources and show it like this in a tablix. But it is not sorted alphabetically as each split is a separate group (I have two groups). The following problem is account managers can be duplicated, because once they can be mentioned as first one in the source data, while at another sales rule they might be the second one mentioned

    The way I did it beneaht . And all problems arise from the necessity of needing two groupings instead of one grouping


    Wednesday, July 24, 2019 3:16 PM

Answers

  • Hi,

    From your post, you want to find the distinct value from the accound manager as you second pic shows right?

    If so, from my opinion, it would be hard to use only SSRS expression for this. But we could try using sql query to get a new field. Use your sample data you could try something similar as:

    create table test
    (
    Sales  int ,
    [Account Manger] varchar(100)
    )
    insert into test values 
    (100,'Logan,John,Kennedy,James'),
    (200,'Sammart,Ron,Price,Katie')
    
     
    
    ;with cte as (
    SELECT t.*,Rtrim(ltrim(v.value)) as value,
    (row_number()over(partition by Sales order by (select 1))+1)/2 rn 
    FROM test t
    CROSS APPLY STRING_SPLIT(t.[Account Manger], ',')v)
    
     
    
    SELECT distinct LEFT(name,LEN(name)-1) as name FROM (
    SELECT Sales,
    (SELECT value+' ' FROM cte 
      WHERE Sales=A.Sales and rn=A.rn
      FOR XML PATH(''), TYPE).value('.', 'varchar(max)') AS name
    FROM cte A 
    GROUP BY  Sales,rn
    ) B 

    This will get distinct name for sign off.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 25, 2019 7:57 AM

All replies

  • Hi,

    From your post, you want to find the distinct value from the accound manager as you second pic shows right?

    If so, from my opinion, it would be hard to use only SSRS expression for this. But we could try using sql query to get a new field. Use your sample data you could try something similar as:

    create table test
    (
    Sales  int ,
    [Account Manger] varchar(100)
    )
    insert into test values 
    (100,'Logan,John,Kennedy,James'),
    (200,'Sammart,Ron,Price,Katie')
    
     
    
    ;with cte as (
    SELECT t.*,Rtrim(ltrim(v.value)) as value,
    (row_number()over(partition by Sales order by (select 1))+1)/2 rn 
    FROM test t
    CROSS APPLY STRING_SPLIT(t.[Account Manger], ',')v)
    
     
    
    SELECT distinct LEFT(name,LEN(name)-1) as name FROM (
    SELECT Sales,
    (SELECT value+' ' FROM cte 
      WHERE Sales=A.Sales and rn=A.rn
      FOR XML PATH(''), TYPE).value('.', 'varchar(max)') AS name
    FROM cte A 
    GROUP BY  Sales,rn
    ) B 

    This will get distinct name for sign off.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 25, 2019 7:57 AM
  • Thank you very much Lukas I am going to give it a try on my case and if it works  I will mark it solved!
    Thursday, August 1, 2019 11:41 AM