Eclipse Community Forums: BIRT » How to hide crosstab columns at runtime (in Excel)

Home » Archived » BIRT » How to hide crosstab columns at runtime (in Excel)

How to hide crosstab columns at runtime (in Excel) [message #896524]

Wed, 18 July 2012 12:45

Eclipse User

Hello,

I am trying to hide columns (levels) that don't have data in a crosstab. But the only way I can find to actually remove the column when exporting to Excel is to change the column width to 0.

However, I also have header labels in a grid (tried table as well) above the crosstab levels that will also need to set the column with to 0 for the same column below it.

I can get the columns to hide in script, but since setting the column width appears to be required in the onPrepare event for the column (anything later than this won't change the column width), I don't know how to see if there is data in the dataset before this happens.

I tried a hidden table above this table to calculate the existence of data, but I can't seem to get it to work.

Any help is greatly appreciated.

Re: How to hide crosstab columns at runtime (in Excel) [message #896576 is a reply to message #896524]

Wed, 18 July 2012 20:15

Eclipse User

Matt

Do you mean something like the attached example. I did not clean up the
padding but it should give you a start.
BTW have a look at this post:
http://birtworld.blogspot.com/2010/02/birt-crosstab-scripting.html

Jason

On 7/18/2012 12:45 PM, Matt Rhoads wrote:
> Hello,
>
> I am trying to hide columns (levels) that don't have data in a
> crosstab. But the only way I can find to actually remove the column
> when exporting to Excel is to change the column width to 0.
>
> However, I also have header labels in a grid (tried table as well) above
> the crosstab levels that will also need to set the column with to 0 for
> the same column below it.
>
> I can get the columns to hide in script, but since setting the column
> width appears to be required in the onPrepare event for the column
> (anything later than this won't change the column width), I don't know
> how to see if there is data in the dataset before this happens.
>
> I tried a hidden table above this table to calculate the existence of
> data, but I can't seem to get it to work.
>
> Any help is greatly appreciated.

Attachment: EmptyRowsSDS.zip
(Size: 2.46KB, Downloaded 362 times)

Re: How to hide crosstab columns at runtime (in Excel) [message #896578 is a reply to message #896524]

Wed, 18 July 2012 20:15

Eclipse User

Attachment: EmptyRowsSDS.zip
(Size: 2.46KB, Downloaded 330 times)

Re: How to hide crosstab columns at runtime (in Excel) [message #896589 is a reply to message #896576]

Wed, 18 July 2012 23:40

Eclipse User

I actually want the Level/Column to go away instead of the row.

So, in your example, if the second column ([dim]) had no data in any records, I would want the entire column to be removed when exporting to Excel.

But I also have column headers in a grid above the crosstab columns. I would need the corresponding column/label from the grid to be removed as well.

If you want an example, I may be able to throw one together.

Thanks!

Matt

Re: How to hide crosstab columns at runtime (in Excel) [message #896797 is a reply to message #896589]

Thu, 19 July 2012 16:12

Eclipse User

Can you put together a sample from the classic models sample db?
It should be just as easy to remove a column.

Jason

On 7/18/2012 11:40 PM, Matt Rhoads wrote:
> I actually want the Level/Column to go away instead of the row.
>
> So, in your example, if the second column ([dim]) had no data in any
> records, I would want the entire column to be removed when exporting to
> Excel.
>
> But I also have column headers in a grid above the crosstab columns. I
> would need the corresponding column/label from the grid to be removed as
> well.
>
> If you want an example, I may be able to throw one together.
>
> Thanks!
>
> Matt

Re: How to hide crosstab columns at runtime (in Excel) [message #897339 is a reply to message #896797]

Mon, 23 July 2012 12:15

Eclipse User

I wasn't able to set it up exactly how I need it, but I think the attached report will help show my problem.

I added a grid with labels above the Crosstab columns. The grid will actually be a table and the dataset will populate the 'headings'.

Ideally, if the [dim] column had no data in any rows, I would want to hide the entire [dim] column and the column in the header table called 'DimLabel' so that neither would show in Excel.

Please let me know you need more details from me.

Thanks!

Matt

Attachment: Test.rptdesign
(Size: 16.59KB, Downloaded 460 times)

Re: How to hide crosstab columns at runtime (in Excel) [message #897405 is a reply to message #897339]

Mon, 23 July 2012 22:09

Eclipse User

I am not certain there is an easy way to do this. Hiding the crosstab
column is easy enough but without using a hidden table before the
crosstab I do not see a way of doing this. What do you envision the
report output to look like. Maybe another solution is available.

Jason

On 7/23/2012 12:16 PM, Matt Rhoads wrote:
> I wasn't able to set it up exactly how I need it, but I think the attached report will help show my problem.
>
> I added a grid with labels above the Crosstab columns. The grid will actually be a table and the dataset will populate the 'headings'.
>
> Ideally, if the [dim] column had no data in any rows, I would want to hide the entire [dim] column and the column in the header table called 'DimLabel' so that neither would show in Excel.
>
> Please let me know you need more details from me.
>
> Thanks!
>
> Matt
>

Re: How to hide crosstab columns at runtime (in Excel) [message #897583 is a reply to message #897405]

Tue, 24 July 2012 11:19

Eclipse User

Having a hidden table above is okay (unless it affects the output to Excel). Not worried about performance.

But when I tried using a hidden table, I was still unable to make it work.

The desired output will be a simple Excel report (with headers), but it needs to hide any column that doesn't have data (only columns on the left side of the crosstab...not the dynamic columns).

I can achieve the desired output by setting the column with to 0 in for both the header table column and the crosstab cell, but I can't get the information (whether to hide it or not) before the OnCreate method of the header table. Does the OnCreate method fire for all controls before the OnFetch fires for any datasets? That seems to be what I am running in to.

If this post only added more confusion, please let me know and I will try to post a more accurate report of what I am seeing...

Thanks again!

Matt

Re: How to hide crosstab columns at runtime (in Excel) [message #897634 is a reply to message #897583]

Tue, 24 July 2012 16:16

Eclipse User

Matt,

If you do not mind using a hidden element you could paste the same xtab
in the report twice. Use the code I showed you earlier to hide the
row/column in the crosstab in both. In the first xtab hide it using
visibility rules and in the script that hides the row/col set a
persistentGlobal variable using
reportContext.setPersistentGlobalVariable("hidetablecol",
"columnnametohide");

Then in the second crosstab in your table put a visibility expression on
the column that checks the value of the persisted global variable.

Jason

On 7/24/2012 11:19 AM, Matt Rhoads wrote:
> Having a hidden table above is okay (unless it affects the output to
> Excel). Not worried about performance.
>
> But when I tried using a hidden table, I was still unable to make it work.
>
> The desired output will be a simple Excel report (with headers), but it
> needs to hide any column that doesn't have data (only columns on the
> left side of the crosstab...not the dynamic columns).
>
> I can achieve the desired output by setting the column with to 0 in for
> both the header table column and the crosstab cell, but I can't get the
> information (whether to hide it or not) before the OnCreate method of
> the header table. Does the OnCreate method fire for all controls before
> the OnFetch fires for any datasets? That seems to be what I am running
> in to.
>
> If this post only added more confusion, please let me know and I will
> try to post a more accurate report of what I am seeing...
>
> Thanks again!
>
> Matt

Previous Topic:	Accessing any dataset from a scripted datasource
Next Topic:	Dynamic text with page number

Goto Forum:

-=] Back to Top [=-

Current Time: Tue Jul 01 00:37:38 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter