Crystal Reports XI - Alternative required to cross-tab in subreport 2

[srkeevil]

I have developed a suite of KPI reports for a customer, based on data supplied from a Stored Procedure in MS SQL Server 2005.

Although based on a common dataset, and sharing the same parameters, each report is quite distinct and features different record selection criteria. I initially developed each report as a standalone RPT file with a view to combining them all as subreports in a Main Report and linking the parameters.

Everything was great until i hit the virtual pages limitation of Cross-Tabs in Subreports.
Having trawled google and various forums it seems clear that I can't make a cross-tab that spans multiple pages display fully when held in a subreport.

My next option was to try and move the cross-tab into the main report, but my problem is then one of filtering & record selection:

My dataset is basically a list of Sales Transactions including detail of Sales Outlet (Branch), Product criteria, such as Manufacturer and Product Group, and also Customer criteria, such as Account Group and Region.

I need to implement two cross-tabs in my report:

The first is the sum of Units, Turnover, and Profit by Product Manufacturer (Columns), analysed by Branch (Rows).
This needs to be limited to 10 specific manufacturers (out of approx 100 present in data).

The second cross-tab is the sum of Units, turnover, and profit, by Customer Account Group (Columns), analysed by Branch (Rows). This data needs to be unrestricted and include all records in the dataset.

I really can't see how I can achieve the different record selection criteria required in the two cross-tabs when both held in the same report.

I'm getting a bit desperate, any help seriously appreciated!! I hope I've explained this problem clearly enough. Any other info required, just ask!

Thanks in advance... Shaun

 

[lbass]

Try adding only the widest crosstab to the main report. Then stretch the subreport containing the other crosstab to the width of the main report crosstab. You should use parameters that will maximize the number of columns when you set this up, since the subreport will not be able to grow beyond the original stretched dimensions.

-LB

 

[srkeevil]

Hi LB,

Thanks for your reply. This seems like my best choice.
But I'm struggling a little...

My page setup is A4 landscape. Both cross-tabs fit vertically into one page, respectively.

I have placed the widest sub-report directly into Report Header on main report, as you suggested, and this spans pages as required.

I then added a second report header section below and placed my sub-report containing the 2nd cross-tab into this. I have stretched the sub-report way beyond the A4 page limit, but it is not spanning horizontal pages.

As a little test, I placed the sub-report into the same header section as the main cross-tab and sure enough, the page spanning worked, but now the cross-tabs are on top of each other.
I tried moving the sub-report down within the same report header section, but as soon as it stopped being on top of the main cross-tab, the spanning stopped working.

What am I missing? It feels like I'm now really close to a solution.

Thanks again for your help...!

 

[lbass]

I just tested this again, with the main report crosstab in RH_a and the sub in RH_b, and the crosstab in the RH of the sub. Run the report and then grab the border of the sub and in one try stretch it to the right. I found that if I used a sort of swirling motion while pulling to the right at the page margin, it worked better. I tested this with the xtreme database and got 46 virtual pages.

-LB

 

[srkeevil]

Hi again LB,

Thanks for your perseverance here!

I've now started a completely blank report, using my dataset.
As you have described, I have placed the main cross-tab into RHa and the sub into RHb. I've ensured that, in the sub, the crosstab is in RHa.

I've attempted the stretch as you have described, but I cannot make it work unless both crosstabs feature on the same page (in which case they are overlayed).

My crosstabs both occupy the full depth of A4 in landscape.
I can only get the behaviour you are describing when i place both crosstabs in the same RH section.

I'm not familiar with the Extreme data. I will download it. Would you be prepared to email me your example RPT file? At least I can witness this working with my own eyes then!!

(I've also tried this in both XI and 2008)

 

[lbass]

Okay, I see the issue--the main report crosstab has to be on the same page. The following works. Insert another report header section_b, so that Crosstab 1 is in RHa and Crosstab 2 is in RHc. Then copy RHa into RHb, except go into the expert and remove the row field. Then suppress all labels and cells, and then go to the customize style tab->format grid lines->uncheck "show grid lines". The crosstab is now invisible. Then in go into the section expert->RHb->check "Underlay following sections.

-LB

 

[srkeevil]

Absolutely stunning!!!!!!!!!!!!!!
Thank you very much indeed!!!!!!!

You, Sir, are worth your weight in gold!

 

[ianoctdec]

A work around solving the different selection criteria for the two crosstab, so that they can be both in the same report:
if you know exactly which manufacturers are populating the first crosstab, then use the @Null formula to restrict the columns.
Open the formulas editor for a new formula, name it 'Null' (or however you desire), save it empty. Then create a formula as follows:

@ValidManufacturer
if ProductManufacturer in [<desired manufacturers>] then
table.ProductManufacturer
else
@Null

Use this formula as columns in the crosstab.

For the summary fields, use formulas that will make them zero if ValidManufacturer is not in the desired selection:

@ValidAccount
if ProductManufacturer in [<desired manufacturers>] then
table.Account
else
0
Check Suppress If Zero in the crosstab, then 'Suppress Empty Columns'.

This worked with me for rows, I think it should work for columns too.

Dana

 

[srkeevil]

Dana,

Thanks very much for your response!

This has actually been an extremely useful lesson for me. Using your technique, I've now managed to remove all sub-reports and have everything in the main report as cross-tabs.

This has had a MASSIVE effect on the time taken to run the report since the query is now running only once rather than 6 times!

The only downside is i've had to completely rip the report apart and start again! But still, an excellent lesson to learn.

Thank you...
Shaun

 

[ianoctdec]

I am so glad you found it useful! I have learned the @Null formula trick from this forum (Ken Hamady and lbass), along with many others.

Good luck,

Dana