Oracle Business Intelligence

Products Banner

Subtotal Label Not Displaying

Received Response
22
Views
5
Comments

We have an analysis that displays subtotals at each change in the Payment Date value.  The subtotal includes a custom label.

This works great as long as there is a payment date.  But some records do not have a payment date.  In this case the subtotals are correct; however, the label does not display.

Does anyone have a way to work around this?

Thanks,

Dennis

Answers

  • Here is a screen print to demonstrate the issue.

    The lines with payment dates of 3/15/2019 and 4/1/2019 correctly print the subtotals as well as the "Total" label.

    But the line without a payment date value does not print the "Total" label.  It only shows the subtotal values.

    Any assistance you can provide is greatly appreciated.  Thanks!

    pastedImage_0.png

  • Jerry Casey
    Jerry Casey ✭✭✭✭✭

    Hi Dennis,

    If you cast your date column as char, you can use an ifnull statement like: IFNULL(CAST(datecol as CHAR),'No Date').  You'll get a line labeled No Date and a subtotal with a label.

    If you want to maintain the date format, you can use EVALUATE('TO_CHAR(%1,%2)' as char,DATECOL,'mm/dd/yyyy') instead of the CAST statement.

  • Thank you!!

    My co-worker and I have been looking at this for the past hour or so when we saw your reply.  His words: This is the best answer ever!

    It works like a champ.  Thanks again.


    Dennis

  • As a follow-up to this conversation, I wanted to let you know that we ended up combining both of your suggestions into this one:

    ifnull(EVALUATE('TO_CHAR(%1,%2)' as char,DATECOL,'fmmm/dd/yyyy'),'  ')

    We did have one further complication in that this field contains an action link.  Since the screen it linked to was expecting a "date" data type (and not character), this caused a little grief.  But we got around that by adding a hidden payment date field that contains the actual payment date value and not a formula.

    Anyway, this is all working as expected.  Thanks again for your invaluable input on this!

    Dennis

  • Jerry Casey
    Jerry Casey ✭✭✭✭✭

    Thanks for the follow-up.  Glad it's working for you.