locked
Displaying two rows side by side RRS feed

  • Question

  •  

    Hi Ppl,

     

    Hope you all doing well.

     

    I have a stored procedure that return a list of Employee with one column which looks like

     

    Name

    John

    Ben

    Ian

    Peter

    Tony

    Brian

    Nick

    Bobby

    .....

     

    What I woud like to achieve in my SQL Report in a table control is something like this

     

    Name1       Name2

    John            Ben

    Ian               Peter

    Tony             Brian

    Nick            Bobby

    ......

     

    I want to merge two adjacent rows into 1 row in a Table control or any other work around.

    There is no relation between the rows. I just want to dislpay in this form in SSRS report.

     

    Any idea how could it be done in SSRS without altering my sql query?

     

    Regards,

    Nabeel

    Saturday, December 6, 2008 12:09 AM

Answers

  • One way that you could achieve it (but requires changes in the query) is to return the rownumber for that dataset and use a matrix with a column group using the expression:

    Code Snippet

    =Ceiling(Fields!RowNum.Value mod 2)

    And the Row group expression as:

    Code Snippet

    =Ceiling(Fields!RowNum.Value / 2)

     

    By only replacing 2 in the expression you could make three columns, four columns, etc...

     

    ----

     

    If you really don't want to change the query you can still get the two column side-by-side by using expressions in a table. Add a table with two columns to your report.

    In the first column, use an expression like this to show the values:

    Code Snippet

    =iif(RowNumber("DataSet1") mod 2 = 0 OR RowNumber("DataSet1")  < Count(Fields!name.Value,"DataSet1"),Previous(Fields!name.Value), Fields!name.Value)

    Notice that you will be showing mostly the 'Previous' Value for that column.

     

    In the second column, you will use a similar expression:

    Code Snippet
    =iif(RowNumber("DataSet1") mod 2 = 0 OR RowNumber("DataSet1")  < Count(Fields!name.Value,"DataSet1"), Fields!name.Value, "")

     

    In this case, notice that you are no longer using previous, but the current field value or an empty string. This is mostly to

    handle the last line in an odd number of records.

     

    If you preview the report now you will see that the second column of a given line, is repeated in the first column of the next. Now all you have to do is hide the odd lines, so In the visibility expression for the detail row use an expression like this:

    Code Snippet

    =iif(RowNumber("DataSet1") mod 2 = 1 AND RowNumber("DataSet1") < Count(Fields!name.Value,"DataSet1"), true, false)

     

    Your result should be exatcly the one you posted.

     

    Good luck,

    Boreki

    Saturday, December 6, 2008 11:38 PM
    Answerer

All replies

  • One way that you could achieve it (but requires changes in the query) is to return the rownumber for that dataset and use a matrix with a column group using the expression:

    Code Snippet

    =Ceiling(Fields!RowNum.Value mod 2)

    And the Row group expression as:

    Code Snippet

    =Ceiling(Fields!RowNum.Value / 2)

     

    By only replacing 2 in the expression you could make three columns, four columns, etc...

     

    ----

     

    If you really don't want to change the query you can still get the two column side-by-side by using expressions in a table. Add a table with two columns to your report.

    In the first column, use an expression like this to show the values:

    Code Snippet

    =iif(RowNumber("DataSet1") mod 2 = 0 OR RowNumber("DataSet1")  < Count(Fields!name.Value,"DataSet1"),Previous(Fields!name.Value), Fields!name.Value)

    Notice that you will be showing mostly the 'Previous' Value for that column.

     

    In the second column, you will use a similar expression:

    Code Snippet
    =iif(RowNumber("DataSet1") mod 2 = 0 OR RowNumber("DataSet1")  < Count(Fields!name.Value,"DataSet1"), Fields!name.Value, "")

     

    In this case, notice that you are no longer using previous, but the current field value or an empty string. This is mostly to

    handle the last line in an odd number of records.

     

    If you preview the report now you will see that the second column of a given line, is repeated in the first column of the next. Now all you have to do is hide the odd lines, so In the visibility expression for the detail row use an expression like this:

    Code Snippet

    =iif(RowNumber("DataSet1") mod 2 = 1 AND RowNumber("DataSet1") < Count(Fields!name.Value,"DataSet1"), true, false)

     

    Your result should be exatcly the one you posted.

     

    Good luck,

    Boreki

    Saturday, December 6, 2008 11:38 PM
    Answerer
  • brillaint!...cheers :)
    Nabeel Farid
    Sunday, December 14, 2008 2:29 PM