mdx

Using colors with MDX calculated measure

Hi, I'm using this query in MDX for a calculated measure topcount(nonempty([StatusPlanes].[Status].Status.members,[Measures].[Planes]),1)(0).member_caption This will bring me this result Dimension1 Measure Center 1 'Status 1' ---> i want this text blue Center 2 'Status 2' ---> i want this text in red Center 3 'Statu...

using colors in calculated member

Hi, Im using this query in MDX for a calculate measure topcount(nonempty([StatusPlanes].[Status].Status.members,[Measures].[Planes]),1)(0).member_caption this will bring me this result Dimension1 Measure Center 1 'Status 1' ---> i want this text blue Center 2 'Status 2' ---> i want this text in red Center 3 'Status...

SSRS and MDX causes VStudio to crush

Hi I am trying to generate a report using reporting services in visual studio 2005. the data source is in MDX DB and was created successfuly. When I save the query in the query builder window it closes and displays an alert : "the query cannot be read from the query window, please check for syntax errors" and then VS crushes! the query...

Adding a Total column to MDX

I have the following query that gives me an extract (I am putting this into a CSV from my cube). I want to show the Total of all NRx in a column as the first column NRx and the rest of the NRx columns should come up as they do now. I tried to add a SUM (Measures.NRx) to this, but it did not work How do I get a Total NRx column? SELECT...

In SSAS, can parent-child DATAMEMBER name be customised

In a parent-child dimension in SSAS, the datamember is automatically named the same as the parent. E.g. Division X Risk Register Division X Risk Register Department A Risk Register Department B Risk Register Division Y Risk Register Division Y Risk Register Department P Risk Register Department Q Risk Register I want all the da...

Semi-additive measures problem - sum snapshots for each month, but not across months

Proficient with SQL but new to MDX, I am having trouble getting my head around this: I have a fact table that contains snapshots of account balances monthly. I need to roll up the balances as a semi-additive measure - straight sum doesn't work, obviously, for balances. However, I DO need to sum all the balances within EACH month, separa...

Passing multiple parameters into MDX query for WHERE clause

If I have a simple MDX query such as: SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS, NON EMPTY { [Date].[Date].[Date] } ON ROWS FROM [MyCube] And I want to filter it by an Id an another dimension this is easy enough. SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS, NON EMPTY { [Date].[Date].[Date] } ON ROWS ...

MDX Replace Range With Filter

While looking at the following answer I wanted to replace the range with a filter. http://stackoverflow.com/questions/1361039/mdx-concurrent-calculations-based-on-a-record-time-range/1391009#1391009 The MDX is: with member [Measures].[TotalUsed] as sum({[Date].[YQM].&[20090501]:[Date].[YQM].&[20090907]}, [Measures].[Used]) select {...

MDX query containing 2 dimensions from the same Hierarchy

The relevant structures from my cube are that I have a Hierarchy with "Class" and "SubClass". I also have a Measure called "Value" which is what im trying to obtain. A simple query may look like: SELECT NON EMPTY ([Measures].[Value]) ON COLUMNS, NON EMPTY ([Some Dimension].[Class Hierarchy].[Class]) ON ROWS FROM [MyCube] And I can...

Obtaining a measure based on date in MDX

These are really hard to describe without going in-depth into the data structure, i'll try to keep it as short as possible while giving as much info as I can. I have a measure which is a monetary value over time (cashflow), along with dimensions for date, and one for valuation. A valuation has a date, and this date would correspond with...

MDX WHERE NOT IN() equivalent in many-to-many dimension

Hi, I have a many-to-many dimension in my cube (next to other regular dimensions). When I want to exclude fact rows in my row count measure, I usually do something like the following in MDX SELECT [Measures].[Row Count] on 0 FROM cube WHERE ([dimension].[attribute].Children - [dimension].[attribute].&[value]) This might seem more com...

Invisible Attribute Hierarchy under Dimension

Below is the snippet of an mdx query used in a report. with member [Measures].[Leased Coin In] ([Machine Dimension].[Leased Flag].&1 , [Measures].[Coin In]) Now, my question is: In the cube under Machine Dimension, there is no attribute hierarchy name called Leased Flag. I looked at in the xmla script, there is no such name. But, the ...

using MDX Sprite.Draw2D() and the positional problems i have encountered. [Fixed]

I'm trying to use the Sprite Class in Microsoft.DirectX.Direct3D to draw some sprites to my device. GameObject go = gameDevice.Objects[x]; SpriteDraw.Draw2D(go.ObjectTexture, go.CenterPoint, go.DegreeToRadian(go.Rotation), go.Position, Color.White); GameObject is a class i wrote, in which all the basic information required of a game o...

Aggregating results for all versions of a slowly changing dimension

Hi, I am looking to aggregate values for all versions of a slowly changing dimension. Here is a simplified explanation of the problem. I have a Product dimension and this dimension has an attribute called ProductGroup. Products from time to time change ProductGroups. The Product dimension is a slowly changing dimension, and the ch...

MDX Measure Filtering

I am building a Mondrian Cube that shows information for a large range of dates. One of the measures for this cube is an average of a percentage value. Because some of the items in the cube should not make up the final average, I need to know how to filter them out based off this measure and only for this calculated member. ...

Different approaches to accessing SSAS

I’m interested in knowing some different approaches for retrieving data from Analysis Services, to use in either objects in code, or for end-user reporting. I’ve used two different approaches in the past, one was using ADOMD to pull results and put these into a dataset, the other was using SQL OPENQUERY to a linked SSAS server to get r...

MDX C# Loading Wavefront OBJs

I'm trying to load and display (EXTREMELY SIMPLY) a wavefront OBJ file. I have the parser working 100%... it loads the data from the files and stores them in structures in the program. Now is the part where I am utterly failing to find any information on, the target data structure. I have no clue how to use Vertex Buffers and Index Bu...

mdx: retrieve the number of days between two different date dimensions

Hi all, I'm working on a cube with two different date dimensions: the fist one manages the patient hospedalization dates: [hosp_date] it is organized in Year,quater, month and relative_date : this one counts the number of days since 1st Jan. 1980. and the second one manages the patient discharge dates: [disc_date] with the same organiz...

MDX: How to turn tuples with multiple members into tuples with a single member?

I have this MDX query (based on the Foodmart sample database): SELECT {[Measures].[Profit]} ON COLUMNS, {CROSSJOIN([Product].[All Products].Children, [Time].[1997].Children)} ON ROWS FROM [Sales] This generates a result like this: [Product].[Product Family] [Time].[Year] [Time].[Quarter] [Measures].[Profit] --------------------------...

MDX: Calculating avg action time and change over time, for top 5 actions

I have an "Actions" cube. The dimensions are "time" and "action ID" and the measurements are "number of actions" and "total time" and a calculated measurement "average action time". I am trying to calculate the top 5 actions by avg time, and the show the change from the previous day. I can do this in two separate queries: SELECT {[Measur...