none
Formatting attribute in dimension table

    Question

  • I have date and float in attribute in a dimension table. If deploy my cube and I try to create a report with RS, I can't format this data. The value seems to be a String so I have to do a CDate or a CDbl before formating it.
    I have no problem when I try to format my measures.

    Thanks for your help.
    Cheers,
    Monday, April 07, 2008 12:19 PM

All replies

  • It's a very simple sample cube.
    I have 1 attribute SalesDate, 1 SalesID.
    I've chosen "Date" for the type of my attribute in the Dimension Structure tab.


    Monday, April 07, 2008 1:06 PM
  • Yes it is.
    Monday, April 07, 2008 1:48 PM
  • I have the same problem with the attribute BirthDate of dimension employee of the sample cube Analysis Services Tutorial.
    I have to cast the field with CDate before formating the textbox.
    Monday, April 07, 2008 10:34 PM
  •  SkYneT54 wrote:

    I have to cast the field with CDate before formating the textbox.

     

    To do it in correct way you should create NameColumn (or even ValueColumn) in you dimension with proper formating and use that to display dates.

     

    Radim

    Tuesday, April 08, 2008 7:02 AM
  • Ok so it's not possible to define a date type for an attribute and let the user format the field in SSRS ?

     

    Sunday, April 13, 2008 9:56 PM
  • I know that it's possible to format fields with SSRS but I have to cast the attribute in date or double with CDate or CDec.
    I want to define this type in my cube to avoid that.
    Monday, April 14, 2008 7:40 AM
  •  SkYneT54 wrote:
    I know that it's possible to format fields with SSRS but I have to cast the attribute in date or double with CDate or CDec.
    I want to define this type in my cube to avoid that.

    Hi,

    in each attribute you can define three basic column-related properties that helps you to define your desired behavior:

    • KeyColumns - this is used to join dim and fact tables (for example date or surrogate int representing date)
    • NameColumn - user-friendly string name of the member (formatted date, format is provided by cube designer)
    • ValueColumns - Optional property that by default equals to KeyColumns(when name is empty) or to NameColumn when it is present. But you can overwrite it to supply strong typed value (like date) and some client tools (Excel) can use it, Excel is able to filter on that (last week, this month, etc.)

    Read more about these tree properties here:

    Defining and Configuring Dimension Attributes

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

     

    Here you have a short note about Excel and ValueColumn in Olap Design Best Practices for AS2005:

    http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/olapdbpssas2005.mspx

     

    And you can read my post about RS2005 and extended field attributes (it's more about keys, but same principle works for value as well):

    http://blog.vyvojar.cz/radim/archive/2008/04/08/Passing-MDX-parameters-in-Reporting-Services-reports.aspx

     

    Hope that helps,

    Radim

    Monday, April 14, 2008 10:48 AM
  • Thanks for your reply.
    The problem is that I can't set the DataType in the property NameColumn. It says that the datatype Int is not allowed for NameColumn and I have to choose wchar.

    Monday, April 21, 2008 9:32 AM
  • Hi,

     

    that's right, as I wrote above, NameColumn is string representation of value(you can't change it, just one type allowed). It must be a string, so you can provider here a column with proper formatting. If you want to use other type, put it in ValueColumn.

     

    Radim

    Monday, April 21, 2008 9:46 AM
  • Even if I create a valueColumn with Integer type, I have to convert the field with a CInt in SSRS.

    Monday, April 21, 2008 10:19 AM
  • I'm not sure, how you access the member value. Read my post about extended fields here:

    http://blog.vyvojar.cz/radim/archive/2008/04/08/Passing-MDX-parameters-in-Reporting-Services-reports.aspx

     

    But it still might be that you will have to say (cast the field) to proper datatype (you won't convert it, but you will inform RS about current type), because I don't know if RS will be able to detect the type of the value automatically.

     

    Radim

    Monday, April 21, 2008 10:32 AM
  • Obviously you're right.
    RS doesn't seem to be able to detect the type of the value from an AS datasource
    Monday, April 21, 2008 1:06 PM
  • Does someone can confirm that please ?
    Monday, April 28, 2008 11:45 AM
  • Monday, May 19, 2008 2:55 PM
  • Old post, but just in case someone else happens to trip across this.

    With MSAS and MSRS, you have to do a couple of extra steps to get the correct results:

    First, in your MDX query, make sure you're getting the correct CELL PROPERTIES you need. That includes VALUE and FORMAT_STRING. (Consider FONT_COLOR as well.)

    Next, for the report, in the General.Value you want to use:

    =Fields!myField.Value   (not .FormattedValue  !!!)

    and in Number.Format use:

    =Fields!myField.Properties("FORMAT_STRING")

    Consider adding to Font.Color:

    =Fields!myField.Color

    Now, MSRS picks up the formatting from the cube.

    Tip: if you want to create / use tool tips, use something like:

    ="The value is: " + Fields!myField.FormattedValue


    Dave Balsillie Memeplex, Inc.

    Friday, December 13, 2013 8:40 PM