Value Prompt

[Cognos_Jan2017]

Has been working since January 2018, and today has errored as ..
The prompt 'p_BobTMonth' is used multiple times with conflicting data types: tinyint, smallint.

Have to study how tinyint, smallint can conflict.  p_BobTMonth only runs from 1 to 12,
and is formatted as Number, zero decimal places.

Anyone know how to "non-conflict" data types tinyint, smallint?

P_BobTMonth is setup in the Value Prompt as ...
1   .... January
2   .... February
3   .... March
4   .... April
5   .... May
6   .... June
7   .... July
8   .... August
9   .... September
10 .... October
11 .... November
12 .... December

It has Data Items for Column Labels such as ...
Case
When ?p_BobTMonth? = 12 Then 'R12 Nov '+Right(?p_TCalYr?-1,2)+ ' - Oct '+Right(?p_TCalYr?,2)
When ?p_BobTMonth? = 11 Then 'R12 Oct '+Right(?p_TCalYr?-1,2)+ ' - Sep '+Right(?p_TCalYr?,2)
....

TIA, Bob

[CognosPaul]

Try using prompt macros instead of aliases. If you're using DQM you could also use a macro simple case.
#case prompt('p_BobTMonth','int')
When 1 then sq('whatever')
End#

It looks like you're trying to format the date in the query. In general I would recommend you construct it as a date and use data formatting.

[Cognos_Jan2017]

Thank you Cognos Paul.

We utilize a 3rd Party App.  This particular Package IS DQM.

I have utilized limited use of prompt macros, and have read
some powerful they are.

In the interim, I changed the Value Prompt "use" to the 12 Months
(January, February, ... , December, and adjusted code to get the
Report to work.

The Query utilized timestamps, and subtracts from 2 of those
timestamps to calculate "Number of Months" between.  We then
have code like ,,,
Case
When [Query1].[LTMonths] between 2 and 13 Then [Query1].[L_In_Pounds]
End

Has been working well.

I had written a recent Topic here about formatting _make_timestamp,
and now understand better Date formatting.

I will utilize your recommendations.

Thank you, Bob

[Cognos_Jan2017]

Not out of the Woods yet.

Running the Report now errors as ...
Need to resolve prompts.
XQE-PLN-0097 Invalid format for numeric prompt 'p_TMonth'

The Value Prompt is ...
Use         Display
January   January
February  February
..........................
December December

Its Data format is "text".

Is there a way to (since all the Case Whens
are now looking for Month Names ... and not
utilize a Preompt Macro) to ...
Running the Report now errors as ...
Need to resolve prompts.
XQE-PLN-0097 Invalid format for numeric prompt 'p_TMonth'

TIA, Bob

[Cognos_Jan2017]

S O L U T I O N ....

With all the code in this Report, which has worked WELL a long time,
we kept the existing Value Prompt where ...
Use  Display
1     January
2     February
...
12   December

The key was assigning Month Number to the
_make_timestamps in code.  Example ...
NOT SURE the most efficient way, but it works ..
Case
When ?p_TMonth? = 1 then
_make_timestamp (?p_TCalYr?,1,1)

When ?p_TMonth? = 2 then
_make_timestamp (?p_TCalYr?,2,1)

When ?p_TMonth? = 3 then
_make_timestamp (?p_TCalYr?,3,1)

When ?p_TMonth? = 4 then
_make_timestamp (?p_TCalYr?,4,1)

When ?p_TMonth? = 5 then
_make_timestamp (?p_TCalYr?,5,1)

When ?p_TMonth? = 6 then
_make_timestamp (?p_TCalYr?,6,1)

When ?p_TMonth? = 7 then
_make_timestamp (?p_TCalYr?,7,1)

When ?p_TMonth? = 8 then
_make_timestamp (?p_TCalYr?,8,1)

When ?p_TMonth? = 9 then
_make_timestamp (?p_TCalYr?,9,1)

When ?p_TMonth? = 10 then
_make_timestamp (?p_TCalYr?,10,1)

When ?p_TMonth? = 11 then
_make_timestamp (?p_TCalYr?,11,1)

When ?p_TMonth? = 12 then
_make_timestamp (?p_TCalYr?,12,1)
End

[CognosPaul]

How about _make_timestamp (?p_TCalYr?,?p_TMonth?,1)

[Cognos_Jan2017]

How about _make_timestamp (?p_TCalYr?,?p_TMonth?,1)

Thank you CognosPaul.

Strangely, that is where it started failing, after working many months.

I guesstimated that is where the tinyint began appearing from the previous design.

We have other Reports w/ similar _make_timestamps (that have worked well over
a year) ... will they start failing?  If they do, we know what will correct them.