Filter on drill down does not work

Question

Hi,

I have a couple of analyses, one base analysis and the other one is for drill down. In the base analysis, when I try to drill down based on a column in a pivot table, the filter does not work as expected. The column is 'Month' which is one of the Selected columns in both the analyses. I have edited the formula for the Month column to be - CONCAT(CONCAT(MONTHNAME("Bug Header"."Filed Date"),'-'),CAST(YEAR("Bug Header"."Filed Date") AS CHAR)) in both analyses.

Now, when I click on a cell in the column 'Aug-2017' for drill down, I get no result for the filter - CONCAT( CONCAT( MONTHNAME(Filed Date), '-'), CAST ( EXTRACT( YEAR FROM Filed Date) AS CHARACTER ( 30 ))) is equal to / is in Aug-2017.

Any thoughts on what am I doing wrong here?

Thanks.

Robert Angel · Answer

Very kind.

I always think that if we just do what is asked we are not doing our jobs, we should be providing the best advice we can to protect the client from bad practise and its long term and short term consequences.

Now if only the askers of the questions would mark their answers ;-)

Thomas Dodds · Answer

+1 to @Robert Angel

Use of information drives design ... you are attempting to back into a deficient design.  Robert has given you the 'best, better, worst'.

I advise you to pursue #2

Robert Angel · Answer

Hi,

the default drills will only work on real fields, not calculated ones, how would the system 'know' that formula X is the same as formula Y in another report?

Ways around this; -

1. Populate a variable with your value to filter on and filter your target based on the variable

2. Have a decently designed time hierarchy that has the full syntax for Year month already formed and use this in the reports

3. Generate your formula in the rpd then you will have it as a 'real' column in both reports to drill on

2 is best

3 next

1 is a workaround

There are others...