Crosstab spacing issue

[dev1212]

Hi,

I need to sort cross tab based on the parameter value passed.

The parameter is "{?STRORDER}"

I used the below formulae for doing "Group Sort" - "For All" in cross tab.

For Account number ascending,
{@Acct_Asc}
==============
If
Instr({?STRORDER},'ACCT.ACCOUNT_NUMBER ASC')>0
Then
{Rpt.ACCOUNT_NUMBER}


For Account number Descending,
{@Acct_Desc}
===========
If
Instr({?STRORDER},'ACCT.ACCOUNT_NUMBER DESC')>0
Then
{Rpt.ACCOUNT_NUMBER}


This sorting is working fine.

This summaries are appearing in cross tab because i used formulaes {@Acct_Asc} and {@Acct_Desc}
in summary of cross tab to do the conditional sorting. So i suppressed it and When i suppressed these fields,
the existing summaries are getting stretched after export/new pages are created.

When i exported to PDF, 4 new blank pages are created, when exported to excel, the summaries are getting stretched.

Any solution to avoid this..?

Thanks

 

[lbass]

Is Account Number the row field in the crosstab? If so, in the crosstab expert, you should select the row field->group options->group sort->x+2 and enter:

if {?Sort} = "Ascending" then
crAscending else
crDescending

Or is the Account number used some sort of summary? If so, please identify row, column and type of summary and what the summary fields are.

-LB

 

[dev1212]

I have 2 row fields, ACCT.ACCOUNT_NUMBER and ORG.ORG_VAL.
I dont have any columns.

The summaries i am displaying are,
Count of Accounts - ACCT.ACCOUNT_NUMBER,
Count of Transactions - ACCT.TXN_VAL,
Sum of Amount - ACCT.ACCT_AM

I am sorting this cross tab based on the value provided in the parameter "{?STRORDER}".

If the value in the parameter is "TXN_DATE ASC" then i am sorting cross tab based on transaction date in ascending order.
If the value in the parameter is "ACCOUNT_NUMBER ASC" then am sorting cross tab based on account number in ascending order.

There are 6 sorting values,
TXN_DATE
ACCOUNT_NUMBER
DEPT_NO
EMP_ID
JOIN_DATE
LAST_UPD_DATE

So, i had to include the conditional formulaes in summary fields of crosstab.

i created formulaes like,
For Transaction date ASC
========================
{@TXNDate_Asc}

If
Instr({?STRORDER},TXN_DATE ASC')>0
Then
{Rpt.TXN_DATE}


For Account number Ascending
=============================
{@Acct_Asc}

If
Instr({?STRORDER},'ACCT.ACCOUNT_NUMBER ASC')>0
Then
{Rpt.ACCOUNT_NUMBER}

Then i inserted this formulaes as summary fields in cross tab. The summaries of this formulaes are getting displayed on the cross tab. But i dont need to display the summary of these formulaes. So i suppressed these summaries as i dont need it.

The sorting is working fine without any problem.

I have no issues with sorting.

The problem is with the blank spaces created in cross tab because of suppression. I want to avoid these blank spaces.

When i export the report in PDF, extra blank pages are created because cross tab is spread over multiple pages with blank spaces (suppressed summaries).
When exported to Excel, the required below summaries cell width is getting expanded.
Count of Accounts - ACCT.ACCOUNT_NUMBER,
Count of Transactions - ACCT.TXN_VAL,
Sum of Amount - ACCT.ACCT_AM

Hope this clarifies the doubt.

 

[lbass]

This makes no sense to me. Why would you sort by a field not even used in the crosstab (date). I also can't tell whether your intent is just to sort the row field (account number) or the count of the account number.

I understand you have the sorting the way you like it, but it is your approach to this that is causing the problem, I think.

-LB

 

[dev1212]

LB,

Its totally understandable.
This doesn't make any sense to me too. But this is the requirement of the client. They want to see the details sorted in ascending/descending order of the transaction date, etc. without them being displayed on the report.

The issue i have is with the spacing and blank spaces. Everything on the crosstab is working fine. I want to remove the blank spaces generated after suppressing the fields on crosstab.

Just remove the blank spaces created after suppressing the summaries.
When you suppress the summaries on cross tab, its suppressing it but also creating blank spaces which i want to avoid.

Thanks

 

[lbass]

Do you see the blank spaces in Crystal? Or just after export?

-LB

 

[dev1212]

I see it in crystal and as well as after export (obviously)

 

[lbass]

I don't know how to address the export issues. I don't think crosstabs export that well to Excel. They should look fine in pdf though. After suppressing the summaries, you should go into design mode and resize the suppressed summary fields to minimize their width or height (not sure how the summaries are oriented). Since they aren't really empty, you can't expect them to suppress using the customize style tab suppression options.

-LB

 

[dev1212]

I got a solution to this, not very efficient but works though. In cross tab expert if we uncheck 'show cell margins' then the size of the cross tab will get reduced and then we can arrange the size of the cross tab summaries. The way i solved the header issue is i used another section where i have column headings and the section following it will have the cross tab. It worked for me.

Thanks

 

[lbass]

You can resize summaries so they are virtually undetectable without unchecking the show cell margins--but glad you got it to work.

-LB