Remove the dynamically generated blank columns from rdl

Question

I have a SQL server result set which I need to display using SSRS rdl. The result set id like:

SpeakerId      Product    Topic 
16               a       A
16               a       B
16               b       C
16               a       D
17               b       B
17               c       C
17               c       E
18               a       B
18               c       A
19               c       C

I need to display this information in different tabs in excel based on Products. Each speaker will have a only one row and the topics will be displayed in different coulmns based on alphabetical order. When I tried to implement this dynamic pages and columns in rdl, I am facing following issue: I am getting blank columns for the topics, if the product and the speaker in that tab is not having the topic which other Product and speaker(in other tabs) have.

Please suggest any way to resolve this. I tried to use Rank but the concept does not helps in this case as we have multiple pages in the excel.

Thank You.

Answer 1

In this sort of situation, you need to make sure that all required topics are returned for all speakers so that SSRS knows to display the topics.

One way of doing this is with a PIVOT query, something like:

select SpeakerId
  , [A],[B],[C],[D],[E]
from ResultSet
pivot
(
  max(Product)
  for Topic in ([A],[B],[C],[D],[E])
) p

SQL Fiddle with demo.

You can see here that each speaker has a column for each Topic in the result.

This means that in the report, you can use a Table instead of a Matrix to display the data.

Unknown number of Topics?

You may still run into issues if you don't know the required Topics before writing the report query - in this case I would suggest making sure that you include an OUTER JOIN to the Topic table.

This depends on your data, obviously. But this is useful as it means that each Speaker will have a row for each Topic, which is what you require... Here you would use a Matrix to display results in the report.

Whatever approach you use, the goal is to get a DataSet to SSRS that includes every possible Speaker/Topic combination, even if the actual value for the Topic is NULL, so the report always displays the same Topic columns for each Speaker.