Customized Schema

Hi,
Is there any way to specify an XML Schema to SQL Server so when i use "For XML", SQL Server return data in XML Format in the Schema i have defined.
For example. I need sql server to return XML as
<object name="<table_name>">
<property name="<column_name>" value="<column_value>"/>
<property name="<column_name" value="<column_value>"/>
</object>
is it possible ? is there any other work aournd ?
Thanks in advance.
Regards,
Hatim Ali.
You should be able to do that with For XML Explicit. Another option would
be to create a mapping schema and use the XPath query from SQLXML to do the
queries for you. This also uses For XML Explicit but the XPath logic
generates the query so you don't have to.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Hatim Ali" <HatimAli@.discussions.microsoft.com> wrote in message
news:63B74B0A-E902-45F8-9255-4293BD18C70F@.microsoft.com...
> Hi,
> Is there any way to specify an XML Schema to SQL Server so when i use "For
> XML", SQL Server return data in XML Format in the Schema i have defined.
> For example. I need sql server to return XML as
> <object name="<table_name>">
> <property name="<column_name>" value="<column_value>"/>
> <property name="<column_name" value="<column_value>"/>
> </object>
> is it possible ? is there any other work aournd ?
> Thanks in advance.
> Regards,
> Hatim Ali.
|||Roger's solution works if you want <colname>value</colname> or exactly now
all your column names in advance.
We do not provide a general pivoting mechanism in T-SQL for SQL Server 2000
(and the PIVOT in 2005 has some limits).
If you need a completely generic way, you best use FOR XML for getting the
<colname>value</colname> format and then use an XSLT stylesheet to transform
it into the generic form below.
Best regards
Michael
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:eIfu5RzaEHA.3480@.TK2MSFTNGP11.phx.gbl...
> You should be able to do that with For XML Explicit. Another option would
> be to create a mapping schema and use the XPath query from SQLXML to do
> the queries for you. This also uses For XML Explicit but the XPath logic
> generates the query so you don't have to.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Hatim Ali" <HatimAli@.discussions.microsoft.com> wrote in message
> news:63B74B0A-E902-45F8-9255-4293BD18C70F@.microsoft.com...
>

customize the table format

I don't want every row divided by a solid line,
how can I customize the table format so every third row
is divided by a solid line? is that even possible in reporting service? I
don't know what to do to make it happen."Britney" <britneychen_2001@.yahoo.com> wrote in message news:<#xmtlxnzEHA.824@.TK2MSFTNGP11.phx.gbl>...
> I don't want every row divided by a solid line,
> how can I customize the table format so every third row
> is divided by a solid line? is that even possible in reporting service? I
> don't know what to do to make it happen.
Hi Britney,
You can customize the table format in reporting services.
To have every third row divided by a solid line, the Borderstyle of
the textboxes inside the table have to be set based on the following
expression
=iif((RowNumber("DataSet1") mod 3=0),"Solid","None")
where DataSet1 is the dataset name you are going to use..
Hope this helps you.
Cheers,
Prathima.C|||YES, it worked partially,
the table looked pretty ugly now.
but I want the format with one line only. is that possible?
______________
1 3 4 5
1 4 5 5
2 4 5 6
______________
1 3 4 5
1 4 5 5
2 4 5 6
______________
.........
..........
"Prathima" <prathima.chandramouli@.gmail.com> wrote in message
news:f941b84.0411220300.5888d832@.posting.google.com...
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
news:<#xmtlxnzEHA.824@.TK2MSFTNGP11.phx.gbl>...
> > I don't want every row divided by a solid line,
> > how can I customize the table format so every third row
> > is divided by a solid line? is that even possible in reporting service?
I
> > don't know what to do to make it happen.
> Hi Britney,
> You can customize the table format in reporting services.
> To have every third row divided by a solid line, the Borderstyle of
> the textboxes inside the table have to be set based on the following
> expression
> =iif((RowNumber("DataSet1") mod 3=0),"Solid","None")
> where DataSet1 is the dataset name you are going to use..
> Hope this helps you.
> Cheers,
> Prathima.C|||Can u please define the problem more clearly so that i can help u. I'm
not able to get your problem correctly.
"Britney" <britneychen_2001@.yahoo.com> wrote in message news:<u8ELx2K0EHA.3976@.TK2MSFTNGP09.phx.gbl>...
> YES, it worked partially,
> the table looked pretty ugly now.
> but I want the format with one line only. is that possible?
>
> ______________
> 1 3 4 5
> 1 4 5 5
> 2 4 5 6
> ______________
> 1 3 4 5
> 1 4 5 5
> 2 4 5 6
> ______________
> .........
> ..........
>
> "Prathima" <prathima.chandramouli@.gmail.com> wrote in message
> news:f941b84.0411220300.5888d832@.posting.google.com...
> > "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:<#xmtlxnzEHA.824@.TK2MSFTNGP11.phx.gbl>...
> > > I don't want every row divided by a solid line,
> > > how can I customize the table format so every third row
> > > is divided by a solid line? is that even possible in reporting service?
> I
> > > don't know what to do to make it happen.
> >
> > Hi Britney,
> >
> > You can customize the table format in reporting services.
> > To have every third row divided by a solid line, the Borderstyle of
> > the textboxes inside the table have to be set based on the following
> > expression
> >
> > =iif((RowNumber("DataSet1") mod 3=0),"Solid","None")
> >
> > where DataSet1 is the dataset name you are going to use..
> > Hope this helps you.
> > Cheers,
> > Prathima.C|||never mind, I figured it out.
your logic is correct, I specified it in default input of border style, it
draw all the four lines (top, bottom, left ,right) as solid lines, but I
really should just specify it on "bottom" input box of border style.
thanks for your help
"Prathima" <prathima.chandramouli@.gmail.com> wrote in message
news:f941b84.0411232053.66adc74d@.posting.google.com...
> Can u please define the problem more clearly so that i can help u. I'm
> not able to get your problem correctly.
>
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
news:<u8ELx2K0EHA.3976@.TK2MSFTNGP09.phx.gbl>...
> > YES, it worked partially,
> >
> > the table looked pretty ugly now.
> >
> > but I want the format with one line only. is that possible?
> >
> >
> >
> > ______________
> > 1 3 4 5
> > 1 4 5 5
> > 2 4 5 6
> > ______________
> > 1 3 4 5
> > 1 4 5 5
> > 2 4 5 6
> > ______________
> > .........
> > ..........
> >
> >
> >
> > "Prathima" <prathima.chandramouli@.gmail.com> wrote in message
> > news:f941b84.0411220300.5888d832@.posting.google.com...
> > > "Britney" <britneychen_2001@.yahoo.com> wrote in message
> > news:<#xmtlxnzEHA.824@.TK2MSFTNGP11.phx.gbl>...
> > > > I don't want every row divided by a solid line,
> > > > how can I customize the table format so every third row
> > > > is divided by a solid line? is that even possible in reporting
service?
> > I
> > > > don't know what to do to make it happen.
> > >
> > > Hi Britney,
> > >
> > > You can customize the table format in reporting services.
> > > To have every third row divided by a solid line, the Borderstyle of
> > > the textboxes inside the table have to be set based on the following
> > > expression
> > >
> > > =iif((RowNumber("DataSet1") mod 3=0),"Solid","None")
> > >
> > > where DataSet1 is the dataset name you are going to use..
> > > Hope this helps you.
> > > Cheers,
> > > Prathima.C

customize export format drop down list

Hi All,
Is there any way to customize export format drop down list?
I do not want so many export formats,I just want these 3 [PDF, Excel,
tif]
option to apear in the dropdown.
Thanks in advance.
Rakesh HalderIn the \Microsoft SQL Server\MSSQL.X\Reporting
Services\ReportServer\rsreportserver.config file. Go to the <Render> section
and set the types as Visible="false" for the ones you want to hide.
I hope this helps.
--
Ron
"rakesh.halder@.gmail.com" wrote:
> Hi All,
> Is there any way to customize export format drop down list?
> I do not want so many export formats,I just want these 3 [PDF, Excel,
> tif]
> option to apear in the dropdown.
> Thanks in advance.
> Rakesh Halder
>sql

CustomColumn

Hi All, How to define a column in sql2k with format of its value as
10,000,000
TIA"Vai2000" <nospam@.microsoft.com> wrote in message
news:eBl8SrazFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Hi All, How to define a column in sql2k with format of its value as
> 10,000,000
>
> TIA
>
Formatting is a front-end issue. You should probably create the column as a
BIGINT, or some type of Decimal.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi
create table #t
(
col1 int,
col2 as convert(varchar,cast(col1 as money),1)
)
insert into #t values (10000)
select * from #t
If it does not help you ,please lookup SUBSTRING system function in the BOL
"Vai2000" <nospam@.microsoft.com> wrote in message
news:eBl8SrazFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Hi All, How to define a column in sql2k with format of its value as
> 10,000,000
>
> TIA
>|||You don't. Formatting is controlled by your client application, not by
SQL Server.
If it's a numeric column then use one of the numeric datatypes.
David Portas
SQL Server MVP
--|||how about if you are exporting data to excel and want to populate in excel
in that format...avoiding writing format in excel...
TIA
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128957032.919975.66170@.f14g2000cwb.googlegroups.com...
> You don't. Formatting is controlled by your client application, not by
> SQL Server.
> If it's a numeric column then use one of the numeric datatypes.
> --
> David Portas
> SQL Server MVP
> --
>|||"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23F4F4EbzFHA.2792@.tk2msftngp13.phx.gbl...
> how about if you are exporting data to excel and want to populate in excel
> in that format...avoiding writing format in excel...
Still a front-end issue.
Storing is not the same as displaying or migrating.
If you want to migrate it as something else, then you have multiple options.
You could create a view and convert it to just about any format that you
wish.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, I am looking the convert format to the desired Column
type..(1,00,000)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OZNzqIbzFHA.612@.TK2MSFTNGP10.phx.gbl...
> "Vai2000" <nospam@.microsoft.com> wrote in message
> news:%23F4F4EbzFHA.2792@.tk2msftngp13.phx.gbl...
excel
>
> Still a front-end issue.
> Storing is not the same as displaying or migrating.
> If you want to migrate it as something else, then you have multiple
options.
> You could create a view and convert it to just about any format that you
> wish.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||how to convert a view to desired format?
Thanks a lot
"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23sqAnObzFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Thanks, I am looking the convert format to the desired Column
> type..(1,00,000)
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OZNzqIbzFHA.612@.TK2MSFTNGP10.phx.gbl...
> excel
> options.
>|||"Vai2000" <nospam@.microsoft.com> wrote in message
news:%23iPvPQbzFHA.3660@.TK2MSFTNGP15.phx.gbl...
> how to convert a view to desired format?
> Thanks a lot
Take a look at the CONVERT function in SQL Server.
-- Create a table variable to hold our data.
DECLARE @.Foo TABLE (
x bigint
)
-- Load a value
INSERT @.Foo VALUES (10000000)
-- Convert BigInt to money datatype, then convert the money to a character
string and specify the "1" flag
-- so that commas are added in the appropriate place.
SELECT CONVERT(varchar(20), CONVERT(money, x), 1) FROM @.Foo
Rick Sawtell
MCT, MCSD, MCDBA

Custom xml format with FOR XML EXPLICIT

I'm working on Sql Server 2005 xml capabilities, but I'm not able to obtain what I want.

Let's say I have a table with 10 fields: Field1, Field2, ..., Field10.

I would, with a "FOR XML" clause, obtain an xml document like this one:

<MyTable>
<Field1>F1value</Field1>
<Field2 Field3="F3value">F2value</Field2>
</MyTable>
<MyTable>
...
</MyTable>

I think correct way is with the "EXPLICIT" mode, but I'm not able to find the syntax to make Field3 become an attribute of the element generated from Field2.

Does someone knows if it's possible and, if it is, how?

Any help will be truly appreciated.

Easier to do this

select Field1 as "Field1",
Field3 as "Field2/@.Field3",
Field2 as "Field2"
from MyTable
for xml path('MyTable')

|||Thank you so much!

This is exactly what I was looking for.sql

Custom number format

Hello everybody,
I have a float type field in my sql server table and I want to select the
data in the format $XX,XXX.XX.
Any ideas '
Thanks.usamaalam wrote:

> Hello everybody,
> I have a float type field in my sql server table and I want to select the
> data in the format $XX,XXX.XX.
> Any ideas '
> Thanks.
Your client application controls how data is formatted for display, not
SQL Server. If you want to do that in SQL you'll have to return a
string rather than a number. I don't recommend it, but you can take a
look at the CONVERT function in Books Online.
Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
so is an unusual and probably unwise choice for accounting. More
typically, I would suggest you should use NUMERIC/DECIMAL for this.
Hope this helps.
David Portas
SQL Server MVP
--|||I need select float in the specified format and return the formatted string.
Thanks.
"David Portas" wrote:

> usamaalam wrote:
>
> Your client application controls how data is formatted for display, not
> SQL Server. If you want to do that in SQL you'll have to return a
> string rather than a number. I don't recommend it, but you can take a
> look at the CONVERT function in Books Online.
> Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
> so is an unusual and probably unwise choice for accounting. More
> typically, I would suggest you should use NUMERIC/DECIMAL for this.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||As David says, you should do it in the application tier and not in the
database. If you are using .NET in your application tier, there are powerful
formatting capabilities.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
news:8207FDF2-C83E-491A-AE06-FF91FD965E14@.microsoft.com...
>I need select float in the specified format and return the formatted
>string.
> Thanks.
> "David Portas" wrote:
>|||Basically the application mechanism has been developed by someone else. I
need to export data from some tables in CSV format. The mechanism executes
an stored procedure, get data in a data table and a method gets the data
table as a parameter and generates CSV whatever present in the data table.
This will be more helpful for me to do it in sql, if possible.
Thanks a lot.
"SriSamp" wrote:

> As David says, you should do it in the application tier and not in the
> database. If you are using .NET in your application tier, there are powerf
ul
> formatting capabilities.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
> news:8207FDF2-C83E-491A-AE06-FF91FD965E14@.microsoft.com...
>
>|||One method:
SELECT
'$' + CONVERT(varchar(20), CAST(MyFloatColumn AS money), 1)
FROM MyTable
I agree with the others that this is a kludge. This is a deficiency in the
application that ought to be addressed in the app code rather than
Transact-SQL.
Happy Holidays
Dan Guzman
SQL Server MVP
"usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
news:874B2D06-CA54-4852-AB97-4EB07762DA84@.microsoft.com...
> Basically the application mechanism has been developed by someone else. I
> need to export data from some tables in CSV format. The mechanism
> executes
> an stored procedure, get data in a data table and a method gets the data
> table as a parameter and generates CSV whatever present in the data table.
> This will be more helpful for me to do it in sql, if possible.
> Thanks a lot.
> "SriSamp" wrote:
>|||i've needed to do this myself a number of times, format the output on the
SELECT because i'm using BCP to plonk the data into an output file ready for
FTP'ing off to another server somewhere for another department/company to
use.
I'm not going to go to the effort of building a .NET application just to do
this; far simpler and quicker to format on the SELECT and output using BCP -
less components, less complexity.
There are times in reporting too where we need to do this because it would
be more difficult to do it in the reporting tool, for instance reporting
services; consider a trading report where trade amounts might be in
different currency, two ways of doing it - either concatenate in the front
end which might be more difficult or just simply do it easily on the SELECT.
You need to consider what you are doing before definitively saying do it in
the front end or in the db.
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1135337208.989446.250540@.g43g2000cwa.googlegroups.com...
> usamaalam wrote:
>
> Your client application controls how data is formatted for display, not
> SQL Server. If you want to do that in SQL you'll have to return a
> string rather than a number. I don't recommend it, but you can take a
> look at the CONVERT function in Books Online.
> Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
> so is an unusual and probably unwise choice for accounting. More
> typically, I would suggest you should use NUMERIC/DECIMAL for this.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Maybe you should consider using XML as a transport medium, instead of CSV.
After all, this is the 21st century. :)
If the data is being used in an application at the other end then I really
see no point in formatting it, unless the actual data type is ignored at the
other end, which I doubt to be true.
ML
http://milambda.blogspot.com/|||i just want to reiterate what David said: if at all possible, change the
float to another type - you *will* get bad data at some point, and this
being money, that will be a problem.
if not possible for you to do it, hound the person who can do it :)
usamaalam wrote:
> Hello everybody,
> I have a float type field in my sql server table and I want to select the
> data in the format $XX,XXX.XX.
> Any ideas '
> Thanks.|||On Fri, 23 Dec 2005 08:17:02 -0800, "ML" <ML@.discussions.microsoft.com> wrot
e:
in <61588BC5-7F5E-4BD4-A95E-7B6B24984F3A@.microsoft.com>

>Maybe you should consider using XML as a transport medium, instead of CSV.
>After all, this is the 21st century. :)
I don't understand that sort of reasoning. What makes a proprietary schema
and
bloated data better than the CSV format?
Stefan Berglund

Custom Number format

There is a standard number format 1,234.00
but how can i make a custom one?
I more need like 1.234,00
ThanksHi , you'll need to use custom formatting strings . Something like
{0:0.000,0} should do the trick for you. You can access the formaat
string from the properties entry.
Cheers
Shai
On Dec 11, 2:01 am, Extern Bureaublad
<ExternBureaub...@.discussions.microsoft.com> wrote:
> There is a standard number format 1,234.00
> but how can i make a custom one?
> I more need like 1.234,00
> Thanks

Custom Formatting SQLXML Result

Here's what am doing:
I have a couple of SPs that can only return data in the format i want when i use FOR XML EXPLICIT.
The problem is if am using SQLXML then the data cannot be returned as XML since formatting is done by the client (which only gives me the option of raw/nested) and therefore the result i get is not formatted as i need it.
Is there a way i can format the result from the SP in the same way as if i were just using FOR XML EXPLICIT on the server side.

Example of output formatting expected:
<Patients_List>
<patient>
<Field name="forename"/>
<Field name="surname">Patience</Field>
</patient>
<patient>
<Field name="forename">Adam</Field>

<Field name="surname">Sandler</Field>
</patient>
...
</Patients_List>

My problem is, the raw/nested options cannot format the output as above (using FOR XML EXPLICIT could achieve this).

Any suggestions?

Are you using SQL2005? If so please give me your table structure. I might help you to write FOR XML RAW/nested query.|||Am actually using SQL 2000.

sample table structure.

Table Name: pat_info

id int
surname varchar(35)
forename varchar(35)
dob datetime
occupation varchar(100)

not sure if the structure helps (its just part of table).

|||

Hello,

You can use XPath query over annotated xsd schema (which internally translates into FOR XML EXPLICIT queries) to get the desired results.

Hope this helps,

Monica

|||

Hello,

You can use XPath query over annotated xsd schema (which internally translates into FOR XML EXPLICIT queries) to get the desired results.

http://msdn2.microsoft.com/en-us/library/ms171802.aspx

Hope this helps,

Monica

Custom Formatting

I need to Format some items in a different way.
for example I have values that are .962 and want
the report to format this to 0.962 but
if say the value is .65 I want it to format it as 0.65
how would I do this?Patrick,
Try Format(Convert.ToDouble("<your field>"), "0.00")
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Patrick K" <PatrickK@.discussions.microsoft.com> wrote in message
news:FD864917-BC4A-4DFB-912F-B893AD8B6AC5@.microsoft.com...
> I need to Format some items in a different way.
> for example I have values that are .962 and want
> the report to format this to 0.962 but
> if say the value is .65 I want it to format it as 0.65
> how would I do this?|||"Teo Lachev [MVP]" wrote:
> Patrick,
> Try Format(Convert.ToDouble("<your field>"), "0.00")
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Patrick K" <PatrickK@.discussions.microsoft.com> wrote in message
> news:FD864917-BC4A-4DFB-912F-B893AD8B6AC5@.microsoft.com...
> > I need to Format some items in a different way.
> > for example I have values that are .962 and want
> > the report to format this to 0.962 but
> > if say the value is .65 I want it to format it as 0.65
> >
> > how would I do this?
>
> That is good but what if the values are mixed, some are .962 and others are .65|||Then use a custom function which will determine the length of the string and
figure out the format specifier.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Patrick K" <PatrickK@.discussions.microsoft.com> wrote in message
news:74808F24-4605-4DAD-8F55-00D80D677B8F@.microsoft.com...
>
> "Teo Lachev [MVP]" wrote:
> > Patrick,
> >
> > Try Format(Convert.ToDouble("<your field>"), "0.00")
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Patrick K" <PatrickK@.discussions.microsoft.com> wrote in message
> > news:FD864917-BC4A-4DFB-912F-B893AD8B6AC5@.microsoft.com...
> > > I need to Format some items in a different way.
> > > for example I have values that are .962 and want
> > > the report to format this to 0.962 but
> > > if say the value is .65 I want it to format it as 0.65
> > >
> > > how would I do this?
> >
> >
> > That is good but what if the values are mixed, some are .962 and others
are .65

Custom Formatt Required

Hi,
I need custom format string for displaying percentage values.
If negative values are there it should be displayed within
paranthesis(brackets), it should display two decimal values also.
Example:
Present Value Format
--
-0.21%
Required Value Format
--
(0.21)%
Plzz help me with any suggestion/url, it's very urgent.
Thanks and Regards,
Rajesh Yennam.
HA,India.#0.0%;(#0.0%)
HTH
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in message
news:3FFB1760-D5F1-4092-A4F5-1F339F6CC6B6@.microsoft.com...
> Hi,
> I need custom format string for displaying percentage values.
> If negative values are there it should be displayed within
> paranthesis(brackets), it should display two decimal values also.
> Example:
> Present Value Format
> --
> -0.21%
> Required Value Format
> --
> (0.21)%
> Plzz help me with any suggestion/url, it's very urgent.
> Thanks and Regards,
> Rajesh Yennam.
> HA,India.|||Take a look at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
You can use semi-colons in your format string semicolon to specify positive,
negative, and zero formats, e.g.
%;(%);%
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in message
news:3FFB1760-D5F1-4092-A4F5-1F339F6CC6B6@.microsoft.com...
> Hi,
> I need custom format string for displaying percentage values.
> If negative values are there it should be displayed within
> paranthesis(brackets), it should display two decimal values also.
> Example:
> Present Value Format
> --
> -0.21%
> Required Value Format
> --
> (0.21)%
> Plzz help me with any suggestion/url, it's very urgent.
> Thanks and Regards,
> Rajesh Yennam.
> HA,India.|||Thanks a lot for your response Tim.
It's working fine.
Thanks and Regards,
Rajesh Yennam
HA,India.
"Tim Ellison" wrote:
> #0.0%;(#0.0%)
> HTH
> "Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in message
> news:3FFB1760-D5F1-4092-A4F5-1F339F6CC6B6@.microsoft.com...
> > Hi,
> > I need custom format string for displaying percentage values.
> > If negative values are there it should be displayed within
> > paranthesis(brackets), it should display two decimal values also.
> >
> > Example:
> > Present Value Format
> > --
> > -0.21%
> >
> > Required Value Format
> > --
> > (0.21)%
> >
> > Plzz help me with any suggestion/url, it's very urgent.
> >
> > Thanks and Regards,
> > Rajesh Yennam.
> > HA,India.
>
>

Custom Format

Hi,
I've a problem. I get some decimals data from by database like 123,45 and
I'd like to create a custom format to print in my reports the decimals like
123,45 â?¬. If I've 0 in database I'd like print as 0,00 â?¬
How can I do that?
ThanksYes I can change the format but I can't choose â?¬ format. I can have only $
symbol.
How can I choose the â?¬ symbol?
"Mike Klaarhamer" wrote:
> Hi,
> Click right on the textbox and properties.
> There you can change the format to currency.
> Mike
> "Seb" wrote:
> > Hi,
> > I've a problem. I get some decimals data from by database like 123,45 and
> > I'd like to create a custom format to print in my reports the decimals like
> > 123,45 â?¬. If I've 0 in database I'd like print as 0,00 â?¬
> > How can I do that?
> >
> > Thanks
> >|||Try to change the report Language :-)
"Seb" wrote:
> Yes I can change the format but I can't choose â?¬ format. I can have only $
> symbol.
> How can I choose the â?¬ symbol?
> "Mike Klaarhamer" wrote:
> > Hi,
> > Click right on the textbox and properties.
> > There you can change the format to currency.
> >
> > Mike
> >
> > "Seb" wrote:
> >
> > > Hi,
> > > I've a problem. I get some decimals data from by database like 123,45 and
> > > I'd like to create a custom format to print in my reports the decimals like
> > > 123,45 â?¬. If I've 0 in database I'd like print as 0,00 â?¬
> > > How can I do that?
> > >
> > > Thanks
> > >|||Yes, change the Language for example to: Dutch (Netherlands)
Don't forget the (Netherlands), because when you change it to only Dutch,
you cant export the report to PDF.
Mike
"Soan" wrote:
> Try to change the report Language :-)
> "Seb" wrote:
> > Yes I can change the format but I can't choose â?¬ format. I can have only $
> > symbol.
> > How can I choose the â?¬ symbol?
> >
> > "Mike Klaarhamer" wrote:
> >
> > > Hi,
> > > Click right on the textbox and properties.
> > > There you can change the format to currency.
> > >
> > > Mike
> > >
> > > "Seb" wrote:
> > >
> > > > Hi,
> > > > I've a problem. I get some decimals data from by database like 123,45 and
> > > > I'd like to create a custom format to print in my reports the decimals like
> > > > 123,45 â?¬. If I've 0 in database I'd like print as 0,00 â?¬
> > > > How can I do that?
> > > >
> > > > Thanks
> > > >

Custom Export Format List in Rendered ReportViewer Control...

I know that at the server level you can suppress the export types that
appear in the export format list control when a report is rendered. Some of
my reports need to only display a subset of the server defined export types.
An example would probably best help in my issue... I need to display one
report with options to export being Excel and PDF. With another report I
need Excel and CSV. How can I set the export list control to only display
export types required for a specific report?
Thanks in advance.
--
Bob MorvayThat is not supported in the current product, it is controlled for the
entire app.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob Morvay" <adamantiumrocks@.hotmail.com> wrote in message
news:ejFlBLNeFHA.2664@.TK2MSFTNGP15.phx.gbl...
>I know that at the server level you can suppress the export types that
>appear in the export format list control when a report is rendered. Some
>of my reports need to only display a subset of the server defined export
>types. An example would probably best help in my issue... I need to
>display one report with options to export being Excel and PDF. With
>another report I need Excel and CSV. How can I set the export list control
>to only display export types required for a specific report?
> Thanks in advance.
> --
> Bob Morvay
>|||Well that sucks :) Thanks for responding.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eaA4oEleFHA.2584@.tk2msftngp13.phx.gbl...
> That is not supported in the current product, it is controlled for the
> entire app.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Bob Morvay" <adamantiumrocks@.hotmail.com> wrote in message
> news:ejFlBLNeFHA.2664@.TK2MSFTNGP15.phx.gbl...
>>I know that at the server level you can suppress the export types that
>>appear in the export format list control when a report is rendered. Some
>>of my reports need to only display a subset of the server defined export
>>types. An example would probably best help in my issue... I need to
>>display one report with options to export being Excel and PDF. With
>>another report I need Excel and CSV. How can I set the export list
>>control to only display export types required for a specific report?
>> Thanks in advance.
>> --
>> Bob Morvay
>>
>

Custom date format...

Hi,
I'm just trying to get custom date formatting working. I have a datetime
variable plotted on an x axis of a scatter plot. The axis is marked as
continuous ('numeric of timescale values').
I'd like the time to appear as eg 'Jan 04' and thought this would be
possible based on the posting by Robert Bruckner in response to 'Custom Date
Format' (09/Apr) in which he said that one could use these functions
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
However I cannot get any of these codes to work. I have tried putting the
codes in or by putting the format code as this:
=Format(Fields!calDay, "MMM")
Woudl be very grateful if someone could tell me whether this is possible and
if so how I should go about it.
Thanks,
Phil
PS I don't want to convert to a string as I have lots of values and quite
like keeping it as a continuous axis.Pull up chart properties dialog, go to X-axis tab, check "Numeric and
Timescale Values" checkbox, check the "Show Labels" checkbox, and enter "MMM
yy" in the Format code textbox (without the double-quotes.)
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Phil Aldis" <PhilAldis@.discussions.microsoft.com> wrote in message
news:5D4AF02E-618F-4FAD-9E52-473A5EAFBB70@.microsoft.com...
> Hi,
> I'm just trying to get custom date formatting working. I have a datetime
> variable plotted on an x axis of a scatter plot. The axis is marked as
> continuous ('numeric of timescale values').
> I'd like the time to appear as eg 'Jan 04' and thought this would be
> possible based on the posting by Robert Bruckner in response to 'Custom
Date
> Format' (09/Apr) in which he said that one could use these functions:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
> However I cannot get any of these codes to work. I have tried putting the
> codes in or by putting the format code as this:
> =Format(Fields!calDay, "MMM")
> Woudl be very grateful if someone could tell me whether this is possible
and
> if so how I should go about it.
> Thanks,
> Phil
> PS I don't want to convert to a string as I have lots of values and quite
> like keeping it as a continuous axis.
>
>
>|||Thanks - works perfectly.
Phil
"Ravi Mumulla (Microsoft)" wrote:
> Pull up chart properties dialog, go to X-axis tab, check "Numeric and
> Timescale Values" checkbox, check the "Show Labels" checkbox, and enter "MMM
> yy" in the Format code textbox (without the double-quotes.)
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Phil Aldis" <PhilAldis@.discussions.microsoft.com> wrote in message
> news:5D4AF02E-618F-4FAD-9E52-473A5EAFBB70@.microsoft.com...
> > Hi,
> >
> > I'm just trying to get custom date formatting working. I have a datetime
> > variable plotted on an x axis of a scatter plot. The axis is marked as
> > continuous ('numeric of timescale values').
> >
> > I'd like the time to appear as eg 'Jan 04' and thought this would be
> > possible based on the posting by Robert Bruckner in response to 'Custom
> Date
> > Format' (09/Apr) in which he said that one could use these functions:
> >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
> >
> > However I cannot get any of these codes to work. I have tried putting the
> > codes in or by putting the format code as this:
> >
> > =Format(Fields!calDay, "MMM")
> >
> > Woudl be very grateful if someone could tell me whether this is possible
> and
> > if so how I should go about it.
> >
> > Thanks,
> >
> > Phil
> >
> > PS I don't want to convert to a string as I have lots of values and quite
> > like keeping it as a continuous axis.
> >
> >
> >
> >
> >
>
>

Custom Date Format

I need to change the date format from mmddyyyy to yyyymmdd and I'm not
quite sure how to accomplish it. Obviously there are default date
views, but yyyymmdd is not one of them. I'm guessing I need to create
an expression. Can someone help?
Thanks,
tpTry putting this into a textbox.
= Format(<Database Field>, "yyyyMMdd")
This should help with all formatting inquries:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
Regards,
Dan|||Thanks Dan. I figured it was an easy one...|||I went though the MSDN docs, and put together a small document which include
all of the formatting special characters which can be used...It might be
worth downloading and printing out if you do a lot of reporting services..
It is at www.msbicentral.com. It is in the articles section, titled
'Formatting Expressions in Reporting Services"
Hope this helps.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"newsgroups@.bendcycling.org" wrote:
> Thanks Dan. I figured it was an easy one...
>

custom datatype SQL server 2000

Hi All
In our DB we have info in a format of "150:1" resp "nnn:n". Its information
about Contrast Ratio.
This is of course typed as varchar. That prevents us from sorting the fields
the way we need. we are also
unable to do any kind of 'simple' compare < >
I guess in 2005 we could use custom Data Type feature. Is there any
workaround in MSSQL 2000
Thanx for any hints...
PetttA ratio (rational number) can be expressed as a decimal number. You could
add a computed column to your tabe that represents the contrast ratio as a
decimal
ALTER TABLE [YourTable]
ADD [DecimalRatio]
AS CAST(LEFT([ContrastRatio], CHARINDEX(':',[ContrastRatio]) - 1) AS numeric)
/
CAST(RIGHT([ContrastRatio], LEN([ContrastRatio]) -
CHARINDEX(':',[ContrastRatio])) AS numeric)
"Petr SIMUNEK" wrote:

> Hi All
> In our DB we have info in a format of "150:1" resp "nnn:n". Its informatio
n
> about Contrast Ratio.
> This is of course typed as varchar. That prevents us from sorting the fiel
ds
> the way we need. we are also
> unable to do any kind of 'simple' compare < >
> I guess in 2005 we could use custom Data Type feature. Is there any
> workaround in MSSQL 2000
> Thanx for any hints...
> Pettt
>
>|||Petr
Why not split the values into 2 columns? You could then build a sequence or
do a comparison using a quotient of the 2 values, right? You can always
combine them back to ratio notation for display purposes.
CREATE TABLE Foobar (
key_col VARCHAR(3) NOT NULL PRIMARY KEY
,contrast_num INT NOT NULL
,contrast_den INT NOT NULL
);
INSERT INTO Foobar VALUES ('AAA', 150, 1);
INSERT INTO Foobar VALUES ('BBB', 20, 1);
INSERT INTO Foobar VALUES ('CCC', 1, 1);
INSERT INTO Foobar VALUES ('DDD', 43, 2);
INSERT INTO Foobar VALUES ('EEE', 25, 6);
SELECT
key_col
,CAST(contrast_num AS VARCHAR(3)) + ':' + CAST(contrast_den AS CHAR(1))
AS contrast_ratio
FROM Foobar
ORDER BY contrast_num / contrast_den
;
Comparisons could work the same way.
Joe

Custom Component - Change Data Type

I am developing a custom pipeline component (my first real one) which will handle input columns in a DT_NUMERIC format and convert them to a DT_DATE format. Sort of like the included Data Conversion component, but with some built in business logic. The Data Conversion component puts the converted data in a new column in the output buffer. I would like to keep the data in the same column. So, in my input buffer the column would be of type DT_NUMERIC, but in the output buffer I want to make it DT_DATE.

What is the best approach to do this?

-DarrellUnless you want you component to have to copy data from the input to the ouput you can not do this. There are 2 types of outputs, synchronous and asynchronous. The synchronous one is simple and requires no data copying or column creation (except of course for the new column you would produce of the DT_DATE type). However, because of this you can't change column types and that is why the derived column doesn't allow this. The asynchronous one is complex. You have to create all the output columns and keep them in sync with the input columns and know how to map the input columns to the output columns so that you can copy the data to the correct place. My advise is use a sync output and live with the additional column unless it is absolutely necessary to use and async output.

HTH,
Matt

Custom Col. Format

