formula not working

This does nothing:
([rush24] + [rush6] + [addLocationsTotal] + 50)

This gives me my total correctly:
([rush24] + [rush6] + 50)

All column are numeric(9) except the "total" column which is numeric(13).

Why is this happening?

I did not quite understand your question, could you describe your issue in more detail?sql

Formula Issue

Thanks in advance for any help.
I am spoofing rows to force an X # of rows in a table, which is working
well. However, the spoofed rows are causing an error with the formula
below.
=cstr(iif(Fields!ServiceEnd.Value.ToString = "", " ",
cstr(DatePart("m", Fields!ServiceEnd.Value))))
Without the formula, the rows have no data and no error. Any
suggestions?
Thanks,
MorganNot quite sure what yo're trying to do but might one of these formulas work:
=Iif(IsDate(Fields!ServiceEnd), cstr(DatePart("m",
Fields!ServiceEnd.Value)), "")
OR
=Iif(IsNothing(Fields!ServiceEnd), "", cstr(DatePart("m",
Fields!ServiceEnd.Value)))
HTH
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Morgan" wrote:
> Thanks in advance for any help.
> I am spoofing rows to force an X # of rows in a table, which is working
> well. However, the spoofed rows are causing an error with the formula
> below.
> =cstr(iif(Fields!ServiceEnd.Value.ToString = "", " ",
> cstr(DatePart("m", Fields!ServiceEnd.Value))))
> Without the formula, the rows have no data and no error. Any
> suggestions?
>
> Thanks,
> Morgan
>|||Thank you.
A slightly modified version of your suggestions seemed to do the trick.
=iif(IsDate(Fields!ServiceBegin.Value), Day(Fields!ServiceBegin.Value),
"")
magendo_man (donotspam) wrote:
> Not quite sure what yo're trying to do but might one of these formulas work:
> =Iif(IsDate(Fields!ServiceEnd), cstr(DatePart("m",
> Fields!ServiceEnd.Value)), "")
> OR
> =Iif(IsNothing(Fields!ServiceEnd), "", cstr(DatePart("m",
> Fields!ServiceEnd.Value)))
> HTH
> --
> Magendo_man
> Freelance SQL Reporting Services developer
> Stirling, Scotland
>
> "Morgan" wrote:
> > Thanks in advance for any help.
> >
> > I am spoofing rows to force an X # of rows in a table, which is working
> > well. However, the spoofed rows are causing an error with the formula
> > below.
> >
> > =cstr(iif(Fields!ServiceEnd.Value.ToString = "", " ",
> > cstr(DatePart("m", Fields!ServiceEnd.Value))))
> >
> > Without the formula, the rows have no data and no error. Any
> > suggestions?
> >
> >
> > Thanks,
> >
> > Morgan
> >
> >

Forms authentication sample

After successfully setting up RS on a Win2k server I've been trying to
get forms authentication sample working. I went through all the steps
given at
http://msdn.microsoft.com/sql/sqlwarehouse/reportingservices/default.aspx?pull=/library/en-us/dnsql2k/html/ufairs.asp
but when I get to the login page and try to register a user I get the
following:
An error occurred while attempting to add the account. SQL Server does
not exist or access denied.
I am having trouble finding any documentation on this. Has anyone run
into a similar problem before, and if so, what was the solution?See my other post regarding forms authentication and mixed security. Does
that solve the problem?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Oleg Raskin" <oleg@.rhd.org> wrote in message
news:1103132329.322870.140150@.f14g2000cwb.googlegroups.com...
> After successfully setting up RS on a Win2k server I've been trying to
> get forms authentication sample working. I went through all the steps
> given at
> http://msdn.microsoft.com/sql/sqlwarehouse/reportingservices/default.aspx?pull=/library/en-us/dnsql2k/html/ufairs.asp
> but when I get to the login page and try to register a user I get the
> following:
> An error occurred while attempting to add the account. SQL Server does
> not exist or access denied.
> I am having trouble finding any documentation on this. Has anyone run
> into a similar problem before, and if so, what was the solution?
>sql

Forms Authentication sample

After successfully setting up RS on a Win2k server I've been trying to
get forms authentication sample working. I went through all the steps
given in the MSDN article but when I get to the login page and try to
register a user I get the following:
An error occurred while attempting to add the account. SQL Server does
not exist or access denied.
It's worth mentioning that RS and the database reside on 2 different
servers, but this setup was working just fine with NT-based
authentication.
I am having trouble finding any documentation on this. Has anyone run
into a similar problem before, and if so, what was the solution?
Thanks for any info.Hope you implemented your Custom Security correctly...
Try to debug -custom security- when trying to login.
b/w are you trying to login using using "Username" ?
I am able to get this(form authen) working in my last two projects and it
always works like a charm
"Oleg Raskin" wrote:
> After successfully setting up RS on a Win2k server I've been trying to
> get forms authentication sample working. I went through all the steps
> given in the MSDN article but when I get to the login page and try to
> register a user I get the following:
> An error occurred while attempting to add the account. SQL Server does
> not exist or access denied.
> It's worth mentioning that RS and the database reside on 2 different
> servers, but this setup was working just fine with NT-based
> authentication.
> I am having trouble finding any documentation on this. Has anyone run
> into a similar problem before, and if so, what was the solution?
> Thanks for any info.
>|||Is your database set to mixed security? I've had some problems with the
ReportServer service connecting to a database using SQL authentication until
I changed the Log On account (Control Panel > Administrative Tools >
Services > ReportServer) to an account that the database recognizes. I
know, that seems weird to me too, since I'm using SQL authentication for
this connection.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Oleg Raskin" <oleg@.rhd.org> wrote in message
news:1103133182.531381.92650@.z14g2000cwz.googlegroups.com...
> After successfully setting up RS on a Win2k server I've been trying to
> get forms authentication sample working. I went through all the steps
> given in the MSDN article but when I get to the login page and try to
> register a user I get the following:
> An error occurred while attempting to add the account. SQL Server does
> not exist or access denied.
> It's worth mentioning that RS and the database reside on 2 different
> servers, but this setup was working just fine with NT-based
> authentication.
> I am having trouble finding any documentation on this. Has anyone run
> into a similar problem before, and if so, what was the solution?
> Thanks for any info.
>

