It surprises many developers that you can have a text value as a calculated measure, since a measure usually contains an aggregated number. However, a text value is very simple and very useful in a calculated measure. It could be as simple as getting a property of a member such as a customer's mobile number -Member Measures.MobileNumber as Customers.Customer.properties("Mobile Number")
With Member Measures.TopCity as
Topcount([Client Host].[Client Geography].[City]
,1,measures.[request count]).item(0).member_caption
Select
tail([Date].[Year Month Day].[Day],3) on Columns,
[HoursOfDay].[HoursOfDay].[Hour Of Day] on Rows
From EasternMining
Where Measures.TopCity
I have a real example, in a report that shows hours on rows and the last three days on columns, and for each cell it shows the top City (customer, product, whatever) by virtue of the number of web requests in that hour. This is shown on the default page of my web dashboard on http://EasternMining.com.au/Sites/Demonstrations See down near the bottom is a table called Top Cities by Hour - Last 3 Days. It shows the city with the most activity on my site for that hour. It might look ahead of time to you, if you are in America or Europe. It is actually very close to real-time in Sydney Australia.
Here is the mdx
With Member Measures.TopCity as
Topcount([Client Host].[Client Geography].[City]
,1,measures.[request count]).item(0).member_caption
Select
tail([Date].[Year Month Day].[Day],3) on Columns,
[HoursOfDay].[HoursOfDay].[Hour Of Day] on Rows
From EasternMining
Where Measures.TopCity
See how simple the mdx is. Again, imagine having to write the same query in SQL. Also, if I want to change something in the report, such as last 5 days, last 3 months etc, it is very simple. In fact, If I chage [City] to [Region] I get the following report. This report tends to show the time zones of Asia, Europe and Americas. See how Americas tend to be early morning (Sydney time) while Europe is late at night and Asia is visiting during the online day.
It's so very easy with MDX. If the answer is in the data, then one MDX expression should be able to ask the question. Let me know if you need any assistance in writing or learning mdx.
7 comments:
Hi Richard,
i am facing problem with having Text as measure. I have requirement wher I hvae GroupType and GroupName for a customer site. Each customer has defined GroupType. Now what result I am looking for is, Have GroupType in Column and CustomerSIte in Row and measure as GroupName. But i am not getting how to write MDX for this!
Can you please help me out in this!
Thanks,
Monica
Hi Monica,
I am sure this is possible. In fact, any question can be answered in MDX if the cube holds the information.
Here is a query I wrote to determine the Internet Explorer of my users, Region by Resource.
with member measures.Browser as
topcount(nonempty([Client Agent].[Client Agent],[Measures].[Bytes Total]),1)(0).member_caption
select non empty [Client Host].[Client Geography].[Region] on 0,
non empty filter([Target].[Resource Hierarchy].[Resource Type],[Measures].[Hits]>1000) on 1
from [EasternMining]
where measures.Browser
Naturally, the syntax of the query will differ depending on your cube structure, but you can do it.
Hi, Im following your advice but I cant do what i really need.
topcount(nonempty([StatusPlanes].[Status],[Measures].[Planes]),1)(0).member_caption this is my MDX calculate member. It doesnt work if I dont put the Status on my view (im using proclarity btw) any idea? and something else...how can you put colors depending on the text is trhowing... thank you so much!
adriana
Hi Adriana,
I think your problem might be that you haven't specified the member set correctly. In your calc you have [StatusPlanes].[Status] This should be a set of members. If StatusPlanes is your dimension, and Status is your attribute hierarchy, you will need to add a qualification for the member level. Your member level might also be Status, so the set would be StatusPlanes.Status.Status In fact this is actually shorthand, which written in full would be StatusPlanes.Status.Status.members. Sor your calc might look like topcount(nonempty([StatusPlanes].[Status].Status.members,[Measures].[Planes]),1)(0).member_caption
If that doesn't work try putting [StatusPlanes].[Status].Status.members on rows in a simple query and get that set expression working correctly, then plug it into your calc.
Perfect!! works like a charm !! I cant thank you enough for your help!!... Ive been searching for answers and you are the only person on google who has an entire article about this... so thank you again for sharing the knowledge... now I have to figure it out how to put some colors like your example and thats it... =) Muchas Gracias
What is the Set up in Bids to accomplish something like this. It appears that your using a property of a previously defined dimension. What if you want this as a permanent measure within BIDS what is the method to accomplish this?
Hi Krkolk,
I think there is an example in the blog.
Member Measures.MobileNumber as Customers.Customer.properties("Mobile Number")
So all you need to do is create a calculated measure in your cube design that has a result that is a string. An even simpler example that will work with any cube is
Member Measures.userid as username()
Hope that helps,
Richard
Post a Comment