I have two datasets.  One for Geom Spatial Data (US County Layer from SQL Server database) and one for Analytical Data (Analysis Services).  When the two sets are joined within the map control, some counties will not have values, by design.  However, the Tooltip will not display for counties with no values.  I would still like to show the county name when there are no values associated.  I can only conclude that the map control uses an inner join rather than an outer join to derive the dataset.  How can I overcome this limitation?

This is the tooltip expression.

="County:  " & First(Fields!County.Value) & CHR(13) &
"Availability:  " & FormatPercent(Fields!Availability_Pct.Value,3) & CHR(13) &
"Circuit Utilization:  " & FormatPercent(Fields!Circuit_Utilization_Pct.Value,3) & CHR(13) &
"Traffic:  " & FORMAT((SUM(Fields!In_Bytes.Value) + SUM(Fields!Out_Bytes.Value)) / 1024^4, "#,0.00") & " TB"

 

Hi jhadden,

Thanks for your post.

Just as you said above, we can join the two different data together, but if the two datasets have the default relationship with each other, you can take use of the Lookup function to retrieve the relevant value from anther dataset, more information relate to this function, you can refer to the article below:
http://msdn.microsoft.com/en-us/library/ee210531.aspx

Thanks,
Bill Lu

Thanks for the reply.  Attempted the Lookup function solution which still results in no Tooltip display for empty counties.  The map control simply does not show the Tooltip for Polygons that have no matching analytical data.  It works fine if the analytical data exists.  If the values themselves were the problem, I would expect to see an empty Tooltip popup on hover.  This does not happen.

I dont have my system now and I will definitely try this out when I reach home. But before that, could you try using the names from the Shapefile rather than the name from your dataset in your tooltip? And could you try first with just the county names in the tooltip rather than giving the whole expression?

As far as I can tell, names from the "Shapefile" (Spatial Dataset) are not allowed to be referenced from the Tooltip, even when qualified with a dataset name which requires an aggregation. 

=First(Fields!County.Value, County_Layer) & " COUNTY" & CHR(13) &
"Availability:  " & FormatPercent(Fields!Availability_Pct.Value,3) & CHR(13) &
"Circuit Utilization:  " & FormatPercent(Fields!Circuit_Utilization_Pct.Value,3) & CHR(13) &
"Traffic:  " & FORMAT(SUM(Fields!In_Bytes__TB_.Value) + SUM(Fields!Out_Bytes__TB_.Value), "#,0.00") & " TB"

Only Analytical dataset columns seem to be allowed which would explain why there is no tooltip in empty counties.  I also attemped to use the fields prefaced with "#" in the tooltip expression, but these are not allowed either, unless I'm unaware of proper syntax.

jhadden,

You can take use of the IIF funtion in SSRS to specify a instead string to replace country value when the relevant countries are empty, like =IIF(no country return, “no country”, country)

Thanks,
Bill Lu

Zilong,

Thanks for the suggestion, but it wouldn't be aesthetically accurate in this case.  The absence of analytical data doesn't mean the county doesn't exist. ;-)

I am still working on it. For me, if I just give #Country (where country is the data from the shapefile), I get results. Now I can add expresions like

#Country & "Hello"

in the tooltip and it displays country name with Hello. Now, country is the column which connects the shapefile data and the dataset. The moment I bring Continent also, it breaks. For eg

#Country & "Hello #Continent"

it starts displaying something like 

Japan Hello Polygon_Layer1

 

But if I just use the shapefile without connecting to the dataset, I think I am able to get it working properly. Still trying, will post back if I get an update

jhadden,

I am not meaning country not exist then replace with another value, please pay attention to the words you said above “However, the Tooltip will not display for counties with no values”, I mean you can use expression to check the counties , if you find it return with no values, you can use another string placeholder as tooltip to show up.

Thanks,
Bill Lu

Zilong,

What would be the syntax for checking for an empty value?  I haven't found the magic word.

Thanks, Jeff

If I use the following Syntax.

=IIF(ISNOTHING(Fields!County.Value), "NULL", FIRST(Fields!County.Value) & " COUNTY" & CHR(13) &
"Availability:  " & FORMATPERCENT(Fields!Availability_Pct.Value,3) & CHR(13) &
"Circuit Utilization:  " & FORMATPERCENT(Fields!Circuit_Utilization_Pct.Value,3) & CHR(13) &
"Traffic:  " & FORMAT(SUM(Fields!In_Bytes__TB_.Value) + SUM(Fields!Out_Bytes__TB_.Value), "#,0.00") & " TB")

It still doesn't show the tooltip as "NULL" on empty counties.  I also get a syntax error "Literal is missing termination symbol" if I attempt to use #CountyName column.

jhadden,

Firstly, you to judge the field value of County is null or empty, you can reference the expression below:
=Iif(IsNothing(Fields!County.Value),"NULL",IIF(Fields!County.Value="","Empty",Fields!County.Value))

Thanks,
Bill Lu

I'm also noticing that when you plot points on a map, duplicate locations (data type GEOM) are automatically eliminated from the Geography dataset.  Some control over this behavior would be nice.  Just because points are co-located does not mean that I want them eliminated from either my map layer or analytical data layer.  In my opinion, the assumption that SSRS is making is incorrect and my intentions are being overridden by the tool, which is never good.  Dataset rows are being eliminated against my wishes.

The many problems created by the INNER JOIN when joining the Geographic Layer data with Analytical data could be eliminated if the join were FULL OUTER or at least controllable through the UI.

Hi jhadden,

Did you or anyone found a work around for this "issue"?