From 8728f8e8705cfb6875a315aef85ec6004604e702 Mon Sep 17 00:00:00 2001 From: Kohei Yoshida Date: Mon, 6 Jan 2014 23:45:46 -0500 Subject: fdo#72645: Allow GETPIVOTDATA to get result from leaf node of result tree. Change-Id: I0fc1fd069440ed6fee378fc2dfd2ed761afbdeab --- sc/inc/dpresfilter.hxx | 12 +++++ sc/qa/unit/ucalc.hxx | 2 + sc/qa/unit/ucalc_pivottable.cxx | 93 +++++++++++++++++++++++++++++++++++++ sc/source/core/data/dpobject.cxx | 4 +- sc/source/core/data/dpresfilter.cxx | 44 ++++++++++++++++++ sc/source/core/data/dptabsrc.cxx | 28 ++++++++--- 6 files changed, 174 insertions(+), 9 deletions(-) (limited to 'sc') diff --git a/sc/inc/dpresfilter.hxx b/sc/inc/dpresfilter.hxx index be84b8c83c2d..bc7ffc1f62b3 100644 --- a/sc/inc/dpresfilter.hxx +++ b/sc/inc/dpresfilter.hxx @@ -15,6 +15,7 @@ #include #include #include +#include namespace com { namespace sun { namespace star { namespace sheet { @@ -85,6 +86,15 @@ private: #endif }; + typedef std::pair NamePairType; + + struct NamePairHash + { + size_t operator() (const NamePairType& rPair) const; + }; + typedef boost::unordered_map LeafValuesType; + LeafValuesType maLeafValues; + OUString maPrimaryDimName; MemberNode* mpRoot; @@ -115,6 +125,8 @@ public: const com::sun::star::uno::Sequence< com::sun::star::sheet::DataPilotFieldFilter>& rFilters) const; + double getLeafResult(const com::sun::star::sheet::DataPilotFieldFilter& rFilter) const; + #if DEBUG_PIVOT_TABLE void dump() const; #endif diff --git a/sc/qa/unit/ucalc.hxx b/sc/qa/unit/ucalc.hxx index 98528f6d6734..b56c31e86063 100644 --- a/sc/qa/unit/ucalc.hxx +++ b/sc/qa/unit/ucalc.hxx @@ -123,6 +123,7 @@ public: void testFuncINDIRECT(); void testFuncIFERROR(); void testFuncGETPIVOTDATA(); + void testFuncGETPIVOTDATALeafAccess(); void testCopyAttributes(); void testCopyToDocument(); @@ -340,6 +341,7 @@ public: CPPUNIT_TEST(testFuncINDIRECT); CPPUNIT_TEST(testFuncIFERROR); CPPUNIT_TEST(testFuncGETPIVOTDATA); + CPPUNIT_TEST(testFuncGETPIVOTDATALeafAccess); CPPUNIT_TEST(testCopyToDocument); CPPUNIT_TEST(testSheetsFunc); CPPUNIT_TEST(testVolatileFunc); diff --git a/sc/qa/unit/ucalc_pivottable.cxx b/sc/qa/unit/ucalc_pivottable.cxx index a052adcf32e1..00e83b9f122c 100644 --- a/sc/qa/unit/ucalc_pivottable.cxx +++ b/sc/qa/unit/ucalc_pivottable.cxx @@ -2235,4 +2235,97 @@ void Test::testFuncGETPIVOTDATA() m_pDoc->DeleteTab(0); } +void Test::testFuncGETPIVOTDATALeafAccess() +{ + m_pDoc->InsertTab(0, "Data"); + m_pDoc->InsertTab(1, "Table"); + + // Raw data + const char* aData[][3] = { + { "Type", "Member", "Value" }, + { "A", "Anna", "1" }, + { "B", "Brittany", "2" }, + { "A", "Cecilia", "3" }, + { "B", "Donna", "4" }, + }; + + ScAddress aPos(1,1,0); + ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData)); + CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos); + + ScDPObject* pDPObj = NULL; + + // Dimension definition + DPFieldDef aFields[] = { + { "Type", sheet::DataPilotFieldOrientation_ROW, 0 }, + { "Member", sheet::DataPilotFieldOrientation_ROW, 0 }, + { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM }, + }; + + // Create pivot table at A1 on 2nd sheet. + pDPObj = createDPFromRange(m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false); + + ScDPCollection* pDPs = m_pDoc->GetDPCollection(); + bool bSuccess = pDPs->InsertNewTable(pDPObj); + + CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess); + CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.", + pDPs->GetCount() == 1); + pDPObj->SetName(pDPs->CreateNewName()); + ScRange aOutRange = refresh(pDPObj); + + { + // Expected output table content. 0 = empty cell + const char* aOutputCheck[][3] = { + { "Type", "Member", 0 }, + { "A", "Anna", "1" }, + { 0, "Cecilia", "3" }, + { "B", "Brittany", "2" }, + { 0, "Donna", "4" }, + { "Total Result", 0, "10" }, + }; + + bSuccess = checkDPTableOutput<3>(m_pDoc, aOutRange, aOutputCheck, "Pivot table refreshed"); + CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess); + } + + // Insert formulas with GETPIVOTDATA in column E, and check their results. + + struct Check + { + const char* mpFormula; + double mfResult; + }; + + Check aChecks[] = { + { "=GETPIVOTDATA($A$1;\"Member[Anna]\")", 1.0 }, + { "=GETPIVOTDATA($A$1;\"Member[Brittany]\")", 2.0 }, + { "=GETPIVOTDATA($A$1;\"Member[Cecilia]\")", 3.0 }, + { "=GETPIVOTDATA($A$1;\"Member[Donna]\")", 4.0 }, + }; + + for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i) + m_pDoc->SetString(ScAddress(4,i,1), OUString::createFromAscii(aChecks[i].mpFormula)); + + m_pDoc->CalcAll(); + + const sal_uInt16 nNoError = 0; // no error + for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i) + { + sal_uInt16 nErr = m_pDoc->GetErrCode(ScAddress(4,i,1)); + CPPUNIT_ASSERT_EQUAL(nNoError, nErr); + double fVal = m_pDoc->GetValue(ScAddress(4,i,1)); + CPPUNIT_ASSERT_EQUAL(aChecks[i].mfResult, fVal); + } + + pDPs->FreeTable(pDPObj); + + CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast(0)); + CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.", + pDPs->GetSheetCaches().size(), static_cast(0)); + + m_pDoc->DeleteTab(1); + m_pDoc->DeleteTab(0); +} + /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/core/data/dpobject.cxx b/sc/source/core/data/dpobject.cxx index 677bd5e3f19f..4370d606da01 100644 --- a/sc/source/core/data/dpobject.cxx +++ b/sc/source/core/data/dpobject.cxx @@ -1328,8 +1328,8 @@ public: { // Layout name takes precedence. const OUString* pLayoutName = pDim->GetLayoutName(); - if (pLayoutName) - return *pLayoutName == maName; + if (pLayoutName && *pLayoutName == maName) + return true; sheet::GeneralFunction eGenFunc = static_cast(pDim->GetFunction()); ScSubTotalFunc eFunc = ScDPUtil::toSubTotalFunc(eGenFunc); diff --git a/sc/source/core/data/dpresfilter.cxx b/sc/source/core/data/dpresfilter.cxx index 968be6b424e7..41b2d7eb396d 100644 --- a/sc/source/core/data/dpresfilter.cxx +++ b/sc/source/core/data/dpresfilter.cxx @@ -10,6 +10,8 @@ #include "dpresfilter.hxx" #include "global.hxx" +#include + #include using namespace com::sun::star; @@ -21,6 +23,12 @@ ScDPResultFilter::ScDPResultFilter(const OUString& rDimName, bool bDataLayout) : ScDPResultFilterContext::ScDPResultFilterContext() : mnCol(0), mnRow(0) {} +size_t ScDPResultTree::NamePairHash::operator() (const NamePairType& rPair) const +{ + OUStringHash aHash; + return aHash(rPair.first) + aHash(rPair.second); +} + ScDPResultTree::DimensionNode::DimensionNode(const MemberNode* pParent) : mpParent(pParent) {} @@ -89,6 +97,8 @@ void ScDPResultTree::add( { // TODO: I'll work on the col / row to value node mapping later. + const OUString* pDimName = NULL; + const OUString* pMemName = NULL; MemberNode* pMemNode = mpRoot; std::vector::const_iterator itFilter = rFilters.begin(), itFilterEnd = rFilters.end(); @@ -117,6 +127,8 @@ void ScDPResultTree::add( itDim = r.first; } + pDimName = &itDim->first; + // Now, see if this dimension member exists. DimensionNode* pDim = itDim->second; MembersType& rMembers = pDim->maChildMembers; @@ -135,9 +147,26 @@ void ScDPResultTree::add( itMem = r.first; } + pMemName = &itMem->first; pMemNode = itMem->second; } + if (pDimName && pMemName) + { + NamePairType aNames(*pDimName, *pMemName); + LeafValuesType::iterator it = maLeafValues.find(aNames); + if (it == maLeafValues.end()) + { + // This name pair doesn't exist. Associate a new value for it. + maLeafValues.insert(LeafValuesType::value_type(aNames, fVal)); + } + else + { + // This name pair already exists. Set the value to NaN. + rtl::math::setNan(&it->second); + } + } + pMemNode->maValues.push_back(fVal); } @@ -145,6 +174,7 @@ void ScDPResultTree::swap(ScDPResultTree& rOther) { std::swap(maPrimaryDimName, rOther.maPrimaryDimName); std::swap(mpRoot, rOther.mpRoot); + maLeafValues.swap(rOther.maLeafValues); } bool ScDPResultTree::empty() const @@ -184,6 +214,20 @@ const ScDPResultTree::ValuesType* ScDPResultTree::getResults( return &pMember->maValues; } +double ScDPResultTree::getLeafResult(const com::sun::star::sheet::DataPilotFieldFilter& rFilter) const +{ + NamePairType aPair(rFilter.FieldName, rFilter.MatchValue); + LeafValuesType::const_iterator it = maLeafValues.find(aPair); + if (it != maLeafValues.end()) + // Found! + return it->second; + + // Not found. Return an NaN. + double fNan; + rtl::math::setNan(&fNan); + return fNan; +} + #if DEBUG_PIVOT_TABLE void ScDPResultTree::dump() const { diff --git a/sc/source/core/data/dptabsrc.cxx b/sc/source/core/data/dptabsrc.cxx index 2dcb23189ab7..2371e4f87a5a 100644 --- a/sc/source/core/data/dptabsrc.cxx +++ b/sc/source/core/data/dptabsrc.cxx @@ -423,15 +423,29 @@ uno::Sequence ScDPSource::getFilteredResults( // Get result values from the tree. const ScDPResultTree::ValuesType* pVals = maResFilterSet.getResults(aFilters); - if (!pVals) - return uno::Sequence(); + if (pVals) + { + size_t n = pVals->size(); + uno::Sequence aRet(n); + for (size_t i = 0; i < n; ++i) + aRet[i] = (*pVals)[i]; - size_t n = pVals->size(); - uno::Sequence aRet(n); - for (size_t i = 0; i < n; ++i) - aRet[i] = (*pVals)[i]; + return aRet; + } - return aRet; + if (aFilters.getLength() == 1) + { + // Try to get result from the leaf nodes. + double fVal = maResFilterSet.getLeafResult(aFilters[0]); + if (!rtl::math::isNan(fVal)) + { + uno::Sequence aRet(1); + aRet[0] = fVal; + return aRet; + } + } + + return uno::Sequence(); } void SAL_CALL ScDPSource::refresh() throw(uno::RuntimeException) -- cgit