How to get this lay out by GROUPING?

  • Hi Friends,

    I need to group few of the columns to show in the report as single entry since it can occur multiple times.

    Please refer my sample spread sheet in here. It may explain the lay out I am looking for. So in the spread sheet, the first row has different RequestID (1,2,3). So instead of occurring 3 times for each RequestID, I want to show as one entry.

    Can any one please suggest me?

    Thanks,
    Charmer

  • Can you explain little more, in spreadsheet for 3 request IDs 1,2,3 the customer ref number is different.

    What's the significance of this number and how you want the customer ref number to be displayed in final result?

  • The column starting from "ReqestID" to the end should display 3 rows (1,2,3)...

    The column starting from "Sender" to "FailedCardGroups" should display one row ...since this row will occur 3 times for each RequestID...but I want to display as one row.

    Please refer the attached document. It might give you an idea.

    Thanks,
    Charmer

  • have you tried this?

    SELECT RequestID, ReqSeq, ROW_NUMBER() OVER(PARTITION BY RequestID, ReqSeq ORDER BY ReqSeq )

    FROM Layout;

  • Hi,

    I think its more to handle in formatting the data i.e. pro-grammatically we need to hide the columns based on sender, requestsequence values using expressions.

    If you are using reporting tools like SSRS this kind of formatting of dataset can be handled with less effort.

  • You just need to define the groups in your tablix. Identify which columns define a group and use them to get what you need. Your query should show the same values for the three rows from request sequence 3275 and will differ only on the last four column.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks Luis, that adds more value.

  • Yes Luis. I tried Grouping the columns and it shows same values for each RequestID but I want to show one time. Not three times for each RequestID.

    That's where my concern is. I am not able to define this in tablix.

    Thanks,
    Charmer

  • For remaining duplicate rows, can you try setting visibility = false at column level ?

  • I don't understand. On what basis we should make it hidden? Based on Row Number() to find the duplicates?

    Thanks,
    Charmer

  • Yes.

    1. Use row_number() or

    2. compare the senderid, requestseq of 2nd row with first row and if equals hide those columns in 2nd row etc.

Viewing 11 posts - 1 through 10 (of 10 total)

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