cancel
Showing results for 
Search instead for 
Did you mean: 

Top N on group header

Uli_Stoeckl
Participant
0 Kudos

Hi All,

I´m hoping anyone could help me with following request for a report. Our system is Crystal reports for Enterprise 4.1 SP4.

Here I have to join two Top N conditions for one Group Header.

The report looks like this:

Amount

Differenz to plan

GH1

AAA

1000

-1500

GH1

BBB

2500

0

GH1

CCC

6800

+65

GH1

DDD

100

-900

GH1

ZZZ

150

-10000

The aim is to show only top 10 group headers (GH1) for the kf “Differenz to plan”. 

Additionally however only this GH1 have to be included if they are also within top 50 for “Amount”.

For one Top N contition this would not be a problem. I can set a Top N condition in the menu for groups and sort (group expert)

Does anyone got an idea how to realize this for two Top N conditions? All based on one group header?

Thanks in advance

Uli

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Ulrich,

You can't really do two Top N functions on the same group.

You would first need to find out which groups belong to top 50 as Sastry suggested. After which you would need to actually 'sort' the group based on the 'Differenz to plan' so that it shows the correct Top N order - which unfortunately can't be done due to the Top 50 which has already been applied.

Here's a workaround though:

1) Save the existing report as a new report

2) Go to Insert > Subreport > Select 'use existing report' > Browse to the report you saved in Step 1 > Click OK. Place this Subreport on the Report Header section

3) Get inside this Subreport and make sure this is sorted to TOP 50 based on the 'Amount' Summary field

4) Create a formula with this code and place on the GH1:

WhilePrintingRecords;

shared stringvar array str;

redim preserve str[groupnumber];

str[groupnumber] := {Group_Field};

Replace {Group_Field} with the Database Field you've grouped the report on

5) 'Hide' all sections of this Subreport

6) Go back to the Main Report and go to the Data tab > Sorts > Sorts tab > Choose Group #1 > Under 'Show' choose 'All Groups' > Under 'Sort by' choose 'Sum of Order Amount' > Click the 'A -> Z' icon beside so it sorts in Descending order

7) Right-click anywhere on the Group Header 1 section > Format Section > General tab > Click the formula button beside 'Hide' and use this code:

WhilePrintingRecords;

shared stringvar array str;

Not({Group_Field} IN str)

Hope this helps.

-Abhilash

Uli_Stoeckl
Participant
0 Kudos

Hi Abhilash,


sorry for my very late answer. Your solution is fantastic. The formula works well and I am able now to reduce the data on the first 50 group headers. This is a great help for me!


But I have a second problem. The overall aim of this report is to show the top 10 based on “difference to plan”.


To calculate difference per group header I have had to build a formula with hidden columns (Amount actual/ amount plan * 100)


The problem is that I can´t use this formula for a top-N condition in the group expert. It is not offered there. Do you think there is a possibility to solve this problem on an alternative way?

Thanks in advance

Uli

abhilash_kumar
Active Contributor
0 Kudos

Hmm, if the formula that calculates 'Differenz to plan' is a print-time formula then you won't be to able to use that to sort or top-n.

What are those hidden columns?

-Abhilash

Uli_Stoeckl
Participant
0 Kudos

Hi Abhilash,


the mentioned hidden columns are necessary for some calculations. They are not displayed in the report. In my example I need the column "Amount (plan)" merely to calculate the difference in percentage. It´s not possible to sum up the difference results from details (i.e. D1_A + D1_B).


Amount (plan)

Amount (up to date)

Difference to plan

GH1

AAA

1320

1000

-24,2%

D1_A

350

500

42,9%

D1_B

200

50

-75,0%

D1_C

350

375

7,1%

D1_D

420

75

-82,1%

GH1

BBB

2500

0,0%

GH1

CCC

6800

65,0%

GH1

DDD

100

-19,0%

GH1

ZZZ

150

-100,0%

