Optional Date Parameters and ISNULL

[Former Member]

In a graphical Calculation View, I've created a single input parameter.  P_START_DATE

The notion is that if the user does not supply a date, the beginning of the period should be used, otherwise the user_specified_date should be used to filter table.

No matter what I do, $$P_START_DATE$$ evaluates to a nonnull value in any runtime expression but when copied to a calculated column, it displays as <NULL>.

I've tried setting the default to constant NULL, expression null.

I've tried setting the value of a calculated column L_START_DATE with nullif and if(isnull()).  Oddly, this column will display as <NULL> in the preview.

Puzzling....  But if this is the case, what's the point of a nonmandatory prompt? 

Bug?  SPS11

[rindia · Accepted solution]

Dear James,

The proposed solution for your requirement is documented here.

Please visit this link and let me know is this helpful.

Regards

Raj

[Former Member · Accepted solution]

Thank you!  I was actually trying to set a calculated column with the value.  It didn't dawn on me to prefill the field with the default date for the grading period.  Perfect!

The _SYS_BIC thing is a bit creepy, but for now it solves the problem.

[former_member182302 · Accepted solution]

Hi James ,

To let you know, adding to the blog results shared by Raj, if you feel like you don't want to auto populate the default value ( if the user has to enter a value he has to clear the value and re-enter )

You can keep the expression like below instead of checking with NULL and this would work as well

Regards,

Krishna Tangudu

[rindia · Accepted solution]

Hi Krishna,

If the first day of fiscal period is constant then your formula is perfect. But if it changes then we have to use the proc. I like avoiding the proc as much as possible.

Thanks Krishna.

Regards

Raj

[rindia · Accepted solution]

Thanks James for letting us know

Happy to help you.

Regards

Raj Kumar Salla

[former_member182302 · Accepted solution]

Agreed..

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

As I was playing with this, I tried a scalar UDF. I could not even assign it.  I think that's odd. 

Raj's solution worked for me (I needed to set the value of an interim calculated column that does need to be used in other calculations and procedures.  This seems to have solved the runtime order). I'll keep the expression solution in my bag as well for simpler calculations.

Thanks, everyone, for the speedy and educational responses!

[gopinath_kolli]

can you explain with some screenshots?