Stack Overflow
Stack Overflow

Questions tagged [mdx-query]

A sample MDX query looks like below. 

[ WITH <SELECT WITH clause> [ , <SELECT WITH clause> ... ] ]   
SELECT [ * | ( <SELECT query axis clause>   
    [ , <SELECT query axis clause> ... ] ) ]  
FROM <SELECT subcube clause>   
[ <SELECT slicer axis clause> ]  
[ <SELECT cell property list clause> ]
140 questions
5
votes
0 answers

Find Number of times a particular character appears in a string using MDX query

In the Microsoft SSAS, I have a dimension column that contains multiple values separated by the special character | in a single row as below. Example value of a row: Image|Video|Audio| Requirement: So, I need to count the number of values present in…
Sathish G
  • 91
  • 3
5
votes
1 answer

Multiple dimensions conditions in SSAS MDX query not working

I Am having the following data in my SSAS cube. My need is to get the value of the measure based on two conditions with two different dimensions using the MDX. In this example data, I need to get the Reseller Sales Amount value where the value…
TAMILARASAN R
  • 225
  • 1
  • 6
5
votes
1 answer

IIF() function not working properly while using multiple times in single MDX query

I need to get the aggregated values based on two conditions using single MDX query Am having below data I have created two calculated functions using IIF() function in a below query WITH MEMBER [Measures].[Expression1] AS…
TAMILARASAN R
  • 225
  • 1
  • 6
4
votes
0 answers

Type mismatch error occurs while casting string values to date type in MDX query with some blank rows

I have a string-type dimension in my SSAS cube having the date values along with some blank values and please find the screenshot for your reference. I am trying to convert these values into Date using the CDate function in the MDX query. I have…
TAMILARASAN R
  • 225
  • 1
  • 6
4
votes
1 answer

How to sort records in MDX query when numeric expression is used with TopCount function

I am trying to sort the Top 201 records in my SSAS cube either ascending or descending based on requirements in my C# code. Here I a using Adventure Works Database, where [Reseller Order Count] is the measure and [Reseller].[Business Type] is the…
Sathish G
  • 91
  • 3
4
votes
1 answer

SQL task MDX with parameters. SSIS

I need to create dynamically some local cubes in ssis and i don't know how to use parameters in this query CREATE GLOBAL CUBE test STORAGE 'C:\test.cub' FROM Cube_test( Measure [Cube_test].[Val], dimension [Cube_test].[Date_1] ( level [Date_1], …
LuisSol
  • 41
  • 2
3
votes
0 answers

IIF() function not properly working while using in MDX query

I need to get calculated measure values based on conditions. Am having the below data in my cube I have created below calculated measure using IIF() function IF([Title] = "Sales Representative",[Reseller Order Count],0) I have tried with below…
TAMILARASAN R
  • 225
  • 1
  • 6
3
votes
1 answer

How to construct date range query when not all dates exist in hierarchy?

New to MDX and inherited an application using the following to retrieve the last 7 work days. Note, the actual dates are generated dynamically. WHERE ( [DimCalendar].[WorkDayHierarchy].[WorkDate].&[2016-03-25T00:00:00] :…
SOS
  • 6,430
  • 2
  • 11
  • 29
3
votes
1 answer

iccube : how to count number of members with where clause in a mdx?

Using the following mdx, the result is the same if I run it with or without the where clause : It returns the total nbr of years in the hierarchy. with member nbYears as [Time].[Year].[Year].count select nbYears on 0 from [Sales] where…
Bertrand Miot
  • 929
  • 5
  • 12
3
votes
2 answers

Query returning only the leaf nodes from SSAS Cube using MDX

I have a requirement to list out the parent levels of the resultant leaf node into their corresponding columns into a single row. The below query return the expected result SELECT NON EMPTY { [Measures].[Value] } ON COLUMNS, NON EMPTY {…
asb
  • 781
  • 2
  • 5
  • 23
2
votes
1 answer

How to use multi level hierarchy names in MDX query

I need to use the dimension field under the 2 hierarchies i.e., one hierarchy under another hierarchy in the MDX query. Am having the below hierarchies/data in my cube: I need to use the highlighted [Country] field in the MDX query. Please find the…
TAMILARASAN R
  • 225
  • 1
  • 6
2
votes
0 answers

How to compare the current member value with previous member value in mdx query?

I am having the following data in my SSAS cube I need to get the value based on a comparison between the current value and the previous value using MDX query. In this example data, I need to get the Weight value as Result when the current value of…
TAMILARASAN R
  • 225
  • 1
  • 6
2
votes
0 answers

MDX Query using generate function and calculated members

I've been trying to solve this query for a while but I don't understand where I'm wrong. The questions of the query is this one: Show the winners having a total winner rank points greater than the average winner rank points in each continent by…
Lusi Saliai
  • 35
  • 3
2
votes
0 answers

The Measures hierarchy already appears in the Axis0 axis

I want to use the measures fields in both rows and columns in the MDX query and I have used the below query SELECT{[Measures].[Average Rate]} ON COLUMNS, NONEMPTY( ( TOPCOUNT( ( Order( ( ([Measures].[End of…
TAMILARASAN R
  • 225
  • 1
  • 6
2
votes
1 answer

To calculate the average of Latest 12 weeks

I figured out how to calculate the average of latest 12 weeks volume in my data. But lets say the current year does not have 12 weeks, it only has 8 weeks then I want to take the remaining 4 weeks from the previous year to make it 12? This is how my…
Hari Ganesan
  • 39
  • 1
1
2 3
…
9 10