• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

Crystal Rep. Question: Report and Subreport

Hello:

I've been looking at questions and solutions trying to find out how I can do the following.

I have my main report and 3 subreports linked to it. One subreport counts distinct "number of sales quotes", another adds "number of order" and "sales", the third reports calc margin, profit, etc. My main reports has 2 groups, one is salesperson, and the other is active (Yes or No)...therefore I would like CR to do 3 sums per salesperson, the first one would be a subtotal, sum "quotes", "orders", etc for "yes", another subtotal for "no" and then the grand total.

How can I do this. I am no ace at CR, and have much more experience working with VB and Access for reports.I am using this in conjunction with the Goldmine CRM.

Thanks.
Joseph
0
CEGE
Asked:
CEGE
  • 6
  • 5
1 Solution
 
GJParkerCommented:
You can pass teh values form each of the subreports back to the main report by using shared variables, In the subreports create formulas to hold the value you wish to pass to the main report i.e.

//@SubQuotesValue
WhileprintingRecords;
Shared NumberVar Quotes := //Your Value here;

do the same for sales and orders in the respective subreports

then in the main report you can create formulas to read the values i.e.

//@Quotes
WhilePrintingRecords;
Shared NumberVar Quotes;

As long as the subreport values are not calculated using any print time functions you shoul dbe abale to summarise these liek any other field.

HTH

Gary




0
 
CEGEAuthor Commented:
Thanks.

I see what you are saying. This raises a few other questions. Where  in the subreport must I put the formula and where in the main report?

My main report looks like this

JOHN DOE

*Active Cliente
Company name  Quotes Orders Sales
Acme1               20        20      15000
Acme 2              80        90      25000  
Sub-total:          100      110    40000

*Potential
Company name  Quotes Orders Sales
Racme1               1            1   2000
Racme 2              5           3      500
Sub-total:             6          4    2500

Total:                106       114    42500

This is what I want to get..but right now all I can manage to pull out are the Company names split up into active and potential and then the total, How can I pull out the sub totals?  Quotes is one subreport and orders and sales another. I was doing it by creating another subreport.
thanks
0
 
mlmccCommented:
Looks good.  To add to Gary's comments

http://support.businessobjects.com/library/kbase/articles/c2007600.asp

mlmcc
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CEGEAuthor Commented:
How do I separate the info for subtotals for which I have Active and Potential?
0
 
GJParkerCommented:
I take your subreports are just calculating the totlas per sales rep ?

If so you need to change the subreports to calculate the subtotals and totals per rep and client status place this in the group 2 header, this will then display the subtotals for you. you can then bring back the totals using the shared variable method to calculate the grand totals.

Gary
0
 
CEGEAuthor Commented:
Thanks for yr patience.

My subreports calculate the totals per client per sales rep.

This is my main report:

GH1: Sales person
GH2: Status
details: displays the subreport info.
GF2: blank
GF1: blank

This is one of my subreports:
Footer: DistinctCountQuotes
this is per company per salesperson, where must I pass the status, how can I get this subtotal, if it just  does the count per company per salesperson? I don't conceptually get it.
Sorry for the trouble. Thanks.
0
 
CEGEAuthor Commented:
Can someone respond please?

thanks
0
 
GJParkerCommented:
This is how I see it

Your main report should be grouped by

GH1 - Sales Person - reset formula
GH2 - Company Status
GH3 - Company
Details - suppress
GF3a - subQuote, subOrders, subSales
GF3b - Accumulate formulas
GF2 - suppress
GF1 - Total Formulas

Then create a subreport to show QuoteCounts for each company this should be placed in the GH3 and linked to the main report on company (and rep if a company has more than one rep)

in the sub report create the formula place this in the subreport footer.
//@SubQuotesValue
WhileprintingRecords;
Shared NumberVar Quotes := //Your Value here;

repeat this with subreports for OrderCounts and Sales

Then we need to create the formulas on the main report to total the 3 sets of figures from the subreports

in GH1 Create a formula to reset totals for each rep

//@Reset
WhilePrintingRecords;
Shared NumberVar QuoteTotal :=0;
Shared NumberVar OrderTotal :=0;
Shared NumberVar SalesTotal :=0;

In the main report in GF3b create a formula to return and accumulate the totals

//@AccumulateTotals
WhilePrintingRecords;
Shared NumberVar Quotes;
Shared NumberVar QuoteTotal :=QuoteTotal + Quotes;

Shared NumberVar Orders;
Shared NumberVar OrderTotal :=OrderTotal + Orders;

Shared NumberVar Sales
Shared NumberVar SalesTotal := SalesTotal + Sales;

Finally in GF1 create 3 formulas to display the Totals

//@DisplayQuotes
WhilePrintingRecords;
Shared NumberVar QuoteTotal;

//@DisplayOrders
WhilePrintingRecords;
Shared NumberVar OrderTotal;

//@DisplaySales
WhilePrintingRecords;
Shared NumberVar SalesTotal;

Gary


0
 
CEGEAuthor Commented:
That was it....I was missing the GH3 section.
Thanks a lot.

Joseph
0
 
GJParkerCommented:
Glad I could help

Gary
0
 
CEGEAuthor Commented:
One last thing.---the sub totals are fine. I have @DisplayInfo in the GF2 section and in the GH2 section the Reset formula. How can I add them together, in let's say the GF1 section?
I forgot to mention it before.
0
 
GJParkerCommented:
Use the same 3 formula method

Gary

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now