http://www.sqlservercentral.com/blogs/mikedavissql/2013/04/10/ssrs-map-label-overlap-fixed/

Printed 2014/12/25 01:02AM

SSRS Map Label Overlap Fixed

By MikeDavis, 2013/04/10

In Reporting Services 2008 r2 and above there is a great tool called the Map tool. It is great for visualizing data across a map. One of the issues I have run into is overlapping labels. For example I am trying to track where people in the company are at today. If two people are in the same location there names over lap on the report making them both unreadable.

Here is a sample of my data. Here you can see I have four people in Columbia, SC. You will notice I also have the spatial data for the location of the city.

image

When I show this on a map and try to show the names they over lap as seen below.

image

To fix this I went back to my data set and a created a rank function to rank the number of cities that match. The SQL is below.

,RANK() Over (Partition by SpatialCity.location order by [user].LastName) Ranks

Now my dataset has a rank next to each City and the matching cities have different numbers for each consultant name. The new dataset is seen below.

image

Back in reporting services, now all that is left is to adjust the label on the map. The code below shows the label expression. I am adding line feed/carriage returns for each number in the rank column. I only have 5 levels here you could go as far as you would like.

=iif(Fields!Ranks.Value = 1, Fields!ConsultantName.Value,
iif(Fields!Ranks.Value = 2, vbCRLF + Fields!ConsultantName.Value,
iif(Fields!Ranks.Value = 3, vbCRLF + vbCRLF + Fields!ConsultantName.Value,
iif(Fields!Ranks.Value = 4, vbCRLF + vbCRLF +vbCRLF + Fields!ConsultantName.Value,
iif(Fields!Ranks.Value = 5, vbCRLF + vbCRLF +vbCRLF +vbCRLF + Fields!ConsultantName.Value,
Fields!ConsultantName.Value
)))))

Now the names show on the report not over lapping.

image

Let me know if you have any questions.



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.