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 codes

I need to create formatting that is a bit different from the standard formatting codes in SSRS. Specifically, I need to have the percent sign come right after the number (SSRS inserts a space), whereas with currency fields I need to inster a space between the $ and the number (SSRS has no space). Is there any way to do this with custom formatting codes, or will I need to go through and add the percent and dollar sign in manually to each field so that the space can either be removed or inserted?

just use "#,###%" and "$ #,###" in the formatting condition of textboxes

PS when you put % in the specified manner SSRS multiply the result by 100 so you may require to divide your final result by 100. Just depends.

Priyank

|||Priyank, thanks! This works, but there's one problem. I can't seem to get the decimals if they are zero. In other words $5.00 just displays as $5. I tried using "$#,###.##" but that has the same problem... Any way to get the zeros to show?
|||

$ #,###.#0

|||Great, that did the trick! Thanks!
|||Ok, one more related question - how would I within this code specify using parens for negative values instead of a minus sign? I tried playing around with adding parens to the code, but now luck...
|||

I believe you need to use the following as your formatting code, where $ #,###.#0 is what you were using successfully for positive numbers:

Code Snippet

$ #,###.#0;($ #,###.#0);Zero

(replace Zero above with whatever you want the value 0 to look like)

... see http://msdn2.microsoft.com/en-us/library/0c899ak8(VS.80).aspx

>L<

|||Thanks so much!

I'll try that out.

custom formatting codes

I need to create formatting that is a bit different from the standard formatting codes in SSRS. Specifically, I need to have the percent sign come right after the number (SSRS inserts a space), whereas with currency fields I need to inster a space between the $ and the number (SSRS has no space). Is there any way to do this with custom formatting codes, or will I need to go through and add the percent and dollar sign in manually to each field so that the space can either be removed or inserted?

just use "#,###%" and "$ #,###" in the formatting condition of textboxes

PS when you put % in the specified manner SSRS multiply the result by 100 so you may require to divide your final result by 100. Just depends.

Priyank

|||Priyank, thanks! This works, but there's one problem. I can't seem to get the decimals if they are zero. In other words $5.00 just displays as $5. I tried using "$#,###.##" but that has the same problem... Any way to get the zeros to show?
|||

$ #,###.#0

|||Great, that did the trick! Thanks!
|||Ok, one more related question - how would I within this code specify using parens for negative values instead of a minus sign? I tried playing around with adding parens to the code, but now luck...
|||

I believe you need to use the following as your formatting code, where $ #,###.#0 is what you were using successfully for positive numbers:

Code Snippet

$ #,###.#0;($ #,###.#0);Zero

(replace Zero above with whatever you want the value 0 to look like)

... see http://msdn2.microsoft.com/en-us/library/0c899ak8(VS.80).aspx

>L<

|||Thanks so much!

I'll try that out.

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 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.
> >
> >
> >
> >
> >
>
>