SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS 2008 report linked to multiple datasets


SSRS 2008 report linked to multiple datasets

Author
Message
ketan.mistry
ketan.mistry
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 12
Hi,

I have a like a mail merge (2 page report) linked to 4 datasets that have just the one parameter (learner_id) for each dataset; which is working fine. The reports is built up using Text boxes (for all items in dataset1) and a 3 tablix's (for datasets2-4).

However i now want to take it up another level and bulk print them; so want to change the paremeter to say when the learner records were created (so print out all records created on a certain day), what would be best way about going to do this.

The results in the datasets will vary per learner.

dataset1 - this will alway have one row per learner
dataset2 - the results in this will vary depending on whats in the db
dataset3 - the results in this will vary depending on whats in the db
dataset4 - the results in this will vary depending on whats in the db

Any help and suggestions will be greatly appeciated

Regards
Ketan
geoff5
geoff5
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 543
Without a bit more about what columns are in the datasets it is difficult to have confidence that one particular suggestion is the best approach. My first thought is to create a filter on each dataset using the newly added date parameter. This is assuming that each dataset has a datetime column with the "date created" information in it.
ketan.mistry
ketan.mistry
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 12
The data in the fields are pretty much notes, integers, a few chars, and yes the date created is in every data set. I was thinking to use the date as a the only parameter, but how do I tell it to group each data set by learners. As I was only running it individually before so it worked fine, but now I want to bulk print x amount depending on many are created on a certain day.

Basically this report report is like a learner portfolio.

Also re the datasets

dataset1 - this will alway have one row per learner
dataset2 - the row count of results per learner in this will vary depending on whats in the db
dataset3 - the row count of results per learner in this will vary depending on whats in the db
dataset4 - the row count of results per learner in this will vary depending on whats in the db
geoff5
geoff5
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 543
If the datasets are populated by basic queries, it would be easiest to re-write the queries into one uniform set of results, probably through the use of UNION to "stack" one result set with another. However, if the datasets are populated by stored procedures and the data cannot be pulled together prior to being added as a dataset, then your best bet would probably be to create subreports within a List tablix and set the subreports to be filtered to the corresponding learner ID in the List.

Without a subreport I know of no way to incorporate data from a "foreign" dataset except by use of the "Lookup," "LookupSet," or "MultiLookup" functions; however, these functions will not return data in a way that could be transformed into full-blown matrix report objects.
ketan.mistry
ketan.mistry
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 12
Many thanks for you responses.

Just another question, if I have my new parameter being the date created how does ssrs know to link each Learner together?

I'm sure I'm being a big dense here, an hope all will become a lot clearer when back at my computer early tomorrow.

Thanks
Ketan
geoff5
geoff5
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 543
Assuming you are going the subreport method, when you create the subreport (which is developed first as a standalone report before being added as a subreport to the final design), add a parameter for the learner ID. Then when you add the subreport to the main report, you will set the learner id parameter of the subreport to equal the learner id field value in the main report.
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