sql server reporting services: Fields question -- sql server reporting services

You're in the

sql server reporting services

group:

Fields question

G. Dean Blake
8/18/2004 5:39:35 PM

sql server reporting services:

I have two datasets in my report with some common column names. If I drag
Col1 from Dataset2 onto a detail line in a table, the formula looks like
this:
=First(Fields!Col1.Value, "DataSet1") (I don't know why it defaults to
First on a detail line.)

I want to print every row so I have to get rid of the 'First'. The docs say
it should say Fields!Col1.Value but the docs don't talk about qualifiying by
dataset. I have tried Fields!Col1.Value, "DataSet2" but that doesn work
either. What is the proper way to qualify by dataset?

Thanks,

G.

Re: Fields question

Ravi Mumulla (Microsoft)
8/18/2004 6:02:17 PM

Switch to the second dataset from the dataset dropdown Fields window and
drag the fields into the table detail cells. This should add the fields like
you're describing.

You can only add fields (without aggregation) from one dataset into details
section of a table (or any other data region for that matter.) If you want
data from two datasets in a table detail section, you'd have to do a SQL
join or a UNION to pull the data into one dataset.

--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services

This posting is provided "AS IS" with no warranties, and confers no rights.
[quoted text, click to view]

Re: Fields question

G. Dean Blake
8/19/2004 9:09:28 AM

I am not trying to put fields from two different datasets into the detail
section.

I am simply trying to drag a field from dataset1 into the detail section in
the first column. When I do that it the cell has:
=First(Fields!Col1.Value, "DataSet1") I don't want it to say FIRST of course
because its the detail section. I can't understand why it insists on saying
FIRST. If I change it to say =Fields!Col1.Value I get a diagnostic that
says: The value expression for the textbox 'Col1' refers to the field'Col1'.
Report item expressions can only refer to fields within the current data
scope.

I don't know what it is trying to say.

DataSet2 is being used for the heading portion and that seems to work fine.
What determines "current dataset scope" It seems that that scope is
dataset2??

G

[quoted text, click to view]

Re: Fields question

Bruce Johnson [MSFT]
8/19/2004 10:49:53 AM

Data regions, in SQL Server 2000 Reporting Services, can only be bound to a
single data set with once exception: All secondary data references must be
contained in an aggregate function with the dataset specified. For example,
First(=Fields!<SomeField>.Value), "<SomeDataSet>"), is allowed.

A data set can be get bound to a table in a couple of ways:
* A new table is automatically bound to dataset of the first field
dragged into it.
* Setting the data explicitly using the table properties dialog.

In your case it sounds like you placed a field from dataset 2 in the table
first.
If that is correct, then any field you add from dataset 1 must be in an
aggregate and have its dataset specified.

If you need to use fields from both datasets in the same table you will need
to join them in the query.
Some of the tools available to you are joins, unions, openrowset, or linked
servers.

--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services

This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

Re: Fields question

Ravi Mumulla (Microsoft)
8/19/2004 10:56:11 AM

Even though you didn't drop any fields from Dataset2 into a detail cell of
the table, you may have already bound Dataset2 to the table. This will show
up in the Dataset Name dropdown in the table properties dialog. If that is
the case, yes, any fields that you want to drag and drop into detail cells
of the table will show up as aggregates. You'll need to clear the Dataset
selection from the Dataset Name dropdown in the table properties dialog at
which point you'll get the desired results.

--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services

This posting is provided "AS IS" with no warranties, and confers no rights.
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn