Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Filter on drill down does not work

Received Response
31
Views
3
Comments
1037201
1037201 Rank 1 - Community Starter
edited Aug 13, 2024 5:57PM in Oracle Analytics Cloud and Server

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.

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    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...

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    +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
    Robert Angel Rank 8 - Analytics Strategist

    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 ;-)