Subtotal Label Not Displaying

Dennis Hancy

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

Dennis Hancy

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!

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.

Dennis Hancy

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

Dennis Hancy

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

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