How best to develop a complex report 2

[johnstrang]

Hi,

Using CR 8.5 connected to an Oracle database.
I have a request to develop a report giving various information on orders received in my company.
We need the orders selected by
- Service (each order is for 1 service only)
- Week Number
- Type (either New, Change, Cease)

Report Layout to be like this:

          !--------Week---------!   !------Cumulative------!
Service   New     Change    Cease   New     Change    Cease

S1        5 50%  1  25%  1  33.3%   20 40%  4 40%     2 40%
S2        2 20%  2  50%  1  33.3%   15 30%  2 20%     1 20%
S3        3 30%  1  25%  1  33.3%   15 30%  4 40%     2 40%

TOTALS   10      4       4          50     10         5

Part of the problem is this is already a subreport (the main report has details on the individual orders, and various other summaries) so I cannot use a further subreport.

What I have done is have 2 subreports, one for weekly and one for cumulative.
This works fine - all the totals and %ages are correct. The only problem I have is that there are services which have NO orders in some weeks so they don't appear in the "weekly" subreport.

Is there a way to incorporate both in just 1 subreport? I can do it apart from calculating the %age in the "Weekly" one, because I have to use a formula for calculating the week number:

datepart("ww", {ORDER.CREATION_TIME} + 7, crMonday) - 1

which means that to obtain the counts for the "weekly" side I have to use a formula like

whileprintingrecords;
if {ORDER.ORDER_TYPE} = "New Installation" and {@order_week_number} = maximum({@order_week_number}) then 
(
numbervar week_new := week_new + 1 ;
numbervar tot_week_new := tot_week_new + 1 ;
)

Note that I am not looking for detailed help with formulas etc, just a pointer in the right direction.

I have been told that "everythng is possible" with Crystal reports if only you can think of how to do it.

Many thanks for taking the trouble to read this, please let me know if I have nmissed anything.

John

 

[Katy44]

I'm sorry I can't be much help with the problem in general, but:

"This works fine - all the totals and %ages are correct. The only problem I have is that there are services which have NO orders in some weeks so they don't appear in the "weekly" subreport.""

Have you tried doing this using an outer join? (Left join with services to orders, assuming these are separate tables), then make sure that these left joins are also on any table joined to orders etc.

I know what you mean about the complex report thing, I also have developed a report with many subreports, conditional grouping, etc and it seems like you solve one problem only to create another!

 

[johnstrang]

Hi Katy,

Thanks for the prompt reply - but all the data is contained in just one table (ORDER).

Maybe I could include the same table twice, with a left outer join between the two occurrences.

John

 

[Katy44]

You could create a command within Crystal which is just select distinct values of Services and link to this - would this work?

 

[johnstrang]

Hi again Katy,

That sounds like an interesting idea, but I don't know how to do that - is it straightforward?
Could you please let me know how I can do that?

Many thanks again for all your time,

John

 

[Katy44]

I'm not sure whether it's possible in 8.5, but go to the database expert (or wherever you have your data sources set up), expand the connection you are using and there should be an option to "Add command".

 

[lbass]

I might be wrong, but I think "Add command" became available only in CR 9.0.

I think you could use one subreport if you limit the subreport to those records you want for the cumulative amounts (this might be {@weeknumber} <= {?weekno}), and then use a parameter to identify the week number for the weekly calculations, so that your weekly count formula would look like:

if {@weeknumber} = {?weekno} and
{ORDER.ORDER_TYPE} = "New Installation"
then 1 else 0

Then you could insert summaries on this as well as use it in the percentage calculations.

Not sure this is what you're looking for...

-LB

 

[Ngolem]

If everything is in a single table I am not certain you even need subreports to do this.

- Service (each order is for 1 service only)
- Week Number
- Type (either New, Change, Cease)

I would group by

Group 1 header {@WeekNumber)
Group 2 header {table.Service}
Group 3 header {Table.Type}
details (suppressed)
Group 3 footer (suppressed)
Group 2 footer (Shows Service data line)
Group 1 footer (shows the totals)

From the look of your data the cumulative is addition from one week to the next for each service...these totals are easily accumulated with variables in a formula suppressed in the detail section.

Having no services executed in a week is not a great problem and can be accomodated.

How many "services" are there...100's?? or a relative few

