Author Topic: "Hiding" One Column in a Crosstab  (Read 457 times)

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
"Hiding" One Column in a Crosstab
« on: 27 Jul 2017 11:19:37 pm »
We have a Crosstab with 3 columns (a Query Item, and 2 calculated Data Items).

These 3 Columns will appear under [Fiscal Year] always for only two Fiscal Years.

The problem ... As an example, we will show FY17 and FY16.  Mgmt wants to see all 3 Columns under FY17, but ONLY 2 of those 3 Columns under FY16.  Is there a way to somehow show a Column calculation for ONLY FY17 but NOT for FY16?

We may be able to calculate 5 of those 6 Columns and present them in a List coding the Data Item labels.

Thoughts please.  TIA, Bob

Offline New_Guy

  • Statesman
  • ******
  • Join Date: Mar 2016
  • Posts: 308
  • Forum Citizenship: +15/-0
Re: "Hiding" One Column in a Crosstab
« Reply #1 on: 03 Aug 2017 06:47:11 am »
Hi,
Please refer to the below post.
http://www.cognoise.com/index.php/topic,33386.0.html
Good luck
New guy

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: "Hiding" One Column in a Crosstab
« Reply #2 on: 03 Aug 2017 01:38:57 pm »
Thank you New guy.  LOT there for me to learn which I will do.

I wound up writing a List displaying 8 Columns.

I used techniques I learned from Cognoise guys the last few months including ...

1 - SelMMYY = _make_timestamp (extract(year,?p_EndDate?),extract(month,?p_EndDate?),1)

2 - TimeL = _make_timestamp ([Calendar Year],[Calendar Year Month #],1)

3 - LTMonths =_months_between ([SelMMYY],[TimeL])

4 - LatestHours (for Current Fiscal Year)
Case
When (extract(month,?p_EndDate?)=10 and [Q_TPRR].[LTMonths] = 0)  then [Q_TPRR].[Employee and Supervised Contractor Hours]
...
When (extract(month,?p_EndDate?)=9 and [Q_TPRR].[LTMonths] between 0 and 11) then [Q_TPRR].[Employee and Supervised Contractor Hours]
End

Works great as a List.

Thanks to ALL the Cognoise guys who taught me how to do this.

 


       
Twittear