none
Multiple rows of data presented in a single row, 3 column 'phone book' with cover page and 2 groups of people

    Question

  • Using SSRS to display a directory of people.

    My stored proc returns what you might expect, name, address, phone, etc., one person per row.

    From the report side, I need to put in a grid type format, 3 people per row.

    dataset:

    name1, addy1, phone1

    name2, addy2, phone2

    ...

    name500, addy500, phone500

    presentation:

    name1                                                    name2                                         name3

    addy1                                                     addy2                                          addy3

    phone1                                                   phone2                                        phone3

    Can this be tackled in SSRS?   What concepts do I need to learn to make it so?


    • Edited by cwmizner Friday, August 02, 2013 9:11 PM
    Thursday, August 01, 2013 10:19 PM

Answers

All replies

  • There can be many ways to acheive this. In your case, lets say you want to fix column size to 3 person records.

    1. Add some mechanism in your query which can add group number kind of column with each 3 rows. For example, first three rows get group number 1, row number 4 to 6 get group number 2 and so on.

    Let's assume your database table containing persons' information has 300 employees so, 300/3 = 100. We can now use NTILE function in SQL to acheive this.

    SELECT PersonID,PersonName,NTILE(100) OVER(ORDER BY PersonID) AS GroupingColumn  FROM Person

    2. Now, in your tablix you can use grouping on rows and using GroupingColumn of query to group rows.

    Hope this would give you some hints to acheive your requirments.

    Thursday, August 01, 2013 11:02 PM
  • Hi Cwmizner,

    From your description, you want to dispaly report on multiple columns, right?  In this case, we can design a multi-column report to achieve this requirement. Have a look at this article for detailed setps for how to do this http://p2p.wrox.com/content/articles/creating-mailing-labels-sql-server-reporting-services

    Besides, we can use three tablix to display it, and control the rows visibility to achieve it.
    Rows visibility on tablix 1: =IIF(RowNumber(nothing) mod 3 =1, false, true)
    Rows visibility on tablix 2: =IIF(RowNumber(nothing) mod 3 =2, false, true)
    Rows visibility on tablix 3: =IIF(RowNumber(nothing) mod 3 =0, false, true)

    Hope this helps.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Friday, August 02, 2013 6:23 AM
    Moderator
  • Thanks for the suggestions.

    Been investigating the multi-column report, got it working sorta, but having issues with a lot of my other requirments, I changed the thread title, I'll list out full requirments here:

    1. Printable PDF format is all that is important
    2. Cover Page
    3. 2 alpha sorted lists of people and their addresses, page numbering seperately for each people list, different header for each list.

    I have a single column version of the report working 100%, maybe I'll go back and try to sell that format, :)

    The design of the single column is:

    1. Cover Page as a sub report
    2. 2 datasets for each list of people, a Tablix for each
    3. Header is handled by a tablix header row (a row outside the group) and had Advanced Mode turned on to set 'RepeatOnNewPage' to true, this is done for each list Tablix giving different headers as required
    4. Page numbering is handled by a page break before the 2nd Tablix and 'ResetPageNumber' set to true 

    Issues I'm having with the multi column report:

    1. Cover page, if I add it as a subreport, it doesn't display right (get's chopped into 3 columns).  Had the thought of an outer report housing the cover page as a sub report as well as the data report, having problems with that as well, the muli column report goes to a single column
    2. Header issues, with current header strategy, I get my header on each of the 3 columns which I don't want.
    3. Page number/page break issues, if the first Tablix happens to end on the first column of the page, the page break takes it to the 2nd column rather than true page break, the 2 page numbering lists works.

    Thinking I need to go to an alternate solution unless above issues can be solved???  Seems it's just the nature of the beast?  Tradeoffs with the multi column stratedgy?

    Friday, August 02, 2013 9:52 PM
  • Besides, we can use three tablix to display it, and control the rows visibility to achieve it.
    Rows visibility on tablix 1: =IIF(RowNumber(nothing) mod 3 =1, false, true)
    Rows visibility on tablix 2: =IIF(RowNumber(nothing) mod 3 =2, false, true)
    Rows visibility on tablix 3: =IIF(RowNumber(nothing) mod 3 =0, false, true)

    Hope this helps.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support


    Going to investigate this solution, do you just place the 3 tablix on top of each other? Load the same data 3 times? etc?  Sounds like some maintainablity issues, but we'll see how it turns out...
    Friday, August 02, 2013 9:58 PM
  • There can be many ways to acheive this. In your case, lets say you want to fix column size to 3 person records.

    1. Add some mechanism in your query which can add group number kind of column with each 3 rows. For example, first three rows get group number 1, row number 4 to 6 get group number 2 and so on.

    Let's assume your database table containing persons' information has 300 employees so, 300/3 = 100. We can now use NTILE function in SQL to acheive this.

    SELECT PersonID,PersonName,NTILE(100) OVER(ORDER BY PersonID) AS GroupingColumn  FROM Person

    2. Now, in your tablix you can use grouping on rows and using GroupingColumn of query to group rows.

    Hope this would give you some hints to acheive your requirments.


    Can you please provide a little more detail on step 2?
    Friday, August 02, 2013 9:59 PM
  • I'm going down this road here, at this point I'm guessing it will be my final solution:

    http://blog.hoegaerden.be/2012/11/01/creating-multiple-column-reports/

    Friday, August 02, 2013 11:03 PM
  • I'm going down this road here, at this point I'm guessing it will be my final solution:

    http://blog.hoegaerden.be/2012/11/01/creating-multiple-column-reports/


    This technique worked out.
    • Marked as answer by cwmizner Wednesday, August 07, 2013 6:17 PM
    Wednesday, August 07, 2013 6:17 PM