Customer info when they buy two products 1

[RobbieB]

Crystal XI Developer
SQL Server 2000 Enterprise Ed.

Here's my problem,

I work for a ski area and I needed to write a report that would show all customers who purchased pass insurance with their season pass. These are sold as two seperate products and customers do not need to buy pass insurance. So I needed pass type, customer name, insurance purchased and the net $ paid for the insurance.

I wrote a report that selects all sales of passes by post date (date range parameter entered by user). I put a sub report in the Grp 2 header joined on customer.number and by the date range parameter with the select criteria only selecting the item.number for our companies portion of the insurance sale. The sub report groups on customer number with Sales Before Tax and item number in the detail section. I am passing the revenue from the sub report to the main report with a shared variable. Then I grouped the report like this

GRP Hdr 1 Item.Description
Grp Hdr 2 Customer - @RevStart InsuranceSubReport
Grp Hdr 2b @RevCalc
Grp Footer 2 Displaying Customer Name and @RevTotal

The @Rev* formulas calculate the revenue from the sub report for that customer and move it into the footer of Group 2 so I can suppress the name if the revenue is $0.00. However, I can not suppress Grp Header 1 and I have made the fields in Grp Hdr 2 and 2b as small as possible and suppressed them but I end up with a lot of space between names because of those fields.
Does anyone have any ideas of how to better write this report.

 

[lbass]

If you only want to show people who paid for both, why not just link the two tables on customer ID and eliminate the subreport?

-LB

 

[RobbieB]

Well, because in order to show just the people who purchased passes I need the selection criteria of the main report to be {item.function_id} = "WP" //WP = Winter Pass. This will pull any pass out of an invoice without showing anything else from the invoice. The insurance is sold as a seperate item on the invoice.

 

[lbass]

Is there mroe than one table? Please show the tables (and how they are linked) and fields that you are working with.

-LB

 

[RobbieB]

i am using 6 tables. The Post_Date comes from the Post table. The only way to join to the post table is like this.
Post --> to Deposit on Post # ---> to Audited_Cash on Deposit ---> to Audited_Invoice_Line on Register and Control number. --- Item joined to Audited_Invoice_Line on Item number Customer is also joined to Audited_Invoice_Line on Customer number. ALL JOINS ARE EQUAL JOINS.

besides the fields mentioned in my origial post I have the
Audited_invoice_line.Register, Audited_invoice_line.control,
Audited_invoice_line.Invoice_no and Audited_invoice_line.Line_no fields.

The customer group is actually a formula that reads like this:
if isnull ({CUSTOMER.LAST_NAME}) or {CUSTOMER.LAST_NAME} = ""
then {CUSTOMER.FULL_NAME} & " - " & totext({CUSTOMER.CUSTOMER_NO},"#")
else if isnull ({CUSTOMER.FIRST_NAME}) or {CUSTOMER.FIRST_NAME}=""
then {CUSTOMER.FULL_NAME}& " - " & totext({CUSTOMER.CUSTOMER_NO},"#")
else {CUSTOMER.LAST_NAME} & ", " & {CUSTOMER.FIRST_NAME}& " - " & totext({CUSTOMER.CUSTOMER_NO},"#")

The select criteria for my main report looks like this:

{POST.POST_DATE} in {?DateRange} and
{ITEM.FUNCTION_ID} in ["wp","ac"]
//WP = "Winter Pass" and "AC" is a different kind of pass we want to include.

The sub report uses the same tables and it's select criteria looks like this:

{POST.POST_DATE} in {?DateRange} and
{AUDITED_INVOICE_LINE.ITEM_NO} = 163818 and
{AUDITED_INVOICE_LINE.CUSTOMER_NO} =
{?Pm-AUDITED_INVOICE_LINE.CUSTOMER_NO}

The only other fields in the sub report not mentioned in my original post are
Audited_invoice_line.Register, Audited_invoice_line.control,
Audited_invoice_line.Invoice_no and Audited_invoice_line.Line_no fields, @SalesBeforeTax

@SalesBeforeTax is a forula subtracting the line_tax from the Line_net.

I am passing a shared variable from the sub report to the mai report called "X". "X" represents the @SalesBeforeTax.

The formula in the main report that takes the shared variable and puts in the GroupFooter two looks like this

GroupHeader 2 - @MoveStart
whileprintingrecords;
shared currencyvar W;
W := 0

In GroupHeader2b - @MoveCalc

whileprintingrecords;
shared currencyvar W;
shared currencyvar X;
W := W + X

In GroupFooter2 - @MoveTotal

whileprintingrecords;
shared currencyvar W;
W := W

I suppress GroupFooter with this formula -
{@MoveTotal} = 0

If the Customer did not buy Pass Insurance the value passed to the main report is $0.00.

I hope this clears things up....

 

[lbass]

If the issue is the space created by group headers 2a and 2b, then make sure you have suppressed all sections within the subreport. Also select the subreport->format subreport->subreport tab->check "suppress blank subreport". Then in the main report, go to the section expert and check "suppress blank section" for both GH#2 header sections. You won't however be able to suppress the GH#1 header based on a subreport value,, since it is calculated after the GH1 is printed.

-LB

 

[RobbieB]

Thanks Lbass - I had all of the sections of the sub report hidden but I didn't have Header 2a and 2b suppressed if blank because I didn't think they would be blank because they have formulas in them. It worked though so, thank you. Now if I could just spress Group1 when there are no sales of insurance. I guess I'll just have to live with it.