runningvalue syntax

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan D. - 25 Sep 2006 15:21 GMT

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,

Signature

Dan D.

Reply to this Message

Kaisa M. Lindahl Lervik - 26 Sep 2006 14:55 GMT

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

> Using RS2000 and SS2000 SP4.
>
[quoted text clipped - 24 lines]
>
> Thanks,

Reply to this Message

Dan D. - 26 Sep 2006 15:32 GMT

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?

Signature

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
[quoted text clipped - 38 lines]
> >
> > Thanks,

Reply to this Message

Kaisa M. Lindahl Lervik - 27 Sep 2006 11:32 GMT

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

> You're right that the company names are literal. I'm using this
> "=CountDistinct(Fields!CompanyName.Value)" to get the count. I thought
[quoted text clipped - 92 lines]
>> >
>> > Thanks,

Reply to this Message

Dan D. - 27 Sep 2006 14:15 GMT

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.

Signature

Dan D.

> OK, again untested and in a hurry, but this is what I think:
>
[quoted text clipped - 109 lines]
> >> >
> >> > Thanks,

Reply to this Message

Kaisa M. Lindahl Lervik - 27 Sep 2006 14:55 GMT

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

> When I try this
> =RunningValue(CountDistinct(Fields!CompanyName.Value,"dsBillingDetail"),Sum,Nothing)
[quoted text clipped - 138 lines]
>> >> >
>> >> > Thanks,

Reply to this Message

Dan D. - 27 Sep 2006 16:52 GMT

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,

Signature

Dan D.

> Just realized I've been through this in an old report. You need to do the
> CountDistinct in the RunningValue function:
[quoted text clipped - 148 lines]
> >> >> >
> >> >> > Thanks,

Reply to this Message

Kaisa M. Lindahl Lervik - 28 Sep 2006 07:50 GMT

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

>I have two footer groups and I tried both of them. I get the same error -
>The
[quoted text clipped - 188 lines]
>> >> >> >
>> >> >> > Thanks,

Reply to this Message

Dan D. - 29 Sep 2006 18:20 GMT

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.

Signature

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
[quoted text clipped - 194 lines]
> >> >> >> >
> >> >> >> > Thanks,

Reply to this Message

Dan D. - 26 Sep 2006 15:49 GMT

What would the syntax be to add cint() to the expression?

Signature

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
[quoted text clipped - 38 lines]
> >
> > Thanks,

Reply to this Message

Dan D. - 26 Sep 2006 16:04 GMT

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.

Signature

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
[quoted text clipped - 38 lines]
> >
> > Thanks,

Reply to this Message