question

sam21 avatar image
sam21 asked

MDX Query with excel take values from excel

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,
ssasexcelmdx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Scot!
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Pavel !!!
0 Likes 0 ·
+1 nice job Pavel, I normally do the dynamic MDX in performance point or reporting svcs and tend to overlook VBA
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.