Hello folks…

A very long time ago, I wrote something about getting the VFP concept of “multiple detail bands” in  SSRS.  (That’s the scenario where you have two unrelated children of a parent and want to display them in the same table.)

It now occurs to me that the RDL function LookupSet was practically made to help solve this problem. (I think it came in in SSRS 2008, but it might only have appeared in R2 — I haven’t checked this.)

Suppose, for example, I want to show multiple languages along with multiple cities for a country, using my standard borrowed-from-mySql World database. Suppose I have a denormalized dataset in my report that shows countries and their related cities.  I can add another dataset for country languages, without denormalizing any further.  Now, I can write an expression like this (from the context of the first dataset, which might have a name like “CountryCities”):

 

…and add a little code function like this:

Public Function GetLanguages(ByRef Langs as Object()) As String
   Dim sb as new System.Text.StringBuilder()
   For Each o As Object in Langs
      sb.append(“<li>” & o.ToString() & “</li>”)
   Next
   Return “<ul>” & sb.ToString() & “</ul>”
End Function

… and that’s all I need. As you can see I’ve gussied it up somewhat here by adding some HTML placeholder formatting (HTML placeholders were not available before 2008 for sure) so that my “details” can be put into a single textbox but are neatly formatted as a list. Still, it’s precious little code.

 

What do you think?

Looking up even further

I have rarely used the trio of SSRS Lookup functions.  Most of the time, it seems to me that I should do most of the joining myself, in SQL queries, before bringing the data into the RDL for arrangement. Seems like perf would be better that way, although I have never tested this assumption and now am somewhat more motivated to do so.

Goodness me. It certainly is nice to be back in RDL-territory….