Well, this is embarrassing. I must really be out of practice.
Last time I wrote, which was the first time in a very long time, I wrote about how you could use Lookup functions to handle dataset relationships in an RDL. Not only didn’t my example show you the best way to do it, but I neglected to notice that the stupid example I came up with was almost exactly the same as the not-best-way code in an MSDN example.
Luckily, I almost immediately had a real-life need to handle this requirement in a Dominican report, and realized there was a much better way.
Let’s start again, shall we?
How should we concatenate child attributes in a parent dataset’s detail row?
Walkthrough: Teachers’ credentials
We have a set of teachers, which may be filtered by a set of parameters.
We want to add their credentialed subjects (or their phone numbers, or their assigned rooms, or anything they may have more than one of, really) as a single cell in each teacher-row, within our table of teachers.
Step 1: get your data
Think of your teacher information (the attributes in 1:1 relationship with teacher) as the “header” data. The the credential data (many:one relationship with teacher) is the “detail” data, even though you’re not planning to display them that way.
Note that you may have more than one set of “details”, just as in our previous discussions of this subject using the World database, our countries have many cities and also have many languages.
Select your “header” information as one dataset — and yes, it really does help to name it something like “Teacher” or “Header”, not “Dataset1”. Retrieve your various detail datasets separately.
Make sure that each detail dataset joins back to the parent or header table, not so that you can repeat any columns except for the teacher ID, but so that you can filter by the same conditions. Otherwise you will make your detail datasets unnecessarily large.
The select statements here each represent a separate dataset:
select teacher.id, firstname, lastname
from teacher
join teacherTerms on teacher.id =
teacherTerms.teacher_id
where teacherTerms.termName in (@Terms)
— Credentials dataset, shares only primary key
— and filter conditions with the Teacher dataset :
select credential.teacher_id, cred_name
from credential
join teacher on teacher.id =
credential.teacher_id
join teacherTerms on teacher.id =
teacherTerms.teacher_id
where teacherTerms.termName in (@Terms)
— ditto, any additional “child” or detail datasets:
select room.teacher_id, room_name
from room
join teacher on teacher.id =
room.teacher_id
join teacherTerms on teacher.id =
teacherTerms.teacher_id
where teacherTerms.termName in (@Terms)
Step 2: create your “header” table, matrix, etc
Your header dataset is your driver for each row.
Create most of its columns in the usual way. Then create another column for each child attribute into which you are going to stuff specialized, detail content.
You’re going to create an expression for these specialized textboxes. Note that it’s a good idea to use the Label attribute so that the surface of the Report Designer shows the intended result of your expression, just like the other columns:
Step 3: use the JOIN VB.NET function and the RDL LookupSet function
Your expression gets a set of Credentials from the Credentials dataset for each Teacher, using the LookupSet function. This creates an array object, holding the multiple credential items. Then, you use the JOIN function to concatenate those items with whatever delimiters you want; here, I’m using a comma and a space.
Fields!teacher_id.Value,
Fields!cred_name.Value, “Credentials”),
“, “)
In the LookupSet function, you’re using:
- the id value from the parent or header dataset as the first argument,
- the matching key value from the child or detail dataset as the second argument,
- the value you want to display from the child or detail as the third argument,
- the name of the child or detail dataset as the final argument.
… you’ll also find the JOIN function in the Text group.
What else can this do?
What about the case where you want to use HTML? Believe it or not, you can just use the following, and mark your expression to be evaluated as HTML:
Join(LookupSet(Fields!id.Value,
Fields!teacher_id.Value,
Fields!cred_name.Value, “Credentials”),”<li>”)
& “</ul>”
… if you know for sure that every teacher has at least one credential, this will work fine. If some teachers have no credentials, you can tidy this up a little with an IIF() function so that the list is not created in those cells. You can do this either by calling the LookupSet function again in the first argument of the IIF(), which would be a bit wasteful, or by including a count of credentials for each teacher in your Teacher set, so that you can then do the following:
“”,
“<ul><li>” &
Join(LookupSet(Fields!id.Value,
Fields!teacher_id.Value,
Fields!cred_name.Value, “Credentials”),”<li>”)
& “</ul>”)
What could go wrong?
It’s easy to make a mistake when writing this type of expression, especially since you don’t always have the detail dataset column names readily available to you. If you spell something wrong, and then you try to preview your report, you’ll get an error, which will be fairly explicit and help you go back to correct whatever you did.
Sometimes, however, after you are sure you have corrected the error, you get the same error or something that seems related to it over and over again. When this happens, try deleting the .data file which is created as part of your previewing/test runs. Often the problem is that your expression is cached. You may have to close and re-open the RDL, or even Visual Studio, to get rid of it, in rare cases.
One good way to tell if this is happening to you is to rename the textbox or placeholder that is causing the error. If you continue to see the same error, and it is still including the same object name that you know you have renamed, you’ve got a cache problem.
Additional thoughts, perf consequences, etc
There are obviously other ways to do this that would involve SQL code in the database rather than VB.NET code in an RDL, regardless of how simple or complex the RDL code would be. What’s best?
If you’re going to leverage this exact code for multiple reports, score one point for doing it on the SQL level. But consider that the SQL methods for doing this are not as “natural” as doing the same task in VB, and there would be some perf consequences, especially if you did it in a view, and then filtered the results in a combined SQL statement. The right approach would be a table-valued function, so that you could pass filtering parameters directly into the code, and only have to perform the concatenation on the rows you really want on the given report run.
Consider, also, that you’re flattening the dataset and making it very wide. I’m not entirely sure that this is going to be faster than pulling the parent and child data separately, in two, targeted, narrow datasets. (Note: adding the count of credentials that I suggest for the use in HTML results, by contrast, doesn’t make the dataset much wider and in most cases can be done very efficiently as part of the header dataset query.)
Consider, also, that the formatting code will often not be exactly the same for each report. Pulling the data raw and then formatting within each RDL — as in our example, could be comma delimited, could be HTML — seems like a better idea than building this complexity into the SQL.
Finally, consider that a self-service reporting environment, which gives end users the pleasure and power of designing their own results, allows the RDL to contain this code without having to ask IT for a database change. Empowering users in this way is one of the great joys of working with SSRS, IMHO, and also one of its primary raison d’êtres.
All things considered, I’m pretty happy to use, and share, this RDL technique.