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