Problem with Scope parameter for SUM function

Is it possible to use a string expression for the scope parameter of the SUM
function?
I am using conditional grouping in my report and do not know the name of the
group I need to SUM for until the report is run.
I need to use an expression like this: = SUM(Fields!Field1.Value,
Code.GetMaxGroupNameUsed()), where I am determining the name of the
containing group in custom code.
Unfortunately I get this error:
The value expression for the textbox 'textbox18' 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.
Any ideas?
Thanks,
TessaScope names have to be string constants.
I'm not sure why you need dynamic scopes, but you can simulate them.
Assuming you have a function that returns a number between 1 and N, use an
expression similar to this:
=Choose( Code.DetermineScope(), Sum(Fields!F1.Value), Sum(Fields!F1.Value,
"Group1"), Sum(Fields!F1.Value, "Group2"), ......, Sum(Fields!F1.Value,
"DataRegionName"), Sum(Fields!F1.Value, "DataSetName") )
Notes:
* you can only use scope names of containing groups, and data regions (e.g.
table, list, matrix)
* for huge reports (i.e. with many data rows in the dataset) this approach
of simulating dynamic scopes will degrade performance
* documentation on the Choose function is available at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctchoose.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tessa" <nospam@.thanks> wrote in message
news:eXcayiYWEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Is it possible to use a string expression for the scope parameter of the
SUM
> function?
> I am using conditional grouping in my report and do not know the name of
the
> group I need to SUM for until the report is run.
> I need to use an expression like this: = SUM(Fields!Field1.Value,
> Code.GetMaxGroupNameUsed()), where I am determining the name of the
> containing group in custom code.
> Unfortunately I get this error:
> The value expression for the textbox 'textbox18' 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.
> Any ideas?
> Thanks,
> Tessa
>

Problem with Precedence Expression

Hello,

I have a SQL Task the executes the following statement;

SELECT COUNT(SettleDate) AS CountResult
FROM SettleDateCount

I have the SQL Task Editor configured for a Single Row result set. The Result Name is CountResult, and the variable it populates is RowCountRes. RowCountRes is Int32 with EvaluateAsExpression set to False.

I have a boolean variable called RowCountStatus. It evaluates as the following expression;

@.[User::RowCountRes] > 1

From the SQL Task, I have two different constraints, both of which evaluate with an expression. One constraint evaluates this way; @.[User::RowCountStatus] = True

The other evaluates this way; @.[User::RowCountStatus] = False

The idea is that if the count comes back from the sql statement greater than 1 then execution continues down one path, else it goes down the other path. Currently, the result comes back = 1. I expect that the 'False' path will execute, but the 'True' path executes instead.

What am I doing wrong?

Thank you for your help!

cdun2

cdun2 wrote:

Hello,

I have a SQL Task the executes the following statement;

SELECT COUNT(SettleDate) AS CountResult
FROM SettleDateCount

I have the SQL Task Editor configured for a Single Row result set. The Result Name is CountResult, and the variable it populates is RowCountRes. RowCountRes is Int32 with EvaluateAsExpression set to False.

I have a boolean variable called RowCountStatus. It evaluates as the following expression;

@.[User::RowCountRes] > 1

From the SQL Task, I have two different constraints, both of which evaluate with an expression. One constraint evaluates this way; @.[User::RowCountStatus] = True

The other evaluates this way; @.[User::RowCountStatus] = False

The idea is that if the count comes back from the sql statement greater than 1 then execution continues down one path, else it goes down the other path. Currently, the result comes back = 1. I expect that the 'False' path will execute, but the 'True' path executes instead.

What am I doing wrong?

Thank you for your help!

cdun2

You should use the watch window to test if the variable truly contains the value that you think it does.

This should help:

Using variables to store expressions

(http://blogs.conchango.com/jamiethomson/archive/2005/12/05/SSIS_3A00_-Using-variables-to-store-expressions.aspx)

-Jamie

|||

Thank you!

cdun2

|||Don't know for certain, but shouldn't it be @.[User::RowCountStatus] == False (NOTE: double equal sign for comparison, single equal for assignment). Seems like it might hit the first test {can rowcountstatus = true, yes} and stops there. I very easily could be wrong though.|||

EWisdahl wrote:

Don't know for certain, but shouldn't it be @.[User::RowCountStatus] == False (NOTE: double equal sign for comparison, single equal for assignment).

That was it! Thanks!

Problem with Page break and Visibility expression

Hi,
I am using sql server 2005 sp1 reporting services.
Currently creating a report, which contains several sub-reports held in
separate rectangles. The rectangles need page breaks to be displayed after
the rectangle. I have checked the â'Insert After Rectangleâ' page break
attribute on the General tab.
The problem is I want the page breaks on the rectangle to work when I set
the visibility attribute to false (this allows the rectangle to display). I
am setting the visibility using an expression and the text in the rectangle
displays properly, but the page breaks do not work.
Can anybody shed any light on this?
Thanks.Hello ringt,
This is a known issue in Reporting Services 2005. I am contacting to the
Product Team to check whether there is any workaround. I appreciate your
patience. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello ringt,
Unfortunately, I have not got any update from the product team. I
appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support|||Hello ringt,
Unfortunately, the product team feedback that there is no workaround now
for this issue. They are addressing in a future release.
If this really impact your business, you could open a CSS case so taht a
Support Professional can work with you in a more timely and efficient
manner.
If there are further questions on the issue, please feel free to let us
know. Have a great day!
Sincerely,
Wei Lu
Microsoft Online Community Support|||You can insert a rectangle into a rectangle, and set a page break in the
inner rectangle and visibility in the outer and paste this nested rectangles
in between subreports. It Visible expression is True, the page break will
work. if Visible expression is False , it will ignore a Page Break. I have
Master report with a few subreports which need to be separated by page
breaks and shown/hidden depending on the input parameters and this approach
worked for me.

Problem with Padding Expression

Hi,
I am trying to use this "=17*Now.Date.Month" in Left Padding of a textbox.
But it is not working.
Even "=17*3" does not work.
Any ideas
Thanks
KiranThe Padding value needs to be a string with a size unit, e.g. 10 pt.
Try this: =CStr(17 * Month(Today)) + " pt"
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kiran" <Kiran@.nospam.net> wrote in message
news:%23QxPZFO5EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am trying to use this "=17*Now.Date.Month" in Left Padding of a textbox.
> But it is not working.
> Even "=17*3" does not work.
> Any ideas
> Thanks
> Kiran
>