<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Reporting Services
»
Reporting Services 2008 Development
»
Why isn't SP returning correct range of...
Why isn't SP returning correct range of fields to Dataset
Rate Topic
Display Mode
Topic Options
Author
Message
swhedges47
swhedges47
Posted Wednesday, March 20, 2013 11:20 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 13, 2013 3:04 PM
Points: 8,
Visits: 52
I've got a data source that I will need to use as the basis for some 12 to 15 reports and because the table is quite large (several million rows and over 50 columns), I created a SP that given a passed parameter, will return filtered datasets of varying numbers of rows and columns specific to the requirements of each report.
So I thought that for each separate report, I could create a Report Parameter, set it equal to the default value of the result set I wanted to return and then pass that value to a single Shared Dataset.
The problem is that when I create a Shared Dataset in SSRS (R2) and attempt to execute the SP with the passed parameter, I can't seem to get the SP to return the different datasets (it returns the same dataset regardless of what I've set the parameter to), even if I replace the parameter with the actual value of the result set I want returned.
I've set the parameter value every place I can think of (Report Data Parameter, Report Data Dataset, Solution Shared Dataset) but nothing seems to change the dataset that's returned when I refresh the Shared Dataset.
I've also tried using an embedded dataset rather than the Shared Dataset but the same issue persists, I can't get the correct set of fields to return when I refresh the data, even though the SP works without issue when executed in a query window ...
[edit] In a more general sense, I guess I'm asking, "what exactly happens when I hit 'Refresh Fields' in Dataset Properties when I'm using a dynamic SP"?
Post #1433359
Koen Verbeeck
Koen Verbeeck
Posted Wednesday, March 20, 2013 3:45 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 9,378,
Visits: 6,473
Maybe BIDS is using cached data when you run the report.
Make sure all the .data files are deleted before you run the report again.
How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
Member of
LinkedIn
. My blog at
LessThanDot
.
MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1433515
swhedges47
swhedges47
Posted Wednesday, March 20, 2013 4:38 PM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 13, 2013 3:04 PM
Points: 8,
Visits: 52
Unfortunately, that's one of the first things I tried.
Here's the thing though (and this is the reason I added the [edit]), refreshing the fields for a Report dataset (that doesn't have a .data file already created) doesn't create a .data file. So what *does* happen when I refresh the fields? It obviously compiles and evaluates the SP in some fashion, since it does return the list of fields from one of the queries in the SP (specifically #3) ...
Post #1433542
Koen Verbeeck
Koen Verbeeck
Posted Thursday, March 21, 2013 2:13 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 9,378,
Visits: 6,473
swhedges47 (3/20/2013)
Unfortunately, that's one of the first things I tried.
Here's the thing though (and this is the reason I added the [edit]), refreshing the fields for a Report dataset (that doesn't have a .data file already created) doesn't create a .data file. So what *does* happen when I refresh the fields? It obviously compiles and evaluates the SP in some fashion, since it does return the list of fields from one of the queries in the SP (specifically #3) ...
I once had a strange report with shared datasets that only "refreshed" everything when I deployed it to the server.
How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
Member of
LinkedIn
. My blog at
LessThanDot
.
MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1433624
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
