none
Format number with Thousands separator? 10000 --> 10,000

    Pregunta

  •  

    I would like to select a BIGINT type and get a formatted result with commas.  Anyone have ideas?

     

    declare @i bigint

    set @i = 123456789

     

    select @i

     

    --Would like to get

    123,456,789

    miércoles, 24 de octubre de 2007 20:34

Respuestas

  • Hi there,

     

    This URL will answer your question. The author has an excellent book about User Defined Function.

    http://novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-48-formatnumber.htm

     

    Hope this helps,

    Gonzalo

    • Propuesto como respuesta SAinCA martes, 01 de mayo de 2012 18:19
    • Marcado como respuesta Kalman TothModerator lunes, 07 de mayo de 2012 20:03
    miércoles, 24 de octubre de 2007 21:13
  • This type of formatting should only be done in the GUI.  No one would ask for this in a file transfer or any type of feed.  SQL Server isn't the place to do this type of formatting even though it can be done.
    --Jeff Moden
    lunes, 29 de diciembre de 2008 0:32
  •  

    And  the winner is:

     

     

    DECLARE @MyValue bigint

    SET @MyValue = 123456789

     

    select replace(convert(varchar,convert(Money, @MyValue),1),'.00','')

    jueves, 25 de octubre de 2007 0:08
  • This is a bit 'unwieldy', but it seems to work.

    (You could put it in a User Defined Function.)

     


    DECLARE @MyValue bigint
    SET @MyValue = 123456789156789

     

    SELECT replace( convert( varchar(32), cast( '$' + cast( @MyValue AS varchar(32) ) AS money ), 1 ), '.00', '' )

     

    --------------------------------------
    123,456,789,156,789

     

    jueves, 25 de octubre de 2007 0:01
    Moderador
  • SQLUSA said:

    gvee said:

    This is a formatting issue and should be handled in the presentation layer where the appropriate formatting functions will no doubt exist and will be hundreds of times more efficient.

    I would not take such a hard-liner position on this issue.

    While in principle I agree that the formatting should be done by the presentation layer such as Reporting Services reports, there are plenty of exceptions when, for one reason or another,  that solution is not available.

    Assume you are the Senior DBA (database designer, developer) at company cDelta and the CFO request a revenue ad-hoc report to be emailed to him/her ASAP.  Your presentation layer is the email body. In such a case I would use the

    SELECT ...., Revenue='$'+convert(varchar, RevenueCol, 1), ....

    money to varchar conversion to make the report look presentable.



    Kalman Toth, SQL Server 2005 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com



    Heh... you wouldn't be the Senior DBA for long because most CFO's are gonna want it in a spreadsheet.  That would be the GUI where you do the formatting.  And if you send it as a formatted text file with those bloody commas in it, you just know there's gonna be heck to pay when (s)he tries to import it into a spreadsheet.  CFO's don't want text files.
    --Jeff Moden
    martes, 30 de diciembre de 2008 3:00
  • I've only ever recieved the request to have the data in an email once, which was promptly followed up by a phone call asking for it in a format they could copy into aspreadsheet ;)

    But no, I completely appreciate the point you're making Kalman, but if you've got enough time to post the question to the interwebz and wait for a response, you have enough time to run a straight forward query in QA and copy the results into Excel, and format it in there! ;)

    There are exceptions to every rule, but this is not one of them.


    George
    martes, 30 de diciembre de 2008 9:53
    Usuario que responde
  • Remember the first rule of any tiered architecture?  Display formatting is always done in the front end and never in the database. What you are tryingto do is write 1950's COBOL in SQL; it was very good for this kind of thing. 
    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    viernes, 07 de enero de 2011 18:49
  • The following articles are on similar topic:

    http://www.sqlusa.com/bestpractices2005/moneyformat/

    http://www.sqlusa.com/bestpractices2005/padleadingzeros/

    UPDATE: SQL Server 2012 has introduced the FORMAT() function, borrowed from .NET languages. It does date, currency, number and percent formatting.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    jueves, 29 de diciembre de 2011 15:46
    Moderador
  • ABSOLUTELY AGREE, KALMAN.

    When I see absolutes in threads like this, my first thought is "blinkers", ably demonstrated above.

    Very sad.

    I also need to emit formatted decimals in SMS messages.  No scope for a spreadsheet there (see post +2 below).

    Sometimes our defense of a "position" leads to entrenchment beyond reason/reasonableness.

    I wouldn't last in my DBA/Developer role if I didn't emit the emails/SMS messages to our clients, conveying CRUCIAL operating data, in a well-formatted manner!

    martes, 01 de mayo de 2012 17:13
  • Just in case anyone thought my last post was meant to be sarcastic, it wasn't. ;-)  I was in a hurry.

    Looking back on this years-old thread, I've changed a fair bit on this subject over the last few years.  While I agree that formatting should be done in an application if there is one, I don't always agree that you need an application just to do some formatting.  And it's really not that hard to do some rather nice HTML formatting so you can quickly get emails out without having to have externally managed code and the delays associated with running the likes of SSRS for such simple things.  The really cool part is that Excel likes to open such attachments as formatted spreadsheets.

    Yep... compared to some of my older posts on this thread, I sound like a hypocrite and I apologize for that.  Let's just say that a bit of experience with needing to get "pretty" emails out in a hurry without turning it into a mini-reporting project has been a strong driving force and have changed my previous hard stance on the subject.  As with all else in this wonderful world of IT, "It Depends". ;-)


    --Jeff Moden

    miércoles, 02 de mayo de 2012 12:30

Todas las respuestas

  • Hi

     

    There is no build-in fuctionality available.

     

    If you do not need so often write a user defined function.

     

    If you need better performance, write a CLR function.

     

    Regards

    Flo

    miércoles, 24 de octubre de 2007 20:37
  • Hi there,

     

    This URL will answer your question. The author has an excellent book about User Defined Function.

    http://novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-48-formatnumber.htm

     

    Hope this helps,

    Gonzalo

    • Propuesto como respuesta SAinCA martes, 01 de mayo de 2012 18:19
    • Marcado como respuesta Kalman TothModerator lunes, 07 de mayo de 2012 20:03
    miércoles, 24 de octubre de 2007 21:13
  • This is a bit 'unwieldy', but it seems to work.

    (You could put it in a User Defined Function.)

     


    DECLARE @MyValue bigint
    SET @MyValue = 123456789156789

     

    SELECT replace( convert( varchar(32), cast( '$' + cast( @MyValue AS varchar(32) ) AS money ), 1 ), '.00', '' )

     

    --------------------------------------
    123,456,789,156,789

     

    jueves, 25 de octubre de 2007 0:01
    Moderador
  •  

    And  the winner is:

     

     

    DECLARE @MyValue bigint

    SET @MyValue = 123456789

     

    select replace(convert(varchar,convert(Money, @MyValue),1),'.00','')

    jueves, 25 de octubre de 2007 0:08
  • This type of formatting should only be done in the GUI.  No one would ask for this in a file transfer or any type of feed.  SQL Server isn't the place to do this type of formatting even though it can be done.
    --Jeff Moden
    lunes, 29 de diciembre de 2008 0:32
  • Jeff Moden said:

    This type of formatting should only be done in the GUI.  No one would ask for this in a file transfer or any type of feed.  SQL Server isn't the place to do this type of formatting even though it can be done.


    --Jeff Moden



    100% agreed. This is a formatting issue and should be handled in the presentation layer where the appropriate formatting functions will no doubt exist and will be hundreds of times more efficient.
    George
    lunes, 29 de diciembre de 2008 10:34
    Usuario que responde
  • gvee said:

    This is a formatting issue and should be handled in the presentation layer where the appropriate formatting functions will no doubt exist and will be hundreds of times more efficient.

    I would not take such a hard-liner position on this issue.

    While in principle I agree that the formatting should be done by the presentation layer such as Reporting Services reports, there are plenty of exceptions when, for one reason or another,  that solution is not available.

    Assume you are the Senior DBA (database designer, developer) at company cDelta and the CFO request a revenue ad-hoc report to be emailed to him/her ASAP.  Your presentation layer is the email body. In such a case I would use the

    SELECT ...., Revenue='$'+convert(varchar, RevenueCol, 1), ....

    money to varchar conversion to make the report look presentable.



    Kalman Toth, SQL Server 2005 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    lunes, 29 de diciembre de 2008 17:03
    Moderador
  • Perhaps use SSRS report, with Currency format (or use Excel and currency/money format)

    I agree that SQL should not be used for presentation, if possible.

    Jerry Hung
    lunes, 29 de diciembre de 2008 19:15
  • SQLUSA said:

    gvee said:

    This is a formatting issue and should be handled in the presentation layer where the appropriate formatting functions will no doubt exist and will be hundreds of times more efficient.

    I would not take such a hard-liner position on this issue.

    While in principle I agree that the formatting should be done by the presentation layer such as Reporting Services reports, there are plenty of exceptions when, for one reason or another,  that solution is not available.

    Assume you are the Senior DBA (database designer, developer) at company cDelta and the CFO request a revenue ad-hoc report to be emailed to him/her ASAP.  Your presentation layer is the email body. In such a case I would use the

    SELECT ...., Revenue='$'+convert(varchar, RevenueCol, 1), ....

    money to varchar conversion to make the report look presentable.



    Kalman Toth, SQL Server 2005 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com



    Heh... you wouldn't be the Senior DBA for long because most CFO's are gonna want it in a spreadsheet.  That would be the GUI where you do the formatting.  And if you send it as a formatted text file with those bloody commas in it, you just know there's gonna be heck to pay when (s)he tries to import it into a spreadsheet.  CFO's don't want text files.
    --Jeff Moden
    martes, 30 de diciembre de 2008 3:00
  • Jeff Moden said:

    you wouldn't be the Senior DBA for long because most CFO's are gonna want it in a spreadsheet.  That would be the GUI where you do the formatting.  n


    Good pick Jeff, however, you missed the point. Yes, first time in his life the CFO did not want a spreadsheet attachment to the email....

    Indeed Excel is the favorite presentation layer for ad-hoc reports prepared by senior DBAs/Developers.

    I am still defending the

    '$'+CONVERT(varchar, MoneyCol, 1) 

    currency conversion for one thing it is in the T-SQL language, so we cannot just ignore it.

    I myself use it regularly when publishing results to the web.  I just don't like to publish currency data not formatted properly.  Example:

    SELECT TOP(7)   
            ProductName=Name,   
            unformattedCost = StandardCost,  
            formattedCost = '$'+CONVERT(varchar,StandardCost,1),  
            Color  
    FROM AdventureWorks2008.Production.Product  
    WHERE Color is not null 
    ORDER BY ListPrice desc, ProductName 


    Results:

    ProductName                   unformattedCost   formattedCost     Color

    Road-150 Red, 44              2171.2942         $2,171.29         Red

    Road-150 Red, 48              2171.2942         $2,171.29         Red

    Road-150 Red, 52              2171.2942         $2,171.29         Red

    Road-150 Red, 56              2171.2942         $2,171.29         Red

    Road-150 Red, 62              2171.2942         $2,171.29         Red

    Mountain-100 Silver, 38       1912.1544         $1,912.15         Silver

    Mountain-100 Silver, 42       1912.1544         $1,912.15         Silver



    I am also agreeing with everybody above who stated that formatting belongs to the presentation layer such as SSRS reports, crystal reports or even Excel worksheets.

    Kalman Toth, SQL Server 2005 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    martes, 30 de diciembre de 2008 5:54
    Moderador
  • I've only ever recieved the request to have the data in an email once, which was promptly followed up by a phone call asking for it in a format they could copy into aspreadsheet ;)

    But no, I completely appreciate the point you're making Kalman, but if you've got enough time to post the question to the interwebz and wait for a response, you have enough time to run a straight forward query in QA and copy the results into Excel, and format it in there! ;)

    There are exceptions to every rule, but this is not one of them.


    George
    martes, 30 de diciembre de 2008 9:53
    Usuario que responde
  • gvee said:

    , you have enough time to run a straight forward query in QA and copy the results into Excel, and format it in there! ;)


    I am afraid George Excel does not work for me when publishing to the web and trying to stay close to Management Studio results display.  See the example below. I just copy and paste a simple datatime value and see what happens:

    select GETDATE()
    /*

    Result window in Management Studio:
    2008-12-30 18:51:37.653

    Excel 2007
    51:37.7

    */

    Here is SQL Server 2008 code to format a positive BIGINT number with thousand-separators:

    DECLARE @BIGINT bigint = 9223372036854775807  
    DECLARE @strBIGINT varchar(32)=CONVERT(varchar(32), @BIGINT)  
    DECLARE @i tinyint = LEN(@strBIGINT) % 3 + 1  
    IF @i = 1 SET @i = 4  
    WHILE ( @i <= LEN(@strBIGINT))  
    BEGIN 
          SET @strBIGINT = STUFF(@strBIGINT, @i, 0, ',')  
          SET @i += 4  
    END 
    SELECT [FormattedBIGINT]= @strBIGINT 


    Kalman Toth, SQL Server 2005 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    martes, 30 de diciembre de 2008 23:57
    Moderador
  • Then you right click the column header > format > date/time :D
    It was more advice to the OP to make sure they had considered this.

    I don't feel either of us has to enter a debate to justify our suggestions, because they are both right! ;)
    George
    miércoles, 31 de diciembre de 2008 9:15
    Usuario que responde
  • Hi

    I've just encountered a similar problem myself, and thought it might be useful to share my solution.  Before everyone jumps on me, I know best practice is to format in the presentation layer, but in my (rather odd) case, this was not feasible - without boring you with details, I needed to use a DataGrid on the fly in c#, where the DataTables had relationships, so that the user could drill down through the hierarchy, with the effect that the column count varied and formatting became a huge pain!

    My solution was a CLR function.  Because it took me some time to find out how to do this, I give the code below:

    using

     

    System.Collections;

    using

     

    System.Data.SqlTypes;

    public

     

    class SQLUtils

    {

     

    public static SqlString FN(SqlDouble num, SqlInt32 decPlaces)

    {

     

    double d = (double)num;

     

    string format = "#,##0";

     

    if (decPlaces > 0)

    {

    format +=

    ".";

     

    for (int i = 0; i < decPlaces; i++)

    {

    format +=

    "0";

    }

    }

     

    SqlString formattedNumber = (SqlString)d.ToString(format);

     

    return formattedNumber;

    }

     

    public static SqlString FN2(SqlDouble num, SqlInt32 decPlaces, SqlInt32 extraDecPlaces)

    {

     

    double d = (double)num;

     

    string format = "#,##0";

     

    bool noDec = true;

     

    if (decPlaces > 0)

    {

    format +=

    ".";

    noDec =

    false;

     

    for (int i = 0; i < decPlaces; i++)

    {

    format +=

    "0";

    }

    }

     

    if (extraDecPlaces > 0)

    {

     

    if (noDec)

    {

    format +=

    ".";

    }

     

    for (int i = 0; i < extraDecPlaces; i++)

    {

    format +=

    "#";

    }

    }

     

    SqlString formattedNumber = (SqlString)d.ToString(format);

     

    return formattedNumber;

    }

    }

    Then add the assembly (Right click Programmability/Assemblies and follow the wizard).

    If not set, you must call sp_configure 'clr enabled', 1 followed by reconfigure.

    Finally:

    CREATE

     

    FUNCTION [dbo].[FN](@num [float], @decPlaces [int])

    RETURNS

     

    [nvarchar](4000) WITH EXECUTE AS CALLER

    AS

     

    EXTERNAL

     

    NAME [SQLHelper].[SQLUtils].[FN]

    and

    CREATE

     

    FUNCTION [dbo].[FN2](@num [float], @decPlaces [int], @extraDecPlaces [int])

    RETURNS

     

    [nvarchar](4000) WITH EXECUTE AS CALLER

    AS

     

    EXTERNAL

     

    NAME [SQLHelper].[SQLUtils].[FN2]

    Now any float that needs to be formatted can be called SELECT dbo.FN(myfloat, 2) FROM myTable

    NB the above creates scalar-valued functions, which need to be called with at least a two part name.

     

     

    viernes, 07 de enero de 2011 16:40
  • Remember the first rule of any tiered architecture?  Display formatting is always done in the front end and never in the database. What you are tryingto do is write 1950's COBOL in SQL; it was very good for this kind of thing. 
    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    viernes, 07 de enero de 2011 18:49
  • Thanks for your code.It's working.

    Regards,

    Karthick S.

    jueves, 29 de diciembre de 2011 14:41
  • This is a bit 'unwieldy', but it seems to work.

    (You could put it in a User Defined Function.)

     


    DECLARE @MyValue bigint
    SET @MyValue = 123456789156789

     

    SELECT replace( convert( varchar(32), cast( '$' + cast( @MyValue AS varchar(32) ) AS money ), 1 ), '.00', '' )

     

    --------------------------------------
    123,456,789,156,789

      


    Thanks its working

     

    Regards,

    Karthick S.

    jueves, 29 de diciembre de 2011 14:41
  • The following articles are on similar topic:

    http://www.sqlusa.com/bestpractices2005/moneyformat/

    http://www.sqlusa.com/bestpractices2005/padleadingzeros/

    UPDATE: SQL Server 2012 has introduced the FORMAT() function, borrowed from .NET languages. It does date, currency, number and percent formatting.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    jueves, 29 de diciembre de 2011 15:46
    Moderador
  • And if one HAS to emit email via DBMail one is left with, oh, let's think - T-SQL!  

    "Display formatting s MOSTLY done in the front end and SOMETIMES in the database."  Thank goodness we have the ability in T-SQL!

    The first RULE is sometimes BREAKABLE.  I need to show decimal(18,4) numbers in an email emitted from a very specialized SP and the CONVERT(...CAST(x AS money),1) won't cut it because it chops off 2 of my significant decimals.  A UDF or a CLR function WILL break the rule of necessity.

    (I coded COBOL for a decade and more, and emitted 3270 screens with it - very good at it was jolly old COBOL on an IBM mainframe!  One just had to think outside the box...).

    martes, 01 de mayo de 2012 17:07
  • ABSOLUTELY AGREE, KALMAN.

    When I see absolutes in threads like this, my first thought is "blinkers", ably demonstrated above.

    Very sad.

    I also need to emit formatted decimals in SMS messages.  No scope for a spreadsheet there (see post +2 below).

    Sometimes our defense of a "position" leads to entrenchment beyond reason/reasonableness.

    I wouldn't last in my DBA/Developer role if I didn't emit the emails/SMS messages to our clients, conveying CRUCIAL operating data, in a well-formatted manner!

    martes, 01 de mayo de 2012 17:13
  • For those of us tied to the DB for our formatting (SMS/email), the UDF above is excellent and very flexible and will go into Production today!

    Thanks to Steve Strong and Andrew Novick for their vintage 2003 UDF.

    martes, 01 de mayo de 2012 18:21
  • For those of us tied to the DB for our formatting (SMS/email), the UDF above is excellent and very flexible and will go into Production today!

    Thanks to Steve Strong and Andrew Novick for their vintage 2003 UDF.


    Don't forget to decimal align it within a max column width setting for maximum beautification. ;-)

    --Jeff Moden

    martes, 01 de mayo de 2012 23:05
  • Did that, too - wrote a custom version of a UDF I found that pads left/right/center/ends to a required length with a single nchar() that is either a regular space for text emails or an &nbsp; for HTML.

    After boiling down the HTML to the simplest form that Notes, Gmail, Hotmail and Outlook will render decently, it may be repetitive-HTML but it looks "beautiful", especially compared to the pug-ugly text version we still have to emit for very-old-Lotus-Notes consumption.

    Painful in T-SQL but FOR XML came in handy...

    Production launch 10 minutes ago :-)


    • Editado SAinCA miércoles, 02 de mayo de 2012 0:38
    miércoles, 02 de mayo de 2012 0:38
  • Just in case anyone thought my last post was meant to be sarcastic, it wasn't. ;-)  I was in a hurry.

    Looking back on this years-old thread, I've changed a fair bit on this subject over the last few years.  While I agree that formatting should be done in an application if there is one, I don't always agree that you need an application just to do some formatting.  And it's really not that hard to do some rather nice HTML formatting so you can quickly get emails out without having to have externally managed code and the delays associated with running the likes of SSRS for such simple things.  The really cool part is that Excel likes to open such attachments as formatted spreadsheets.

    Yep... compared to some of my older posts on this thread, I sound like a hypocrite and I apologize for that.  Let's just say that a bit of experience with needing to get "pretty" emails out in a hurry without turning it into a mini-reporting project has been a strong driving force and have changed my previous hard stance on the subject.  As with all else in this wonderful world of IT, "It Depends". ;-)


    --Jeff Moden

    miércoles, 02 de mayo de 2012 12:30
  • Thanks, Jeff.  I didn't take it for sarcasm - my father was a master at sarcasm, not the insulting kind, the pointed humor kind (sorely miss him!)

    Fully agree with your observations and conclusion.  Having near-beginner C# skills but a ton of T-SQL and being the only developer on staff, one has to use the tools available.  I like the way Microsoft has left us with so many opportunities for ingenuity - and that IS a tongue in cheek comment :=)

    "It Depends" indeed...

    Best regards,

    Stephen

    miércoles, 02 de mayo de 2012 16:52
  • SQLUSA said:

    gvee said:

    This is a formatting issue and should be handled in the presentation layer where the appropriate formatting functions will no doubt exist and will be hundreds of times more efficient.

    I would not take such a hard-liner position on this issue.

    While in principle I agree that the formatting should be done by the presentation layer such as Reporting Services reports, there are plenty of exceptions when, for one reason or another,  that solution is not available.

    Assume you are the Senior DBA (database designer, developer) at company cDelta and the CFO request a revenue ad-hoc report to be emailed to him/her ASAP.  Your presentation layer is the email body. In such a case I would use the

    SELECT ...., Revenue='$'+convert(varchar, RevenueCol, 1), ....

    money to varchar conversion to make the report look presentable.



    Kalman Toth, SQL Server 2005 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com



    Heh... you wouldn't be the Senior DBA for long because most CFO's are gonna want it in a spreadsheet.  That would be the GUI where you do the formatting.  And if you send it as a formatted text file with those bloody commas in it, you just know there's gonna be heck to pay when (s)he tries to import it into a spreadsheet.  CFO's don't want text files.
    --Jeff Moden

    I am in that very situation. Our CFO request a report to be emailed to him periodically, because it's easier to watch it at his phone rather than an spreadsheet. But I'm not sending plain text, I'm sending an HTML table. I agree that formatting is better to be done in the presentation layer, but it's is not always possible.
    lunes, 01 de octubre de 2012 21:38
  • But I'm not sending plain text, I'm sending an HTML table. I agree that formatting is better to be done in the presentation layer, but it's is not always possible.

    The MS SQL Server development team is on your side. SQL Server 2012 has the new FORMAT() function borrowed from .NET languages. Now you can do currency formatting the proper way:

    SELECT TopPrice = FORMAT(max(ListPrice),'c0', 'en-US')
    FROM AdventureWorks2012.Production.Product;
    -- $3,578


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    lunes, 01 de octubre de 2012 22:23
    Moderador
  • There is no one right or wrong answer to where to do formatting, you have to take it on a case by case basis.

    My Date Dimension has both numbers and equivalent formatted strings stored directly in the relational table. This is a best practice.

    I arrived here on this thread because of this one: Dimension Name Column Format Property SSAS 2005

    Apparently someone needs to tell Microsoft where to do formatting because this was their response on that thread:

    The "Format" string for Attribute names is a stub for a later addon and is not implemented.  Attribute names will only accept WChar types.  Any formatting should be done either in the data source view as a "Named Calculation" or in the source table/view on the relational source.

    You do formatting where it makes the most sense to do it. You need to consider a myriad of conditions like storage, performance, consistency, ease-of-use, maintenance, extensibility, etc. We like to say format is a user concern and belongs in the presentation tier, and most times that may be true, but there are many, many cases where formatting elsewhere is either desirable or even required.

    viernes, 18 de enero de 2013 16:09
  • Can you make a suggestion at Connect and post the URL here? Thanks.

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    viernes, 18 de enero de 2013 16:32
    Moderador