Solved

SSRS DATASET FROM STORED PROCEDURE

Posted on 2011-09-08
3
311 Views
Last Modified: 2012-05-12
Have a stored procedure that massages alot of data.  It returns two sets of records.  I would like to use the first set for the details of the main report and the second set as a subreport for those main report details.  I could separate it into 2 sprocs, but would rather not since there's a lot hits to the server, why run it twice?  When I refresh the dataset it only sees the first set of results.  Anyway to make this work as is or should I just separate it?
0
Comment
Question by:MariaHalt
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
dbaSQL earned 500 total points
Comment Utility
I've almost always had to separte the two datasets, and call the 2nd from the first, as a subreport.
http://www.codeproject.com/KB/reporting-services/SSRS_Part2.aspx

Of course, it is more optimal to collect all the data in one dataset, because it reduces server round trips, and simplifies the data processing.  Again, I've almost always had to do a second dataset with a subreport, but maybe you can try it differently.  First create your table that contains both sets of records, but don't detail it -- I think you want to put it into groups.  Then, you just filter the child table to the parent table, on the data return.

This is a good read on using the lookup function to retrieve from two datasets:
http://blog.datainspirations.com/2010/03/19/sql-server-2008-r2-reporting-services-look-up-look-down-look-all-around-part-i/





0
 

Author Closing Comment

by:MariaHalt
Comment Utility
I ended up joining my 2 datasets and used grouping and conditional row suppression to make it appear the way they would like it to appear.

Also, thanks for the tip about Lookup function...never used it but now I will.
0
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
Excellent!  I would love to see your solution. I believe my email is in my profile, if you don't mind sharing.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction: This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes. It demonstrates how to build a report using SQL Ser…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now