When you right click on the value there is a list of options which appear, in that choose: Show properties in Screen Tips, in that option choose Show all properties in the Report. When the user hovers the mouse over the values, the properties will be displayed as Tool Tips. This is really a neat feature which can be used to build/search on member properties.
Wednesday, November 4, 2009
SSAS 2008 - Member Properties
In Microsoft SQL Server 2000 Analysis Services there used to be a feature called Member properties. In SSAS 2005 and SSAS 2008 it can be achieved through a different strategy. I was learning the important aspects of user defined hierarchies and attribute relationships in SSAS 2005 and SSAS 2008 through the videos available at LearnmicrosftBI. While working with attirbute relationships, there were two properties of attributes which were discussed. In BIDS 2005/2008 for each attribute in a dimension there are properties called AttributeHierachyEnabled and AttributeHierarchyVisible. In order to achieve the member properties type effect, the AttributeHierarchyEnabled should be to False for a particular attribute. Once this is done, the dimensions and cube need to processed. In the Browser tool available in BIDS, drag over the measures and then drag over the lowest member of the dimension as values to be filtered by.
Subscribe to:
Post Comments (Atom)
I have a problem with this that hopefully someone can help with.
ReplyDeleteWe have need to be able to drill down to the individual fact lines to see all data, but we dont want to use the Drill through Action as it doesnt let you name the columns properly, we want to use the attribute names. To get around this we have created a Hierachy that at the bottom level lets you see all the attributes on the fact detail line, this is done with a Factbased dimension joined to every other dimension, annoying but that's the only way i could get it to work. So the problem is in SSRS if i set the attribute hierachy = false for say "Tracking Number" as i dont want users to do anaysis by that attribute I cant select it in SSRS query browser. However in MS Excel I can go to OLAP properties and add it as a column. Question is how can I get the SSRS report to add the "Tracking Number" as a column if set to false I cant add it as a column for my report?
Yes you can add the Property as Column.
ReplyDeleteTo do so add a calculated field to your Dataset.
The Expression for this column add:
=Fields!ColumnOfWhichTrackingNumberIsProperty.Properties("Tracking Number")
Further on you have to tell your Dataset to select Member Properties. To do so add:
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, [MyDimension].[MyHierarchy].[MyAttribute].[TrackingNumber] before ON ROWS.
For Instance: I Have a product hierarchy as follows: Category->Subcategory->Product No
For each Attribute in this Hierarchy i have a Caption-Property with AttributeHierarchyEnabled = False
so to get the Data in the Dataset My Dataset is as follows:
SELECT [Measures].[MyMeasures] ON COLUMNS,
[DimProduct].[MyHierarchy].[ProductNo].CHILDREN DIMENSION PROPERTIES MEMBER_CAPTION,MEMBER_UNIQUE_NAME,[DimProduct].[MyHierarchy].[Category].[CategoryCaption],[DimProduct].[MyHierarchy].[SubCategory].[SubCategoryCaption],[DimProduct].[MyHierarchy].[Product No].[ProductCaption] ON ROWS
FROM [MyCube]
So i basically get 4 columns in my Dataset:
MyMeasures
Category,SubCategory,ProductNo
Now i can add 3 calculated Fields as follows:
a)=Fields!Category.Properties("CategoryCaption")
b)=Fields!SubCategory.Properties("SubCategoryCaption")
c) = Fields!ProductNo.Properties("ProductCaption")
Hope this helps