Formulas (that are correct) don't validate in EM

I have the following table in SQL server 2000 SP3:
CREATE TABLE [dbo].[Dates] ([ID] [int] NOT NULL ,
[EOMDate] [smalldatetime] NOT NULL ,
[BOMDate] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) +
1))) ,
[BOM3MonthsAgo] AS (dateadd(month,(-3),(convert(smalldatetime,[EOMDa
te])
+ 1))) ,
[EOMPrev] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) +
1))
- 1)
) ON [PRIMARY]
The idea here is simple; we enter the end-of-month date for the most
recent month where we have all the data, (when that happens is a little
unpredictable). The other dates are commonly used beginning-of-month
and the ending date of the previous month, etc. (Those convert
functions appeared when I scripted the table -- I didn't enter them.)
This create statement works fine, and I can fill in the EOMDate, and
everything else works perfectly.
UNLESS I edit any of the table definition in Enterprise Manager. Then I
get these messages from EM:
'Dates' table
- Error validating the formula for column 'BOMDate'.
- Error validating the formula for column 'EOMPrev'.
- Error validating the formula for column 'BOM3MonthsAgo'.
Why does EM not like these formulas? In EM, the BOMDate formula looks
like this:
(dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) + 1)))
Aside from the abundance of parentheses, what is EM complaining about?
Since it *works*, I think EM is confused.
Thanks for any insight on this.
David WalkerEM is not as sophisticated. That's one of the reasons why you should use
Query Analyzer and DDL statements for altering your table structures. You
will also see similar problems with view definitions in EM.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DW" <None> wrote in message news:eO85pTTDEHA.3888@.TK2MSFTNGP10.phx.gbl...
I have the following table in SQL server 2000 SP3:
CREATE TABLE [dbo].[Dates] ([ID] [int] NOT NULL ,
[EOMDate] [smalldatetime] NOT NULL ,
[BOMDate] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) +
1))) ,
[BOM3MonthsAgo] AS (dateadd(month,(-3),(convert(smalldatetime,[EOMDa
te])
+ 1))) ,
[EOMPrev] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) +
1))
- 1)
) ON [PRIMARY]
The idea here is simple; we enter the end-of-month date for the most
recent month where we have all the data, (when that happens is a little
unpredictable). The other dates are commonly used beginning-of-month
and the ending date of the previous month, etc. (Those convert
functions appeared when I scripted the table -- I didn't enter them.)
This create statement works fine, and I can fill in the EOMDate, and
everything else works perfectly.
UNLESS I edit any of the table definition in Enterprise Manager. Then I
get these messages from EM:
'Dates' table
- Error validating the formula for column 'BOMDate'.
- Error validating the formula for column 'EOMPrev'.
- Error validating the formula for column 'BOM3MonthsAgo'.
Why does EM not like these formulas? In EM, the BOMDate formula looks
like this:
(dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) + 1)))
Aside from the abundance of parentheses, what is EM complaining about?
Since it *works*, I think EM is confused.
Thanks for any insight on this.
David Walker|||So it's a bug in EM, or maybe a lack of interest on the developers' part
in making it work correctly? That's good to know, so I won't rely on EM
or believe what it says. It might throw some newbies off, though.
Doesn't EM just send the definition through as Alter Table statements?
Those shouldn't fail. Strange...
Thanks.
David Walker
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in
news:Oh1KLxTDEHA.3584@.TK2MSFTNGP11.phx.gbl:

> EM is not as sophisticated. That's one of the reasons why you should
> use Query Analyzer and DDL statements for altering your table
> structures. You will also see similar problems with view definitions
> in EM. --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "DW" <None> wrote in message
> news:eO85pTTDEHA.3888@.TK2MSFTNGP10.phx.gbl... I have the following
> table in SQL server 2000 SP3:
> CREATE TABLE [dbo].[Dates] ([ID] [int] NOT NULL ,
> [EOMDate] [smalldatetime] NOT NULL ,
> [BOMDate] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate])
+
> 1))) ,
> [BOM3MonthsAgo] AS
> (dateadd(month,(-3),(convert(smalldatetime,[EOMDate]) + 1))) ,
> [EOMPrev] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate])
+
> 1)) - 1)
> ) ON [PRIMARY]
> The idea here is simple; we enter the end-of-month date for the most
> recent month where we have all the data, (when that happens is a
> little unpredictable). The other dates are commonly used
> beginning-of-month and the ending date of the previous month, etc.
> (Those convert functions appeared when I scripted the table -- I
> didn't enter them.)
> This create statement works fine, and I can fill in the EOMDate, and
> everything else works perfectly.
> UNLESS I edit any of the table definition in Enterprise Manager. Then
> I get these messages from EM:
> 'Dates' table
> - Error validating the formula for column 'BOMDate'.
> - Error validating the formula for column 'EOMPrev'.
> - Error validating the formula for column 'BOM3MonthsAgo'.
> Why does EM not like these formulas? In EM, the BOMDate formula looks
> like this:
> (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) + 1)))
> Aside from the abundance of parentheses, what is EM complaining about?
> Since it *works*, I think EM is confused.
> Thanks for any insight on this.
> David Walker
>
>|||Hi David,
Yes, when you press YES when the prompt titled ' Validation Warnings', the
Enterprise Manager will send the Alter statement to change the definition
of the table. However, before that there is some pre-validation process for
the table with formulas. It have to check all the constraints and use the
formulas to re-acount the related columns. This process is complecated and
this is during this process that the warning message prompt. It is just a
warning and would not prevent you from alter the table. As Vyas said, you
could use the Query Analyzer to alter the table without get any warning
messages so as your client application.
Hope this helps.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Formula Question