Forms Authentication Not Working

I have installed SQL Server September CTP (Full Install) on a Windows 2003
Server with IIS 6 setup with SSL. I have also installed Visual Studio 2005
(Full Install).
I installed the custom security extension example that comes with the server
install and the AdventureWorks sample reports.
When I login to the web-based Report Manager I get the following error:
Key cannot be null. Parameter name: key
The error message is displayed on an rs manager like interface with no links.
I followed the instructions that came with the Custom security extension to
the tee. I have tried several different configurations including non-SSL and
nothing works.
Does anyone know what this error pertains to or has ANYONE been able to get
Forms Authentication work AT ALL? Microsoft is not responding to any of our
posts so I guess it's up to us.
Thanks in advance.I am having the same problem with the final release version. Did you ever
resolve this issue?
"Zach" wrote:
> I have installed SQL Server September CTP (Full Install) on a Windows 2003
> Server with IIS 6 setup with SSL. I have also installed Visual Studio 2005
> (Full Install).
> I installed the custom security extension example that comes with the server
> install and the AdventureWorks sample reports.
> When I login to the web-based Report Manager I get the following error:
> Key cannot be null. Parameter name: key
> The error message is displayed on an rs manager like interface with no links.
> I followed the instructions that came with the Custom security extension to
> the tee. I have tried several different configurations including non-SSL and
> nothing works.
> Does anyone know what this error pertains to or has ANYONE been able to get
> Forms Authentication work AT ALL? Microsoft is not responding to any of our
> posts so I guess it's up to us.
> Thanks in advance.

Forms authentication fun

Anyway, I finally get forms authentication up and working (I think). I use the included example. I finally get to the point where I register a user. Then I modify the rsreportservice.config file to make the new user the 'admin' user. Now when I login, I have no permissions to anything. I restart the entire server. Now when I login I get a "Key cannot be null. Parameter name: key:". Any ideas here? Why the heck didn't ms just bake forms authentication as a selectable option! It is so complicated to setup, you have to modify like a bjillion config files.

I had exactly the same problem today.

Are you using the VB code sample? I usually code in C# but the customer I'm working with prefers VB so I used the VB sample solution and got the error you mention.

Using the C# sample solution instead works fine, in theory you should be able to just build the C# solution and drop the assembly in the appropriate folders.

I've not had a chance to take a look at what the difference is between the VB/C# solutions yet but that should get you going.

Totally agree with the config file madness.

Cheers,

Keith.

|||

Keith,

The problem is that the FormsAuthentication sample is incomplete and simply does not work. Only with help from other people on the forum was I able to get it working. I can email some working sample code to you if you wish.

|||

Hello Jon,

Can you send me some working example of FormsAuthentication?
A try Microsoft's example, but, isn’t working :(

Thanks!

Forms authentication fun

Anyway, I finally get forms authentication up and working (I think). I use the included example. I finally get to the point where I register a user. Then I modify the rsreportservice.config file to make the new user the 'admin' user. Now when I login, I have no permissions to anything. I restart the entire server. Now when I login I get a "Key cannot be null. Parameter name: key:". Any ideas here? Why the heck didn't ms just bake forms authentication as a selectable option! It is so complicated to setup, you have to modify like a bjillion config files.

I had exactly the same problem today.

Are you using the VB code sample? I usually code in C# but the customer I'm working with prefers VB so I used the VB sample solution and got the error you mention.

Using the C# sample solution instead works fine, in theory you should be able to just build the C# solution and drop the assembly in the appropriate folders.

I've not had a chance to take a look at what the difference is between the VB/C# solutions yet but that should get you going.

Totally agree with the config file madness.

Cheers,

Keith.

|||

Keith,

The problem is that the FormsAuthentication sample is incomplete and simply does not work. Only with help from other people on the forum was I able to get it working. I can email some working sample code to you if you wish.

|||

Hello Jon,

Can you send me some working example of FormsAuthentication?
A try Microsoft's example, but, isn’t working :(

Thanks!

Forms Authentication Finally Working!!

