Data will not show on report preview

I have a proc that combines the results from 2 queries. I can see all of the
data in QA and the Data tab of SRS there are no issues with that part. When
I preview the report only the second part shows up, nothing from the first
query. I have started over a few times, still the same problem. Any ideas'
ThanksMake sure your SP returns only one resultset. RS does not handle multiple
resultsets. I.e. you should only have one select statement. If you have
multiple it will only use the first one.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
> I have a proc that combines the results from 2 queries. I can see all of
the
> data in QA and the Data tab of SRS there are no issues with that part.
When
> I preview the report only the second part shows up, nothing from the first
> query. I have started over a few times, still the same problem. Any
ideas'
> Thanks|||There is only one select statement, it joins 2 temp tables. The record set
looks fine in SRS Data tab and in QA, just not in the actual report.
"Bruce L-C [MVP]" wrote:
> Make sure your SP returns only one resultset. RS does not handle multiple
> resultsets. I.e. you should only have one select statement. If you have
> multiple it will only use the first one.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
> > I have a proc that combines the results from 2 queries. I can see all of
> the
> > data in QA and the Data tab of SRS there are no issues with that part.
> When
> > I preview the report only the second part shows up, nothing from the first
> > query. I have started over a few times, still the same problem. Any
> ideas'
> > Thanks
>
>|||Try it using the generic query designer in the data tab. The code used there
is exactly what is used during report preview time.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:913433BC-A552-4559-8CD8-7FC508719402@.microsoft.com...
> There is only one select statement, it joins 2 temp tables. The record set
> looks fine in SRS Data tab and in QA, just not in the actual report.
> "Bruce L-C [MVP]" wrote:
>> Make sure your SP returns only one resultset. RS does not handle multiple
>> resultsets. I.e. you should only have one select statement. If you have
>> multiple it will only use the first one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "darwin" <darwin@.discussions.microsoft.com> wrote in message
>> news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
>> > I have a proc that combines the results from 2 queries. I can see all
>> > of
>> the
>> > data in QA and the Data tab of SRS there are no issues with that part.
>> When
>> > I preview the report only the second part shows up, nothing from the
>> > first
>> > query. I have started over a few times, still the same problem. Any
>> ideas'
>> > Thanks
>>|||This should work. One thing to make sure of with your SP is that you do not
explicitly drop your temp tables. Just let them fall out of scope. Also,
have your select be the last statement.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:913433BC-A552-4559-8CD8-7FC508719402@.microsoft.com...
> There is only one select statement, it joins 2 temp tables. The record set
> looks fine in SRS Data tab and in QA, just not in the actual report.
> "Bruce L-C [MVP]" wrote:
>> Make sure your SP returns only one resultset. RS does not handle multiple
>> resultsets. I.e. you should only have one select statement. If you have
>> multiple it will only use the first one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "darwin" <darwin@.discussions.microsoft.com> wrote in message
>> news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
>> > I have a proc that combines the results from 2 queries. I can see all
>> > of
>> the
>> > data in QA and the Data tab of SRS there are no issues with that part.
>> When
>> > I preview the report only the second part shows up, nothing from the
>> > first
>> > query. I have started over a few times, still the same problem. Any
>> ideas'
>> > Thanks
>>|||I started all over again, this time I created the parameter dataset first
then added the call to the stored proc. This worked, no clue as to why.. as
to the temp tables I have always droped them after the last select with no
issues.
thanks all ;)
"Bruce L-C [MVP]" wrote:
> This should work. One thing to make sure of with your SP is that you do not
> explicitly drop your temp tables. Just let them fall out of scope. Also,
> have your select be the last statement.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> news:913433BC-A552-4559-8CD8-7FC508719402@.microsoft.com...
> > There is only one select statement, it joins 2 temp tables. The record set
> > looks fine in SRS Data tab and in QA, just not in the actual report.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Make sure your SP returns only one resultset. RS does not handle multiple
> >> resultsets. I.e. you should only have one select statement. If you have
> >> multiple it will only use the first one.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> >> news:4AE2623F-3AC3-456C-BC85-72B228C0C1FD@.microsoft.com...
> >> > I have a proc that combines the results from 2 queries. I can see all
> >> > of
> >> the
> >> > data in QA and the Data tab of SRS there are no issues with that part.
> >> When
> >> > I preview the report only the second part shows up, nothing from the
> >> > first
> >> > query. I have started over a few times, still the same problem. Any
> >> ideas'
> >> > Thanks
> >>
> >>
> >>
>
>

Data Warehousing:Best way to write SP?

I'm writing a set of queries designed to test the data quality of our data warehouse at the fact level. The intent is to ensure that there are no keys at the fact level that do not exist at the dimension level.

I'm dealing with 8 fact tables, 8 dimension tables and 9 keys. I'm currently doing:

SELECT COUNT(DISTINCT key)
FROM t_fact
WHERE key NOT IN
(
SELECT DISTINCT key FROM t_dimension
)

If I do one of those for each key-fact table combo, there are about 50 queries in total. Not every key exists in every fact table.

I'm a Stored Procedure novice. What is the best way to check all of the fact tables, aside from running 50 counts with subqueries? If I run the queries one fact table at a time, it will take about 30 minutes. I've tried to run one query per fact table, by counting all keys, and doing a subselect to each dimension table, but got misleading results.

Any tips will be greatly appreciated. Abandoning data warehousing isn't a current option!

MikeIn Oracle I would find missing keys like this:

SELECT key FROM t_fact
MINUS
SELECT key FROM t_dimension

BTW, why not use a foreign key constraint to ensure all fact keys are based on dimension keys?