Is it possible to dynamically create SSRS tablix? I have a pretty complex requirement.
I have created tables in which I fill "metadata" for reports, i.e.:
- column names
- row names
- row/column levels (parent/child)
- font colours
- font sizes
- valid from/valid to attributes
- translations
Then, I generate queries from this metadata, and join them on fact tables. So each query returns data as it should.
I generate data in levels, so you always know, in which level you're currently. So, you get data in the following form (I can change it, if that would ease creating of reports):
Row Text | Level 1 Text | Level 2 Text | Level 3 Text | Level 4 Text | Level 5 Text | Value
Basically, SSRS doesn't have to sum anything, it just shows the data. But here's where I get into trouble. I don't know, how to achieve a normal-looking report. I have made 5 column groups (5 is the highest number of levels in any report), and every one is a child to previous one. Row groups get hidden based on the max level of current query, so if the query only has data on level 1, only 1 row is shown.
But here comes the tricky part. I have a report, that has data like this:
If some data is on level 5, and some on level 1 (others everywhere in between), the data on level 1 has 4 empty rows...
So, my report shows a lot of empty cells. Either that, or I show the same text for all 5 levels. Is there a smart way to address this issue? I can even change the whole concept, as I'm still early in development.
EDIT:
Here is one of the exact reports that has to be generated dynamically (it's a public template): ssrs template