I finally got forms authentication to work, but when I create a new user and
log in, I don't get any menus. My question is how do I assign the user I
created access to the appropriate menus (e.g., view report list,
subscriptions, etc.)?
The sample dB is different then the ReportServer dB.Ok I am an idiot and forgot to go into the RSReportServer.config file to
enter the name of the user i created. For all of you that had this problem,
this is what you needed to do!
"Neo" wrote:
> I finally got forms authentication to work, but when I create a new user and
> log in, I don't get any menus. My question is how do I assign the user I
> created access to the appropriate menus (e.g., view report list,
> subscriptions, etc.)?
> The sample dB is different then the ReportServer dB.|||Hi Neo
Where do I include this user in RSReportServer.config. The
<AdminConfiguration> portion in for the admin user I guess....
thanks
Angelo
"Neo" wrote:
> Ok I am an idiot and forgot to go into the RSReportServer.config file to
> enter the name of the user i created. For all of you that had this problem,
> this is what you needed to do!
> "Neo" wrote:
> > I finally got forms authentication to work, but when I create a new user and
> > log in, I don't get any menus. My question is how do I assign the user I
> > created access to the appropriate menus (e.g., view report list,
> > subscriptions, etc.)?
> >
> > The sample dB is different then the ReportServer dB.|||Hi Angelo,
You need to find the <security> element and change the "username." Please
see the sample below:
<Security>
<Extension Name="Forms"
Type="Microsoft.Samples.ReportingServices.CustomSecurity.Authorization,
Microsoft.Samples.ReportingServices.CustomSecurity" >
<Configuration>
<AdminConfiguration>
<UserName>changethistoyourusername</UserName>
</AdminConfiguration>
</Configuration>
</Extension>
</Security>
Sorry about the word wrap. HTH.
"Mathi" wrote:
> Hi Neo
> Where do I include this user in RSReportServer.config. The
> <AdminConfiguration> portion in for the admin user I guess....
> thanks
> Angelo
> "Neo" wrote:
> > Ok I am an idiot and forgot to go into the RSReportServer.config file to
> > enter the name of the user i created. For all of you that had this problem,
> > this is what you needed to do!
> >
> > "Neo" wrote:
> >
> > > I finally got forms authentication to work, but when I create a new user and
> > > log in, I don't get any menus. My question is how do I assign the user I
> > > created access to the appropriate menus (e.g., view report list,
> > > subscriptions, etc.)?
> > >
> > > The sample dB is different then the ReportServer dB.

Forms authentication and VB

Does anyone have forms authentication working with calls from VB? We've got
the custom security working through the standard interface, but can't get
LogonUser calls to work from a VB.NET app. Any input would be appreciated.Are you calling the web service using VB? I imagine you'll need to
implement a report server proxy, similar to the code in the forms
authentication sample. I haven't heard back from Microsoft or anyone with
experience yet whether the application will need to be a web service or web
app itself to have visibility to the formsAuthCookie.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"R. Hamilton" <roark.hamilton@.cionex.com> wrote in message
news:%23oLfkL38EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Does anyone have forms authentication working with calls from VB? We've
> got
> the custom security working through the standard interface, but can't get
> LogonUser calls to work from a VB.NET app. Any input would be
> appreciated.
>

FormAuthentication

Hi i succeeded in just getting of ReportManager,ReportServer browsers.
Now problem r
*I am not working with RS Programming .I am working all by designing only*
1)After i registerd name and password i can't able to logon into
reportmanager.
I am getting error like
" The request failed with HTTP status 401: Access Denied. "
2)I am loging on to reportserver but i can't view the reports .
Here error is like
The encrypted value for configuration setting
UnattendedExecutionAccount\UserName cannot be decrypted.
(rsFailedToDecryptConfigInformation) Get Online Help
Invalid length for a Base-64 char array.
Now where i made mistake.
if u know plz help me
Thanks!
simmiThis happens due to the fact that RS cannot decrypt the logonCred element in
the RSReportServer.config. Did you by any chance made any mods to the
Machine.config file ' To my understanding what happens is when you are
installing the RS its prompts you at one stage about the account info under
which the RS will run and then it stores all the info encrypted in the
RSReportserver.config file. But in later if you decide to change the local
user or aspnet user account the RS fails to decrypt and hence you get this
error. OK the workaround is you will have to run the the rsconfig and
rskeymgmt utilities to get it working. see the books online for more info
about how to run these scripts and the params that need to be passed.
My suggestion is simply reinstall the RS!!
Hope it helps!!
"simmi" wrote:
> Hi i succeeded in just getting of ReportManager,ReportServer browsers.
> Now problem r
> *I am not working with RS Programming .I am working all by designing only*
> 1)After i registerd name and password i can't able to logon into
> reportmanager.
> I am getting error like
> " The request failed with HTTP status 401: Access Denied. "
> 2)I am loging on to reportserver but i can't view the reports .
> Here error is like
> The encrypted value for configuration setting
> UnattendedExecutionAccount\UserName cannot be decrypted.
> (rsFailedToDecryptConfigInformation) Get Online Help
> Invalid length for a Base-64 char array.
> Now where i made mistake.
> if u know plz help me
>
> Thanks!
> simmi
>|||Hi ,
it is working fine when i used windows authentication.But it is not working
in formauthentication.thats what my problem.so if u know can u hel me?
Thanks!
shahab" wrote:
> This happens due to the fact that RS cannot decrypt the logonCred element in
> the RSReportServer.config. Did you by any chance made any mods to the
> Machine.config file ' To my understanding what happens is when you are
> installing the RS its prompts you at one stage about the account info under
> which the RS will run and then it stores all the info encrypted in the
> RSReportserver.config file. But in later if you decide to change the local
> user or aspnet user account the RS fails to decrypt and hence you get this
> error. OK the workaround is you will have to run the the rsconfig and
> rskeymgmt utilities to get it working. see the books online for more info
> about how to run these scripts and the params that need to be passed.
> My suggestion is simply reinstall the RS!!
> Hope it helps!!
> "simmi" wrote:
> > Hi i succeeded in just getting of ReportManager,ReportServer browsers.
> > Now problem r
> >
> > *I am not working with RS Programming .I am working all by designing only*
> >
> > 1)After i registerd name and password i can't able to logon into
> > reportmanager.
> >
> > I am getting error like
> >
> > " The request failed with HTTP status 401: Access Denied. "
> >
> > 2)I am loging on to reportserver but i can't view the reports .
> >
> > Here error is like
> >
> > The encrypted value for configuration setting
> > UnattendedExecutionAccount\UserName cannot be decrypted.
> > (rsFailedToDecryptConfigInformation) Get Online Help
> > Invalid length for a Base-64 char array.
> >
> > Now where i made mistake.
> >
> > if u know plz help me
> >
> >
> > Thanks!
> > simmi
> >|||Hi Shahab:-
I was reading your advice to Simmi which I (think) I basically have the
similar problem. The error message I got is:-
Reporting Services Error
----
The encrypted value for configuration setting Dsn cannot be decrypted.
(rsFailedToDecryptConfigInformation) Get Online Help
An internal error occurred on the report server. See the error log for more
details. (rsInternalError) Get Online Help
----
Microsoft Reporting Services
I am trying to follow your advice but I am not Entirely sure which parameter
that I need to run with the rskeymgmt.exe utility tool. Was it "Extract" ,
"Apply" , "Remove" or "Delete"? I know how to use the rsConfig.exe utility
but not sure with rskeymgmt.exe. Would you be able to help.
Thanks for reading this message.
"shahab" wrote:
> This happens due to the fact that RS cannot decrypt the logonCred element in
> the RSReportServer.config. Did you by any chance made any mods to the
> Machine.config file ' To my understanding what happens is when you are
> installing the RS its prompts you at one stage about the account info under
> which the RS will run and then it stores all the info encrypted in the
> RSReportserver.config file. But in later if you decide to change the local
> user or aspnet user account the RS fails to decrypt and hence you get this
> error. OK the workaround is you will have to run the the rsconfig and
> rskeymgmt utilities to get it working. see the books online for more info
> about how to run these scripts and the params that need to be passed.
> My suggestion is simply reinstall the RS!!
> Hope it helps!!
> "simmi" wrote:
> > Hi i succeeded in just getting of ReportManager,ReportServer browsers.
> > Now problem r
> >
> > *I am not working with RS Programming .I am working all by designing only*
> >
> > 1)After i registerd name and password i can't able to logon into
> > reportmanager.
> >
> > I am getting error like
> >
> > " The request failed with HTTP status 401: Access Denied. "
> >
> > 2)I am loging on to reportserver but i can't view the reports .
> >
> > Here error is like
> >
> > The encrypted value for configuration setting
> > UnattendedExecutionAccount\UserName cannot be decrypted.
> > (rsFailedToDecryptConfigInformation) Get Online Help
> > Invalid length for a Base-64 char array.
> >
> > Now where i made mistake.
> >
> > if u know plz help me
> >
> >
> > Thanks!
> > simmi
> >sql

Formatting Rows on one line?

I am working on a report with 4 seperate data sources. I want my
report to look like this:
Consulting Staffing
Active Pipeline $ 100,000 $ 150,000
Lifetime Value $ 200,000
Instead because Staffing is coming from a difference data source I am
getting a line break after staffing.
Consulting Staffing
$ 150,000
Active Pipeline $ 100,000
Lifetime Value $ 200,000
Is there any way I can make this work?Do you have to use individual data sources? Trying to "unite" your
data sources into a single table within RS, which is what you have
above, can be tricky. However, if you open your report in Design mode
and select "Expressions" for the field for Staffing, you can select
fields from other datasets.
Personally, I do as much manipulation of data within the data backend
(SQL 2005 for me) so that I can get a result-set from a view (or if
the report calls for a ton of grouping and/or calculations, I set up a
stored procedure) that closely approximates how my Report will appear.
HTH!
On Apr 7, 2:51 pm, BLAW <brad...@.gmail.com> wrote:
> I am working on a report with 4 seperate data sources. I want my
> report to look like this:
> Consulting Staffing
> Active Pipeline $ 100,000 $ 150,000
> Lifetime Value $ 200,000
> Instead because Staffing is coming from a difference data source I am
> getting a line break after staffing.
> Consulting Staffing
> $ 150,000
> Active Pipeline $ 100,000
> Lifetime Value $ 200,000
> Is there any way I can make this work?

Formatting number

I have a phone number as 0396526888. if I were to format this to say +61 3
9652 6888 how would I go about working on this.
Any help would be helpful. thanks heaps
Regards
RidhimaOn Sep 3, 12:26 am, Ridhima Sood
<RidhimaS...@.discussions.microsoft.com> wrote:
> I have a phone number as 0396526888. if I were to format this to say +61 3
> 9652 6888 how would I go about working on this.
> Any help would be helpful. thanks heaps
> Regards
> Ridhima
Assuming that there was a field called PhoneNumber set to 0396526888,
you would use an expression similar to the following:
= "+61 " + Left(Fields!PhoneNumber.Value, 2) + " " + Fields!
PhoneNumber.Value.Substring(3, 4) + " " + Right(Fields!
PhoneNumber.Value, 4)
Of course, you could use the 'Mid' function in place of the
'Substring' function. Also, you might want to use CStr(CInt(...))
around the 'Left' part of the expression to get rid of the zero.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You could also simply use the following format code:
+61 ## #### ####
Assuming you have a static length (10) and you will alway append +61.
Let me know if you have questions...
"EMartinez" wrote:
> On Sep 3, 12:26 am, Ridhima Sood
> <RidhimaS...@.discussions.microsoft.com> wrote:
> > I have a phone number as 0396526888. if I were to format this to say +61 3
> > 9652 6888 how would I go about working on this.
> >
> > Any help would be helpful. thanks heaps
> >
> > Regards
> > Ridhima
>
> Assuming that there was a field called PhoneNumber set to 0396526888,
> you would use an expression similar to the following:
> = "+61 " + Left(Fields!PhoneNumber.Value, 2) + " " + Fields!
> PhoneNumber.Value.Substring(3, 4) + " " + Right(Fields!
> PhoneNumber.Value, 4)
> Of course, you could use the 'Mid' function in place of the
> 'Substring' function. Also, you might want to use CStr(CInt(...))
> around the 'Left' part of the expression to get rid of the zero.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>sql

formatting for a birth date field?

Hello, I'm new to SQL Server, working for a non-profit computerizing a
lot of its data.

I imported a table of people's names, birth dates, etc. into SS2005
from Access, and the birth_date was imported as an Access date/time
field, giving it the datetime datatype in SQL.

The column values look like:

10/14/1964 12:00:00 AM

Where and how do I learn to specify that all fields like this should be
in ISO format of yyyy-mm-dd??

Do I have to create a new column and put all the dates into it??
Should I just convert the data in queries/views??
Use a constraint to format the data??
I can redo the Access table if necessary, it is only 300-some rows.

I tried BOL but it was not helpful...

The end users will likely enter mm/dd/yy or mm/dd/yyyy and it will have
to be stored properly in the database table as column/field
birth_date...

Thank you, Tomtlyczko wrote:

Quote:

Originally Posted by

Hello, I'm new to SQL Server, working for a non-profit computerizing a
lot of its data.
>
I imported a table of people's names, birth dates, etc. into SS2005
from Access, and the birth_date was imported as an Access date/time
field, giving it the datetime datatype in SQL.
>
The column values look like:
>
10/14/1964 12:00:00 AM
>
Where and how do I learn to specify that all fields like this should be
in ISO format of yyyy-mm-dd??
>
Do I have to create a new column and put all the dates into it??
Should I just convert the data in queries/views??
Use a constraint to format the data??
I can redo the Access table if necessary, it is only 300-some rows.
>
I tried BOL but it was not helpful...
>
The end users will likely enter mm/dd/yy or mm/dd/yyyy and it will have
to be stored properly in the database table as column/field
birth_date...
>
Thank you, Tom

A DATETIME column doesn't have any specific format. SQL Server cannot
control the format of dates as displayed by your client application.
For that you have to use the features of your client app or development
environment.Typically these might be based on the regional format
defined in Windows Control Panel.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||On 16 Sep 2006 06:12:07 -0700, tlyczko wrote:

Quote:

Originally Posted by

>Hello, I'm new to SQL Server, working for a non-profit computerizing a
>lot of its data.

(snip)

Quote:

Originally Posted by

>Where and how do I learn to specify that all fields like this should be
>in ISO format of yyyy-mm-dd??

(snip)

Quote:

Originally Posted by

>The end users will likely enter mm/dd/yy or mm/dd/yyyy and it will have
>to be stored properly in the database table as column/field
>birth_date...

Hi Tom,

Read Tibor Karaszi's article "The ultimate guide to the datetime
datatypes", and you'll know everything you need to know for safely using
datetimes in SQL Server databases:

http://www.karaszi.com/SQLServer/info_datetime.asp
--
Hugo Kornelis, SQL Server MVP|||David Portas wrote:

Quote:

Originally Posted by

A DATETIME column doesn't have any specific format. SQL Server cannot
control the format of dates as displayed by your client application.
For that you have to use the features of your client app or development
environment.Typically these might be based on the regional format
defined in Windows Control Panel.
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

I was looking at the table itself through SSMS 2005, I did specify
above which server I was working in, there were no error messages, it
was an import using the wizard, but I'll remember the create/insert
etc. items next time.

Thank you, Tom|||Hugo Kornelis wrote:

Quote:

Originally Posted by

Read Tibor Karaszi's article "The ultimate guide to the datetime
datatypes", and you'll know everything you need to know for safely using
datetimes in SQL Server databases:
http://www.karaszi.com/SQLServer/info_datetime.asp

Thank you, I shall...I was looking at the SQL Server table itself in
SSMS 2005, and the above post mentions how SQL stores/displays data,
maybe that's where I'm getting confused, smalldatetime will work for
me, I am only concerned with dates and *maybe* time to the nearest
minute.

Thank you, Tom|||tlyczko (tlyczko@.gmail.com) writes:

Quote:

Originally Posted by

I was looking at the table itself through SSMS 2005,

I don't think so. I think you looked at a textual representation of the
table, as presented by SSMS.

I believe that when you run a SELECT query, you always get ISO format,
but in Open Table regional settings are applied. I cannot really tell
for sure, since my regional settings agree with the ISO format.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

tlyczko (tlyczko@.gmail.com) writes:

Quote:

Originally Posted by

I was looking at the table itself through SSMS 2005,

>
I don't think so. I think you looked at a textual representation of the
table, as presented by SSMS.
I believe that when you run a SELECT query, you always get ISO format,
but in Open Table regional settings are applied. I cannot really tell
for sure, since my regional settings agree with the ISO format.

Hello, both ideas make sense, however, I now just need to learn how to
update all the fields such that the TIME part for each datum is
00:00:00.etc., I don't need to worry about the time in a birth date
field or anything similar...I'll do this as a separate post.

Thank you, Tom|||tlyczko (tlyczko@.gmail.com) writes:

Quote:

Originally Posted by

Hello, both ideas make sense, however, I now just need to learn how to
update all the fields such that the TIME part for each datum is
00:00:00.etc., I don't need to worry about the time in a birth date
field or anything similar...I'll do this as a separate post.

CONSTRAINT dateonly CHECK (convert(char(8), birthdate, 112) = birthdate)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

tlyczko (tlyczko@.gmail.com) writes:

Quote:

Originally Posted by

Hello, both ideas make sense, however, I now just need to learn how to
update all the fields such that the TIME part for each datum is
00:00:00.etc., I don't need to worry about the time in a birth date
field or anything similar...I'll do this as a separate post.

>
CONSTRAINT dateonly CHECK (convert(char(8), birthdate, 112) = birthdate)

Hello Erland,
Thank you for taking time to reply...you'll get a lot of stars from
me!! :) :)
Now I know what to read about to begin understanding your statement
above, I'll also add that constraint and check to the field itself (and
other fields too).
Thank you, Tom|||Hi Erland,

I did a quick benchmarking. I always knew that DATEDIFF approach should
be faster than converting to CHAR, but I had no idea it is that faster:

--CONSTRAINT dateonly CHECK (convert(char(8), birthdate, 112) =
birthdate)

DECLARE @.d1 DATETIME, @.d2 DATETIME, @.i INT, @.cnt INT
DECLARE @.d TABLE(ddd DATETIME)
SET NOCOUNT ON

SET @.i = 0
WHILE @.i<100000 BEGIN
INSERT @.d VALUES('20060101')
SET @.i = @.i + 1
END
SET @.d1 = GETDATE()
SET @.i = (SELECT COUNT(*) FROM @.d WHERE (convert(char(8), ddd, 112) =
ddd))
SET @.d2 = GETDATE()
SELECT DATEDIFF(ms, @.d1, @.d2), 'char'

SET @.d1 = GETDATE()
SET @.i = (SELECT COUNT(*) FROM @.d WHERE
(dateadd(d,datediff(d,'1990-01-01',ddd),'1990-01-01') = ddd))
SET @.d2 = GETDATE()
SELECT DATEDIFF(ms, @.d1, @.d2), 'datediff'

-----------------

---- --
346 char

---- ---
46 datediff|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:

Quote:

Originally Posted by

I did a quick benchmarking. I always knew that DATEDIFF approach should
be faster than converting to CHAR, but I had no idea it is that faster:

Yeah, I know. Other people has been suggesting that as well. I just keep
looking it and saying to myself "what on earth does that do?".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Wed, 20 Sep 2006 07:00:14 +0000 (UTC), Erland Sommarskog wrote:

Quote:

Originally Posted by

>I just keep
>looking it and saying to myself "what on earth does that do?".

Hi Erland,

It's actually quite simple. It counts the number of days between a
randomly chosen pivot date and the input date, then counts that number
of days from the pivot date to arrive back at the input date.

A: "Hey, do you know how many days have passed since Jan 1st?"
B: "That would be 263."
A: "Okay. Next question: what date is 263 days after Jan 1st?"
B: "Hey, stupid, that would be today, of course. Sept 21. Couldn't you
just have asked what day it is instead of these silly calculations?"
A: "Could have, but knowing you, you would have told me the time as
well. I wanted just the date."

--
Hugo Kornelis, SQL Server MVP

Formatting a string from the code

I have a textbox that displays the amout of free disk space in megabytes.
I'm working on converting it to gigabytes and if it's less that one gig, I
want to display the string "Critical" bold and in red. Can this be done?
Thanks.This can certainly be done. Where do you want to display the text
'Critical'? If you have another textbox you would use an expression to
determine the value of that checkbox. for example if you were getting the
value from a dataset you would have a similar code to the following in the
expression editor:
=IIf(Fields!Number_Of_Megs < 1024,"","Critical")
Hope this helps
"chalexan" wrote:
> I have a textbox that displays the amout of free disk space in megabytes.
> I'm working on converting it to gigabytes and if it's less that one gig, I
> want to display the string "Critical" bold and in red. Can this be done?
> Thanks.|||Bret or anyone else,
Thanks for the reply. Any suggestions on changing the font's color to red
if it's critical and making it bold?
--Chad|||You will have to do this from the report designer. You can select the
textbox that the text is in and go to the textbox properties. (right click
and properties) From here you can select the format for this text box... A
nice feature is using the Expression Editor to select the font color. This
will allow you to use an IIF() statement to have the font red or black. You
can allow do this with the font weight.
Example of formatting font color in expression editor: (you would put this
in the format expression for the font-color in the designer)
=IIf(Fields!Number_Of_Megs < 1024,"black","red")
"chalexan" wrote:
> Bret or anyone else,
> Thanks for the reply. Any suggestions on changing the font's color to red
> if it's critical and making it bold?
> --Chad|||Bret,
Wow! I thought that the "IIf(" was a typo. That functionality can be very
useful. Thank you so much for explaining that to me.
--Chad

Format on text box not working as expected

I have a table in a report that is displaying some call duration data. I
have a field that averages the results and displays them. My issue is the
result is often a number with many decimal places (example 15.888883) and I
would like the field to display a whole number (example 15).
In report designer I right click on the cell in the table I wish to alter
and choose properties. In the Textbox Properties window under Format I
select Number and then 1234 (custom code D0) and click OK. I then preview
the report and the number still shows a bunch of decimal places.
When I go back to check the properties of the cell the Format is often reset
back to 'Default' as if it didn't save my select. I've tried using the
Standard and Custom radio buttons under Format but nothing I do seems to
change the display of the number to a nondecimal type. I'm sure I can run a
function that rounds the number but I'd much rather do it in the display as
it seems you should be able to do.
Any help would be appreciated!
Peace,
DanDan,
I'm not aware of this as a bug but if it is try setting it in the
properties window (not the same as right-click|properties) if you can't
see it, press F4. You will see a 'Format' property, set that to d0 or
alternatively try n0 (puts a comma in as a thousands seperator).
Chris
Dan Christjohn wrote:
> I have a table in a report that is displaying some call duration
> data. I have a field that averages the results and displays them. My
> issue is the result is often a number with many decimal places
> (example 15.888883) and I would like the field to display a whole
> number (example 15).
> In report designer I right click on the cell in the table I wish to
> alter and choose properties. In the Textbox Properties window under
> Format I select Number and then 1234 (custom code D0) and click OK. I
> then preview the report and the number still shows a bunch of decimal
> places.
> When I go back to check the properties of the cell the Format is
> often reset back to 'Default' as if it didn't save my select. I've
> tried using the Standard and Custom radio buttons under Format but
> nothing I do seems to change the display of the number to a
> nondecimal type. I'm sure I can run a function that rounds the number
> but I'd much rather do it in the display as it seems you should be
> able to do.
> Any help would be appreciated!
> Peace,
> Dan|||Chris,
Thank you for your reply. d0 did not work and that was a value for format
that I had already tried. n0 however worked great and change the display
from 8.333333336 to 8 exactly the way I want it. Weird how that's not one of
the choices when you rightclick/format and it shows the standard formatting.
Oh well it works, thanks much!
Peace,
Dan
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:ez03hkRiFHA.1204@.TK2MSFTNGP12.phx.gbl...
> Dan,
> I'm not aware of this as a bug but if it is try setting it in the
> properties window (not the same as right-click|properties) if you can't
> see it, press F4. You will see a 'Format' property, set that to d0 or
> alternatively try n0 (puts a comma in as a thousands seperator).
> Chris
>
> Dan Christjohn wrote:
>> I have a table in a report that is displaying some call duration
>> data. I have a field that averages the results and displays them. My
>> issue is the result is often a number with many decimal places
>> (example 15.888883) and I would like the field to display a whole
>> number (example 15).
>> In report designer I right click on the cell in the table I wish to
>> alter and choose properties. In the Textbox Properties window under
>> Format I select Number and then 1234 (custom code D0) and click OK. I
>> then preview the report and the number still shows a bunch of decimal
>> places.
>> When I go back to check the properties of the cell the Format is
>> often reset back to 'Default' as if it didn't save my select. I've
>> tried using the Standard and Custom radio buttons under Format but
>> nothing I do seems to change the display of the number to a
>> nondecimal type. I'm sure I can run a function that rounds the number
>> but I'd much rather do it in the display as it seems you should be
>> able to do.
>> Any help would be appreciated!
>> Peace,
>> Dan
>|||Hi,
I just had the exact same problem. Choosing D0 does not work, but N0
does. This behaviour is weird and should be fixed because N0 puts a
comma in there.
e.g.
source = 2793.333333
D0 = 2793.333333
N0 = 2,793
When what I really want is 2793!! I think this is a bug. Internally,
the casting from float (with decimal) places is not being converted to
integeter properly.|||I think n with no zero will remove the comma but I'd have to test it to be
sure.
"Roy Assaly" <royassaly@.gmail.com> wrote in message
news:1121885650.591601.179260@.g43g2000cwa.googlegroups.com...
> Hi,
> I just had the exact same problem. Choosing D0 does not work, but N0
> does. This behaviour is weird and should be fixed because N0 puts a
> comma in there.
> e.g.
> source = 2793.333333
> D0 = 2793.333333
> N0 = 2,793
> When what I really want is 2793!! I think this is a bug. Internally,
> the casting from float (with decimal) places is not being converted to
> integeter properly.
>|||Hi Dan,
Just tested it:
source = 2793.333333
n = 2793.33
N = 2793.33
No difference. I still believe that D0 is the problem and that it's a
bug.|||'n' on it's own will always default to 2 decimal places.
I think the 'D' issue is because d or D is also a date format code it
may be getting confused!?!
You may want to consider custom format codes.
#0 or ## will give you 2793 with no commas. Using this you can specify
different formatting for positive, negative and zero numbers.
i.e. a format code of #,#;(#,#);"zero" would produce
2793 = 2,793
-2793 = (2,793)
0 = zero
Look up custom format codes in msdn vb help.
Chris
Roy Assaly wrote:
> Hi Dan,
> Just tested it:
> source = 2793.333333
> n = 2793.33
> N = 2793.33
> No difference. I still believe that D0 is the problem and that it's a
> bug.

Format number

I am doing some simple arithmatic for a query that I am working on. What I have below describes the arithmatic that is taking place withing a portion of the query:

select(o.sales_val/o.qty_sales) as 'Unit_Price', (o.acctng_cost_val/o.qty_sales) as 'Unit_Cost'
from opcsahf as o
go

The division is correct. My only issue is that the results for 'Unit Price' or 'Unit Cost' may be formatted like example: 4.25000000.

How can I have my nubers show up with only to 2 decimal places instead of all the zeros at teh end?It really is a presentation layer issue..

What are the datatypes of the columns

Did you just try CONVERT(decimal(15,2),....|||Brett,

I went ahead and tryed the convert(decimal(15,2) and everything worked fine. This was the results for the statement:

select convert(decimal (15,2),o.sales_val/ convert(decimal (15,2),o.qty_sales)) as 'Unit_Price', convert(decimal (15,2),o.acctng_cost_val/ convert(decimal (15,2),o.qty_sales)) as 'Unit_Cost'
from opcsahf as o
go

Thanks alot Brett. It did the trick.

Originally posted by Brett Kaiser
It really is a presentation layer issue..

What are the datatypes of the columns

Did you just try CONVERT(decimal(15,2),....

FORMAT ISSUE... what to do?

Hey,
I have a little format issue with Excel 2003:
I am working in reporting services - creating a report. The SQL store
procedure returns a date with the following format:
MM/DD/YYYY HH:MM PM/AM
In my report on that perticular text field I have the following format:
dd/mm/yyyy HH:MM:SS
Because I want it in military time.
It displays fine in the report but when I open it in Excel it brings up
the following ERROR:
"File Error. Some number formats may have been lost"
And it shows the date columns the following way:
"38692.46597"
The document is NOT huge and does't contain more than 2000 rows.
Any ideas on how to solve this?Alright solved this:
Changed the format
"dd/mm/yyyy HH:MM:SS"
to
"dd/mm/yyyy HH:MM:ss"
seems that did it ... weird
regards,
Sorcerdon

Format chart output

I need to display a bargraph with the top 15 errors for a production line. I
have everything working the way I like it except for the times when ther are
not 15 errors then the bargraph automatically resizes. Then it looks
terrible. Especially when there is one one item returned - then you get 1
HUGE bar that goes acress the graph.
My question is - is there a way that I can always have 15 place holders for
the bargraph? so that no matter how many values I get returned the bargraphs
will always be the same size? (except for the number of bars that are
showing).
I tried setting the min and max in the x-axis and y-axis to 0 and 15 - but
if there are less than 15 it still resizes the bars.
Thanks in advance.Hi Mike,
is that your issue solved? i also facing this same issue, when there is one
data, it becomes a HUGE columns. Or let say the chart size is best to view
for 15 placeholders but the data is >15 placeholders, all the columns will
squeeze together.
i set the category axis min/max value, also no help on this issue.
any one can help? thanks.
"Mike" wrote:
> I need to display a bargraph with the top 15 errors for a production line. I
> have everything working the way I like it except for the times when ther are
> not 15 errors then the bargraph automatically resizes. Then it looks
> terrible. Especially when there is one one item returned - then you get 1
> HUGE bar that goes acress the graph.
> My question is - is there a way that I can always have 15 place holders for
> the bargraph? so that no matter how many values I get returned the bargraphs
> will always be the same size? (except for the number of bars that are
> showing).
> I tried setting the min and max in the x-axis and y-axis to 0 and 15 - but
> if there are less than 15 it still resizes the bars.
> Thanks in advance.

Format a string

Hi all,
My company haa a big detabase and i am working on a marketing and sale repor
t.
i had lot of junk data like:
D-CAB PZTN4-3553-01 TOYOTA,Plate Hook A,7.0mm,(SET)
TOYOTA LINER D-CAB (PZO53-0K003), Plate, Robot Hook A, 7.0 MM.
i want to take out only ( D-CAB) but i coudnt make it from the string.
Can u all help me.
thanx
waiting for solution
from
SufianIf you need the first word from every row, you can use something like
this:
SELECT ProductName, LEFT(ProductName,
ISNULL(NULLIF(CHARINDEX(' ',ProductName),0),
LEN(ProductName))) FROM Northwind..Products
Razvan|||Dear thax for ur suggestion/.
but please read the questation i had asked to write a query in which where
ever the D-CAB is there it return me that not only the first.
if can be done in many ways.
but i need what i asked so please if u know then pls.
thanx
--
waiting for solution
from
Sufian
"Razvan Socol" wrote:

> If you need the first word from every row, you can use something like
> this:
> SELECT ProductName, LEFT(ProductName,
> ISNULL(NULLIF(CHARINDEX(' ',ProductName),0),
> LEN(ProductName))) FROM Northwind..Products
> Razvan
>|||Is this what you need:
SELECT CASE
WHEN ProductName LIKE '%D-CAB%' THEN 'D-CAB'
WHEN ProductName LIKE '%Something else%' THEN 'Something else'
-- and so on...
END
FROM YourTable
If it's anything like that, I'd recommend that you create a table with
the keywords and use it in an UPDATE query to fill a new column in the
main table.
Razvan|||The following will update a field named CarName and remove D-CAB.
update
MyTable
set
CarName = replace(CarName,'D-CAB','')
"Mohd Sufian" <sufian@.aeroflex.co.th> wrote in message
news:609D681C-7EDE-4E87-80D5-204E437AB453@.microsoft.com...
> Hi all,
> My company haa a big detabase and i am working on a marketing and sale
report.
> i had lot of junk data like:
> D-CAB PZTN4-3553-01 TOYOTA,Plate Hook A,7.0mm,(SET)
> TOYOTA LINER D-CAB (PZO53-0K003), Plate, Robot Hook A, 7.0 MM.
> i want to take out only ( D-CAB) but i coudnt make it from the string.
> Can u all help me.
> thanx
>
>
> --
> waiting for solution
> from
> Sufian

format a numeric values with commas

hi Steve,
Thanks for that. it is working fine.
But for the whole numbers like 123456 it is not doing so.
it is formatting to 1,234,567.00 (with two zeroes after
decimal). i want it to be formatted as 1,234,567
how to format 1234567 to 1,234,567
thanks,
hari.

>--Original Message--
>declare @.list_price numeric(20,6)
>set @.list_price = 1221512.34
>select
> convert(varchar(100),cast(@.list_price as money),1)
>Steve Kass
>Drew University
>hari wrote:
>.
>
..
See my answer to your other question about removing zeros.
SK
hari wrote:

>hi Steve,
> Thanks for that. it is working fine.
> But for the whole numbers like 123456 it is not doing so.
> it is formatting to 1,234,567.00 (with two zeroes after
> decimal). i want it to be formatted as 1,234,567
> how to format 1234567 to 1,234,567
>thanks,
>hari.
>
>.
>
>
|||thanks Steve,
it's working fine for removing zeroes and formating it
with commas. i changed it with combining ur previous and
current answers.
rgds,
hari.
[vbcol=seagreen]
>--Original Message--
>See my answer to your other question about removing zeros.
>SK
>hari wrote:
so.[vbcol=seagreen]
there
>.
>