Queries Versus Views - Help Understanding the BIG Picture

[Patrick A]

I have written many Access reports I would now like to make available
via MS SS RS.

Many of my reports rely on queries that relies on 2-3-4 other
queries. Nested queries, sub-queries, whatever you want to call
them. Often this is because of differences between the ways I need
the data (one row, many columns) and the way it is stored (many rows,
one column).

What is the most efficient way to deal with nested queries, sub-
queries, or whatever in MS SS RS?

Create views instead of queries? I don't seem to be able to create
one data set based on another...

Thanks,

Patrick

[Bruce L-C [MVP]]

Your queries should be the same. But, then you throw in the last statement
about one data set based on another. Which is something totally different.

If you want to do master/detail you need to look at using sub reports. You
cannot join two datasets together.

However, complicated queries are just fine. You do not need to create a
view. It doesn't hurt to have a view but I would only do that if it is very
common sql statements.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Patrick A" <par...@stradley.com> wrote in message
news:fd5bcc70-24e5-49e9...@p19g2000vbq.googlegroups.com...

[Patrick A]

Bruce,

Thanks for your response. I'm still confused, though.

Say in the world of Access, I have QRY_AllHrs, which is based on the
combination of SRC_QRY_BillHrs and SRC_QRY_NonBillHrs. Not for Master/
Detail, but to combine two sets of results into one.

In the world of SSRS, what does that equate to? Are you using the
terms "query" and "DataSet" to mean the same thing?

Patrick

[Bruce L-C [MVP]]

I'm not using queries and datasets to mean the same thing.

I'm not familiar with how you would combine two sets of results into one. I
have a feeling that what you want to do can be done but what you need to do
is take what you have below and create a SQL statement that works. It can be
a complicated query with subqueries, joins, unions etc. Essentially, if you
can run the query from query analyzer against SQL Server then you can use it
as a basis for a report. You will want to have parameters in your query in
order to limit the amount of data coming back.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Patrick A" <par...@stradley.com> wrote in message

news:ba131443-fced-4557...@x16g2000vbk.googlegroups.com...

[Patrice]

Hello,

> Many of my reports rely on queries that relies on 2-3-4 other
> queries. Nested queries, sub-queries, whatever you want to call
> them. Often this is because of differences between the ways I need
> the data (one row, many columns) and the way it is stored (many rows,
> one column).

In SRSS I would use the "matrix" (or "tablix" in SSRS 2008) that is able to
create columns (even in Access I would have used a crosstab report). This
kind of report allows to create a column in your report for each value
stored in a database column...

> What is the most efficient way to deal with nested queries, sub-
> queries, or whatever in MS SS RS?

A bit vague. My personal preference is to use do the simplest thing that
works. SRSS 2005 tends to reformat the SQL so complex queries or where
clause or a bit hard to get right. My personal preference is to use stored
procedure and to limit nested query to the minimum.

> Create views instead of queries? I don't seem to be able to create
> one data set based on another...

A view is just a registered query. A dataset is the resultset returned by
the query (basically a recordset).

--
Patrice