We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Error when using "WITH MEMBER" in MDX query

Thread ID:

Created:

Updated:

Platform:

Replies:

128511 Jan 26,2017 03:54 AM Jan 27,2017 04:26 AM JavaScript 1
loading
Tags: ejPivotGrid
Bas Nielander
Asked On January 26, 2017 03:54 AM

Hello,

In a SharePoint webpage we like to show a Pivotgrid widget. The widgets retrieve (Olap) data from a WebApi service. All labels on the page should be shown in Dutch (locale = 1043). This is no problem, but we also like to show currency values in the pivotgrid with the proper formatting (€ #.##0,00). 
The currency values in the OLAP cube are not formatted as currency but as double and the cube will not be changed for our sake. 
As a solution a like to format the currency values using the "WITH MEMBERS" statement in the MDX query that is used to create the OLAP Report. But the query returns "Error" values in the pivotgrid. The query itself does not seem to be the problem, it is validated with SQL Server Management Studio.

The problem can be reproduced using the Adventure Works cube with the following MDX query:
string MDXQuery = "WITH MEMBER [Measures].[OrderQuantityCurr] AS [Measures].[Order Quantity], LANGUAGE=1043, FORMAT_STRING='Currency'  " + 
                              "SELECT NON EMPTY { [Measures].[OrderQuantityCurr] } ON COLUMNS, " + 
                              "       NON EMPTY { ([Sales Territory].[Sales Territory].[Region].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS " + 
                              "FROM [Adventure Works] " + 
                              "CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS";
Note, [Order Quantity] is not a currency value, but it's datatype is Double so it is possible to format it as a currency.

When this query is run using the Adomd client assemblies, the Order Quantity is shown correctly in Dutch currency format. Run from the SharePoint webpage only "Error" is shown as Order Quantity values. What strikes are the AdomdErrorResponseException's that show in the Debug window when the statement PivotGrid.GetJsonData is executed in the WebApi service.

The attachment contains a test console application, the pivotgrid controller class and several screenprints.


Question, what is going wrong? How can we show double values from a Olap cube in Dutch format in a ejPivotGrid widget?


With kind regards,

Bas


Attachment: Locale_issue_c4b86c34.zip

Sastha Prathap Selvamoorthy [Syncfusion]
Replied On January 27, 2017 04:26 AM

Hi Nielander,   
    
The reported problem occurs when the MDX Query has been provided directly to the property “OlapDataManager.MdxQuery”. So, you have to use the below MDX Query in “BeforeMDXQueryExecute” event to create the PivotGrid widget. Please find the sample code snippet for your reference.   
 
[AcceptVerbs("Post")]   
        public Dictionary<stringobject> Initialize(Dictionary<stringobject> jsonResult)   
        {   
            OlapDataManager DataManager = null;   
            //..   
            DataManager = new OlapDataManager(connectionString);   
            DataManager.SetCurrentReport(CreateOlapReport());   
            DataManager.BeforeMdxQueryExecute += DataManager_BeforeMdxQueryExecute;   
            DataManager.OverrideDefaultFormatStrings = true;   
            return htmlHelper.GetJsonData(jsonResult["action"].ToString(), DataManager, jsonResult.ContainsKey("gridLayout") ? jsonResult["gridLayout"].ToString() : null,Convert.ToBoolean(jsonResult["enablePivotFieldList"].ToString()));   
        }   
   
       void DataManager_BeforeMdxQueryExecute(object sender,QueryExecutingEventArgs e)   
        {   
            e.MdxQuery = "WITH MEMBER [Measures].[OrderQuantityCurr] AS [Measures].[Order Quantity], LANGUAGE=1043, FORMAT_STRING='Currency'  " +   
                       "SELECT NON EMPTY { [Measures].[OrderQuantityCurr] } ON COLUMNS, " +   
                       "       NON EMPTY { ([Sales Territory].[Sales Territory].[Region].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS " +   
                       "FROM [Adventure Works] " +   
                       "CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS";   
        }   
   
   private OlapReport CreateOlapReport()   
        {   
            OlapReport olapReport = new OlapReport();   
            olapReport.CurrentCubeName = "Adventure Works";   
            {   
                MeasureElements measureElement = new MeasureElements();   
                measureElement.Elements.Add(new MeasureElement { UniqueName = "[Measures].[Customer Count]" });   
   
                DimensionElement dimensionElementRow = newDimensionElement();   
                dimensionElementRow.Name = "Date";   
                dimensionElementRow.AddLevel("Fiscal""Fiscal Year");   
   
                DimensionElement dimensionElementColumn = newDimensionElement();   
                dimensionElementColumn.Name = "Customer";   
                dimensionElementColumn.AddLevel("Customer Geography","Country");   
   
                olapReport.SeriesElements.Add(dimensionElementRow);   
                olapReport.CategoricalElements.Add(dimensionElementColumn);   
                olapReport.CategoricalElements.Add(measureElement);   
            }   
            return olapReport;   
        }   
  
   
Note: The “BeforeMDXQueryExecute” executes the query along with OLAP report only.   
   
Also, we have provided “Number Formatting” support for Olap Datasource for PivotGrid control. Using this, you can change the number format for the provided Measure elements in the OLAP report without using MDX Query. This feature will be available in the 2017 Volume 1 release, which is estimated to be rolled out in mid-February 2017.   
   
Please let us know if you any other concern.   
   
Regards,   
Sastha Prathap S.   


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;