Solved

mdx query

Posted on 2008-10-15
4
155 Views
Last Modified: 2016-02-13
i have to write an mdx query. i have measure sales and target. i want to create other measure or kpi when sales is less than target then show the value also and show the color in red.
0
Comment
Question by:romeiovasu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 8

Expert Comment

by:srnar
ID: 22729627
Go to the sample SSAS application called Adventure Works. In the cube designer there is KPI tab where there are plenty of examples. You can look the attachment.
kpi.jpg
0
 

Author Comment

by:romeiovasu
ID: 22732594
sorry actually i was try to create in mdx itself. for example
case when [measures].[variance] > 0 then bgcolor green,
      when [measures].[variance] = 0 then orange
else -1
end

how do i get background or kpi over here i am using proclarity as a front end.
0
 
LVL 8

Accepted Solution

by:
srnar earned 500 total points
ID: 22734867
Ok then look into the calculation of the [Scenario].[Scenario].[Budget Variance %]. There is a section back_color that sets directly value of a cell. Plug this Mdx into you Proclerity desktop and don't forget to check the grid properties/grid formating: use cube cell properties formating. You will se the value of the measure with red bgcolor.

SELECT NON EMPTY { [Scenario].[Scenario].&[1], [Scenario].[Scenario].&[2], [Scenario].[Scenario].&[3], [Scenario].[Scenario].[Budget Variance], [Scenario].[Scenario].[Budget Variance %] } ON COLUMNS ,
 
NON EMPTY { NONEMPTYCROSSJOIN( { [Account].[Account Type].&[], [Account].[Account Type].&[Assets], [Account].[Account Type].&[Balances], [Account].[Account Type].&[Expenditures], [Account].[Account Type].&[Flow], [Account].[Account Type].&[Liabilities], [Account].[Account Type].&[Revenue], [Account].[Account Type].&[Statistical] }, { [Account].[Account Number].[All Accounts].CHILDREN }, { [Measures].[Amount] }, { [Scenario].[Scenario].&[1], [Scenario].[Scenario].&[2], [Scenario].[Scenario].&[3], [Scenario].[Scenario].[Budget Variance], [Scenario].[Scenario].[Budget Variance %] }, 2 ) } ON ROWS  
 
FROM [Adventure Works] 
 
WHERE ( [Measures].[Amount] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORE_COLOR, BACK_COLOR, FONT_FLAGS 

Open in new window

0
 

Author Comment

by:romeiovasu
ID: 22735876
thanks
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question