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