.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

The Measures Hierarchy is used more than once in a CrossJoin Function

Posted By:      Posted Date: September 03, 2010    Points: 0   Category :Sql Server
Hi Folks, I am encountering a rather frustrating error in my SSRS report. It deals with MDX so I am posting this question in the SSAS forum. I have to create a report which should look something like this.   YTD1  YTD2 Product Measure 1 Measure 2 Measure 3 Measure 1 Measure 2 Measure 3 A B C D             I am writing an MDX using cross join to fetch this data, while the MDX works fine in SQL Server Management Studio, as soon as I run in on the MDX Editor in SSRS, I get the error, "The Query must at least have one axis". The MDX that I am using is WITH SET   [Top 10 Manufacturers] AS TOPCOUNT   ( { [Product].[Manufacturer].[Manufacturer]. allMembers}, 10, ( [Measures].[SALES VALUES(000)], [PERIOD].[MAT].&[MAT01]) )   SELECT NON EMPTY   CROSSJOIN( {[PERIOD].[Months in Year].&[1],[PERIOD].[Months in Year].&[2]}, {[Measures].[AVG SALES PRICE], [Measures].[AVG SALES PRICE CU], [Measures].[SALES VALUES] } ) ON COLUMNS, [Top 10 Manufacturers] on rows   FROM [MYCUBE] P.S. I need both the measures as well as the Hierarchy (MAT) on columns. Any help will be greatly appreciated. Thanks, Suman Sarkar  

View Complete Post

More Related Resource Links

Allmembers function expects a hierarchy expression for the argument, a member expression is used" er

  Hi , I have a report that was working before.  After adding couple calculated mesaure, my report is throwing the error.  Here is the query I used.  Can you please help? this was a working prototype, now it is in trouble.  Need to fix it quickly.  thanks a million   SELECT NON EMPTY { [Measures].[sales], [Measures].[%of sales] } ON COLUMNS, NON EMPTY { ([region].[country].[country].ALLMEMBERS * [Fiscal Period].[Fiscal Year].[Fiscal Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [sales] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS      

Parent child hierarchy & MDX to fetch particular level & all members below it along with measures us

I have a Sales Territory dimension that has employee and parent employee attribute on which parent child hierarchy is defined and it gives below hierarchy while browsing - - Mark Rolls --- Lumin Jacs ----- Larry Gomes ------- Messica Owens ------- Tom Ted ----------- Jackson Lopez ----- Matthew Ron --- Fred jacob - Jason Ron --- Jecy Pedro   But beside this parent-child hierarchy I have other attributes like employee address, email and telephone. My facts related sales transaction is tagged to lowest level. For example here facts are available only for Jackson Lopez (being Field Executive). When use below query I get complete sales reporting hierarchy result with some measures like sales amount, sales volume. But while accesing other attributes like address or email, it's repeating address/email/telephone of jackson Lopes everywhere to whom fact record is linked, Actually I want address/email/telephone of each sales employee from that dimension within hierarchy. How do I get it? The query I used is: Here Parent Terr ID is parent child hierachy. SELECT ( Descendants( { [Dim SalesRegion].[Parent Terr ID].[Employee Level 01].&[538018] /* here Mark Rolls is 538018 */ }, 0,AFTER), NONEMPTY([Dim SalesRegion].[Emp Address].[Emp Address].Members), NONEMPTY([Dim SalesRegion].[Emp Email].[Emp Email].Members) ) ON ROWS , { ([Dim Date].[The Year].[The Year].[CY-2010], [Dim

Replace pre-calculated Measures at "All" Level depending upon the Dimension Hierarchy Level

Have an interesting Challenge; thought will reach out to you if you can help Currently I am working on SSAS 2008 OLAP Cube, whereby most aggregate level measures are pre-calculated using the C# Code as part of ETL and are stored in Data Warehouse as well as Measure Groups in Cube (MOLAP). These are mostly non-additive measures have to choose this approach for performance reasons, considering the complexity of the aggregations and data volumes.  Data  Volumes are Huge (about 300+ Million Rows/ per day),  98 % of measures are non additive / semi additive.  Cube is used primarily for advance analytics and will be eventually used for data mining like time series , what if analysis and scenario analysis  Excel is used as front end . Question is how can we replace the aggregate level data for various dimensions attributes (Totals and Grand Totals) from pre-calculated measures, those are also available in the Cube as measure groups? We are currently using Scope and Root statement which is not working as expected SCOPE ([D1].[H1].[A1], M1) Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A1],  > EndScope; SCOPE ([D1].[H1].[A2], M1) Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A2] > EndScope;   Thanks in Advance   Best Regards,   Dave

Calculation using Crossjoin throws the error "hierarchy is used more than once in the crossjoin func


My original calculation using a nested Crossjoin works & filters then aggregates records appropriately with the following rules:
- The IncidentDate is within the time period
- The latest Status is within the time period
- The [Status Count] measure is a distinct count

 AS Aggregate(
      NULL:LinkMember(Tail(existing [Status Date].[Calendar].[Day].Members).item(0),[Status Start].[Calendar]),
      LinkMember(Tail(existing [Status Date].[Calendar].[Day].Members).item(0),[Status End].[Calendar]):NULL)
    ,Generate(existing [Status Date].[Calendar].[Day],
        {(LinkMember([Status Date].[Calendar].CurrentMember,[Incident Date].[Calendar]),[Status Date].[Calendar].[All Periods])})
, [Measures].[Status Count]),

I only want the users to select data based on the [Status Date] dimension which is why I am using the LinkMember function.
I also had to use the Generate statement so that the measure works on multiselected dates.

Now I need to add in the last piece (Reported Date) to complete the rules:
- The IncidentDate is within the time

Content page control passes as argument to javascript function


Here is the content page:


<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="JavaDynamicCopyValue.aspx.cs" Inherits="JavaDynamicCopyValue" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
 <script type="text/javascript" >
 function copyValue(input, output )
   ouput.value = input.value;
   return true;
 <asp:TextBox ID="txt1" runat="server" onchange="copyValue(this,document.getElementById('<%=txt2.ClientID%>'))"></asp:TextBox>
    <br />
    <br />
    <asp:TextBox ID="txt2" runat="server"></asp:TextBox>


I got a javascript run time error says output is undefined. if I view source of the page, the txt1 markup became:

<input name="ctl00$ContentPlaceHol

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly.


I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.

 I have two SQL Server instances installed.

 1- UserLT (this is sql 2000)
2- UserLT\SQL2005 (this is SQL 2005 named instance)

But when i try to add a database to my VS website project I get the following error:

Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925

I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)

and changed the "SQLEXPRESS" to "USERLT\SQL2005".

But I still get the same error message. Any ideas how i can resolve this issue?

Public Shared function doubt????

I am using VB.net/ASP.NET and SQL Server 2000 for a web application.

For populating the dropdowns, I wrote a Public Shared function LoadDDL(), in which I pass the dataset and dropdown name to set.

I have my doubts that this function will produce unexpected results for concurrent users. And will mix the results among sessions because it is a Shared function.

I am confused here. Is it the right way to go?

Please advice. Thanks in advance.


Trim function in C Sharp

How do i use "trim" function in C Sharp? I have a string and i want to strip off spaces from the end of the string. Any idea how do i do it??


Hierarchy ID: Model Your Data Hierarchies With SQL Server 2008


Here we explain how the new hierarchyID data type in SQL Server 2008 helps solve some of the problems in modeling and querying hierarchical information.

Kent Tegels

MSDN Magazine September 2008

Pure C++: Invoking Generic or Function Templates


As I promised last time, in this month's column I'll walk through the process of defining and invoking a generic or template function under C++\CLI. A function template or a generic function begins with the template or generic keyword followed by its parameter list.

Stanley B. Lippman

MSDN Magazine October 2005

Test Run: The Analytic Hierarchy Process


Most software testing takes place at a relatively low level. Testing an application's individual methods for functional correctness is one example. However, some important testing must take place at a very high level-for example, determining if a current build is significantly better overall than a previous build.

James McCaffrey

MSDN Magazine June 2005

Cutting Edge: Using an Eval Function in Web Services


Web Services are often presented as the perfect tool for pro-grammers. They're interoperable, based on open standards such as SOAP and WSDL, and are fully integrated with the Microsoft® . NET platform.

Dino Esposito

MSDN Magazine September 2002

way of calling "javascript function of parent window from child window."

The article way of calling "javascript function of parent window from child window." was added by raja.s on Sunday, June 06, 2010.

Hi,Here i am going to explain how to call the javascript function of a parent window from the child window(pop-Up).Some may know before reading this post.Parent Java Script Function:script language="Javascript" type="text/javascript">

Need function to find difference between two dates


Hi all

I need a function written in vb to find the exact month difference between two dates.

For example

    Start Date : 01/01/2010

    End Date   : 15/02/2010

 then the difference value should be like this 1.5  i.e i need the difference in decimal value.

>>>How to use javascript function in aspx file?



I have a file util.js and there some funtions in same file.

For example functions name are keyUp, keyDown, isNumeric, isAlphaNumeric

I have used these functions in aspx code behind in a textbox to restrict user that he may not be able to input any special character.

Before, I used the code directly in aspx file but I have do it for different textboxes therefore to avoid much coding in aspx file I created util.js and put all code in this file but how to use these particular functions in aspx file I couldn't find the way.

Anyone have idea. Do share with me.

I used the reference like this:


' Font-Names="Verdana" Font-Size="8pt" Onkeyup= "keyUp(event.keyCode)" Onkeydown="return isAlphaNumeric(event.keyCode);" onpaste="return false;">

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend