As I work on my Reporting Services 201 session for PASS Summit 2011, I am reminded that there are a few areas I always struggle with. No matter how many times I add multi-value parameters, I usually forget to change the dataset WHERE clause to IN instead of =. And no matter how many times I create a list and add a table to it, I never remember to delete the detail rows. Every time that happens, a cryptic error appears, and I have to remember how to fix it.

No more! With this blog post, I have now documented, for my benefit and yours, how to fix this!

The Situation

I have created a simple report, with a table. I drag a list control on the body, underneath the table. Then, I drag a table on the list.

I go to the Preview tab. I see this error: “The tablix ‘tablixname’ has a detail member with inner members. Detail members can only contain static inner members.”

Say what?

A table with detail rows is considered “dynamic” because the number of rows can change. A list is also dynamic, because the number of rows returned can change. A dynamic object can’t be nested inside another. The table must be static, not dynamic. To achieve this, I edit the table so all information is in the header and there are no detail rows. To do so, I right-click the header and choose to Insert Row > Below.

Then, I move the detail information into the new row.

I delete the row group. My list and table look like this.

Now, when I Preview the report, I see two rows for each item. The first row is the header, with the static titles like “Product Number” and “Name”. The second row is the actual values for that list item.

That’s one more Unsolved Mystery put to rest!