Hiding a column in Cross tab

[Former Member]

Hi all,

Could any one provide me a solution to hide a cross tab column.

My cross tab has the Dimension in the rows and date values in the columns

Dec'06 Dec'07 Jan'08 Feb'08..........Dec 08

DIM1 20 10 35 28 26

DIM1 30 20 15 14 22

Some reason am bringing Dec'06 data and this data is coming from the universe.

So now I would like to hide this Dec'06 column in the cross tab how can I achieve this?

or is there any solution to hide first column in the cross tab?

Any help would be highly appreciated.

Thanks

[Former Member]

Hi all,

resolved this issue bye modifying my above formula with userresponse date(year)-2

Thanks for al for your help.

[Former Member]

Hi all,

For this I just created a variables as below first for Prior Prior year end as I always need to hide prior prior year Dec data so created a variable as below

Prior prior year end=ToDate("31/12/"+FormatNumber(Year([Date]);"0000");"dd/MM/yyyy")

after that created another variable like

If([date]=[Prior prior year end];"Y";"N")

Now created a filter saying filter cal date="N"

It is hiding all the years december data but I only want to filter only prior prior year end

any help pls

Edited by: VP S on Feb 10, 2011 5:21 PM

[Former Member]

Just try this:

=if(Year(\[Date\]) = Year(min(\[Date\]))) Then "HiddenValue" Else "VisibleValue"

or

=if(Year(\[Date\]) = Year(max(\[Date\])) - 2) Then "HiddenValue" Else "VisibleValue"

and set filter to "VisibleValue"

[Former Member]

Hi,

drag the data object to Query filter-->date object->not equal to->Value from list----> Dec 06

so, from this you can hide first column data

All the best

Praveen

[Former Member]

Hi Praveen thanks for the reply,

I cannot apply query filter as I need this Dec06 value coming into the report as there is a calculation depending on the Prior prior year end.

so somehow i need to restrict it only at reporting level.

Thanks

[Former Member]

Then you'll have to use an Alterter to set the text "white on white" when its Dec 06 data.

[Former Member]

Hello.

You can set a filter to table to restrict the columns.

[Former Member]

Hi,

Apply Filter

year not equal to dec 06

[Former Member]

Hi Narender /segey Thanks for your reply.

I did that it was working but I forgot to mention another thing which is the Dec06 may change once the 2009 data comes

that means Now I am bringing 13 months data from Dec 07 to Dec 08 and can restrict the prior prior year end data which is Dec 06 with filter as you mentioned.

but when the data for 2009 starts coming at that time Dec 07 would be my column to restrict and I need to restrict Dec 07 as it is the prior prior year end for 2009

Means the first column will change depending on the data of the year .

so please suggest how can I restrict this column based on this.

Thanks

[Former Member]

You should create a variable based on the Month dimension. It should seem like this:

=If ( \[Month] = Month(RelativeDate( CurrentDate();-DayNumberOfYear(CurrentDate())-1) )) Then "HiddenMonth" Else \[Month]

After that you can restrict table to show all values of Month dimension except "HiddenMonth".

[Former Member]

Hi Sergey,

Thanks for the formula could you please explian the formula bit more means the steps.

I am bit confused here

I have the dimension1 in the rows and date dimension which is coming in for eg. 31/10/2008 in this format

so what are the steps to proceed.

Thanks

Edited by: VP S on Feb 7, 2011 4:52 PM

[Former Member]

The point of the furmula is in grouping all values you need to hide in one group. To do that we need to create a variable based on your Date dimension. The grouping can be realized by this way:

If ( SomeConditionForGroup ) Than "NameOfGroup" Else \[Date\]

After making the group you can set filter based on it.

So, which condition is needed in your case? It must be based on CurrentDate() function to be dinamical. I think you should check if the year of value of \[Date\] equal current year - 2. That'll seem like this:

If ( Year(\[Date\]) = Year( CurrentDate() ) - 2 ) Then "HiddenValue" Else \[Date\]

P.S. While I'd been trying to explain the meaning of the formula from my previous message I invented a simplier way to resolve your issue. Hope it's help.

[Former Member]

Hi sergey,

First of all will tell you what i did

I applied a filter for the date like...created two filter variables for Min year and Max month and based on that applied a filter to the block to restrict the Min Year and Max month.

I am Sorry i couldnt get the grouping in the first half of your solution.means how to acieve that?

Would you mind explain me in detail the total formula pls

I just did the above filter but not sure this will work or not

if possible give me a complete steps of the formulas .

Thanks a lot