Error when using "WITH MEMBER" in MDX query

[Bas Nielander]

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]

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<string, object> Initialize(Dictionary<string, object> 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.