Confusion over datasets, lists, first(

I'm trying to build a report in VS2005. The main dataset (DSCases) for
the report is just "Select * from LawCases where Docket = ?"
It should only return one case. So I dragged a few fields onto the
report body, and VS kept making the expression First(Fields!...)
instead of just Fields!. I don't know why but I don't know much about
SSRS so that isn't too surprising.
I used a list with that dataset as the DataSetName and then I could
get rid of the Firsts. Fine.
However I need some other tables mixed in, so I created another
dataset DSAttorneys (Select * from LawCasesAttorney where Docket = ?),
which should get me the various attorneys who worked on the case. Then
I added a list inside the main list, set the DataSetName to the
DSAttorneys, and VS insists on using First again. And, indeed, I do
get the first attorney, but I need them all. It won't compile without
the First though.
I hoped that the inside list would understand that it would repeat
those fields for each record. I looked at filters and grouping but
can't get it to work as I expect.
Do I need to use subreports instead of a list?As you saw you can't just put a field on the layout surface. It has to be
bound to a list or a table. If you know there is only going to be one record
returned then drag and drop the fields and use the First aggregate. Then
drag the list over (or the table object, whichever works best). Otherwise
use a subreport.
What is happening is RS does not know it is a single row, how can it. So for
a list within a list it would need to be joining the data (like a
master-detail). For instance a list of orders and a list of items for each
order. When you do a 1 to many or a 1 to 1 you need to use subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:0i3pr39o6so20jdih2k8p1lrbpsep6mt37@.4ax.com...
> I'm trying to build a report in VS2005. The main dataset (DSCases) for
> the report is just "Select * from LawCases where Docket = ?"
> It should only return one case. So I dragged a few fields onto the
> report body, and VS kept making the expression First(Fields!...)
> instead of just Fields!. I don't know why but I don't know much about
> SSRS so that isn't too surprising.
> I used a list with that dataset as the DataSetName and then I could
> get rid of the Firsts. Fine.
> However I need some other tables mixed in, so I created another
> dataset DSAttorneys (Select * from LawCasesAttorney where Docket = ?),
> which should get me the various attorneys who worked on the case. Then
> I added a list inside the main list, set the DataSetName to the
> DSAttorneys, and VS insists on using First again. And, indeed, I do
> get the first attorney, but I need them all. It won't compile without
> the First though.
> I hoped that the inside list would understand that it would repeat
> those fields for each record. I looked at filters and grouping but
> can't get it to work as I expect.
> Do I need to use subreports instead of a list?|||On Wed, 20 Feb 2008 15:06:49 -0600, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>As you saw you can't just put a field on the layout surface. It has to be
>bound to a list or a table. If you know there is only going to be one record
>returned then drag and drop the fields and use the First aggregate. Then
>drag the list over (or the table object, whichever works best). Otherwise
>use a subreport.
>What is happening is RS does not know it is a single row, how can it. So for
>a list within a list it would need to be joining the data (like a
>master-detail). For instance a list of orders and a list of items for each
>order. When you do a 1 to many or a 1 to 1 you need to use subreports.
Thanks. There will be quite a few different detail records for each
master (at least four other tables) so I can't even think of how to
join them all. Subreports certainly seems the way to go. I'll give it
a shot.