Hi All, This is what I am trying to accomplish
SELECT a query from SQL2k2 export to Excel 2k.
When I populate excel workbook I need the data to be formatted in certain
order (esp. my column values should have comma) like 10000 should be 10,000
Unfortunately not allowed to write macros or any VBA's in excel...so the
only way to do would be
Format the data in SQL before sending to Excel...
can someone provide me a headstart?
TIA
-- pseudo code looking for something like this
-- create table T (myCustomCol varchar(20) Format(#,##,###)Vai2000,
1. Create your table in SQL Server
2. Populate the table with data
3. Create a view with the data format required
4. Use DTS to export the data from the view to the Excel workbook
5. Schedule the DTS package as a job for automation
HTH
Jerry
"Vai2000" <nospam@.microsoft.com> wrote in message
news:OQBg0dbzFHA.3924@.TK2MSFTNGP14.phx.gbl...
> Hi All, This is what I am trying to accomplish
> SELECT a query from SQL2k2 export to Excel 2k.
> When I populate excel workbook I need the data to be formatted in certain
> order (esp. my column values should have comma) like 10000 should be
> 10,000
> Unfortunately not allowed to write macros or any VBA's in excel...so the
> only way to do would be
> Format the data in SQL before sending to Excel...
> can someone provide me a headstart?
> TIA
> -- pseudo code looking for something like this
> -- create table T (myCustomCol varchar(20) Format(#,##,###)
>|||See function "convert" in BOL.
Example:
select parsename(convert(varchar(25), cast(12345678 as money), 1), 2)
go
AMB
"Vai2000" wrote:

> Hi All, This is what I am trying to accomplish
> SELECT a query from SQL2k2 export to Excel 2k.
> When I populate excel workbook I need the data to be formatted in certain
> order (esp. my column values should have comma) like 10000 should be 10,00
0
> Unfortunately not allowed to write macros or any VBA's in excel...so the
> only way to do would be
> Format the data in SQL before sending to Excel...
> can someone provide me a headstart?
> TIA
> -- pseudo code looking for something like this
> -- create table T (myCustomCol varchar(20) Format(#,##,###)
>
>|||Thanks but my data getting exported to excel doesn't has comma, since my
Table in SQL is a regular INT column!
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uFoV1hbzFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Vai2000,
> 1. Create your table in SQL Server
> 2. Populate the table with data
> 3. Create a view with the data format required
> 4. Use DTS to export the data from the view to the Excel workbook
> 5. Schedule the DTS package as a job for automation
> HTH
> Jerry
> "Vai2000" <nospam@.microsoft.com> wrote in message
> news:OQBg0dbzFHA.3924@.TK2MSFTNGP14.phx.gbl...
certain
the
>

Custom Code/function to format Seconds to hh:mm:ss with ability to go over 24 hours

Hello,

I am trying to get this to work - but it only returns minutes & seconds:

Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = (seconds - ss) / 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function

Can anyone help me out? I am not that familiar with VB.

Thanks,

Deb

I think this is more of an access report expression format, but it should help you out.

=Int([sumofacd]/3600) & ":" & (Int([sumofacd]/60)-(Int([sumofacd]/3600)*60)) & ":" & format(([sumofacd] Mod 60),"00")

"It works by dividing sumofacd by the number of seconds in 1 hour, the integer of this then becomes the Hours, we then need to take the remainder and convert it to Minutes.

There is more than one way to do the next part. My choice is to divide sumofacd by 60, the integer of this will be the total number of minutes from sumofacd, I then repeat the first part of the equation this time multiplying it by 60 to change it back to minutes, I then minus this from the minutes calculated earlier, this gives the minutes (ie less than 60 or 1 hour).

To get seconds is much easier, as the other elements are made up of multiples of 60 we only require the balance, below 60 so we use the VB Mod function to do this and then format the result.

My first error was to ignore the formatting, doing this would give a :3 instead of :03, and thinking about it you maybe should format the minutes the same way to give 0:03:03 instead of 0:3:03.

Think I will stop looking at the equation, can see additional brakets in the minutes calculation now, for the record would complete each part of the equation before attempting to use the format, as you need the result before formatting, this way you are less likely to get mixed up due to the high number of brackets used"

http://www.utteraccess.com/forums/showflat.php?Board=87&Number=1140884

|||

Thanks for your reply, however, I am not sure how to incorporate this into my report. Does the above expression belong in the code itself, or is it referenced in a report field? This is the first time I've ever tried to use code in a RS report, if you could steer me in the right direction I'm sure I can figure it out.

Deb

Custom Assembly Error

I have a SQL Server 2000 database that has a table with a text field whose
contents are in RTF format. My goal is to display this text in SSRS
reports. I have created a custom assembly to convert rtf formated text into
plain text.
Imports System.Windows.Forms
Public Class RichTextServices
Public Shared Function ConvertToPlainText(ByVal RTFText As String) As String
Dim m_rtb = New RichTextBox
m_rtb.Rtf = RTFText
Return m_rtb.Text
End Function
End Class
The assembly works great in preview mode. However, when I run the report
from my browser I get an error in the field containing the converted text.
I have copied my DLL to "C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\bin".
I have also added the attached code group to the "rssrvpolicy.config" and
"rsmgrpolicy.config" files. It still does not work.
What am I missing? Any suggestions would be greatly appreciated.
PS - If anyone knows a better way to handle RTF in SSRS that would also be
great to know. Especially if you can actually display the text with the
original formatting.
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="MyCodeGroup"
Description="Code group for my data processing extension">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\myAssembly.dll"
/>
</CodeGroup>Hello Guess,
It seems the issue is caused by RichTextBox which is a com object. I wonder
if you also deploy the interop com assembly to the following folder:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin
Also, I suggest that you refer to the following article to deploy your
custom assembly in reporting service.
Deploying a Custom Assembly
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/
rsp_prog_rdl_8mue.asp
Have a great day!
Best Regards,
Peter Yang
MCSE2000, MCSA, MCDBA
Microsoft Partner Online Support
Get Secure! - www.microsoft.com/security
=====================================================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.
| Reply-To: "Guess Nospam" <guess@.nospam.ha>
| From: "Guess Nospam" <guess@.nospam.ha>
| Subject: Custom Assembly Error
| Date: Mon, 25 Oct 2004 16:33:58 -0700
| Lines: 62
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <O2KcisuuEHA.3376@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: ftp.city.vancouver.bc.ca 199.175.219.1
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12