FAQ Database Discussion Community


Get the last three months dynamically

mdx
I have the following MDX query SELECT Hierarchize ( { [PERIOD].[Year Month].&[2014 / 10] ,[PERIOD].[Year Month].&[2014 / 11] ,[PERIOD].[Year Month].&[2014 / 12] } ) ON COLUMNS ,{ [Measures].[Amount] ,[Measures].[Total Cost] } ON ROWS FROM [Asset]; Is there a way to get the last three months dynamically ? ...

SSRS mdx report: use dimension on columns

reporting-services,mdx,olap,dimension,rdl
This simple mdx query do work in SSMS: SELECT CrossJoin({[Measures].[Qnt]}, {[Sales_step].CHILDREN}) ON COLUMNS, [City] ON ROWS FROM [SALES_PIPE] But it fails to run within Visual Studio for rdl report. An error accures: The query cannot be prepared: The query must have at least one axis. The first axis of the...

Intersect Select Query in MDX

ssas,mdx,olap
I want to have customers intersection in two mdx querys. 1. SELECT [Measures].[Cs] ON 0 ,NonEmpty([Customers].[Customer].MEMBERS) ON 1 FROM [sfe cube] WHERE ( [Calend].[Period].&[201506] ,[Customers].[BP Territory].&[38UZ1] ,[Materials].[Brand].&[Coca-Cola] ); 2. SELECT [Measures].[Cs] ON 0 ,NonEmpty([Customers].[Customer].MEMBERS) ON 1 FROM [sfe cube] WHERE ( [Calend].[Period].&[201506] ,[Customers].[BP Territory].&[38UZ1] ,[Materials].[Brand].&[Fanta Orange CSD] ); My attempted...

MDX Query to get employee ID based on their login ID

ssas,mdx,username
I'm trying to implement dynamic security on a cube where an employer should be able to see the measures associated with all the employees under him. I'm doing this by first getting the login credentials of the logged in user using USERNAME() and then getting the descendants of that user....

MDX - same grand total even when filtering

mdx,mondrian
I have a calculated member which brings the total Sales for all Shops. The formula I used is this: SUM(([SHOP].[SHOP].Members), [Measures].[SALES]) So, even if I filter for one Shop, the above calculated member still brings me the total for all shops. The problem is that if I filter for any...

Difference between AdomdConnection and AdomdCommand classes

c#,c#-4.0,mdx,olap,adomd.net
My understanding is that AdomdConnection initializes a connection with a remote database. This class opens it based upon the ConnectionString. The AdomdCommand then seems capable of setting a CommandText and Executing said command, similar to executing a query. Is this understanding correct? But how are the two related? Do I...

How to create an interval in a matrix

reporting-services,ssrs-2008,mdx,ssrs-grouping
I am working with SSRS 2008, my source is a cube and I use this MDX query: SELECT NON EMPTY { [Measures].[Val] } ON COLUMNS, NON EMPTY { ([DimCl].[Cl].[Cl].ALLMEMBERS, [DDate].[Year].[Year].ALLMEMBERS ) } ON ROWS FROM [DW] I created a matrix with the following data 2014 2015 0 10 11 1...

icCube - InterpolateRGBColors based on min & max values?

colors,mdx,interpolation,iccube
I understand that InterpolateRGBColors function is returning a color by position of value between 0 and 1... So its seems to be doable only with percentages, not numbers... Is there a way to have the same functionality, but based on the min and max values returned in a set ?...

MDX - TopCount plus 'Other' or 'The Rest' by group (over a set of members)

ssas,mdx,olap
I've got requirement to display top 5 customer sales by customer group, but with other customers sales within the group aggregated as 'Others'. Something similar to this question, but counted separately for each of customer groups. According to MSDN to perform TopCount, over a set of members you have to...

Mondrian - Parent with filtered children

filter,mdx,parent,mondrian
I want to create a topcount of users per product category. My product dimension is like the following [Product].[ProductCategory].[ProductType] Users of my application can filter the products and it return a set like this: [Product].[ProductCategory1].[ProductType1], [Product].[ProductCategory3].[ProductType5], ... Now I got the topcount per product category, but it doesn't filter the...

Creating a Calculated Time Periods Hierarchy

ssas,mdx,calculated-member
In order to give a nicer Cube browsing experience to end users, I am trying to create a Time Periods hierarchy consisting of Calculated Members. Currently I have used a Calculated Column in my DSV to create a column with the same value on every row in my Dates table...

Create a measure in MDX for a set of Members

mdx,measure
I need something as following: with member Measures.Test2 as (Measures.Test, {[Assets Monthly].[Disbursed Date].&[2014], [Assets Monthly].[Disbursed Date].&[2015]}) select Measures.Test2 on columns from databaseX It works only if I set it with only one member, ex: with member Measures.Test2 as (Measures.Test, [Assets Monthly].[Disbursed Date].&[2014]) The error that I get in the first...

MDX Start and End Time per transaction

ssas,mdx,cubes
I hope you can help i have tried so many way to try get this right with no luck. I am trying to get out the player account number the date and start and end date time and maybe calculate the play duration between the start and end times. I...

MDX Top Count Sub total

sql-server,ssas,mdx
I am trying to create an MDX query to calculate the top selling stores for a territory, then subtotal the territory. I have used the TOPCOUNT function with the GENERATE function to create a SET for the Top Locations, however I am having real trouble trying to sub total each...

How to Reload CDA and Mondrian cache in Pentaho CE 4.8?

caching,mdx,pentaho,mondrian,cda
I'm currently stuck in some performance issue for my Dashboard. I've created a dashboard in Pentaho Community edition 4.8. For my charts, using the SQL and MDX (Mondrian) queries. My Problem is that, When I first time open my dashboards after clearing cda and Mondrian cache. It take 50 secs...

Count Product was sold In MDX query

mdx
My MDX query as SELECT {[Measures].[SaleAMT]} ON COLUMNS ,NON EMPTY [Account Date13h].[Date].&[2015-01-01T00:00:00] : [Account Date13h].[Date].&[2015-05-05T00:00:00] ON ROWS FROM [Sale_Period_report]; I want to return a Count of product was sold, like that: Date SaleAmt ProductSold 2015-04-01 20.000 150 2015-04-02 36.212 650 2015-04-05 10.333 65 ...

MDX: Top 10 with filtered members

filter,mdx,ireport
I'm creating a report for JasperServer in iReport. I am creating a report with the top 10 companies with most product downloads of the selected products. Users can select in JasperReports which products with a list (parameter {$P{ProductFormat}}). My dimension for products has the following structure: [Product].[ProductFactory].[ProductType], so input for...

MDX: How to get the weeks between two dates in MDX Query

date,tfs,mdx,olap-cube
I want to get the weeks as columns between two dates in an MDX query. For example if I input these dates: 2015-01-01 and 2015-02-01 I want to get this: Week ending January 1,Week ending January 8,Week ending January 15,Week ending January 22 I have created this MDX query with...

MDX ORDER by multiply measures

mdx
Suppose that i have next MDX query select { [Measures].[Measure1], [Measures].[Measure2], } on columns, { ORDER([Dim].Children, [Measures].[Measure1], desc) } on rows from [Cube] which selects two measures and sorts rows by first one. How can sort by two measures, frist sort by Measure1 and then sort by Measure2?...

format_string in icCube does not format null values

mdx,iccube
I was trying to display values that are null as 0 using format_string. I currently have the following: WITH MEMBER [test] AS null, FORMAT_STRING = '0.#;Neg (0.#);0.00;0.00' SELECT [test] ON COLUMNS FROM [Sales] The fourth value (;0.00) should format the null values to zeros. I read that here: https://msdn.microsoft.com/en-us/library/ms146084.aspx Currently...

Mdx query incosistent with cube browser

ssas,mdx
I have built a multidimensional SSAS cube with some calculations. I have a calculated member in which I use the the parallelperiod function to calculate the previous month value according to the following code : Sum ( (EXISTING [TimeDim Transactions].[Year - Quarter - Month - Date].[date].MEMBERS) ,( ParallelPeriod ( [TimeDim...

Named Set using iif in mdx

ssas,mdx
Ι am trying to make calculated dimension attribute using a named set but I am not very familiar with mdx. I want the attribute to contain a description based on the values of 4 different attributes. What I have tried to use is the following code but it must have...

Get the amount of days in a month - mdx

sql-server,tsql,mdx,cube
In TSQL I can do this to get the amount of days in some month: declare @date as datetime set @date = '2015-02-06' select datediff(day, dateadd(day, 1-day(@date), @date), dateadd(month, 1, dateadd(day, 1-day(@date), @date))) How can I get the same functionality in MDX? P.S. I need the result to be an...

drill down on reversed hierarchy gives incorrect results

mdx,iccube
I have got the following MDX statement in icCube (based on the standard Sales model): with set [time-set] as hierarchize({[Time].[Calendar].[All Periods],[Time].[Calendar].[Year].members,[Time].[Calendar].[Year].[2009].children},post) member [issue] as [amount], caption = "drill down on 2008" select [issue] on 0 , [time-set] on 1 from sales This gives the following result: When clicked on 2008:...

How to make a calculated member in MDX for SSAS?

ssas,mdx,business-intelligence,olap,olap-cube
I have a problem in my calculation and can't solve. Example: Our company sells about 100 products, divided into categories and product ID. Product ID is the smallest granularity. I need to show how this product list we sell in each city. See the picture: Overall, without showing the category...

Equivalent to max group by in mdx

ssas,mdx
How do I get the sales for the last product of a cross join of each product group and brand? I had a look at the Tail function but I can't get it to work properly. This is the MDX I have so far: SELECT {[Measures].[Sales Amount]} ON COLUMNS, {...

mdx nonempty exclude value that exists

mdx
My understanding of Nonempty() function is that it takes first set expression, apply that onto 2nd set expression and remove ones that do not have value in the 2nd expression. I have WITH SET [Date Range] AS Filter( [Date].[Date].[Date], [Date].[Date].CurrentMember.Member_Value >= CDate(@StartDateParam) AND [Date].[Date].CurrentMember.Member_Value <= CDate(@EndDateParam) ) MEMBER [Measures].[DateValue] AS...

What does an MDX query return?

ssas,mdx
I just started learning SSAS and cannot understand the basic idea. What happens when query fixes fewer dimensions than cube has? All examples usually present queries where intersection of dimensions gives either a point or an axis; in the former case we have the value and in the latter one...

MDX query dynamic where clause

sql,sql-server-2008-r2,mdx
SELECT NON EMPTY { [Measures].[Total Value],[Measures].[Value less than 30], [Measures].[Value less than 60],[Measures].[Value less than 90],[Measures].[Value less than 150], [Measures].[Value less than 180],[Measures].[Value less than 365],[Measures].[Value more than 365]} DIMENSION PROPERTIES CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME ON COLUMNS, NON EMPTY {[Combined].[Drill Down Path 4].[Supplier Name].ALLMEMBERS } DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM [InventoryAge]...

How to get the Percentage using MDX

ssas,mdx
I want to find the percentage of claims in run time. I learn that it can be achieved using calculated measure, but I do not know how to do that. Please guide me how to find the percentage of claims((line count/grandtotoal) *100). ...

How to go from rows to a hierarchy in icCube

mdx,iccube
I want to create a one-column hierarchy from multiple columns using mdx. I got this result: from the Sales cube with this mdx: SELECT { {[Measures].[Amount]} } ON COLUMNS, { [Customers].[Geography].firstNotAllLevel().allmembers }*{ [Product].[Product].[Category].allmembers }*{ [Time].[Quarter].firstNotAllLevel().allmembers } ON ROWS FROM [Sales] I want the first three columns to form a hierarchy...

Topcount with total of all members

filter,mdx,mondrian
I'm creating a query which shows all continents, their countries and the top 5 users in these countries (1 dimension) with their orders (measure). Now I want to show to total of all users in these countries instead of only those in the top 5. I followed the answer in...

List all previous Years and measure till today such that all 12 Month data is available for year.Else exclude the year

sql,mdx,analysis
We have a typical Date Dimension. I am struggling to write an MDX to list the Dim.Year dimension member and measure contain data strictly for all month.(from Jan-Dec) otherwise exclude the year?....

SSAS report action to pass multi-value list values to a SSRS report parameter

excel,reporting-services,ssas,mdx,olap
I have searched many hours attempting to find an end-to-end solution to this problem. I want to create a report action in an SSAS OLAP cube that generates a list of formatted values to pass into an SSRS report parameter that accepts a multi-valued parameter list. This would be applied...

Set 0 for specific value MDX query

sql,sql-server-2008,ssas,mdx,cube
I've been looking around for the answer but I didn't find anything. Sorry if the answer has been given elsewhere. Here is my problem : I have a calculated member which is the number of items (of the current member) divided by the total number of items (sumitem). with member...

Mdx , All and All Members differences

ssas,mdx
In MDX what is the difference between [Period].[Period Name].[Period Name].ALLMEMBERS and [Period].[Period Name].ALL are these statement same?...

Working MDX query doesn't work as a Calculated Member

ssas,mdx
I have made the following mdx query to calculate a running total using some references I found on internet which works when I ran it on management studio. With member measures.test as ( sum([Card Issue date].[YearQuarterMonth].[Calendar Year Month].&[2014]&>>[2014-10] :[Card Issue date].[YearQuarterMonth].CurrentMember , [Measures].[LoyaltyMembers]) ) select measures.test on 0, [Card Issue...

How to filter measure in the Filter function by multiple items from the same hierarchy

mdx
I am creating an application that generates MDX queries. I stuck with implementing a scenario when the user uses the Filter function and, in the logical expression, the user wants to slice a measure by multiple members from the same hierarchy. Here is a real example: there is a cube...

Non empty MDX query in Visual Studio 2010

mdx
My issue is when I use non empty on the row, all row fields disappear from the data set in Visual Studios 2010. The same happens if I place non empty on columns -- all the column fields disappear from the data set. If the non empty is removed from...

Filter Measure by reference date column

ssas,mdx
In my date dimension I have an attribute called CompareToDatekey which shows the date that should be used to compare measures through time. How can I build a calculated measure that will give the measure value for the compare date when I filter a particular primary date? What I tried...

How to group record by range and put it on rows

ssas,mdx
Below query compute the percent of work employee wise. i.e. employee on rows and percent of work on columns With Member [Measures].[EmployeeWisePercent] AS [Measures].[Hours] / ([Employee].[Employee].[All], [Measures].[Hours]) * 100 Member [Measures].[TotalHours] AS ([Employee].[Employee].[All], [Measures].[Hours]) SELECT NON EMPTY { [Measures].[Hours], [Measures].[EmployeeWisePercent], [Measures].[TotalHours] } ON COLUMNS, NON EMPTY { ([Employee].[Employee].[Employee].ALLMEMBERS ) }...

Easiest way to programmatically generate MDX rowcount query?

sql,mdx,olap
Right now I'm dealing with a program that can generate and return SQL or MDX queries (depending on the source database of the queries). I'm working on adding a feature that counts all the rows returned by a given query. Now, I have some small background with SQL, so I...

SSRS stacked bar chart multiple series, utilizing category groups for different date fields

reporting-services,mdx,stackedbarseries,reporting-services-2012
The source data is an SSAS cube which contains information about tickets, particularly priority, date opened, and date closed. Each ticket has a priority and date opened value, though the closed value may be null or another placeholder value (subject to change). A ticket is represented by a record in...

Replacing blank with “NA” or “MISSING” text

mdx,slimdx,mdxstudio
I am trying to replace blank spaces in data with the "MISSING" or "NA" text. CoalesceEmpty([Product Type].[All Product Type].[ ],"MISSING") I have tried the above code, with or without quotes around MISSING. Getting the following error: CoalesceEmpty([Product Type].[All Product Type].[ ],"MISSING") is invalid and cannot be used in a query....

select data from olap MDX query

ssas,mdx,olap,olap-cube
Please help to understand how MDX query works. I have connected to cube using excel and construct a mdx query. In short finally I need to get the table like this: 12.01.2015 +-------+-------+-------+-----+-------+------ | 00:00 | 01:00 | 02:00 | ... | 23:00 | TOTAL --------+-------+-------+-------+-----+-------+------ Ivan | null |...

MDX MTD to get the month to date data

mdx
Currently i need to show the data which is from the first day of month until the specific date. SUM(MTD([D Time].[calendar].CurrentMember.parent), [Measures].[Wt]) I try the code above is shown the first day of month until the end of the month... Anyone can help to correct my code? Or should i...

SSRS cascading parameters: child parameters' default 'All' value drops to blank after user changes parent parameter

reporting-services,parameters,mdx,rdl,cascading
I have a big bunch of cascading parameters in my rdl report with default value 'All' everywhere. But all children go blank after user changes some parent parameter, and then he has to choose 'All' value from drop-down list for each of child parameters manually. This is pretty annoying staff....

MDX - Retrieving a level of members currently used in where clause

sql-server,ssas,mdx,ssas-2008,data-cube
I have a simple data cube with organization structure hierarchy defined. In my calculations inside the cube I would like to have different calculations depending on which level of organization items is currently used in WHERE clause in MDX query. So let's say that I have 5 levels of organization...

Handling MDX OPENQUERY, The identifier that starts with is too long. Maximum length is 128

sql-server-2008,mdx
I have a problem using openquery. i want to select the column with it's name but the name is too long because there are much levels SELECT A."[Dimension].[NAMEOFLEVEL1].[NAMEOFLEVEL2].[NAMEOFLEVEL3].[NAMEOFLEVEL4].[NAMEOFLEVEL5].[NAMEOFLEVEL6]" AS Acc2 FROM OPENQUERY(CUBEX, 'SELECT ({ [Dimension].[NAMEOFLEVEL1].[NAMEOFLEVEL2].[NAMEOFLEVEL3].[NAMEOFLEVEL4].[NAMEOFLEVEL5].[NAMEOFLEVEL6] }) DIMENSION PROPERTIES MEMBER_KEY ON COLUMNS, NON EMPTY ( [TIME].[MONTH] ) DIMENSION PROPERTIES MEMBER_KEY ON...