0

I have SSAS Tabular cube with a table that has a decimal price field. That cube is used as datasource for building SSRS reports. In one of those reports I need to make some calculations only over the facts in which price is higher than value given as a parameter. That parameters was created simply by drag-and-drop in Query Designer. But I don't need to search for records with exact price, I just need to set upper limit of the price.

Thus I started to look for MDX-related information to manually modify MDX query built in Query Designer. It seems like I should use Filter() function and pass parameter value into its logical expression. There are a lot of examples in the net with logical expression where measures are used. But when I try to use dimension member in logical expression (I tried Membervalues and Values functions) I got different errors. I understand that the issue is much more trickier like using T-SQL.

But how to solve it? Is it possible to filter out dimension table by numeric value?

Script as follows:

SELECT 
  NON EMPTY 
    {
      [Measures].[# состоявшихся аптечных визитов (с препаратами)]
    } ON COLUMNS
FROM 
(
  SELECT 
    StrToSet
    (@[ПродажиаптекЦена]
     ,CONSTRAINED
    ) ON COLUMNS
  FROM [Аптечные визиты]
)
WHERE 
  IIF
  (
    StrToSet(@[ПродажиаптекЦена],CONSTRAINED).Count = 1
   ,StrToSet
    (@[ПродажиаптекЦена]
     ,CONSTRAINED
    )
   ,[Продажи аптек].[Цена].CurrentMember
  )
CELL PROPERTIES 
  Value
 ,BACK_COLOR
 ,FORE_COLOR
 ,FORMATTED_VALUE
 ,FORMAT_STRING
 ,FONT_NAME
 ,FONT_SIZE
 ,FONT_FLAGS;
whytheq
  • 34,466
  • 65
  • 172
  • 267
erop
  • 1,510
  • 1
  • 14
  • 27
  • can you please the basic mdx script that is produced after dragging and dropping? – whytheq Apr 21 '16 at 19:15
  • Sorry @whytheq for misleading you and others. In fact I just placed 'price' field onto filter pane and checked it as a parameter. But actually I even didn't test it working since I immediately started thinking on how to use parameter built in Query Designer to filter out query result by its integer value. All my subsequent experiments I wrote above were made by hand in SSMS MDX query editor, not touching SSRS designer. When you asked me to build a basic MDX script I tried to build extremely simple report with one measure and one parameter by dragging 'price' hierarchy onto Filter pane. – erop Apr 22 '16 at 13:52
  • Query now looks like the following: `SELECT NON EMPTY { [Measures].[# состоявшихся аптечных визитов (с препаратами)] } ON COLUMNS FROM ( SELECT ( StrToSet ( @[ПродажиаптекЦена], CONSTRAINED ) ) ON COLUMNS FROM [Аптечные визиты] ) WHERE ( IIf ( StrToSet ( @[ПродажиаптекЦена], CONSTRAINED ) .Count = 1, StrToSet ( @[ПродажиаптекЦена], CONSTRAINED ), [Продажи аптек].[Цена].CurrentMember ) ) CELL Properties Value, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS` – erop Apr 22 '16 at 13:53
  • Of course I switched parameter from default Text type to Integer and set available and default values to None. And report is actually **not working** throwing error like: `Query (1, 109) The restriction imposed by the CONSTRAINED flag in the STRTOSET function were violated.` – erop Apr 22 '16 at 13:53
  • I tried @Ala solution in SSMS. But it looks like it works only on exact matching members. In my case I need to compare member numeric value with parameter one and filter out result set by this logical comparison. In plain words I need to filter out all the fact records (actually price monitoring records made by sales rep) with price (member value) less than parameter value. – erop Apr 22 '16 at 13:54
  • There are a lot of information related to usage of MDX `Filter()` function where logical expression starts with `[Measures].[...] > xyz`. But how to use `Filter()` where logical expression should start with `[].[...]`? I assume that I just misunderstand base MDX and SSRS consepts to properly build reports needed and I need another not `Filter()`-related approach to solve the issue. – erop Apr 22 '16 at 13:54
  • OMG! I formated my answer with markdown but it looks like impossible to use it in comments. Sorry for bad code formatting :( – erop Apr 22 '16 at 13:55

0 Answers0