Creating a Calculated Field

I want to create a calculated field, but I can't get the expression to
work. All I want to do is the sum of field 1 and field 2. This must
be easy, but I just can't see it?On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> I want to create a calculated field, but I can't get the expression to
> work. All I want to do is the sum of field 1 and field 2. This must
> be easy, but I just can't see it?
You should be able to use something like this:
=Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
Field2Name.Value, "DataSetName")
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> > I want to create a calculated field, but I can't get the expression to
> > work. All I want to do is the sum of field 1 and field 2. This must
> > be easy, but I just can't see it?
> You should be able to use something like this:
> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
> Field2Name.Value, "DataSetName")
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
I entered that into the expression of the calculated field, but I get
the following error message.
A value expression used for the report parameter '=sum( Fields!
converganceValue.Value, "CapmLOB")
+ sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
aggregate function. Aggregate functions cannot be used in report
parameter expressions.|||Why don't you do it in your SQL query instead of doing it in the textbox
formula ?
SELECT (SUM(fld1)+SUM(fld2)) AS fld3 FROM table ...
And then put the following in your tb formula.
=SUM(Fields!fld3.Value)
This is how I work and I think it's the real way to do it.
Good Luck
Julien
"Andy" <andywilliams1971@.msn.com> wrote in message
news:1174391075.416295.213680@.n76g2000hsh.googlegroups.com...
> On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
>> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
>> > I want to create a calculated field, but I can't get the expression to
>> > work. All I want to do is the sum of field 1 and field 2. This must
>> > be easy, but I just can't see it?
>> You should be able to use something like this:
>> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
>> Field2Name.Value, "DataSetName")
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
> I entered that into the expression of the calculated field, but I get
> the following error message.
> A value expression used for the report parameter '=sum( Fields!
> converganceValue.Value, "CapmLOB")
> + sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
> aggregate function. Aggregate functions cannot be used in report
> parameter expressions.
>|||On Mar 20, 3:01 pm, "Julien Bonnier" <jul...@.m0851.com> wrote:
> Why don't you do it in your SQL query instead of doing it in the textbox
> formula ?
> SELECT (SUM(fld1)+SUM(fld2)) AS fld3 FROM table ...
> And then put the following in your tb formula.
> =SUM(Fields!fld3.Value)
> This is how I work and I think it's the real way to do it.
> Good Luck
> Julien
> "Andy" <andywilliams1...@.msn.com> wrote in message
> news:1174391075.416295.213680@.n76g2000hsh.googlegroups.com...
> > On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
> >> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> >> > I want to create a calculated field, but I can't get the expression to
> >> > work. All I want to do is the sum of field 1 and field 2. This must
> >> > be easy, but I just can't see it?
> >> You should be able to use something like this:
> >> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
> >> Field2Name.Value, "DataSetName")
> >> Regards,
> >> Enrique Martinez
> >> Sr. SQL Server Developer
> > I entered that into the expression of the calculated field, but I get
> > the following error message.
> > A value expression used for the report parameter '=sum( Fields!
> > converganceValue.Value, "CapmLOB")
> > + sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
> > aggregate function. Aggregate functions cannot be used in report
> > parameter expressions.
Julien has a valid point.
If you are using the calculated field inside a table and there is only
one cell/value per item, you can most likely avoid using the aggregate
(i.e., =(Fields!
converganceValue.Value + Fields!nonConverganceValue.Value)); however,
if the calculated field is outside the table, etc then you will most
likely need to create the calculated field in the query or stored
procedure that is sourcing the report. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On 21 Mar, 01:44, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Mar 20, 3:01 pm, "Julien Bonnier" <jul...@.m0851.com> wrote:
>
>
> > Why don't you do it in your SQL query instead of doing it in the textbox
> > formula ?
> > SELECT (SUM(fld1)+SUM(fld2)) AS fld3 FROM table ...
> > And then put the following in your tb formula.
> > =SUM(Fields!fld3.Value)
> > This is how I work and I think it's the real way to do it.
> > Good Luck
> > Julien
> > "Andy" <andywilliams1...@.msn.com> wrote in message
> >news:1174391075.416295.213680@.n76g2000hsh.googlegroups.com...
> > > On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
> > >> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> > >> > I want to create a calculated field, but I can't get the expression to
> > >> > work. All I want to do is the sum of field 1 and field 2. This must
> > >> > be easy, but I just can't see it?
> > >> You should be able to use something like this:
> > >> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
> > >> Field2Name.Value, "DataSetName")
> > >> Regards,
> > >> Enrique Martinez
> > >> Sr. SQL Server Developer
> > > I entered that into the expression of the calculated field, but I get
> > > the following error message.
> > > A value expression used for the report parameter '=sum( Fields!
> > > converganceValue.Value, "CapmLOB")
> > > + sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
> > > aggregate function. Aggregate functions cannot be used in report
> > > parameter expressions.
> Julien has a valid point.
> If you are using the calculated field inside a table and there is only
> one cell/value per item, you can most likely avoid using the aggregate
> (i.e., =(Fields!
> converganceValue.Value + Fields!nonConverganceValue.Value)); however,
> if the calculated field is outside the table, etc then you will most
> likely need to create the calculated field in the query or stored
> procedure that is sourcing the report. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer- Hide quoted text -
> - Show quoted text -
Thanks. I put it in the SQL code and it worked fine. Don't know why
I didn't think of that earlier.|||On Mar 21, 4:01 am, "Andy" <andywilliams1...@.msn.com> wrote:
> On 21 Mar, 01:44, "EMartinez" <emartinez...@.gmail.com> wrote:
>
> > On Mar 20, 3:01 pm, "Julien Bonnier" <jul...@.m0851.com> wrote:
> > > Why don't you do it in your SQL query instead of doing it in the textbox
> > > formula ?
> > > SELECT (SUM(fld1)+SUM(fld2)) AS fld3 FROM table ...
> > > And then put the following in your tb formula.
> > > =SUM(Fields!fld3.Value)
> > > This is how I work and I think it's the real way to do it.
> > > Good Luck
> > > Julien
> > > "Andy" <andywilliams1...@.msn.com> wrote in message
> > >news:1174391075.416295.213680@.n76g2000hsh.googlegroups.com...
> > > > On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
> > > >> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> > > >> > I want to create a calculated field, but I can't get the expression to
> > > >> > work. All I want to do is the sum of field 1 and field 2. This must
> > > >> > be easy, but I just can't see it?
> > > >> You should be able to use something like this:
> > > >> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
> > > >> Field2Name.Value, "DataSetName")
> > > >> Regards,
> > > >> Enrique Martinez
> > > >> Sr. SQL Server Developer
> > > > I entered that into the expression of the calculated field, but I get
> > > > the following error message.
> > > > A value expression used for the report parameter '=sum( Fields!
> > > > converganceValue.Value, "CapmLOB")
> > > > + sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
> > > > aggregate function. Aggregate functions cannot be used in report
> > > > parameter expressions.
> > Julien has a valid point.
> > If you are using the calculated field inside a table and there is only
> > one cell/value per item, you can most likely avoid using the aggregate
> > (i.e., =(Fields!
> > converganceValue.Value + Fields!nonConverganceValue.Value)); however,
> > if the calculated field is outside the table, etc then you will most
> > likely need to create the calculated field in the query or stored
> > procedure that is sourcing the report. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer- Hide quoted text -
> > - Show quoted text -
> Thanks. I put it in the SQL code and it worked fine. Don't know why
> I didn't think of that earlier.
You're welcome. Glad it worked.
Regards,
Enrique Martinez
Sr. SQL Server Developer