Anyone know how to get the ParamValue macro function to not return a string? Here is a simple challenge using the Branches query in the sample GO data module - but should be easy to replicate on any data module.  

Create a macro like this: #'Branch_code'#

This macro is the equivalent of a calculation just saying Branch_code. You can see in the preview it is working as expected - its just returning the values in the branch code column. 

How about: #ParamValue( 'UnresolvedParameter', 'Branch_code', 'token' )#

I would expect this to show the same. I am asking for a token, not a string. Yet this returns:  

 

Even using the help example, returns a string....

If you try that last example then this is returned....

Not the expression but a string of the expression...

------------------------------
Marc Reed
------------------------------

Hi Marc, I can't tell why the IBM example for data type token doesn't work in the paramvalue macro. But you can use the prompt macro for this purpose. Same arguments but different order (see information). 

------------------------------
Philipp Hornung
Business Intelligence Manager
Techniker Krankenkasse
Hamburg Germany
#IBMChampion
------------------------------

Hi Phillip,

Yes, the old faithful macro prompt returns the correct data type. However, this can cause issues in data modules - randomly causing unresolved prompt validation errors (even when you supply defaults). I think this is why ParamValue was created and why I am trying to use it. 
I can also think of many instances where I want to use a paramvalue and not have a system generated prompt appear - which would happen happen with the prompt function.

------------------------------
Marc Reed
------------------------------

Original Message:
Sent: Thu August 31, 2023 05:01 AM
From: Philipp Hornung
Subject: Macro ParamValue function always returns a string

Hi Marc, I can't tell why the IBM example for data type token doesn't work in the paramvalue macro. But you can use the prompt macro for this purpose. Same arguments but different order (see information). 

------------------------------
Philipp Hornung
Business Intelligence Manager
Techniker Krankenkasse
Hamburg Germany
#IBMChampion
------------------------------

Hi Marc, 

I'm aware of the prompt macro errors thrown in data moduls (e.g. in the data preview). But I ignore them systematically and never found a real problem with it (and the data module itself actually validates). 

And in reports you don't need to feed any prompt from a macro as long as you provide appropriate defaults everywhere you use them (and really execute them at least once with the report). 

But as far as I know from the IBM roadmap they are planning to introduce the prompt pages from reports (CA12) also for dashboards - and data modules! That sould really solve all of such problems :-) 

Best regards

Philipp

------------------------------
Philipp Hornung
Business Intelligence Manager
Techniker Krankenkasse
Hamburg Germany
#IBMChampion
------------------------------

Original Message:
Sent: Thu August 31, 2023 05:18 AM
From: Marc Reed
Subject: Macro ParamValue function always returns a string

Hi Phillip,

Yes, the old faithful macro prompt returns the correct data type. However, this can cause issues in data modules - randomly causing unresolved prompt validation errors (even when you supply defaults). I think this is why ParamValue was created and why I am trying to use it. 
I can also think of many instances where I want to use a paramvalue and not have a system generated prompt appear - which would happen happen with the prompt function.

------------------------------
Marc Reed
------------------------------


Original Message:
Sent: Thu August 31, 2023 05:01 AM
From: Philipp Hornung
Subject: Macro ParamValue function always returns a string

Hi Marc, I can't tell why the IBM example for data type token doesn't work in the paramvalue macro. But you can use the prompt macro for this purpose. Same arguments but different order (see information). 

------------------------------
Philipp Hornung
Business Intelligence Manager
Techniker Krankenkasse
Hamburg Germany
#IBMChampion

Hi Marc,
Sorry for the inconvenience, it is a bug.
When 'token' is given as a datatype, it is erroneously interpreted as a 'string'.
Customer support will follow up with you and get it logged.

(It was introduced for handling the global parameter that gives the 'Time perspective' for the Date Relative calculations that are defined in the Gregorian Calendar Data Module. )

------------------------------
Kind regards,
Henk Cazemier
------------------------------

Hi Marc,

There is a workaround for this issue that may work in your situation. This workaround will continue to work even when the bug-fix has been delivered, as long as the response to the prompt is not a quoted string.
Instead of using:
#ParamValue ( 'PString', ' cast ( null as varchar(20)) ' , 'token' )#
Change it to:
# substitute("'$", "", substitute("^'", "", 
ParamValue ( 'PString', ' cast ( null as varchar(20)) ' , 'token' )
))
#
It's a Buckley fix (It tastes awful, and it works).

The substitute operations take out a single quote at the beginning and end of the response of the prompt.

------------------------------
Kind regards,
Henk Cazemier
------------------------------

Original Message:
Sent: Thu August 31, 2023 12:38 PM
From: HENK CAZEMIER
Subject: Macro ParamValue function always returns a string

Hi Marc,
Sorry for the inconvenience, it is a bug.
When 'token' is given as a datatype, it is erroneously interpreted as a 'string'.
Customer support will follow up with you and get it logged.

(It was introduced for handling the global parameter that gives the 'Time perspective' for the Date Relative calculations that are defined in the Gregorian Calendar Data Module. )

------------------------------
Kind regards,
Henk Cazemier
------------------------------

@HENK CAZEMIER as always a huge thanks, it confirms I am not using the function incorrectly. I will use that work around. Issue is logged under PH56673.

------------------------------
Marc Reed
------------------------------

Original Message:
Sent: Thu August 31, 2023 03:24 PM
From: HENK CAZEMIER
Subject: Macro ParamValue function always returns a string

