public static void AddMultiplePageFieldFilterToDic(IEnumerable <string> pivotFieldNames, string ptMdx,
PivotCellDictionary pivotCellDic)
{
var daxFilterList = DaxDrillParser.ConvertPivotTableMdxToDaxFilterList(ptMdx, pivotFieldNames);
DaxDrillParser.ConvertDaxFilterListToDictionary(daxFilterList, pivotCellDic.MultiSelectDictionary);
}
private static void AddMultiplePageFieldFilterToDic(Excel.PivotTable pt, PivotCellDictionary pivotCellDic)
{
var mdxString = pt.MDX;
var pivotFields = pt.PivotFields();
var pivotFieldNames = new List <string>();
foreach (Excel.PivotField pf in pivotFields)
{
pivotFieldNames.Add(pf.Name);
}
AddMultiplePageFieldFilterToDic(pivotFieldNames, mdxString, pivotCellDic);
}
public static PivotCellDictionary GetPivotCellQuery(Excel.Range rngCell)
{
Excel.PivotTable pt = rngCell.PivotTable;
Excel.PivotCell pc = rngCell.PivotCell; //Field values
Excel.PivotFields pgfs = (Excel.PivotFields)(pt.PageFields);
var pivotCellDic = new PivotCellDictionary();
AddSingleAxisFiltersToDic(pc, pivotCellDic);
AddSinglePageFieldFiltersToDic(pgfs, pivotCellDic);
AddMultiplePageFieldFilterToDic(pt, pivotCellDic);
return(pivotCellDic);
}
private static void AddSingleAxisFiltersToDic(Excel.PivotCell pc, PivotCellDictionary pivotCellDic)
{
Dictionary <string, string> singDic = pivotCellDic.SingleSelectDictionary;
//Filter by Row and ColumnFields - note, we don't need a loop here but will use one just in case
foreach (Excel.PivotItem pi in pc.RowItems)
{
Excel.PivotField pf = (Excel.PivotField)pi.Parent;
singDic.Add(pf.Name, pi.SourceName.ToString());
}
foreach (Excel.PivotItem pi in pc.ColumnItems)
{
Excel.PivotField pf = (Excel.PivotField)pi.Parent;
singDic.Add(pf.Name, pi.SourceName.ToString());
}
}
private static void AddSinglePageFieldFiltersToDic(Excel.PivotFields pfs, PivotCellDictionary pivotCellDic)
{
//Filter by page field if not all items are selected
foreach (Excel.PivotField pf in pfs)
{
var dicCell = pivotCellDic.SingleSelectDictionary;
if (DaxFilterCreator.PivotFieldIsHierarchy(pf.SourceName))
{
continue;
}
string pageName = pf.DataRange.Value2;
if (pageName == "All" || pageName == "(Multiple Items)")
{
continue;
}
var cubeField = pf.CubeField;
dicCell.Add(pf.Name, cubeField.Name + ".&[" + pageName + "]");
}
}