Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using multiple datasets in SSRS 2005

Posted on 2011-03-01
10
Medium Priority
?
1,094 Views
Last Modified: 2012-05-11
I'm trying to get a report to work with multiple datasets.  The datasets do not have a direct tie to each other and come from different databases.   But the totals from one dataset need to be used in a subsequent dataset.  One dataset with goals, a second dataset has gifts and 3rd dataset has income and expenses.  I need to take X % of the gift dataset and add it into the income and expense dataset.  Then I need to get a grand total and determine the % of goal using the first dataset.  Once I start adding different datasets and data regions the report only runs if I use first or sum on each of the fields.  Some of these can't be summed - the goals for example have to be listed separately by FY.  I'm not sure what I'm doing wrong.  I know I can't use sub reports because I can't carry totals from them onto the other data regions.  Any input?
0
Comment
Question by:cindyfiller
  • 4
  • 3
10 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 35012058
I would create SQL Views if posible for each "data set" to be in SSRS and use queries in your SSRS to agregate data based on these views as you described above.
0
 
LVL 10

Expert Comment

by:itcouple
ID: 35012133
Hi

Multiple dataset are not designed to talk to each other. At least not in 2005 it is slightly better with 2008 and even better with R2.

I think you can peform "lookups" on different datasets in 2005 but you need to type (hard-code) filter critieria they cannot be based on another field from a different dataset (at least I haven't found a way to do that).

If you want better answer I suggest to break your tasks into smaller ones and post them one by one there are bigger chances of getting more precise and quicker replies.

Also ideally you wouldn't have to do that in SSRS as this kind of tasks (data integration) should be performed before you use SSRS but that is ideal solution which is not always possible.

Hope that helps
Emil

0
 

Author Comment

by:cindyfiller
ID: 35012245
Some of the datasets are based on views.  I'm not sure how easy it would be to try and make all of the data look the same so it can be one aggregated view.  If possible, I'd like to get the multiple datasets to work.  Is this possible??
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 10

Expert Comment

by:itcouple
ID: 35014649
Hi

Creating views would be much simpler than working with multiple datasets in SSRS 2005. You mentioned that your queries come from different databases; are they all on the same server?

Regards
Emil
0
 

Author Comment

by:cindyfiller
ID: 35017114
Yes they are on the same server and they are all sql db's.  It does sound like I am going to have to go about this report a different way.  I'd always heard you could do the different datasets, but I guess the key is whether they will have data that has to be used in multiple data regions.  I've used different datasets for parameters many times, but had hoped 2005 would allow me to build out this report using this feature.   Thanks for confirming what I was fearing.  At least now I won't waste time trying to do something it won't do!
0
 
LVL 10

Accepted Solution

by:
itcouple earned 2000 total points
ID: 35018006
Hi

SSRS supports multiple datasets but usually it is not so much related data. You should aim to answer the question by providing one dataset if possible. Remember also that SQL will be usually much faster preparing the data then using SSRS. I usually use multiple dataset only for parameters or dynamic configuration, usually my reports are one main dataset and if I build a dashboard then multiple datasets but not related to sharing the same level of data.

Are you ok doing the view from multiple databases?

Regards
Emil
0
 

Author Comment

by:cindyfiller
ID: 35019319
I think I'll end up having to do unions for each of the different types of data I'm pulling in.  I've never done one, but I think the trick is to have the same exact fields in each section that I'm going to union.   Again, thanks!
0
 

Author Comment

by:cindyfiller
ID: 35224387
I awarded points and closed this case - at least I tried to.  It obviously didn't work.  Can you allow me to do this?  The person definitely deserves the points.
0

Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Chart-Optimization-Tips.html).  This article explained how …
Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question