Get Free Trial

Pivot grid drill through values incorrect w/ MS Analysis Services

Braxton Goodman asked on September 13, 2018

Tanya had responded to my prior question regarding showDrillThroughConfigurator.  Can you please confirm if the drill through feature is compatible at all when using MS Analysis Services as the pivot grid data source?  What I am seeing are that values in the drill-through only occasionally sum up to the value of the cell.  More often than not the values don’t add up and appear to often contain values from a larger dataset that should not be within scope of the cell that was selected.  Other times I get no records when selecting a cell that should return many rows.  Whether the data is correct or not, it also also maxes out at 1000 detail records.  Is this the limit?  

3 answers

Iryna Kulchytska Iryna Kulchytska Flexmonster September 14, 2018

Hi Braxton,

Thank you for posting your question and for the reference to the previous one.

The drill through feature works a bit different when using MS Analysis Services as the pivot grid data source or when using simple data like JSON, CSV as the pivot grid data source. In case of MS Analysis Services, the drill through data is requested from MS Analysis Services and just displayed in the drill through pop-up. As Tanya wrote in her answer to your previous question, there is no option to configure which columns to show there. The data is just displayed as it comes from MS Analysis Services as the result of DRILLTHROUGH MDX query. 

About the inconsistency you are describing, do you experience some randomness in the result of clicking the same cell on the pivot table? We cannot control what MS Analysis Services return to the pivot table component, but the data should definitely be the same when you do drill through for the same cell and nothing was changed in the cube. Can you share more details about this with us?

This is correct that MAXROWS parameter is used for DRILLTHROUGH query and it is 1000. If you connect to the cube via XMLA, there is a drillthroughMaxRows option to set the maximal number of rows for the drill through pop-up for MS Analysis Services ( If you use Flexmonster Accelerator to connect to the cube, there is no such option for Flexmonster Accelerator right now but it can be added. Please let us know if you need it.

Kind regards,

Braxton Goodman September 17, 2018

Hi Iryna,
The results are consistent between multiple clicks of the same cell, just not correct the majority of the time. 
On my columns I have “forecast” then “year”.  If I select the cell for the entire forecast, the drill through values add up correctly, occasionally.  On the occasion that the cell does add up correctly in drill through details, if I then expand the pivot grid to the year level and then select a cell for a particular year, then the drill through values show no records at all.  The drill through window correctly displays the row, column, and amount criteria at the top, just no records.
Another situation I observe is when I select a cell, the drill through detail results contain the correct values plus additional values that pertain to all the other columns, which should not be included.  It’s as if it’s correct on the row criteria, but appears to ignore the column criteria.
Lastly, it seems the more I expand to child levels on both the row and column axis, the more it goes into the weeds, displaying no results or only incorrect results that don’t add up to the correct value.

Dmytro Zvazhii Dmytro Zvazhii Flexmonster September 17, 2018

Hi Braxton,
Thank you for the clarification. Could please provide us with the access to your environment so we can make all the necessary tests? That will definitely help us in our further investigation. You can send all the sensitive information on our email. Also, it is important for us to know if you are using the Flexmonster’s accelerator. Meanwhile, could you please check if the same type of issue occurs on our demo page with our SSAS cube:
Waiting for the update from you.

Please login or Register to Submit Answer