Hi all, I want to execute a MDX query in excel. The results from the query should change based on value in a cell in excel. For Example, The results from the mdx are internet sales $1000 and value in refernce cell C22 is 100, then the results should display $1100. So , I want get a new calculated member based on the values in a specified excel cell. Any help Much Appreciated !!!! Thanks,
Create a pivot table in excel to return the internet sales. You cannot change the pivot table values so hide the columns that make up the table. Create another excel table with cells that reference the hidden pivot table.
In 2007 and 2010 there is a possibility with a little workaround. Excel 2007 and above allows a Custom calculated formulas in Pivot Tables, although those calculated formulas doesn't allows references to cells, this can be handled by VBA. So say, you have a "PivotTable1" which is result of a MDX Query and it has a measure "Internet Sales". You then add a Custom Calculated Field named "Internet Sales Altered" with formula "`= 'Internet Sales'`". Then you want that the calculated field reflects changes to the value in the cell C22. Then you write a simple handler for that: Private Sub Worksheet_Change(ByVal Target As Range) If (Target.Address = "$C$22") Then Dim p As PivotTable Dim f As PivotField Set p = Me.PivotTables("PivotTable1") Set f = p.CalculatedFields.Item("Internet Sales Altered") f.Formula = "= 'Internet Sales' + " & Target.Value End If End Sub After this the pivot calculated field will reflect the changes to the cell C22 and automatically add that value to the Internet Sales measure.