runningvalue syntax

Using RS2000 and SS2000 SP4.
I'm trying to add the total for each group so I can get a grand total and
RunningValue seems like the right function to use. I tried using
=RunningValue(Fields!CompanyName.Value,Sum,Nothing) but I get the error
"â'textbox2â' uses a numeric aggregate function on data that is not numeric".
The group is getting a CountDistinct of company naems. When I try to put in
the group name which is "table1_group1_entnbr" I get the error:
"c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl The value
expression for the textbox â'textbox2â' has a scope parameter that is not valid
for an aggregate function. The scope parameter must be set to a string
constant that is equal to either the name of a containing group, the name of
a containing data region, or the name of a data set." Wouldn't the
"containing group" be the group name? I'm getting the group name by clicking
on the group row while on the Layout tab in VS2003, then go to "edit group"
and the "name" field on the "General" tab.
If that isn't the correct group name to use in the scope argument of
RunningValue, what is?
Thanks,
--
Dan D.Just based on your example, it looks like you're trying to sum the running
value of a field called Company Name. I'd assume your company names are
literal, and RS doesn't know how to sum entities like "Acme", "Bob's
Carwash" etc. If the CompanyName field contains numeric data, you could try
adding a cint() to it, to make RS treat it as a number.
If you want to do a CountDistinct, you could try
= CountDistinct(fields!CompanyName.Value, "table1_group1_entbr")
or
= CountDistinct(fields!CompanyName.Value, "the group containing the group
table1_group1_entbr")
Kaisa M. Lindahl Lervik
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:0CCC8704-000F-436A-83BF-7226AF8ED548@.microsoft.com...
> Using RS2000 and SS2000 SP4.
> I'm trying to add the total for each group so I can get a grand total and
> RunningValue seems like the right function to use. I tried using
> =RunningValue(Fields!CompanyName.Value,Sum,Nothing) but I get the error
> "'textbox2' uses a numeric aggregate function on data that is not
> numeric".
> The group is getting a CountDistinct of company naems. When I try to put
> in
> the group name which is "table1_group1_entnbr" I get the error:
> "c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl The
> value
> expression for the textbox 'textbox2' has a scope parameter that is not
> valid
> for an aggregate function. The scope parameter must be set to a string
> constant that is equal to either the name of a containing group, the name
> of
> a containing data region, or the name of a data set." Wouldn't the
> "containing group" be the group name? I'm getting the group name by
> clicking
> on the group row while on the Layout tab in VS2003, then go to "edit
> group"
> and the "name" field on the "General" tab.
> If that isn't the correct group name to use in the scope argument of
> RunningValue, what is?
> Thanks,
> --
> Dan D.|||You're right that the company names are literal. I'm using this
"=CountDistinct(Fields!CompanyName.Value)" to get the count. I thought that
even though the company names were literal that runningvalue would sum the
number that "=CountDistinct(Fields!CompanyName.Value)" generates for each
group so it might work. Obviously it didn't.
I tried your suggestion of "= CountDistinct(fields!CompanyName.Value,
"table1_group1_entnbr")" before but I get this error:
c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl The value
expression for the textbox â'textbox2â' has a scope parameter that is not valid
for an aggregate function. The scope parameter must be set to a string
constant that is equal to either the name of a containing group, the name of
a containing data region, or the name of a data set. I thought that a
"containing group" or a "containing data region" would be my group named
"table1_group1_entnbr" but it doesn't like that name. I'm not sure this
CountDistinct will give me the correct answer anyway. My report looks like
this:
Enterprise 1 Company A Item 1
Enterprise 1 Company B Item 2
Enterprise 1 Company C Item 1
Enterprise 1 Company A Item 2
Enterprise 1 Company C Item 2
Group Total for Enterprise 1 Company Count 3 - because we only count Company
A & C once. the expression used for this group is
=CountDistinct(Fields!CompanyName.Value)
Enterprise 2 Company A Item 1
Enterprise 2 Company B Item 2
Group Total for Enterprise 2 Company Count 2
Total Company Count 5
I originally used =CountDistinct(Fields!CompanyName.Value) for the grand total
and I would get a count of 3 but I need to add the total for each group
instead and get the correct answer of 5.
I've posted this in 4 or 5 newsgroups and you're only the second person to
respond. I don't know if reporting services is so new that most people don't
use it much yet or what. So, thanks for your help.
Do you have any other ideas?
--
Dan D.
"Kaisa M. Lindahl Lervik" wrote:
> Just based on your example, it looks like you're trying to sum the running
> value of a field called Company Name. I'd assume your company names are
> literal, and RS doesn't know how to sum entities like "Acme", "Bob's
> Carwash" etc. If the CompanyName field contains numeric data, you could try
> adding a cint() to it, to make RS treat it as a number.
> If you want to do a CountDistinct, you could try
> = CountDistinct(fields!CompanyName.Value, "table1_group1_entbr")
> or
> = CountDistinct(fields!CompanyName.Value, "the group containing the group
> table1_group1_entbr")
> Kaisa M. Lindahl Lervik
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:0CCC8704-000F-436A-83BF-7226AF8ED548@.microsoft.com...
> > Using RS2000 and SS2000 SP4.
> >
> > I'm trying to add the total for each group so I can get a grand total and
> > RunningValue seems like the right function to use. I tried using
> > =RunningValue(Fields!CompanyName.Value,Sum,Nothing) but I get the error
> > "'textbox2' uses a numeric aggregate function on data that is not
> > numeric".
> > The group is getting a CountDistinct of company naems. When I try to put
> > in
> > the group name which is "table1_group1_entnbr" I get the error:
> > "c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl The
> > value
> > expression for the textbox 'textbox2' has a scope parameter that is not
> > valid
> > for an aggregate function. The scope parameter must be set to a string
> > constant that is equal to either the name of a containing group, the name
> > of
> > a containing data region, or the name of a data set." Wouldn't the
> > "containing group" be the group name? I'm getting the group name by
> > clicking
> > on the group row while on the Layout tab in VS2003, then go to "edit
> > group"
> > and the "name" field on the "General" tab.
> >
> > If that isn't the correct group name to use in the scope argument of
> > RunningValue, what is?
> >
> > Thanks,
> > --
> > Dan D.
>
>|||What would the syntax be to add cint() to the expression?
--
Dan D.
"Kaisa M. Lindahl Lervik" wrote:
> Just based on your example, it looks like you're trying to sum the running
> value of a field called Company Name. I'd assume your company names are
> literal, and RS doesn't know how to sum entities like "Acme", "Bob's
> Carwash" etc. If the CompanyName field contains numeric data, you could try
> adding a cint() to it, to make RS treat it as a number.
> If you want to do a CountDistinct, you could try
> = CountDistinct(fields!CompanyName.Value, "table1_group1_entbr")
> or
> = CountDistinct(fields!CompanyName.Value, "the group containing the group
> table1_group1_entbr")
> Kaisa M. Lindahl Lervik
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:0CCC8704-000F-436A-83BF-7226AF8ED548@.microsoft.com...
> > Using RS2000 and SS2000 SP4.
> >
> > I'm trying to add the total for each group so I can get a grand total and
> > RunningValue seems like the right function to use. I tried using
> > =RunningValue(Fields!CompanyName.Value,Sum,Nothing) but I get the error
> > "'textbox2' uses a numeric aggregate function on data that is not
> > numeric".
> > The group is getting a CountDistinct of company naems. When I try to put
> > in
> > the group name which is "table1_group1_entnbr" I get the error:
> > "c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl The
> > value
> > expression for the textbox 'textbox2' has a scope parameter that is not
> > valid
> > for an aggregate function. The scope parameter must be set to a string
> > constant that is equal to either the name of a containing group, the name
> > of
> > a containing data region, or the name of a data set." Wouldn't the
> > "containing group" be the group name? I'm getting the group name by
> > clicking
> > on the group row while on the Layout tab in VS2003, then go to "edit
> > group"
> > and the "name" field on the "General" tab.
> >
> > If that isn't the correct group name to use in the scope argument of
> > RunningValue, what is?
> >
> > Thanks,
> > --
> > Dan D.
>
>|||I tried this syntax
=RunningValue(CInt(Fields!CompanyName.Value),Sum,"dsBillingDetail") and got
this error - The value expression for the textbox â'textbox2â' contains an
error: Input string was not in a correct format. dsBillingDetail is the
dataset name.
Then I put CInt() around the group by total like this
=CInt(CountDistinct(Fields!CompanyName.Value)) but I got the same error.
Then I tried putting the group name back in the expression like this
=RunningValue(CInt(Fields!CompanyName.Value),Sum,"table1_group1_entnbr") and
got these errors c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail
Report.rdl The value expression for the textbox â'textbox2â' has a scope
parameter that is not valid for an aggregate function. The scope parameter
must be set to a string constant that is equal to either the name of a
containing group, the name of a containing data region, or the name of a data
set. The value expression for the textbox â'textbox2â' contains an error: Input
string was not in a correct format.
Dan D.
"Kaisa M. Lindahl Lervik" wrote:
> Just based on your example, it looks like you're trying to sum the running
> value of a field called Company Name. I'd assume your company names are
> literal, and RS doesn't know how to sum entities like "Acme", "Bob's
> Carwash" etc. If the CompanyName field contains numeric data, you could try
> adding a cint() to it, to make RS treat it as a number.
> If you want to do a CountDistinct, you could try
> = CountDistinct(fields!CompanyName.Value, "table1_group1_entbr")
> or
> = CountDistinct(fields!CompanyName.Value, "the group containing the group
> table1_group1_entbr")
> Kaisa M. Lindahl Lervik
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:0CCC8704-000F-436A-83BF-7226AF8ED548@.microsoft.com...
> > Using RS2000 and SS2000 SP4.
> >
> > I'm trying to add the total for each group so I can get a grand total and
> > RunningValue seems like the right function to use. I tried using
> > =RunningValue(Fields!CompanyName.Value,Sum,Nothing) but I get the error
> > "'textbox2' uses a numeric aggregate function on data that is not
> > numeric".
> > The group is getting a CountDistinct of company naems. When I try to put
> > in
> > the group name which is "table1_group1_entnbr" I get the error:
> > "c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl The
> > value
> > expression for the textbox 'textbox2' has a scope parameter that is not
> > valid
> > for an aggregate function. The scope parameter must be set to a string
> > constant that is equal to either the name of a containing group, the name
> > of
> > a containing data region, or the name of a data set." Wouldn't the
> > "containing group" be the group name? I'm getting the group name by
> > clicking
> > on the group row while on the Layout tab in VS2003, then go to "edit
> > group"
> > and the "name" field on the "General" tab.
> >
> > If that isn't the correct group name to use in the scope argument of
> > RunningValue, what is?
> >
> > Thanks,
> > --
> > Dan D.
>
>|||OK, again untested and in a hurry, but this is what I think:
You need to sum the count distincts, not the numbers. Even though you have
done count distinct on the names, your code for running value only shows me
that you try to sum the running value of the names, not the count distinct
of the names.
Try doing something like
=RunningValue(countdistinct(fields!CompanyName.Value, "yourdatasetname"),
SUM, Nothing)
Or just do the whole calculation in your dataset instead of in your report.
Sometimes it makes more sense to do it in your SQL query.
Kaisa M. Lindahl Lervik
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:0E716043-E89D-404B-BE8C-4DE097B79883@.microsoft.com...
> You're right that the company names are literal. I'm using this
> "=CountDistinct(Fields!CompanyName.Value)" to get the count. I thought
> that
> even though the company names were literal that runningvalue would sum the
> number that "=CountDistinct(Fields!CompanyName.Value)" generates for each
> group so it might work. Obviously it didn't.
> I tried your suggestion of "= CountDistinct(fields!CompanyName.Value,
> "table1_group1_entnbr")" before but I get this error:
> c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl The
> value
> expression for the textbox 'textbox2' has a scope parameter that is not
> valid
> for an aggregate function. The scope parameter must be set to a string
> constant that is equal to either the name of a containing group, the name
> of
> a containing data region, or the name of a data set. I thought that a
> "containing group" or a "containing data region" would be my group named
> "table1_group1_entnbr" but it doesn't like that name. I'm not sure this
> CountDistinct will give me the correct answer anyway. My report looks like
> this:
> Enterprise 1 Company A Item 1
> Enterprise 1 Company B Item 2
> Enterprise 1 Company C Item 1
> Enterprise 1 Company A Item 2
> Enterprise 1 Company C Item 2
> Group Total for Enterprise 1 Company Count 3 - because we only count
> Company
> A & C once. the expression used for this group is
> =CountDistinct(Fields!CompanyName.Value)
> Enterprise 2 Company A Item 1
> Enterprise 2 Company B Item 2
> Group Total for Enterprise 2 Company Count 2
> Total Company Count 5
> I originally used =CountDistinct(Fields!CompanyName.Value) for the grand
> total
> and I would get a count of 3 but I need to add the total for each group
> instead and get the correct answer of 5.
> I've posted this in 4 or 5 newsgroups and you're only the second person to
> respond. I don't know if reporting services is so new that most people
> don't
> use it much yet or what. So, thanks for your help.
> Do you have any other ideas?
> --
> Dan D.
>
> "Kaisa M. Lindahl Lervik" wrote:
>> Just based on your example, it looks like you're trying to sum the
>> running
>> value of a field called Company Name. I'd assume your company names are
>> literal, and RS doesn't know how to sum entities like "Acme", "Bob's
>> Carwash" etc. If the CompanyName field contains numeric data, you could
>> try
>> adding a cint() to it, to make RS treat it as a number.
>> If you want to do a CountDistinct, you could try
>> = CountDistinct(fields!CompanyName.Value, "table1_group1_entbr")
>> or
>> = CountDistinct(fields!CompanyName.Value, "the group containing the group
>> table1_group1_entbr")
>> Kaisa M. Lindahl Lervik
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:0CCC8704-000F-436A-83BF-7226AF8ED548@.microsoft.com...
>> > Using RS2000 and SS2000 SP4.
>> >
>> > I'm trying to add the total for each group so I can get a grand total
>> > and
>> > RunningValue seems like the right function to use. I tried using
>> > =RunningValue(Fields!CompanyName.Value,Sum,Nothing) but I get the error
>> > "'textbox2' uses a numeric aggregate function on data that is not
>> > numeric".
>> > The group is getting a CountDistinct of company naems. When I try to
>> > put
>> > in
>> > the group name which is "table1_group1_entnbr" I get the error:
>> > "c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl The
>> > value
>> > expression for the textbox 'textbox2' has a scope parameter that is not
>> > valid
>> > for an aggregate function. The scope parameter must be set to a string
>> > constant that is equal to either the name of a containing group, the
>> > name
>> > of
>> > a containing data region, or the name of a data set." Wouldn't the
>> > "containing group" be the group name? I'm getting the group name by
>> > clicking
>> > on the group row while on the Layout tab in VS2003, then go to "edit
>> > group"
>> > and the "name" field on the "General" tab.
>> >
>> > If that isn't the correct group name to use in the scope argument of
>> > RunningValue, what is?
>> >
>> > Thanks,
>> > --
>> > Dan D.
>>|||When I try this
=RunningValue(CountDistinct(Fields!CompanyName.Value,"dsBillingDetail"),Sum,Nothing)
I get this error:c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail
Report.rdl The value expression for the textbox 'textbox10' contains an
aggregate function (or RunningValue or RowNumber functions) in the argument
to another aggregate function (or RunningValue). Aggregate functions cannot
be nested inside other aggregate functions.
--
Dan D.
"Kaisa M. Lindahl Lervik" wrote:
> OK, again untested and in a hurry, but this is what I think:
> You need to sum the count distincts, not the numbers. Even though you have
> done count distinct on the names, your code for running value only shows me
> that you try to sum the running value of the names, not the count distinct
> of the names.
> Try doing something like
> =RunningValue(countdistinct(fields!CompanyName.Value, "yourdatasetname"),
> SUM, Nothing)
> Or just do the whole calculation in your dataset instead of in your report.
> Sometimes it makes more sense to do it in your SQL query.
> Kaisa M. Lindahl Lervik
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:0E716043-E89D-404B-BE8C-4DE097B79883@.microsoft.com...
> > You're right that the company names are literal. I'm using this
> > "=CountDistinct(Fields!CompanyName.Value)" to get the count. I thought
> > that
> > even though the company names were literal that runningvalue would sum the
> > number that "=CountDistinct(Fields!CompanyName.Value)" generates for each
> > group so it might work. Obviously it didn't.
> >
> > I tried your suggestion of "= CountDistinct(fields!CompanyName.Value,
> > "table1_group1_entnbr")" before but I get this error:
> > c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl The
> > value
> > expression for the textbox 'textbox2' has a scope parameter that is not
> > valid
> > for an aggregate function. The scope parameter must be set to a string
> > constant that is equal to either the name of a containing group, the name
> > of
> > a containing data region, or the name of a data set. I thought that a
> > "containing group" or a "containing data region" would be my group named
> > "table1_group1_entnbr" but it doesn't like that name. I'm not sure this
> > CountDistinct will give me the correct answer anyway. My report looks like
> > this:
> >
> > Enterprise 1 Company A Item 1
> > Enterprise 1 Company B Item 2
> > Enterprise 1 Company C Item 1
> > Enterprise 1 Company A Item 2
> > Enterprise 1 Company C Item 2
> > Group Total for Enterprise 1 Company Count 3 - because we only count
> > Company
> > A & C once. the expression used for this group is
> > =CountDistinct(Fields!CompanyName.Value)
> >
> > Enterprise 2 Company A Item 1
> > Enterprise 2 Company B Item 2
> > Group Total for Enterprise 2 Company Count 2
> >
> > Total Company Count 5
> > I originally used =CountDistinct(Fields!CompanyName.Value) for the grand
> > total
> > and I would get a count of 3 but I need to add the total for each group
> > instead and get the correct answer of 5.
> >
> > I've posted this in 4 or 5 newsgroups and you're only the second person to
> > respond. I don't know if reporting services is so new that most people
> > don't
> > use it much yet or what. So, thanks for your help.
> >
> > Do you have any other ideas?
> > --
> > Dan D.
> >
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> Just based on your example, it looks like you're trying to sum the
> >> running
> >> value of a field called Company Name. I'd assume your company names are
> >> literal, and RS doesn't know how to sum entities like "Acme", "Bob's
> >> Carwash" etc. If the CompanyName field contains numeric data, you could
> >> try
> >> adding a cint() to it, to make RS treat it as a number.
> >>
> >> If you want to do a CountDistinct, you could try
> >> = CountDistinct(fields!CompanyName.Value, "table1_group1_entbr")
> >> or
> >> = CountDistinct(fields!CompanyName.Value, "the group containing the group
> >> table1_group1_entbr")
> >>
> >> Kaisa M. Lindahl Lervik
> >>
> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> >> news:0CCC8704-000F-436A-83BF-7226AF8ED548@.microsoft.com...
> >> > Using RS2000 and SS2000 SP4.
> >> >
> >> > I'm trying to add the total for each group so I can get a grand total
> >> > and
> >> > RunningValue seems like the right function to use. I tried using
> >> > =RunningValue(Fields!CompanyName.Value,Sum,Nothing) but I get the error
> >> > "'textbox2' uses a numeric aggregate function on data that is not
> >> > numeric".
> >> > The group is getting a CountDistinct of company naems. When I try to
> >> > put
> >> > in
> >> > the group name which is "table1_group1_entnbr" I get the error:
> >> > "c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl The
> >> > value
> >> > expression for the textbox 'textbox2' has a scope parameter that is not
> >> > valid
> >> > for an aggregate function. The scope parameter must be set to a string
> >> > constant that is equal to either the name of a containing group, the
> >> > name
> >> > of
> >> > a containing data region, or the name of a data set." Wouldn't the
> >> > "containing group" be the group name? I'm getting the group name by
> >> > clicking
> >> > on the group row while on the Layout tab in VS2003, then go to "edit
> >> > group"
> >> > and the "name" field on the "General" tab.
> >> >
> >> > If that isn't the correct group name to use in the scope argument of
> >> > RunningValue, what is?
> >> >
> >> > Thanks,
> >> > --
> >> > Dan D.
> >>
> >>
> >>
>
>|||Just realized I've been through this in an old report. You need to do the
CountDistinct in the RunningValue function:
=RunningValue(Fields!PPScheduledStart.Value, CountDistinct, "table3_Group1")
The "table3_Group1" is the first group after the detail cells, i.e. first
containing group over details.
Kaisa
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:1F91A4A7-6AEE-46F4-A538-444E83354719@.microsoft.com...
> When I try this
> =RunningValue(CountDistinct(Fields!CompanyName.Value,"dsBillingDetail"),Sum,Nothing)
> I get this error:c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail
> Report.rdl The value expression for the textbox 'textbox10' contains an
> aggregate function (or RunningValue or RowNumber functions) in the
> argument
> to another aggregate function (or RunningValue). Aggregate functions
> cannot
> be nested inside other aggregate functions.
> --
> Dan D.
>
> "Kaisa M. Lindahl Lervik" wrote:
>> OK, again untested and in a hurry, but this is what I think:
>> You need to sum the count distincts, not the numbers. Even though you
>> have
>> done count distinct on the names, your code for running value only shows
>> me
>> that you try to sum the running value of the names, not the count
>> distinct
>> of the names.
>> Try doing something like
>> =RunningValue(countdistinct(fields!CompanyName.Value, "yourdatasetname"),
>> SUM, Nothing)
>> Or just do the whole calculation in your dataset instead of in your
>> report.
>> Sometimes it makes more sense to do it in your SQL query.
>> Kaisa M. Lindahl Lervik
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:0E716043-E89D-404B-BE8C-4DE097B79883@.microsoft.com...
>> > You're right that the company names are literal. I'm using this
>> > "=CountDistinct(Fields!CompanyName.Value)" to get the count. I thought
>> > that
>> > even though the company names were literal that runningvalue would sum
>> > the
>> > number that "=CountDistinct(Fields!CompanyName.Value)" generates for
>> > each
>> > group so it might work. Obviously it didn't.
>> >
>> > I tried your suggestion of "= CountDistinct(fields!CompanyName.Value,
>> > "table1_group1_entnbr")" before but I get this error:
>> > c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl The
>> > value
>> > expression for the textbox 'textbox2' has a scope parameter that is not
>> > valid
>> > for an aggregate function. The scope parameter must be set to a string
>> > constant that is equal to either the name of a containing group, the
>> > name
>> > of
>> > a containing data region, or the name of a data set. I thought that a
>> > "containing group" or a "containing data region" would be my group
>> > named
>> > "table1_group1_entnbr" but it doesn't like that name. I'm not sure this
>> > CountDistinct will give me the correct answer anyway. My report looks
>> > like
>> > this:
>> >
>> > Enterprise 1 Company A Item 1
>> > Enterprise 1 Company B Item 2
>> > Enterprise 1 Company C Item 1
>> > Enterprise 1 Company A Item 2
>> > Enterprise 1 Company C Item 2
>> > Group Total for Enterprise 1 Company Count 3 - because we only count
>> > Company
>> > A & C once. the expression used for this group is
>> > =CountDistinct(Fields!CompanyName.Value)
>> >
>> > Enterprise 2 Company A Item 1
>> > Enterprise 2 Company B Item 2
>> > Group Total for Enterprise 2 Company Count 2
>> >
>> > Total Company Count 5
>> > I originally used =CountDistinct(Fields!CompanyName.Value) for the
>> > grand
>> > total
>> > and I would get a count of 3 but I need to add the total for each group
>> > instead and get the correct answer of 5.
>> >
>> > I've posted this in 4 or 5 newsgroups and you're only the second person
>> > to
>> > respond. I don't know if reporting services is so new that most people
>> > don't
>> > use it much yet or what. So, thanks for your help.
>> >
>> > Do you have any other ideas?
>> > --
>> > Dan D.
>> >
>> >
>> > "Kaisa M. Lindahl Lervik" wrote:
>> >
>> >> Just based on your example, it looks like you're trying to sum the
>> >> running
>> >> value of a field called Company Name. I'd assume your company names
>> >> are
>> >> literal, and RS doesn't know how to sum entities like "Acme", "Bob's
>> >> Carwash" etc. If the CompanyName field contains numeric data, you
>> >> could
>> >> try
>> >> adding a cint() to it, to make RS treat it as a number.
>> >>
>> >> If you want to do a CountDistinct, you could try
>> >> = CountDistinct(fields!CompanyName.Value, "table1_group1_entbr")
>> >> or
>> >> = CountDistinct(fields!CompanyName.Value, "the group containing the
>> >> group
>> >> table1_group1_entbr")
>> >>
>> >> Kaisa M. Lindahl Lervik
>> >>
>> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> >> news:0CCC8704-000F-436A-83BF-7226AF8ED548@.microsoft.com...
>> >> > Using RS2000 and SS2000 SP4.
>> >> >
>> >> > I'm trying to add the total for each group so I can get a grand
>> >> > total
>> >> > and
>> >> > RunningValue seems like the right function to use. I tried using
>> >> > =RunningValue(Fields!CompanyName.Value,Sum,Nothing) but I get the
>> >> > error
>> >> > "'textbox2' uses a numeric aggregate function on data that is not
>> >> > numeric".
>> >> > The group is getting a CountDistinct of company naems. When I try to
>> >> > put
>> >> > in
>> >> > the group name which is "table1_group1_entnbr" I get the error:
>> >> > "c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl
>> >> > The
>> >> > value
>> >> > expression for the textbox 'textbox2' has a scope parameter that is
>> >> > not
>> >> > valid
>> >> > for an aggregate function. The scope parameter must be set to a
>> >> > string
>> >> > constant that is equal to either the name of a containing group, the
>> >> > name
>> >> > of
>> >> > a containing data region, or the name of a data set." Wouldn't the
>> >> > "containing group" be the group name? I'm getting the group name by
>> >> > clicking
>> >> > on the group row while on the Layout tab in VS2003, then go to "edit
>> >> > group"
>> >> > and the "name" field on the "General" tab.
>> >> >
>> >> > If that isn't the correct group name to use in the scope argument of
>> >> > RunningValue, what is?
>> >> >
>> >> > Thanks,
>> >> > --
>> >> > Dan D.
>> >>
>> >>
>> >>
>>|||I have two footer groups and I tried both of them. I get the same error - The
value expression for the textbox â'textbox10â' has a scope parameter that is
not valid for an aggregate function. The scope parameter must be set to a
string constant that is equal to either the name of a containing group, the
name of a containing data region, or the name of a data set. I'm reasonably
sure that I have the right group name. I'm getting the group name by clicking
on the group row while on the Layout tab in VS2003, then go to "edit group"
and the "name" field on the "General" tab.
Thanks,
--
Dan D.
"Kaisa M. Lindahl Lervik" wrote:
> Just realized I've been through this in an old report. You need to do the
> CountDistinct in the RunningValue function:
> =RunningValue(Fields!PPScheduledStart.Value, CountDistinct, "table3_Group1")
> The "table3_Group1" is the first group after the detail cells, i.e. first
> containing group over details.
>
> Kaisa
>
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1F91A4A7-6AEE-46F4-A538-444E83354719@.microsoft.com...
> > When I try this
> > =RunningValue(CountDistinct(Fields!CompanyName.Value,"dsBillingDetail"),Sum,Nothing)
> > I get this error:c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail
> > Report.rdl The value expression for the textbox 'textbox10' contains an
> > aggregate function (or RunningValue or RowNumber functions) in the
> > argument
> > to another aggregate function (or RunningValue). Aggregate functions
> > cannot
> > be nested inside other aggregate functions.
> >
> > --
> > Dan D.
> >
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> OK, again untested and in a hurry, but this is what I think:
> >>
> >> You need to sum the count distincts, not the numbers. Even though you
> >> have
> >> done count distinct on the names, your code for running value only shows
> >> me
> >> that you try to sum the running value of the names, not the count
> >> distinct
> >> of the names.
> >>
> >> Try doing something like
> >>
> >> =RunningValue(countdistinct(fields!CompanyName.Value, "yourdatasetname"),
> >> SUM, Nothing)
> >>
> >> Or just do the whole calculation in your dataset instead of in your
> >> report.
> >> Sometimes it makes more sense to do it in your SQL query.
> >>
> >> Kaisa M. Lindahl Lervik
> >>
> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> >> news:0E716043-E89D-404B-BE8C-4DE097B79883@.microsoft.com...
> >> > You're right that the company names are literal. I'm using this
> >> > "=CountDistinct(Fields!CompanyName.Value)" to get the count. I thought
> >> > that
> >> > even though the company names were literal that runningvalue would sum
> >> > the
> >> > number that "=CountDistinct(Fields!CompanyName.Value)" generates for
> >> > each
> >> > group so it might work. Obviously it didn't.
> >> >
> >> > I tried your suggestion of "= CountDistinct(fields!CompanyName.Value,
> >> > "table1_group1_entnbr")" before but I get this error:
> >> > c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl The
> >> > value
> >> > expression for the textbox 'textbox2' has a scope parameter that is not
> >> > valid
> >> > for an aggregate function. The scope parameter must be set to a string
> >> > constant that is equal to either the name of a containing group, the
> >> > name
> >> > of
> >> > a containing data region, or the name of a data set. I thought that a
> >> > "containing group" or a "containing data region" would be my group
> >> > named
> >> > "table1_group1_entnbr" but it doesn't like that name. I'm not sure this
> >> > CountDistinct will give me the correct answer anyway. My report looks
> >> > like
> >> > this:
> >> >
> >> > Enterprise 1 Company A Item 1
> >> > Enterprise 1 Company B Item 2
> >> > Enterprise 1 Company C Item 1
> >> > Enterprise 1 Company A Item 2
> >> > Enterprise 1 Company C Item 2
> >> > Group Total for Enterprise 1 Company Count 3 - because we only count
> >> > Company
> >> > A & C once. the expression used for this group is
> >> > =CountDistinct(Fields!CompanyName.Value)
> >> >
> >> > Enterprise 2 Company A Item 1
> >> > Enterprise 2 Company B Item 2
> >> > Group Total for Enterprise 2 Company Count 2
> >> >
> >> > Total Company Count 5
> >> > I originally used =CountDistinct(Fields!CompanyName.Value) for the
> >> > grand
> >> > total
> >> > and I would get a count of 3 but I need to add the total for each group
> >> > instead and get the correct answer of 5.
> >> >
> >> > I've posted this in 4 or 5 newsgroups and you're only the second person
> >> > to
> >> > respond. I don't know if reporting services is so new that most people
> >> > don't
> >> > use it much yet or what. So, thanks for your help.
> >> >
> >> > Do you have any other ideas?
> >> > --
> >> > Dan D.
> >> >
> >> >
> >> > "Kaisa M. Lindahl Lervik" wrote:
> >> >
> >> >> Just based on your example, it looks like you're trying to sum the
> >> >> running
> >> >> value of a field called Company Name. I'd assume your company names
> >> >> are
> >> >> literal, and RS doesn't know how to sum entities like "Acme", "Bob's
> >> >> Carwash" etc. If the CompanyName field contains numeric data, you
> >> >> could
> >> >> try
> >> >> adding a cint() to it, to make RS treat it as a number.
> >> >>
> >> >> If you want to do a CountDistinct, you could try
> >> >> = CountDistinct(fields!CompanyName.Value, "table1_group1_entbr")
> >> >> or
> >> >> = CountDistinct(fields!CompanyName.Value, "the group containing the
> >> >> group
> >> >> table1_group1_entbr")
> >> >>
> >> >> Kaisa M. Lindahl Lervik
> >> >>
> >> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> >> >> news:0CCC8704-000F-436A-83BF-7226AF8ED548@.microsoft.com...
> >> >> > Using RS2000 and SS2000 SP4.
> >> >> >
> >> >> > I'm trying to add the total for each group so I can get a grand
> >> >> > total
> >> >> > and
> >> >> > RunningValue seems like the right function to use. I tried using
> >> >> > =RunningValue(Fields!CompanyName.Value,Sum,Nothing) but I get the
> >> >> > error
> >> >> > "'textbox2' uses a numeric aggregate function on data that is not
> >> >> > numeric".
> >> >> > The group is getting a CountDistinct of company naems. When I try to
> >> >> > put
> >> >> > in
> >> >> > the group name which is "table1_group1_entnbr" I get the error:
> >> >> > "c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl
> >> >> > The
> >> >> > value
> >> >> > expression for the textbox 'textbox2' has a scope parameter that is
> >> >> > not
> >> >> > valid
> >> >> > for an aggregate function. The scope parameter must be set to a
> >> >> > string
> >> >> > constant that is equal to either the name of a containing group, the
> >> >> > name
> >> >> > of
> >> >> > a containing data region, or the name of a data set." Wouldn't the
> >> >> > "containing group" be the group name? I'm getting the group name by
> >> >> > clicking
> >> >> > on the group row while on the Layout tab in VS2003, then go to "edit
> >> >> > group"
> >> >> > and the "name" field on the "General" tab.
> >> >> >
> >> >> > If that isn't the correct group name to use in the scope argument of
> >> >> > RunningValue, what is?
> >> >> >
> >> >> > Thanks,
> >> >> > --
> >> >> > Dan D.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Try using the name of the dataset, if the table groups throws an error.
Or the name of the table. Is your table in a list? That might confuse
things, I know I run into lots of troubles when I have a table in a list.
Kaisa
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:7EF5D9EF-C8F2-4195-B137-457082852092@.microsoft.com...
>I have two footer groups and I tried both of them. I get the same error -
>The
> value expression for the textbox 'textbox10' has a scope parameter that is
> not valid for an aggregate function. The scope parameter must be set to a
> string constant that is equal to either the name of a containing group,
> the
> name of a containing data region, or the name of a data set. I'm
> reasonably
> sure that I have the right group name. I'm getting the group name by
> clicking
> on the group row while on the Layout tab in VS2003, then go to "edit
> group"
> and the "name" field on the "General" tab.
> Thanks,
> --
> Dan D.
>
> "Kaisa M. Lindahl Lervik" wrote:
>> Just realized I've been through this in an old report. You need to do the
>> CountDistinct in the RunningValue function:
>> =RunningValue(Fields!PPScheduledStart.Value, CountDistinct,
>> "table3_Group1")
>> The "table3_Group1" is the first group after the detail cells, i.e. first
>> containing group over details.
>>
>> Kaisa
>>
>>
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:1F91A4A7-6AEE-46F4-A538-444E83354719@.microsoft.com...
>> > When I try this
>> > =RunningValue(CountDistinct(Fields!CompanyName.Value,"dsBillingDetail"),Sum,Nothing)
>> > I get this error:c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail
>> > Report.rdl The value expression for the textbox 'textbox10' contains an
>> > aggregate function (or RunningValue or RowNumber functions) in the
>> > argument
>> > to another aggregate function (or RunningValue). Aggregate functions
>> > cannot
>> > be nested inside other aggregate functions.
>> >
>> > --
>> > Dan D.
>> >
>> >
>> > "Kaisa M. Lindahl Lervik" wrote:
>> >
>> >> OK, again untested and in a hurry, but this is what I think:
>> >>
>> >> You need to sum the count distincts, not the numbers. Even though you
>> >> have
>> >> done count distinct on the names, your code for running value only
>> >> shows
>> >> me
>> >> that you try to sum the running value of the names, not the count
>> >> distinct
>> >> of the names.
>> >>
>> >> Try doing something like
>> >>
>> >> =RunningValue(countdistinct(fields!CompanyName.Value,
>> >> "yourdatasetname"),
>> >> SUM, Nothing)
>> >>
>> >> Or just do the whole calculation in your dataset instead of in your
>> >> report.
>> >> Sometimes it makes more sense to do it in your SQL query.
>> >>
>> >> Kaisa M. Lindahl Lervik
>> >>
>> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> >> news:0E716043-E89D-404B-BE8C-4DE097B79883@.microsoft.com...
>> >> > You're right that the company names are literal. I'm using this
>> >> > "=CountDistinct(Fields!CompanyName.Value)" to get the count. I
>> >> > thought
>> >> > that
>> >> > even though the company names were literal that runningvalue would
>> >> > sum
>> >> > the
>> >> > number that "=CountDistinct(Fields!CompanyName.Value)" generates for
>> >> > each
>> >> > group so it might work. Obviously it didn't.
>> >> >
>> >> > I tried your suggestion of "=>> >> > CountDistinct(fields!CompanyName.Value,
>> >> > "table1_group1_entnbr")" before but I get this error:
>> >> > c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl
>> >> > The
>> >> > value
>> >> > expression for the textbox 'textbox2' has a scope parameter that is
>> >> > not
>> >> > valid
>> >> > for an aggregate function. The scope parameter must be set to a
>> >> > string
>> >> > constant that is equal to either the name of a containing group, the
>> >> > name
>> >> > of
>> >> > a containing data region, or the name of a data set. I thought that
>> >> > a
>> >> > "containing group" or a "containing data region" would be my group
>> >> > named
>> >> > "table1_group1_entnbr" but it doesn't like that name. I'm not sure
>> >> > this
>> >> > CountDistinct will give me the correct answer anyway. My report
>> >> > looks
>> >> > like
>> >> > this:
>> >> >
>> >> > Enterprise 1 Company A Item 1
>> >> > Enterprise 1 Company B Item 2
>> >> > Enterprise 1 Company C Item 1
>> >> > Enterprise 1 Company A Item 2
>> >> > Enterprise 1 Company C Item 2
>> >> > Group Total for Enterprise 1 Company Count 3 - because we only count
>> >> > Company
>> >> > A & C once. the expression used for this group is
>> >> > =CountDistinct(Fields!CompanyName.Value)
>> >> >
>> >> > Enterprise 2 Company A Item 1
>> >> > Enterprise 2 Company B Item 2
>> >> > Group Total for Enterprise 2 Company Count 2
>> >> >
>> >> > Total Company Count 5
>> >> > I originally used =CountDistinct(Fields!CompanyName.Value) for the
>> >> > grand
>> >> > total
>> >> > and I would get a count of 3 but I need to add the total for each
>> >> > group
>> >> > instead and get the correct answer of 5.
>> >> >
>> >> > I've posted this in 4 or 5 newsgroups and you're only the second
>> >> > person
>> >> > to
>> >> > respond. I don't know if reporting services is so new that most
>> >> > people
>> >> > don't
>> >> > use it much yet or what. So, thanks for your help.
>> >> >
>> >> > Do you have any other ideas?
>> >> > --
>> >> > Dan D.
>> >> >
>> >> >
>> >> > "Kaisa M. Lindahl Lervik" wrote:
>> >> >
>> >> >> Just based on your example, it looks like you're trying to sum the
>> >> >> running
>> >> >> value of a field called Company Name. I'd assume your company names
>> >> >> are
>> >> >> literal, and RS doesn't know how to sum entities like "Acme",
>> >> >> "Bob's
>> >> >> Carwash" etc. If the CompanyName field contains numeric data, you
>> >> >> could
>> >> >> try
>> >> >> adding a cint() to it, to make RS treat it as a number.
>> >> >>
>> >> >> If you want to do a CountDistinct, you could try
>> >> >> = CountDistinct(fields!CompanyName.Value, "table1_group1_entbr")
>> >> >> or
>> >> >> = CountDistinct(fields!CompanyName.Value, "the group containing the
>> >> >> group
>> >> >> table1_group1_entbr")
>> >> >>
>> >> >> Kaisa M. Lindahl Lervik
>> >> >>
>> >> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> >> >> news:0CCC8704-000F-436A-83BF-7226AF8ED548@.microsoft.com...
>> >> >> > Using RS2000 and SS2000 SP4.
>> >> >> >
>> >> >> > I'm trying to add the total for each group so I can get a grand
>> >> >> > total
>> >> >> > and
>> >> >> > RunningValue seems like the right function to use. I tried using
>> >> >> > =RunningValue(Fields!CompanyName.Value,Sum,Nothing) but I get the
>> >> >> > error
>> >> >> > "'textbox2' uses a numeric aggregate function on data that is not
>> >> >> > numeric".
>> >> >> > The group is getting a CountDistinct of company naems. When I try
>> >> >> > to
>> >> >> > put
>> >> >> > in
>> >> >> > the group name which is "table1_group1_entnbr" I get the error:
>> >> >> > "c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail
>> >> >> > Report.rdl
>> >> >> > The
>> >> >> > value
>> >> >> > expression for the textbox 'textbox2' has a scope parameter that
>> >> >> > is
>> >> >> > not
>> >> >> > valid
>> >> >> > for an aggregate function. The scope parameter must be set to a
>> >> >> > string
>> >> >> > constant that is equal to either the name of a containing group,
>> >> >> > the
>> >> >> > name
>> >> >> > of
>> >> >> > a containing data region, or the name of a data set." Wouldn't
>> >> >> > the
>> >> >> > "containing group" be the group name? I'm getting the group name
>> >> >> > by
>> >> >> > clicking
>> >> >> > on the group row while on the Layout tab in VS2003, then go to
>> >> >> > "edit
>> >> >> > group"
>> >> >> > and the "name" field on the "General" tab.
>> >> >> >
>> >> >> > If that isn't the correct group name to use in the scope argument
>> >> >> > of
>> >> >> > RunningValue, what is?
>> >> >> >
>> >> >> > Thanks,
>> >> >> > --
>> >> >> > Dan D.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||When I try using the dataset name it runs but it gives the wrong answer. I'm
not sure what you mean by the table being in a list.
--
Dan D.
"Kaisa M. Lindahl Lervik" wrote:
> Try using the name of the dataset, if the table groups throws an error.
> Or the name of the table. Is your table in a list? That might confuse
> things, I know I run into lots of troubles when I have a table in a list.
> Kaisa
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:7EF5D9EF-C8F2-4195-B137-457082852092@.microsoft.com...
> >I have two footer groups and I tried both of them. I get the same error -
> >The
> > value expression for the textbox 'textbox10' has a scope parameter that is
> > not valid for an aggregate function. The scope parameter must be set to a
> > string constant that is equal to either the name of a containing group,
> > the
> > name of a containing data region, or the name of a data set. I'm
> > reasonably
> > sure that I have the right group name. I'm getting the group name by
> > clicking
> > on the group row while on the Layout tab in VS2003, then go to "edit
> > group"
> > and the "name" field on the "General" tab.
> >
> > Thanks,
> >
> > --
> > Dan D.
> >
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> Just realized I've been through this in an old report. You need to do the
> >> CountDistinct in the RunningValue function:
> >>
> >> =RunningValue(Fields!PPScheduledStart.Value, CountDistinct,
> >> "table3_Group1")
> >>
> >> The "table3_Group1" is the first group after the detail cells, i.e. first
> >> containing group over details.
> >>
> >>
> >>
> >> Kaisa
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> >> news:1F91A4A7-6AEE-46F4-A538-444E83354719@.microsoft.com...
> >> > When I try this
> >> > =RunningValue(CountDistinct(Fields!CompanyName.Value,"dsBillingDetail"),Sum,Nothing)
> >> > I get this error:c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail
> >> > Report.rdl The value expression for the textbox 'textbox10' contains an
> >> > aggregate function (or RunningValue or RowNumber functions) in the
> >> > argument
> >> > to another aggregate function (or RunningValue). Aggregate functions
> >> > cannot
> >> > be nested inside other aggregate functions.
> >> >
> >> > --
> >> > Dan D.
> >> >
> >> >
> >> > "Kaisa M. Lindahl Lervik" wrote:
> >> >
> >> >> OK, again untested and in a hurry, but this is what I think:
> >> >>
> >> >> You need to sum the count distincts, not the numbers. Even though you
> >> >> have
> >> >> done count distinct on the names, your code for running value only
> >> >> shows
> >> >> me
> >> >> that you try to sum the running value of the names, not the count
> >> >> distinct
> >> >> of the names.
> >> >>
> >> >> Try doing something like
> >> >>
> >> >> =RunningValue(countdistinct(fields!CompanyName.Value,
> >> >> "yourdatasetname"),
> >> >> SUM, Nothing)
> >> >>
> >> >> Or just do the whole calculation in your dataset instead of in your
> >> >> report.
> >> >> Sometimes it makes more sense to do it in your SQL query.
> >> >>
> >> >> Kaisa M. Lindahl Lervik
> >> >>
> >> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> >> >> news:0E716043-E89D-404B-BE8C-4DE097B79883@.microsoft.com...
> >> >> > You're right that the company names are literal. I'm using this
> >> >> > "=CountDistinct(Fields!CompanyName.Value)" to get the count. I
> >> >> > thought
> >> >> > that
> >> >> > even though the company names were literal that runningvalue would
> >> >> > sum
> >> >> > the
> >> >> > number that "=CountDistinct(Fields!CompanyName.Value)" generates for
> >> >> > each
> >> >> > group so it might work. Obviously it didn't.
> >> >> >
> >> >> > I tried your suggestion of "=> >> >> > CountDistinct(fields!CompanyName.Value,
> >> >> > "table1_group1_entnbr")" before but I get this error:
> >> >> > c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl
> >> >> > The
> >> >> > value
> >> >> > expression for the textbox 'textbox2' has a scope parameter that is
> >> >> > not
> >> >> > valid
> >> >> > for an aggregate function. The scope parameter must be set to a
> >> >> > string
> >> >> > constant that is equal to either the name of a containing group, the
> >> >> > name
> >> >> > of
> >> >> > a containing data region, or the name of a data set. I thought that
> >> >> > a
> >> >> > "containing group" or a "containing data region" would be my group
> >> >> > named
> >> >> > "table1_group1_entnbr" but it doesn't like that name. I'm not sure
> >> >> > this
> >> >> > CountDistinct will give me the correct answer anyway. My report
> >> >> > looks
> >> >> > like
> >> >> > this:
> >> >> >
> >> >> > Enterprise 1 Company A Item 1
> >> >> > Enterprise 1 Company B Item 2
> >> >> > Enterprise 1 Company C Item 1
> >> >> > Enterprise 1 Company A Item 2
> >> >> > Enterprise 1 Company C Item 2
> >> >> > Group Total for Enterprise 1 Company Count 3 - because we only count
> >> >> > Company
> >> >> > A & C once. the expression used for this group is
> >> >> > =CountDistinct(Fields!CompanyName.Value)
> >> >> >
> >> >> > Enterprise 2 Company A Item 1
> >> >> > Enterprise 2 Company B Item 2
> >> >> > Group Total for Enterprise 2 Company Count 2
> >> >> >
> >> >> > Total Company Count 5
> >> >> > I originally used =CountDistinct(Fields!CompanyName.Value) for the
> >> >> > grand
> >> >> > total
> >> >> > and I would get a count of 3 but I need to add the total for each
> >> >> > group
> >> >> > instead and get the correct answer of 5.
> >> >> >
> >> >> > I've posted this in 4 or 5 newsgroups and you're only the second
> >> >> > person
> >> >> > to
> >> >> > respond. I don't know if reporting services is so new that most
> >> >> > people
> >> >> > don't
> >> >> > use it much yet or what. So, thanks for your help.
> >> >> >
> >> >> > Do you have any other ideas?
> >> >> > --
> >> >> > Dan D.
> >> >> >
> >> >> >
> >> >> > "Kaisa M. Lindahl Lervik" wrote:
> >> >> >
> >> >> >> Just based on your example, it looks like you're trying to sum the
> >> >> >> running
> >> >> >> value of a field called Company Name. I'd assume your company names
> >> >> >> are
> >> >> >> literal, and RS doesn't know how to sum entities like "Acme",
> >> >> >> "Bob's
> >> >> >> Carwash" etc. If the CompanyName field contains numeric data, you
> >> >> >> could
> >> >> >> try
> >> >> >> adding a cint() to it, to make RS treat it as a number.
> >> >> >>
> >> >> >> If you want to do a CountDistinct, you could try
> >> >> >> = CountDistinct(fields!CompanyName.Value, "table1_group1_entbr")
> >> >> >> or
> >> >> >> = CountDistinct(fields!CompanyName.Value, "the group containing the
> >> >> >> group
> >> >> >> table1_group1_entbr")
> >> >> >>
> >> >> >> Kaisa M. Lindahl Lervik
> >> >> >>
> >> >> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> >> >> >> news:0CCC8704-000F-436A-83BF-7226AF8ED548@.microsoft.com...
> >> >> >> > Using RS2000 and SS2000 SP4.
> >> >> >> >
> >> >> >> > I'm trying to add the total for each group so I can get a grand
> >> >> >> > total
> >> >> >> > and
> >> >> >> > RunningValue seems like the right function to use. I tried using
> >> >> >> > =RunningValue(Fields!CompanyName.Value,Sum,Nothing) but I get the
> >> >> >> > error
> >> >> >> > "'textbox2' uses a numeric aggregate function on data that is not
> >> >> >> > numeric".
> >> >> >> > The group is getting a CountDistinct of company naems. When I try
> >> >> >> > to
> >> >> >> > put
> >> >> >> > in
> >> >> >> > the group name which is "table1_group1_entnbr" I get the error:
> >> >> >> > "c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail
> >> >> >> > Report.rdl
> >> >> >> > The
> >> >> >> > value
> >> >> >> > expression for the textbox 'textbox2' has a scope parameter that
> >> >> >> > is
> >> >> >> > not
> >> >> >> > valid
> >> >> >> > for an aggregate function. The scope parameter must be set to a
> >> >> >> > string
> >> >> >> > constant that is equal to either the name of a containing group,
> >> >> >> > the
> >> >> >> > name
> >> >> >> > of
> >> >> >> > a containing data region, or the name of a data set." Wouldn't
> >> >> >> > the
> >> >> >> > "containing group" be the group name? I'm getting the group name
> >> >> >> > by
> >> >> >> > clicking
> >> >> >> > on the group row while on the Layout tab in VS2003, then go to
> >> >> >> > "edit
> >> >> >> > group"
> >> >> >> > and the "name" field on the "General" tab.
> >> >> >> >
> >> >> >> > If that isn't the correct group name to use in the scope argument
> >> >> >> > of
> >> >> >> > RunningValue, what is?
> >> >> >> >
> >> >> >> > Thanks,
> >> >> >> > --
> >> >> >> > Dan D.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>sql