Dynamic Tablix

Question

Dynamic Tablix

Charmer

SSChampion

Points: 14903
More actions
May 23, 2016 at 4:58 am

#327249

Hi Friends,
I have been asked to create a report with dynamic display of tables based on the multi-value parameter.
when I pass 3 values to the parameter,
3 tables should be created.
For ex : If I pass, "Germany, UK, Austria, Czech"
Then tablix 1 should be holding Germany
Tablix 2 = UK
and so and so....
Is it possible to create dynamic tables?
Thanks,
Charmer
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 1

I guess my question would be... why do they want to build the 3 tables just to do a report? Personally, I wouldn't let such table creation to occur. I don't know enough about how to create reports using SSRS but I'm pretty sure that you don't need SSRS to be duplicating data in tables that it creates nor do you want to give it or the users privs to do so.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Charmer SSChampion Points: 14903 More actions · Answer 2

Hi Jeff,

It's not just 3 tables bcoz its not fixed...

The tablix creation depends upon how many values we pass.

But as far as I know, even if creating dynamic tablix is possible, when we pass multiple values, all of the tablix would be containing the same result set like you said.

But what users are expecting is that, when we pass multiple values, first tablix should only hold one value information. The second tablix holds 2nd value and so on. I don't even know if that's possible. Seriously I don't know Why are they looking for such crazy report.

But I'm just trying get suggestions from you and our friends in here.

Thanks,
Charmer

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 3

Charmer (5/23/2016)
Hi Jeff,
It's not just 3 tables bcoz its not fixed...
The tablix creation depends upon how many values we pass.
But as far as I know, even if creating dynamic tablix is possible, when we pass multiple values, all of the tablix would be containing the same result set like you said.
But what users are expecting is that, when we pass multiple values, first tablix should only hold one value information. The second tablix holds 2nd value and so on. I don't even know if that's possible. Seriously I don't know Why are they looking for such crazy report.
But I'm just trying get suggestions from you and our friends in here.

Maybe someone knows how to pull it ALL off using only SSRS but my recommendation would be to build dynamic CROSSTABs in stored procedures and use those as a row-source for your reports. Please see the following...

http://www.sqlservercentral.com/articles/Crosstab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47068 More actions · Answer 4

Yes, you can use a "List" control with a subreport.

Create a report with a "Country" parameter that filters your dataset.

Place a table with the columns from your dataset on the report.

Test it works.

Now create a new report and add a List control.

Add a dataset that contains all valid countries (or use the same one as the first report if you need to).

Drop one of the columns from your dataset onto the list to bind it. (or more, and some text labels maybe)

Add a "subreport" control to the list control and configure it to run the first report, passing in the Country as it's parameter.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Charmer SSChampion Points: 14903 More actions · Answer 5

Hi Magoo,

Thanks for the suggestion. I am not able to open the RDL you have attached in here.

I tried with 2008 and 2012 VS as well. May I know what version were these RDL's created?

Thanks,
Charmer

mister.magoo SSC-Forever Points: 47068 More actions · Answer 6

Charmer (5/24/2016)
Hi Magoo,
Thanks for the suggestion. I am not able to open the RDL you have attached in here.
I tried with 2008 and 2012 VS as well. May I know what version were these RDL's created?

Sorry, I forget that not everyone has 2016 😛

Here they are for 2008.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Charmer SSChampion Points: 14903 More actions · Answer 7

Thanks Magoo. you explained very simply but awesome. I will have to play with this according to my requirement.

Once again, Thanks a lot.

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 8

Hi Magoo,

I have a problem now that when we pass multi value through a parameter, it is not generating tables for all values but top one. I used filter operator "= and in" but no luck.

Thanks,
Charmer

mister.magoo SSC-Forever Points: 47068 More actions · Answer 9

So, are you saying that your Main report only has one item/table on it - the first one?

How are you using the parameter to filter? Are you passing it as a parameter to your dataset query? Are you using the SSRS dataset filter?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

amaramoosa SSC Veteran Points: 287 More actions · Answer 10

I agree with this solution. I list report is great for such business scenario as it will take a data set and break it out into the number of occurrence it finds.:-)

Charmer SSChampion Points: 14903 More actions · Answer 11

Yes, Magoo.

Main table with one item with first one and I am using SSRS filter.

Thanks,
Charmer

mister.magoo SSC-Forever Points: 47068 More actions · Answer 12

Charmer (5/25/2016)
Yes, Magoo.
Main table with one item with first one and I am using SSRS filter.

Here's an updated outer report with SSRS filtering for you as an example.

I suspect all that has happened is that you have (0) after the parameter value.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

David Burrows SSC Guru Points: 65138 More actions · Answer 13

Is there a specific reason why separate tables?

Far away is close at hand in the images of elsewhere.
Anon.

Charmer SSChampion Points: 14903 More actions · Answer 14

Yes, that's where I made the mistake.:-)

Thank you , Magoo. I really appreciate all your help.

Thanks,
Charmer