I have a field "Accept" that can be Null or contain either "Y" or "N". In my
test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
want a count of each case in the footer along with some descriptive text. I
tried the formula
=Count(Fields!Accept.Value="Y")
But it returns "4"? The same thing happens if I use
=Count(Fields!Accept.Value="N")
What is wrong in this formula?
Also, I really want the report to display something like: "Number of
Accepts: 2" but if I try to code the field as:
"Number of Accepts: " & =Count(Fields!Accept.Value="Y")
the formula displays as text?
TIA
Waynetry to write:
= "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
Good Luck
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
> I have a field "Accept" that can be Null or contain either "Y" or "N". In
my
> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
> want a count of each case in the footer along with some descriptive text.
I
> tried the formula
> =Count(Fields!Accept.Value="Y")
> But it returns "4"? The same thing happens if I use
> =Count(Fields!Accept.Value="N")
> What is wrong in this formula?
> Also, I really want the report to display something like: "Number of
> Accepts: 2" but if I try to code the field as:
> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
> the formula displays as text?
>
> TIA
> Wayne
>
>|||Liz;
Thank you very much. I never would have figured that one out. Seems like
making a point the hard way. I've ordered a couple of books that I hope will
explain the logic of creating expressions.
Wayne
"Liz Matyas" <lizmts@.mail.com> wrote in message
news:uK3U8FZ3FHA.1276@.TK2MSFTNGP09.phx.gbl...
> try to write:
> = "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
> Good Luck
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
>> I have a field "Accept" that can be Null or contain either "Y" or "N". In
> my
>> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
>> want a count of each case in the footer along with some descriptive text.
> I
>> tried the formula
>> =Count(Fields!Accept.Value="Y")
>> But it returns "4"? The same thing happens if I use
>> =Count(Fields!Accept.Value="N")
>> What is wrong in this formula?
>> Also, I really want the report to display something like: "Number of
>> Accepts: 2" but if I try to code the field as:
>> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
>> the formula displays as text?
>>
>> TIA
>> Wayne
>>
>>
>|||The Count RDL aggregate function works similar as the Count aggregate in
SQL: it will count all rows that are NOT NULL. The expresson
Fields!Accept.Value="Y" will return a boolean value (true/false) for all
rows, that's why the count results in the value 4.
Besides using =Sum(iif(...)) as shown by Liz, you could also use
=Count(iif(Fields!Accept.Value="Y", 1, Nothing))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:u8BwZda3FHA.128@.tk2msftngp13.phx.gbl...
> Liz;
> Thank you very much. I never would have figured that one out. Seems like
> making a point the hard way. I've ordered a couple of books that I hope
> will explain the logic of creating expressions.
> Wayne
> "Liz Matyas" <lizmts@.mail.com> wrote in message
> news:uK3U8FZ3FHA.1276@.TK2MSFTNGP09.phx.gbl...
>> try to write:
>> = "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
>> Good Luck
>>
>> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
>> news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
>> I have a field "Accept" that can be Null or contain either "Y" or "N".
>> In
>> my
>> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
>> want a count of each case in the footer along with some descriptive
>> text.
>> I
>> tried the formula
>> =Count(Fields!Accept.Value="Y")
>> But it returns "4"? The same thing happens if I use
>> =Count(Fields!Accept.Value="N")
>> What is wrong in this formula?
>> Also, I really want the report to display something like: "Number of
>> Accepts: 2" but if I try to code the field as:
>> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
>> the formula displays as text?
>>
>> TIA
>> Wayne
>>
>>
>>
>|||Robert;
Thanks for that additional information. It helps me understand the process.
Wayne
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eZK0yma3FHA.3036@.TK2MSFTNGP10.phx.gbl...
> The Count RDL aggregate function works similar as the Count aggregate in
> SQL: it will count all rows that are NOT NULL. The expresson
> Fields!Accept.Value="Y" will return a boolean value (true/false) for all
> rows, that's why the count results in the value 4.
> Besides using =Sum(iif(...)) as shown by Liz, you could also use
> =Count(iif(Fields!Accept.Value="Y", 1, Nothing))
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:u8BwZda3FHA.128@.tk2msftngp13.phx.gbl...
>> Liz;
>> Thank you very much. I never would have figured that one out. Seems like
>> making a point the hard way. I've ordered a couple of books that I hope
>> will explain the logic of creating expressions.
>> Wayne
>> "Liz Matyas" <lizmts@.mail.com> wrote in message
>> news:uK3U8FZ3FHA.1276@.TK2MSFTNGP09.phx.gbl...
>> try to write:
>> = "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
>> Good Luck
>>
>> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
>> news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
>> I have a field "Accept" that can be Null or contain either "Y" or "N".
>> In
>> my
>> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null.
>> I
>> want a count of each case in the footer along with some descriptive
>> text.
>> I
>> tried the formula
>> =Count(Fields!Accept.Value="Y")
>> But it returns "4"? The same thing happens if I use
>> =Count(Fields!Accept.Value="N")
>> What is wrong in this formula?
>> Also, I really want the report to display something like: "Number of
>> Accepts: 2" but if I try to code the field as:
>> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
>> the formula displays as text?
>>
>> TIA
>> Wayne
>>
>>
>>
>>
>sql

Formatting "scoped" calculated members

Hi,

How do I format calculated members that have been modified in a Scope-statement?

Example:

CREATE MEMBER CURRENTCUBE.Test1
AS NULL,
FORMAT_STRING = "#,#.00",
NON_EMPTY_BEHAVIOR = { [Measure1], [Measure2] },
VISIBLE = 1 ;

SCOPE ([Company].[Company].children,[Measures].[Test1]);
This = [Measures].[Measure1]/[Measures].[Measure2];
END SCOPE;

My calculated member "Test1" looses its formatting in the Scope-statement and is therefore no longer formatted as "#,#.00". How can I change this?

Try the following:

CREATE MEMBER CURRENTCUBE.Test1
AS NULL,
FORMAT_STRING = "#,#.00",
NON_EMPTY_BEHAVIOR = { [Measure1], [Measure2] },
VISIBLE = 1 ;

SCOPE ([Company].[Company].children,[Measures].[Test1]);
This = [Measures].[Measure1]/[Measures].[Measure2];
FORMAT_STRING(THIS) = '#,#.00';
END SCOPE;

formating in crystal reports

How to suppress the display of a template field or the text field in crystal report on its value being NULL or empty string.

This works only for numbers, it was not possible for me to do so for strings.

Can anybody help me in this regard.

This is a forum for discussion of SQL Server Reporting Services, not Crystal Reports...you might get lucky, but I don't think anyone will be able to answer your question here. Try the businessobjects website and see if they have some sort of public forum system -- I bet you'll have better responses there?

format output as scientific notation (was "sql 2000 question")

SELECT membername, outputval
case when choice = 0 then outputval else null end as outputval
from MyDatabase
group by membername, outputval

how to format outputval:
if outputval < 40000
format outputval as:
5 - 5.78 - 6.9 - 6,778 - 4,567.8 - 12,456.78 - etc.
if outputval >= 40000
format it as a scientific.I would strongly suggest that you do the formatting at either the web server (using PHP), or better yet at the client (possibly using Javascript). This would allow you to use locale specific information that isn't usually available to SQL Server.

If you really have to format the output at the SQL Server, it could be done using a SQL UDF that called xp_sprintf or other related tools. This means that every query will need to be formatted using the locale of your SQL Server, which is usually a huge problem for application globalization.

-PatP

Form submission showing null values

I have designed a for to submit some info to a sql database, but when i enter the information to the form fields and click submit, I get an error saying that a null value cannot be submitted, when it should not be null.

Not to sound whiney, but i really need a code fix, not links to msdn pages that define properties (usually dont do this, but i have a deadline to submit this to my tech admin of my company's site)

Here is the code, and THANK YOU to anyone who helps me with this!!

<%

@.PageLanguage="VB"MasterPageFile="~/MasterPage.master" %>

<

scriptrunat="server">PrivateSub Submitdata(ByVal SourceAsObject,ByVal eAs EventArgs)

SqlDataSource1.Insert()

EndSub' Submitdata

</

script>

<

asp:ContentID="Content1"runat="server"ContentPlaceHolderID="ContentPlaceHolder1"><asp:sqldatasourceid="SqlDataSource1"runat="server"connectionstring="<%$ ConnectionStrings:clientInfoConnectionString %>"selectcommand="SELECT Client Name,Client Address FROM Clients"insertcommand="INSERT INTO Clients (ClientName,ClientAddress) VALUES (@.CName,@.CAddress)"><insertparameters><asp:formparametername="CName"formfield="Namebox"/><asp:formparametername="CAddress"formfield="Addressbox"/></insertparameters></asp:sqldatasource> Name:<br/><asp:TextBoxID="Namebox"runat="server"/><br/>

<asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="Namebox"ErrorMessage="Please Enter A Name"></asp:RequiredFieldValidator>

<br/>Address:<br/><asp:TextBoxID="Addressbox"runat="server"/><br/>

<asp:RequiredFieldValidatorID="RequiredFieldValidator2"runat="server"ControlToValidate="Addressbox"ErrorMessage="Please Enter An Address"></asp:RequiredFieldValidator>

<br/><asp:ButtonID="Submitbtn"runat="server"Text="Submit"OnClick="Submitdata"/><br/>

</

asp:Content>

Thanks,

The King

If the columns accept null values your code without any change will work.To Insert into a column wich donot accept null this is the work around.

Make a small change in your code to make it work.

Remove the input parameter specified in the design and add the same in your code .so this part

<insertparameters>

<asp:formparametername="CName"formfield="Namebox"/>

<asp:formparametername="CAddress"formfield="Addressbox"/>

</insertparameters>

can be removed.

In the submit click add this code

SqlDataSource1.InsertParameters.Add(

"CName", Namebox.Text)

SqlDataSource1.InsertParameters.Add(

"CAddress", Addressbox.Text)

Now you are done.

|||

All I can say is

Thank You!!!!!!

It finally works!

Thanks so much,

The King