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