Hi, I have two datasets. They both have a common Key (WeekNo), but Class details and Achievements have no inter-relation. dataset 1 WeekNo, ClassName, Number of students Week1, Maths, 12 Week1, English, 20 Week2, Science, 14 Week3, Maths, 9 dataset 2 WeekNo, Achievement Week1, School won the national competition Week2, A teacher won an award Week2: Soccer team won an inter-school sports competition I would like to show this data grouped by the WeekNo across two datasets, as below. Week1 Maths, 12 English, 20 * School won the national competition Week2 Science, 14 * A teacher won an award * Soccer team won an inter-school sports competition Week 3 Maths, 9 The report will be exported to Excel, so I think the use of subreports is not a good idea. I would appreciate if anyone could give me ideas to how to implement this with SSRS. Thanks

Hi There Your tablix is always bond to one dataset at a time but if you are using SSRS 2008 you can use lookup function to get the data from another dataset. You can only use lookup when there is one-to-one relationship between source and destination. If there is 1 to many relationships you need to use LookupSet instead. The syntax for lookup function is look like this Lookup(Fields!Code.Value, Fields!Code.Value,Fields!toatlHotcount.Value,"Dataset2") Lookup(Fields!Code.Value, Fields!Code.Value,Fields!toatlHotcount.Value,"Dataset2") Where the first Fields! Code.Value is your first dataset field ("Dataset1") and Fields! Code.Value is the field in the second dataset ("Dataset2") and both have one to one relationship with each other Fields! toatlHotcount.Value is the value which you are getting from second dataset ("Dataset2") If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Syed, Lookup will not work since it returns only one value for the given key. LookupSet may work, but I can't find how to bind LookupSet's output to a table. In the actual report, there will be few more columns besides the Achievement (date achieved the achievement, $$ received, points received etc), and I would like to show them in a table.

Hi there I think you do not need two dataset actually, to work around your problem you can create a temporary table inside your stored procedure and insert all your dataset1 fields values in that temporary table and create extra columns for your dataset2 You can get output your second dataset (dataset2)field in the same stored procedure or create a wrapper procedure that then update these values into extra fields inside your temporary table. Then return the values from your temporary table. This is very common approach If you have any questions please do ask Many thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

Hi Syed, If I understood what you meant, the combined dataset will look like below. WeekNo, ClassName, Number of students, Achivement Week1, Maths, 12, School won the national competition Week1, English, 20, null Week2, Science, 14, A teacher won an award Week2, null, null, Soccer team won an inter-school sports competition Week3, Maths, 9, null How do I show the Achivement column in a separate table below the first tables that lists the class details (to get the report format I showed above)? Thanks

Hi There If this is what you after then this can be achieved using one dataset there is bit of juggling with SSRS J So create a detail row inside your tablix and add a group by right click on details row and add group (parent group) and put your class name as group and add click on group header check box. Please repeat this step for your week (add as a parent group of class name)now you have two group and a detail row please delete the detail row and right click on your class group and add group adjacent below and put your achievement related column in there You are done. If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. SELECT dbo.Achievement.WeekNo, dbo.Achievement.Achievement, dbo.WeekDetails.ClassName, dbo.WeekDetails.[Number of students], dbo.Achievement.Amountreceived, dbo.Achievement.PointReceived FROM dbo.WeekDetails INNER JOIN dbo.Achievement ON dbo.WeekDetails.WeekNo = dbo.Achievement.WeekNo

Thanks Syed for the detailed explanation! Is there a possibility that you can attach the RDL file here for me to have a look? I quite don't understand how setting the "ClassName" as the parent group of "Achievement" get the desired output. Why doesn't it cause "Achivements" to be listed against its parent ie. each "ClassName"? The output you produced is exactly what I want, however, I don't understand why it won't produce the output as below. Week1 Maths, 12 School won the national competition English, 20 The above is not what I want, but since the "Achievement" "School won the national competition" is arbitrary linked to "Maths, 12" row in the dataset, why wouldn't it produce the output above?

Hi There This is actually bit of juggling with SSRSJ. I would not be able to attach the file here but if you would like me to send you on your email address please flick an email to me at sqazafi@hotmail.com If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Thanks Syed. I've just sent an email.

Hi There I have sent the file to your email address; I hope you find it helpful If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Hi There For more clarification "ClassName" is not parent group of "Achievement" "Achievement" is the group adjacent below to the Classname If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Hi Syed, Can you point me to the right direction where I can get some ideas of how to combine the two resultsets into one temporary table as you suggested? The INNER JOIN you used in the example will not work in general when there are multiple classes and achievements in one week.

Hi There Can you please give me a specific example when your join will not work? If you would like to use temporary table approach you can first dump your first dataset into temporary table and create extra field for your second data set later on update the temporary table with second dataset fields. But if you please tell me the exact scenario then I will be able to help you out If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Hi Syed, ".......later on update the temporary table with second dataset fields." When the second result set is being updated to the temporary table, update logic has to be complex and there may be not enough rows. In the above example, when you try to update with Week1's Achivement to Week1's Class details, there are two records in the Class Details, but only one Achivement. So, the update logic has to have some additional logic to restrict update to only one record. Secondly, when the Week2's 2 Achivements are going to be updated, there is only one Class Detail record. So, a new record has to be inserted.

Hi James_Sm Thanks for your posting. Yes that right you have to do some logic to get your one dataset right. But If you have some sample data send it to me and I will help you out with that. But I am still not sure in above example where your inner join will not satisfy your requirement. Can you please give me some specific example so that I understand your data structure well? If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Hi Syed, "....But I am still not sure in above example where your inner join will not satisfy your requirement....." dataset 1 WeekNo, ClassName, Number of students Week1, Maths, 12 Week1, English, 20 Week1, Science, 7 Week1, Sports, 16 Week2,..... ............ ............ dataset 2 WeekNo, Achievement Week1, School won the national competition Week1, A teacher won an award Week2,................ .................. The query: SELECT * FROM dbo.WeekDetails INNER JOIN dbo.Achievement ON dbo.WeekDetails.WeekNo = dbo.Achievement.WeekNo When joined by the week number, there will be total of 8 (=4 from table 1 x 2 from table 2) records for "Week1"

Hi James_Sm Thanks for youre posting again. Yes that right and your Inner join will produce result like this and your report shows result like as shown in the picture? Is this not what you after? SELECT dbo.Achievement.WeekNo, dbo.Achievement.Achievement, dbo.WeekDetails.ClassName, dbo.WeekDetails.[Number of students], dbo.Achievement.Amountreceived, dbo.Achievement.PointReceived FROM dbo.WeekDetails INNER JOIN dbo.Achievement ON dbo.WeekDetails.WeekNo = dbo.Achievement.WeekNo If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Hi Syed, Thanks for taking so much effort to explain this to me. Even though there are duplicates in the dataset, I take it that the tablix grouping removes them? Thanks

Hi James_Sm Yes that's right. Row group will remove those duplicate automatically. I am really glad that your problem has been resolved and we will keep in touch if you need any further help Many thanks Syed Qazafi Anjum

Thanks Syed. Have a good day.