Hide crosstab column

[priya@90]

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

[New_Guy]

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

[priya@90]

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,

[MFGF]

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.

[priya@90]

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

[MFGF]

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.

[priya@90]

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)

[New_Guy]

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

[MFGF]

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.

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.

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]

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.

[priya@90]

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

[MFGF]

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.

[priya@90]

I tried but expected result i didn't get.

Forecast still showing in 2015 and 2016 and 2017.

[MFGF]

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.

[priya@90]

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.

[MFGF]

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:

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.

[priya@90]

 :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.