PowerPivot to consolidate several sharepoint lists into one table - error on £0 values RRS feed

  • Question

  • I have 6 identical sharepoint lists that i want to consolidate into one so that i can plot a pivot table as if it is one data set. They are business unit specific sales pipeline lists and need to be seperate in sharepoint to allow the level of control i need over permissions. I need to report them as one divisional level list however and wanted to automate the consolidation i have been doing manually.

    I am using PowerPivot to do this and have a UNION ALL SQL command. The issue i am getting is that i have £0 values in some of the columns in the data set and PowerPivot cannot cope with them. These columns are mandatory in sharepoint and set to currency as their format. If i put £1 in instead of £0 the query works fine. I cannot simply change it to a text field as then the pivot table cannot sum these values.

    How do i get PowerPivot to cope with £0's in a column?

    Monday, February 4, 2013 11:10 AM