Solved

mdx query

Posted on 2008-10-15
4
134 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
  • 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

770 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