Formatting query/view in SQL Server Management Studio (2005)

This may be a stupid question but...
When designing a query or view, I write the SQL visually formatted the way I
like it. But when I run the query in Management Studio, the software
reformats the query into a mess (even though it still runs).
Is there an option or configuration which would allow me to keep the
formatting that I like?
Should I post this in another newsgroup?Yes, use a query window (Ctrl + N), not the query designer?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9563908A-1E74-4606-AEB8-7099BF9C6BB6@.microsoft.com...
> This may be a stupid question but...
> When designing a query or view, I write the SQL visually formatted the way
> I
> like it. But when I run the query in Management Studio, the software
> reformats the query into a mess (even though it still runs).
> Is there an option or configuration which would allow me to keep the
> formatting that I like?
> Should I post this in another newsgroup?

Formatting query/view in SQL Server Management Studio (2005)

This may be a stupid question but...
When designing a query or view, I write the SQL visually formatted the way I
like it. But when I run the query in Management Studio, the software
reformats the query into a mess (even though it still runs).
Is there an option or configuration which would allow me to keep the
formatting that I like?
Should I post this in another newsgroup?
Yes, use a query window (Ctrl + N), not the query designer?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9563908A-1E74-4606-AEB8-7099BF9C6BB6@.microsoft.com...
> This may be a stupid question but...
> When designing a query or view, I write the SQL visually formatted the way
> I
> like it. But when I run the query in Management Studio, the software
> reformats the query into a mess (even though it still runs).
> Is there an option or configuration which would allow me to keep the
> formatting that I like?
> Should I post this in another newsgroup?

Formatting query/view in SQL Server Management Studio (2005)

This may be a stupid question but...
When designing a query or view, I write the SQL visually formatted the way I
like it. But when I run the query in Management Studio, the software
reformats the query into a mess (even though it still runs).
Is there an option or configuration which would allow me to keep the
formatting that I like?
Should I post this in another newsgroup?Yes, use a query window (Ctrl + N), not the query designer?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9563908A-1E74-4606-AEB8-7099BF9C6BB6@.microsoft.com...
> This may be a stupid question but...
> When designing a query or view, I write the SQL visually formatted the way
> I
> like it. But when I run the query in Management Studio, the software
> reformats the query into a mess (even though it still runs).
> Is there an option or configuration which would allow me to keep the
> formatting that I like?
> Should I post this in another newsgroup?

Formatting parameters

Hi!
I would like to change date parameter from
4/30/2002 12:00:00 AM to 30.4.2002 in report.
How can parameters be formatted?
Thanks
Romanhi Bender,
Write this expression in the the textbox that you want to show you the date:
=format(Globals!ExecutionTime,"dd.MM.yyyy")
i hope it will help you.
"Bender" wrote:
> Hi!
> I would like to change date parameter from
> 4/30/2002 12:00:00 AM to 30.4.2002 in report.
> How can parameters be formatted?
> Thanks
> Roman
>
>|||For parameters, you must aplicate the function format to
Parameters!parameter_name.value (instead of Globals!ExecutionTime).
"Bender" wrote:
> Hi!
> I would like to change date parameter from
> 4/30/2002 12:00:00 AM to 30.4.2002 in report.
> How can parameters be formatted?
> Thanks
> Roman
>
>|||I'll try to explain again what I want to do :)
In the Report Parameters dialog I define my Parameter and set
Data Type to Datetime (there is not many options anyway).
When I run report I get 4/1/2002 12:00:00 AM in parameter box on
top of report (parameters section of report), which is correct.
But I want to format this to see and make input like
dd.MM.yyyy and without time part.
Any ideas.
Thanks
"Mirela" <Mirela@.discussions.microsoft.com> wrote in message
news:037EE286-0206-42A1-88C9-A6C3303EE254@.microsoft.com...
> For parameters, you must aplicate the function format to
> Parameters!parameter_name.value (instead of Globals!ExecutionTime).
> "Bender" wrote:
> > Hi!
> >
> > I would like to change date parameter from
> > 4/30/2002 12:00:00 AM to 30.4.2002 in report.
> >
> > How can parameters be formatted?
> >
> > Thanks
> > Roman
> >
> >
> >

formatting in cube

Is there a way to format data in the cube?

I have a date formatted as such:

YYYY-MM

I want to show the date as 01, 02, 10, 11, ... instead of 2006 1, 2006 2, I want 2006 01, 2006 02, and so on

is this possible to accomplish?

Also,

I have numbers showing

5.12345677999E-02

How can i get this to show as 5.12345 only?

Is this something that should be handled on the db/select statement side or is there a way to do this on the analysis services cube side?

thanks

On the date formatting, I am assuming that your dates are part of a dimension. In this case what you would normally do is to define a column either in a view or in the DSV that formats the date the way you want it and returns this as a string. You then set this string up to be the attributes name and set up the actual value to be the attribute key. This allows you to do things like sort by values, but display nice strings for the names.

On the numbers issue, there is a format string that you can define for every measure. The only problem here is that Analysis Services can return both the formatted and unformatted values and some clients (like Excel prior to Excel 2007) only take the unformatted value, so the formatting may have to be done client side.

|||

i have my derived column formatted as yyyy-dd-mm but how would I have the 0 in front of 1, 2, 3, 4, 5, 6, 7, 8, 9 and not in front of 10,11,12?

what would i have to add in the derived column task?

|||You can use the Len() function to determine if your number is 1 character long, and prefix a zero if it is. Len is supported in the SSIS derived column transformation, if that is what you were referring to in your question. It's also supported in most databases, so you could implement it in your data source view as well.|||

In T-SQL you could do something like the following, just replace the call to GETDATE() with the date column name in your table

SELECT LEFT(CONVERT(varchar,getdate(),121),7)

The 121 format is "yyyy-mm-dd hh:mi:ss.mmm(24h)" and you just grab the left 7 of that.

Formatting Date /Time in SQL 2000

I used to have an Access 2k query that formatted this date field:
Format([PrDateStart],"yyyy/mm/dd"" 00:00:01""") AS ProjectStartDate
That gave me the date as this:
2006/03/30 00:00:01
Recently, we moved the data to a SQL 2000 table, so now I need to
create a view that gives me this date in that same format like I was
able to get it with that access query.
The actual date stored in the SQL table is 03/30/20006.
We use (convert(varchar,getdate(),101)) to get only the actual date
without the time.
Can anyone help me figure out how to get the same date format in SQL
Server 2000?
Thanks.SELECT CONVERT(CHAR(10), GETDATE(), 111);
http://www.aspfaq.com/2464
<ILCSP@.NETZERO.NET> wrote in message
news:1143819658.697747.320460@.j33g2000cwa.googlegroups.com...
>I used to have an Access 2k query that formatted this date field:
> Format([PrDateStart],"yyyy/mm/dd"" 00:00:01""") AS ProjectStartDate
> That gave me the date as this:
> 2006/03/30 00:00:01
> Recently, we moved the data to a SQL 2000 table, so now I need to
> create a view that gives me this date in that same format like I was
> able to get it with that access query.
> The actual date stored in the SQL table is 03/30/20006.
> We use (convert(varchar,getdate(),101)) to get only the actual date
> without the time.
> Can anyone help me figure out how to get the same date format in SQL
> Server 2000?
> Thanks.
>|||First off, if you've changed to a SQL server 2000 back end I'd hope
you've moved to a sensible front end (like .NET) in which case this is
where you should be doing the formatting (.NET has a very easy and
powerful set of format strings).
otherwise, to my knowledge you can only use the set formats that
convert gives you, otherwise you'll have to build up your string using
CAST(DatePart(YEAR,mydate) as char(4)) + '/' + CAST(DATEPART(MONTH,
mydate) as char(2)) + '/' etc...
NOTE: it's important to cast them as strings, otherwise sql my
interpret this as an arithmetic expression and start dividing the year
by the month etc.
Cheers
Will|||SELECT replace(convert(varchar, getdate(), 120),'-','/') as todays_date|||Hi guys, I did use Aaron's date and then I added a string showing the
seconds and that worked out well..
Thanks for your suggestions.
By the way, yes, we're moving to .NET in the coming months.

formatting

I have a textbox on a report containing a number I want formatted in
scientific notation. When I bring up the properties of the textbox, there
is a formatting option. I select number and am given a choice of
1.234123e+003. This works great, but I really don't want to show that many
number in the format. I would rather have the number something sensible,
say 1.23e+03. It looks like the custom format option might give the ability
to format the number this way, but I can't find any examples on how to use
it.
Any ideas?
Thanks.They're listed in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp?frame=true.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Leon Chuck Gosslin" <powerb@.inel.gov> wrote in message
news:ekCx1oveEHA.2916@.TK2MSFTNGP12.phx.gbl...
> I have a textbox on a report containing a number I want formatted in
> scientific notation. When I bring up the properties of the textbox, there
> is a formatting option. I select number and am given a choice of
> 1.234123e+003. This works great, but I really don't want to show that
many
> number in the format. I would rather have the number something sensible,
> say 1.23e+03. It looks like the custom format option might give the
ability
> to format the number this way, but I can't find any examples on how to use
> it.
>
> Any ideas?
>
> Thanks.
>
>|||Thanks, man...I owe you a cold beer...
Leon.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:%23YUg5wveEHA.3200@.TK2MSFTNGP09.phx.gbl...
> They're listed in
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp?frame=true.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Leon Chuck Gosslin" <powerb@.inel.gov> wrote in message
> news:ekCx1oveEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > I have a textbox on a report containing a number I want formatted in
> > scientific notation. When I bring up the properties of the textbox,
there
> > is a formatting option. I select number and am given a choice of
> > 1.234123e+003. This works great, but I really don't want to show that
> many
> > number in the format. I would rather have the number something
sensible,
> > say 1.23e+03. It looks like the custom format option might give the
> ability
> > to format the number this way, but I can't find any examples on how to
use
> > it.
> >
> >
> >
> > Any ideas?
> >
> >
> >
> > Thanks.
> >
> >
> >
> >
>

Formatted Values in Stored Procedures

Hello,
I have to put some data from an SQL Server 2000 DB to a
Word document containing some numeric values (money).
I use a stored procedure like this
select field1 + ' ' + field2 + ' ' + field3 ...
from table1 where ...
So I get back one long string. How can I get all numeric
values in this string formatted? There is no format
function like in VB/A.
Second: I'd like to have a tab (Ascii 9) instead of ' ':
How can I get this?
Klaus
www.trappdata.deFormatted like what?
Take a look at this
DECLARE @.v MONEY
SELECT @.v = 1322323.6666
SELECT CONVERT(VARCHAR,@.v,0) --1322323.67 Rounded but not formatted
SELECT CONVERT(VARCHAR,@.v,1) --1,322,323.67 Formatted with commas
SELECT CONVERT(VARCHAR,@.v,2) --1322323.6666 No formatting
If you have a decimal field it doesn't work with the convert function.
The work around is to cast it to money
DECLARE @.v2 DECIMAL (36,10)
SELECT @.v2 = 13243543.56565656
SELECT CONVERT(VARCHAR,CONVERT(MONEY,@.v2),1) --13,243,543.57 Formatted
with commas
http://sqlservercode.blogspot.com/

Formatted text in report parameter

I want the user to add his/her own title and some text to the report which can be bold, different color etc. Can this be done via a single report parameter and are there any ways to do it?

Not out of the box. If you use SRS 2005, the ReportViewer controls (part of VS 2005) will allow you to show reports in any ASP.NET or Winforms application. Using the controls, you can hide the report parameters page and replace it with your own prompting logic. See www.gotreportviewer.com for more details.

If you use SRS 2000, you can achieve most of this by putting an iframe to the report server in your web page and include rc:Parameters=False to hide the parameters area. You web page would be responsible for parameters prompting and for feeding the values to the report server iframe. For ex: http://server/reportserver/?PathToTheReport&rc:Parameters=false&Param1=value1&Param2=Value2

Formatted text in a column;

Hi All ,

Is it possible to insert the below text into a column and retrieve it in the same format ?

Thanks,

Hari Haran Arulmozhi

TEXT :

CLIENT NAME : ABC Corporation
CLIENT CITY : MUMBAI

================================================
INV_NO INV_DATE INV_AMT
================================================
I100 01-01-2006 Rs.600
I200 01-02-2006 Rs.800
I300 01-03-2006 Rs.1600
I400 01-04-2006 Rs.2600
I500 01-05-2006 Rs.9600

RECEIVED ADVANCE :Rs.10000u will need to take care of the formatting in the front end.|||Well, strictly speaking you CAN do the formatting on the back end. What harshall means is that you SHOULD do the formatting on the front end.

Formatting is a presentation issue and does not fall within the scope of a database server.

Read up on the datetime datatype and how it is stored in Books Online.|||Thanks Harshal & Blindman !!

Formatted Text Data in DB

Can anyone tell me if SQL Server can store formatted text like words in
bold, italic, different font size, etc. ?
Researching and trying to find the answer to anything on microsoft sites is
impossible!
TIA
JeffThis is a multi-part message in MIME format.
--=_NextPart_000_004A_01C368C3.7A69EEA0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You could create a Word document and then save it as an image datatype =in a table.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com.sql
.
"Jeff Reed" <thebigvalbosky@.hotmail.com> wrote in message =news:OJwvLROaDHA.2572@.TK2MSFTNGP12.phx.gbl...
Can anyone tell me if SQL Server can store formatted text like words =in
bold, italic, different font size, etc. ?
Researching and trying to find the answer to anything on microsoft =sites is
impossible!
TIA
Jeff
--=_NextPart_000_004A_01C368C3.7A69EEA0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Dwindows-1252">
<META content=3D"MSHTML 6.00.2800.1226" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#c0c0c0>
<DIV><FONT size=3D2>You could create a Word document and then save it as =an image datatype in a table.</FONT></DIV>
<DIV><BR>-- <BR> Tom<BR>---<BR>Thomas A. Moreau, =BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL Server Professional<BR>Toronto, ON Canada<BR><A href=3D"www.pinnaclepublishing.com=/">http://www.pinnaclepublishing.com.sql">www.pinnaclepublishing.com=
.sql</A><BR>.<BR></DIV>
<BLOCKQUOTE style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Jeff Reed" <<A =href=3D"mailto:thebigvalbosky@.hotmail.com">thebigvalbosky@.hotmail.com</A>=> wrote in message <A =href=3D"news:OJwvLROaDHA.2572@.TK2MSFTNGP12.phx.gbl">news:OJwvLROaDHA.2572=@.TK2MSFTNGP12.phx.gbl</A>...</DIV>Can anyone tell me if SQL Server can store formatted text like words =in<BR>bold, italic, different font size, etc. ?<BR><BR>Researching and trying to =find the answer to anything on microsoft sites =is<BR>impossible!<BR><BR>TIA<BR><BR>Jeff<BR><BR></BLOCKQUOTE></BODY></HTM=L>
--=_NextPart_000_004A_01C368C3.7A69EEA0--

Formatted Text

I want to build a report that would resemble a Word document. This report
would have a bold header and in the body of the report have a variety of
formatted text (bold, underline, italic, and different size fonts). There may
be differents formats on the same line.
I only see one way to do this and that is with the Textbox control. In this
report I would have to have a lot of Textbox controls in the layout to
generate one report page!
What other way can I accomplish this? Are there third party SQL Reporting
Services controls?Do you mean paragraphs of information with certain words bolded, for
example? That would be difficult, since there isn't a rich text control
yet.
If you mean to have simply repeating headers and paragraphs, similar to Word
headings, then that would be relatively easy using a list control or table
control.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"nickka" <nickka@.discussions.microsoft.com> wrote in message
news:27B6EF1E-0CFA-4328-828C-DD7B72C56572@.microsoft.com...
>I want to build a report that would resemble a Word document. This report
> would have a bold header and in the body of the report have a variety of
> formatted text (bold, underline, italic, and different size fonts). There
> may
> be differents formats on the same line.
> I only see one way to do this and that is with the Textbox control. In
> this
> report I would have to have a lot of Textbox controls in the layout to
> generate one report page!
> What other way can I accomplish this? Are there third party SQL Reporting
> Services controls?|||I mean paragraphs of information and the need for a rich text control.
Can third party controls be installed in SQL Reporting Services? Are there
any third party controls? Is there any information on developing custom
controls?
"Jeff A. Stucker" wrote:
> Do you mean paragraphs of information with certain words bolded, for
> example? That would be difficult, since there isn't a rich text control
> yet.
> If you mean to have simply repeating headers and paragraphs, similar to Word
> headings, then that would be relatively easy using a list control or table
> control.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "nickka" <nickka@.discussions.microsoft.com> wrote in message
> news:27B6EF1E-0CFA-4328-828C-DD7B72C56572@.microsoft.com...
> >I want to build a report that would resemble a Word document. This report
> > would have a bold header and in the body of the report have a variety of
> > formatted text (bold, underline, italic, and different size fonts). There
> > may
> > be differents formats on the same line.
> >
> > I only see one way to do this and that is with the Textbox control. In
> > this
> > report I would have to have a lot of Textbox controls in the layout to
> > generate one report page!
> >
> > What other way can I accomplish this? Are there third party SQL Reporting
> > Services controls?
>
>|||Third-party controls aren't supported yet, but I think they will in a future
release.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"nickka" <nickka@.discussions.microsoft.com> wrote in message
news:2CDD598F-F70D-4EDC-9520-9AFD50C8F32D@.microsoft.com...
>I mean paragraphs of information and the need for a rich text control.
> Can third party controls be installed in SQL Reporting Services? Are there
> any third party controls? Is there any information on developing custom
> controls?
> "Jeff A. Stucker" wrote:
>> Do you mean paragraphs of information with certain words bolded, for
>> example? That would be difficult, since there isn't a rich text control
>> yet.
>> If you mean to have simply repeating headers and paragraphs, similar to
>> Word
>> headings, then that would be relatively easy using a list control or
>> table
>> control.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "nickka" <nickka@.discussions.microsoft.com> wrote in message
>> news:27B6EF1E-0CFA-4328-828C-DD7B72C56572@.microsoft.com...
>> >I want to build a report that would resemble a Word document. This
>> >report
>> > would have a bold header and in the body of the report have a variety
>> > of
>> > formatted text (bold, underline, italic, and different size fonts).
>> > There
>> > may
>> > be differents formats on the same line.
>> >
>> > I only see one way to do this and that is with the Textbox control. In
>> > this
>> > report I would have to have a lot of Textbox controls in the layout to
>> > generate one report page!
>> >
>> > What other way can I accomplish this? Are there third party SQL
>> > Reporting
>> > Services controls?
>>

Formatted procs

Can somebody provide me an argument on how nicely formatted and indented procs perform better. I am currently formatting a 1000 line proc just to understand what it does ... Maybe your argument will help convince my development team to write neat procs :)Originally posted by Enigma
Can somebody provide me an argument on how nicely formatted and indented procs perform better. I am currently formatting a 1000 line proc just to understand what it does ... Maybe your argument will help convince my development team to write neat procs :)

As far as my knowledge goes, indentation and formatting of the proc has nothing got to do with the performance. Its just that, u improve the readability of the code, and believe me it is very much required. I had stumbled upon a stored proc which was not working properly, it was about 1500 lines and the code was so badly written without any comments, that it took hours first to format it, then to understand the whole thing. Properly formatted code performs better in the long run, meaning after six months of coding if u have to debug the proc, it wouldn't be a mess.|||Can somebody supply me with an argument as to why my kids should help keep the house clean? They claim that it provides just as much shelter with crap strewn all over the place, because it is the roof that counts. I say that when the place is cluttered it makes it harder to find things they have lost and it is more likely that things will get accidentally broken, but they aren't buying this. So I am currently cleaning up a 1000 sqr foot floor in order to make the area a more practical and inviting place to live.

I think I will just tell my development team (oops, I mean kids) that if they don't keep the place clean they will not get their allowance.|||Make the sentence for writing undocumented, poorly formatted code be a stint doing nothing but maintenance? At least in the shops that I've worked in, that would make them consider suicide as a viable alternative!

-PatP|||Oh, I like that one!|||Buy a big bat....

I gotta find that thread where we had a war about this...

don't forget change conmtrol in the comments up front...

does anyone comment out old code and leave it if it's being altered?|||I don't even want to start this one...Naming conventions, coding standards, change control, release management, and the most dreaded one, - documentation...|||I've got a little Perl script that shrouds T-SQL. It removes all the extraneous whitespace and renames the variables, cursors, and temp tables @.v1, @.v2, @.v3... The thing is a work of pure evil, but you could always ask them to debug a 1500 line stored procedure that you'd passed through the shrouder.

-PatP|||Ha ha .. i would love to have that script ;)

formatted outputs

Hi,
Is there a way to get stored procedure texts out in a formatted way
like defncopy in Sybase??? The way they print it out really
sucks.........
Regards
dba_sybaseYou can Use Enterprise manager to script them in formatted way .. unless you really not able to use Em .. or try using

exec sp_helptext 'sp_name'|||Thanks AAshu. I already knew that but it seems that the third pary application created the SP as a single line. So I couldnot find a way to formatt them other than formatting them by hand.

Thanks

Jaideep|||Try this trick for a select statement (not updates or deletes!):

Copy your line to the clipboard.
Open select a table (any table) in your database in Enterprise Manager, right click, and select show all rows (or show data, or whatever).
When the window opens up and displays the data, look for an icon near the top left that will display the SQL code applied (likely just a select from statement),
Replace the SQL Statement with what you have on the clipboard and requery by clicking the red "!" icon.
EM should reformat your code before displaying the results. If it doesn't, try adding a dummy table to the display and then removing it.

Sorry if this isn't step-by-step clear, but I'm going from memory.

Personally, I don't like the way EM formats statements, so I usually do some touch-up afterward, but its better than one long line of code.

blindman|||Thanks

formatted output

Hi,
Is there a way to get stored procedure texts out in a formatted way
like defncopy in Sybase??? The way they print it out really
sucks.........

Regards

SubhasSubhas (dba_sybase2003@.yahoo.com) writes:
> Is there a way to get stored procedure texts out in a formatted way
> like defncopy in Sybase??? The way they print it out really
> sucks.........

There is no command-line utility, but there are scripting facilities
both in Query Analyzer and Enterprise Manager.

In QA click F8 to get an Object Brower. Right-click a procedure and you
find the scripting option. In EM, right-click a database and pick
All Tasks and then Generate SQL Scripts.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

formatted invoice line item report

I am trying to make a report that show invoice line items using a table with
groups. We what the table to fill the page no matter how many line items are
on the invoice. For example the invoice my only have three items on the last
page so the group footer ends up in the middle of the page. The group footer
has the invoice total. How can we place group footer at the bottom of the
last page? Even in the case of a small invoice that only has one page ?I'm going to take a stab at this but I hope someone comes up with a better
way. I could use it myself. Anyway here goes:
Insert a row in your above your totals in the group footer.
Figure out the number of line items that fit on a page.
Make the row height of the just inserted row an expression equal to
(MaxLineItemsPerPage - (LineItemCount MOD MaxLineItemsPerPage)) *
LineItemRowHeight)
This will give you the number of "blank" lines on the last page of the
invoice and make your empty footer row that height to fill in the space. The
expression will get a little more complicated if pages 2 on have more rows
than page 1 (i.e. no invoice header) but I think the calculation can be done.
Let me know if this works...Or if someone comes up with a better way!
"DJJIII" wrote:
> I am trying to make a report that show invoice line items using a table with
> groups. We what the table to fill the page no matter how many line items are
> on the invoice. For example the invoice my only have three items on the last
> page so the group footer ends up in the middle of the page. The group footer
> has the invoice total. How can we place group footer at the bottom of the
> last page? Even in the case of a small invoice that only has one page ?

Formatted HTML input to Sql Server.

Hello,
I am having a html editor for headers and footers and would like to
pass this HTML to reporting services as a field.When i am doing this , i am
getting just the HTML (including Tags) Is there any way how to display the
html using reporting services.Not currently, no.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"kartheek via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:533FBF91F5CBD@.SQLMonster.com...
> Hello,
> I am having a html editor for headers and footers and would like to
> pass this HTML to reporting services as a field.When i am doing this , i
> am
> getting just the HTML (including Tags) Is there any way how to display the
> html using reporting services.

formatted file using stored procedure

how do i create a formatted file using a stored procedure?

for eg. if i have the following...

date : 5th April : should occupy 6 spaces (numeric) (05/04/2003)
Place: London : should occupy 20 spaces (text).
Country: United Kingdom should occupy 20 spaces (text)
EOL Terminator: (1 character only)
:
:
:

the sample final output should be like:

050403London United Kingdom
050403Washington DC USA
050403Delhi India

any quick help is appreciated,earlier post discarded the spaces between the sample records... it should reas as

050403London^^^^^^^^^^^^^^United Kingdom^^^^^^
:
:

where ^ denotes a blank space.

regards|||In your SELECT statement CONVERT the text fields to fix length CHAR. So if City needs to be 40 characters it is CONVERT(CHAR(40), City). You can run the stored procedure from ISQL using -h-1 so no header get printed

C:\>isql -E -S -Q"select status,CONVERT(CHAR(35),name)+CONVERT
(char(10),crdate,103) from sysobjects where type='S'" -h-1

-2147483645 sysobjects 13/11/1998
-2147483643 sysindexes 13/11/1998
-2147483645 syscolumns 13/11/1998
-2147483645 systypes 13/11/1998
-2147483647 syscomments 13/11/1998
-2147483648 sysfiles1 13/11/1998
-2147483647 syspermissions 13/11/1998

Substitue the SELECT with your stored procedure. Or even better use BCP.

Formating Column Date - Probably simple problem, please help

Hi,
I have a data table with a field as :
aField char(6) NO NULLS
The field aField is supposed to hold numbers formatted as 012033 (2
groups of 3 digits, each starting with 0).
Some of the entries in this field are formatted the right way, some
are missing 0s in front like for the number above: 1233 instead
012033.
I would like to fix this quickly by issuing a query to format all
numbers that are of length 4 (e.g. 1233) to numbers of length 6 (e.g.
012033) by adding 0s at the begining and in middle.
Can somebody help me with this?
Any help will be appreciated.I ment "Formatting Column Data" not " ... Date"
thank you
On Thu, 24 Mar 2005 19:39:45 GMT, Dino Buljubasic
<dino@.noplacelikehome.com> wrote:

>Hi,
>I have a data table with a field as :
>aField char(6) NO NULLS
>The field aField is supposed to hold numbers formatted as 012033 (2
>groups of 3 digits, each starting with 0).
>Some of the entries in this field are formatted the right way, some
>are missing 0s in front like for the number above: 1233 instead
>012033.
>I would like to fix this quickly by issuing a query to format all
>numbers that are of length 4 (e.g. 1233) to numbers of length 6 (e.g.
>012033) by adding 0s at the begining and in middle.
>Can somebody help me with this?
>Any help will be appreciated.|||Unless you constrain it, your data is going to start looking like this right
after you issue the update. Why don't you apply this formatting when you
PRESENT the data, instead of where it is stored? Otherwise, you will have
to use a trigger or a scheduled job to run this update again, every time the
data changes (insert OR update).
Anyway, this can done with an UPDATE statement and LEFT, RIGHT, SUBSTRING,
etc. However I don't think we have enough information to give you a perfect
answer. What happens if the data is 144? Is that 001044 or 014004?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:gh56415d3f7ldcg32krns1ocnsvo8f02c4@.
4ax.com...
> Hi,
> I have a data table with a field as :
> aField char(6) NO NULLS
> The field aField is supposed to hold numbers formatted as 012033 (2
> groups of 3 digits, each starting with 0).
> Some of the entries in this field are formatted the right way, some
> are missing 0s in front like for the number above: 1233 instead
> 012033.
> I would like to fix this quickly by issuing a query to format all
> numbers that are of length 4 (e.g. 1233) to numbers of length 6 (e.g.
> 012033) by adding 0s at the begining and in middle.
> Can somebody help me with this?
> Any help will be appreciated.|||Try,
select
'0' + left(ltrim(rtrim(colA)), 2) + '0' + right(ltrim(rtrim(colA)), 2)
from
table1
where
ltrim(rtrim(colA)) like '[0-9][0-9][0-9][0-9]'
go
AMB
"Dino Buljubasic" wrote:

> I ment "Formatting Column Data" not " ... Date"
> thank you
> On Thu, 24 Mar 2005 19:39:45 GMT, Dino Buljubasic
> <dino@.noplacelikehome.com> wrote:
>
>|||I just need to fix some rows that are created in invalid format (xxyy)
due to an error inside my code. I have fixed the error in code (when
I present data), but now instead of manually fixind the database
entries (xxyy to 0xx0yy) I want to do it fast.
A number can not be 144 or any other 3 digits. It is simply a 4
digits number that needs to be formated to be 6 digits like you would
do in VB with Format function as:
Format(xx, "000") & Format(yy, "000") = 0xx0yy.
To summarize, the data is always goint to be a number consisting of 2
parts xx and yy. Each of these is a 2 digit number. I need to make
it 3 digit number by appending 0 in front and then concatanating these
two to get 0xx0yy.
Thank you
On Thu, 24 Mar 2005 14:46:26 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>Unless you constrain it, your data is going to start looking like this righ
t
>after you issue the update. Why don't you apply this formatting when you
>PRESENT the data, instead of where it is stored? Otherwise, you will have
>to use a trigger or a scheduled job to run this update again, every time th
e
>data changes (insert OR update).
>Anyway, this can done with an UPDATE statement and LEFT, RIGHT, SUBSTRING,
>etc. However I don't think we have enough information to give you a perfec
t
>answer. What happens if the data is 144? Is that 001044 or 014004?|||UPDATE table
SET aField = '0'+LEFT(LTRIM(aField),2)+'0'+RIGHT(aFie
ld,2)
WHERE LEN(LTRIM(aField))=4
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:vj664195prmsmjtvqsv7vfdv0saut8tgde@.
4ax.com...
> I just need to fix some rows that are created in invalid format (xxyy)
> due to an error inside my code. I have fixed the error in code (when
> I present data), but now instead of manually fixind the database
> entries (xxyy to 0xx0yy) I want to do it fast.
> A number can not be 144 or any other 3 digits. It is simply a 4
> digits number that needs to be formated to be 6 digits like you would
> do in VB with Format function as:
> Format(xx, "000") & Format(yy, "000") = 0xx0yy.
> To summarize, the data is always goint to be a number consisting of 2
> parts xx and yy. Each of these is a 2 digit number. I need to make
> it 3 digit number by appending 0 in front and then concatanating these
> two to get 0xx0yy.
> Thank you
>
> On Thu, 24 Mar 2005 14:46:26 -0500, "Aaron [SQL Server MVP]"
> <ten.xoc@.dnartreb.noraa> wrote:
>
right
have
the
SUBSTRING,
perfect
>|||If I am not mistaken he is using a char(6) field and then you would need
rtrim not ltrim am I right'
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23HnNuxKMFHA.1176@.TK2MSFTNGP12.phx.gbl...
> UPDATE table
> SET aField = '0'+LEFT(LTRIM(aField),2)+'0'+RIGHT(aFie
ld,2)
> WHERE LEN(LTRIM(aField))=4
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
> news:vj664195prmsmjtvqsv7vfdv0saut8tgde@.
4ax.com...
> right
> have
> the
> SUBSTRING,
> perfect
>|||Thanks Aaron,
It works fine, just needed to fix the last part:
Instead '0' + RIGHT(aField, 2) needs to be:
'0' + RIGHT(RTRIM(aField), 2)
It does exactly what I needed. I appreciate your help.
On Thu, 24 Mar 2005 15:02:09 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>UPDATE table
> SET aField = '0'+LEFT(LTRIM(aField),2)+'0'+RIGHT(aFie
ld,2)
> WHERE LEN(LTRIM(aField))=4|||Yep, I think so... I'm used to just doing LTRIM(RTRIM())...
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Denis" <denis.gobo@.gmail.com> wrote in message
news:#lzSO2KMFHA.2464@.TK2MSFTNGP10.phx.gbl...
> If I am not mistaken he is using a char(6) field and then you would need
> rtrim not ltrim am I right'
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23HnNuxKMFHA.1176@.TK2MSFTNGP12.phx.gbl...
time
>

Format Question (Currency)

I know that you can set a textbox to be of format Currency, but how
can I set the text of a textbox equal to a string that has formatted
strings within it?
What I mean is, lets say that I am getting back 2 values: MyFieldA and
MyFieldB, which are both floats (say 12.3123 and 43.2234). I want to
make the text "MyFieldA - MyFieldB", where both are formatted as
currency, so it would look like "$12.31 - $43.22".
Is there a way, with in the section where I specify
Fields!MyFieldA.Value to do something like
Fields!MyFieldA.Value.Format("C") or something like that? anyone
know?Chris,
Maybe something like this:
=(String.Format("{$###,##0.00}"),Fields!MyFieldA.Value) & " - " &
(String.Format("{$###,##0.00}"),Fields!MyFieldB.Value)
I had thought about using cdec, but I don't think that works in RS.
I'm pretty new to the RS world, so I don't know it this will help but
it doesn't hurt to try. Good Luck.
Jonathan
cmay@.walshgroup.com (Chris) wrote in message news:<44f6e099.0410261338.3f991c5@.posting.google.com>...
> I know that you can set a textbox to be of format Currency, but how
> can I set the text of a textbox equal to a string that has formatted
> strings within it?
> What I mean is, lets say that I am getting back 2 values: MyFieldA and
> MyFieldB, which are both floats (say 12.3123 and 43.2234). I want to
> make the text "MyFieldA - MyFieldB", where both are formatted as
> currency, so it would look like "$12.31 - $43.22".
> Is there a way, with in the section where I specify
> Fields!MyFieldA.Value to do something like
> Fields!MyFieldA.Value.Format("C") or something like that? anyone
> know?