hide columns in webi on condition

[Former Member]

Hi friends,

I have a webi report where in the data is coming from BEX query. The functionality is like user enters a date in date prompt in webi then the report should show next 12 months data from the key date. But the thing is if user enters say Sep 2011 then it should not show Jan 2012-Aug 2012 data as it falls in next year,it should only show Sep 2011 to Dec 2011 . But if user enters Jan 2011 then it should show Jan2011 - Dec 2011 Data. Hence I had to create 12 key figures in BEX queries. But I need to hide colums if they fall in next year.

Please guide.

Thanks,

Gaurav

BO XI 3.1

[Former Member]

Hi,

I have implemented similar kind of logic in my webi report. But I have used Universe objects(DB:Teradata). I have created object and put restriction on year by extracting Year from date pompt and validating this year with Year(current date). So instead of hiding columns after generating the report, you can try by putting Year condtion on your objects.

If you really need to hide the column at report level, you can extract year from the Year prompt by using UserResponse() and substr() functions. First get the entered prompt date by using UserResponse() functiuon and then apply substr() on top of it, to get the Year part of the prompt and validate this with Year(currentdate).

Regards

Ram

[Former Member]

Hi Ram,

Thanks for the reply.

The thing is I had no other option but to create 12 different key figures for 12 months. Now all these 12 Key figures are getting used in BO. Depending upon condition I have to show/hide those key figures. The substr validation will not hide the column but only empty the column.

Thanks,

Gaurav

[amitrathi239]

Hi,

Extract the month name by user response function.

Set height and width to 4px and checked the check box for auto height and width.

create alerter based on the userreponse function.Like if user select Feb then show ="" for jan and remove the border based on the condition in alerter.

Just try with this logic may be this will resolve your issue.

Thanks,

Amit

[Former Member]

Hi Amit,

Thanks for the reply.

I have a total column at the end. So even if I apply your logic then it will create a gap between actual key figures and Total column.

Please guide.

Thanks,

Gaurav

[amitrathi239]

Hi,

Yes there will be some more width compare to total column but you can remove the border based on the condition.

Thanks,

Amit

[Former Member]

Hi Amit,

But since I can have maximum of 11 columns to hide i,e if user enters Dec 2011 then I need to hide 11 Key figures which will result in gap of 11*4 = 44 Pixels. This is big in my opinion. Dont we have any alternative to hide the columns?

Thanks,

Gaurav

[amitrathi239]

Hi,

Other then this create 12 tables..one for each month.Like for jan add all columns.For Feb add only 11 columns.

Capture the month value by user response variable.Select the table and apply table level filter.like month=Jan on Jan table.

Set the height and width to 4 px for all columns and checked the check box for auto.

tables are top on others.

Create 12 alerter for each tables.Like for Jan ( User response objects not equal to All other month) then show blank and no border. Jan alerter apply on all columns of Jan Table.

Same steps for others.

I have tried for two dummy tables and it is working.

You can try with this solution or earlier one.

Thanks,

Amit

[Former Member]

Hi,

For the logic I explained above, need to create two report level measures.

1. To extract year from prompt object.M1

substr(userresonse("Prompt Text")) --

For ex: your propmt text date format is 10-15-2011.

Then substr(userresponse("Prompt Text");7;4)

2. Create one object to pull the year from current date.M2

Year(currentdate())

Now pull first measure to report and make column width 4pt and remove all boarders. also format text to match with background color.

Now on this column create one report filter M1=M2

Hope this will help.

Regards

Ram