Therefore I do have to calculate this difference (in %) for every Group Header on its own. Till this point everything looks fine and works as requested.


My Problem is an additional Top-N condition on this formula “Difference to plan in %”. I was not able to find a way to use this formula in the group expert ( Top 10) or in a function “NthLargest” within the formula workshop. Everything based on first top 50 condition solved by you.


Thanks

Uli

abhilash_kumar
Active Contributor
0 Kudos

I don't think I entirely understand this statement: "It´s not possible to sum up the difference results from details (i.e. D1_A + D1_B)"

What is the logic you've used to calculate the difference in percentages?

-Abhilash

Uli_Stoeckl
Participant
0 Kudos

Hi Abhilash,

the rows “D1_A” to “D1_D” are the details for Group header 1. The difference to plan is calculated as a percentage deviation; i.e. D1_B (50-200)/200*100 = -75%

Summing up all detailed percentage deviation (D1_A – D1_D) i.e. GH1: (+42.9%  -75  +7,1 -82.1 = -107.1). This result is wrong for the GH1. The right result is (1000 – 1320)/1320*100 = -24.2%. cf. crosstab from last reply.

That is the reason why I have to calculate this difference (in %) for every Group Header on its own within the formula workshop. This formula works fine in the report. At the end the requested report is intended to show only the Top 10 Group Headers based on difference (in %). Right at this point all of my previous trials failed.

Thank for all

Uli

abhilash_kumar
Active Contributor
0 Kudos

Ahh, I see what you're saying.

What database are you reporting against and what is datasource for the report (Stored Proc, tables, views, command objects)?

You can create a SQL Expression and calculate the Percent Difference for each group on the database side

-Abhilash

Uli_Stoeckl
Participant
0 Kudos

The datasource is a BEx Query. Therefore I can´t use a BEx condition because they are not supported by CRfE.

All data delivered from the query are at the report details. All sums, results, other calculations and groups (based on parts of the CO-Orderkey, from SAP BW) are generated in CRfE.

Additionally there is a first Top 50 condition on amount active. I could do this Top 50 condition only with your great help.

Thanks

Uli

Uli_Stoeckl
Participant
0 Kudos

Hi Abhilash,

I have also tried to solve this problem with crosstabs. But all in all without success.

Do you see any possibility to get this from our customer requested results?

Thanks

Uli

abhilash_kumar
Active Contributor
0 Kudos

With no help from the datasource available, the only (expensive) way to do this is by adding another Subreport. The workaround involves creating a blank Main Report and adding three Subreports in the Report Header.

1) Subreport 1 finds out the Top 50

2) Subreport 2 checks whether groups belong to this Top 50 and finds the Top 10 > Sends the print time formula to Subreport 3 as an input

3) Subreport 3 filters records based on this Top 10 and uses a Crosstab to display the Top 10 based on the workaround I posted in this thread:

Well, the only issue is - the workaround in the thread above works great for Crystal Reports 2008/2011/2013 but for CR for Enterprise!

-Abhilash

Uli_Stoeckl
Participant
0 Kudos

Hi Abhilash,

sorry for my late reply. In addition to your proposal I have had to define a proper BEx hierarchiy for the charakter 0coorder. With this hierarchy I was in a position to avoid the claculation on group header. After this I could suppress any hierachy element outside the Top 50.

But the most important step was your shared array string var in der report header.

thank a lot

Uli

abhilash_kumar
Active Contributor
0 Kudos

Thanks for posting back. Glad I could help, Ulrich!

-Abhilash

Answers (1)

Answers (1)

former_member205840
Active Contributor
0 Kudos

Hi Ulrich,

For first Top N i.e. 50 you use group sort expert then for second Top N write below formula

Whileprintingrecords; // Place this formula on group header 1

Numbervar x:=x+1;

Now go in Section Expert and write a suppress condition like

Whileprintingrecords;

Numbervar x;

If X >10 then true else false

This will limit your group header 1 to 10

Thanks,

Sastry