January 21, 2013 at 1:44 pm
Hello Everyone. I'm attempting to connect three different datasets together and trying to see if its possible. I'm able to make a connection between two datasets easily but find it difficult to get the third one to connect. The lookupset that I currently have is below. What I'm trying to achieve is have my Task report check to see if there is a percentage for the Tertiary Task table and if there is then it uses the Tertiary Task percentage but if the Tertiary Task percentage is 0 then it will use the Subtask percentage and if that is also 0 then it will use the percentage entered within the Task table. Right now the below statement works within my Subtask report well but when it gets to the Task report it can't make that connection and doesn't know that the Tertiary Task is connected to the Task. It only knows to make a connection to the Subtask or Project table before it. If anyone knows how to make that third connection please let me know. Thanks!
=IIF(Code.SumArray(LookupSet(Fields!ID.Value, Fields!SubTask.Value, Fields!PercentComplete.Value, "TertiaryTasks")) > 0, Code.SumArray(LookupSet(Fields!ID.Value, Fields!SubTask.Value, Fields!PercentComplete.Value, "TertiaryTasks"))/LookupSet(Fields!ID.Value, Fields!SubTask.Value, Fields!TertiaryTask.Value, "TertiaryTasks").Length, "0")
Projects
Project Name
ID
Task
ProjectID(Connects to ID in Projects)
Task
ID
Percent Complete
SubTask
ProjectID
Task (Connects to ID in Task)
SubTask
ID
Percent Complete
TertiaryTask
ProjectID
Task
SubTask (Connects to ID in SubTask)
TertiaryTask
ID
Percent Complete
January 22, 2013 at 3:36 pm
Assuming all the data comes from one database, one not just return all the data in one dataset and then you don't have to jump through hoops to get it to display correctly. Then you might even be able to handle the percentage value in the query and only have to select the correct column in the report.
Granted I don't know enough about the report to know that this would work, but my preference is to have as few database calls as possible in a report, so if I can do the work in one dataset and handle grouping in the report I do.
It would be helpful if you could share the RDL then we could see what you are doing more clearly and make better suggestions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 23, 2013 at 8:06 am
Thanks a lot Jack. That actually worked. I just created a query within Access that pulled information from all the tables, then I imported that query within report builder and was able to make the connections effectively. I was thinking around it instead of looking at a simple picture. Thanks again!
January 23, 2013 at 10:46 am
Great, Glad I could help.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply