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