sc/qa/unit/ucalc_pivottable.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*
   3  * This file is part of the LibreOffice project.
   4  *
   5  * This Source Code Form is subject to the terms of the Mozilla Public
   6  * License, v. 2.0. If a copy of the MPL was not distributed with this
   7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8  */
   9
  10 #include <rtl/math.hxx>
  11 #include "helper/qahelper.hxx"
  12 #include <dpshttab.hxx>
  13 #include <dpobject.hxx>
  14 #include <dpsave.hxx>
  15 #include <dpdimsave.hxx>
  16 #include <scopetools.hxx>
  17 #include <stringutil.hxx>
  18 #include <dbdocfun.hxx>
  19 #include <generalfunction.hxx>
  20
  21 #include <formula/errorcodes.hxx>
  22 #include <com/sun/star/sheet/DataPilotFieldGroupBy.hpp>
  23 #include <com/sun/star/sheet/DataPilotFieldReferenceType.hpp>
  24 #include <com/sun/star/sheet/DataPilotFieldReferenceItemType.hpp>
  25
  26 template<> std::string CppUnit::assertion_traits<ScDPItemData>::toString(ScDPItemData const &)
  27 { return "ScDPItemData"; } //TODO: combine with ScDPItemData::Dump?
  28
  29 using namespace ::com::sun::star;
  30
  31 namespace {
  32
  33 struct DPFieldDef
  34 {
  35     const char* pName;
  36     sheet::DataPilotFieldOrientation eOrient;
  37
  38     /**
  39      * Function for data field.  It's used only for data field.  When 0, the
  40      * default function (SUM) is used.
  41      */
  42     ScGeneralFunction eFunc;
  43     bool bRepeatItemLabels;
  44 };
  45
  46 bool checkDPTableOutput(
  47     const ScDocument* pDoc, const ScRange& aOutRange,
  48     const std::vector<std::vector<const char*>>& aOutputCheck, const char* pCaption )
  49 {
  50     return checkOutput(pDoc, aOutRange, aOutputCheck, pCaption);
  51 }
  52
  53 ScDPObject* createDPFromSourceDesc(
  54     ScDocument* pDoc, const ScSheetSourceDesc& rDesc, const DPFieldDef aFields[], size_t nFieldCount,
  55     bool bFilterButton)
  56 {
  57     ScDPObject* pDPObj = new ScDPObject(pDoc);
  58     pDPObj->SetSheetDesc(rDesc);
  59     pDPObj->SetOutRange(ScAddress(0, 0, 1));
  60
  61     ScDPSaveData aSaveData;
  62     // Set data pilot table output options.
  63     aSaveData.SetIgnoreEmptyRows(false);
  64     aSaveData.SetRepeatIfEmpty(false);
  65     aSaveData.SetColumnGrand(true);
  66     aSaveData.SetRowGrand(true);
  67     aSaveData.SetFilterButton(bFilterButton);
  68     aSaveData.SetDrillDown(true);
  69
  70     // Check the sanity of the source range.
  71     const ScRange& rSrcRange = rDesc.GetSourceRange();
  72     SCROW nRow1 = rSrcRange.aStart.Row();
  73     SCROW nRow2 = rSrcRange.aEnd.Row();
  74     CPPUNIT_ASSERT_MESSAGE("source range contains no data!", nRow2 - nRow1 > 1);
  75
  76     // Set the dimension information.
  77     for (size_t i = 0; i < nFieldCount; ++i)
  78     {
  79         OUString aDimName = OUString::createFromAscii(aFields[i].pName);
  80         ScDPSaveDimension* pDim = aSaveData.GetNewDimensionByName(aDimName);
  81         pDim->SetOrientation(aFields[i].eOrient);
  82         pDim->SetUsedHierarchy(0);
  83
  84         if (aFields[i].eOrient == sheet::DataPilotFieldOrientation_DATA)
  85         {
  86             ScGeneralFunction eFunc = ScGeneralFunction::SUM;
  87             if (aFields[i].eFunc != ScGeneralFunction::NONE)
  88                 eFunc = aFields[i].eFunc;
  89
  90             pDim->SetFunction(eFunc);
  91             pDim->SetReferenceValue(nullptr);
  92         }
  93         else
  94         {
  95             sheet::DataPilotFieldSortInfo aSortInfo;
  96             aSortInfo.IsAscending = true;
  97             aSortInfo.Mode = 2;
  98             pDim->SetSortInfo(&aSortInfo);
  99
 100             sheet::DataPilotFieldLayoutInfo aLayInfo;
 101             aLayInfo.LayoutMode = 0;
 102             aLayInfo.AddEmptyLines = false;
 103             pDim->SetLayoutInfo(&aLayInfo);
 104             sheet::DataPilotFieldAutoShowInfo aShowInfo;
 105             aShowInfo.IsEnabled = false;
 106             aShowInfo.ShowItemsMode = 0;
 107             aShowInfo.ItemCount = 0;
 108             pDim->SetAutoShowInfo(&aShowInfo);
 109             pDim->SetRepeatItemLabels(aFields[i].bRepeatItemLabels);
 110         }
 111     }
 112
 113     // Don't forget the data layout dimension.
 114     ScDPSaveDimension* pDim = aSaveData.GetDataLayoutDimension();
 115     pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
 116     pDim->SetShowEmpty(true);
 117
 118     pDPObj->SetSaveData(aSaveData);
 119     pDPObj->InvalidateData();
 120
 121     return pDPObj;
 122 }
 123
 124 ScDPObject* createDPFromRange(
 125     ScDocument* pDoc, const ScRange& rRange, const DPFieldDef aFields[], size_t nFieldCount,
 126     bool bFilterButton)
 127 {
 128     ScSheetSourceDesc aSheetDesc(pDoc);
 129     aSheetDesc.SetSourceRange(rRange);
 130     return createDPFromSourceDesc(pDoc, aSheetDesc, aFields, nFieldCount, bFilterButton);
 131 }
 132
 133 ScRange refresh(ScDPObject* pDPObj)
 134 {
 135     bool bOverflow = false;
 136     ScRange aOutRange = pDPObj->GetNewOutputRange(bOverflow);
 137     CPPUNIT_ASSERT_MESSAGE("Table overflow!?", !bOverflow);
 138
 139     pDPObj->Output(aOutRange.aStart);
 140     aOutRange = pDPObj->GetOutRange();
 141     return aOutRange;
 142 }
 143
 144 ScRange refreshGroups(ScDPCollection* pDPs, ScDPObject* pDPObj)
 145 {
 146     // We need to first create group data in the cache, then the group data in
 147     // the object.
 148     o3tl::sorted_vector<ScDPObject*> aRefs;
 149     bool bSuccess = pDPs->ReloadGroupsInCache(pDPObj, aRefs);
 150     CPPUNIT_ASSERT_MESSAGE("Failed to reload group data in cache.", bSuccess);
 151     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be only one table linked to this cache.", size_t(1), aRefs.size());
 152     pDPObj->ReloadGroupTableData();
 153
 154     return refresh(pDPObj);
 155 }
 156
 157 }
 158
 159 class TestPivottable : public ScUcalcTestBase
 160 {
 161 public:
 162     /**
 163      * Basic test for pivot tables.
 164      */
 165     void testPivotTable();
 166
 167     /**
 168      * Test against unwanted automatic format detection on field names and
 169      * field members in pivot tables.
 170      */
 171     void testPivotTableLabels();
 172
 173     /**
 174      * Make sure that we set cells displaying date values numeric cells,
 175      * rather than text cells.  Grouping by date or number functionality
 176      * depends on this.
 177      */
 178     void testPivotTableDateLabels();
 179
 180     /**
 181      * Test for pivot table's filtering functionality by page fields.
 182      */
 183     void testPivotTableFilters();
 184
 185     /**
 186      * Test for pivot table's named source range.
 187      */
 188     void testPivotTableNamedSource();
 189
 190     /**
 191      * Test for pivot table cache.  Each dimension in the pivot cache stores
 192      * only unique values that are sorted in ascending order.
 193      */
 194     void testPivotTableCache();
 195
 196     /**
 197      * Test for pivot table containing data fields that reference the same
 198      * source field but different functions.
 199      */
 200     void testPivotTableDuplicateDataFields();
 201
 202     void testPivotTableNormalGrouping();
 203     void testPivotTableNumberGrouping();
 204     void testPivotTableDateGrouping();
 205     void testPivotTableEmptyRows();
 206     void testPivotTableTextNumber();
 207
 208     /**
 209      * Test for checking that pivot table treats strings in a case insensitive
 210      * manner.
 211      */
 212     void testPivotTableCaseInsensitiveStrings();
 213
 214     /**
 215      * Test for pivot table's handling of double-precision numbers that are
 216      * very close together.
 217      */
 218     void testPivotTableNumStability();
 219
 220     /**
 221      * Test for pivot table that include field with various non-default field
 222      * references.
 223      */
 224     void testPivotTableFieldReference();
 225
 226     /**
 227      * Test pivot table functionality performed via ScDBDocFunc.
 228      */
 229     void testPivotTableDocFunc();
 230     void testFuncGETPIVOTDATA();
 231     void testFuncGETPIVOTDATALeafAccess();
 232
 233     /**
 234      * Test pivot table per-field repeat item labels functionality
 235      */
 236     void testPivotTableRepeatItemLabels();
 237
 238     /**
 239      * Test DPCollection public methods
 240      */
 241     void testPivotTableDPCollection();
 242
 243     /**
 244     * Test pivot table median function
 245     */
 246     void testPivotTableMedianFunc();
 247
 248     CPPUNIT_TEST_SUITE(TestPivottable);
 249
 250     CPPUNIT_TEST(testPivotTable);
 251     CPPUNIT_TEST(testPivotTableLabels);
 252     CPPUNIT_TEST(testPivotTableDateLabels);
 253     CPPUNIT_TEST(testPivotTableFilters);
 254     CPPUNIT_TEST(testPivotTableNamedSource);
 255     CPPUNIT_TEST(testPivotTableCache);
 256     CPPUNIT_TEST(testPivotTableDuplicateDataFields);
 257     CPPUNIT_TEST(testPivotTableNormalGrouping);
 258     CPPUNIT_TEST(testPivotTableNumberGrouping);
 259     CPPUNIT_TEST(testPivotTableDateGrouping);
 260     CPPUNIT_TEST(testPivotTableEmptyRows);
 261     CPPUNIT_TEST(testPivotTableTextNumber);
 262     CPPUNIT_TEST(testPivotTableCaseInsensitiveStrings);
 263     CPPUNIT_TEST(testPivotTableNumStability);
 264     CPPUNIT_TEST(testPivotTableFieldReference);
 265     CPPUNIT_TEST(testPivotTableDocFunc);
 266     CPPUNIT_TEST(testFuncGETPIVOTDATA);
 267     CPPUNIT_TEST(testFuncGETPIVOTDATALeafAccess);
 268     CPPUNIT_TEST(testPivotTableRepeatItemLabels);
 269     CPPUNIT_TEST(testPivotTableDPCollection);
 270     CPPUNIT_TEST(testPivotTableMedianFunc);
 271
 272     CPPUNIT_TEST_SUITE_END();
 273
 274 private:
 275     template<size_t Size>
 276     ScRange insertDPSourceData(ScDocument* pDoc, DPFieldDef const aFields[], size_t nFieldCount, const char* aData[][Size], size_t nDataCount);
 277 };
 278
 279 template<size_t Size>
 280 ScRange TestPivottable::insertDPSourceData(ScDocument* pDoc, DPFieldDef const aFields[], size_t nFieldCount, const char* aData[][Size], size_t nDataCount)
 281 {
 282     // Insert field names in row 0.
 283     for (size_t i = 0; i < nFieldCount; ++i)
 284         pDoc->SetString(static_cast<SCCOL>(i), 0, 0, OUString(aFields[i].pName, strlen(aFields[i].pName), RTL_TEXTENCODING_UTF8));
 285
 286     // Insert data into row 1 and downward.
 287     for (size_t i = 0; i < nDataCount; ++i)
 288     {
 289         SCROW nRow = static_cast<SCROW>(i) + 1;
 290         for (size_t j = 0; j < nFieldCount; ++j)
 291         {
 292             SCCOL nCol = static_cast<SCCOL>(j);
 293             pDoc->SetString(
 294                 nCol, nRow, 0, OUString(aData[i][j], strlen(aData[i][j]), RTL_TEXTENCODING_UTF8));
 295         }
 296     }
 297
 298     SCROW nRow1 = 0, nRow2 = 0;
 299     SCCOL nCol1 = 0, nCol2 = 0;
 300     pDoc->GetDataArea(0, nCol1, nRow1, nCol2, nRow2, true, false);
 301     CPPUNIT_ASSERT_EQUAL_MESSAGE("Data is expected to start from (col=0,row=0).", SCCOL(0), nCol1);
 302     CPPUNIT_ASSERT_EQUAL_MESSAGE("Data is expected to start from (col=0,row=0).", SCROW(0), nRow1);
 303     CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected data range.",
 304                            static_cast<SCCOL>(nFieldCount - 1), nCol2);
 305     CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected data range.",
 306                            static_cast<SCROW>(nDataCount), nRow2);
 307
 308     ScRange aSrcRange(nCol1, nRow1, 0, nCol2, nRow2, 0);
 309     printRange(pDoc, aSrcRange, "Data sheet content");
 310     return aSrcRange;
 311 }
 312
 313
 314 void TestPivottable::testPivotTable()
 315 {
 316     m_pDoc->InsertTab(0, "Data");
 317     m_pDoc->InsertTab(1, "Table");
 318
 319     // Dimension definition
 320     static const DPFieldDef aFields[] = {
 321         { "Name",  sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
 322         { "Group", sheet::DataPilotFieldOrientation_COLUMN, ScGeneralFunction::NONE, false },
 323         { "Score", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false }
 324     };
 325
 326     // Raw data
 327     const char* aData[][3] = {
 328         { "Andy",    "A", "30" },
 329         { "Bruce",   "A", "20" },
 330         { "Charlie", "B", "45" },
 331         { "David",   "B", "12" },
 332         { "Edward",  "C",  "8" },
 333         { "Frank",   "C", "15" },
 334     };
 335
 336     size_t nFieldCount = SAL_N_ELEMENTS(aFields);
 337     size_t const nDataCount = SAL_N_ELEMENTS(aData);
 338
 339     ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
 340     SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
 341     SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
 342
 343     ScDPObject* pDPObj = createDPFromRange(
 344         m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
 345
 346     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
 347     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
 348     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
 349                            size_t(1), pDPs->GetCount());
 350     pDPObj->SetName(pDPs->CreateNewName());
 351
 352     bool bOverflow = false;
 353     ScRange aOutRange = pDPObj->GetNewOutputRange(bOverflow);
 354     CPPUNIT_ASSERT_MESSAGE("Table overflow!?", !bOverflow);
 355
 356     pDPObj->Output(aOutRange.aStart);
 357     aOutRange = pDPObj->GetOutRange();
 358     {
 359         // Expected output table content.  0 = empty cell
 360         std::vector<std::vector<const char*>> aOutputCheck = {
 361             { "Sum - Score", "Group", nullptr, nullptr, nullptr },
 362             { "Name", "A", "B", "C", "Total Result" },
 363             { "Andy", "30", nullptr, nullptr, "30" },
 364             { "Bruce", "20", nullptr, nullptr, "20" },
 365             { "Charlie", nullptr, "45", nullptr, "45" },
 366             { "David", nullptr, "12", nullptr, "12" },
 367             { "Edward", nullptr, nullptr, "8", "8" },
 368             { "Frank", nullptr, nullptr, "15", "15" },
 369             { "Total Result", "50", "57", "23", "130" }
 370         };
 371
 372         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
 373         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
 374     }
 375     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be only one data cache.", size_t(1), pDPs->GetSheetCaches().size());
 376
 377     // Update the cell values.
 378     double aData2[] = { 100, 200, 300, 400, 500, 600 };
 379     for (size_t i = 0; i < SAL_N_ELEMENTS(aData2); ++i)
 380     {
 381         SCROW nRow = i + 1;
 382         m_pDoc->SetValue(2, nRow, 0, aData2[i]);
 383     }
 384
 385     printRange(m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), "Data sheet content (modified)");
 386
 387     // Now, create a copy of the datapilot object for the updated table, but
 388     // don't reload the cache which should force the copy to use the old data
 389     // from the cache.
 390     ScDPObject* pDPObj2 = new ScDPObject(*pDPObj);
 391     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj2));
 392
 393     aOutRange = pDPObj2->GetOutRange();
 394     pDPObj2->ClearTableData();
 395     pDPObj2->Output(aOutRange.aStart);
 396     {
 397         // Expected output table content.  0 = empty cell
 398         std::vector<std::vector<const char*>> aOutputCheck = {
 399             { "Sum - Score", "Group", nullptr, nullptr, nullptr },
 400             { "Name", "A", "B", "C", "Total Result" },
 401             { "Andy", "30", nullptr, nullptr, "30" },
 402             { "Bruce", "20", nullptr, nullptr, "20" },
 403             { "Charlie", nullptr, "45", nullptr, "45" },
 404             { "David", nullptr, "12", nullptr, "12" },
 405             { "Edward", nullptr, nullptr, "8", "8" },
 406             { "Frank", nullptr, nullptr, "15", "15" },
 407             { "Total Result", "50", "57", "23", "130" }
 408         };
 409
 410         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (from old cache)");
 411         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
 412     }
 413
 414     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be only one data cache.", size_t(1), pDPs->GetSheetCaches().size());
 415
 416     // Free the first datapilot object after the 2nd one gets reloaded, to
 417     // prevent the data cache from being deleted before the reload.
 418     pDPs->FreeTable(pDPObj);
 419
 420     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be only one data cache.", size_t(1), pDPs->GetSheetCaches().size());
 421
 422     // This time clear the cache to refresh the data from the source range.
 423     CPPUNIT_ASSERT_MESSAGE("This datapilot should be based on sheet data.", pDPObj2->IsSheetData());
 424     o3tl::sorted_vector<ScDPObject*> aRefs;
 425     TranslateId pErrId = pDPs->ReloadCache(pDPObj2, aRefs);
 426     CPPUNIT_ASSERT_EQUAL_MESSAGE("Cache reload failed.", false, bool(pErrId));
 427     CPPUNIT_ASSERT_EQUAL_MESSAGE("Reloading a cache shouldn't remove any cache.",
 428                            static_cast<size_t>(1), pDPs->GetSheetCaches().size());
 429
 430     pDPObj2->ClearTableData();
 431     pDPObj2->Output(aOutRange.aStart);
 432
 433     {
 434         // Expected output table content.  0 = empty cell
 435         std::vector<std::vector<const char*>> aOutputCheck = {
 436             { "Sum - Score", "Group", nullptr, nullptr, nullptr },
 437             { "Name", "A", "B", "C", "Total Result" },
 438             { "Andy", "100", nullptr, nullptr, "100" },
 439             { "Bruce", "200", nullptr, nullptr, "200" },
 440             { "Charlie", nullptr, "300", nullptr, "300" },
 441             { "David", nullptr, "400", nullptr, "400" },
 442             { "Edward", nullptr, nullptr, "500", "500" },
 443             { "Frank", nullptr, nullptr, "600", "600" },
 444             { "Total Result", "300", "700", "1100", "2100" }
 445         };
 446
 447         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (refreshed)");
 448         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
 449     }
 450
 451     CPPUNIT_ASSERT_MESSAGE("Cache should be here.", pDPs->GetSheetCaches().hasCache(aSrcRange));
 452
 453     // Swap the two sheets.
 454     m_pDoc->MoveTab(1, 0);
 455     CPPUNIT_ASSERT_EQUAL_MESSAGE("Swapping the sheets shouldn't remove the cache.",
 456                            size_t(1), pDPs->GetSheetCaches().size());
 457     CPPUNIT_ASSERT_MESSAGE("Cache should have moved.", !pDPs->GetSheetCaches().hasCache(aSrcRange));
 458     aSrcRange.aStart.SetTab(1);
 459     aSrcRange.aEnd.SetTab(1);
 460     CPPUNIT_ASSERT_MESSAGE("Cache should be here.", pDPs->GetSheetCaches().hasCache(aSrcRange));
 461
 462     pDPs->FreeTable(pDPObj2);
 463     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any data pilot table stored with the document.",
 464                            size_t(0), pDPs->GetCount());
 465
 466     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more data cache.",
 467                            size_t(0), pDPs->GetSheetCaches().size());
 468
 469     // Insert a brand new pivot table object once again, but this time, don't
 470     // create the output to avoid creating a data cache.
 471     m_pDoc->DeleteTab(1);
 472     m_pDoc->InsertTab(1, "Table");
 473
 474     pDPObj = createDPFromRange(
 475         m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
 476     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
 477     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
 478                            size_t(1), pDPs->GetCount());
 479     pDPObj->SetName(pDPs->CreateNewName());
 480     CPPUNIT_ASSERT_EQUAL_MESSAGE("Data cache shouldn't exist yet before creating the table output.",
 481                            size_t(0), pDPs->GetSheetCaches().size());
 482
 483     // Now, "refresh" the table.  This should still return a reference to self
 484     // even with the absence of data cache.
 485     aRefs.clear();
 486     pDPs->ReloadCache(pDPObj, aRefs);
 487     CPPUNIT_ASSERT_EQUAL_MESSAGE("It should return the same object as a reference.",
 488                            o3tl::sorted_vector<ScDPObject*>::size_type(1), aRefs.size());
 489     CPPUNIT_ASSERT_EQUAL_MESSAGE("It should return the same object as a reference.",
 490                            pDPObj, *aRefs.begin());
 491
 492     pDPs->FreeTable(pDPObj);
 493
 494     m_pDoc->DeleteTab(1);
 495     m_pDoc->DeleteTab(0);
 496 }
 497
 498 void TestPivottable::testPivotTableLabels()
 499 {
 500     m_pDoc->InsertTab(0, "Data");
 501     m_pDoc->InsertTab(1, "Table");
 502
 503     // Dimension definition
 504     static const DPFieldDef aFields[] = {
 505         { "Software", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
 506         { "Version",  sheet::DataPilotFieldOrientation_COLUMN, ScGeneralFunction::NONE, false },
 507         { "1.2.3",    sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false }
 508     };
 509
 510     // Raw data
 511     const char* aData[][3] = {
 512         { "LibreOffice", "3.3.0", "30" },
 513         { "LibreOffice", "3.3.1", "20" },
 514         { "LibreOffice", "3.4.0", "45" },
 515     };
 516
 517     size_t nFieldCount = SAL_N_ELEMENTS(aFields);
 518     size_t const nDataCount = SAL_N_ELEMENTS(aData);
 519
 520     ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
 521     SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
 522     SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
 523
 524     ScDPObject* pDPObj = createDPFromRange(
 525         m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
 526
 527     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
 528     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
 529     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
 530                            size_t(1), pDPs->GetCount());
 531     pDPObj->SetName(pDPs->CreateNewName());
 532
 533     ScRange aOutRange = refresh(pDPObj);
 534     {
 535         // Expected output table content.  0 = empty cell
 536         std::vector<std::vector<const char*>> aOutputCheck = {
 537             { "Sum - 1.2.3", "Version", nullptr, nullptr, nullptr },
 538             { "Software", "3.3.0", "3.3.1", "3.4.0", "Total Result" },
 539             { "LibreOffice", "30", "20", "45", "95" },
 540             { "Total Result", "30", "20", "45", "95" }
 541         };
 542
 543         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
 544         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
 545     }
 546
 547     pDPs->FreeTable(pDPObj);
 548
 549     m_pDoc->DeleteTab(1);
 550     m_pDoc->DeleteTab(0);
 551 }
 552
 553 void TestPivottable::testPivotTableDateLabels()
 554 {
 555     m_pDoc->InsertTab(0, "Data");
 556     m_pDoc->InsertTab(1, "Table");
 557
 558     // Dimension definition
 559     static const DPFieldDef aFields[] = {
 560         { "Name",  sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
 561         { "Date",  sheet::DataPilotFieldOrientation_COLUMN, ScGeneralFunction::NONE, false },
 562         { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false }
 563     };
 564
 565     // Raw data
 566     const char* aData[][3] = {
 567         { "Zena",   "2011-1-1", "30" },
 568         { "Yodel",  "2011-1-2", "20" },
 569         { "Xavior", "2011-1-3", "45" }
 570     };
 571
 572     size_t nFieldCount = SAL_N_ELEMENTS(aFields);
 573     size_t const nDataCount = SAL_N_ELEMENTS(aData);
 574
 575     ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
 576     SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
 577     SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
 578
 579     ScDPObject* pDPObj = createDPFromRange(
 580         m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
 581
 582     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
 583     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
 584     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
 585                            size_t(1), pDPs->GetCount());
 586     pDPObj->SetName(pDPs->CreateNewName());
 587
 588     ScRange aOutRange = refresh(pDPObj);
 589     {
 590         // Expected output table content.  0 = empty cell
 591         std::vector<std::vector<const char*>> aOutputCheck = {
 592             { "Sum - Value", "Date", nullptr, nullptr, nullptr },
 593             { "Name", "2011-01-01", "2011-01-02", "2011-01-03", "Total Result" },
 594             { "Xavior",  nullptr, nullptr, "45", "45" },
 595             { "Yodel",  nullptr, "20", nullptr, "20" },
 596             { "Zena",  "30", nullptr, nullptr, "30" },
 597             { "Total Result", "30", "20", "45", "95" }
 598         };
 599
 600         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
 601         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
 602     }
 603
 604     {
 605         const char* const aChecks[] = {
 606             "2011-01-01", "2011-01-02", "2011-01-03"
 607         };
 608
 609         // Make sure those cells that contain dates are numeric.
 610         SCROW nRow = aOutRange.aStart.Row() + 1;
 611         nCol1 = aOutRange.aStart.Col() + 1;
 612         nCol2 = nCol1 + 2;
 613         for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
 614         {
 615             OUString aVal = m_pDoc->GetString(nCol, nRow, 1);
 616             CPPUNIT_ASSERT_MESSAGE("Cell value is not as expected.", aVal.equalsAscii(aChecks[nCol-nCol1]));
 617             CPPUNIT_ASSERT_MESSAGE("This cell contains a date value and is supposed to be numeric.",
 618                                    m_pDoc->HasValueData(nCol, nRow, 1));
 619         }
 620     }
 621
 622     pDPs->FreeTable(pDPObj);
 623
 624     m_pDoc->DeleteTab(1);
 625     m_pDoc->DeleteTab(0);
 626 }
 627
 628 void TestPivottable::testPivotTableFilters()
 629 {
 630     m_pDoc->InsertTab(0, "Data");
 631     m_pDoc->InsertTab(1, "Table");
 632
 633     // Dimension definition
 634     static const DPFieldDef aFields[] = {
 635         { "Name",   sheet::DataPilotFieldOrientation_HIDDEN, ScGeneralFunction::NONE, false },
 636         { "Group1", sheet::DataPilotFieldOrientation_HIDDEN, ScGeneralFunction::NONE, false },
 637         { "Group2", sheet::DataPilotFieldOrientation_PAGE, ScGeneralFunction::NONE, false },
 638         { "Val1",   sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false },
 639         { "Val2",   sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false }
 640     };
 641
 642     // Raw data
 643     const char* aData[][5] = {
 644         { "A", "1", "A", "1", "10" },
 645         { "B", "1", "A", "1", "10" },
 646         { "C", "1", "B", "1", "10" },
 647         { "D", "1", "B", "1", "10" },
 648         { "E", "2", "A", "1", "10" },
 649         { "F", "2", "A", "1", "10" },
 650         { "G", "2", "B", "1", "10" },
 651         { "H", "2", "B", "1", "10" }
 652     };
 653
 654     size_t nFieldCount = SAL_N_ELEMENTS(aFields);
 655     size_t const nDataCount = SAL_N_ELEMENTS(aData);
 656
 657     ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
 658     SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
 659     SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
 660
 661     ScDPObject* pDPObj = createDPFromRange(
 662         m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, true);
 663
 664     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
 665     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
 666     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
 667                            size_t(1), pDPs->GetCount());
 668     pDPObj->SetName(pDPs->CreateNewName());
 669
 670     ScRange aOutRange = refresh(pDPObj);
 671     {
 672         // Expected output table content.  0 = empty cell
 673         std::vector<std::vector<const char*>> aOutputCheck = {
 674             { "Filter", nullptr },
 675             { "Group2", "- all -" },
 676             { nullptr, nullptr },
 677             { "Data", nullptr },
 678             { "Sum - Val1", "8" },
 679             { "Sum - Val2", "80" }
 680         };
 681
 682         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (unfiltered)");
 683         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
 684     }
 685
 686     sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calculation.
 687
 688     ScAddress aFormulaAddr = aOutRange.aEnd;
 689     aFormulaAddr.IncRow(2);
 690     m_pDoc->SetString(aFormulaAddr.Col(), aFormulaAddr.Row(), aFormulaAddr.Tab(),
 691                       "=B6");
 692     double fTest = m_pDoc->GetValue(aFormulaAddr);
 693     ASSERT_DOUBLES_EQUAL_MESSAGE("Incorrect formula value that references a cell in the pivot table output.", 80.0, fTest);
 694
 695     // Set current page of 'Group2' to 'A'.
 696     pDPObj->BuildAllDimensionMembers();
 697     ScDPSaveData aSaveData(*pDPObj->GetSaveData());
 698     ScDPSaveDimension* pPageDim = aSaveData.GetDimensionByName(
 699         "Group2");
 700     CPPUNIT_ASSERT_MESSAGE("Dimension not found", pPageDim);
 701     OUString aPage("A");
 702     pPageDim->SetCurrentPage(&aPage);
 703     pDPObj->SetSaveData(aSaveData);
 704     aOutRange = refresh(pDPObj);
 705     {
 706         // Expected output table content.  0 = empty cell
 707         std::vector<std::vector<const char*>> aOutputCheck = {
 708             { "Filter", nullptr },
 709             { "Group2", "A" },
 710             { nullptr, nullptr },
 711             { "Data", nullptr },
 712             { "Sum - Val1", "4" },
 713             { "Sum - Val2", "40" }
 714         };
 715
 716         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by page)");
 717         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
 718     }
 719
 720     fTest = m_pDoc->GetValue(aFormulaAddr);
 721     ASSERT_DOUBLES_EQUAL_MESSAGE("Incorrect formula value that references a cell in the pivot table output.", 40.0, fTest);
 722
 723     // Set query filter.
 724     ScSheetSourceDesc aDesc(*pDPObj->GetSheetDesc());
 725     ScQueryParam aQueryParam(aDesc.GetQueryParam());
 726     CPPUNIT_ASSERT_MESSAGE("There should be at least one query entry.", aQueryParam.GetEntryCount() > 0);
 727     ScQueryEntry& rEntry = aQueryParam.GetEntry(0);
 728     rEntry.bDoQuery = true;
 729     rEntry.nField = 1;  // Group1
 730     rEntry.GetQueryItem().mfVal = 1;
 731     aDesc.SetQueryParam(aQueryParam);
 732     pDPObj->SetSheetDesc(aDesc);
 733     aOutRange = refresh(pDPObj);
 734     {
 735         // Expected output table content.  0 = empty cell
 736         std::vector<std::vector<const char*>> aOutputCheck = {
 737             { "Filter", nullptr },
 738             { "Group2", "A" },
 739             { nullptr, nullptr },
 740             { "Data", nullptr },
 741             { "Sum - Val1", "2" },
 742             { "Sum - Val2", "20" }
 743         };
 744
 745         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by query)");
 746         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
 747     }
 748
 749     fTest = m_pDoc->GetValue(aFormulaAddr);
 750     ASSERT_DOUBLES_EQUAL_MESSAGE("Incorrect formula value that references a cell in the pivot table output.", 20.0, fTest);
 751
 752     // Set the current page of 'Group2' back to '- all -'. The query filter
 753     // should still be in effect.
 754     pPageDim->SetCurrentPage(nullptr); // Remove the page.
 755     pDPObj->SetSaveData(aSaveData);
 756     aOutRange = refresh(pDPObj);
 757     {
 758         // Expected output table content.  0 = empty cell
 759         std::vector<std::vector<const char*>> aOutputCheck = {
 760             { "Filter", nullptr },
 761             { "Group2", "- all -" },
 762             { nullptr, nullptr },
 763             { "Data", nullptr },
 764             { "Sum - Val1", "4" },
 765             { "Sum - Val2", "40" }
 766         };
 767
 768         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by page)");
 769         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
 770     }
 771
 772     pDPs->FreeTable(pDPObj);
 773     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any data pilot table stored with the document.",
 774                            size_t(0), pDPs->GetCount());
 775
 776     m_pDoc->DeleteTab(1);
 777     m_pDoc->DeleteTab(0);
 778 }
 779
 780 void TestPivottable::testPivotTableNamedSource()
 781 {
 782     m_pDoc->InsertTab(0, "Data");
 783     m_pDoc->InsertTab(1, "Table");
 784
 785     // Dimension definition
 786     static const DPFieldDef aFields[] = {
 787         { "Name",  sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
 788         { "Group", sheet::DataPilotFieldOrientation_COLUMN, ScGeneralFunction::NONE, false },
 789         { "Score", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false }
 790     };
 791
 792     // Raw data
 793     const char* aData[][3] = {
 794         { "Andy",    "A", "30" },
 795         { "Bruce",   "A", "20" },
 796         { "Charlie", "B", "45" },
 797         { "David",   "B", "12" },
 798         { "Edward",  "C",  "8" },
 799         { "Frank",   "C", "15" },
 800     };
 801
 802     size_t nFieldCount = SAL_N_ELEMENTS(aFields);
 803     size_t const nDataCount = SAL_N_ELEMENTS(aData);
 804
 805     // Insert the raw data.
 806     ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
 807     OUString aRangeStr(aSrcRange.Format(*m_pDoc, ScRefFlags::RANGE_ABS_3D));
 808
 809     // Name this range.
 810     OUString aRangeName("MyData");
 811     ScRangeName* pNames = m_pDoc->GetRangeName();
 812     CPPUNIT_ASSERT_MESSAGE("Failed to get global range name container.", pNames);
 813     ScRangeData* pName = new ScRangeData(
 814         *m_pDoc, aRangeName, aRangeStr);
 815     bool bSuccess = pNames->insert(pName);
 816     CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bSuccess);
 817
 818     ScSheetSourceDesc aSheetDesc(m_pDoc);
 819     aSheetDesc.SetRangeName(aRangeName);
 820     ScDPObject* pDPObj = createDPFromSourceDesc(m_pDoc, aSheetDesc, aFields, nFieldCount, false);
 821     CPPUNIT_ASSERT_MESSAGE("Failed to create a new pivot table object.", pDPObj);
 822
 823     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
 824     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
 825     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
 826                            size_t(1), pDPs->GetCount());
 827     pDPObj->SetName(pDPs->CreateNewName());
 828
 829     ScRange aOutRange = refresh(pDPObj);
 830     {
 831         // Expected output table content.  0 = empty cell
 832         std::vector<std::vector<const char*>> aOutputCheck = {
 833             { "Sum - Score", "Group", nullptr, nullptr, nullptr },
 834             { "Name", "A", "B", "C", "Total Result" },
 835             { "Andy", "30", nullptr, nullptr, "30" },
 836             { "Bruce", "20", nullptr, nullptr, "20" },
 837             { "Charlie", nullptr, "45", nullptr, "45" },
 838             { "David", nullptr, "12", nullptr, "12" },
 839             { "Edward", nullptr, nullptr, "8", "8" },
 840             { "Frank", nullptr, nullptr, "15", "15" },
 841             { "Total Result", "50", "57", "23", "130" }
 842         };
 843
 844         bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
 845         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
 846     }
 847
 848     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be one named range data cache.",
 849                            size_t(1), pDPs->GetNameCaches().size());
 850     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be one named range data cache.",
 851                            size_t(0), pDPs->GetSheetCaches().size());
 852
 853     // Move the table with pivot table to the left of the source data sheet.
 854     m_pDoc->MoveTab(1, 0);
 855     OUString aTabName;
 856     m_pDoc->GetName(0, aTabName);
 857     CPPUNIT_ASSERT_EQUAL_MESSAGE( "Wrong sheet name.", OUString("Table"), aTabName);
 858     CPPUNIT_ASSERT_EQUAL_MESSAGE("Pivot table output is on the wrong sheet!",
 859                            static_cast<SCTAB>(0), pDPObj->GetOutRange().aStart.Tab());
 860
 861     CPPUNIT_ASSERT_EQUAL_MESSAGE("Moving the pivot table to another sheet shouldn't have changed the cache state.",
 862                            size_t(1), pDPs->GetNameCaches().size());
 863     CPPUNIT_ASSERT_EQUAL_MESSAGE("Moving the pivot table to another sheet shouldn't have changed the cache state.",
 864                            size_t(0), pDPs->GetSheetCaches().size());
 865
 866     const ScSheetSourceDesc* pDesc = pDPObj->GetSheetDesc();
 867     CPPUNIT_ASSERT_MESSAGE("Sheet source description doesn't exist.", pDesc);
 868     CPPUNIT_ASSERT_EQUAL_MESSAGE("Named source range has been altered unexpectedly!",
 869                            pDesc->GetRangeName(), aRangeName);
 870
 871     CPPUNIT_ASSERT_MESSAGE("Cache should exist.", pDPs->GetNameCaches().hasCache(aRangeName));
 872
 873     pDPs->FreeTable(pDPObj);
 874     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
 875     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
 876                            size_t(0), pDPs->GetNameCaches().size());
 877
 878     pNames->clear();
 879     m_pDoc->DeleteTab(1);
 880     m_pDoc->DeleteTab(0);
 881 }
 882
 883 void TestPivottable::testPivotTableCache()
 884 {
 885     m_pDoc->InsertTab(0, "Data");
 886
 887     // Raw data
 888     const std::vector<std::vector<const char*>> aData = {
 889         { "F1", "F2", "F3" },
 890         { "Z",  "A", "30" },
 891         { "R",  "A", "20" },
 892         { "A",  "B", "45" },
 893         { "F",  "B", "12" },
 894         { "Y",  "C",  "8" },
 895         { "12", "C", "15" },
 896     };
 897
 898     ScAddress aPos(1,1,0);
 899     ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
 900     CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);
 901
 902     ScDPCache aCache(*m_pDoc);
 903     aCache.InitFromDoc(*m_pDoc, aDataRange);
 904     tools::Long nDimCount = aCache.GetColumnCount();
 905     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong dimension count.", tools::Long(3), nDimCount);
 906     OUString aDimName = aCache.GetDimensionName(0);
 907     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong dimension name", OUString("F1"), aDimName);
 908     aDimName = aCache.GetDimensionName(1);
 909     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong dimension name", OUString("F2"), aDimName);
 910     aDimName = aCache.GetDimensionName(2);
 911     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong dimension name", OUString("F3"), aDimName);
 912
 913     // In each dimension, member ID values also represent their sort order (in
 914     // source dimensions only, not in group dimensions). Value items are
 915     // sorted before string ones. Also, no duplicate dimension members should
 916     // exist.
 917
 918     // Dimension 0 - a mix of strings and values.
 919     tools::Long nMemCount = aCache.GetDimMemberCount(0);
 920     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong dimension member count", tools::Long(6), nMemCount);
 921     const ScDPItemData* pItem = aCache.GetItemDataById(0, 0);
 922     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
 923     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::Value, pItem->GetType());
 924     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
 925                            12.0, pItem->GetValue());
 926     pItem = aCache.GetItemDataById(0, 1);
 927     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
 928     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
 929     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
 930                            OUString("A"), pItem->GetString());
 931     pItem = aCache.GetItemDataById(0, 2);
 932     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
 933     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
 934     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
 935                            OUString("F"), pItem->GetString());
 936     pItem = aCache.GetItemDataById(0, 3);
 937     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
 938     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
 939     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
 940                            OUString("R"), pItem->GetString());
 941     pItem = aCache.GetItemDataById(0, 4);
 942     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
 943     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
 944     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
 945                            OUString("Y"), pItem->GetString());
 946     pItem = aCache.GetItemDataById(0, 5);
 947     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
 948     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
 949     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
 950                            OUString("Z"), pItem->GetString());
 951     pItem = aCache.GetItemDataById(0, 6);
 952     CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);
 953
 954     // Dimension 1 - duplicate values in source.
 955     nMemCount = aCache.GetDimMemberCount(1);
 956     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong dimension member count", tools::Long(3), nMemCount);
 957     pItem = aCache.GetItemDataById(1, 0);
 958     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
 959     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
 960     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
 961                            OUString("A"), pItem->GetString());
 962     pItem = aCache.GetItemDataById(1, 1);
 963     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
 964     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
 965     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
 966                            OUString("B"), pItem->GetString());
 967     pItem = aCache.GetItemDataById(1, 2);
 968     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
 969     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
 970     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
 971                            OUString("C"), pItem->GetString());
 972     pItem = aCache.GetItemDataById(1, 3);
 973     CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);
 974
 975     // Dimension 2 - values only.
 976     nMemCount = aCache.GetDimMemberCount(2);
 977     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong dimension member count", tools::Long(6), nMemCount);
 978     pItem = aCache.GetItemDataById(2, 0);
 979     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
 980     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::Value, pItem->GetType());
 981     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
 982                            8.0, pItem->GetValue());
 983     pItem = aCache.GetItemDataById(2, 1);
 984     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
 985     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::Value, pItem->GetType());
 986     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
 987                            12.0, pItem->GetValue());
 988     pItem = aCache.GetItemDataById(2, 2);
 989     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
 990     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::Value, pItem->GetType());
 991     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
 992                            15.0, pItem->GetValue());
 993     pItem = aCache.GetItemDataById(2, 3);
 994     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
 995     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::Value, pItem->GetType());
 996     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
 997                            20.0, pItem->GetValue());
 998     pItem = aCache.GetItemDataById(2, 4);
 999     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
1000     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::Value, pItem->GetType());
1001     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
1002                            30.0, pItem->GetValue());
1003     pItem = aCache.GetItemDataById(2, 5);
1004     CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
1005     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::Value, pItem->GetType());
1006     CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
1007                            45.0, pItem->GetValue());
1008     pItem = aCache.GetItemDataById(2, 6);
1009     CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);
1010
1011     {
1012         // Check the integrity of the source data.
1013         ScDPItemData aTest;
1014         tools::Long nDim;
1015
1016         {
1017             // Dimension 0: Z, R, A, F, Y, 12
1018             nDim = 0;
1019             const char* aChecks[] = { "Z", "R", "A", "F", "Y" };
1020             for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1021             {
1022                 pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
1023                 aTest.SetString(OUString::createFromAscii(aChecks[i]));
1024                 CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem);
1025                 CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong data value", aTest, *pItem);
1026             }
1027
1028             pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, 5, false));
1029             aTest.SetValue(12);
1030             CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem);
1031             CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong data value", aTest, *pItem);
1032         }
1033
1034         {
1035             // Dimension 1: A, A, B, B, C, C
1036             nDim = 1;
1037             const char* aChecks[] = { "A", "A", "B", "B", "C", "C" };
1038             for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1039             {
1040                 pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
1041                 aTest.SetString(OUString::createFromAscii(aChecks[i]));
1042                 CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem);
1043                 CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong data value", aTest, *pItem);
1044             }
1045         }
1046
1047         {
1048             // Dimension 2: 30, 20, 45, 12, 8, 15
1049             nDim = 2;
1050             double aChecks[] = { 30, 20, 45, 12, 8, 15 };
1051             for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
1052             {
1053                 pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
1054                 aTest.SetValue(aChecks[i]);
1055                 CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem);
1056                 CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong data value", aTest, *pItem);
1057             }
1058         }
1059     }
1060
1061     // Now, on to testing the filtered cache.
1062
1063     {
1064         // Non-filtered cache - everything should be visible.
1065         ScDPFilteredCache aFilteredCache(aCache);
1066         aFilteredCache.fillTable();
1067
1068         sal_Int32 nRows = aFilteredCache.getRowSize();
1069         CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong dimension.", sal_Int32(6), nRows);
1070         CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong dimension.", sal_Int32(3), aFilteredCache.getColSize());
1071
1072         for (sal_Int32 i = 0; i < nRows; ++i)
1073         {
1074             if (!aFilteredCache.isRowActive(i))
1075             {
1076                 std::ostringstream os;
1077                 os << "Row " << i << " should be visible but it isn't.";
1078                 CPPUNIT_ASSERT_MESSAGE(os.str(), false);
1079             }
1080         }
1081     }
1082
1083     // TODO : Add test for filtered caches.
1084
1085     m_pDoc->DeleteTab(0);
1086 }
1087
1088 void TestPivottable::testPivotTableDuplicateDataFields()
1089 {
1090     m_pDoc->InsertTab(0, "Data");
1091     m_pDoc->InsertTab(1, "Table");
1092
1093     // Raw data
1094     const std::vector<std::vector<const char*>> aData = {
1095         { "Name", "Value" },
1096         { "A",       "45" },
1097         { "A",        "5" },
1098         { "A",       "41" },
1099         { "A",       "49" },
1100         { "A",        "4" },
1101         { "B",       "33" },
1102         { "B",       "84" },
1103         { "B",       "74" },
1104         { "B",        "8" },
1105         { "B",       "68" }
1106     };
1107
1108     // Dimension definition
1109     static const DPFieldDef aFields[] = {
1110         { "Name",  sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
1111         { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
1112         { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::COUNT, false }
1113     };
1114
1115     ScAddress aPos(2,2,0);
1116     ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
1117     CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);
1118
1119     ScDPObject* pDPObj = createDPFromRange(
1120         m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
1121
1122     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
1123     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
1124     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
1125                            size_t(1), pDPs->GetCount());
1126     pDPObj->SetName(pDPs->CreateNewName());
1127
1128     ScRange aOutRange = refresh(pDPObj);
1129     {
1130         // Expected output table content.  0 = empty cell
1131         std::vector<std::vector<const char*>> aOutputCheck = {
1132             { "Name", "Data", nullptr },
1133             { "A", "Sum - Value", "144" },
1134             { nullptr, "Count - Value", "5" },
1135             { "B", "Sum - Value", "267" },
1136             { nullptr, "Count - Value", "5" },
1137             { "Total Sum - Value", nullptr, "411" },
1138             { "Total Count - Value", nullptr, "10" },
1139         };
1140
1141         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
1142         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1143     }
1144
1145     // Move the data layout dimension from row to column.
1146     ScDPSaveData* pSaveData = pDPObj->GetSaveData();
1147     CPPUNIT_ASSERT_MESSAGE("No save data!?", pSaveData);
1148     ScDPSaveDimension* pDataLayout = pSaveData->GetDataLayoutDimension();
1149     CPPUNIT_ASSERT_MESSAGE("No data layout dimension.", pDataLayout);
1150     pDataLayout->SetOrientation(sheet::DataPilotFieldOrientation_COLUMN);
1151     pDPObj->SetSaveData(*pSaveData);
1152
1153     // Refresh the table output.
1154     aOutRange = refresh(pDPObj);
1155     {
1156         // Expected output table content.  0 = empty cell
1157         std::vector<std::vector<const char*>> aOutputCheck = {
1158             { nullptr, "Data", nullptr },
1159             { "Name", "Sum - Value", "Count - Value" },
1160             { "A", "144", "5" },
1161             { "B", "267", "5" },
1162             { "Total Result", "411", "10" }
1163         };
1164
1165         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
1166         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1167     }
1168
1169     ScPivotParam aParam;
1170     pDPObj->FillLabelData(aParam);
1171     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be exactly 4 labels (2 original, 1 data layout, and 1 duplicate dimensions).",
1172                            size_t(4), aParam.maLabelArray.size());
1173
1174     pDPs->FreeTable(pDPObj);
1175     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
1176     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
1177                            size_t(0), pDPs->GetSheetCaches().size());
1178
1179     m_pDoc->DeleteTab(1);
1180     m_pDoc->DeleteTab(0);
1181 }
1182
1183 void TestPivottable::testPivotTableNormalGrouping()
1184 {
1185     m_pDoc->InsertTab(0, "Data");
1186     m_pDoc->InsertTab(1, "Table");
1187
1188     // Raw data
1189     const std::vector<std::vector<const char*>> aData = {
1190         { "Name", "Value" },
1191         { "A", "1" },
1192         { "B", "2" },
1193         { "C", "3" },
1194         { "D", "4" },
1195         { "E", "5" },
1196         { "F", "6" },
1197         { "G", "7" }
1198     };
1199
1200     // Dimension definition
1201     static const DPFieldDef aFields[] = {
1202         { "Name",  sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
1203         { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
1204     };
1205
1206     ScAddress aPos(1,1,0);
1207     ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
1208     CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);
1209
1210     ScDPObject* pDPObj = createDPFromRange(
1211         m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
1212
1213     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
1214     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
1215     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
1216                            size_t(1), pDPs->GetCount());
1217     pDPObj->SetName(pDPs->CreateNewName());
1218
1219     ScRange aOutRange = refresh(pDPObj);
1220     {
1221         // Expected output table content.  0 = empty cell
1222         std::vector<std::vector<const char*>> aOutputCheck = {
1223             { "Name", "Sum - Value" },
1224             { "A", "1" },
1225             { "B", "2" },
1226             { "C", "3" },
1227             { "D", "4" },
1228             { "E", "5" },
1229             { "F", "6" },
1230             { "G", "7" },
1231             { "Total Result", "28" }
1232         };
1233
1234         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Initial output without grouping");
1235         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1236     }
1237
1238     ScDPSaveData* pSaveData = pDPObj->GetSaveData();
1239     CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
1240     ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
1241     CPPUNIT_ASSERT_MESSAGE("Failed to create dimension data.", pDimData);
1242
1243     OUString aGroupPrefix("Group");
1244     OUString aBaseDimName("Name");
1245     OUString aGroupDimName =
1246         pDimData->CreateGroupDimName(aBaseDimName, *pDPObj, false, nullptr);
1247
1248     {
1249         // Group A, B and C together.
1250         ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
1251         OUString aGroupName = aGroupDim.CreateGroupName(aGroupPrefix);
1252         CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected group name", OUString("Group1"), aGroupName);
1253
1254         ScDPSaveGroupItem aGroup(aGroupName);
1255         aGroup.AddElement("A");
1256         aGroup.AddElement("B");
1257         aGroup.AddElement("C");
1258         aGroupDim.AddGroupItem(aGroup);
1259         pDimData->AddGroupDimension(aGroupDim);
1260
1261         ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
1262         pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
1263         pSaveData->SetPosition(pDim, 0); // Set it before the base dimension.
1264     }
1265
1266     pDPObj->SetSaveData(*pSaveData);
1267     aOutRange = refreshGroups(pDPs, pDPObj);
1268     {
1269         // Expected output table content.  0 = empty cell
1270         std::vector<std::vector<const char*>> aOutputCheck = {
1271             { "Name2", "Name", "Sum - Value" },
1272             { "D", "D", "4" },
1273             { "E", "E", "5" },
1274             { "F", "F", "6" },
1275             { "G", "G", "7" },
1276             { "Group1", "A", "1" },
1277             { nullptr,        "B", "2" },
1278             { nullptr,        "C", "3" },
1279             { "Total Result", nullptr, "28" }
1280         };
1281
1282         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "A, B, C grouped by Group1.");
1283         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1284     }
1285
1286     pSaveData = pDPObj->GetSaveData();
1287     pDimData = pSaveData->GetDimensionData();
1288
1289     {
1290         // Group D, E, F together.
1291         ScDPSaveGroupDimension* pGroupDim = pDimData->GetGroupDimAccForBase(aBaseDimName);
1292         CPPUNIT_ASSERT_MESSAGE("There should be an existing group dimension.", pGroupDim);
1293         OUString aGroupName = pGroupDim->CreateGroupName(aGroupPrefix);
1294         CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected group name", OUString("Group2"), aGroupName);
1295
1296         ScDPSaveGroupItem aGroup(aGroupName);
1297         aGroup.AddElement("D");
1298         aGroup.AddElement("E");
1299         aGroup.AddElement("F");
1300         pGroupDim->AddGroupItem(aGroup);
1301     }
1302
1303     pDPObj->SetSaveData(*pSaveData);
1304     aOutRange = refreshGroups(pDPs, pDPObj);
1305     {
1306         // Expected output table content.  0 = empty cell
1307         std::vector<std::vector<const char*>> aOutputCheck = {
1308             { "Name2", "Name", "Sum - Value" },
1309             { "G", "G", "7" },
1310             { "Group1", "A", "1" },
1311             { nullptr,        "B", "2" },
1312             { nullptr,        "C", "3" },
1313             { "Group2", "D", "4" },
1314             { nullptr,        "E", "5" },
1315             { nullptr,        "F", "6" },
1316             { "Total Result", nullptr, "28" }
1317         };
1318
1319         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "D, E, F grouped by Group2.");
1320         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1321     }
1322
1323     pDPs->FreeTable(pDPObj);
1324     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
1325     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
1326                            size_t(0), pDPs->GetSheetCaches().size());
1327
1328     m_pDoc->DeleteTab(1);
1329     m_pDoc->DeleteTab(0);
1330 }
1331
1332 void TestPivottable::testPivotTableNumberGrouping()
1333 {
1334     m_pDoc->InsertTab(0, "Data");
1335     m_pDoc->InsertTab(1, "Table");
1336
1337     // Raw data
1338     const std::vector<std::vector<const char*>> aData = {
1339         { "Order", "Score" },
1340         { "43", "171" },
1341         { "18", "20"  },
1342         { "69", "159" },
1343         { "95", "19"  },
1344         { "96", "163" },
1345         { "46", "70"  },
1346         { "22", "36"  },
1347         { "81", "49"  },
1348         { "54", "61"  },
1349         { "39", "62"  },
1350         { "86", "17"  },
1351         { "34", "0"   },
1352         { "30", "25"  },
1353         { "24", "103" },
1354         { "16", "59"  },
1355         { "24", "119" },
1356         { "15", "86"  },
1357         { "69", "170" }
1358     };
1359
1360     // Dimension definition
1361     static const DPFieldDef aFields[] = {
1362         { "Order", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
1363         { "Score", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
1364     };
1365
1366     ScAddress aPos(1,1,0);
1367     ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
1368     CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);
1369
1370     ScDPObject* pDPObj = createDPFromRange(
1371         m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
1372
1373     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
1374     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
1375     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
1376                            size_t(1), pDPs->GetCount());
1377     pDPObj->SetName(pDPs->CreateNewName());
1378
1379     ScDPSaveData* pSaveData = pDPObj->GetSaveData();
1380     CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
1381     ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
1382     CPPUNIT_ASSERT_MESSAGE("No dimension data !?", pDimData);
1383
1384     {
1385         ScDPNumGroupInfo aInfo;
1386         aInfo.mbEnable = true;
1387         aInfo.mbAutoStart = false;
1388         aInfo.mbAutoEnd = false;
1389         aInfo.mbDateValues = false;
1390         aInfo.mbIntegerOnly = true;
1391         aInfo.mfStart = 30;
1392         aInfo.mfEnd = 60;
1393         aInfo.mfStep = 10;
1394         ScDPSaveNumGroupDimension aGroup("Order", aInfo);
1395         pDimData->AddNumGroupDimension(aGroup);
1396     }
1397
1398     pDPObj->SetSaveData(*pSaveData);
1399     ScRange aOutRange = refreshGroups(pDPs, pDPObj);
1400     {
1401         // Expected output table content.  0 = empty cell
1402         std::vector<std::vector<const char*>> aOutputCheck = {
1403             { "Order", "Sum - Score" },
1404             { "<30",   "423" },
1405             { "30-39", "87"  },
1406             { "40-49", "241" },
1407             { "50-60", "61"  },
1408             { ">60",   "577" },
1409             { "Total Result", "1389" }
1410         };
1411
1412         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Order grouped by numbers");
1413         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1414     }
1415
1416     pDPs->FreeTable(pDPObj);
1417     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
1418     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
1419                            size_t(0), pDPs->GetSheetCaches().size());
1420
1421     m_pDoc->DeleteTab(1);
1422     m_pDoc->DeleteTab(0);
1423 }
1424
1425 void TestPivottable::testPivotTableDateGrouping()
1426 {
1427     m_pDoc->InsertTab(0, "Data");
1428     m_pDoc->InsertTab(1, "Table");
1429
1430     // Raw data
1431     const std::vector<std::vector<const char*>> aData = {
1432         { "Date", "Value" },
1433         { "2011-01-01", "1" },
1434         { "2011-03-02", "2" },
1435         { "2012-01-04", "3" },
1436         { "2012-02-23", "4" },
1437         { "2012-02-24", "5" },
1438         { "2012-03-15", "6" },
1439         { "2011-09-03", "7" },
1440         { "2012-12-25", "8" }
1441     };
1442
1443     // Dimension definition
1444     static const DPFieldDef aFields[] = {
1445         { "Date", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
1446         { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
1447     };
1448
1449     ScAddress aPos(1,1,0);
1450     ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
1451     CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);
1452
1453     ScDPObject* pDPObj = createDPFromRange(
1454         m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
1455
1456     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
1457     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
1458     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
1459                            size_t(1), pDPs->GetCount());
1460     pDPObj->SetName(pDPs->CreateNewName());
1461
1462     ScDPSaveData* pSaveData = pDPObj->GetSaveData();
1463     CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
1464     ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
1465     CPPUNIT_ASSERT_MESSAGE("No dimension data !?", pDimData);
1466
1467     OUString aBaseDimName("Date");
1468
1469     ScDPNumGroupInfo aInfo;
1470     aInfo.mbEnable = true;
1471     aInfo.mbAutoStart = true;
1472     aInfo.mbAutoEnd = true;
1473     {
1474         // Turn the Date dimension into months.  The first of the date
1475         // dimensions is always a number-group dimension which replaces the
1476         // original dimension.
1477         ScDPSaveNumGroupDimension aGroup(aBaseDimName, aInfo, sheet::DataPilotFieldGroupBy::MONTHS);
1478         pDimData->AddNumGroupDimension(aGroup);
1479     }
1480
1481     {
1482         // Add quarter dimension.  This will be an additional dimension.
1483         OUString aGroupDimName =
1484             pDimData->CreateDateGroupDimName(
1485                 sheet::DataPilotFieldGroupBy::QUARTERS, *pDPObj, true, nullptr);
1486         ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
1487         aGroupDim.SetDateInfo(aInfo, sheet::DataPilotFieldGroupBy::QUARTERS);
1488         pDimData->AddGroupDimension(aGroupDim);
1489
1490         // Set orientation.
1491         ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
1492         pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
1493         pSaveData->SetPosition(pDim, 0); // set it to the left end.
1494     }
1495
1496     {
1497         // Add year dimension.  This is a new dimension also.
1498         OUString aGroupDimName =
1499             pDimData->CreateDateGroupDimName(
1500                 sheet::DataPilotFieldGroupBy::YEARS, *pDPObj, true, nullptr);
1501         ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
1502         aGroupDim.SetDateInfo(aInfo, sheet::DataPilotFieldGroupBy::YEARS);
1503         pDimData->AddGroupDimension(aGroupDim);
1504
1505         // Set orientation.
1506         ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
1507         pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
1508         pSaveData->SetPosition(pDim, 0); // set it to the left end.
1509     }
1510
1511     pDPObj->SetSaveData(*pSaveData);
1512     ScRange aOutRange = refreshGroups(pDPs, pDPObj);
1513     {
1514         // Expected output table content.  0 = empty cell
1515         std::vector<std::vector<const char*>> aOutputCheck = {
1516             { "Years", "Quarters", "Date", "Sum - Value" },
1517             { "2011", "Q1", "Jan", "1" },
1518             { nullptr, nullptr,         "Mar", "2" },
1519             { nullptr,      "Q3", "Sep", "7" },
1520             { "2012", "Q1", "Jan", "3" },
1521             { nullptr, nullptr,         "Feb", "9" },
1522             { nullptr, nullptr,         "Mar", "6" },
1523             { nullptr,      "Q4", "Dec", "8" },
1524             { "Total Result", nullptr, nullptr, "36" },
1525         };
1526
1527         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Years, quarters and months date groups.");
1528         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1529     }
1530
1531     {
1532         // Let's hide year 2012.
1533         pSaveData = pDPObj->GetSaveData();
1534         ScDPSaveDimension* pDim = pSaveData->GetDimensionByName("Years");
1535         CPPUNIT_ASSERT_MESSAGE("Years dimension should exist.", pDim);
1536         ScDPSaveMember* pMem = pDim->GetMemberByName("2012");
1537         CPPUNIT_ASSERT_MESSAGE("Member should exist.", pMem);
1538         pMem->SetIsVisible(false);
1539     }
1540     pDPObj->SetSaveData(*pSaveData);
1541     pDPObj->ReloadGroupTableData();
1542     pDPObj->InvalidateData();
1543
1544     aOutRange = refresh(pDPObj);
1545     {
1546         // Expected output table content.  0 = empty cell
1547         std::vector<std::vector<const char*>> aOutputCheck = {
1548             { "Years", "Quarters", "Date", "Sum - Value" },
1549             { "2011", "Q1", "Jan", "1" },
1550             { nullptr, nullptr,         "Mar", "2" },
1551             { nullptr,      "Q3", "Sep", "7" },
1552             { "Total Result", nullptr, nullptr, "10" },
1553         };
1554
1555         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Year 2012 data now hidden");
1556         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1557     }
1558
1559     // Remove all date grouping. The source dimension "Date" has two
1560     // external dimensions ("Years" and "Quarters") and one internal ("Date"
1561     // the same name but different hierarchy).  Remove all of them.
1562     pSaveData = pDPObj->GetSaveData();
1563     pSaveData->RemoveAllGroupDimensions(aBaseDimName);
1564     pDPObj->SetSaveData(*pSaveData);
1565     pDPObj->ReloadGroupTableData();
1566     pDPObj->InvalidateData();
1567
1568     aOutRange = refresh(pDPObj);
1569     {
1570         // Expected output table content.  0 = empty cell
1571         std::vector<std::vector<const char*>> aOutputCheck = {
1572             { "Date", "Sum - Value" },
1573             { "2011-01-01", "1" },
1574             { "2011-03-02", "2" },
1575             { "2011-09-03", "7" },
1576             { "2012-01-04", "3" },
1577             { "2012-02-23", "4" },
1578             { "2012-02-24", "5" },
1579             { "2012-03-15", "6" },
1580             { "2012-12-25", "8" },
1581             { "Total Result", "36" }
1582         };
1583
1584         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Remove all date grouping.");
1585         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1586     }
1587
1588     pDPs->FreeTable(pDPObj);
1589     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
1590     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
1591                            size_t(0), pDPs->GetSheetCaches().size());
1592
1593     m_pDoc->DeleteTab(1);
1594     m_pDoc->DeleteTab(0);
1595 }
1596
1597 void TestPivottable::testPivotTableEmptyRows()
1598 {
1599     m_pDoc->InsertTab(0, "Data");
1600     m_pDoc->InsertTab(1, "Table");
1601
1602     // Raw data
1603     const std::vector<std::vector<const char*>> aData = {
1604         { "Name", "Value" },
1605         { "A", "1" },
1606         { "B", "2" },
1607         { "C", "3" },
1608         { "D", "4" },
1609     };
1610
1611     // Dimension definition
1612     static const DPFieldDef aFields[] = {
1613         { "Name", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
1614         { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
1615     };
1616
1617     ScAddress aPos(1,1,0);
1618     ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
1619     CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);
1620
1621     // Extend the range downward to include some trailing empty rows.
1622     aDataRange.aEnd.IncRow(2);
1623
1624     ScDPObject* pDPObj = createDPFromRange(
1625         m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
1626
1627     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
1628     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
1629     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
1630                            size_t(1), pDPs->GetCount());
1631     pDPObj->SetName(pDPs->CreateNewName());
1632
1633     ScRange aOutRange = refresh(pDPObj);
1634
1635     {
1636         // Expected output table content.  0 = empty cell
1637         std::vector<std::vector<const char*>> aOutputCheck = {
1638             { "Name", "Sum - Value" },
1639             { "A", "1" },
1640             { "B", "2" },
1641             { "C", "3" },
1642             { "D", "4" },
1643             { "(empty)", nullptr },
1644             { "Total Result", "10" },
1645         };
1646
1647         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Include empty rows");
1648         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1649     }
1650
1651     // This time, ignore empty rows.
1652     ScDPSaveData* pSaveData = pDPObj->GetSaveData();
1653     CPPUNIT_ASSERT_MESSAGE("Save data doesn't exist.", pSaveData);
1654     pSaveData->SetIgnoreEmptyRows(true);
1655     pDPObj->ClearTableData();
1656     aOutRange = refresh(pDPObj);
1657
1658     {
1659         // Expected output table content.  0 = empty cell
1660         std::vector<std::vector<const char*>> aOutputCheck = {
1661             { "Name", "Sum - Value" },
1662             { "A", "1" },
1663             { "B", "2" },
1664             { "C", "3" },
1665             { "D", "4" },
1666             { "Total Result", "10" },
1667         };
1668
1669         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Ignore empty rows");
1670         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1671     }
1672
1673     // Modify the source to remove member 'A', then refresh the table.
1674     m_pDoc->SetString(1, 2, 0, "B");
1675
1676     o3tl::sorted_vector<ScDPObject*> aRefs;
1677     TranslateId pErr = pDPs->ReloadCache(pDPObj, aRefs);
1678     CPPUNIT_ASSERT_MESSAGE("Failed to reload cache.", !pErr);
1679     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should only be one pivot table linked to this cache.",
1680                            o3tl::sorted_vector<ScDPObject*>::size_type(1), aRefs.size());
1681     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should only be one pivot table linked to this cache.",
1682                            pDPObj, *aRefs.begin());
1683
1684     pDPObj->ClearTableData();
1685     aOutRange = refresh(pDPObj);
1686
1687     {
1688         // Expected output table content.  0 = empty cell
1689         std::vector<std::vector<const char*>> aOutputCheck = {
1690             { "Name", "Sum - Value" },
1691             { "B", "3" },
1692             { "C", "3" },
1693             { "D", "4" },
1694             { "Total Result", "10" },
1695         };
1696
1697         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Ignore empty rows");
1698         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1699     }
1700
1701     pDPs->FreeTable(pDPObj);
1702     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
1703     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
1704                            size_t(0), pDPs->GetSheetCaches().size());
1705
1706     m_pDoc->DeleteTab(1);
1707     m_pDoc->DeleteTab(0);
1708 }
1709
1710 void TestPivottable::testPivotTableTextNumber()
1711 {
1712     m_pDoc->InsertTab(0, "Data");
1713     m_pDoc->InsertTab(1, "Table");
1714
1715     // Raw data
1716     const char* aData[][2] = {
1717         { "Name", "Value" },
1718         { "0001", "1" },
1719         { "0002", "2" },
1720         { "0003", "3" },
1721         { "0004", "4" },
1722     };
1723
1724     // Dimension definition
1725     static const DPFieldDef aFields[] = {
1726         { "Name", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
1727         { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
1728     };
1729
1730     // Insert raw data such that the first column values are entered as text.
1731     for (size_t nRow = 0; nRow < SAL_N_ELEMENTS(aData); ++nRow)
1732     {
1733         ScSetStringParam aParam;
1734         aParam.mbDetectNumberFormat = false;
1735         aParam.meSetTextNumFormat = ScSetStringParam::Always;
1736         m_pDoc->SetString(0, nRow, 0, OUString::createFromAscii(aData[nRow][0]), &aParam);
1737         aParam.meSetTextNumFormat = ScSetStringParam::Never;
1738         m_pDoc->SetString(1, nRow, 0, OUString::createFromAscii(aData[nRow][1]), &aParam);
1739
1740         if (nRow == 0)
1741             // Don't check the header row.
1742             continue;
1743
1744         // Check the data rows.
1745         CPPUNIT_ASSERT_MESSAGE("This cell is supposed to be text.", m_pDoc->HasStringData(0, nRow, 0));
1746         CPPUNIT_ASSERT_MESSAGE("This cell is supposed to be numeric.", m_pDoc->HasValueData(1, nRow, 0));
1747     }
1748
1749     ScRange aDataRange(0, 0, 0, 1, 4, 0);
1750
1751     ScDPObject* pDPObj = createDPFromRange(
1752         m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
1753
1754     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
1755     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
1756     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
1757                            size_t(1), pDPs->GetCount());
1758     pDPObj->SetName(pDPs->CreateNewName());
1759
1760     ScRange aOutRange = refresh(pDPObj);
1761
1762     {
1763         // Expected output table content.  0 = empty cell
1764         std::vector<std::vector<const char*>> aOutputCheck = {
1765             { "Name", "Sum - Value" },
1766             { "0001", "1" },
1767             { "0002", "2" },
1768             { "0003", "3" },
1769             { "0004", "4" },
1770             { "Total Result", "10" },
1771         };
1772
1773         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Text number field members");
1774         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1775     }
1776
1777     // Set the Name dimension to page dimension.
1778     pDPObj->BuildAllDimensionMembers();
1779     ScDPSaveData aSaveData(*pDPObj->GetSaveData());
1780     ScDPSaveDimension* pDim = aSaveData.GetExistingDimensionByName(u"Name");
1781     CPPUNIT_ASSERT(pDim);
1782     pDim->SetOrientation(sheet::DataPilotFieldOrientation_PAGE);
1783     OUString aVisiblePage("0004");
1784     pDim->SetCurrentPage(&aVisiblePage);
1785     pDPObj->SetSaveData(aSaveData);
1786
1787     aOutRange = refresh(pDPObj);
1788
1789     {
1790         // Expected output table content.  0 = empty cell
1791         std::vector<std::vector<const char*>> aOutputCheck = {
1792             { "Name", "0004" },
1793             {  nullptr, nullptr },
1794             { "Sum - Value", nullptr },
1795             { "4", nullptr }
1796         };
1797
1798         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Text number field members");
1799         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1800     }
1801
1802     pDPs->FreeTable(pDPObj);
1803     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
1804     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
1805                                  size_t(0), pDPs->GetSheetCaches().size());
1806
1807     m_pDoc->DeleteTab(1);
1808     m_pDoc->DeleteTab(0);
1809 }
1810
1811 void TestPivottable::testPivotTableCaseInsensitiveStrings()
1812 {
1813     m_pDoc->InsertTab(0, "Data");
1814     m_pDoc->InsertTab(1, "Table");
1815
1816     // Raw data
1817     const std::vector<std::vector<const char*>> aData = {
1818         { "Name", "Value" },
1819         { "A", "1" },
1820         { "a", "2" },
1821     };
1822
1823     // Dimension definition
1824     static const DPFieldDef aFields[] = {
1825         { "Name", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
1826         { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
1827     };
1828
1829     ScAddress aPos(1,1,0);
1830     ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
1831     CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);
1832
1833     ScDPObject* pDPObj = createDPFromRange(
1834         m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
1835
1836     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
1837     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
1838     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
1839                            size_t(1), pDPs->GetCount());
1840     pDPObj->SetName(pDPs->CreateNewName());
1841
1842     ScRange aOutRange = refresh(pDPObj);
1843
1844     {
1845         // Expected output table content.  0 = empty cell
1846         std::vector<std::vector<const char*>> aOutputCheck = {
1847             { "Name", "Sum - Value" },
1848             { "A", "3" },
1849             { "Total Result", "3" },
1850         };
1851
1852         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Case insensitive strings");
1853         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
1854     }
1855
1856     pDPs->FreeTable(pDPObj);
1857     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
1858     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
1859                            size_t(0), pDPs->GetSheetCaches().size());
1860
1861     m_pDoc->DeleteTab(1);
1862     m_pDoc->DeleteTab(0);
1863 }
1864
1865 void TestPivottable::testPivotTableNumStability()
1866 {
1867     FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);
1868
1869     // Raw Data
1870     const std::vector<std::vector<const char*>> aData = {
1871         { "Name",   "Time Start", "Time End", "Total"          },
1872         { "Sam",    "07:48 AM",   "09:00 AM", "=RC[-1]-RC[-2]" },
1873         { "Sam",    "09:00 AM",   "10:30 AM", "=RC[-1]-RC[-2]" },
1874         { "Sam",    "10:30 AM",   "12:30 PM", "=RC[-1]-RC[-2]" },
1875         { "Sam",    "12:30 PM",   "01:00 PM", "=RC[-1]-RC[-2]" },
1876         { "Sam",    "01:00 PM",   "01:30 PM", "=RC[-1]-RC[-2]" },
1877         { "Sam",    "01:30 PM",   "02:00 PM", "=RC[-1]-RC[-2]" },
1878         { "Sam",    "02:00 PM",   "07:15 PM", "=RC[-1]-RC[-2]" },
1879         { "Sam",    "07:47 AM",   "09:00 AM", "=RC[-1]-RC[-2]" },
1880         { "Sam",    "09:00 AM",   "10:00 AM", "=RC[-1]-RC[-2]" },
1881         { "Sam",    "10:00 AM",   "11:00 AM", "=RC[-1]-RC[-2]" },
1882         { "Sam",    "11:00 AM",   "11:30 AM", "=RC[-1]-RC[-2]" },
1883         { "Sam",    "11:30 AM",   "12:45 PM", "=RC[-1]-RC[-2]" },
1884         { "Sam",    "12:45 PM",   "01:15 PM", "=RC[-1]-RC[-2]" },
1885         { "Sam",    "01:15 PM",   "02:30 PM", "=RC[-1]-RC[-2]" },
1886         { "Sam",    "02:30 PM",   "02:45 PM", "=RC[-1]-RC[-2]" },
1887         { "Sam",    "02:45 PM",   "04:30 PM", "=RC[-1]-RC[-2]" },
1888         { "Sam",    "04:30 PM",   "06:00 PM", "=RC[-1]-RC[-2]" },
1889         { "Sam",    "06:00 PM",   "07:15 PM", "=RC[-1]-RC[-2]" },
1890         { "Mike",   "06:15 AM",   "08:30 AM", "=RC[-1]-RC[-2]" },
1891         { "Mike",   "08:30 AM",   "10:03 AM", "=RC[-1]-RC[-2]" },
1892         { "Mike",   "10:03 AM",   "12:00 PM", "=RC[-1]-RC[-2]" },
1893         { "Dennis", "11:00 AM",   "01:00 PM", "=RC[-1]-RC[-2]" },
1894         { "Dennis", "01:00 PM",   "02:00 PM", "=RC[-1]-RC[-2]" }
1895     };
1896
1897     // Dimension definition
1898     static const DPFieldDef aFields[] = {
1899         { "Name",  sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
1900         { "Total", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
1901     };
1902
1903     m_pDoc->InsertTab(0, "Data");
1904     m_pDoc->InsertTab(1, "Table");
1905
1906     ScAddress aPos(1,1,0);
1907     ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
1908
1909     // Insert formulas to manually calculate sums for each name.
1910     m_pDoc->SetString(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+1, aDataRange.aStart.Tab(), "=SUMIF(R[-23]C:R[-1]C;\"Dennis\";R[-23]C[3]:R[-1]C[3])");
1911     m_pDoc->SetString(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+2, aDataRange.aStart.Tab(), "=SUMIF(R[-24]C:R[-2]C;\"Mike\";R[-24]C[3]:R[-2]C[3])");
1912     m_pDoc->SetString(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+3, aDataRange.aStart.Tab(), "=SUMIF(R[-25]C:R[-3]C;\"Sam\";R[-25]C[3]:R[-3]C[3])");
1913
1914     m_pDoc->CalcAll();
1915
1916     // Get correct sum values.
1917     double fDennisTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+1, aDataRange.aStart.Tab());
1918     double fMikeTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+2, aDataRange.aStart.Tab());
1919     double fSamTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+3, aDataRange.aStart.Tab());
1920
1921     ScDPObject* pDPObj = createDPFromRange(
1922         m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
1923
1924     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
1925     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
1926     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
1927                            size_t(1), pDPs->GetCount());
1928     pDPObj->SetName(pDPs->CreateNewName());
1929
1930     ScRange aOutRange = refresh(pDPObj);
1931
1932     // Manually check the total value for each name.
1933     //
1934     // +--------------+----------------+
1935     // | Name         |                |
1936     // +--------------+----------------+
1937     // | Dennis       | <Dennis total> |
1938     // +--------------+----------------+
1939     // | Mike         | <Miks total>   |
1940     // +--------------+----------------+
1941     // | Sam          | <Sam total>    |
1942     // +--------------+----------------+
1943     // | Total Result | ...            |
1944     // +--------------+----------------+
1945
1946     aPos = aOutRange.aStart;
1947     aPos.IncCol();
1948     aPos.IncRow();
1949     double fTest = m_pDoc->GetValue(aPos);
1950     CPPUNIT_ASSERT_MESSAGE("Incorrect value for Dennis.", rtl::math::approxEqual(fTest, fDennisTotal));
1951     aPos.IncRow();
1952     fTest = m_pDoc->GetValue(aPos);
1953     CPPUNIT_ASSERT_MESSAGE("Incorrect value for Mike.", rtl::math::approxEqual(fTest, fMikeTotal));
1954     aPos.IncRow();
1955     fTest = m_pDoc->GetValue(aPos);
1956     CPPUNIT_ASSERT_MESSAGE("Incorrect value for Sam.", rtl::math::approxEqual(fTest, fSamTotal));
1957
1958     pDPs->FreeTable(pDPObj);
1959     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
1960     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
1961                            size_t(0), pDPs->GetSheetCaches().size());
1962
1963     m_pDoc->DeleteTab(1);
1964     m_pDoc->DeleteTab(0);
1965 }
1966
1967 void TestPivottable::testPivotTableFieldReference()
1968 {
1969     m_pDoc->InsertTab(0, "Data");
1970     m_pDoc->InsertTab(1, "Table");
1971
1972     // Raw data
1973     const std::vector<std::vector<const char*>> aData = {
1974         { "Name", "Value" },
1975         { "A", "1" },
1976         { "B", "2" },
1977         { "C", "4" },
1978         { "D", "8" },
1979     };
1980
1981     // Dimension definition
1982     static const DPFieldDef aFields[] = {
1983         { "Name", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
1984         { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
1985     };
1986
1987     ScAddress aPos(1,1,0);
1988     ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
1989     CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);
1990
1991     ScDPObject* pDPObj = createDPFromRange(
1992         m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
1993
1994     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
1995     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
1996     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
1997                            size_t(1), pDPs->GetCount());
1998     pDPObj->SetName(pDPs->CreateNewName());
1999
2000     ScRange aOutRange = refresh(pDPObj);
2001
2002     {
2003         // Expected output table content.  0 = empty cell
2004         std::vector<std::vector<const char*>> aOutputCheck = {
2005             { "Name", "Sum - Value" },
2006             { "A", "1" },
2007             { "B", "2" },
2008             { "C", "4" },
2009             { "D", "8" },
2010             { "Total Result", "15" },
2011         };
2012
2013         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Field reference (none)");
2014         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2015     }
2016
2017     ScDPSaveData aSaveData = *pDPObj->GetSaveData();
2018     sheet::DataPilotFieldReference aFieldRef;
2019     aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::ITEM_DIFFERENCE;
2020     aFieldRef.ReferenceField = "Name";
2021     aFieldRef.ReferenceItemType = sheet::DataPilotFieldReferenceItemType::NAMED;
2022     aFieldRef.ReferenceItemName = "A";
2023     ScDPSaveDimension* pDim = aSaveData.GetDimensionByName("Value");
2024     CPPUNIT_ASSERT_MESSAGE("Failed to retrieve dimension 'Value'.", pDim);
2025     pDim->SetReferenceValue(&aFieldRef);
2026     pDPObj->SetSaveData(aSaveData);
2027
2028     aOutRange = refresh(pDPObj);
2029     {
2030         // Expected output table content.  0 = empty cell
2031         std::vector<std::vector<const char*>> aOutputCheck = {
2032             { "Name", "Sum - Value" },
2033             { "A", nullptr },
2034             { "B", "1" },
2035             { "C", "3" },
2036             { "D", "7" },
2037             { "Total Result", nullptr },
2038         };
2039
2040         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Field reference (difference from)");
2041         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2042     }
2043
2044     aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::ITEM_PERCENTAGE;
2045     pDim->SetReferenceValue(&aFieldRef);
2046     pDPObj->SetSaveData(aSaveData);
2047
2048     aOutRange = refresh(pDPObj);
2049     {
2050         // Expected output table content.  0 = empty cell
2051         std::vector<std::vector<const char*>> aOutputCheck = {
2052             { "Name", "Sum - Value" },
2053             { "A", "100.00%" },
2054             { "B", "200.00%" },
2055             { "C", "400.00%" },
2056             { "D", "800.00%" },
2057             { "Total Result", nullptr },
2058         };
2059
2060         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Field reference (% of)");
2061         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2062     }
2063
2064     aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::ITEM_PERCENTAGE_DIFFERENCE;
2065     pDim->SetReferenceValue(&aFieldRef);
2066     pDPObj->SetSaveData(aSaveData);
2067
2068     aOutRange = refresh(pDPObj);
2069     {
2070         // Expected output table content.  0 = empty cell
2071         std::vector<std::vector<const char*>> aOutputCheck = {
2072             { "Name", "Sum - Value" },
2073             { "A", nullptr },
2074             { "B", "100.00%" },
2075             { "C", "300.00%" },
2076             { "D", "700.00%" },
2077             { "Total Result", nullptr },
2078         };
2079
2080         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Field reference (% difference from)");
2081         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2082     }
2083
2084     aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::RUNNING_TOTAL;
2085     pDim->SetReferenceValue(&aFieldRef);
2086     pDPObj->SetSaveData(aSaveData);
2087
2088     aOutRange = refresh(pDPObj);
2089     {
2090         // Expected output table content.  0 = empty cell
2091         std::vector<std::vector<const char*>> aOutputCheck = {
2092             { "Name", "Sum - Value" },
2093             { "A", "1" },
2094             { "B", "3" },
2095             { "C", "7" },
2096             { "D", "15" },
2097             { "Total Result", nullptr },
2098         };
2099
2100         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Field reference (Running total)");
2101         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2102     }
2103
2104     aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::COLUMN_PERCENTAGE;
2105     pDim->SetReferenceValue(&aFieldRef);
2106     pDPObj->SetSaveData(aSaveData);
2107
2108     aOutRange = refresh(pDPObj);
2109     {
2110         // Expected output table content.  0 = empty cell
2111         std::vector<std::vector<const char*>> aOutputCheck = {
2112             { "Name", "Sum - Value" },
2113             { "A", "6.67%" },
2114             { "B", "13.33%" },
2115             { "C", "26.67%" },
2116             { "D", "53.33%" },
2117             { "Total Result", "100.00%" },
2118         };
2119
2120         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Field reference (% of column)");
2121         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2122     }
2123
2124     pDPs->FreeTable(pDPObj);
2125     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
2126     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
2127                            size_t(0), pDPs->GetSheetCaches().size());
2128
2129     m_pDoc->DeleteTab(1);
2130     m_pDoc->DeleteTab(0);
2131 }
2132
2133 void TestPivottable::testPivotTableDocFunc()
2134 {
2135     m_pDoc->InsertTab(0, "Data");
2136     m_pDoc->InsertTab(1, "Table");
2137
2138     // Raw data
2139     const std::vector<std::vector<const char*>> aData = {
2140         { "Name",      "Value" },
2141         { "Sun",       "1" },
2142         { "Oracle",    "2" },
2143         { "Red Hat",   "4" },
2144         { "SUSE",      "8" },
2145         { "Apple",     "16" },
2146         { "Microsoft", "32" },
2147     };
2148
2149     // Dimension definition
2150     static const DPFieldDef aFields[] = {
2151         { "Name", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
2152         { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
2153     };
2154
2155     ScAddress aPos(1,1,0);
2156     ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
2157     CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);
2158
2159     std::unique_ptr<ScDPObject> pDPObj(createDPFromRange(
2160         m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false));
2161
2162     CPPUNIT_ASSERT_MESSAGE("Failed to create pivot table object.", pDPObj);
2163
2164     // Create a new pivot table output.
2165     ScDBDocFunc aFunc(*m_xDocShell);
2166     bool bSuccess = aFunc.CreatePivotTable(*pDPObj, false, true);
2167     CPPUNIT_ASSERT_MESSAGE("Failed to create pivot table output via ScDBDocFunc.", bSuccess);
2168     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2169     CPPUNIT_ASSERT_MESSAGE("Failed to get pivot table collection.", pDPs);
2170     CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pDPs->GetCount());
2171     ScDPObject* pDPObject = &(*pDPs)[0];
2172     ScRange aOutRange = pDPObject->GetOutRange();
2173     {
2174         // Expected output table content.  0 = empty cell
2175         std::vector<std::vector<const char*>> aOutputCheck = {
2176             { "Name", "Sum - Value" },
2177             { "Apple", "16" },
2178             { "Microsoft", "32" },
2179             { "Oracle", "2" },
2180             { "Red Hat", "4" },
2181             { "Sun", "1" },
2182             { "SUSE", "8" },
2183             { "Total Result", "63" },
2184         };
2185
2186         bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Pivot table created via ScDBDocFunc");
2187         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2188     }
2189
2190     // Remove this pivot table output. This should also clear the pivot cache
2191     // it was referencing.
2192     bSuccess = aFunc.RemovePivotTable(*pDPObject, false, true);
2193     CPPUNIT_ASSERT_MESSAGE("Failed to remove pivot table output via ScDBDocFunc.", bSuccess);
2194     CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(0), pDPs->GetCount());
2195     CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(0), pDPs->GetSheetCaches().size());
2196
2197     m_pDoc->DeleteTab(1);
2198     m_pDoc->DeleteTab(0);
2199 }
2200
2201 void TestPivottable::testFuncGETPIVOTDATA()
2202 {
2203     m_pDoc->InsertTab(0, "Data");
2204     m_pDoc->InsertTab(1, "Table");
2205
2206     // Raw data
2207     const std::vector<std::vector<const char*>> aData = {
2208         { "Name", "Value" },
2209         { "A", "1" },
2210         { "A", "2" },
2211         { "A", "3" },
2212         { "B", "4" },
2213         { "B", "5" },
2214         { "B", "6" },
2215     };
2216
2217     ScAddress aPos(1,1,0);
2218     ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
2219     CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);
2220
2221     ScDPObject* pDPObj = nullptr;
2222
2223     {
2224         // Dimension definition
2225         static const DPFieldDef aFields[] = {
2226             { "Name", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
2227             { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
2228         };
2229
2230         pDPObj = createDPFromRange(m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
2231     }
2232
2233     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2234     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
2235     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
2236                            size_t(1), pDPs->GetCount());
2237     pDPObj->SetName(pDPs->CreateNewName());
2238
2239     ScRange aOutRange = refresh(pDPObj);
2240     {
2241         // Expected output table content.  0 = empty cell
2242         std::vector<std::vector<const char*>> aOutputCheck = {
2243             { "Name", "Sum - Value" },
2244             { "A", "6" },
2245             { "B", "15" },
2246             { "Total Result", "21" },
2247         };
2248
2249         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Pivot table created for GETPIVOTDATA");
2250         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2251     }
2252
2253     aPos = aOutRange.aEnd;
2254     aPos.IncRow(2); // Move 2 rows down from the table output.
2255
2256     OUString aPivotPosStr(aOutRange.aStart.Format(ScRefFlags::ADDR_ABS));
2257
2258     sc::AutoCalcSwitch aSwitch(*m_pDoc, true); // turn autocalc on.
2259
2260     // First, get the grand total.
2261     OUString aFormula = "=GETPIVOTDATA(\"Value\";" + aPivotPosStr + ")";
2262     m_pDoc->SetString(aPos, aFormula);
2263     double fVal = m_pDoc->GetValue(aPos);
2264     CPPUNIT_ASSERT_EQUAL(21.0, fVal);
2265
2266     // Get the subtotal for 'A'.
2267     aFormula = "=GETPIVOTDATA(\"Value\";" + aPivotPosStr + ";\"Name\";\"A\")";
2268     m_pDoc->SetString(aPos, aFormula);
2269     fVal = m_pDoc->GetValue(aPos);
2270     CPPUNIT_ASSERT_EQUAL(6.0, fVal);
2271
2272     // Get the subtotal for 'B'.
2273     aFormula = "=GETPIVOTDATA(\"Value\";" + aPivotPosStr + ";\"Name\";\"B\")";
2274     m_pDoc->SetString(aPos, aFormula);
2275     fVal = m_pDoc->GetValue(aPos);
2276     CPPUNIT_ASSERT_EQUAL(15.0, fVal);
2277
2278     clearRange(m_pDoc, aPos); // Delete the formula.
2279
2280     pDPs->FreeTable(pDPObj);
2281
2282     {
2283         // Dimension definition
2284         static const DPFieldDef aFields[] = {
2285             { "Name", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
2286             { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
2287             { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::COUNT, false },
2288         };
2289
2290         pDPObj = createDPFromRange(m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
2291     }
2292
2293     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
2294     aOutRange = refresh(pDPObj);
2295
2296     {
2297         // Expected output table content.  0 = empty cell
2298         std::vector<std::vector<const char*>> aOutputCheck = {
2299             { "Name",                "Data",           nullptr   },
2300             { "A",                   "Sum - Value",   "6"  },
2301             {  nullptr,                    "Count - Value", "3"  },
2302             { "B",                   "Sum - Value",  "15"  },
2303             {  nullptr,                    "Count - Value", "3"  },
2304             { "Total Sum - Value",   nullptr,               "21" },
2305             { "Total Count - Value", nullptr,               "6"  },
2306         };
2307
2308         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Pivot table refreshed");
2309         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2310     }
2311
2312     aPos = aOutRange.aEnd;
2313     aPos.IncRow(2); // move 2 rows down from the output.
2314
2315     aPivotPosStr = aOutRange.aStart.Format(ScRefFlags::ADDR_ABS);
2316
2317     // First, get the grand totals.
2318     aFormula = "=GETPIVOTDATA(\"Sum - Value\";" + aPivotPosStr + ")";
2319     m_pDoc->SetString(aPos, aFormula);
2320     fVal = m_pDoc->GetValue(aPos);
2321     CPPUNIT_ASSERT_EQUAL(21.0, fVal);
2322     aFormula = "=GETPIVOTDATA(\"Count - Value\";" + aPivotPosStr + ")";
2323     m_pDoc->SetString(aPos, aFormula);
2324     fVal = m_pDoc->GetValue(aPos);
2325     CPPUNIT_ASSERT_EQUAL(6.0, fVal);
2326
2327     // Get the subtotals for 'A'.
2328     aFormula = "=GETPIVOTDATA(\"Sum - Value\";" + aPivotPosStr + ";\"Name\";\"A\")";
2329     m_pDoc->SetString(aPos, aFormula);
2330     fVal = m_pDoc->GetValue(aPos);
2331     CPPUNIT_ASSERT_EQUAL(6.0, fVal);
2332     aFormula = "=GETPIVOTDATA(\"Count - Value\";" + aPivotPosStr + ";\"Name\";\"A\")";
2333     m_pDoc->SetString(aPos, aFormula);
2334     fVal = m_pDoc->GetValue(aPos);
2335     CPPUNIT_ASSERT_EQUAL(3.0, fVal);
2336
2337     // Get the subtotals for 'B'.
2338     aFormula = "=GETPIVOTDATA(\"Sum - Value\";" + aPivotPosStr + ";\"Name\";\"B\")";
2339     m_pDoc->SetString(aPos, aFormula);
2340     fVal = m_pDoc->GetValue(aPos);
2341     CPPUNIT_ASSERT_EQUAL(15.0, fVal);
2342     aFormula = "=GETPIVOTDATA(\"Count - Value\";" + aPivotPosStr + ";\"Name\";\"B\")";
2343     m_pDoc->SetString(aPos, aFormula);
2344     fVal = m_pDoc->GetValue(aPos);
2345     CPPUNIT_ASSERT_EQUAL(3.0, fVal);
2346
2347     pDPs->FreeTable(pDPObj);
2348
2349     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
2350     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
2351                            size_t(0), pDPs->GetSheetCaches().size());
2352
2353     m_pDoc->DeleteTab(1);
2354     m_pDoc->DeleteTab(0);
2355 }
2356
2357 void TestPivottable::testFuncGETPIVOTDATALeafAccess()
2358 {
2359     m_pDoc->InsertTab(0, "Data");
2360     m_pDoc->InsertTab(1, "Table");
2361
2362     // Raw data
2363     const std::vector<std::vector<const char*>> aData = {
2364         { "Type", "Member", "Value" },
2365         { "A", "Anna", "1" },
2366         { "B", "Brittany", "2" },
2367         { "A", "Cecilia", "3" },
2368         { "B", "Donna", "4" },
2369     };
2370
2371     ScAddress aPos(1,1,0);
2372     ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
2373     CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);
2374
2375     ScDPObject* pDPObj = nullptr;
2376
2377     // Dimension definition
2378     static const DPFieldDef aFields[] = {
2379         { "Type", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
2380         { "Member", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
2381         { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
2382     };
2383
2384     // Create pivot table at A1 on 2nd sheet.
2385     pDPObj = createDPFromRange(m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
2386
2387     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2388     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
2389     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
2390                            size_t(1), pDPs->GetCount());
2391     pDPObj->SetName(pDPs->CreateNewName());
2392     ScRange aOutRange = refresh(pDPObj);
2393
2394     {
2395         // Expected output table content.  0 = empty cell
2396         std::vector<std::vector<const char*>> aOutputCheck = {
2397             { "Type",         "Member",   "Sum - Value" },
2398             { "A",            "Anna",     "1"           },
2399             {  nullptr,             "Cecilia",  "3"           },
2400             { "B",            "Brittany", "2"           },
2401             {  nullptr,             "Donna",    "4"           },
2402             { "Total Result",  nullptr,         "10"          },
2403         };
2404
2405         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Pivot table refreshed");
2406         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2407     }
2408
2409     // Insert formulas with GETPIVOTDATA in column E, and check their results.
2410
2411     struct Check
2412     {
2413         const char* mpFormula;
2414         double mfResult;
2415     };
2416
2417     static const Check aChecks[] = {
2418         { "=GETPIVOTDATA($A$1;\"Member[Anna]\")",     1.0 },
2419         { "=GETPIVOTDATA($A$1;\"Member[Brittany]\")", 2.0 },
2420         { "=GETPIVOTDATA($A$1;\"Member[Cecilia]\")",  3.0 },
2421         { "=GETPIVOTDATA($A$1;\"Member[Donna]\")",    4.0 },
2422     };
2423
2424     for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2425         m_pDoc->SetString(ScAddress(4,i,1), OUString::createFromAscii(aChecks[i].mpFormula));
2426
2427     m_pDoc->CalcAll();
2428
2429     for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
2430     {
2431         FormulaError nErr = m_pDoc->GetErrCode(ScAddress(4,i,1));
2432         CPPUNIT_ASSERT_EQUAL(sal_uInt16(FormulaError::NONE), static_cast<sal_uInt16>(nErr));
2433         double fVal = m_pDoc->GetValue(ScAddress(4,i,1));
2434         CPPUNIT_ASSERT_EQUAL(aChecks[i].mfResult, fVal);
2435     }
2436
2437     pDPs->FreeTable(pDPObj);
2438
2439     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
2440     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
2441                            size_t(0), pDPs->GetSheetCaches().size());
2442
2443     m_pDoc->DeleteTab(1);
2444     m_pDoc->DeleteTab(0);
2445 }
2446
2447 void TestPivottable::testPivotTableRepeatItemLabels()
2448 {
2449     m_pDoc->InsertTab(0, "Data");
2450     m_pDoc->InsertTab(1, "Table");
2451
2452     // Dimension definition
2453     static const DPFieldDef aFields[] = {
2454         { "Name",  sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, true },
2455         { "Country", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
2456         { "Year", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
2457         { "Score", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false }
2458     };
2459
2460     // Raw data
2461     const char* aData[][4] = {
2462         { "Andy",    "US", "1999", "30" },
2463         { "Andy",    "US", "2002", "20" },
2464         { "Andy",    "US", "2010", "45" },
2465         { "David",   "GB", "1998", "12" },
2466         { "Edward",  "NO", "2000",  "8" },
2467         { "Frank",   "FR", "2009", "15" },
2468         { "Frank",   "FR", "2008", "45" },
2469         { "Frank",   "FR", "2007", "45" },
2470     };
2471
2472     size_t nFieldCount = SAL_N_ELEMENTS(aFields);
2473     size_t const nDataCount = SAL_N_ELEMENTS(aData);
2474
2475     ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
2476     SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
2477     SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
2478
2479     ScDPObject* pDPObj = createDPFromRange(
2480         m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
2481
2482     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2483     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
2484     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
2485                            size_t(1), pDPs->GetCount());
2486     pDPObj->SetName(pDPs->CreateNewName());
2487
2488     bool bOverflow = false;
2489     ScRange aOutRange = pDPObj->GetNewOutputRange(bOverflow);
2490     CPPUNIT_ASSERT_MESSAGE("Table overflow!?", !bOverflow);
2491
2492     pDPObj->Output(aOutRange.aStart);
2493     aOutRange = pDPObj->GetOutRange();
2494     {
2495         // Expected output table content.  0 = empty cell
2496         std::vector<std::vector<const char*>> aOutputCheck = {
2497             { "Name",         "Country", "Year", "Sum - Score" },
2498             { "Andy",         "US",      "1999", "30"          },
2499             { "Andy",         nullptr,         "2002", "20"          },
2500             { "Andy",         nullptr,         "2010", "45"          },
2501             { "David",        "GB",      "1998", "12"          },
2502             { "Edward",       "NO",      "2000", "8"           },
2503             { "Frank",        "FR",      "2007", "45"          },
2504             { "Frank",        nullptr,         "2008", "45"          },
2505             { "Frank",        nullptr,         "2009", "15"          },
2506             { "Total Result", nullptr,         nullptr,      "220"         }
2507         };
2508
2509         bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
2510         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2511     }
2512
2513     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be only one data cache.", size_t(1), pDPs->GetSheetCaches().size());
2514
2515     pDPs->FreeTable(pDPObj);
2516     CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
2517     CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
2518                                  size_t(0), pDPs->GetSheetCaches().size());
2519
2520     m_pDoc->DeleteTab(1);
2521     m_pDoc->DeleteTab(0);
2522 }
2523
2524 void TestPivottable::testPivotTableDPCollection()
2525 {
2526     m_pDoc->InsertTab(0, "Data");
2527     m_pDoc->InsertTab(1, "Table");
2528
2529     // Dimension definition
2530     static const DPFieldDef aFields[] = {
2531         { "Software", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
2532         { "Version",  sheet::DataPilotFieldOrientation_COLUMN, ScGeneralFunction::NONE, false },
2533         { "1.2.3",    sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false }
2534     };
2535
2536     // Raw data
2537     const char* aData[][3] = {
2538         { "LibreOffice", "3.3.0", "30" },
2539         { "LibreOffice", "3.3.1", "20" },
2540         { "LibreOffice", "3.4.0", "45" },
2541     };
2542
2543     size_t nFieldCount = SAL_N_ELEMENTS(aFields);
2544     size_t const nDataCount = SAL_N_ELEMENTS(aData);
2545
2546     ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
2547     SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
2548     SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
2549     ScRange aDataRange(nCol1, nRow1, 0, nCol2, nRow2, 0);
2550
2551     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2552
2553     // Check at the beginning
2554     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be no DP table", size_t(0), pDPs->GetCount());
2555
2556     CPPUNIT_ASSERT_EQUAL_MESSAGE("should return nullptr",
2557                                  static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u"DP1"));
2558     CPPUNIT_ASSERT_EQUAL_MESSAGE("should return nullptr",
2559                                  static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u""));
2560
2561     // Add 2 DP objects
2562     ScDPObject* pDPObj = createDPFromRange(m_pDoc, aDataRange , aFields, nFieldCount, false);
2563     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
2564     pDPObj->SetName("DP1"); // set custom name
2565
2566     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.", size_t(1), pDPs->GetCount());
2567
2568     ScDPObject* pDPObj2 = createDPFromRange(m_pDoc, aDataRange, aFields, nFieldCount, false);
2569     pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj2));
2570     pDPObj2->SetName("DP2"); // set custom name
2571
2572     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be two DP tables", size_t(2), pDPs->GetCount());
2573     CPPUNIT_ASSERT_EQUAL_MESSAGE("should return first DPObject",
2574                                  pDPObj, pDPs->GetByName(u"DP1"));
2575     CPPUNIT_ASSERT_EQUAL_MESSAGE("should return second DPObject",
2576                                  pDPObj2, pDPs->GetByName(u"DP2"));
2577     CPPUNIT_ASSERT_EQUAL_MESSAGE("empty string should return nullptr",
2578                                  static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u""));
2579     CPPUNIT_ASSERT_EQUAL_MESSAGE("non existent name should return nullptr",
2580                                  static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u"Non"));
2581     // Remove first DP Object
2582     pDPs->FreeTable(pDPObj);
2583     CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one DP table", size_t(1), pDPs->GetCount());
2584
2585     CPPUNIT_ASSERT_EQUAL_MESSAGE("first DP object was deleted, should return nullptr",
2586                                  static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u"DP1"));
2587     CPPUNIT_ASSERT_EQUAL_MESSAGE("should return second DPObject",
2588                                  pDPObj2, pDPs->GetByName(u"DP2"));
2589     CPPUNIT_ASSERT_EQUAL_MESSAGE("empty string should return nullptr",
2590                                  static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u""));
2591     CPPUNIT_ASSERT_EQUAL_MESSAGE("non existent name should return nullptr",
2592                                  static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u"Non"));
2593
2594     // Remove second DP Object
2595     pDPs->FreeTable(pDPObj2);
2596     CPPUNIT_ASSERT_EQUAL_MESSAGE("first DP object was deleted, should return nullptr",
2597                                  static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u"DP1"));
2598     CPPUNIT_ASSERT_EQUAL_MESSAGE("second DP object was deleted, should return nullptr",
2599                                  static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u"DP2"));
2600     CPPUNIT_ASSERT_EQUAL_MESSAGE("empty string should return nullptr",
2601                                  static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u""));
2602     CPPUNIT_ASSERT_EQUAL_MESSAGE("non existent name should return nullptr",
2603                                  static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u"Non"));
2604
2605     // Clean-up
2606     m_pDoc->DeleteTab(1);
2607     m_pDoc->DeleteTab(0);
2608 }
2609
2610 void TestPivottable::testPivotTableMedianFunc()
2611 {
2612     m_pDoc->InsertTab(0, "Data");
2613     m_pDoc->InsertTab(1, "Table");
2614
2615     // Raw data
2616     const std::vector<std::vector<const char*>> aData = {
2617         { "Condition", "Day1Hit", "Day1Miss", "Day1FalseAlarm" },
2618         { "False Memory", "7", "3", "0" },
2619         { "Control", "10", "0", "1" },
2620         { "False Memory", "9", "1", "0" },
2621         { "Control", "9", "1", "2" },
2622         { "False Memory", "7", "3", "3" },
2623         { "Control", "10", "0", "0" },
2624         { "False Memory", "9", "1", "1" },
2625         { "Control", "6", "4", "2" },
2626         { "False Memory", "8", "2", "1" },
2627         { "Control", "7", "3", "3" },
2628         { "False Memory", "9", "1", "1" },
2629         { "Control", "10", "0", "0" },
2630         { "False Memory", "10", "0", "0" },
2631         { "Control", "10", "0", "0" },
2632         { "False Memory", "10", "0", "0" },
2633         { "Control", "9", "1", "1" },
2634         { "False Memory", "10", "0", "0" },
2635         { "Control", "10", "0", "0" },
2636     };
2637
2638     // Dimension definition
2639     static const DPFieldDef aFields[] = {
2640         { "Condition", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
2641         { "Day1Hit", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::MEDIAN, false },
2642         { "Day1Miss", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::MEDIAN, false },
2643         { "Day1FalseAlarm", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::MEDIAN, false },
2644     };
2645
2646     ScAddress aPos(1, 1, 0);
2647     ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
2648     CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);
2649
2650     std::unique_ptr<ScDPObject> pDPObj(createDPFromRange(
2651         m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false));
2652     CPPUNIT_ASSERT_MESSAGE("Failed to create pivot table object.", pDPObj);
2653
2654     // Create a new pivot table output.
2655     ScDBDocFunc aFunc(*m_xDocShell);
2656     bool bSuccess = aFunc.CreatePivotTable(*pDPObj, false, true);
2657     CPPUNIT_ASSERT_MESSAGE("Failed to create pivot table output via ScDBDocFunc.", bSuccess);
2658     ScDPCollection* pDPs = m_pDoc->GetDPCollection();
2659     CPPUNIT_ASSERT_MESSAGE("Failed to get pivot table collection.", pDPs);
2660     ScDPObject* pDPObject = &(*pDPs)[0];
2661     ScRange aOutRange = pDPObject->GetOutRange();
2662     {
2663         // Expected output table content.  0 = empty cell
2664         std::vector<std::vector<const char*>> aOutputCheck = {
2665             { "Condition", "Data", nullptr },
2666             { "Control", "Median - Day1Hit", "10" },
2667             { nullptr, "Median - Day1Miss", "0" },
2668             { nullptr, "Median - Day1FalseAlarm", "1", },
2669             { "False Memory", "Median - Day1Hit", "9" },
2670             { nullptr, "Median - Day1Miss", "1" },
2671             { nullptr, "Median - Day1FalseAlarm", "0", "0" },
2672             { "Total Median - Day1Hit", nullptr, "9", nullptr },
2673             { "Total Median - Day1Miss", nullptr, "1", nullptr },
2674             { "Total Median - Day1FalseAlarm", nullptr, "0.5", nullptr }
2675         };
2676
2677         bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Pivot table created via ScDBDocFunc");
2678         CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
2679     }
2680
2681     bSuccess = aFunc.RemovePivotTable(*pDPObject, false, true);
2682     CPPUNIT_ASSERT_MESSAGE("Failed to remove pivot table object.", bSuccess);
2683
2684     m_pDoc->DeleteTab(1);
2685     m_pDoc->DeleteTab(0);
2686 }
2687
2688 CPPUNIT_TEST_SUITE_REGISTRATION(TestPivottable);
2689
2690 CPPUNIT_PLUGIN_IMPLEMENT();
2691
2692 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */