Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple Tablix with One to Many Relationships Expand / Collapse
Author
Message
Posted Monday, November 18, 2013 1:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 6, 2014 10:00 AM
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


  Post Attachments 
Doc30.docx (9 views, 260.45 KB)
Post #1515337
Posted Tuesday, November 19, 2013 6:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 6, 2014 10:00 AM
Points: 5, Visits: 13
Anyone have any advice? I would really like to knock this out today if at all possible. :/
Post #1515523
Posted Tuesday, November 19, 2013 9:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:40 AM
Points: 451, Visits: 847
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
Post #1515657
Posted Tuesday, November 19, 2013 10:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 6, 2014 10:00 AM
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
Post #1515666
Posted Tuesday, November 19, 2013 10:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:40 AM
Points: 451, Visits: 847
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
Post #1515672
Posted Tuesday, November 19, 2013 10:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 6, 2014 10:00 AM
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.

Post #1515677
Posted Tuesday, November 26, 2013 2:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:18 AM
Points: 69, Visits: 281
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 ??

Post #1517825
Posted Tuesday, November 26, 2013 2:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:18 AM
Points: 69, Visits: 281
I think that may be a tablix in a tablix can solve the problem ??

I am waiting for your answer.
Post #1517831
Posted Wednesday, November 27, 2013 6:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 6, 2014 10:00 AM
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
Post #1518047
Posted Thursday, November 28, 2013 3:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:18 AM
Points: 69, Visits: 281
Hi,

Your solution worked for me.

Thank U.

Post #1518310
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse