How do you change the Y axis? For instance, instead of 10, I may want
it to show 10% Or instead of 1,000 I may want it to show $1000 (with
dollar sign). Basically I'm just trying to learn how to manipulate it
period.
Thanks!How a Chart's Y axis values are fomatted are controlled via the Chart Options
dialog : Y Axis tab. In your case you will need to supply a formatting string
in the Format code textbox. The information at this link will give some idea
about how to construct the string:
http://msdn2.microsoft.com/en-us/library/26etazsy.aspx.
"Malik" wrote:
> How do you change the Y axis? For instance, instead of 10, I may want
> it to show 10% Or instead of 1,000 I may want it to show $1000 (with
> dollar sign). Basically I'm just trying to learn how to manipulate it
> period.
> Thanks!
>
Showing posts with label dollar. Show all posts
Showing posts with label dollar. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Formatting Currency Numbers from Analaysis Services
Hi,
I'm using Reporting Services in conjunction with Analysis Services.
The problem I'm having is formatting numbers to be dollar amounts. I know how to set this in Reporting Services and when a data source was regular SQL, then everything was just fine. However, when I switch to Analysis Services the number would just stay the same.
i.e. "60.2" instead of "$60.20". The only thing I can think of is if Analysis Services is returning the number as a string for some reason, and RS won't format strings for currency.
Any thoughts? Thanks!You should be able to format measures from SSAS the same way you do this with a relational database. Can you repro your issue with the Adventure Works cube?|||I think this is the problem: the numbers that being returned are attributes not measures... which is right b/c I'm using the numbers as parameters for drill-down reports.
So the question is how do you format a number returned by AS20005 as an attribute to be a U.S. dollar amount? Thanks!|||Okay, looks like the attribute number was getting returned as a string. I used CDbl() to convert into a double and it works fine now.
Wednesday, March 7, 2012
Format numeric to display dollar value $1,000.00
Hi,
Currently, I have a numeric field stored a value of 1000, how can I covert
it to varchar value and display it as $1,000.00
Thanks>> Currently, I have a numeric field stored a value of 1000, how can I cover
t it to varchar value and display it as $1,000.00 <<
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.
You are the kid in the class that the other students make fun of
because he just does not understand ...|||I need to store a string of combine text that should should appear ....
$1,000.00
I'm wondering, if there that's a way to do that.
"--CELKO--" wrote:
> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
> You are the kid in the class that the other students make fun of
> because he just does not understand ...
>|||The following example illustrates how this can be achieved:
CREATE TABLE dbo.currency
(
col1 INT
)
INSERT dbo.currency SELECT 1000
INSERT dbo.currency SELECT 10
INSERT dbo.currency SELECT 999999
SELECT '$' + CONVERT(VARCHAR(20), CAST(col1 AS MONEY), 1)
FROM dbo.currency
HTH
- Peter Ward
WARDY IT Solutions
"slimla" wrote:
> I need to store a string of combine text that should should appear ....
> $1,000.00
> I'm wondering, if there that's a way to do that.
>
> "--CELKO--" wrote:
>|||Extract from my blog:
http://sqlblogcasts.com/blogs/tonyr...1/429.aspx....
Introduction
Application programmers and Business Intelligent professionals are faced
with having to format data - taylored into what the users want. We have two
choices as to where we do this processing, keep it in the database using the
facilities of the database engine, for instance T-SQL, standard SQL dialect,
CLR, XML or whatever the product has to offer or we can bring the data out
of the database and down into the front end application or middle tier and
format the data there (keep the database for store and retreive only).
My Opinion
The IT industry is full of rules and best practices unfortunetly some of
these rules and best practices aren't based on current technology or
business problems, in fact some of the rules and best practices are based on
techniques adopted in the 70's and 80's on mainframes or early client server
architecture.
No product is just a database anymore, sure SQL Server stores and retrieves
data but it also offers us a lot more, in fact its moving more towards being
the middle and data tiers in the three tier architecture now that SQL Server
can be a web service and the inclusion of CLR.
Data formatting, be it paging, value concatenation should always been done
where it is most efficient to do it. Consider (and benchmark) where its most
efficient to do this, would you really drag 1 million rows into the middle
tier or client browser only to get page 2 of 20 rows? It doesn't make sense.
Relating this to a well known expert, --CELKO--, he states that you should
NEVER do formatting in the database and it should always be done in the
front end. Think this through, take value concatenation for instance, say
you need to create a list of values for a given product category, for
instance for a given person show the mailing lists they belong to. In the
database this will be held in rows, so if a person belongs to 5 mailing
lists there will be 5 rows, now, say the user requires the values to be
normalised so they are displayed on just one line entry. We have two
choices, drag the 5 rows down to the front end or middle tier and use a 4GL
to process the data or we can use some of the extensions available in SQL
Server to do this.
Example
create table mailing_list (
individual_name nvarchar(100) not null,
list_name nvarchar(10) not null
)
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
C' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
A' )
select distinct
individual_name,
list = substring(
( select ', ' + list_name as [text()]
from mailing_list m2
where m2.individual_name = m1.individual_name
for xml path(''), elements )
, 3, 100 )
from mailing_list m1
Gives this result :-
alex r List A
joe r List A, List B
tony r List A, List B, List C
Now, just how easy was that! It only takes a few lines of SQL and you have
also saved a lot of network traffic back out to the middle tier or front
end.
So, my point is this: whatever you do - always think through what you are
doing, don't just follow 'rules' blindly!
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150411335.797938.9320@.f6g2000cwb.googlegroups.com...
> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
> You are the kid in the class that the other students make fun of
> because he just does not understand ...
>|||slimla wrote:
> Hi,
> Currently, I have a numeric field stored a value of 1000, how can I covert
> it to varchar value and display it as $1,000.00
> Thanks
you can also set up a compute column to have string instead of integer.
select '$' + cast(<int value> as varchar(20))|||Tony,
Very well said!
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam|||Thanks All
"P. Ward" wrote:
> The following example illustrates how this can be achieved:
> CREATE TABLE dbo.currency
> (
> col1 INT
> )
> INSERT dbo.currency SELECT 1000
> INSERT dbo.currency SELECT 10
> INSERT dbo.currency SELECT 999999
> SELECT '$' + CONVERT(VARCHAR(20), CAST(col1 AS MONEY), 1)
> FROM dbo.currency
> HTH
> - Peter Ward
> WARDY IT Solutions
>
> "slimla" wrote:
>|||I like that method, sort of gives you a formatting tier within the data
tier - nice and central and it doesn't effect storage, gives you a standard
view for people to use and code against... A lot more easier and
maintainable than using views as well...
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"BurgerKING" <syi916@.gmail.com> wrote in message
news:1150461726.914638.202330@.i40g2000cwc.googlegroups.com...
> slimla wrote:
>
> you can also set up a compute column to have string instead of integer.
> select '$' + cast(<int value> as varchar(20))
>
Currently, I have a numeric field stored a value of 1000, how can I covert
it to varchar value and display it as $1,000.00
Thanks>> Currently, I have a numeric field stored a value of 1000, how can I cover
t it to varchar value and display it as $1,000.00 <<
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.
You are the kid in the class that the other students make fun of
because he just does not understand ...|||I need to store a string of combine text that should should appear ....
$1,000.00
I'm wondering, if there that's a way to do that.
"--CELKO--" wrote:
> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
> You are the kid in the class that the other students make fun of
> because he just does not understand ...
>|||The following example illustrates how this can be achieved:
CREATE TABLE dbo.currency
(
col1 INT
)
INSERT dbo.currency SELECT 1000
INSERT dbo.currency SELECT 10
INSERT dbo.currency SELECT 999999
SELECT '$' + CONVERT(VARCHAR(20), CAST(col1 AS MONEY), 1)
FROM dbo.currency
HTH
- Peter Ward
WARDY IT Solutions
"slimla" wrote:
> I need to store a string of combine text that should should appear ....
> $1,000.00
> I'm wondering, if there that's a way to do that.
>
> "--CELKO--" wrote:
>|||Extract from my blog:
http://sqlblogcasts.com/blogs/tonyr...1/429.aspx....
Introduction
Application programmers and Business Intelligent professionals are faced
with having to format data - taylored into what the users want. We have two
choices as to where we do this processing, keep it in the database using the
facilities of the database engine, for instance T-SQL, standard SQL dialect,
CLR, XML or whatever the product has to offer or we can bring the data out
of the database and down into the front end application or middle tier and
format the data there (keep the database for store and retreive only).
My Opinion
The IT industry is full of rules and best practices unfortunetly some of
these rules and best practices aren't based on current technology or
business problems, in fact some of the rules and best practices are based on
techniques adopted in the 70's and 80's on mainframes or early client server
architecture.
No product is just a database anymore, sure SQL Server stores and retrieves
data but it also offers us a lot more, in fact its moving more towards being
the middle and data tiers in the three tier architecture now that SQL Server
can be a web service and the inclusion of CLR.
Data formatting, be it paging, value concatenation should always been done
where it is most efficient to do it. Consider (and benchmark) where its most
efficient to do this, would you really drag 1 million rows into the middle
tier or client browser only to get page 2 of 20 rows? It doesn't make sense.
Relating this to a well known expert, --CELKO--, he states that you should
NEVER do formatting in the database and it should always be done in the
front end. Think this through, take value concatenation for instance, say
you need to create a list of values for a given product category, for
instance for a given person show the mailing lists they belong to. In the
database this will be held in rows, so if a person belongs to 5 mailing
lists there will be 5 rows, now, say the user requires the values to be
normalised so they are displayed on just one line entry. We have two
choices, drag the 5 rows down to the front end or middle tier and use a 4GL
to process the data or we can use some of the extensions available in SQL
Server to do this.
Example
create table mailing_list (
individual_name nvarchar(100) not null,
list_name nvarchar(10) not null
)
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
C' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
A' )
select distinct
individual_name,
list = substring(
( select ', ' + list_name as [text()]
from mailing_list m2
where m2.individual_name = m1.individual_name
for xml path(''), elements )
, 3, 100 )
from mailing_list m1
Gives this result :-
alex r List A
joe r List A, List B
tony r List A, List B, List C
Now, just how easy was that! It only takes a few lines of SQL and you have
also saved a lot of network traffic back out to the middle tier or front
end.
So, my point is this: whatever you do - always think through what you are
doing, don't just follow 'rules' blindly!
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150411335.797938.9320@.f6g2000cwb.googlegroups.com...
> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
> You are the kid in the class that the other students make fun of
> because he just does not understand ...
>|||slimla wrote:
> Hi,
> Currently, I have a numeric field stored a value of 1000, how can I covert
> it to varchar value and display it as $1,000.00
> Thanks
you can also set up a compute column to have string instead of integer.
select '$' + cast(<int value> as varchar(20))|||Tony,
Very well said!
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam|||Thanks All
"P. Ward" wrote:
> The following example illustrates how this can be achieved:
> CREATE TABLE dbo.currency
> (
> col1 INT
> )
> INSERT dbo.currency SELECT 1000
> INSERT dbo.currency SELECT 10
> INSERT dbo.currency SELECT 999999
> SELECT '$' + CONVERT(VARCHAR(20), CAST(col1 AS MONEY), 1)
> FROM dbo.currency
> HTH
> - Peter Ward
> WARDY IT Solutions
>
> "slimla" wrote:
>|||I like that method, sort of gives you a formatting tier within the data
tier - nice and central and it doesn't effect storage, gives you a standard
view for people to use and code against... A lot more easier and
maintainable than using views as well...
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"BurgerKING" <syi916@.gmail.com> wrote in message
news:1150461726.914638.202330@.i40g2000cwc.googlegroups.com...
> slimla wrote:
>
> you can also set up a compute column to have string instead of integer.
> select '$' + cast(<int value> as varchar(20))
>
Friday, February 24, 2012
Format Currency Problem - HELP!
I need to display currency fields with a dollar sign, commas and no
decimal places. I entered
=FormatCurreny(fields!nameoffield.value,0,-1,-1)in the textbox
property's custom edit expressions box.
It drops the decimal places and adds the dollar sign just fine but,
I need the commas as well. What do I do?
I would apprectiate help here.
Thanks,
GailIf your Regional Settings in Control Panel is US, then go to the Format
property of the field in the Properties sheet and type in C0. (letter "C"
followed by a zero).
If your Regional Settings is not US, then go to the Language property of the
textbox, select English (United States) from the listbox, then in the Format
property type in C0.
Alternatively, you can just use an expression: =Format(FieldName, "$#,###").
HTH
Charles Kangai, MCT, MCDBA
"Gail Beedie" wrote:
> I need to display currency fields with a dollar sign, commas and no
> decimal places. I entered
> =FormatCurreny(fields!nameoffield.value,0,-1,-1)in the textbox
> property's custom edit expressions box.
> It drops the decimal places and adds the dollar sign just fine but,
> I need the commas as well. What do I do?
>
> I would apprectiate help here.
> Thanks,
> Gail
>|||YOU ARE MY HERO!!!!
GAIL
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message news:<B6BAD0E4-6010-4560-A59D-788C94A5640E@.microsoft.com>...
> If your Regional Settings in Control Panel is US, then go to the Format
> property of the field in the Properties sheet and type in C0. (letter "C"
> followed by a zero).
> If your Regional Settings is not US, then go to the Language property of the
> textbox, select English (United States) from the listbox, then in the Format
> property type in C0.
> Alternatively, you can just use an expression: =Format(FieldName, "$#,###").
> HTH
> Charles Kangai, MCT, MCDBA
>
> "Gail Beedie" wrote:
> > I need to display currency fields with a dollar sign, commas and no
> > decimal places. I entered
> > =FormatCurreny(fields!nameoffield.value,0,-1,-1)in the textbox
> > property's custom edit expressions box.
> >
> > It drops the decimal places and adds the dollar sign just fine but,
> > I need the commas as well. What do I do?
> >
> >
> > I would apprectiate help here.
> >
> > Thanks,
> > Gail
> >|||And mine as well. I had a different issue, but this clued me into what my
problem was. Thanks
"Gail Beedie" wrote:
> YOU ARE MY HERO!!!!
> GAIL
>
> "Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message news:<B6BAD0E4-6010-4560-A59D-788C94A5640E@.microsoft.com>...
> > If your Regional Settings in Control Panel is US, then go to the Format
> > property of the field in the Properties sheet and type in C0. (letter "C"
> > followed by a zero).
> >
> > If your Regional Settings is not US, then go to the Language property of the
> > textbox, select English (United States) from the listbox, then in the Format
> > property type in C0.
> >
> > Alternatively, you can just use an expression: =Format(FieldName, "$#,###").
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> >
> > "Gail Beedie" wrote:
> >
> > > I need to display currency fields with a dollar sign, commas and no
> > > decimal places. I entered
> > > =FormatCurreny(fields!nameoffield.value,0,-1,-1)in the textbox
> > > property's custom edit expressions box.
> > >
> > > It drops the decimal places and adds the dollar sign just fine but,
> > > I need the commas as well. What do I do?
> > >
> > >
> > > I would apprectiate help here.
> > >
> > > Thanks,
> > > Gail
> > >
>
decimal places. I entered
=FormatCurreny(fields!nameoffield.value,0,-1,-1)in the textbox
property's custom edit expressions box.
It drops the decimal places and adds the dollar sign just fine but,
I need the commas as well. What do I do?
I would apprectiate help here.
Thanks,
GailIf your Regional Settings in Control Panel is US, then go to the Format
property of the field in the Properties sheet and type in C0. (letter "C"
followed by a zero).
If your Regional Settings is not US, then go to the Language property of the
textbox, select English (United States) from the listbox, then in the Format
property type in C0.
Alternatively, you can just use an expression: =Format(FieldName, "$#,###").
HTH
Charles Kangai, MCT, MCDBA
"Gail Beedie" wrote:
> I need to display currency fields with a dollar sign, commas and no
> decimal places. I entered
> =FormatCurreny(fields!nameoffield.value,0,-1,-1)in the textbox
> property's custom edit expressions box.
> It drops the decimal places and adds the dollar sign just fine but,
> I need the commas as well. What do I do?
>
> I would apprectiate help here.
> Thanks,
> Gail
>|||YOU ARE MY HERO!!!!
GAIL
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message news:<B6BAD0E4-6010-4560-A59D-788C94A5640E@.microsoft.com>...
> If your Regional Settings in Control Panel is US, then go to the Format
> property of the field in the Properties sheet and type in C0. (letter "C"
> followed by a zero).
> If your Regional Settings is not US, then go to the Language property of the
> textbox, select English (United States) from the listbox, then in the Format
> property type in C0.
> Alternatively, you can just use an expression: =Format(FieldName, "$#,###").
> HTH
> Charles Kangai, MCT, MCDBA
>
> "Gail Beedie" wrote:
> > I need to display currency fields with a dollar sign, commas and no
> > decimal places. I entered
> > =FormatCurreny(fields!nameoffield.value,0,-1,-1)in the textbox
> > property's custom edit expressions box.
> >
> > It drops the decimal places and adds the dollar sign just fine but,
> > I need the commas as well. What do I do?
> >
> >
> > I would apprectiate help here.
> >
> > Thanks,
> > Gail
> >|||And mine as well. I had a different issue, but this clued me into what my
problem was. Thanks
"Gail Beedie" wrote:
> YOU ARE MY HERO!!!!
> GAIL
>
> "Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message news:<B6BAD0E4-6010-4560-A59D-788C94A5640E@.microsoft.com>...
> > If your Regional Settings in Control Panel is US, then go to the Format
> > property of the field in the Properties sheet and type in C0. (letter "C"
> > followed by a zero).
> >
> > If your Regional Settings is not US, then go to the Language property of the
> > textbox, select English (United States) from the listbox, then in the Format
> > property type in C0.
> >
> > Alternatively, you can just use an expression: =Format(FieldName, "$#,###").
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> >
> > "Gail Beedie" wrote:
> >
> > > I need to display currency fields with a dollar sign, commas and no
> > > decimal places. I entered
> > > =FormatCurreny(fields!nameoffield.value,0,-1,-1)in the textbox
> > > property's custom edit expressions box.
> > >
> > > It drops the decimal places and adds the dollar sign just fine but,
> > > I need the commas as well. What do I do?
> > >
> > >
> > > I would apprectiate help here.
> > >
> > > Thanks,
> > > Gail
> > >
>
Subscribe to:
Posts (Atom)