Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple Tablix with One to Many Relationships


Multiple Tablix with One to Many Relationships

Author
Message
bhollida
bhollida
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 13
Hello! I am hoping someone can help me with this - I am quite certain it can be done, I just haven't run across this particular kind of report request yet!

I have 2 datasets that are tied together by their unit_id. In table 1 you have basic yes/no info about specific types of pension plans offered by a company and a bit field called bridgePlan. If this field (bridgePlan) is true, there will be corresponding records in table 2 that gives details of the bridge plans.

The task in front of me seems quite simple, but I haven't worked it out. In the first tablix I need to display the basic info from Table 1 (dataset1). No problem, I've done this many times. After that, however, I need to display one (or several) more tables with the information from Table 2 (dataset2). There is no limit on how many records in Table2 that actually correspond. It is unlikely that it will ever be more than 5, so I want to bank on 10 just to cover my bases.

Table1
unit_id, year, bridgePlan
1,2013,true
2,2013,false
3,2013,true

Table2
ppid, unit_id, year, bridgetPlan/ppinfo
139,1,2013,test
140,1,2013,test2
175,3,2013,test3

Thoughts on how I should approach this? Should I simply try to concatenate all of the information for a specific unit into one long record so that there is only one record returned for each unit/year? I am going to attach a word document that shows the tablix layout requirement given to me. Essentially, the top tablix will always be visible. The bottom one would be visible if bridgetPlan = true for a particular unit/year and it should cycle through each record from dataset 2 .. so it could be there multiple times but with different information.

Thank you in advance for your help.

Bethany
Attachments
Doc30.docx (9 views, 260.00 KB)
bhollida
bhollida
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 13
Anyone have any advice? I would really like to knock this out today if at all possible. :/
PB_BI
PB_BI
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1291 Visits: 2407
You could achieve this by having your second tablix as a sub report. You could have a (hidden) parameter in the sub report for say Unit_ID that is passed into the sub report by the main report.
You can then toggle the visibility of the sub report based on your needs.
More on sub reports with parameters here

I hope that helps :-)




I'm on LinkedIn
bhollida
bhollida
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 13
Thank you for the suggestion, SSC Veteran. I appreciate it. I wondered if it was possible with a subreport, but I wasn't sure if there were 3 records for a particular unitID, if it would replicate that subreport 3 times .. essentially like a loop?!

I will follow your link provided and see what I can come up with.

Thanks again!
B
PB_BI
PB_BI
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1291 Visits: 2407
No it won't loop, assuming you are only showing one unit id record in your first tablix then the sub report you set up will show the many records of that unit id.




I'm on LinkedIn
bhollida
bhollida
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 13
I am definitely showing one unitID in the first tablix (with one record)... and just as an example .. the 2nd dataset for that same unitID has 3 records. Each of those 3 records need to be parsed out into a tablix that has approximately 40 fields in it. So the first record parsed out into the tablix, then under that, the 2nd record parsed out into the same tablix format ..etc.
Lidou123
Lidou123
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 378
Hi bhollida,

How are U doing ?

I have the same problem for a SSRS report.

Did U find a solution ?

I tried to use a subreport in a tablix but it can't loop for each tablix group.

Any idea ??
Lidou123
Lidou123
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 378
I think that may be a tablix in a tablix can solve the problem ??

I am waiting for your answer.
bhollida
bhollida
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 13
Yes, indeed it did. I meant to post what worked for me but I got distracted and moved on. So Sorry. I had to put a tablix, inside one row of a tablix within a subreport. It worked very well.

Bethany
Lidou123
Lidou123
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 378
Hi,

Your solution worked for me.

Thank U.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search