locked
How create record-level maps in an SSRS report? RRS feed

  • Question

  • I have two SQL Server tables, each with a spatial data column: one has point locations for 657 social service programs in New York City; the other has polygons for the 70 or so Community Districts in the 5 boroughs.  When I SELECT both tables with a UNION ALL I get a citywide map of Community Districts with all point locations.  I am also able to select/produce a map with the location of any single program showing only the borough it is located in. 

    What I do not know how to do and wish to learn is the following: produce an SSRS report on all the programs with a tiny borough map accompanying the data for each program -- that is a report on 657 programs with 657 small maps associated with the program data.  Each brough map needs to highlight only the community district where the particular program is located, leaving blank shapes for the other districts in the borough.  And something like a shiny star should mark the location of the program within that borough.

    Thanks for any advice, Mike


    m mccullo

    Monday, February 27, 2012 9:06 PM

Answers

  • I'm not sure anyone will be able to provide you with a complete guide to SSRS in a forum post ;)

    However, one point - I'm not sure you do want a UNION ALL query... what you want is a single resultset in which the point location appears in one column, and the polygon borough appears in a different column of the same row, right? That's not what UNION ALL will give you.

    UNION ALL combines two resultsets (think about lining up two spreadsheets, one on top of the other), whereas you want to JOIN the table containing the point to the table containing the polygon (i.e. lining up two spreadsheets side-by-side), so that each row represents a single program.


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Marked as answer by Stephanie Lv Thursday, March 8, 2012 7:28 AM
    Tuesday, February 28, 2012 7:05 AM
    Answerer

All replies

  • I'm not quite sure what "advice" you want... what you're asking is perfectly possible, so what have you already tried and which bit are you stuck on?

    Have you read the MSDN reference to the SSRS map control at: http://technet.microsoft.com/en-us/library/ee240845.aspx  ?


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    Monday, February 27, 2012 10:04 PM
    Answerer
  • The key problem may be that I'm a newbie with SSRS.  I've tried using the Report Wizard in Visual Studio to produce a report.  The best I can do so far is a report with a single citywide map.  Logically I see that, with each record as it prints, a UNION ALL SQL query needs to retrieve and print the point location of that record along with with the borough but I don't see how the query gets assigned to each record.  I'm sure I need to study up on how SSRS displays spatial data.  I'll study the link you indicate.   Any tips or further study links would be appreciated.

    m mccullo

    Monday, February 27, 2012 10:20 PM
  • I'm not sure anyone will be able to provide you with a complete guide to SSRS in a forum post ;)

    However, one point - I'm not sure you do want a UNION ALL query... what you want is a single resultset in which the point location appears in one column, and the polygon borough appears in a different column of the same row, right? That's not what UNION ALL will give you.

    UNION ALL combines two resultsets (think about lining up two spreadsheets, one on top of the other), whereas you want to JOIN the table containing the point to the table containing the polygon (i.e. lining up two spreadsheets side-by-side), so that each row represents a single program.


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Marked as answer by Stephanie Lv Thursday, March 8, 2012 7:28 AM
    Tuesday, February 28, 2012 7:05 AM
    Answerer