Expressions still get evaluated even though the task isn't executed

Whilst investigating the problem talked about here: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=118141
I noticed something quite strange.

The symptom of whatever problem we are having (we don't know yet) is that some property expressions fail to get evaluated and cause the package to fail. ALL of these property expressions are in package-scoped eventhandlers.

Now, if I set DisableEventHandlers=TRUE on the package container the problems still occur. If I completely delete the eventhandlers the problem goes away.

So, it seems to me that even though DisableEventHandlers=TRUE, the property expressions within those eventhandlers are still evaluated.

Why is that? Its pointless to evaluate property expressions if they are not going to be used - it means the package is doing unnecassary work.

-JamieAny comments?|||Jamie! So sorry - I review every couple days and just found this. Let me see what I can find out for you.

hold tight.
Andrea|||cool. Thanks Andrea.

Expressions not working if Matrix cell is not populated by dataset

Hi
I'm using the following expression for the BackGroundColor in a matrix
detail cell:
=IIF(Fields!ID_Quarter.Value = "Cumulative", "Gainsboro","Transparent")
This is checking the value of another cell before setting the
BackGroundColor value.
I'm also using an expression for the bottom border style:
=IIF(Fields!ID_Quarter.Value = "Cumulative", "Solid","Dotted")
This is checking the value of the same cell that the BackGroundColor
expression checks.
These expression ONLY work if the cell has been populated from the dataset
(in my matrix some cells don't have a value - this is expected).
My Cell has three expressions all up, the remaining expression checks it's
own value for nothing and then put's a "0" in the cell if true.
=IIF(Fields!Deep_SSI.Value = Nothing,"0",Sum(Fields!Deep_SSI.Value))
This expression always works.
Is there a problem with using more than one expression on a cell' I think
not because they all work fine if the cell is populated.
Any thoughts?
SimonBHello...
RE: Expressions not working if Matrix cell is not populated by dataset!
Has anyone got any clues on this problem?
Simon
"simonb" wrote:
> Hi
> I'm using the following expression for the BackGroundColor in a matrix
> detail cell:
> =IIF(Fields!ID_Quarter.Value = "Cumulative", "Gainsboro","Transparent")
> This is checking the value of another cell before setting the
> BackGroundColor value.
> I'm also using an expression for the bottom border style:
> =IIF(Fields!ID_Quarter.Value = "Cumulative", "Solid","Dotted")
> This is checking the value of the same cell that the BackGroundColor
> expression checks.
> These expression ONLY work if the cell has been populated from the dataset
> (in my matrix some cells don't have a value - this is expected).
> My Cell has three expressions all up, the remaining expression checks it's
> own value for nothing and then put's a "0" in the cell if true.
> =IIF(Fields!Deep_SSI.Value = Nothing,"0",Sum(Fields!Deep_SSI.Value))
> This expression always works.
> Is there a problem with using more than one expression on a cell' I think
> not because they all work fine if the cell is populated.
> Any thoughts?
> SimonB

Expressions not evaluating values?

Hi,

I use an expression in a column text box to dynamically compute the column title.

The problem must have something linked to the way expressions generally works. I do not understand it clearly.

In this example, I use a SWITCH function to test the numerical value of a 1 row 1 column dataset.

The problem is that I can test the number only if it is lower or equal to the number in the dataset. if I test a number greater than the number in the dataset, I get an error.

How can I get this test to work if the value tested is greater than the value in the dataset?

Thanks

Philippe

Bellow is the code.

-

=switch(

First(Fields!HeaderCount.Value, "HeadersCount") < 2

, nothing

, First(Fields!HeaderCount.Value, "HeadersCount") = 2

, Right(Parameters!Headers.Value, Len(Parameters!Headers.Value) - Parameters!Headers.Value.IndexOf(",2,")-3)

, First(Fields!HeaderCount.Value, "HeadersCount") > 2

, Parameters!Headers.Value.Substring(

Parameters!Headers.Value.IndexOf(",2,")+3

, Parameters!Headers.Value.IndexOf(",3,")-Parameters!Headers.Value.IndexOf(",2,")-3

)

)

Philippe wrote:

is that I can test the number only if it is lower or equal to the number in the dataset. if I test a number greater than the number in the dataset, I get an error.

Note, If the dataset contains 2, the expression will return an error because I try to test >2 in the last case.
if the dataset contains 3 or greater, it works fine.

it is clearly the test which fails because if I replace the action by a fixed string it still return an error.

|||

You are using the switch() function. Since it is a function, all arguments are evaluated before the switch functionality is executed.

I recommend to write a custom code function that uses the VB switch statement and call the custom code function from the expression.

-- Robert

|||

Hi,

I have made some research on the Custom Code however I could not find documentation nor examples that show how to use parameters or dataset values within the custom code. If I create a function like this How can I access the report items?

Public Function Headers(ByVal Column as Integer) As String
Return CStr(Microsoft.VisualBasic.Switch( _
First(Fields!HeaderCount.Value, "HeadersCount") < Column _
, nothing _
, First(Fields!HeaderCount.Value, "HeadersCount") = Column _
, Right(Parameters!Headers.Value, Len(Parameters!Headers.Value) - Parameters!Headers.Value.IndexOf(",Column,")-3) _
, First(Fields!HeaderCount.Value, "HeadersCount") > Column _
, Parameters!Headers.Value.Substring( _
Parameters!Headers.Value.IndexOf(",Column,")+3 _
, Parameters!Headers.Value.IndexOf(",Column + 1,")-Parameters!Headers.Value.IndexOf(",Column,")-3 _
) _
) _
)
End Function

I did find a much simpler solution though.

The string I use contains a variable number of names separated by indexes, i.e.

,1,Charles,2,Tom,3,Laura,4,Rick

I have a report with a fixed number of columns, i.e 50 columns and I populate the columns title by pulling from the string, i.e. Column 3 title will be Laura.

I have converted the string so each name will have trailing spaces. Since now each item has the same lenght, I do not need anymore the switch, I can do simply this in each column with just another index number. Then I use an expression to control the visibility of the column.

Title

=Parameters!Headers.Value.Substring(Parameters!Headers.Value.IndexOf(",3,")+3, 85)

Visibility

=iif(First(Fields!HeaderCount.Value, "HeadersCount")<3,True,False)

If I spend the time to build this instead of using the Matrix report, it is because the Matrix report has 2 majors issues for me.

1) You cannot have column titles for your categories

2) You cannot have the categories values repeated

Because of the strict format requirement I have, I am obliged to use the PIVOT SQL operator and the dynamic column population. My users want a pivoted flat file they can put in Excel and build a pivot table with it. You cannot do that with a matrix report, too bad. That would be so much easy.

Philippe

Expressions not always evaluated in correct order

Hello!

I have an SSIS package, run by the DTExec utility, each night. When I run my package, I assign a value to a variable (package scope) (/SET "\Package.Variables[User:: psRunNo]";0154). (Note: all the variables here are strings).

Value for variable psRunNo = 0154

In the package, I have another package variable called S_SOURCE_FILE_NAME, which is an expression and that uses the variable psRunNo.

Expression for S_SOURCE_FILE_NAME = @.[User:: psRunNo] + "_{TABLENAME}.txt"

So, at the beginning, the value of variable S_SOURCE_FILE_NAME is 0154_{TABLENAME}.txt with that example.

Then, I use the variable S_SOURCE_FILE_NAME in the expression used to assign the ConnectionString property of a Connection (a source file), like this:

REPLACE( @.[User:: S_REJECTED_ROWS_FILE_NAME] ,"{TABLENAME}", "STADDRES")

So here for example, the final value for ConnectionString would be 0154_STADDRES.txt

Everything works nice, most of the time. Sometimes (intermittent problem), the value of the ConnectionString for one of the many Connections I have in the package is not assigned with the right value of psRunNo. The ConnectionString gets the value of psRunNo which is saved into the package (when it was deployed) instead of the value of psRunNo passed with the DTExec.

It is like if the ConnectionString value was computed before the variable psRunNo (and S_SOURCE_FILE_NAME expression) was assigned to the new value, but only for one of the connections (all my connections use the same kind of expression for their ConnectionString property).

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

Isabelle_ wrote:

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

There isn't really a precedence. If VariableC depends on VariableB which depends on VariableA then all will get evaluated when VariableC gets used somewhere. The expressions are not pre-evaluated or anything like that.

This doesn't help explain the problem you are seeing. I confess I can't undersand why it might be happening. Can you post a repro?

-Jamie

|||

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

|||

Isabelle_ wrote:

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

You could try that but this is just papering over the cracks. Something else is wrong here. Could it be that you haven't got EvaluateAsExpression=TRUE?

-Jamie

|||

I've check, and I got the EvaluateAsExpression=TRUE. I guess also that if I wouldn't have it to true, it would never work. But it works, 90% of the time ...

Ok, thanks anyway. I've changed my thing so I just have one level of expression. I will let it run for few days/weeks and see if this problem still happen.

Thanks everybody for your input!

Expressions not always evaluated in correct order

Hello!

I have an SSIS package, run by the DTExec utility, each night. When I run my package, I assign a value to a variable (package scope) (/SET "\Package.Variables[User:: psRunNo]";0154). (Note: all the variables here are strings).

Value for variable psRunNo = 0154

In the package, I have another package variable called S_SOURCE_FILE_NAME, which is an expression and that uses the variable psRunNo.

Expression for S_SOURCE_FILE_NAME = @.[User:: psRunNo] + "_{TABLENAME}.txt"

So, at the beginning, the value of variable S_SOURCE_FILE_NAME is 0154_{TABLENAME}.txt with that example.

Then, I use the variable S_SOURCE_FILE_NAME in the expression used to assign the ConnectionString property of a Connection (a source file), like this:

REPLACE( @.[User:: S_REJECTED_ROWS_FILE_NAME] ,"{TABLENAME}", "STADDRES")

So here for example, the final value for ConnectionString would be 0154_STADDRES.txt

Everything works nice, most of the time. Sometimes (intermittent problem), the value of the ConnectionString for one of the many Connections I have in the package is not assigned with the right value of psRunNo. The ConnectionString gets the value of psRunNo which is saved into the package (when it was deployed) instead of the value of psRunNo passed with the DTExec.

It is like if the ConnectionString value was computed before the variable psRunNo (and S_SOURCE_FILE_NAME expression) was assigned to the new value, but only for one of the connections (all my connections use the same kind of expression for their ConnectionString property).

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

Isabelle_ wrote:

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

There isn't really a precedence. If VariableC depends on VariableB which depends on VariableA then all will get evaluated when VariableC gets used somewhere. The expressions are not pre-evaluated or anything like that.

This doesn't help explain the problem you are seeing. I confess I can't undersand why it might be happening. Can you post a repro?

-Jamie

|||

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

|||

Isabelle_ wrote:

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

You could try that but this is just papering over the cracks. Something else is wrong here. Could it be that you haven't got EvaluateAsExpression=TRUE?

-Jamie

|||

I've check, and I got the EvaluateAsExpression=TRUE. I guess also that if I wouldn't have it to true, it would never work. But it works, 90% of the time ...

Ok, thanks anyway. I've changed my thing so I just have one level of expression. I will let it run for few days/weeks and see if this problem still happen.

Thanks everybody for your input!

Expressions not always evaluated in correct order

Hello!

I have an SSIS package, run by the DTExec utility, each night. When I run my package, I assign a value to a variable (package scope) (/SET "\Package.Variables[User:: psRunNo]";0154). (Note: all the variables here are strings).

Value for variable psRunNo = 0154

In the package, I have another package variable called S_SOURCE_FILE_NAME, which is an expression and that uses the variable psRunNo.

Expression for S_SOURCE_FILE_NAME = @.[User:: psRunNo] + "_{TABLENAME}.txt"

So, at the beginning, the value of variable S_SOURCE_FILE_NAME is 0154_{TABLENAME}.txt with that example.

Then, I use the variable S_SOURCE_FILE_NAME in the expression used to assign the ConnectionString property of a Connection (a source file), like this:

REPLACE( @.[User:: S_REJECTED_ROWS_FILE_NAME] ,"{TABLENAME}", "STADDRES")

So here for example, the final value for ConnectionString would be 0154_STADDRES.txt

Everything works nice, most of the time. Sometimes (intermittent problem), the value of the ConnectionString for one of the many Connections I have in the package is not assigned with the right value of psRunNo. The ConnectionString gets the value of psRunNo which is saved into the package (when it was deployed) instead of the value of psRunNo passed with the DTExec.

It is like if the ConnectionString value was computed before the variable psRunNo (and S_SOURCE_FILE_NAME expression) was assigned to the new value, but only for one of the connections (all my connections use the same kind of expression for their ConnectionString property).

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

Isabelle_ wrote:

Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ?

Thanks!

There isn't really a precedence. If VariableC depends on VariableB which depends on VariableA then all will get evaluated when VariableC gets used somewhere. The expressions are not pre-evaluated or anything like that.

This doesn't help explain the problem you are seeing. I confess I can't undersand why it might be happening. Can you post a repro?

-Jamie

|||

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

|||

Isabelle_ wrote:

I could not post a reproduction as is, because even if I would make up a small package with my case, this case is intermittent, so almost impossible to get it right... I was thinking maybe on making a script at the beginning of my package and "manually" assign the variable values, instead of using expressions.. Normally, the outcome would be the same, but maybe that would ensure that those variables always get their right values before being use... ?

You could try that but this is just papering over the cracks. Something else is wrong here. Could it be that you haven't got EvaluateAsExpression=TRUE?

-Jamie

|||

I've check, and I got the EvaluateAsExpression=TRUE. I guess also that if I wouldn't have it to true, it would never work. But it works, 90% of the time ...

Ok, thanks anyway. I've changed my thing so I just have one level of expression. I will let it run for few days/weeks and see if this problem still happen.

Thanks everybody for your input!

expressions linked to parameters

Is it possible to link expressions to report parameters
for instance i have WTD, MTD and YTD sales targets in my DB and have
WTD, MTD and YTD as report Parameters can i do this?
If i select MTD link the Expression to say if MTD then Show MTD sales
target etc etcOn Jun 8, 10:23 am, blueboy <matt_me...@.hotmail.com> wrote:
> Is it possible to link expressions to report parameters
> for instance i have WTD, MTD and YTD sales targets in my DB and have
> WTD, MTD and YTD as report Parameters can i do this?
> If i select MTD link the Expression to say if MTD then Show MTD sales
> target etc etc
If I'm understanding you correctly, you will want to access the values
of a parameter as opposed to if a parameter value has been selected or
not. Something like this should work:
=iif(Parameters!SalesTargetParam.Value = "MTD", Fields!
MTDSalesTarget.Value, Nothing)
Of course, this should also work:
=switch(Parameters!SalesTargetParam.Value = "WTD", Fields!
WTDSalesTarget.Value, Parameters!SalesTargetParam.Value = "MTD",
Fields!MTDSalesTarget.Value, Parameters!SalesTargetParam.Value ="YTD", Fields!YTDSalesTarget.Value, true, nothing)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Expressions in RS..

Hey guys,
I try to hide some sections of my report using the expression editor.
I tryed to find what language the expression editor is but I did not found
it.
Does someone knows any references about expressions in RS?
Thanks!VB.Net. I sometimes open up a VB project to test out expressions before
using them in RS. Same with code behind reports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
news:uJ37Cv$eFHA.580@.TK2MSFTNGP15.phx.gbl...
> Hey guys,
> I try to hide some sections of my report using the expression editor.
> I tryed to find what language the expression editor is but I did not found
> it.
> Does someone knows any references about expressions in RS?
> Thanks!
>

Expressions in RS

How do I add comments to expressions I create in Reporting Services?

Hello,

To add comments, prefix the line with a single quote (').

For example:

=Iif(Fields!Amount.Value < "1", "Red", "Black")

'Change color to red if below 1.

Hope this helps.

Jarret

|||Thanks, Jarret. I had forgotten that the expression must always be first. I had been trying to add the comment above the expression. I'm too silly

Expressions in RS

Hi,
Im using the following expression to calculate the percentage
=iif(Fields!BudgetedRevenue.Value = 0,0,(Fields!BudgetedRevenue.Value-
Fields!BudgetedCostStan.Value- Fields!BudgetedCostOT.Value-
Fields!BudgetedCostExp.Value)/ Fields!BudgetedRevenue.Value)
if the Fields!BudgetedRevenue.Value = 0 it is returning the #error,instead
of 0.
Fields!BudgetedRevenue.Value is of datatype money.
Thanks in advance.Try using this:
=iif(isnothing(Fields!BudgetedRevenue.Value),0,(Fields!BudgetedRevenue.Value-
Fields!BudgetedCostStan.Value- Fields!BudgetedCostOT.Value-
Fields!BudgetedCostExp.Value)/ Fields!BudgetedRevenue.Value)
I belive the isnothing() should take the place of the = 0 and might help
your formula calculate correctly.
Let me know if that didn't work.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"CCP" wrote:
> Hi,
> Im using the following expression to calculate the percentage
> =iif(Fields!BudgetedRevenue.Value = 0,0,(Fields!BudgetedRevenue.Value-
> Fields!BudgetedCostStan.Value- Fields!BudgetedCostOT.Value-
> Fields!BudgetedCostExp.Value)/ Fields!BudgetedRevenue.Value)
> if the Fields!BudgetedRevenue.Value = 0 it is returning the #error,instead
> of 0.
> Fields!BudgetedRevenue.Value is of datatype money.
> Thanks in advance.

Expressions in queries

I'm new to SQL Server and converting a VB app from Access back end to
SQL Server back end. In this app I use a lot of expressions in
queries
e.g. SELECT iif(isnull([MyText],"",[MyText]) as sRemoveNull
or SELECT [SaleDate]>=#01-Jan-2004# as bUseSale
I can't get anything of the sort to work in SQL Server. I've written a
few UDFs to cope with some more common expressions, but for some cases
it would be much easier to be able to use a complex expression within
the query.
Stored Procedures are not really appropriate as queries are built in
VB and the SQL string passed to SQL Server.
Am I missing something obvious?
Thanks,
MattThese two examples may help. You can check out the full syntax of these
expressions in SQL Server Books Online.
SELECT COALESCE(mytext,'') AS sRemoveNull
SELECT CASE WHEN saledate>='20040101' THEN 1 ELSE 0 END AS bUseSale

> queries are built in
> VB and the SQL string passed to SQL Server.
In the world of SQL Server that's generally a really bad idea. Standard
practice is to use SPs for all data access unless you have an exceptional
reason not to. SPs make the most sense from the point of view of
performance, security and maintainability. If you just want to continue
using SQL the way you've always used Access then you can't expect to realize
all the benefits of the platform change. You may well be better off sticking
with your Access backend.
David Portas
SQL Server MVP
--|||Thanks David, that'll get me started.
Re your further comments, I was also going to post the
"How do I do SELECT * FROM @.tablename" question last night,
but did some reading here first and found numerous responses along the
same lines as yours. I've read two articles from www.sommarskog.se
about dynamic SQL and it seems I have to accept that I'll have to do
things quite differently in the future than I have been (sticking with
Access is not an option). The upside is that I'm learning heaps and
enjoying doing it.
Thanks for your comments.
Matt
On Sun, 30 Jan 2005 18:03:56 -0000, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>These two examples may help. You can check out the full syntax of these
>expressions in SQL Server Books Online.
>SELECT COALESCE(mytext,'') AS sRemoveNull
>SELECT CASE WHEN saledate>='20040101' THEN 1 ELSE 0 END AS bUseSale
>
>In the world of SQL Server that's generally a really bad idea. Standard
>practice is to use SPs for all data access unless you have an exceptional
>reason not to. SPs make the most sense from the point of view of
>performance, security and maintainability. If you just want to continue
>using SQL the way you've always used Access then you can't expect to realiz
e
>all the benefits of the platform change. You may well be better off stickin
g
>with your Access backend.
>--
>David Portas
>SQL Server MVP

Expressions in Height property of a Table

Is it possible to use an expression to set the Height property of a row
in a Table? If so, show me an example.I don't think this is possible, but the hight will can be set to change in
accordance with the content
"Rlane" wrote:
> Is it possible to use an expression to set the Height property of a row
> in a Table? If so, show me an example.
>

Expressions in For Loop Container

Hi,
I am having problem when setting for-each container's EvalExpression as a variable comparison.

I have set this in expression editor
@.[User::Syn] == 1

Editor says that it couldnt convert from System.Bool to System.String.

If I put it in quote, then for-each container says that it needs Bool, not String.

Please help me with this,
Thanks
@.[User::Syn] == "1"|||Syn is of Int32 datatype.
And now it says

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

The data types "DT_I4" and "DT_WSTR" are incompatible for binary operator "==". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation "@.[User::SyncStatus] == "0"" failed with error code 0xC0047080.

(Microsoft.DataTransformationServices.Controls)|||actually, EvalExpression property is string and expression editor must end up with some string value and something == something is basically a boolean.|||So is it User::Syn or User::SyncStatus? If SyncStatus is a DT_I4, then this should work:

@.[User::SyncStatus] == 0

It works for me.|||

Fahad349 wrote:

actually, EvalExpression property is string and expression editor must end up with some string value andsomething == something is basically a boolean.

EvalExpression "must evaluate to a Boolean"|||

Fahad349 wrote:

actually, EvalExpression property is string and expression editor must end up with some string value andsomething == something is basically a boolean.

Why aren't you using the EvalExpression in the editor, not the expression property?

If you are editing the property expressions, then this is what you'd want:

"@.[User::SyncStatus] == 0"

Expressions in calucated columns

Where can I find a list of expressions that are allowed in calculated columns?
Thanks
IT PHYTOSAN
On Mon, 3 Oct 2005 15:31:03 -0700, IT PHYTOSAN wrote:

>Where can I find a list of expressions that are allowed in calculated columns?
Hi IT PHYTOSAN,
In Books Online, subject "CREATE TABLE":
computed_column_expression
Is an expression defining the value of a computed column. A computed
column is a virtual column not physically stored in the table. It is
computed from an expression using other columns in the same table. For
example, a computed column can have the definition: cost AS price * qty.
The expression can be a noncomputed column name, constant, function,
variable, and any combination of these connected by one or more
operators. The expression cannot be a subquery.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks,but what is the syntax? What commands are allowed?
IT PHYTOSAN
"Hugo Kornelis" wrote:

> On Mon, 3 Oct 2005 15:31:03 -0700, IT PHYTOSAN wrote:
>
> Hi IT PHYTOSAN,
> In Books Online, subject "CREATE TABLE":
> computed_column_expression
> Is an expression defining the value of a computed column. A computed
> column is a virtual column not physically stored in the table. It is
> computed from an expression using other columns in the same table. For
> example, a computed column can have the definition: cost AS price * qty.
> The expression can be a noncomputed column name, constant, function,
> variable, and any combination of these connected by one or more
> operators. The expression cannot be a subquery.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||Example Script
CREATE TABLE [dbo].[Table1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Price] [decimal](18, 0) NOT NULL ,
[Qty] [int] NOT NULL ,
[Total] AS ([Price] * [Qty])
) ON [PRIMARY]
GO
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
news:B26DE2A2-2E51-4E7B-BADA-FBDE15A48004@.microsoft.com...[vbcol=seagreen]
> Thanks,but what is the syntax? What commands are allowed?
> IT PHYTOSAN
> "Hugo Kornelis" wrote:

Expressions in calucated columns

Where can I find a list of expressions that are allowed in calculated column
s?
Thanks
IT PHYTOSANOn Mon, 3 Oct 2005 15:31:03 -0700, IT PHYTOSAN wrote:
[vbcol=seagreen]
>Where can I find a list of expressions that are allowed in calculated columns?[/vbc
ol]
Hi IT PHYTOSAN,
In Books Online, subject "CREATE TABLE":
computed_column_expression
Is an expression defining the value of a computed column. A computed
column is a virtual column not physically stored in the table. It is
computed from an expression using other columns in the same table. For
example, a computed column can have the definition: cost AS price * qty.
The expression can be a noncomputed column name, constant, function,
variable, and any combination of these connected by one or more
operators. The expression cannot be a subquery.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks,but what is the syntax? What commands are allowed?
IT PHYTOSAN
"Hugo Kornelis" wrote:

> On Mon, 3 Oct 2005 15:31:03 -0700, IT PHYTOSAN wrote:
>
> Hi IT PHYTOSAN,
> In Books Online, subject "CREATE TABLE":
> computed_column_expression
> Is an expression defining the value of a computed column. A computed
> column is a virtual column not physically stored in the table. It is
> computed from an expression using other columns in the same table. For
> example, a computed column can have the definition: cost AS price * qty.
> The expression can be a noncomputed column name, constant, function,
> variable, and any combination of these connected by one or more
> operators. The expression cannot be a subquery.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Example Script
CREATE TABLE [dbo].[Table1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Price] [decimal](18, 0) NOT NULL ,
[Qty] [int] NOT NULL ,
[Total] AS ([Price] * [Qty])
) ON [PRIMARY]
GO
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
news:B26DE2A2-2E51-4E7B-BADA-FBDE15A48004@.microsoft.com...[vbcol=seagreen]
> Thanks,but what is the syntax? What commands are allowed?
> IT PHYTOSAN
> "Hugo Kornelis" wrote:
>

Expressions in calucated columns

Where can I find a list of expressions that are allowed in calculated columns?
Thanks
IT PHYTOSANOn Mon, 3 Oct 2005 15:31:03 -0700, IT PHYTOSAN wrote:
>Where can I find a list of expressions that are allowed in calculated columns?
Hi IT PHYTOSAN,
In Books Online, subject "CREATE TABLE":
computed_column_expression
Is an expression defining the value of a computed column. A computed
column is a virtual column not physically stored in the table. It is
computed from an expression using other columns in the same table. For
example, a computed column can have the definition: cost AS price * qty.
The expression can be a noncomputed column name, constant, function,
variable, and any combination of these connected by one or more
operators. The expression cannot be a subquery.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks,but what is the syntax? What commands are allowed?
IT PHYTOSAN
"Hugo Kornelis" wrote:
> On Mon, 3 Oct 2005 15:31:03 -0700, IT PHYTOSAN wrote:
> >Where can I find a list of expressions that are allowed in calculated columns?
> Hi IT PHYTOSAN,
> In Books Online, subject "CREATE TABLE":
> computed_column_expression
> Is an expression defining the value of a computed column. A computed
> column is a virtual column not physically stored in the table. It is
> computed from an expression using other columns in the same table. For
> example, a computed column can have the definition: cost AS price * qty.
> The expression can be a noncomputed column name, constant, function,
> variable, and any combination of these connected by one or more
> operators. The expression cannot be a subquery.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Example Script
CREATE TABLE [dbo].[Table1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Price] [decimal](18, 0) NOT NULL ,
[Qty] [int] NOT NULL ,
[Total] AS ([Price] * [Qty])
) ON [PRIMARY]
GO
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
news:B26DE2A2-2E51-4E7B-BADA-FBDE15A48004@.microsoft.com...
> Thanks,but what is the syntax? What commands are allowed?
> IT PHYTOSAN
> "Hugo Kornelis" wrote:
>> On Mon, 3 Oct 2005 15:31:03 -0700, IT PHYTOSAN wrote:
>> >Where can I find a list of expressions that are allowed in calculated
>> >columns?
>> Hi IT PHYTOSAN,
>> In Books Online, subject "CREATE TABLE":
>> computed_column_expression
>> Is an expression defining the value of a computed column. A computed
>> column is a virtual column not physically stored in the table. It is
>> computed from an expression using other columns in the same table. For
>> example, a computed column can have the definition: cost AS price * qty.
>> The expression can be a noncomputed column name, constant, function,
>> variable, and any combination of these connected by one or more
>> operators. The expression cannot be a subquery.
>>
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)

Expressions for parameter values?

Hello all... im trying to figure out... how can i use an expression in a
parameter formula?
I have a report which takes a date parameter. I want to make a subscription
using last week as the date. So for example, i want to set up the
subscription to run every Sunday, and use a value of TODAY() - 7. If i enter
that as a value though, it says its an invalid type. I also tried it with an
equal sign on the front ( =TODAY()-6 ).
Is there a way to do this?
Thanks in advance,
- Arthur Dent.You can use the DateAdd function to add a day/hour/minute/month etc to a
date.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello all... im trying to figure out... how can i use an expression in a
> parameter formula?
> I have a report which takes a date parameter. I want to make a
> subscription using last week as the date. So for example, i want to set up
> the subscription to run every Sunday, and use a value of TODAY() - 7. If i
> enter that as a value though, it says its an invalid type. I also tried it
> with an equal sign on the front ( =TODAY()-6 ).
> Is there a way to do this?
> Thanks in advance,
> - Arthur Dent.
>|||Thanks for the reply. Unfortunately, that doesnt seem to work either. When i
type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error as so:
The value provided for the report parameter 'ForWeekOf' is not valid for its
type. (rsReportParameterTypeMismatch)
TIA-
"Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
> You can use the DateAdd function to add a day/hour/minute/month etc to a
> date.
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in a
>> parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() - 7.
>> If i enter that as a value though, it says its an invalid type. I also
>> tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>|||It sounds like you are trying to type an expression into the date field in
report manager. This won't work - you can only type date constants there.
You should rather load the report in report designer and set the default
value of the date parameter to something like =Today.AddDays(-7). Before
publishing to the report server, make sure to delete the existing report
*before* publishing.
Then, you can create a subscription which will use an expression-based
default value for the date parameter.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:eYecTDtSFHA.3184@.TK2MSFTNGP14.phx.gbl...
> Thanks for the reply. Unfortunately, that doesnt seem to work either. When
> i type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error as so:
> The value provided for the report parameter 'ForWeekOf' is not valid for
> its type. (rsReportParameterTypeMismatch)
> TIA-
>
> "Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
> message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
>> You can use the DateAdd function to add a day/hour/minute/month etc to a
>> date.
>> --
>> Rajeev Karunakaran [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
>> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in
>> a parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() - 7.
>> If i enter that as a value though, it says its an invalid type. I also
>> tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>>
>|||Ah, yes, that sounds like it would do exactly what i need.
Thanks!!
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:O69jArtSFHA.2556@.TK2MSFTNGP12.phx.gbl...
> It sounds like you are trying to type an expression into the date field in
> report manager. This won't work - you can only type date constants there.
> You should rather load the report in report designer and set the default
> value of the date parameter to something like =Today.AddDays(-7). Before
> publishing to the report server, make sure to delete the existing report
> *before* publishing.
> Then, you can create a subscription which will use an expression-based
> default value for the date parameter.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
> news:eYecTDtSFHA.3184@.TK2MSFTNGP14.phx.gbl...
>> Thanks for the reply. Unfortunately, that doesnt seem to work either.
>> When i type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error
>> as so:
>> The value provided for the report parameter 'ForWeekOf' is not valid for
>> its type. (rsReportParameterTypeMismatch)
>> TIA-
>>
>> "Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
>> message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
>> You can use the DateAdd function to add a day/hour/minute/month etc to a
>> date.
>> --
>> Rajeev Karunakaran [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
>> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in
>> a parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() -
>> 7. If i enter that as a value though, it says its an invalid type. I
>> also tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>>
>>
>

expressions based on subtotals

How do I create calculated fields based on subtotals?

SSRS 2005 doesn't support aggregates over aggregates. You should try deriving the total from the details. If you need absolutely to use subtotals, you can write a simple function that let you set/get the total, e.g.

Dim _total as Double

Sub SetTotal (ByVal total As Double) As Double

_total = total

return total

End Function

Function GetTotal() as Double

return _total

End Function

Then, in subtotal textboxes, change their values to =Code.SetTotal(Fields!FieldName.Value). When you need the total call GetTotal, e.g. =Code.GetTotal(). Of course, in real life it may need to write more complex logic to maintain the totals.

|||Thanks for the feedback, I'll give it a try I'm actually trying to create report to emulate growth over period(OLAP) for an Informix datasource I want to see the difference of the yearly subtotals Still exploring options|||was able to nest a table within a matrix and do all my calculations

expressions based on subtotals

How do I create calculated fields based on subtotals?

SSRS 2005 doesn't support aggregates over aggregates. You should try deriving the total from the details. If you need absolutely to use subtotals, you can write a simple function that let you set/get the total, e.g.

Dim _total as Double

Sub SetTotal (ByVal total As Double) As Double

_total = total

return total

End Function

Function GetTotal() as Double

return _total

End Function

Then, in subtotal textboxes, change their values to =Code.SetTotal(Fields!FieldName.Value). When you need the total call GetTotal, e.g. =Code.GetTotal(). Of course, in real life it may need to write more complex logic to maintain the totals.

|||Thanks for the feedback, I'll give it a try I'm actually trying to create report to emulate growth over period(OLAP) for an Informix datasource I want to see the difference of the yearly subtotals Still exploring options|||was able to nest a table within a matrix and do all my calculations

Expressions as a field value in a table

Is there a way to store an expression inside a table, and have the expression
being pulled from the table to execute during run time? I want to dynamically
create an expression based on the value of a column of the table. Using the
IIF statement will work but it's going to be a very long nested IIF.... and
that brings to the second question -> is there a limit for the length of an
expression?
Thanks in advance.There is no limit on the length of an expression (other than limits imposed
by the VB.NET compiler).
In your case, you may want to look at alternatives to the IIF function:
* =Choose(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
* =Switch(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
In addition, you may want to consider writing a function in custom code or
as custom assembly that contains your logic. You can then reuse the function
from RDL expressions.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"g3kamdbs" <g3kamdbs@.discussions.microsoft.com> wrote in message
news:2A1DC075-220C-4494-AF82-157760B3EBD5@.microsoft.com...
> Is there a way to store an expression inside a table, and have the
> expression
> being pulled from the table to execute during run time? I want to
> dynamically
> create an expression based on the value of a column of the table. Using
> the
> IIF statement will work but it's going to be a very long nested IIF....
> and
> that brings to the second question -> is there a limit for the length of
> an
> expression?
> Thanks in advance.|||Or a User Defined Function that the Stored Procedure calls.
GeoSynch
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uO3ZYBwvFHA.252@.TK2MSFTNGP09.phx.gbl...
> There is no limit on the length of an expression (other than limits imposed by
> the VB.NET compiler).
> In your case, you may want to look at alternatives to the IIF function:
> * =Choose(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
> * =Switch(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
> In addition, you may want to consider writing a function in custom code or as
> custom assembly that contains your logic. You can then reuse the function from
> RDL expressions.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "g3kamdbs" <g3kamdbs@.discussions.microsoft.com> wrote in message
> news:2A1DC075-220C-4494-AF82-157760B3EBD5@.microsoft.com...
>> Is there a way to store an expression inside a table, and have the expression
>> being pulled from the table to execute during run time? I want to dynamically
>> create an expression based on the value of a column of the table. Using the
>> IIF statement will work but it's going to be a very long nested IIF.... and
>> that brings to the second question -> is there a limit for the length of an
>> expression?
>> Thanks in advance.
>