Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

mdx query

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
romeiovasu
Asked:
romeiovasu
  • 2
  • 2
1 Solution
 
srnarCommented:
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
 
romeiovasuAuthor Commented:
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
 
srnarCommented:
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
 
romeiovasuAuthor Commented:
thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now