none
OpenXml Excel : Pivot - Multi-value parameters RRS feed

  • Question

  • Hi,

    I have created Excel template (.xltx) that has pivot table, which is bound to a cube (analytic services).

    I'm using OpenXml to change value of parameters.

    I make changes in PageFields, and CellMetaData

    foreach (WorksheetPart wsPart in m_Doc.WorkbookPart.GetPartsOfType<WorksheetPart>())
          {
            if (wsPart.PivotTableParts.Count() > 0)
            {
              foreach (PivotTablePart ptp in wsPart.PivotTableParts)
              {
                foreach (PageField pf in ptp.pivotTableDefinition.PageFields)
                {
                  foreach (string oldFilter in m_Filters.Keys)
                  {
                    string newFilter = m_Filters[oldFilter];
    
                    if (pf.Name.Value.Contains(oldFilter))
                    {
                      pf.Name.Value = newFilter;// 
                      break;
                    }
                  }
                }
    
                ptp.pivotTableDefinition.Save();
              }
            }
    
            wsPart.Worksheet.Save();
          }

    m_Filters is collection of Filter values

     

     

    foreach
     (CellMetadataPart wsPart in
     m_Doc.WorkbookPart.GetPartsOfType<CellMetadataPart>())
       {
        MetadataStrings ms = wsPart.Metadata.OfType<MetadataStrings>().FirstOrDefault();
        if
     (ms != null
    )
        {
         foreach
     (CharacterValue cvMs in
     ms)
         {
          foreach
     (string
     oldFilter in
     m_Filters.Keys)
          {
           string
     newFilter = m_Filters[oldFilter];
    
           if
     (cvMs.Val.Value.Contains(oldFilter))
           {
            cvMs.Val.Value = newFilter;
            break
    ;
           }
          }
         }
        }
    
        wsPart.Metadata.Save();
       }
    

     

    If parameter value has single value e.g.

    [Employee].[Employee_ID].&[2] everything works fine.

    But if parameter has multiple values

    {[Employee].[Employee_ID].&[2],[Employee].[Employee_ID].&[5]}

     

    I get parser exception when I open Excel.

     

    Any suggestions?

    • Edited by Milan Erić Saturday, April 17, 2010 8:53 AM Code fix
    Saturday, April 17, 2010 8:50 AM

All replies

  • Hi AMMilan Erić

    Thanks for your question.

    I'm not quite sure whether your statement of "parameter has multiple values" means "Select Multiple Items" in the "Report Filter" column in Excel UI. If so, you could make a template of a spreadsheet with a privot table that "select multiple items" in the "report filter" and "Reflect Code" with the Productivity Tool to see the how to achieve it in detail especially for "/xl/pivotTables/pivotTable1.xml". If I have misunderstood your requirements, could you describe it in detail?

    Hope this helps. If you have any question, please let me know.

    Thanks,

    Lu

    Friday, April 30, 2010 6:17 AM