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
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.
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
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
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
How about _make_timestamp (?p_TCalYr?,?p_TMonth?,1)
QuoteHow 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.