Skip to main content

Problems With Conditional Metric and Drill Down

If you are coming across the problem with Conditional metric and Drill Down, you will want to kick somebody's behind after reading the solution(s)!

We have a hierarchy defined as Region - Country - State - City - Zipcode - Branch. For each Branch there is a count of Vistors. The report starts with simply Region and Count of Visitors. You should be able to drill down all the way to Branch from Region. Count of Visitors is a conditional metric based on certain filters. So, the requirements sounds pretty simple, right? Yes, and it is simple.

But for some reason when I built out the report in less then 5 minutes the drill down would not work. Clicking Americas to drill down and expecting to see Canada, USA and Mexico, I was seeing all European and Asian countries as well. I thought there was something major was wrong with relationships or data or even the ETL. This is a pretty simple report so obviously the issue has to be a major one for this report to not work, right? Right? Wrrrrongg!

I had been scratching my head for about 2 hours until somebody pointed out to me to check the Advanced property for Metric Condition. Voila - that's where the issue was; a checkbox that should be unchecked was still checked!


Metric Editor - Condition
Metric Condition > Advanced Options
Uncheck the above checkbox "Remove related report filter elements" and your problem will be fixed.

Now, there is another way to work this out as well. Create a Custom Group with the filters defined within the group definition. Also create a simple metric which is Count of Visitors without any kind of filters. You should be able to use this without any problems as well. Just remember, Custom Groups have more limited functionality than actual Metric.

Comments

The Popular Ones

Using SQL To Calculate XIRR (Internal Rate of Return)

Thanks to binaryworld.net , I was finally able to get a sql way to calculate XIRR. After 2 long hours of search I found this site and the logic as well as the code works perfectly well! XIRR is a function in excel that calculates Internal Rate of Return based on payments/income over a period of time. Without further ado, here is the code (a slightly modified version from BinaryWorld.net. Happy XIRRing! -- First, CREATE XIRR Table to store values CREATE TABLE XIRRTempData( amt float, dt datetime, guid varchar(128) ) go create function dbo.XIRR( @d datetime, @GUID varchar(128) ) returns decimal(18,10) as begin /* USAGE: select @IRR = dbo.xirr(null, guid) select @IRR IRR, @IRR * 100 'IRR %' Note: Leave the first parameter (date) null if you wish to see the XIRR calculated as of the maximum date in the dataset provided else provide a specific date to see the XIRR calculated as the given date. Created By: Ankeet Shah Created On: 7/16/2008 */ IF @d is null SELECT @d = max(d) from Inc...

Alternating Row Background Color For SSRS Matrix (Pivot Table)

I had a tough time to apply alternate row colors to a SSRS Matrix and finally figured out! Without further ado, here it is... Assume you have a matrix with more than 1, lets say 2 row groupings; RG1 and RG2. 1. Right-click on RG2 (innermost row group), and select "Insert Group"; for simplicity call it "RowColorGroup" 2. In the "Group On" section, add some constant value - for example ="" or ="ankeet" etc... you get the idea! 3. Select the newly created group "RowColorGroup" and enter the following in its "Value" property: =iif(RunningValue(Fields!RG1.Value & Fields!RG2.Value,CountDistinct,Nothing) Mod 2, "LightSteelBlue", "White") 4. Select the "BackgroundColor" property of "RowColorGroup" and enter "=Value" 5. Set the width of "RowColorGroup" to 0pt and "CanGrow" to false 6. Select the data cell(s) and set their "BackgroundColor" pro...

cannot create a column accessor for OLE DB provider "ibmdasql" for linked server

I have a linked server from Microsoft SQL 2008 to a DB2 server. Today when I tried to run a SELECT statement based on the linked server, I hit this error, "cannot create a column accessor for OLE DB provider "ibmdasql" for linked server". Earlier in the day, we had restarted the SQL Server Service. Running the following script on the 'affected' sql server should fix the issue. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[usp_enum_oledb_providers] AS exec sp_enum_oledb_providers GO sp_procoption 'usp_enum_oledb_providers', 'startup', 1 Restart the sql server service after running above script.