This is just a manual crosstab type report really.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[synapsevampire]

Consider building the data out in a View in Oracle rather than building it in Crystal.

When posting, you'll get better results if you post example data and expected output, just posting the expected output without sharing what the data looks like leaves a good deal to the imagination, even if it is just one flat table.

I doubt that you require a subreport here, as all you're doing is looking at the same data.

Also there appears to be a flaw in your logic because you compare the week to the maximum week, but if you don't have any data for the current week (assuming that's what you want), this will just give you last weeks data.

Try creating 6 formulas to maintain the counts, where the following demonstrates the current week (or whatever the first 3 week columns are):

datepart("ww", {ORDER.CREATION_TIME} + 7, crMonday) - 1 =
datepart("ww", currentdate + 7, crMonday) - 1

Of course I'm guessing that you want it for the same week as the currentdate, if not, adjust the currentdate accordingly using a minus.

Again, not posting example data is why people are having difficulty helping you, and most don't bother to ask for some odd reason, I assume that it's akin to not stopping and asking someone for directions...

Try grouping by service

Create a formula such as:
if {table.type} = "New Installation"
and
datepart("ww", {ORDER.CREATION_TIME} + 7, crMonday) - 1 =
datepart("ww", currentdate + 7, crMonday) - 1
then
1
else
0

You can then place this in the details, right click it and select insert summary->sum

This sum formula will then be available for use by other formulas in the group header, details or group footer.

Repeat the process for the other 5 fields and you should be good.

Hope this gets you on your way.

-k

 

[johnstrang]

Dear all,

Firstly, I am sorry if my example data and required output was not detailed enough - but I cannot publish any live data in a public forum such as this.

Many thanks for the replies - the approach of having 6 formulas worked fine, but I had a little trouble enabling summaries - it seems that if a formula includes the result of another formula you cannot create a summary on it :-(

To overcome that I used a parameter to the subreport, contaiing the required week number.
I also found there is a field called CREATION_WEEK, which made life easier.
My formulas for the weekly counts are like this:

if {ORDER.ORDER_TYPE} = "New Installation" 
and {ORDER.CREATION_WEEK} = {?Pm-@week_number}
then 1 else 0

It's all fine now, thanks again for increasing my knowledge.

John

 

[synapsevampire]

You didn't supply example data, and when you do, it doesn't need to be valid data, just valid for the expected output.

You should have posted that you had troubles based on the 6 formula approach, we can still sum them, it's simply another formula to do so.

Subreports are slower and require more maintenance, try to avoid using them, there's almost always an alternative.

-k

 

[Ngolem]

I agree SV....this report doesn't need a subreport....makes the report more complex and slower than it needs to be.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[johnstrang]

Dear All,

I thought this was complete, now I have one further requirement.

To recap:
All fields are in 1 table (ORDER)
Data is grouped by ORDER.SERVICE
Here is an example of the report:

			Week Number 21						Cumulative				
													
			New		Change		Cease		New		Change		Cease
					
CATM			0	0.0% 	0	0.0% 	0	0.0% 	3	0.1% 	0	0.0% 	6	0.2% 
CFRD			0	0.0% 	1	2.3% 	0	0.0% 	8	0.2% 	13	0.8% 	47	1.8% 
CFRS			0	0.0% 	0	0.0% 	0	0.0% 	25	0.5% 	12	0.8% 	53	2.0% 
CIAS			0	0.0% 	0	0.0% 	0	0.0% 	0	0.0% 	0	0.0% 	3	0.1% 
ISC			3	1.6% 	0	0.0% 	0	0.0% 	9	0.2% 	1	0.1% 	6	0.2% 	
ISIC			0	0.0% 	0	0.0% 	0	0.0% 	32	0.6% 	0	0.0% 	0	0.0% 
ITC			1	0.5% 	0	0.0% 	0	0.0% 	29	0.6% 	7	0.4% 	0	0.0% 
ITN			7	3.8% 	0	0.0% 	0	0.0% 	120	2.4% 	5	0.3% 	1	0.0% 
IWPI			0	0.0% 	0	0.0% 	0	0.0% 	0	0.0% 	0	0.0% 	1	0.0% 
MIL			0	0.0% 	0	0.0% 	0	0.0% 	110	2.2% 	1	0.1% 	26	1.0% 
MTP1			0	0.0% 	0	0.0% 	0	0.0% 	1	0.0% 	0	0.0% 	0	0.0% 
MTP3			0	0.0% 	0	0.0% 	0	0.0% 	1	0.0% 	2	0.1% 	1	0.0% 
SATM			0	0.0% 	1	2.3% 	0	0.0% 	8	0.2% 	5	0.3% 	4	0.1% 
SBA			7	3.8% 	1	2.3% 	1	1.7% 	244	4.9% 	6	0.4% 	14	0.5%

Now the problem is that the first column from the (ORDER.SERVICE) field needs to be further grouped by the initial letter of the field
ie all those beginning with C need to be grouped under the heading "Concert"; all those beginning with "M" as "Mobile"; "S" as "Special" and others as "Internal".

So it should look like this

			Week Number 21						Cumulative				
													
			New		Change		Cease		New		Change		Cease
					
Concert
CATM			0	0.0% 	0	0.0% 	0	0.0% 	3	0.1% 	0	0.0% 	6	0.2% 
CFRD			0	0.0% 	1	2.3% 	0	0.0% 	8	0.2% 	13	0.8% 	47	1.8% 
CFRS			0	0.0% 	0	0.0% 	0	0.0% 	25	0.5% 	12	0.8% 	53	2.0% 
CIAS			0	0.0% 	0	0.0% 	0	0.0% 	0	0.0% 	0	0.0% 	3	0.1% 
Internal
ISC			3	1.6% 	0	0.0% 	0	0.0% 	9	0.2% 	1	0.1% 	6	0.2% 	
ISIC			0	0.0% 	0	0.0% 	0	0.0% 	32	0.6% 	0	0.0% 	0	0.0% 
ITC			1	0.5% 	0	0.0% 	0	0.0% 	29	0.6% 	7	0.4% 	0	0.0% 
ITN			7	3.8% 	0	0.0% 	0	0.0% 	120	2.4% 	5	0.3% 	1	0.0% 
IWPI			0	0.0% 	0	0.0% 	0	0.0% 	0	0.0% 	0	0.0% 	1	0.0% 
Mobile
MIL			0	0.0% 	0	0.0% 	0	0.0% 	110	2.2% 	1	0.1% 	26	1.0% 
MTP1			0	0.0% 	0	0.0% 	0	0.0% 	1	0.0% 	0	0.0% 	0	0.0% 
MTP3			0	0.0% 	0	0.0% 	0	0.0% 	1	0.0% 	2	0.1% 	1	0.0% 
Special
SATM			0	0.0% 	1	2.3% 	0	0.0% 	8	0.2% 	5	0.3% 	4	0.1% 
SBA			7	3.8% 	1	2.3% 	1	1.7% 	244	4.9% 	6	0.4% 	14	0.5%

Main problem is that "Internal" includes all other letters of the alphabet so can't be sorted by ORDER.SERVICE

I can easily Change Group to get just the 4 Group Names
Concert
Mobile
Special
Internal
but then I miss all the detail lines eg CATM, CFRD etc.

I can use a formula as group name to get the names Concert, Mobile etc, but then I get
Concert
Internal
Mobile
Internal
Special
Internal

If anyone can help with this I'd be very happy - hope this is sufficient information.

Thanks in advance,
John

 

[lyanch]

The grouping is easy with a formula.

if {table.field} startswith "C" then "Concert"
else if {table.field} startswith "M" then "Mobile"
else "Internal"



Then group on the formula. If you want them in a specific order, then use the formula and "specified order" selection in the grouping format.

Lisa

 

[johnstrang]

I have had a brainwave.

I have created a formula

{@service_group}
select left({ODS_CLF_ORDER.CLARIFY_X_SERVICESHORT},1)
case "C" : "Concert"
case "M" : "Mobile"
case "S" : "Special"
default : "Internal"

then grouped on {@service_group} as Group 1, with my existing group on ORDER.SERVICE as Group 2.

It appears to work, but may need a little refinement.

I'll post my results tomorrow (Thursday 27th).

John

 

[johnstrang]

Hi all,

It works fine. Thanks again to Lisa (I've given her post a star) who posted her reply as I was posting my own solution - obviously great minds think alike

Have a good weekend,
John