Dynamic Hiding of columns for a report

[Desperado]

Hi Gurus,

I created a crosstab from a list report, as this is a dynamic report(which updates the columns dynamically) i used two stored procedures one to return the names of the business units and other to return the data.

proc1 returns the BU namesÃ,  like USA, california,...
proc2 returns the data for the corresponding names
the output for proc1 looks as follows..

bu1Ã,  Ã,  Ã,  bu2
USAÃ,  Ã,  California


The o/p for proc2 looks as follows

bu1Ã,  Ã,  Ã,  Ã,  Ã, bu2
200Ã,  Ã,  Ã,  Ã,  Ã, 300

for creating the report I took a block in which i used a table and a list report
the table returns all the BU namesÃ,  and the list returns the data

the o/p looks as follows:

bu1Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, bu2Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  bu3
USAÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, californiaÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, XXXXXXX

bu1Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  bu2Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  bu3
200Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  300Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  0

Then i use the hide option to hide bu1 and bu2

then the o/p is as follows

USAÃ,  Ã,  Ã,  Ã,  Ã, CaliforniaÃ,  Ã,  Ã,  Ã,  Ã,  xxxxxx
200Ã,  Ã,  Ã,  Ã,  Ã,  300Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  0

NowÃ,  I want to hideÃ,  those lables with xxxx(with out the column name ) and their corresponding data if it is 0. I'm trying to create a variable to hide the lable xxxxxxxx . But it's not hiding (as this shd be dynamic i'm trying it with variables...) . is there anyway to hide this... and the value ..

what i want is ..initially i took 20 bu's so, that if any new bu is added to the DB it will popup dynamically...Ã,  and i just want to hide all those bu's and their corresponding values that don't have any values from the DB (Dynamically). is there any workaround ?

Plz help ....!!!!!!

[Darek]

Conditional variable based formatting does not work on crosstabs.

[Desperado]

Thats why i'm using a list and a table ... I'm not using a cross tab here . Is there any way to hide BU's ?

[BIsrik]

is BU an o/p value of ur procedure or the column name.. I am sorry i couldn't get ur question.

Srik

[Desperado]

BU is the output value of my procedure. I'm replacing the BU1, BU2 (Column names ) dynamically with the names from the database depending on the parent id given..

[Desperado]

Got it done. took one more column for BU. and set the aggregation as total. then created a variable

CASE  when [BU1Count] =0 then 'hide'. using this i can hide the column... But my doubt is if an existing column has all the values as 0 then my query fails. it will hide the column from the database .. how to solve this ..? 

[bdybldr]

Is [BUCount] an actual count of rows or is it actually a total of some other field?  If it's a count of rows, there shouldn't be problem.  If the count is zero, then it's not possible to have any values, much less zero values.

Am I understanding correctly?

[rlcaputo]

Where are you placing this statement?

CASE  when [BU1Count] =0 then 'hide'.

Thanks,

[bdybldr]

rl,
You first create a Boolean variable '[BUcount] = 0'.

Then, using conditional formatting, set the box type to none when the variable evaluates to 'TRUE' or Yes.  HTH

[bdybldr]

Reminder:

Please close this thread (see board rules).