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