Propogation of Null

In one of my tables I have to total a column but exclude some of the rows
from the total. I have the expression...
=SUM(Fields!IncludeInTotal.Value * Fields!AtoC.Value)
...where IncludeInTotal is integer value 1 or 0 and AtoC is numeric but
possibly null.
I was expecting a lot of blank cells on my output but instead got lots of
zeros. It seems that when AtoC is null the result of 1*AtoC isn't null but is
zero.
I've recoded to use...
=SUM(Iif( IsNothing(Fields!AtoC.Value) OrElse Fields!IncludeInTotal = 0,
Nothing, Fields!AtoC.Value )
I was very surprised by this behaviour (2005sp2) as I would've expected it
to behave like SQLServer and the null to propogate.
Is this by design? Is there a property against the report that sets this
behaviour as opposed to propogating nulls?
AndrewHello Andrew,
I got some confusion. Since you are using the SUM function, why you will
get a lot of zero?
Based on my test, I add a SUM function in the table footer and it get only
one result with the correct.
The table I use is like this:
AtoC IncludeIn ID
-- -- --
NULL 1 1
NULL 0 2
1 1 3
2 0 4
0 1 5
Could you please clarify it?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the reply.
The SUM is just confusing things, so ignore it. At its simplest just set up
a table and in the detail row set a cell to be 1*Fields!AtoC.Value. You'll
see that when AtoC is null you get zero displayed.
I wondered if it was a formatting issue and null was being displayed as
zero, so I modified one of the cells to be 10+(1*Fields!AtoC.Value) and got
10 when the AtoC is null.
It looks to me as though multiplying by null gives zero, rather than null.
Andrew|||Hello Andrew,
Yes. The Expression will change the Type to numeric and that force the Null
value to 0.
You need to use the IIF to specify whether it is null.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Properties Expressions

I can create an expression for the visibility property as follows:
=iif(countDistinct(Fields!RefNbr.Value)=1,True,False)
BUT when I try to do something similar for the fontweight property like
=iif( Fields!Carryover.Value = 2 ,Heavy,Normal) I get the following error message
Name Heavy is not declared
'Try: =iif( Fields!Carryover.Value = 2 ,"Heavy","Normal")
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:E4B78AB2-9D82-4B14-9A0A-2FFD4D41359D@.microsoft.com...
>I can create an expression for the visibility property as follows:
> =iif(countDistinct(Fields!RefNbr.Value)=1,True,False)
> BUT when I try to do something similar for the fontweight property like
> =iif( Fields!Carryover.Value = 2 ,Heavy,Normal) I get the following error
> message
> Name Heavy is not declared
> '|||Another gotcha is using font weight names in expression. When using a font
weight in an expression you have to use the serialized value.
In your case you will need to use =iif(Fields!Carryover.Value = 2, "900",
"Normal).
This chart shows how the named displyed in Report Designer's UI maps to the
serialized value.
UI Display Serialized
Lighter Lighter
Thin 100
Extra Light 200
Light Light
Normal Normal
Medium 500
Semi-Bold 600
Bold 700
Extra Bold 800
Heavy 900
Bolder Bolder
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:O86wRYyeEHA.384@.TK2MSFTNGP10.phx.gbl...
> Try: =iif( Fields!Carryover.Value = 2 ,"Heavy","Normal")
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:E4B78AB2-9D82-4B14-9A0A-2FFD4D41359D@.microsoft.com...
> >I can create an expression for the visibility property as follows:
> > =iif(countDistinct(Fields!RefNbr.Value)=1,True,False)
> >
> > BUT when I try to do something similar for the fontweight property like
> > =iif( Fields!Carryover.Value = 2 ,Heavy,Normal) I get the following
error
> > message
> >
> > Name Heavy is not declared
> >
> > '
>

Programmatically hiding chart on the report page.

I need to show or hide a chart on my my report based on the result of the
dataset.
I tied to assign an expression to Visible.Hidden property of the chart like
that "=First(Fields.Visible.value)" WHERE I assigned 0 or 1 to the 'Visible'
column of my dataset. It did not work. Is there a way to do that?
Thank you,The result of the Visibility.Hidden expression has to be a boolean value and
not an integer value.
Try this instead:
=(0 = First(Fields.Visible.value))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
news:A7C5B8B9-5D26-43EE-B82B-0EAF7418AA7F@.microsoft.com...
>I need to show or hide a chart on my my report based on the result of the
> dataset.
> I tied to assign an expression to Visible.Hidden property of the chart
> like
> that "=First(Fields.Visible.value)" WHERE I assigned 0 or 1 to the
> 'Visible'
> column of my dataset. It did not work. Is there a way to do that?
> Thank you,
>

Programmatically Evaluate Expression

I would like to evaluate expressions from within my execute function in a custom task. I saw this post from about 8 months ago detailing how it would be possible:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=117564&SiteID=1

Is there an updated response (or more detailed example) to the question? Am I really risking a lot by using this undocumented feature?

Thanks!

Hi David,

To quote Kirk from the thread you linked:

"...this is not documented and will not be supported by Microsoft. It could change at anytime and break your component if you use it. User beware. "

This remains the case. I encourage you to head over to the MSDN Product Feedback site at http://msdn.microsoft.com/sql/bi/integration/ and submit a suggestion for exposing this functionality.

Thanks
Mark

|||

Thanks for the feedback. I have submitted the suggestion:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=133078

Programaticly setting the value of a Select Query Property.

I have a query in which I'd like to use the username of the user currently logged in. The expression im using to retrieve the username is: Membership.GetUser().UserName.
Currently I have the following:
<asp:SqlDataSource ID="ProjectSource" runat="server" ConnectionString="<%$ ConnectionStrings:Code %>"
ProviderName="<%$ ConnectionStrings:Code.ProviderName %>" SelectCommand="Select Name, Namespace from Project where User = $Username">
<SelectParameters>
<asp:Parameter DefaultValue="" Name="$Username" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" DataSourceID="ProjectSource" />

I'd somehow like to set up $Username to be equivlent to Membership.GetUser().UserName.I'd be interested to hear of a better way, however, currently, I cheat. I stuff things like that when they authenticate to session variables (I only have 3 in my current project). Then I pull them in the sqldatasources from there.|||Yeah, my solution doesnt look to be much better. Currently Im Setting the default value every time I need to execute a query.

Programatically Evaluating SSIS Expression

Is there an object in the DTS object model that will allow me to evaluate an SSIS expression? I am trying to build a custom task that will require re-evaluation of an expression multiple times within the execute method and I can't seem to find a way to do this.

Thanks,

Adam

Add a reference to Microsoft.DataTransformationServices.Controls

Use the Microsoft.SqlServer.Dts.Runtime.Wrapper.ExpressionEvaluatorClass class. You will want to use DTSInfoEvents to capture error details when calling Evaluate or Validate. Pass the events to the Events property of the ExpressionEvaluatorClass.

Take a look at the File Watcher Task (http://www.sqlis.com/) for an example of this in action, just set an expression through the task UI to see the Expression Editor Dialog we have built in action. It use the ExpressionEvaluatorClass behind the scenes to provide the evaluation functionality.

Not documented, so not supported, but it works.|||As Darren said, this is not documented and will not be supported by Microsoft. It could change at anytime and break your component if you use it.
User beware.|||

I have logged a bug to document and publicly expose this, but it was on Beta Place, so if someone wants to do the Product Feedback thing I'll vote for it.

Programatic or expression for failure

Hello,

Is there a way is a script task or via a procedural expression to tell if a package is going to fail because of an error?

This would be used to fire something which should occure if the max number of errors/error severity to fail the package had been reached

Thanks

Paul

Hi Paul,

I'm unaware of a way to tell before execution that a package is going to encounter n number of errors. You can use the DtExec /Validate switch (or check the Validate package without executing checkbox in DtExecUI) to perform a package validation, but validation will not catch all conditions that can raise errors during execution.

You may want to check into SSIS Event Handlers. These allow you to respond to error conditions.

Hope this helps,

Andy

|||

Sorry I did not clarify the problem very well.

When a package is running and an OnError event handeler is called i want it to do somthing specific if the package will fail because of the error. usually a OnError would cause the package to fall over but i have changed the package to allowed errors to be 3 so not every error would cause the package to fail.

e.g.

Error 1 would send an email

Error 2 would send an email

Error 3 would send an email and move a file (the package would fail at this point)

|||

Hi Paul,

Oh, ok. Count the errors.

Create a package-scoped variable called iErrorCount of Int32 data type. Make the first task in your OnError Event Handler a Script Task that contains code similar to the following:

Code Snippet

Dim iErrCount as Integer =

Convert.ToInt32(

Dts.Variables("iErrorCount").Value)

iErrCount += 1

If iErrCount >= 3 Then

...

Hope this helps,

Andy