This project has moved. For the latest updates, please go here.

Row or Column Filter Count versus Drillthrough Count

Topics: Developer Forum
Aug 20, 2010 at 3:20 PM
Edited Aug 26, 2010 at 9:45 AM

I reported the following issue to MS:

We have created an  Analysis Services (version 10.0.2734.0) Cube here and we access it using Excel 2007(version (12.0.6514.5000) SP1 MSO(12.0.6320.5000)) on Vista  6.0.6001 SP1. Central to our solution is the use of the Drillthrough functionality. We are using both the default drillthrough and custom drillthrough using CodePlex version 1.3

Our problem is that the cell count displayed in the Excel cell is *not* the value returned in the Drillthrough when we use Row or Column Label filters. It is the correct value when we use the Report Filters. We have also re-created this in the sample Adventure Works Cube. I attach a screen shot of a sample Pivot Table from Adventure Works. We are displaying the Internet sales count broken down by Country and date. We have used July 1, 2001 as a sample date. For example you can see that the internet sales count for Australia shows 5 but when you select ‘show details’ it displays the first 1000 rows.

The problem seems to be that it uses *all* dates and does not honour the row label date filter context.

MS replied that this is a design issue. We were wondering if it is possible using the Custom drillthrough functionality ro retrieve the 5 rows only that are displayed in the cell and not the all the rows for Austraila i.e. can we get a handle on the date row filter value in a custom drillthrough.

Thanks in advance.

 

Sean Marmion


 

 

Aug 24, 2010 at 9:14 AM

I received the following query to my personal email account but replying to it has bounced with an unknowen address error - so I will post the query here and my reply:

The following is the message from furmangg:

Your screenshot didn't come through on your post at http://asstoredprocedures.codeplex.com/Thread/View.aspx?ThreadId=224258. Can you explain again where you're double clicking? Can you send me your xlsx file?

For what it's worth, if you double-click on the grand total, when I try this in Excel 2007 and Excel 2010, I get an error message saying "You cannot use Expand to Detail on a Grand Total when a filter is applied to the row or column fields. To use this command, remove the filter."

__________________________________________________________________

 

 You cannot drillthrough on a Grand Total cell value. Ideally i need to attach my screen shot or sample workbook but I don't seem to be able to do here.

Coordinator
Aug 24, 2010 at 5:33 PM

I wish codeplex discussions allowed attachments. If you want to post the attachment or screenshot elsewhere and link it from here, I'd love to see further info.

This doesn't seem to be a problem with ASSP, so you may also want to pose this question on the SSAS forum at:
http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/threads/

Coordinator
Aug 24, 2010 at 11:05 PM

Can you post the steps to reproduce this issue? Without being able to see it, it's a bit hard to say if the stored proc can help or not. But if you have hit a "by design" issue the chances of being able to work around it are slim.

Aug 25, 2010 at 2:19 PM

OK guys,

here are links to the image and the excel file:

http://www.thefulloctave.com/sample/ExcelDrillthrough.jpg

http://www.thefulloctave.com/sample/ADDrillThroughBug2.xls

I refer to cells B4 and B5 in the excel workbook in sheet1. Sheet 19 is the drillthrough output for B4 and Sheet 21 is the drillthrough output for cell B5. My point is that B4 displays a value of 5 but the drillthrough returns much more than that. So I wonder is there anyway of getting a handle on the MDX that results in 5 for B4 and building a custom drillthrough using this codeplex project. We have developed a number of custom drillthroughs in our application using the GetDefaultDrillThroughMDX() method. But in this case the MDX only includes Australia and not the other row filter date of July 1, 2001. Cell B5 does this correctly. 

So you can imagine if you are using a lot of row filters, then it is disconcerting for our users to see a difference in the cell value and the corresponding drillthrough total. If you collapse Austraila and the Date filter is not visible then this adds to the disconcertion.

Coordinator
Aug 25, 2010 at 4:47 PM

Well, that xls file was corrupted when I downloaded it, but by the screenshot, I could repro what you were saying. I see why they said it was by design. But I agree this behavior could be improved.

B4 (the total for Australia) is showing 5 only because some fields nested below it (Date) have been filtered, and it's showing a "visual total"... that is that the total is only for July 1, 2001. However, when you drillthrough on that cell, Excel doesn't specify the coordinates for any fields nested below that. It doesn't filter on Date. So you get tons of rows on drillthrough.

SSAS drillthrough doesn't support multi-selects. So lets say you filtered to two dates, and B4 was the total of July 1 and July 2. There's nothing Excel could do about this unfortunately. So I think you're out of luck for now. But I would still recommend you request a feature on Connect: https://connect.microsoft.com/sqlserver/feedback. I would recommend you describe this scenario (wanting to see the Australia total, but have it match what you see in your PivotTable. And mention this may require an Excel change. And that it may require SSAS supporting multiselect on drillthrough.

If your users are using Excel 2010, right click on the PivotTable and choose PivotTable Options. Go to the Totals & Filters tab, and check Include filtered items in totals. Then the B4 number will match what you get on drillthrough. This doesn't solve the problem. But it may help explain the issue to your users.

Aug 25, 2010 at 5:28 PM

Thanks furmangg for getting back to me. I just tried the xls link in both IE and FireFox and it works ok for me. I will have another look at that though.

I see you say that drillthrough does not support multi-selects but this only applies to Report Filters. I could multi-select on my Row Filter and use both Austrailia and United Kingdom and my drillthrough works. I could also select July 1 and July 2 and this works in in row filter. When using Austrailia and July 1 and July 2 as my row filters, the drillthrough is correct for July 1 and July 2 but only incorrect for Austrailia.

And using the *Report* filter of Countries and Date in my scenario also works as this combination only produces one cell value. There the cell value and drillthrough value match. But this problem lies when using Row and Column filters only.

Unforunately we are using Excel 2007 but I would be keen to see how Excel 2010 handles this scenario as you state.

I feel it would be better if cell B4 reported the Internet Sales Count for Austraila only and then at least the drillthrough and cell total would match. Then the date filter nested beneath it would also be consistent.

Coordinator
Aug 25, 2010 at 8:49 PM

Oops. Looks like I had an issue with my wireless connection which corrupted the file when I downloaded it. Nevermind about the file being corrupted.

What I mean is that it is impossible to request a drillthrough from SSAS where the filter context is specified by a multi-select. So if you put Date on filters and filter to July 1 and July 2 (multi-select) it won't let you drillthrough. And even if they fixed Excel to handle your Australia scenario better, if you had row filtered Date to July 1 and July 2, it's currently impossible to request from SSAS one drillthrough command that includes both days, but only those days.

I believe the drillthrough on B4 does report only rows from Australia. The problem may be that the Geography dimension does not cross the Internet Orders measure group. So slicing by it has no impact. Use the Reseller Order Count measure instead of the measure you're using and you'll see what I mean.