I have to populate a pivot table. I am using source of the pivot table as an analysis cube. My code is similar to this :
pivotCache.CommandText = "<name of the cube>";
pivotCache.CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdCube;
Now
my requirement is to show a filtered cube, not the whole cube. I am
using SQL server 2000 Analysis Services to prepare and store the cube.
I
think an MDX query as commandText can do this. But I am not being able
to write the suitable MDX that can give a filtered cube which I can
use to populate pivot table?
I have used MDX query as "select
from <cube name> where <filter condition(s)>" . But it is
showing that no column found that excel can use.
What would be the currect MDX?
I dont think writing custom MDX is the way to solve your problem with OWC. You should look at the ways to use OWC built in functionality to restrict your data.
Try creating PivotTable report in Excel and then save it as a web page with interactivity. You will see Excel creating a web page with OWC built in it.
That should give you an idea how to control OWC.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||
If you want to restrict access to the cube in pivottable with OWC 11, you have to do this with permissions of the SSAS. I am using SSAS 2005 and you can create a Role and set exactly which dimensions and which members within a level the customer is able to access. I thought at the beginning that I have to write mdx and set it as CommantText or Mdx property of the pivottable, but this is not the case. You have to do it with access security of the Analysis Services.
There is something more that I found about filtering data in pivottable, but this is not what you need. You can set which fields could be included or excluded programatically, but the user still can change these values (at least I think so). The code is like this:
Code Snippet
varArray = Array("Bikes")
pivotTable.ActiveView.FieldSets("Category").Fields("Category").IncludedMembers = varArray
