Grouping

[sabeeha08]

Information
Method of study ( needs to be calculated) Type ( data form system)

Taught course part time FEEDER or INTENSIVE or BESPOKETAU or BESPOKEIN
Revision WEREV or QBREV or STDREV
Taught course and revision (FEEDER or BESPOKETAU) and (WEREV or QBREV or STDREV)
Distance learning and revision (BASIC or BASICOL or BASICPLUS) and (WEREV or QBREV or STDREV)



data form system


Cust Type Paper

1 FEEDER P1
1 WEREV P1
1 BESPOKEIN P2
2 BASIC P3
2 STDREV P3
3 FEEDER P1
3 BESPOKEIN P3



output required


cust method of study P1 P2 P3 ( hence the papers)

1 Taught course and revision 1 0 0
1 Taught course part time 0 1 0
2 Distance learning and revisio 0 0 1
3 Taught course part time 1 0 1


grouping will be required on cust and Method of study,


CR var 8.5 DB Oracle

if any one can suggest how i can approach this, since i am struggling to do this grouping since it need to be calculated for multiple situations
any help will be much appreciated

thanks

 

[Madawc]

At first sight, your problem looks like a Crosstab, an option found under Insert. (If you have already tried that and it won't work, you should have mentioned that.)

Looking more closely, I'm not sure a crosstab would work. It seems you might need multiple results from a single detail line, which won't work in a crosstab.

The alternative is a 'Mock-Crosstab'. A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crsytal 11.5, you can also duplicate formula fields using the Field Explorer. But I did several of them when I still had 8.5 and you should have been given an example along with some crosstabs.

Each running total will count the record if it was within the criteria

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[sabeeha08]

I did try to write a report which had multiple sub-reports for each method of study, but this makes the report to slow. is it possible to use the whileprintingrecords statement to some how accumulate the paper and type to do some grouping on this on the same report.

Ibass my man where are you, you seem to always have an answer for me, need help please

thanks

 

[IanWaterman]

You need to develop manula cross tab.

Group Report by Customer.

Split Group Footer into 4 sections

Add Four text boxes with you method of study one in each section.

Then create 12 formula

@TaughtCourse P1
If type in [ 'FEEDER', 'INTENSIVE', 'BESPOKETAU', 'BESPOKEIN'] and
Paper = 'P1' then 1 else 0

@TaughtCourse P2
If type in [ 'FEEDER', 'INTENSIVE', 'BESPOKETAU', 'BESPOKEIN'] and
Paper = 'P2' then 1 else 0

Repeat for other permutations.

At appropropriate add Maximium summaries in the GF sections.

If you only want to see Methods which have a record suppress section if all the summaries return a zero.

Ian

 

[sabeeha08]

HI Ian,

Thank you very much for the quick response i will give this a try, only issue is i have 14 papers and many more method of study with a huge permutation of type. does this mean i will have to write formula's to include all possibility's

thanks

 

[IanWaterman]

Because FEEDER or BESPOKETAU appear in more than one method you have to hard code data, as you can only count a record once. If you tried to group on a method formula it would fail for this reason.

Alternatively you can create a command which allows you to union data sets and create additional records for each method. If your SQL is good enough that would be the most efficient way forward.

Other wise its lots for formulae

Ian

 

[sabeeha08]

Ok Ian seems it will have to be formulas then,


only other issue is when i have to check for the below it needs to establish if they have a combination of these types
any ideas, thank you again for the help

Taught course and revision
FEEDER or BESPOKETAU) and (WEREV or QBREV or STDREV)

 

[IanWaterman]

Sort data by type and add variable in details

@Course
whileprintingrecords;

global booleanvar course;
global booleanvar revision;

If type in [ 'FEEDER','BESPOKETAU'] then course:= true;
If type in [WEREV, QBREV, STDREV] then revision:= true;
// add more boolean vars to cover other permutations and papers


In cust header

@reset
whileprintingrecords;

global booleanvar course:= False;
global booleanvar revision:= False


Instead of using Maximum summaries you will now need formula to test the values of these boolean vars to determine 1 and zeros for your cross tab.

Ian

 

[lbass]

I can't follow your post, and I did respond to an earlier post regarding this data, but then you came back in under a different handle and I couldn't even find the first post, so I gave up.

-LB

 

[sabeeha08]

Hi Ibass

You did response to my post on a another account ( thank you for that) but unfortunately for some reason i could not access my account and had to create a new account. Ian has suggested an idea which i am going to attempt but seems there will be lot formulas for this.

the information i have given on the top of the shows how i am trying to group the data and what i get from the system and also the layout of the required data

form the DB i get data like this

Cust Type Paper

1 FEEDER P1
1 WEREV P1
1 BESPOKEIN P2
2 BASIC P3
2 STDREV P3
3 FEEDER P1
3 BESPOKEIN P3

so for customer 1 , for paper P1 , he can study FEEDER, WEREV, so if the student meets this criteria then i need allocate a line for him

i.e .

cust method of study P1 P2 P3

1 Taught course and revision 1 0 0


similarly a student can fall under multiple criteria.
thanks

 

[lbass]

I think I follow now. You should either follow Ian's suggestion or use a command to create a union all, as he also suggested. The latter would be simplest. Why did you rule that out?

-LB

 

[sabeeha08]

Thanks Ibass, for this i will need to create Views in Oracle DB and I am trying to avoid this, there are restriction to what we can do on the DB.

 

[IanWaterman]

YOu do not have to creat a view. You can use bespoke SQL in a Command within the report.

Ian

 

[sabeeha08]

Ok Ian would you able to just explain how i would go about doing this in version 8.5 in little bit of details since i have never done this before

sorry for asking so many question, i really appreciate the help you guys are giving me

thanks

 

[IanWaterman]

Sorry

Missed the point that you were using 8.5

You will need to build a view or follow my suggestion above.

Ian

 

[sabeeha08]

Thanks Ian and Ibass for all the help, I think this one is too
complicated for me to do ( not one to give up too quickly but have spent too long on it), I may get the data exported to excel and write a macro to do it

thanks guys

 

[sabeeha08]

HI Ian,

i wonder if you can suggest what i need to do here, i have done the following on what you have suggested and am very close to the result but not there yet.

i Havee grouped the data as below


G1 Cust
G2 Paper
G3 Type

i acuumulate the data using this formula and then dispaly this on 2 section at the G2 level applying a supression where its not blank, this infect gives me at paper level what i need. I am also calculating a value for each paper at line level and was hoping some how i can use that.


@accnum

whileprintingrecords;
numbervar a;
numbervar b;
if {YBPCRSTYPE.CRSTYPE_0} in ["FEEDER","INTENSIVE","BESPOKETAU","BESPOKEIN","WEREV","QBREV","STDREV","BASIC","BASICOL","BASICPLUS","DL"] then

a:= a +{@crsgrp} else
b := b + {@crsgrp}

{@crsgrp}
select {YBPCRSTYPE.CRSTYPE_0}
case "FEEDER" :1
case "INTENSIVE" :1
case "BESPOKETAU" :1
case "BESPOKEIN" :1

case "WEREV" :2
case "QBREV" :2
case "STDREV" :2

case "BASIC" :10
case "BASICOL" :10
case "BASICPLUS" :10

case "DL" :25

default :50


currently this is how i get the data

bp paper type num
0001922 E3 BASICPLUS 10
0001922 F3 BASICPLUS 10
0003961 E3 FEEDERQBREV 3
0003961 F3 FEEDERQBREV 3
0003961 P3 FEEDERQBREV 3
0003964 E3 QUESTDAY 50
0009169 E2 FEEDER 1
0009169 F1 FEEDER 1
0009909 E3 FEEDERQBREV 3
0009909 F3 FEEDER 1
0009909 P3 FEEDER 1
0010456 E3 FEEDERWEREV 3
0010456 F3 FEEDERWEREV 3
0010456 P3 FEEDERWEREV 3


this is how i need the data

Row Labels num type E2 E3 F1 F3 P3
0001922 10 BASICPLUS 1 1
0003961 3 FEEDERQBREV 1 1 1
0003964 50 QUESTDAY 1
0009169 1 FEEDER 1 1
0009909 1 FEEDER 1 1
0009909 3 FEEDERQBREV 1
0010456 3 FEEDERWEREV 1 1 1

i am not sure what group level i can display the so that it will work

any suggestion will be very much appreciated

thanks

 

[IanWaterman]

Group by BP and type then create Paper test formula for each paper type

@E2

If Paper = 'E2@ then 1 else 0

Then do a max summary this formula in Type GF

Ian

 

[sabeeha08]

So Ian are you saying I don't need the grouping on paper then

 

[IanWaterman]

You are not sumarising on Paper, so no need to group at that level.

Ian