ssas,mdx , The MDX function CURRENTMEMBER failed because the coordinate for the 'Date' attribute contains a set


The MDX function CURRENTMEMBER failed because the coordinate for the 'Date' attribute contains a set

Question:

Tag: ssas,mdx

I'm trying to run this query:

select [Dim Date].[Date] on ROWS,
{[Measures].[Available Time Net],[Measures].[Logged On Time Net]} on columns 
from [OTS Agent Time Net Data]
where {[Dim Date].[Date].&[08/01/2014]:[Dim Date].[Date].&[12/31/2014]}

I want to get the measures that exist in the where clause, but I also want to show the dates on the rows. I keep getting an error.


Answer:

Why not just add the set directly to the ROWS ?

SELECT 
  NON EMPTY 
    {
      [Dim Date].[Date].&[08/01/2014] : [Dim Date].[Date].&[12/31/2014]
    } ON ROWS
 ,{
    [Measures].[Available Time Net]
   ,[Measures].[Logged On Time Net]
  } ON COLUMNS
FROM [OTS Agent Time Net Data];

Related:


Excel 2010 - filter pivot table by pattern


excel,excel-2010,ssas,pivot-table
in Excel 2010 I am trying to analyze some data from an external analysis service. In a pivot table I am trying to filter the report by one field which has multiple values separated by a comma. These look like this: AB, CD1, EF1-5 AB, CD1,3, EF1 BCD, EFG EXG,...

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 ) }...

SSAS average aggregation


sql-server,ssas
I'm currently trying to create an SSAS project which is used to analyse the profitability of each product. One of the facts in my fact table is Profitability, which is a ratio calculated by (ListPrice - Cost) / Cost However I'm having some problems with my Cube because SSAS automatically...

How to view the data from SSAS CUBE


sql-server-2008,ssas,ssas-2008
I am new to SQL Server CUBES. I have processed the CUBE in SQL Server 2008 environment. How to view the data based on its dimensions? Do the CUBE needs to be mapped to the Reports to view the data? Whether the CUBE has to be processed on daily basis...

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...

QTD MDX calculated value displaying differently on SSRS than SSAS


sql-server,reporting-services,ssas
I have created an SSAS cube in SQL 2005 and included calculated members for MTD, QTD as well as YTD. With QTD measure I actually want to get the total over all 3 months of the quarter, not just the up-to-date value. Now when I browse the cube itself the...

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...

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 ...

Assign Dimension value to SSIS variable


sql-server-2008,ssis,ssas,ssas-2008,data-cube
I have data cube hierarchy as follows. I can access the highlighted node as SELECT [Calendar].[Report Days].[All Members].[All].[WantInReport].[Yesterday].LastChild ON 0 I tried to run this query in Execute SQL task and assign the output to an SSIS variable. But issue is the column name is changing. I tried to alias...

Do I need a cube?


sql-server,ssas,reporting,olap
We have a content ingestion system which receives (mobile) digital contents of different types (Music, Ringtone, Video, Game, Wallpaper etc) from various providers (Sony, Universal Music, EA Games etc) and then dispatches them across several online stores (e.g. Store1, Store2 etc). The managers want to know how many of each...

How to delete default Measure Groups in the SSAS CUBE?


sql-server-2008,ssas,ssas-2008
I have created a CUBE. By default CUBE created few Measure Groups. When I go to Aggregations, I can see the default Measure Groups. I want to delete them all and want to create as per the requirement. Attached is the screenshot, ...

Can't create database in SQL SSAS


ssas
I just installed Microsoft SQL Analysis Service because I need it to run a forecast analysis from Excel using the Data Mining Plug-in. When I open MS Management Studio and connect to the SSAS I don't know where to create a new database. When I right-click over Databases there is...

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...

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...

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). ...

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...

SSAS cube: force Budget measure granularity drill down to the leaf level of calendar dimention (split Quarter Budget between Monthes, Weeks and Dates)


ssas,hierarchy,olap,granularity
Our Budget/Plan has Quarter grane, while Fact sales data is stored Dayly. To build Plan-Fact reports for Monthes, Weeks and Dates - I need to split Budget evenly between Calendar dates. How can I do this within a Cube? After hours of googling I've found references to [IgnoreUnrelatedDimensions] property of...

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 |...

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...

What cube deployment changes will force a reprocess?


ssas,cube,ssas-2008
Sometimes when I deploy a cube that has been changed (from BIDS), I can continue to browse the existing cube data. Other times, the engine insists I reprocess the data before I can browse the cube. I can't find a definitive resource showing which changes require a data reprocess and...

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 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....

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...

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...

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...

Errors in the OLAP storage engine: The attribute key cannot be found when processing


ssas,foreign-key-relationship,data-warehouse,olap-cube,dimensional-modeling
I know this is mainly a design problem. I 've read that there is a workaround for this issue by customising errors at processing time but I am not glad to have to ignore errors, also the cube process is scheduled so ignore errors is not a choice at least...

Add value of TOP (ALL) to parameter in SSRS report connecting to SSAS cube


reporting-services,ssrs-2008,ssas
I have a simple SSRS report connecting to an SSAS cube that displays all the sales by employee for a day. The report displays correctly and shows me all employees and its sales, in descending order. But the users don't want to see all the sales; they just want to...

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, {...

tabular in-memory vs multidimensional and molap-mode


ssas,cube,tabular,in-memory,rolap
I have a question regarding the ssas-models tabular and multidimensional cube. I've read that both models can work in a real-time-mode (direct query mode & rolap). My questions concerns the tabular model in in-memory-cache-mode and the multidimensional model in molap-mode. How recent is the data there? Can I define myself...

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....

What does the SSAS setting None in AggregateFunction do?


excel,ssas,pivot-table,cube
As I understand Microsoft's explanation setting the AggregateFunction of a measure to "None" should null out the value unless you are viewing the data at the lowest possible level. (i.e. the data element is a copy of the value in one exact cell in the cube). I've created a fact...

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...

Approaching mixed granularity date dimensions for operational periods in cube design


ssas,olap,olap-cube
I am building a cube in SSAS, modelling (amongst other things) activity of engineering teams. I have a fact table (TeamActivity), with facts such as Mileage and TimeOnSite on a DAILY granularity. This references a date dimension table (DimDate). DimDate contains typical attributes so data can be analysed by calendar/fiscal...

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...

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...

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...

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 install ssas AdventureWorks DW with SQL Server 2008 R2


sql-server,sql-server-2008,sql-server-2008-r2,ssas,adventureworks
I am doing a proof of concept to explain the team using cubes improves the application's query performance. I am allowed to use VS 2008 BI tools. I am in need of the AdventureWorks2008R2 DW to be installed to write some mdx queries to query the cubes. I have landed...

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...

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...

Configuring a standard single server TFS 2013 with SharePoint 2013 and SSRS for reporting


sharepoint,reporting-services,tfs,ssas,reporting
Is following scenario possible? We have got TFS 2013 installed as a standard single server. This server has got the SQL db. We have got SharePoint 2013 installed on another machine. Now we want to integrate TFS 2013 with SharePoint 2013 along with SSRS and SSAS for reports, etc. Please...