Post reply

SSRS Grouping

  • April 23, 2014 at 12:59 am

    #306630

    Hi,

    I have a requirement in SSRS as shown in the attachment. Please suggest me how can i achieve this.

    Attachment Name :

    1. Data_set_from_SQL.png (Data set from SQL query)

    2. Expected_Result_from_SSRS (Expected output in SSRS 2005/2008)

    Thanks in advance.

  • April 23, 2014 at 10:15 am

    #1708185

    Off the top of my head

    In your report create two datasets (assumes your data is in a table called data)

    First as

    ;WITH

    i (InvoiceNo) AS (SELECT InvoiceNo FROM data GROUP BY InvoiceNo HAVING COUNT(*) > 1),

    p (PaymentNo) AS (SELECT PaymentNo FROM data GROUP BY PaymentNo HAVING COUNT(*) > 1)

    SELECT DISTINCT i.InvoiceNo,p.PaymentNo FROM data

    LEFT JOIN i ON i.InvoiceNo = data.InvoiceNo

    LEFT JOIN p ON p.PaymentNo = data.PaymentNo

    Second

    all rows from data

    Add a tablix to the report referencing the first dataset with one row and two columns

    In the left cell insert another tablix that references the second dataset and set the filter to use an expression to determine the column for the filter (InvoiceNo or PaymentNo) depending on which is not nothing and test against whichever value is not nothing

    Repeat above for the right hand cell

    *Edit

    You might want to set the main tablix to 2 rows by 4 columns, put the headings in top row and merge 2 columns of row 2 for each of the other tablix

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply