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