This is a multi-part message in MIME format.
--=_NextPart_000_000A_01C75C1E.0326B0B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Perhaps this is just behavior in the dev environment, but if I provide = default values for all of my paramters, the report runs automatically = when I switch to preview.
Is there a way to provide defaults, but still require the user to click = view before the report runs?
Thanks.
--=_NextPart_000_000A_01C75C1E.0326B0B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Perhaps this is just behavior in the = dev environment, but if I provide default values for all of my paramters, = the report runs automatically when I switch to preview.
Is there a way to provide defaults, but = still require the user to click view before the report runs?
Thanks.
--=_NextPart_000_000A_01C75C1E.0326B0B0--On Mar 1, 5:23 pm, <rlrc...@.newsgroups.nospam> wrote:
> Perhaps this is just behavior in the dev environment, but if I provide default values for all of my paramters, the report runs automatically when I switch to preview.
> Is there a way to provide defaults, but still require the user to click view before the report runs?
> Thanks.
As far as I know, there is not a way to keep the report from
automatically running without removing at least one of the parameter's
default values.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||You can probably create a parameter and hide it...and then not provide it
default value.
-
HelpSeeker
"rlrcstr@.newsgroups.nospam" wrote:
> Perhaps this is just behavior in the dev environment, but if I provide default values for all of my paramters, the report runs automatically when I switch to preview.
> Is there a way to provide defaults, but still require the user to click view before the report runs?
> Thanks|||Hi rlrcstr,
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.
Showing posts with label format. Show all posts
Showing posts with label format. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
How do I hide zeros w/o a conditional stmt
I need a way to format fields without having to write a conditional statement
based on each textbox's value. I'm dealing with hundreds of fields.
Something like a HideZeros setting on the field. Any help would be
appreciatedWhat I do is change the Format property of each textbox to a custom value:
#,##0.0;(#,##0.0);-
the 1st group is how to display positive values;
the 2nd is for negatives;
and the 3rd is for zeroes (I am just displaying a dash here).
good luck,
Greg
"Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
message news:64AAF2A7-F92B-4E91-8F96-3A9AAB2088FC@.microsoft.com...
>I need a way to format fields without having to write a conditional
>statement
> based on each textbox's value. I'm dealing with hundreds of fields.
> Something like a HideZeros setting on the field. Any help would be
> appreciated|||Thanks!
Worked great. Any idea on how to use that with a Percentage field.
"Blake Gremillion" wrote:
> I need a way to format fields without having to write a conditional statement
> based on each textbox's value. I'm dealing with hundreds of fields.
> Something like a HideZeros setting on the field. Any help would be
> appreciated|||I would try custom again and this time use:
0.0%;(0.0%);-
I pesonally use custom with percentages anyways and use
P1
to get just one decimal place. I think the standard "percentage" option,
gives you two. Of course neither hides zeroes. ;(
Greg
"Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
message news:0B62BEDB-123A-4B5A-B363-ACAF9759425C@.microsoft.com...
> Thanks!
> Worked great. Any idea on how to use that with a Percentage field.
>
>
> "Blake Gremillion" wrote:
>> I need a way to format fields without having to write a conditional
>> statement
>> based on each textbox's value. I'm dealing with hundreds of fields.
>> Something like a HideZeros setting on the field. Any help would be
>> appreciated|||Yeah, I think that's what I'll have to do instead of using the Px format.
Thanks Again!
"Greg Burns" wrote:
> I would try custom again and this time use:
> 0.0%;(0.0%);-
> I pesonally use custom with percentages anyways and use
> P1
> to get just one decimal place. I think the standard "percentage" option,
> gives you two. Of course neither hides zeroes. ;(
> Greg
>
> "Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
> message news:0B62BEDB-123A-4B5A-B363-ACAF9759425C@.microsoft.com...
> > Thanks!
> >
> > Worked great. Any idea on how to use that with a Percentage field.
> >
> >
> >
> >
> > "Blake Gremillion" wrote:
> >
> >> I need a way to format fields without having to write a conditional
> >> statement
> >> based on each textbox's value. I'm dealing with hundreds of fields.
> >> Something like a HideZeros setting on the field. Any help would be
> >> appreciated
>
>
based on each textbox's value. I'm dealing with hundreds of fields.
Something like a HideZeros setting on the field. Any help would be
appreciatedWhat I do is change the Format property of each textbox to a custom value:
#,##0.0;(#,##0.0);-
the 1st group is how to display positive values;
the 2nd is for negatives;
and the 3rd is for zeroes (I am just displaying a dash here).
good luck,
Greg
"Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
message news:64AAF2A7-F92B-4E91-8F96-3A9AAB2088FC@.microsoft.com...
>I need a way to format fields without having to write a conditional
>statement
> based on each textbox's value. I'm dealing with hundreds of fields.
> Something like a HideZeros setting on the field. Any help would be
> appreciated|||Thanks!
Worked great. Any idea on how to use that with a Percentage field.
"Blake Gremillion" wrote:
> I need a way to format fields without having to write a conditional statement
> based on each textbox's value. I'm dealing with hundreds of fields.
> Something like a HideZeros setting on the field. Any help would be
> appreciated|||I would try custom again and this time use:
0.0%;(0.0%);-
I pesonally use custom with percentages anyways and use
P1
to get just one decimal place. I think the standard "percentage" option,
gives you two. Of course neither hides zeroes. ;(
Greg
"Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
message news:0B62BEDB-123A-4B5A-B363-ACAF9759425C@.microsoft.com...
> Thanks!
> Worked great. Any idea on how to use that with a Percentage field.
>
>
> "Blake Gremillion" wrote:
>> I need a way to format fields without having to write a conditional
>> statement
>> based on each textbox's value. I'm dealing with hundreds of fields.
>> Something like a HideZeros setting on the field. Any help would be
>> appreciated|||Yeah, I think that's what I'll have to do instead of using the Px format.
Thanks Again!
"Greg Burns" wrote:
> I would try custom again and this time use:
> 0.0%;(0.0%);-
> I pesonally use custom with percentages anyways and use
> P1
> to get just one decimal place. I think the standard "percentage" option,
> gives you two. Of course neither hides zeroes. ;(
> Greg
>
> "Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
> message news:0B62BEDB-123A-4B5A-B363-ACAF9759425C@.microsoft.com...
> > Thanks!
> >
> > Worked great. Any idea on how to use that with a Percentage field.
> >
> >
> >
> >
> > "Blake Gremillion" wrote:
> >
> >> I need a way to format fields without having to write a conditional
> >> statement
> >> based on each textbox's value. I'm dealing with hundreds of fields.
> >> Something like a HideZeros setting on the field. Any help would be
> >> appreciated
>
>
How do i get the report in PDF format?
Hi all,
I need to get a report in PDF format.How to get that.I did the coding but am not getting.The following code i tried am not getting.what is the wrong in this coding.
WebMedRS.ReportingService rs = new WebMedRS.ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
parametersIn[0] = new ParameterValue();
parametersIn[0].Name = "claimid";
parametersIn[0].Value = Request.QueryString[0].ToString();
ParameterValue[] parametersOut = null;
byte[] result = null;
string reportPath = "/CMS/CmsFrom1";
string format = "Image";
string historyID = null;
string encoding;
string mimeType;
string extension;
Warning[] warnings = null;
string[] streamIDs = null;
result = rs.Render(reportPath, format, null, null, parametersIn, null, null, out encoding, out mimeType, out parametersOut, out warnings, out streamIDs);
Response.ClearContent();
Response.AppendHeader("content-length", result.Length.ToString());
Response.ContentType = "application/pdf";
Response.BinaryWrite(result);
Response.Flush();
Response.Close();
Thanks In Advance,
Yours,
Senthil
You will have to change the format:
string format = "PDF";
Jens K. Suessmeyer.
http://www.sqlserver2005.de
Monday, March 12, 2012
how do i get date into dd-mm-yy format?
i have the following in an insert stored proc
CONVERT(CHAR(8),GETDATE(),10)
but this inserts 03-06-06.
how do i get it to 06-03-06?
Cheers,
Craig
my sp is as follows:-
IF (@.blnConsent = 1 AND @.dteActualConsentDate <> NULL) --This is PC insert for YES consent where a ConsentDate is supplied
BEGIN
INSERT INTO tblStudyServices
(SurveyID, PatientID, FourRegularDrugs, FourRegularDrugsNo, HRD,
ReadmissSixMonths, Consent, ConsentDate, CreationDate)
VALUES
(@.intSurveyID, @.intPatientID, @.blnFourRegularDrugs, @.intFourRegularDrugsNo, @.blnHRD,
@.blnReadmissSixMonths, @.blnConsent, @.dteActualConsentDate, CONVERT(CHAR(8),GETDATE(),10))
END
@.dteActualConsentDate is passed in as 14-03-06
if i then look in the table it has
CosentDate = 14-03-06
CreationDate = 03-06-06
i have tried SET_DATEFORMAT dmy
but still no luck
|||Datetime values are not stored in any special format. There are special allowed format and rules for inserting datetime. For output, it is the client application that formats the value to something that is human-readable. See http://www.karaszi.com/SQLServer/info_datetime.asp for more information. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/> CONVERT(CHAR(8),GETDATE(),10) >
> but this inserts 03-06-06. >
> how do i get it to 06-03-06? >
> Cheers,
> Craig >
>|||Did you see the reply I posted? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/
> my sp is as follows:- >
> IF (@.blnConsent = 1 AND @.dteActualConsentDate <> NULL) --This is PC
> insert for YES consent where a ConsentDate is supplied
> BEGIN
> INSERT INTO tblStudyServices
> (SurveyID, PatientID, FourRegularDrugs, FourRegularDrugsNo, HRD,
> ReadmissSixMonths, Consent, ConsentDate, CreationDate)
> VALUES
> (@.intSurveyID, @.intPatientID, @.blnFourRegularDrugs,
> @.intFourRegularDrugsNo, @.blnHRD,
> @.blnReadmissSixMonths, @.blnConsent, @.dteActualConsentDate,
> CONVERT(CHAR(8),GETDATE(),10))
> END >
> @.dteActualConsentDate is passed in as 14-03-06 >
> if i then look in the table it has >
> CosentDate = 14-03-06
> CreationDate = 03-06-06 >
> i have tried SET_DATEFORMAT dmy >
> but still no luck >
>
How do I format zeros (0) to display as dashes (-)?
I am using the current formula and it works. #,###,;(#,###,) How do change
this formula to display the zeros as dashes?
Thanks in AdvanceUse the below expression
=Iif(Fields!Measures_TxnCount.Value=0,'-',
Fields!Measures_TxnCount.Value)
I hope this should solve u r problem
OriginalStealth wrote:
> I am using the current formula and it works. #,###,;(#,###,) How do
change
> this formula to display the zeros as dashes?
>
> Thanks in Advance
this formula to display the zeros as dashes?
Thanks in AdvanceUse the below expression
=Iif(Fields!Measures_TxnCount.Value=0,'-',
Fields!Measures_TxnCount.Value)
I hope this should solve u r problem
OriginalStealth wrote:
> I am using the current formula and it works. #,###,;(#,###,) How do
change
> this formula to display the zeros as dashes?
>
> Thanks in Advance
How do I format mailing address fields for display in reports
I am very new to development with SQL Server but I have lots of
experience with Access. I am producing a report in VB.Net using
CrystalReports that will display a company's address differently
depending on the contents of the data. For example, if the second
address line is NULL then I don't want it to display at all or if the
customer is not from the US then I want the country field displayed. My
database design is your typical address line 1, line 2, city, state,
postal code, country with lookup tables providing the full text for
state/provinces and countries. There's nothing fancy with the data.
My learning curve is both with Crystal and with SQL Server. I think
I'm better off trying to write a function in Transact SQL that I
could call from the query that VB.Net will use to create the XML that
will drive the report. I simply want to send a completely formatted
string to VB.Net, including commas, spaces and carriage returns and
line feeds. Please let me know if I'm nuts. I'm basing this
decision on the fact that Crystal is not the easiest tool to deal with
and that I probably have a better chance doing it on the server end.
I also have to believe that I'm not the first one who's ever wanted
to do this and am hoping that this code is out there somewhere for me
to legally pilfer and modify. I'm posting because my searches have so
far been unsuccessful.
Now, if anyone in Redmond is listening, those VB developers need to
walk down the hall and talk to the Access developers! I REALLY miss the
Access report writer and query builder. Their functionality and
productivity are superb. I find myself often linking Access to my SQL
Server databases, going into Access to build my query and then cutting
and pasting the SQL into Enterprise Manager. Now, if I could just write
my user-defined functions in VB or C then the world would then be a
much prettier and productive place.I kind of understand where you are coming from here, but you are probably
best served by doing this kind of formatting within crystal rather than in
T-SQL. This really is a presentation issue rather than a data issue.
"Foofs" <marta@.mindcrafted.com> wrote in message
news:1132331118.184758.42090@.g43g2000cwa.googlegroups.com...
>I am very new to development with SQL Server but I have lots of
> experience with Access. I am producing a report in VB.Net using
> CrystalReports that will display a company's address differently
> depending on the contents of the data. For example, if the second
> address line is NULL then I don't want it to display at all or if the
> customer is not from the US then I want the country field displayed. My
> database design is your typical address line 1, line 2, city, state,
> postal code, country with lookup tables providing the full text for
> state/provinces and countries. There's nothing fancy with the data.
> My learning curve is both with Crystal and with SQL Server. I think
> I'm better off trying to write a function in Transact SQL that I
> could call from the query that VB.Net will use to create the XML that
> will drive the report. I simply want to send a completely formatted
> string to VB.Net, including commas, spaces and carriage returns and
> line feeds. Please let me know if I'm nuts. I'm basing this
> decision on the fact that Crystal is not the easiest tool to deal with
> and that I probably have a better chance doing it on the server end.
> I also have to believe that I'm not the first one who's ever wanted
> to do this and am hoping that this code is out there somewhere for me
> to legally pilfer and modify. I'm posting because my searches have so
> far been unsuccessful.
> Now, if anyone in Redmond is listening, those VB developers need to
> walk down the hall and talk to the Access developers! I REALLY miss the
> Access report writer and query builder. Their functionality and
> productivity are superb. I find myself often linking Access to my SQL
> Server databases, going into Access to build my query and then cutting
> and pasting the SQL into Enterprise Manager. Now, if I could just write
> my user-defined functions in VB or C then the world would then be a
> much prettier and productive place.
>|||There is a *crystal* related microsoft newsgroup.
"Foofs" <marta@.mindcrafted.com> wrote in message
news:1132331118.184758.42090@.g43g2000cwa.googlegroups.com...
>I am very new to development with SQL Server but I have lots of
> experience with Access. I am producing a report in VB.Net using
> CrystalReports that will display a company's address differently
> depending on the contents of the data. For example, if the second
> address line is NULL then I don't want it to display at all or if the
> customer is not from the US then I want the country field displayed. My
> database design is your typical address line 1, line 2, city, state,
> postal code, country with lookup tables providing the full text for
> state/provinces and countries. There's nothing fancy with the data.
> My learning curve is both with Crystal and with SQL Server. I think
> I'm better off trying to write a function in Transact SQL that I
> could call from the query that VB.Net will use to create the XML that
> will drive the report. I simply want to send a completely formatted
> string to VB.Net, including commas, spaces and carriage returns and
> line feeds. Please let me know if I'm nuts. I'm basing this
> decision on the fact that Crystal is not the easiest tool to deal with
> and that I probably have a better chance doing it on the server end.
> I also have to believe that I'm not the first one who's ever wanted
> to do this and am hoping that this code is out there somewhere for me
> to legally pilfer and modify. I'm posting because my searches have so
> far been unsuccessful.
> Now, if anyone in Redmond is listening, those VB developers need to
> walk down the hall and talk to the Access developers! I REALLY miss the
> Access report writer and query builder. Their functionality and
> productivity are superb. I find myself often linking Access to my SQL
> Server databases, going into Access to build my query and then cutting
> and pasting the SQL into Enterprise Manager. Now, if I could just write
> my user-defined functions in VB or C then the world would then be a
> much prettier and productive place.
>
experience with Access. I am producing a report in VB.Net using
CrystalReports that will display a company's address differently
depending on the contents of the data. For example, if the second
address line is NULL then I don't want it to display at all or if the
customer is not from the US then I want the country field displayed. My
database design is your typical address line 1, line 2, city, state,
postal code, country with lookup tables providing the full text for
state/provinces and countries. There's nothing fancy with the data.
My learning curve is both with Crystal and with SQL Server. I think
I'm better off trying to write a function in Transact SQL that I
could call from the query that VB.Net will use to create the XML that
will drive the report. I simply want to send a completely formatted
string to VB.Net, including commas, spaces and carriage returns and
line feeds. Please let me know if I'm nuts. I'm basing this
decision on the fact that Crystal is not the easiest tool to deal with
and that I probably have a better chance doing it on the server end.
I also have to believe that I'm not the first one who's ever wanted
to do this and am hoping that this code is out there somewhere for me
to legally pilfer and modify. I'm posting because my searches have so
far been unsuccessful.
Now, if anyone in Redmond is listening, those VB developers need to
walk down the hall and talk to the Access developers! I REALLY miss the
Access report writer and query builder. Their functionality and
productivity are superb. I find myself often linking Access to my SQL
Server databases, going into Access to build my query and then cutting
and pasting the SQL into Enterprise Manager. Now, if I could just write
my user-defined functions in VB or C then the world would then be a
much prettier and productive place.I kind of understand where you are coming from here, but you are probably
best served by doing this kind of formatting within crystal rather than in
T-SQL. This really is a presentation issue rather than a data issue.
"Foofs" <marta@.mindcrafted.com> wrote in message
news:1132331118.184758.42090@.g43g2000cwa.googlegroups.com...
>I am very new to development with SQL Server but I have lots of
> experience with Access. I am producing a report in VB.Net using
> CrystalReports that will display a company's address differently
> depending on the contents of the data. For example, if the second
> address line is NULL then I don't want it to display at all or if the
> customer is not from the US then I want the country field displayed. My
> database design is your typical address line 1, line 2, city, state,
> postal code, country with lookup tables providing the full text for
> state/provinces and countries. There's nothing fancy with the data.
> My learning curve is both with Crystal and with SQL Server. I think
> I'm better off trying to write a function in Transact SQL that I
> could call from the query that VB.Net will use to create the XML that
> will drive the report. I simply want to send a completely formatted
> string to VB.Net, including commas, spaces and carriage returns and
> line feeds. Please let me know if I'm nuts. I'm basing this
> decision on the fact that Crystal is not the easiest tool to deal with
> and that I probably have a better chance doing it on the server end.
> I also have to believe that I'm not the first one who's ever wanted
> to do this and am hoping that this code is out there somewhere for me
> to legally pilfer and modify. I'm posting because my searches have so
> far been unsuccessful.
> Now, if anyone in Redmond is listening, those VB developers need to
> walk down the hall and talk to the Access developers! I REALLY miss the
> Access report writer and query builder. Their functionality and
> productivity are superb. I find myself often linking Access to my SQL
> Server databases, going into Access to build my query and then cutting
> and pasting the SQL into Enterprise Manager. Now, if I could just write
> my user-defined functions in VB or C then the world would then be a
> much prettier and productive place.
>|||There is a *crystal* related microsoft newsgroup.
"Foofs" <marta@.mindcrafted.com> wrote in message
news:1132331118.184758.42090@.g43g2000cwa.googlegroups.com...
>I am very new to development with SQL Server but I have lots of
> experience with Access. I am producing a report in VB.Net using
> CrystalReports that will display a company's address differently
> depending on the contents of the data. For example, if the second
> address line is NULL then I don't want it to display at all or if the
> customer is not from the US then I want the country field displayed. My
> database design is your typical address line 1, line 2, city, state,
> postal code, country with lookup tables providing the full text for
> state/provinces and countries. There's nothing fancy with the data.
> My learning curve is both with Crystal and with SQL Server. I think
> I'm better off trying to write a function in Transact SQL that I
> could call from the query that VB.Net will use to create the XML that
> will drive the report. I simply want to send a completely formatted
> string to VB.Net, including commas, spaces and carriage returns and
> line feeds. Please let me know if I'm nuts. I'm basing this
> decision on the fact that Crystal is not the easiest tool to deal with
> and that I probably have a better chance doing it on the server end.
> I also have to believe that I'm not the first one who's ever wanted
> to do this and am hoping that this code is out there somewhere for me
> to legally pilfer and modify. I'm posting because my searches have so
> far been unsuccessful.
> Now, if anyone in Redmond is listening, those VB developers need to
> walk down the hall and talk to the Access developers! I REALLY miss the
> Access report writer and query builder. Their functionality and
> productivity are superb. I find myself often linking Access to my SQL
> Server databases, going into Access to build my query and then cutting
> and pasting the SQL into Enterprise Manager. Now, if I could just write
> my user-defined functions in VB or C then the world would then be a
> much prettier and productive place.
>
How do I format data in the SQL statement
It has been a couple of years but I remember being able to format numbers,
dates and text in SQL Server statements, can't find much in online books ,
Where can I relearn this, thanks
JimThat's a little vague but I think you are after CONVERT(). See BooksOnLine
for more details.
Andrew J. Kelly SQL MVP
"Jim Warren" <jmwarren@.msu.edu> wrote in message
news:u0WUchlCFHA.392@.TK2MSFTNGP14.phx.gbl...
> It has been a couple of years but I remember being able to format numbers,
> dates and text in SQL Server statements, can't find much in online books
> ,
> Where can I relearn this, thanks
> Jim
>|||Sorry
How do I make Number into zero decimal point i.e. 2.0000000 into 2. How do I
convert dates from 2005-1-14 to Jan 1, 2005. Is there a format statement of
some kind
Thanks
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
> That's a little vague but I think you are after CONVERT(). See
> BooksOnLine for more details.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Warren" <jmwarren@.msu.edu> wrote in message
> news:u0WUchlCFHA.392@.TK2MSFTNGP14.phx.gbl...
>|||Yes. It's called CONVERT().
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_ca-co_2f3o.htm
However, CONVERT will not handle your first requirement. You will need to do
that in your client application.
PS. dates are not stored with any format. They are stored as pairs of
integers. You really need to start reading in Books Online (BOL)
Jim Warren wrote:
> Sorry
> How do I make Number into zero decimal point i.e. 2.0000000 into 2.
> How do I convert dates from 2005-1-14 to Jan 1, 2005. Is there a
> format statement of some kind
> Thanks
> Jim
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||As I stated and Bob confirmed you would want to look at CONVERT() in
BooksOnLine. You can convert the decimal into an INT and that will loos the
decimal places.
Andrew J. Kelly SQL MVP
"Jim Warren" <jmwarren@.msu.edu> wrote in message
news:OI$46h3CFHA.1836@.tk2msftngp13.phx.gbl...
> Sorry
> How do I make Number into zero decimal point i.e. 2.0000000 into 2. How do
> I convert dates from 2005-1-14 to Jan 1, 2005. Is there a format statement
> of some kind
> Thanks
> Jim
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
>
dates and text in SQL Server statements, can't find much in online books ,
Where can I relearn this, thanks
JimThat's a little vague but I think you are after CONVERT(). See BooksOnLine
for more details.
Andrew J. Kelly SQL MVP
"Jim Warren" <jmwarren@.msu.edu> wrote in message
news:u0WUchlCFHA.392@.TK2MSFTNGP14.phx.gbl...
> It has been a couple of years but I remember being able to format numbers,
> dates and text in SQL Server statements, can't find much in online books
> ,
> Where can I relearn this, thanks
> Jim
>|||Sorry
How do I make Number into zero decimal point i.e. 2.0000000 into 2. How do I
convert dates from 2005-1-14 to Jan 1, 2005. Is there a format statement of
some kind
Thanks
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
> That's a little vague but I think you are after CONVERT(). See
> BooksOnLine for more details.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Warren" <jmwarren@.msu.edu> wrote in message
> news:u0WUchlCFHA.392@.TK2MSFTNGP14.phx.gbl...
>|||Yes. It's called CONVERT().
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_ca-co_2f3o.htm
However, CONVERT will not handle your first requirement. You will need to do
that in your client application.
PS. dates are not stored with any format. They are stored as pairs of
integers. You really need to start reading in Books Online (BOL)
Jim Warren wrote:
> Sorry
> How do I make Number into zero decimal point i.e. 2.0000000 into 2.
> How do I convert dates from 2005-1-14 to Jan 1, 2005. Is there a
> format statement of some kind
> Thanks
> Jim
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||As I stated and Bob confirmed you would want to look at CONVERT() in
BooksOnLine. You can convert the decimal into an INT and that will loos the
decimal places.
Andrew J. Kelly SQL MVP
"Jim Warren" <jmwarren@.msu.edu> wrote in message
news:OI$46h3CFHA.1836@.tk2msftngp13.phx.gbl...
> Sorry
> How do I make Number into zero decimal point i.e. 2.0000000 into 2. How do
> I convert dates from 2005-1-14 to Jan 1, 2005. Is there a format statement
> of some kind
> Thanks
> Jim
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
>
How do I format an integer
How do I format an integer. Add commas.
1234565 1,234,565
TIAstevek wrote:
> How do I format an integer. Add commas.
> 1234565 1,234,565
> TIA
Hi SteveK,
Create the following function
**************** START HERE ****************
CREATE FUNCTION [dbo].[FormatNumber] (
@.number decimal(38,15),
@.decimalplaces int=0,
@.format varchar(115)='',
@.ifzero varchar(115)=''
) RETURNS varchar(256)
AS BEGIN
/*
Valid @.Format arguments (space between args is ignored)
nothing - returns the number unformatted
$ - return the number preceded by a '$' sign
% - return the number followed by a '%' sign
, - place a , every 3 zeros in the whole number portion (thousands)
c - divide the number by 100 - intended to calc percent values
i - returns integer portion only with no formatting except commas if
requested
d - returns the decimal portion only with no formatting except commas
if requested
b - returns a blank string for 0 values
( - encloses negative numbers in brackets
l - use leading zero
r[int]r - rounds number outside of the decimal context
z[int]z - zero fills to [int] width
*/
DECLARE @.fmtxt varchar(25), @.parsetxt varchar(50)
, @.parsetxtdec varchar(50)
, @.decptloc int, @.zerotext varchar(100)
, @.intpart varchar(25), @.decpart varchar(25)
, @.ERR_type varchar(15), @.roundto varchar(2)
, @.fillto varchar(50), @.fillto# varchar(2)
--A little error checking is in order
IF @.number IS NULL
RETURN '{ERR-null passed}'
ELSE IF @.decimalplaces < 0
RETURN '{ERR-decimal spec <0}'
ELSE IF @.decimalplaces >15
RETURN '{ERR-decimal spec >15}'
-- Handle zero values first
IF @.number = 0 RETURN @.ifzero
-- Now 'C'alculate the percentage if requested using the '%c' arg.
IF CHARINDEX('%c',@.FORMAT) > 0 SET @.number = @.number * 100
-- Do rounding outside if applicable
IF CHARINDEX('r',@.FORMAT) > 0 BEGIN
SET @.roundto = SUBSTRING(@.FORMAT,CHARINDEX('r', @.FORMAT)+1, 115)
SET @.roundto = LEFT(@.roundto,CHARINDEX('r',@.roundto)-1)
SET @.number = round(@.number,cast(@.roundto as integer))
END
-- Get the parsetext variable
IF CHARINDEX(',',@.FORMAT) > 0
SET @.parsetxt = CONVERT(varchar(100),CAST(@.number as money),1)
ELSE
SET @.parsetxt = CONVERT(varchar(100), @.number)
-- Grab some basic stuff
SET @.decptloc = ISNULL(CHARINDEX('.',@.parsetxt),0)
IF @.decptloc = 0
RETURN @.parsetxt
ELSE
SET @.intpart = SUBSTRING(@.parsetxt,1,@.decptloc-1)
-- Handle leading zeros
IF CHARINDEX('l',@.FORMAT) = 0 AND @.intpart = '0' SET @.intpart = ''
-- Now build the decimal portion of the result
SET @.parsetxt = CONVERT(varchar(100),ROUND(@.number,@.decimalplaces) ,2)
SET @.decptloc = ISNULL(CHARINDEX('.',@.parsetxt),0)
IF @.decimalplaces = 0
SET @.decpart = ''
ELSE
SET @.decpart = LEFT(SUBSTRING(@.parsetxt
+ REPLICATE('0',@.decimalplaces)
,@.decptloc
,@.decptloc+50)
,@.decimalplaces+1)
--ASSEMBLE THE RESULTS --
-- for just integer portion
IF CHARINDEX('i',@.FORMAT) > 0
RETURN @.intpart
-- for just decimal portion
IF CHARINDEX('d',@.FORMAT) > 0
RETURN + @.decpart
SET @.fmtxt = @.intpart + @.decpart
--SET @.fmtxt = @.intpart +'*'+ @.decpart
-- Handle brackets if requested
IF CHARINDEX('(',@.FORMAT) > 0 AND @.number < 0
SET @.fmtxt = '(' + RIGHT(@.fmtxt,LEN(@.fmtxt)-1) + ')'
-- Add the symbols
IF CHARINDEX('$',@.FORMAT) > 0
SET @.fmtxt = '$' + @.fmtxt
ELSE IF CHARINDEX('%',@.FORMAT) > 0
SET @.fmtxt = @.fmtxt + '%'
--Handle zero filling
IF CHARINDEX('z',@.FORMAT) > 0 BEGIN
SET @.fillto = SUBSTRING(@.FORMAT,CHARINDEX('z',@.FORMAT)+1,115)
SET @.fillto# = CAST(LEFT(@.fillto,CHARINDEX('z',@.fillto)-1) as INT)
SET @.fmtxt = RIGHT(REPLICATE('0',@.fillto#) + @.fmtxt,@.fillto#)
END
RETURN @.fmtxt
END
GO
**************** END HERE ****************
Now we need to grant access to it:
GRANT EXEC on dbo.FormatNumber to PUBLIC
GO
and to use it do the following:
declare @.testnum int
SET @.TestNum = 123456789
select dbo.FormatNumber (@.TestNum, '0', ',', 'zero') as [Integer]
Good luck
Michael van der Veeke
michaelvanderveeke AT borderexpress DOT com DOT au|||Formatting and presentation usually belong in the client application,
not the server. The formatting above is not correct in many European
languages, for example - by doing it on the client you can use the
correct locale information for different clients, or use formatting
masks/functions like printf or whatever your preferred development
language has.
Simon|||I agree with Simon that data formatting is best performed on in the
presentation layer rather than in SQL. That said, you can use CONVERT to
add commas and string functions to remove extraneous characters.
SELECT
REVERSE(SUBSTRING(REVERSE(CONVERT(varchar(20), CAST(1234565 AS money),
1)), 4, 20))
--
Hope this helps.
Dan Guzman
SQL Server MVP
"stevek" <someone@.somewhere.org> wrote in message
news:IVxQd.220032$w62.163948@.bgtnsc05-news.ops.worldnet.att.net...
> How do I format an integer. Add commas.
> 1234565 1,234,565
> TIA
Sunday, February 19, 2012
How do I deploy XSL file to Reports Server?
Hi:
I have developed a report that uses and XSLT file to format data found in a
CLOB field from our DB2 database. It works just great in Report Designer,
but when I deploy to the Reporting Server, it does not render the data from
the CLOB but rather an error.
The value of the text box is
= Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
I have created a C:\XSL directory on the Reports Server and placed a copy of
the ClearingHouseException.xsl file in it, but it just isn't working.
What am I doing wrong?
Thanks.
Bruce.If this works from development and not from production then you are hitting
a security problem. Could be one of two places, either the rights on the
c:\xsl directory OR (more likely) the rights for the code you are executing.
By default RS limits what code can do to only safe things, which accessing
the file system is definitely not safe. I suggest reading up on what you
would have to do if you were using your own assemblies (this isn't your own
assembly but it is the same thing). You might need to have thise be your own
assembly instead of code behind in order to get the proper security rights
but I am not sure. I feel pretty confident it is one of these two things.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:8C18EF57-E9AC-47D8-9081-B761360C5F8E@.microsoft.com...
> Hi:
> I have developed a report that uses and XSLT file to format data found in
a
> CLOB field from our DB2 database. It works just great in Report Designer,
> but when I deploy to the Reporting Server, it does not render the data
from
> the CLOB but rather an error.
> The value of the text box is
> = Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
> I have created a C:\XSL directory on the Reports Server and placed a copy
of
> the ClearingHouseException.xsl file in it, but it just isn't working.
> What am I doing wrong?
> Thanks.
> Bruce.|||Hi Bruce:
Thanks for the reply, although the "assembly" approach seems like a lot of
work to get right...
I did read in one book on RS that you could upload the XSL file to the same
folder in Report Manager and it could be accessed from there. Of course, it
didn't say how the report would call the file, ie: the normal C:\ path
wouldn't work. Is it possible to point to the file in the Report Manger
folder?
Thanks again.
Bruce.
"Bruce L-C [MVP]" wrote:
> If this works from development and not from production then you are hitting
> a security problem. Could be one of two places, either the rights on the
> c:\xsl directory OR (more likely) the rights for the code you are executing.
> By default RS limits what code can do to only safe things, which accessing
> the file system is definitely not safe. I suggest reading up on what you
> would have to do if you were using your own assemblies (this isn't your own
> assembly but it is the same thing). You might need to have thise be your own
> assembly instead of code behind in order to get the proper security rights
> but I am not sure. I feel pretty confident it is one of these two things.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
> in message news:8C18EF57-E9AC-47D8-9081-B761360C5F8E@.microsoft.com...
> > Hi:
> >
> > I have developed a report that uses and XSLT file to format data found in
> a
> > CLOB field from our DB2 database. It works just great in Report Designer,
> > but when I deploy to the Reporting Server, it does not render the data
> from
> > the CLOB but rather an error.
> >
> > The value of the text box is
> >
> > = Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
> >
> > I have created a C:\XSL directory on the Reports Server and placed a copy
> of
> > the ClearingHouseException.xsl file in it, but it just isn't working.
> >
> > What am I doing wrong?
> >
> > Thanks.
> >
> > Bruce.
>
>|||Sorry, I haven't tried to get to a file. Since I haven't had to mess with
the security stuff for assemblies I can't help too much there (or with
getting to the file). It does make sense for security reasons that they
would not by default allow access to the hard drive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:EE73655D-F3D0-4525-A03B-04DE703FB560@.microsoft.com...
> Hi Bruce:
> Thanks for the reply, although the "assembly" approach seems like a lot of
> work to get right...
> I did read in one book on RS that you could upload the XSL file to the
> same
> folder in Report Manager and it could be accessed from there. Of course,
> it
> didn't say how the report would call the file, ie: the normal C:\ path
> wouldn't work. Is it possible to point to the file in the Report Manger
> folder?
> Thanks again.
> Bruce.
> "Bruce L-C [MVP]" wrote:
>> If this works from development and not from production then you are
>> hitting
>> a security problem. Could be one of two places, either the rights on the
>> c:\xsl directory OR (more likely) the rights for the code you are
>> executing.
>> By default RS limits what code can do to only safe things, which
>> accessing
>> the file system is definitely not safe. I suggest reading up on what you
>> would have to do if you were using your own assemblies (this isn't your
>> own
>> assembly but it is the same thing). You might need to have thise be your
>> own
>> assembly instead of code behind in order to get the proper security
>> rights
>> but I am not sure. I feel pretty confident it is one of these two things.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com>
>> wrote
>> in message news:8C18EF57-E9AC-47D8-9081-B761360C5F8E@.microsoft.com...
>> > Hi:
>> >
>> > I have developed a report that uses and XSLT file to format data found
>> > in
>> a
>> > CLOB field from our DB2 database. It works just great in Report
>> > Designer,
>> > but when I deploy to the Reporting Server, it does not render the data
>> from
>> > the CLOB but rather an error.
>> >
>> > The value of the text box is
>> >
>> > = Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
>> >
>> > I have created a C:\XSL directory on the Reports Server and placed a
>> > copy
>> of
>> > the ClearingHouseException.xsl file in it, but it just isn't working.
>> >
>> > What am I doing wrong?
>> >
>> > Thanks.
>> >
>> > Bruce.
>>
I have developed a report that uses and XSLT file to format data found in a
CLOB field from our DB2 database. It works just great in Report Designer,
but when I deploy to the Reporting Server, it does not render the data from
the CLOB but rather an error.
The value of the text box is
= Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
I have created a C:\XSL directory on the Reports Server and placed a copy of
the ClearingHouseException.xsl file in it, but it just isn't working.
What am I doing wrong?
Thanks.
Bruce.If this works from development and not from production then you are hitting
a security problem. Could be one of two places, either the rights on the
c:\xsl directory OR (more likely) the rights for the code you are executing.
By default RS limits what code can do to only safe things, which accessing
the file system is definitely not safe. I suggest reading up on what you
would have to do if you were using your own assemblies (this isn't your own
assembly but it is the same thing). You might need to have thise be your own
assembly instead of code behind in order to get the proper security rights
but I am not sure. I feel pretty confident it is one of these two things.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:8C18EF57-E9AC-47D8-9081-B761360C5F8E@.microsoft.com...
> Hi:
> I have developed a report that uses and XSLT file to format data found in
a
> CLOB field from our DB2 database. It works just great in Report Designer,
> but when I deploy to the Reporting Server, it does not render the data
from
> the CLOB but rather an error.
> The value of the text box is
> = Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
> I have created a C:\XSL directory on the Reports Server and placed a copy
of
> the ClearingHouseException.xsl file in it, but it just isn't working.
> What am I doing wrong?
> Thanks.
> Bruce.|||Hi Bruce:
Thanks for the reply, although the "assembly" approach seems like a lot of
work to get right...
I did read in one book on RS that you could upload the XSL file to the same
folder in Report Manager and it could be accessed from there. Of course, it
didn't say how the report would call the file, ie: the normal C:\ path
wouldn't work. Is it possible to point to the file in the Report Manger
folder?
Thanks again.
Bruce.
"Bruce L-C [MVP]" wrote:
> If this works from development and not from production then you are hitting
> a security problem. Could be one of two places, either the rights on the
> c:\xsl directory OR (more likely) the rights for the code you are executing.
> By default RS limits what code can do to only safe things, which accessing
> the file system is definitely not safe. I suggest reading up on what you
> would have to do if you were using your own assemblies (this isn't your own
> assembly but it is the same thing). You might need to have thise be your own
> assembly instead of code behind in order to get the proper security rights
> but I am not sure. I feel pretty confident it is one of these two things.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
> in message news:8C18EF57-E9AC-47D8-9081-B761360C5F8E@.microsoft.com...
> > Hi:
> >
> > I have developed a report that uses and XSLT file to format data found in
> a
> > CLOB field from our DB2 database. It works just great in Report Designer,
> > but when I deploy to the Reporting Server, it does not render the data
> from
> > the CLOB but rather an error.
> >
> > The value of the text box is
> >
> > = Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
> >
> > I have created a C:\XSL directory on the Reports Server and placed a copy
> of
> > the ClearingHouseException.xsl file in it, but it just isn't working.
> >
> > What am I doing wrong?
> >
> > Thanks.
> >
> > Bruce.
>
>|||Sorry, I haven't tried to get to a file. Since I haven't had to mess with
the security stuff for assemblies I can't help too much there (or with
getting to the file). It does make sense for security reasons that they
would not by default allow access to the hard drive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:EE73655D-F3D0-4525-A03B-04DE703FB560@.microsoft.com...
> Hi Bruce:
> Thanks for the reply, although the "assembly" approach seems like a lot of
> work to get right...
> I did read in one book on RS that you could upload the XSL file to the
> same
> folder in Report Manager and it could be accessed from there. Of course,
> it
> didn't say how the report would call the file, ie: the normal C:\ path
> wouldn't work. Is it possible to point to the file in the Report Manger
> folder?
> Thanks again.
> Bruce.
> "Bruce L-C [MVP]" wrote:
>> If this works from development and not from production then you are
>> hitting
>> a security problem. Could be one of two places, either the rights on the
>> c:\xsl directory OR (more likely) the rights for the code you are
>> executing.
>> By default RS limits what code can do to only safe things, which
>> accessing
>> the file system is definitely not safe. I suggest reading up on what you
>> would have to do if you were using your own assemblies (this isn't your
>> own
>> assembly but it is the same thing). You might need to have thise be your
>> own
>> assembly instead of code behind in order to get the proper security
>> rights
>> but I am not sure. I feel pretty confident it is one of these two things.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com>
>> wrote
>> in message news:8C18EF57-E9AC-47D8-9081-B761360C5F8E@.microsoft.com...
>> > Hi:
>> >
>> > I have developed a report that uses and XSLT file to format data found
>> > in
>> a
>> > CLOB field from our DB2 database. It works just great in Report
>> > Designer,
>> > but when I deploy to the Reporting Server, it does not render the data
>> from
>> > the CLOB but rather an error.
>> >
>> > The value of the text box is
>> >
>> > = Code.transform(Fields!TXT.Value, "C:\XSL\ClearinghouseException.xsl")
>> >
>> > I have created a C:\XSL directory on the Reports Server and placed a
>> > copy
>> of
>> > the ClearingHouseException.xsl file in it, but it just isn't working.
>> >
>> > What am I doing wrong?
>> >
>> > Thanks.
>> >
>> > Bruce.
>>
Subscribe to:
Posts (Atom)