How can I use one table for mutliple datasets?

Hi All,
I have a table and the dataset it populating all but four cells. These
four cells I want to populate using data from a different dataset (I
couldn;t figure out a query to do what I needed). The main dataset
returns a value I can use as a parameter in the second dataset. Is this
clear? Is this possible. I'm new to this type of reporting so please
bear with me. Thanks!
Kind regards - FredYou can create a sub report and put that in a cell of the table object. I do
this all the time. Sub reports are the only way to link two datasets
together.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<fblock@.tellurian.com> wrote in message
news:1164903187.594743.309230@.l39g2000cwd.googlegroups.com...
> Hi All,
> I have a table and the dataset it populating all but four cells. These
> four cells I want to populate using data from a different dataset (I
> couldn;t figure out a query to do what I needed). The main dataset
> returns a value I can use as a parameter in the second dataset. Is this
> clear? Is this possible. I'm new to this type of reporting so please
> bear with me. Thanks!
> Kind regards - Fred
>|||Thanks Bruce...
I see that I cannot merge cells vertically and the sub-report would
need to be two cells (one over the other). Have you a trick for this.
Also - why is it that my dataset field is "First(field_name)"... Why
the "First()"? The VS IDE was complaining of an aggregate which "sort
of" makes sense but my dataset was onle able to return one row (it
used: TOP 1).
Thanks again!
Kind regards - Fred|||Your subreport can have multiple cells. You create your subreport using the
table control with one cell over the other. You embed it into a single sell
and that cell grows vertically. However, if you are wanting the bottom cell
to line up with the second detail row you can't do it. It will expand the
cell you put it in and then your second detail row of the master will show.
You could have two subreports but remember, it is calling this subreport for
every row.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frblock" <frblock@.yahoo.com> wrote in message
news:1164915731.670399.153450@.79g2000cws.googlegroups.com...
> Thanks Bruce...
> I see that I cannot merge cells vertically and the sub-report would
> need to be two cells (one over the other). Have you a trick for this.
> Also - why is it that my dataset field is "First(field_name)"... Why
> the "First()"? The VS IDE was complaining of an aggregate which "sort
> of" makes sense but my dataset was onle able to return one row (it
> used: TOP 1).
> Thanks again!
> Kind regards - Fred
>|||frblock,
You asked why you needed to have an aggregate function in the field
expression. Perhaps this will help.
When you create a report through the report wizard, it automatically
creates a table on your report. That table is actually "bound" to a
dataset. This binding allows you to reference fields from that dataset
by just saying "=Fields!fieldName.Value". This binding also tells the
table to create 1 detail row for each datarow in the dataset.
Microsoft included the ability to reference other datasets from a table
that has already been bound to a different dataset, but there is no way
to "relate" the rows from the 2 datasets. You would need a sub-report
to do this. As a result, you have to use a scalar function to ensure
that you get only 1 row and 1 column back, and you also have to
explicitly state which dataset you want to use. The formula looks like
this: "=Sum(Fields!fieldName.Value, "Dataset1").
If you are trying to reference fields that belong to the dataset that
is already assigned to your table, in the Edit Expression window, you
should select the "Fields (Dataset1)" option. If you are trying to
reference fields from other datasets, then you choose "Datasets". The
default aggregate function for integer fields is Sum, and the default
for string fields is First. You can change the default. For example, if
you had a query that said "SELECT COUNT(*) FROM tableName", you could
use First, Min, Max, Avg, or Sum. My opinion, just stick with First.
HTH,
Josh
frblock wrote:
> Thanks Bruce...
> I see that I cannot merge cells vertically and the sub-report would
> need to be two cells (one over the other). Have you a trick for this.
> Also - why is it that my dataset field is "First(field_name)"... Why
> the "First()"? The VS IDE was complaining of an aggregate which "sort
> of" makes sense but my dataset was onle able to return one row (it
> used: TOP 1).
> Thanks again!
> Kind regards - Fred