CODEWITHSUNDEEP
sql-serverssrs-2008
DonMax
DonMax

Reputation: 960

How to Group by 2 different columns and Make it to behave differently in certain situations in Sql with SSRS

I have 2 foreign key columns in one table named as Subs (Say like PONo, InvoiceNo). In a single record, either PO or Invoice or Both will have some data (Without Null). When, Both PO and Invoice have data, I need to consider only InvoiceNo not PONo.

I have to use the above query to generate reports. So, I need to group by either PONo or InvoiceNo to send to the specific Supplier. I dont have any idea how to group by two columns into one. Especially, when both PoNo and InvoiceNo are same in two records, I need to generate 2 different letters instead of 1. Please consider the below case,

  SubsId                  PONO         Issue           InvoiceNo          Supp

    1                     001          XX              null              abc
    2                     null         YY              001               abc
    3                     null         ZZ              001               abc

If the above table values are the case, then I need to get the result something like this,

Letter 1

    PONO        Issue         InvoiceNo       Supp

    001          XX           null            abc

Letter 2

    PONO        Issue         InvoiceNo       Supp

    null         YY           001             abc
    null         ZZ           001             abc

Because, I need to show the report with either PONo or InvoiceNo as single group.

I searched everywhere to group by 2 columns into 1 group in SSRS. But, I didn't find any solution. So, Please help me to fix this issue.

Thanks

Upvotes: 2

Views: 506

Answers (2)

Ian Preston
Ian Preston

Reputation: 39566

You can add a Calculated Field to your Dataset like this:

=IIf(IsNothing(Fields!InvoiceNo.Value)
  , "P" & Fields!PONO.Value
  , "I" & Fields!InvoiceNo.Value)

Call it GroupNumber or whatever you like. It looks like PONO and InvoiceNo are both strings in your example so I've just concatenated a type identifier string to them. If they are numeric you could cast them as strings in the above expression as required.

This accomplishes your two goals, i.e. being able to use both PONO and InvoiceNo together as a single grouping item, but also being able to differentiate between the two types so PONO and InvoiceNo values with the same number don't get grouped together as one group item.

Then, when setting up tablix groups in the report, you group on this new field. It never needs to be displayed anywhere, it's just used for the grouping; this should work as you require.

Upvotes: 2

Nathan Fisher
Nathan Fisher

Reputation: 7941

You could use the TSQL Coalesce statement, a TSQL Case When Statement, or an SSRS IIF(compareison, Truepart, falsepart)

TSQL Coalesce 

select colalesce(InvoiceNo, PONO) as Number, Issue, Supp
from mytable1 join mytable2 etc
Group By colalesce(InvoiceNo, PONO), Issue, Supp

TSQL Case when

Select case When InvoiceNo is null then PONO
           else InvoiceNo END as Number, Issue, Supp
    from mytable1 join mytable2 etc
    Group By case When InvoiceNo is null then PONO
           else InvoiceNo END , Issue, Supp

SSRS

Number TextBox expression 

=IIF(Fields!InvoiceNo.value ="", Fields!PONO.value, Fields!InvoiceNo.value)

Upvotes: 1

Related Questions