Hello Friends,
I am using cognos 10.2.2 report studio and relational model. I wanted to hide crosstab column but iam not getting idea.
2015 2016 2017
Jan....Dec Jan Dec jan...Dec
Measure1 measure2 Measure1 measure2 Measure1 measure2 Forecast
I have year column(as of now 2015,2016,2017),Month column and nested measures. when the year is current year then i should have to show measure1, measure2 and forecast only. forecast shouldn't show for 2015, 2016 and if it's 2018 then show measure1,meaure2, Forecast
How do i apply variable and which column i should have to apply, iam not getting the exact expression, can anyone give some idea.
I am using relational model
Hi,
Use the expression like below to create a data item for the forecast scenario, the syntax is not 100% right. Create
if [Time] = #sq( timestampMask ( $current_timestamp , 'yyyy' ) #
) then ([Forecast]) else null
and do a suppress zero or null columns. Hope this helps.
Good luck
New guy
Hellow New_Guy
Thanks for your time and suggestion. I tried your expression with little bit changes in macros section, iam geeting 2017 as current year as per this code #timestampMask ($current_timestamp,'yyyy')# and you have mentioned As
if [Time]=#sq( timestampMask ( $current_timestamp , 'yyyy' ) #
) then ([Forecast]) else null
What is Time here? i have year columns which is having 2015,2016,2017 even though i tried with the year column instead of [Time] but iam not getting it's saying an error called.
XQE-V5-0017
V5 syntax error found for data item 'FORECAST' of query 'Final Query', invalid token "[BMS_YEAR]" found after "if ".
Appreciate if you have anymore ideas
Thanks,
Quote from: priya@90 on 01 Aug 2017 04:54:24 AM
Hellow New_Guy
Thanks for your time and suggestion. I tried your expression with little bit changes in macros section, iam geeting 2017 as current year as per this code #timestampMask ($current_timestamp,'yyyy')# and you have mentioned As
if [Time]=#sq( timestampMask ( $current_timestamp , 'yyyy' ) #
) then ([Forecast]) else null
What is Time here? i have year columns which is having 2015,2016,2017 even though i tried with the year column instead of [Time] but iam not getting it's saying an error called.
XQE-V5-0017
V5 syntax error found for data item 'FORECAST' of query 'Final Query', invalid token "[BMS_YEAR]" found after "if ".
Appreciate if you have anymore ideas
Thanks,
Hi,
You seem to be missing some parentheses here?
if [Time]=#sq( timestampMask ( $current_timestamp , 'yyyy' ) #
) then ([Forecast]) else null
should really be
if ([Time]=#sq( timestampMask ( $current_timestamp , 'yyyy' )) #
) then ([Forecast]) else (null)
MF.
Helloo MFGF,
Thank you very much for correcting the syntax and now iam able to execute without errors but whatever i expected result is not getting.
My year column is having 2015,16,2017 then how ur systax will work here i don't understand and what is the [Time] data item here? Can you please explain if you have time and i don't have any prompts
Quote from: priya@90 on 02 Aug 2017 02:56:13 AM
Helloo MFGF,
Thank you very much for correcting the syntax and now iam able to execute without errors but whatever i expected result is not getting.
My year column is having 2015,16,2017 then how ur systax will work here i don't understand and what is the [Time] data item here? Can you please explain if you have time and i don't have any prompts
Hi,
New_Guy gave you a concept, expecting you to be able to see what it's doing and extrapolate it into an expression suitable for your data.
If you look at the macro expression and try to understand what it's doing, you'll see it is starting with $current_timestamp (the current date and time), masking it using a 'yyyy' format (giving you just the year value from $current_timestamp) then using an sq() function to place that value in single quotes. If you looked at the result right now, you would see it returning '2017'
So the important question is which of your data items contains a character representation of the year? Do you have a character version of year, or is it numeric? If the former, substitute this item for [Time] in the expression. If the latter, you're going to need to remove the sq() function from the expression, then substitute this item in place of [Time]
Cheers!
MF.
Hello MF,
I have 'Year' column as Integer data type and my expression is like below. I don't get your point. What extra i need to do here. am i doing anything wrong in the below expression? please correct me.
I understand the code whatever below macro's, what's its doing and i don't get the [time], instead of [time] i have replaced my [year] column which is having 2015..2017 but it's not working whatever i expected.
if ([YEAR]=#sq (timestampMask ($current_timestamp, 'yyyy'))#)
then ([FORECAST])
else (null)
Hi,
Try the below expression if the year is an integer.
cast(#sq (timestampMask ($current_timestamp, 'yyyy'))#,integer)
if you want to hide based on current and future years you can add the function _add_years to the macro. Check the macros tab in the data item expression window for all the available functions. Let us know if you still face any issues.
Good luck
New guy
Quote from: priya@90 on 02 Aug 2017 04:12:20 AM
I have 'Year' column as Integer data type
Ok, so in that case the part where I said
Do you have a character version of year, or is it numeric? means the latter (last) option is true. Your year value is numeric.
Quote from: priya@90 on 02 Aug 2017 04:12:20 AMand my expression is like below. I don't get your point. What extra i need to do here. am i doing anything wrong in the below expression? please correct me.
What I suggested last time is that if the latter option was true (ie your year value was numeric), you should
remove the sq() function from the expression, then substitute this item (the year) in place of [Time]Quote from: priya@90 on 02 Aug 2017 04:12:20 AM
I understand the code whatever below macro's, what's its doing and i don't get the [time], instead of [time] i have replaced my [year] column which is having 2015..2017 but it's not working whatever i expected.
if ([YEAR]=#sq (timestampMask ($current_timestamp, 'yyyy'))#)
then ([FORECAST])
else (null)
But that expression still has the sq() function in it. Your YEAR item is numeric, therefore you should be comparing it with 2017 rather than with '2017'
As I suggested above, if your Year is numeric, remove the sq() function from the expression.
This should give you
if ([YEAR]=#timestampMask ($current_timestamp, 'yyyy')#)
then ([FORECAST])
else (null)
MF.
Hello MF and New_guy
Thanks for your time and help. MF, I already tried whatever you provided solution but iam not getting any result. I think my explanation was not good that's what i am not getting the correct answer from you people(experts). below is my requirement
The moment when i drag "Forecast" data item into nested column beside measure and when i ran the report, "Forecast" is showing in my 2015, 2016 and 2017 like below result
Actual Result:
2015 2016 2017
Jan....Dec Jan..... Dec jan...Dec
Measure1 measure2 Forecast Measure1 measure2 Forecast Measure1 measure2 Forecast
Expected result:
2015 2016 2017
Jan....Dec Jan..... Dec jan...Dec
Measure1 measure2 Measure1 measure2 Measure1 measure2 Forecast
but i want to hide forecast column for 2015, 2016 and should show for only current year and when next year is 2018 Forecast column should show only for that year. I hope you can understand this explanation.
Thanks,
priya
Quote from: priya@90 on 03 Aug 2017 01:43:49 AM
Hello MF and New_guy
Thanks for your time and help. MF, I already tried whatever you provided solution but iam not getting any result. I think my explanation was not good that's what i am not getting the correct answer from you people(experts). below is my requirement
The moment when i drag "Forecast" data item into nested column beside measure and when i ran the report, "Forecast" is showing in my 2015, 2016 and 2017 like below result
Actual Result:
2015 2016 2017
Jan....Dec Jan..... Dec jan...Dec
Measure1 measure2 Forecast Measure1 measure2 Forecast Measure1 measure2 Forecast
Expected result:
2015 2016 2017
Jan....Dec Jan..... Dec jan...Dec
Measure1 measure2 Measure1 measure2 Measure1 measure2 Forecast
but i want to hide forecast column for 2015, 2016 and should show for only current year and when next year is 2018 Forecast column should show only for that year. I hope you can understand this explanation.
Thanks,
priya
Hi,
The whole idea of the expression above is that you use it in a query calculation, and drag this calculation into your crosstab rather than dragging the Forecast item in. Have you tried this?
MF.
I tried but expected result i didn't get.
Forecast still showing in 2015 and 2016 and 2017.
Quote from: priya@90 on 03 Aug 2017 04:11:27 AM
I tried but expected result i didn't get.
Forecast still showing in 2015 and 2016 and 2017.
Hi,
That doesn't make any sense. The expression
if ([YEAR]=#timestampMask ($current_timestamp, 'yyyy')#)
then ([FORECAST])
else (null)
will return the [FORECAST] value if the [YEAR] item is 2017, and will return null otherwise
Try building a simple list report containing year and this calculation as list columns. Does it display the forecast for 2017 and null for the other years?
MF.
Yes exactly whatever you said it's displaying like that only but also it's displaying other years also as null. I want to hide for other years.
Quote from: priya@90 on 03 Aug 2017 06:04:07 AM
Yes exactly whatever you said it's displaying like that only but also it's displaying other years also as null. I want to hide for other years.
Hi,
In the very first reply to your question, New_Guy said this:
Quote from: New_Guy on 31 Jul 2017 11:16:40 AM
Hi,
Use the expression like below to create a data item for the forecast scenario, the syntax is not 100% right. Create
if [Time] = #sq( timestampMask ( $current_timestamp , 'yyyy' ) #
) then ([Forecast]) else null
and do a suppress zero or null columns. Hope this helps.
Good luck
New guy
I've drawn your attention to the important part by making it bold :)
Use the "suppress" button on the toolbar to suppress rows and columns with nulls in them...
MF.
:P
oh Initially i have applied then after i re-developed and then forgot to apply that(suppression) condition.
Thank you very much for your time and help. Appreciate all of your efforts(MF and New_guy). Sorry to trouble you people.