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