summaryrefslogtreecommitdiff
path: root/sc
diff options
context:
space:
mode:
authorKohei Yoshida <kohei.yoshida@collabora.com>2014-01-06 23:45:46 -0500
committerKohei Yoshida <kohei.yoshida@collabora.com>2014-01-07 00:52:17 -0500
commite5dcc06cf898109a1dd8218cf5eeecb83fb01492 (patch)
tree26cfdfcaacc2000545ced96468c85cc307b7eb46 /sc
parentc009bdb061d074bbddd58a16bdf65022e2099ba5 (diff)
fdo#72645: Allow GETPIVOTDATA to get result from leaf node of result tree.
Change-Id: I0fc1fd069440ed6fee378fc2dfd2ed761afbdeab (cherry picked from commit 8728f8e8705cfb6875a315aef85ec6004604e702)
Diffstat (limited to 'sc')
-rw-r--r--sc/inc/dpresfilter.hxx12
-rw-r--r--sc/qa/unit/ucalc.hxx2
-rw-r--r--sc/qa/unit/ucalc_pivottable.cxx93
-rw-r--r--sc/source/core/data/dpobject.cxx4
-rw-r--r--sc/source/core/data/dpresfilter.cxx44
-rw-r--r--sc/source/core/data/dptabsrc.cxx28
6 files changed, 174 insertions, 9 deletions
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 <map>
#include <vector>
#include <boost/noncopyable.hpp>
+#include <boost/unordered_map.hpp>
namespace com { namespace sun { namespace star { namespace sheet {
@@ -85,6 +86,15 @@ private:
#endif
};
+ typedef std::pair<OUString, OUString> NamePairType;
+
+ struct NamePairHash
+ {
+ size_t operator() (const NamePairType& rPair) const;
+ };
+ typedef boost::unordered_map<NamePairType, double, NamePairHash> 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 af895b41f809..f73189d2a10e 100644
--- a/sc/qa/unit/ucalc.hxx
+++ b/sc/qa/unit/ucalc.hxx
@@ -122,6 +122,7 @@ public:
void testFuncINDIRECT();
void testFuncIFERROR();
void testFuncGETPIVOTDATA();
+ void testFuncGETPIVOTDATALeafAccess();
void testCopyAttributes();
void testCopyToDocument();
@@ -337,6 +338,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<size_t>(0));
+ CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
+ pDPs->GetSheetCaches().size(), static_cast<size_t>(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 7f37011274ae..e244e315cbca 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<sheet::GeneralFunction>(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 <rtl/math.hxx>
+
#include <com/sun/star/sheet/DataPilotFieldFilter.hpp>
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<ScDPResultFilter>::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 b8bcfaa9fc28..2809b0e062bb 100644
--- a/sc/source/core/data/dptabsrc.cxx
+++ b/sc/source/core/data/dptabsrc.cxx
@@ -423,15 +423,29 @@ uno::Sequence<double> ScDPSource::getFilteredResults(
// Get result values from the tree.
const ScDPResultTree::ValuesType* pVals = maResFilterSet.getResults(aFilters);
- if (!pVals)
- return uno::Sequence<double>();
+ if (pVals)
+ {
+ size_t n = pVals->size();
+ uno::Sequence<double> aRet(n);
+ for (size_t i = 0; i < n; ++i)
+ aRet[i] = (*pVals)[i];
- size_t n = pVals->size();
- uno::Sequence<double> 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<double> aRet(1);
+ aRet[0] = fVal;
+ return aRet;
+ }
+ }
+
+ return uno::Sequence<double>();
}
void SAL_CALL ScDPSource::refresh() throw(uno::RuntimeException)