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.

Proplem w/Default Value for Parameter

I have a Parameter with 2 non-queried choices, Yes and No. I set the default
to No. When the report displays, instead of the default value displaying in
the dropdown, the users are being forced to choose. any ideas what would
cause this or how to fix?
TIA
deanI have a yes and no parameter. The labels say Yes and No and the values are
Y and N. You can either put in Y or ="Y" (either syntax works).
Also, it is case sensitive. So be sure the default you put in matches the
value not the label AND you are matching the case.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dean" <deanl144@.hotmail.com.nospam> wrote in message
news:Os5F3I6$HHA.4200@.TK2MSFTNGP04.phx.gbl...
>I have a Parameter with 2 non-queried choices, Yes and No. I set the
>default to No. When the report displays, instead of the default value
>displaying in the dropdown, the users are being forced to choose. any ideas
>what would cause this or how to fix?
> TIA
> dean
>|||I fixed this myself by deleting my deployed report and redeploying.
"Dean" <deanl144@.hotmail.com.nospam> wrote in message
news:Os5F3I6$HHA.4200@.TK2MSFTNGP04.phx.gbl...
>I have a Parameter with 2 non-queried choices, Yes and No. I set the
>default to No. When the report displays, instead of the default value
>displaying in the dropdown, the users are being forced to choose. any ideas
>what would cause this or how to fix?
> TIA
> dean
>sql

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
> >
> > '
>

Proper use of the Right Function

RIGHT(value, numberofchars)
select right(username, 6) from ...
Seems like a reference to Books Online would have done this for you much
more efficiently than a forum post. :-)
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"fiaola" <fiaola@.mail.com> wrote in message
news:O9fJ6tLVIHA.5448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I tried to use the right function as, "Select right(UserName) as UN from
> Table1" and it returned "blank".
> It works well with Left. Im puzzled.
> Thanks
>
"fiaola" <fiaola@.mail.com> wrote in message
news:uIiqWJuVIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Thanks for the reply. I did that, but it returned blank values. If i
> tried the LEFT function, it works well.
> My field is a character, and I could not figure out why it does not pickup
> the parameters requested.
>
What is the username you're using?
Perhaps the 6 right most characters are blank?
Can you post the actual code.
Thanks.

> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13ogib06s428b79@.corp.supernews.com...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Prompt for value

What is the sql code to prompt for a value?
Can I do this in query analyzer or do I have to send the value from some other application?the query analyser cannot prompt the user for a value.|||Prompting for values is the responsibility of the application interface. Query Analyzer is a tool, but it is by no means an application interface.

prompt for value

How do I prompt for a value in my SQL query for MS SQL Server 2000. Eventually I would like to enter the value in excel that passes it to a pivot table and then to the sql query.You need to have Excel prompt for the value. SQL Server doesn't permit interactive things within Transact-SQL.

-PatP

Progress Bar in Table Cell

I'm creating a report with several columns. One of which is a percentage
value. The business people have asked that that column show a progress bar of
sorts to give a visual indicator of the percentage complete, in addition to
the textual %. I have tried to figure out how to do this in a table cell but
to no avail. It appears that you can't use an Expression to set the sizing of
an image, rectangle, etc. Anyone have an idea on how this might be done?
Thanks!you could try a chart (one of the bar graphs that are horizontal based).Then
maybe format the chart to remove the legend, scale, title etc
"rSmoke" wrote:
> I'm creating a report with several columns. One of which is a percentage
> value. The business people have asked that that column show a progress bar of
> sorts to give a visual indicator of the percentage complete, in addition to
> the textual %. I have tried to figure out how to do this in a table cell but
> to no avail. It appears that you can't use an Expression to set the sizing of
> an image, rectangle, etc. Anyone have an idea on how this might be done?
> Thanks!|||I had tried that, except you can't put a chart in a table cell. However, your
post led me to try a subreport with a chart in it. That works great. I had
tried a subreport before but hadn't thought to try a chart in it. Thanks for
the help.
"NH" wrote:
> you could try a chart (one of the bar graphs that are horizontal based).Then
> maybe format the chart to remove the legend, scale, title etc
> "rSmoke" wrote:
> > I'm creating a report with several columns. One of which is a percentage
> > value. The business people have asked that that column show a progress bar of
> > sorts to give a visual indicator of the percentage complete, in addition to
> > the textual %. I have tried to figure out how to do this in a table cell but
> > to no avail. It appears that you can't use an Expression to set the sizing of
> > an image, rectangle, etc. Anyone have an idea on how this might be done?
> > Thanks!|||Note: you can put a chart inside a table group header or group footer. In
that case, the chart would be based of all data rows of that particular
group instance (which is what you typically want). The subreport approach
works but is usually not as efficient as table groups.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"rSmoke" <rSmoke@.discussions.microsoft.com> wrote in message
news:7CC6307F-077B-45A8-9BBD-18C451815AD4@.microsoft.com...
>I had tried that, except you can't put a chart in a table cell. However,
>your
> post led me to try a subreport with a chart in it. That works great. I had
> tried a subreport before but hadn't thought to try a chart in it. Thanks
> for
> the help.
> "NH" wrote:
>> you could try a chart (one of the bar graphs that are horizontal
>> based).Then
>> maybe format the chart to remove the legend, scale, title etc
>> "rSmoke" wrote:
>> > I'm creating a report with several columns. One of which is a
>> > percentage
>> > value. The business people have asked that that column show a progress
>> > bar of
>> > sorts to give a visual indicator of the percentage complete, in
>> > addition to
>> > the textual %. I have tried to figure out how to do this in a table
>> > cell but
>> > to no avail. It appears that you can't use an Expression to set the
>> > sizing of
>> > an image, rectangle, etc. Anyone have an idea on how this might be
>> > done?
>> > Thanks!

Programmatically Specify SqlDataSource Parameters

I have a GridView bound to a SqlDataSource. On page load I would like to programmatically specify a SelectParameter value based on the role of the user. This SelectParameter will be used in my WHERE clause. The closest post I can find ishttp://forums.asp.net/thread/1233258.aspx but no answer was posted.

What code would I use to modify a SelectParameters value? Is it possible to reference a parameter by name (SqlDataSource1.SelectParameters["usertype"]) or does it have to be by index? (SqlDataSource1.SelectParameters[0])

Alternatively, perhaps I'm going about this in the wrong way, is there a better way to have dynamic GridView content based on the role of the user?

Thank you very much for your help.

Yes, you can set the value of a select parameters by name, for example

SqlDataSource1.SelectParameters["usertype"].DefaultValue ="role"

|||

Both parameter name and index are working here.

protected

void Page_Load(object sender,EventArgs e)

{

SqlDataSource1.SelectParameters[0].DefaultValue=

"yourvalue";

or

SqlDataSource1.SelectParameters["usertype"].DefaultValue="yourvalue";

}

|||

You can also set the value in the SqlDataSource_Selecting event via the e parameter:

Private Sub SqlDataSource1_Selecting(stuff)

e.command.paramter("param1").value=something

end sub

Programmatically set a Flat File Connection Manager

Hello,

I need to know how I can programmatically set a Flat File Connection Manager's Column Delimiter value.

The Data Warehouse project I am working on, receives daily information feeds that could contain one of two delimiters. Which is just dumb...anyways, as it is now we have two seperate Data Flow Tasks which handle these two delimiters. Currently we have a script taks that "sneak previews" each incoming flat file to determine which delimiter it has, and direct our flow to the correct Data Flow Task to handle it.

I do not want to have to maintain 2 DFTs. How can I get around this problem?

Even if there is a way to do this by passing variables/setting expressions in the Flat File Connection manager, I would do that. Does not necessarily HAVE to be a pure programmatic approach.

ANY help would be greatly appreciated!

Feel free to email me at ccorbin@.topcoder.com with any questions, or leave me some good news here :)

THANKS!

Chris Corbin

Software Developer

TopCoder Inc.

I thought this was a simple question. Apparently, you can use an expression to set every property of the File Connection Manager except the column delimiter. Maybe that will change in the near future. There must be a reason.|||ColumnDelimiter is a property of the FlatFileColumn, and not the connection manager. Due to which currently it is not expressionable. Feel free to raise a DCR(Design Change Request) for this issue.|||

How would I go about raising a DCR?

Thanks

|||

To file a bug/DCR, start here:

http://connect.microsoft.com/SQLServer

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.

program flow question

I am trying to figure out how to make a report stop at a certain value. For example say I have five categories that I can assign an item. I would like to be able to have the report pull just the first category that applies to the item. For instance I have a bike that falls under categories 1 and 3 and a car the falls under 2, 3, and 5. How can I make it so the report will only show the details that apply to the first category the item falls under. Thanks.

So do you have a category field coming in the dataset agianst your items, and user is gonna select one category or may be more than one.

If i got it correct this can be done by using filter the dataset for that parameter may be using where clause or may be using dataset filter or even at table filter.

|||Actually I was able to solve this problem. I changed the view I was pulling from so the categories would be assigned numbers. Then I declare a varable and set it to Min(category) with the same from and where clauses. Then I just add to the where clause to have the catergory equal the varable set to min.