Hi Marc,

There is a workaround for this issue that may work in your situation. This workaround will continue to work even when the bug-fix has been delivered, as long as the response to the prompt is not a quoted string.
Instead of using:
#ParamValue ( 'PString', ' cast ( null as varchar(20)) ' , 'token' )#
Change it to:
# substitute("'$", "", substitute("^'", "", 
ParamValue ( 'PString', ' cast ( null as varchar(20)) ' , 'token' )
))
#
It's a Buckley fix (It tastes awful, and it works).

The substitute operations take out a single quote at the beginning and end of the response of the prompt.

------------------------------
Kind regards,
Henk Cazemier
------------------------------


Original Message:
Sent: Thu August 31, 2023 12:38 PM
From: HENK CAZEMIER
Subject: Macro ParamValue function always returns a string

Hi Marc,
Sorry for the inconvenience, it is a bug.
When 'token' is given as a datatype, it is erroneously interpreted as a 'string'.
Customer support will follow up with you and get it logged.

(It was introduced for handling the global parameter that gives the 'Time perspective' for the Date Relative calculations that are defined in the Gregorian Calendar Data Module. )

------------------------------
Kind regards,
Henk Cazemier

@HENK CAZEMIER  an update for you. I did a similar thing using STRIP. It made the code a bit easier.

#strip( ParamValue ( 'PString', ' cast ( null as varchar(20)) ' , 'token' ), "'") #

------------------------------
Marc Reed
------------------------------

Original Message:
Sent: Thu August 31, 2023 03:24 PM
From: HENK CAZEMIER
Subject: Macro ParamValue function always returns a string

Hi Marc,

There is a workaround for this issue that may work in your situation. This workaround will continue to work even when the bug-fix has been delivered, as long as the response to the prompt is not a quoted string.
Instead of using:
#ParamValue ( 'PString', ' cast ( null as varchar(20)) ' , 'token' )#
Change it to:
# substitute("'$", "", substitute("^'", "", 
ParamValue ( 'PString', ' cast ( null as varchar(20)) ' , 'token' )
))
#
It's a Buckley fix (It tastes awful, and it works).

The substitute operations take out a single quote at the beginning and end of the response of the prompt.

------------------------------
Kind regards,
Henk Cazemier
------------------------------


Original Message:
Sent: Thu August 31, 2023 12:38 PM
From: HENK CAZEMIER
Subject: Macro ParamValue function always returns a string

Hi Marc,
Sorry for the inconvenience, it is a bug.
When 'token' is given as a datatype, it is erroneously interpreted as a 'string'.
Customer support will follow up with you and get it logged.

(It was introduced for handling the global parameter that gives the 'Time perspective' for the Date Relative calculations that are defined in the Gregorian Calendar Data Module. )

------------------------------
Kind regards,
Henk Cazemier

Hi Marc and Henk, 

I've been playing a bit with the paramvalue and the prompt macro. We're using them for parameter default values of optional prompts in data elements (before CA 12). 

Single select:

In reports I didn't figure out a functional difference as long as I'm using a prompt widget from the toolbox anyway (what makes the parameter exist). 

In data modules the prompt macro creates an optional prompt and the paramvalue macro not (like you told). That could be meaningful in dashboards where you have limited options to handle parameters. In reports that could be annoying if not needed. 

Multi select: 

I didn't figure out how to use the paramvalue macro such that it doesn't turn a multi select parameter into a single select one. The paramvalue and paramdisplayvalue layout calculations in turn can handle multi select parameters. Is it such that the paramvalue macro doesn't support multi select at all? Or if it's possible, how? 

------------------------------
Philipp Hornung
Business Intelligence Manager
Techniker Krankenkasse
Hamburg Germany
#IBMChampion
------------------------------

Original Message:
Sent: Fri September 01, 2023 08:31 AM
From: Marc Reed
Subject: Macro ParamValue function always returns a string

@HENK CAZEMIER  an update for you. I did a similar thing using STRIP. It made the code a bit easier.

#strip( ParamValue ( 'PString', ' cast ( null as varchar(20)) ' , 'token' ), "'") #

------------------------------
Marc Reed
------------------------------


Original Message:
Sent: Thu August 31, 2023 03:24 PM
From: HENK CAZEMIER
Subject: Macro ParamValue function always returns a string

Hi Marc,

There is a workaround for this issue that may work in your situation. This workaround will continue to work even when the bug-fix has been delivered, as long as the response to the prompt is not a quoted string.
Instead of using:
#ParamValue ( 'PString', ' cast ( null as varchar(20)) ' , 'token' )#
Change it to:
# substitute("'$", "", substitute("^'", "", 
ParamValue ( 'PString', ' cast ( null as varchar(20)) ' , 'token' )
))
#
It's a Buckley fix (It tastes awful, and it works).

The substitute operations take out a single quote at the beginning and end of the response of the prompt.

------------------------------
Kind regards,
Henk Cazemier


Original Message:
Sent: Thu August 31, 2023 12:38 PM
From: HENK CAZEMIER
Subject: Macro ParamValue function always returns a string

Hi Marc,
Sorry for the inconvenience, it is a bug.
When 'token' is given as a datatype, it is erroneously interpreted as a 'string'.
Customer support will follow up with you and get it logged.

(It was introduced for handling the global parameter that gives the 'Time perspective' for the Date Relative calculations that are defined in the Gregorian Calendar Data Module. )

------------------------------
Kind regards,
Henk Cazemier