Formatting the Y axis of chart

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

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 in a report viewer

Hi,

I have a dataset and am able to display the result in a table in a report viewer (VS 2005) but my problem is, instead of displaying the records in a row, I want to display the result columnwise.

Can I do this?

Another Question:

I have a textbox in which in I am displaying some text but the text is long and want the text to be both right and left aligned(Justify). How can I do this?

Please help me.

HI

1) For display in the columnwise u can change in the database itself using procedure.

2) For display in the right side u just apply Styles

|||

You may be able to change your SQL to return your results as you want to display them in a table. But without knowing your data, it's hard to say whether that would work. Depending on your data, you may want to use a Matrix instead of a table.

You can use Center to justify your text right and left, but the textbox still needs to be large enough to display the largest amount of text your passing to it. If you have the CanGrow property set to True, the textbox will grow (height-wise) and wrap the text to new row(s) as needed.

|||

You may left, right, or center justify text in RS 2005. Full justification is not supported in the text box.

|||

you can use a matrix report and tie the same to a dataset or variables from your stored procedure. this is sort of pivot table like in excel.

|||

Hi,

Thank you all for your time but still have some problem.

I can't change the procedure since the same procedure is being used by 3 different report.

My report should look like this:(I have 5 players(Fixed) in each race but the number of laps may differ for different race. Result to be displayed for one race at a time)

Player_Name_1 Lap1_Time Lap2_Time Lap3_Time ......

I don't have any idea about matrix report. Please tell me the steps to follow or give me some available resource to refer.

I was looking for full justification. thanks for the information that it is not supported in text box.

|||

Hi,

From your description, it seems that you are unable to modify the stored procedure since it has been on live, right?

If so, I suggest that you can retrieve the return from the procedure and save it in a data container such as a dataset. And then re-modify the table structure and save it into another datatable, so in this way, you can regard the datatable as the new datasource to be bind to your report. Another way is that you can create a new procedure which return the new structure of the data table if you want.

For your second question, you can create a new Business Intelligence Projects and choose a Report Server Project Wizard template, and in this way you can select a Matrix table instead of a tabular one.

Thanks.

FormatNumber >> Excel export?

Hi all

I want my measures to be exported(Excel) as numbers instead of text.
When i simply use them as is it does this correct but as soon
as I as the following number formating it no longer exports
correctley.

=FormatNumber(Fields!Example.Value,2,True,True,True)

The same apllies to whe I set the formatting in the text box properties to N

Can some please tell me how I can acheive this result when exporting

Thanks in advance

G

Apply formatting using N in the Format property, then in the value textbox, apply an explicit cast of the value. E.g.

=CDbl(Fields!Example.Value)

Bizzare, but I've found this to work for me.

|||

Thanks Adam

I came accros the same thing today when i was casting
the values returned from custom code. I just hope the same
principal will aplly to currency.

In my case I had to figure to use N0
as format type to get 1,345,456

When using the cdbl() to do this you cant use FormatNumber(,,,)
that was my snag.

Thanks again for your time

G

Format(Now(), "yyyymm") shows minutes instead of months?

Just ran into a strange error.
I want to see what year and month it is today, like 200411 for November,
2004. I've added a textbox to show this, and set the value to
="Month: " & Format(Now(), "yyyymmmm")
For some reason, this shows 200430, because the time is 09.30 on my
computer. I've tried with m, mm, mmm and mmmm, and it just shows the
minutes. Why?
I tried using VBA!Now() but then I get an error saying "VBA is not
declared". Where do I need to declare it?
Running VS.NET 2003 with the Report Designer installed. The Report Server is
Ent. edition, and it shows the same error when I upload the report.
Any help appreciated!
Kaisa M. LindahlTry "yyyyMMMM".
A really good page for all .NET format code is:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconformattingtypes.asp
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:ePUM8bjxEHA.1392@.tk2msftngp13.phx.gbl...
> Just ran into a strange error.
> I want to see what year and month it is today, like 200411 for November,
> 2004. I've added a textbox to show this, and set the value to
> ="Month: " & Format(Now(), "yyyymmmm")
> For some reason, this shows 200430, because the time is 09.30 on my
> computer. I've tried with m, mm, mmm and mmmm, and it just shows the
> minutes. Why?
> I tried using VBA!Now() but then I get an error saying "VBA is not
> declared". Where do I need to declare it?
> Running VS.NET 2003 with the Report Designer installed. The Report Server
is
> Ent. edition, and it shows the same error when I upload the report.
> Any help appreciated!
>
> Kaisa M. Lindahl
>|||USE Format(Now(), "yyyyMM")
"Kaisa M. Lindahl" wrote:
> Just ran into a strange error.
> I want to see what year and month it is today, like 200411 for November,
> 2004. I've added a textbox to show this, and set the value to
> ="Month: " & Format(Now(), "yyyymmmm")
> For some reason, this shows 200430, because the time is 09.30 on my
> computer. I've tried with m, mm, mmm and mmmm, and it just shows the
> minutes. Why?
> I tried using VBA!Now() but then I get an error saying "VBA is not
> declared". Where do I need to declare it?
> Running VS.NET 2003 with the Report Designer installed. The Report Server is
> Ent. edition, and it shows the same error when I upload the report.
> Any help appreciated!
>
> Kaisa M. Lindahl
>
>|||Using MM instead of mm|||Tried this, and it works.
Thanks to all who answered. And for the url too.
Kaisa
"David Hughes" <spamfree@.freespam.com> wrote in message
news:uFg6$NkxEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Try "yyyyMMMM".
> A really good page for all .NET format code is:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconformattingtypes.asp
>
> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> news:ePUM8bjxEHA.1392@.tk2msftngp13.phx.gbl...
> > Just ran into a strange error.
> > I want to see what year and month it is today, like 200411 for November,
> > 2004. I've added a textbox to show this, and set the value to
> > ="Month: " & Format(Now(), "yyyymmmm")
> >
> > For some reason, this shows 200430, because the time is 09.30 on my
> > computer. I've tried with m, mm, mmm and mmmm, and it just shows the
> > minutes. Why?
> >
> > I tried using VBA!Now() but then I get an error saying "VBA is not
> > declared". Where do I need to declare it?
> >
> > Running VS.NET 2003 with the Report Designer installed. The Report
Server
> is
> > Ent. edition, and it shows the same error when I upload the report.
> >
> > Any help appreciated!
> >
> >
> >
> > Kaisa M. Lindahl
> >
> >
>

format string for dutch language

Hi everyone,

I have some measure with the following format string : #,#0.00

Now in Reporting Services my value is 1,975,647,808.55 instead of 1.975.647.808,55

I know that I can't change the format string to #.#0,00

This gives bad results in reporting Services

Any idea or solution?

thanks!!

Filip

Hello. I do not have a complete solution. We are struggling with this problem in Sweden as well.

You can use this format string '### ### ### ###.##' in SSAS2005.

It will help with avoiding the problem that all numbers will be text when you export to Excel.

The downside is that you will have to accept a dot (.) as a decimal pointer.

I have found no other way to solve this,

Kind Regards

Thomas Ivarsson

Format output of FOR XML RAW

Hi! I use SELECT FOR XML RAW on MSSQL 2000.
I would like to be able to format the XML output so that instead of
having:
<row a="asd1"...>
<row a="asd2"...>
<row a="asd3"...>
I would get
<1 a="asd1"...>
<2 a="asd2"...>
<3 a="asd3"...>
or better yet be able to assign a specifc column value to replace
"row".
This is because I want to load the XML stream into LUA tables, and LUA
tables need unique indexes.
Thanks in advance.
Jeff Lambert
RAW mode in SQL2K generates elements with name 'row'. If you want to change
element name (custom name) you have to use EXPLICIT or AUTO (using aliases)
mode. In SQL 2005 you are able to give custom name with RAW clause.
/Patrick
"Jeff Lambert" <xradicalx@.gmail.com> wrote in message
news:f4f18e47.0410051032.ca9317d@.posting.google.co m...
> Hi! I use SELECT FOR XML RAW on MSSQL 2000.
> I would like to be able to format the XML output so that instead of
> having:
> <row a="asd1"...>
> <row a="asd2"...>
> <row a="asd3"...>
> I would get
> <1 a="asd1"...>
> <2 a="asd2"...>
> <3 a="asd3"...>
> or better yet be able to assign a specifc column value to replace
> "row".
> This is because I want to load the XML stream into LUA tables, and LUA
> tables need unique indexes.
> Thanks in advance.
> Jeff Lambert
|||Also note that <1 a="asd1"/> is not a well-formed XML element (names cannot
start with numbers).
If you want to generate something like <row1/><row2/> etc. you would need an
XSLT stylesheet that creates such elements and copies all the attributes
over.
What is LUA exactly?
Best regards
Michael
"Patrick Akerblom" <patrik.akerblom@.ontrax.se> wrote in message
news:%23m2N2x2qEHA.2900@.TK2MSFTNGP12.phx.gbl...
> RAW mode in SQL2K generates elements with name 'row'. If you want to
> change element name (custom name) you have to use EXPLICIT or AUTO (using
> aliases) mode. In SQL 2005 you are able to give custom name with RAW
> clause.
> /Patrick
> "Jeff Lambert" <xradicalx@.gmail.com> wrote in message
> news:f4f18e47.0410051032.ca9317d@.posting.google.co m...
>

Format numbers on chart y axis

Hi!
How can I format number on chart's y axis? I wanto to show 1.000 instead of 1000.

Thank you!

Set the format property on the y-axis tab of the chart properties dialog.

More information about chart label formatting is available in the following whitepaper: http://msdn.microsoft.com/library/en-us/dnsql90/html/MoreSSRSCharts.asp?frame=true#moressrscharts_topic3

-- Robert

Format Help

i'm trying to format integers to have ,s... like 1,000 instead of 1000.
??????You want commas in your database? Don't you only want the commas in your application (front-end)?|||Output formatting really ought to be done on the client side, for a number of reasons.

First and foremost, if you format the data at the server you create a logistical nightmare if/when you need to expand to support multiple locales. You'll have to write code everywhere you do formatting to determine how the data needs to be formatted for each case. This is an exercise in quick ways to create code that will drive your insane at some later date.

From a pure performance basis, it is a poor choice to format your data before you need to. The formatting will inhibit you from being able to do any additional processing. It is really hard to add a 7.5% VAT to "$1,000.00" in the United Kingdom, while it is trivial to add the VAT to the unformatted monetary value which will also format correctly when it reaches the client!

-PatP|||what i'm trying to do is write a select statement that would add the ,s in...

is that possible??|||Nearly anything is possible. I'm pretty sure that it isn't a good idea. I would very strongly advise that you do your formatting on the client side, using a tool such as Crystal Reports, MS-Access, VB, etc instead of formatting the data on the server.

-PatP

format datetime column

Hello,

I am wondering if someone could help me with formatting datatime column.

Goal - keep column in datatime instead of convert to varchar

Current - '2006-06-21 16:54:33.000'

Wants - '2006-06-21 16:54:33'

Any help is appreciated!

-Lawrence

Code Bits

declare @.time datetime

set @.time = '2006-06-21 16:54:33.000'

select @.time, convert(varchar(255), @.time, 20)

You usually do not want to format the output in the database. This would lead to the question why do you need to do this in T-SQL?

The front-end should be able to format the date to a proper format instead of relying on the database to do it. The database gives the data and the front-end presents it. Skipping the milliseconds are most likely the task of the front-end.

|||

I know what you are saying, but the reason is that the format from this t-sql goes into a transformation and oddly enough the transformation does not accept datetime format with millisecond. I thought SET DateFormat would be able to set any datetime format, but I guess that feature is very limited.

-Lawrence

|||declare @.time datetime
set @.time = '2006-06-21 16:54:33.000'
select @.time, convert(varchar(19), @.time, 121)|||What transformation are you referring to? Is it a DTS/SSIS package? If so that can format the data accordingly. Please elaborate on your problem. The datetime value is stored in native format on the server and if you want to format it as string you need to use CONVERT & other string functions to do it. Typically you will send the values as is to the client and format on the client side using richer mechanisms.