How to Retrieve Pivot Detail Cell Value

[RAGHAVAN JAYARAMAN]

I'm having a problem getting the Current Cell for the Pivot Field which is
dropped on the Data Axis.
if (PivotTable.SelectionType == "PivotDetailRange")
{
var pvDetCell = pivotTable.Selection.BottomRight;
var RowNum = pvDetCell.Row;
var ColNum = pvDetCell.Row;
var pvCell = pivotTable.ActiveData.CurrentCell;
alert(pvCell.DetailCells(RowNum,ColNum).Value)
}
The above Code is not properly giving the Cell value , it always gives the
first Cell Value for each column . How can i get the Current Cell value like
that for Pivot Totals ( In Case of pivotTotals i could use
pivotTable.Selection(0) or PivotTable.Selection.Item(0) ) when Double Click
Occurs, but i cannot get current Pivot Detail Cell value.
Alternative Method ( Server Side -- Web Service )
Issue 2 ( I had Successfull Created Custom Hyperlink from Metadata XML File
).But i need to capture the Row member value ( for ex: I have dropped Last
Extracted fieldset on the Row Member which contains value Nov 2005 , Dec 2005
etc ) How can i read this value or Iterate through PivotRowMember /
PivotMember values so that i can dynamically append the Date to the Hyperlink
for the Calculated Field which is dropped on the Data Axis. ( ex: Row Axis :
Last Extracted ( Fieldset )
Data Axis : Cal Fld1 ( [count] + "#http://yourserver/test1.aspx?Date=(Last
Extracted ) #" [ something like this ].

 

[Mark Burns]

RAGHAVAN,

From some Access VBA code I have:
Dim selVal As Variant
'used because Access returns an abstracted interface, not an
OWC11.PivotTable instance (or any sub-objects thereof)

Set sel = Me.PivotTable.Selection
selVal = sel.Cell.Recordset.Fields(sel.TopLeft.Field.Name).Value
' Show the value.
If VarType(selVal) = vbString Then
Debug.Print "The Detail cell you double-clicked has a value of " &
Chr(34) & selVal & Chr(34) & "."
ElseIf (VarType(selVal) = vbDataObject) Or (VarType(selVal) = vbError)
Or (VarType(selVal) = vbObject) Then
Debug.Print "The Detail cell you double-clicked has a value of
<Object/Error/DataObject>."
ElseIf (VarType(selVal) = vbEmpty) Or IsNull(selVal) Then
Debug.Print "The Detail cell you double-clicked has a value of
<Empty>/<Null>."
Else
Debug.Print "The Detail cell you double-clicked has a value of " &
CStr(selVal) & "."
End If

Also be aware that if the user selects a cell that does not have a valud
value (or no record is bound to that cell in a back-end dataset/recordset)
you will not get a PivotDetailRange, you will instead see a PivotRange Object.

....I hope this helps.

 

[RAGHAVAN JAYARAMAN]

Hi Burns
Thanks!..it worked
Somehow , the Sel.Cell.RecordSet.Fields(Sel.Topleft.Field.Name).value gave
me this error
" Cell.RecordSet.Fields(.).value gives null or not a object" but the Idea
of Selecting
the Topleft worked for me.

I tried this way and it worked.
if (pta.SelectionType == "PivotDetailRange")
{

var Sel = pta.Selection;
var pvDetCell = Sel.TopLeft;
var ColNum = pvDetCell.Column;
var RowNum = pvDetCell.Row;

var pvCell = pta.ActiveData.CurrentCell;
alert("Your Current Selection " + pvCell.DetailCells(RowNum,ColNum).value);

}

Also in another report i have hyperlink for PivotDetails and it's calculated
Field
that contains Counts and Hyperlink underneath the values. This Hyperlink is
created on the Server side ( Web service ) and how can i eliminate the
Hyperlink for the Values that contains "0" ( Zero ) .Show hyperlink for
fields that contains only Positive field.

Is there any MDX Formula for Calculated Field , Is it possible to handle
Hyperlink ( click ) event.Not showing Hyperlink for Zero values would be
better choice.

Regards,
Raghavan J

RAGHAVAN,

From some Access VBA code I have:
Dim selVal As Variant
'used because Access returns an abstracted interface, not an
OWC11.PivotTable instance (or any sub-objects thereof)

Set sel = Me.PivotTable.Selection
selVal = sel.Cell.Recordset.Fields(sel.TopLeft.Field.Name).Value
' Show the value.
If VarType(selVal) = vbString Then
Debug.Print "The Detail cell you double-clicked has a value of " &
Chr(34) & selVal & Chr(34) & "."
ElseIf (VarType(selVal) = vbDataObject) Or (VarType(selVal) = vbError)
Or (VarType(selVal) = vbObject) Then
Debug.Print "The Detail cell you double-clicked has a value of
<Object/Error/DataObject>."
ElseIf (VarType(selVal) = vbEmpty) Or IsNull(selVal) Then
Debug.Print "The Detail cell you double-clicked has a value of
<Empty>/<Null>."
Else
Debug.Print "The Detail cell you double-clicked has a value of " &
CStr(selVal) & "."
End If

Also be aware that if the user selects a cell that does not have a valud
value (or no record is bound to that cell in a back-end dataset/recordset)
you will not get a PivotDetailRange, you will instead see a PivotRange Object.

...I hope this helps.

I'm having a problem getting the Current Cell for the Pivot Field which is
dropped on the Data Axis.
if (PivotTable.SelectionType == "PivotDetailRange")
{
var pvDetCell = pivotTable.Selection.BottomRight;
var RowNum = pvDetCell.Row;
var ColNum = pvDetCell.Row;
var pvCell = pivotTable.ActiveData.CurrentCell;
alert(pvCell.DetailCells(RowNum,ColNum).Value)
}
The above Code is not properly giving the Cell value , it always gives the
first Cell Value for each column . How can i get the Current Cell value like
that for Pivot Totals ( In Case of pivotTotals i could use
pivotTable.Selection(0) or PivotTable.Selection.Item(0) ) when Double Click
Occurs, but i cannot get current Pivot Detail Cell value.
Alternative Method ( Server Side -- Web Service )
Issue 2 ( I had Successfull Created Custom Hyperlink from Metadata XML File
).But i need to capture the Row member value ( for ex: I have dropped Last
Extracted fieldset on the Row Member which contains value Nov 2005 , Dec 2005
etc ) How can i read this value or Iterate through PivotRowMember /
PivotMember values so that i can dynamically append the Date to the Hyperlink
for the Calculated Field which is dropped on the Data Axis. ( ex: Row Axis :
Last Extracted ( Fieldset )
Data Axis : Cal Fld1 ( [count] + "#http://yourserver/test1.aspx?Date=(Last
Extracted ) #" [ something like this ].