Report Design: Duplicate data needs calculated once for crosstab

Print Page | Close Window

Duplicate data needs calculated once for crosstab

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5923
Printed Date: 19 May 2024 at 1:20pm

Topic: Duplicate data needs calculated once for crosstab

Posted By: craigm
Subject: Duplicate data needs calculated once for crosstab
Date Posted: 30 Mar 2009 at 6:22am

I am trying to make a crosstab to show who entered what payments. The data has payment_link number. I would only want to add a unique payment_link to the user id crosstab.

How would I get 2.00 for joe

10.00 for mike

and 12.00 total

How should I handle this??

Data in the sql database

Check number, Amt, Payment_link, user id

1234 2.00 7001 joe

7701 10.00 7002 Mike

Replies:

Posted By: DBlank
Date Posted: 30 Mar 2009 at 10:28am

This might work

Sort your data by checknumber and then payment link

Create a formula as "Unique_Amount":

if onfirstrecord then {table.amountfield} else if previous({table.checkNUmberfield})={table.checknumberfield} then 0 else {table.Amountfield}

Place it on your details to validate it is giving the correct data on the correct rows and a 0 on duplicate data.

Use this @Unique_amount formula field to SUM on in your crosstab.

Double check the results.

This assumes you do not have the user changing mid way through any one "set".

Posted By: craigm
Date Posted: 30 Mar 2009 at 12:37pm

I have not been able to place the formual in the crosstab table I am only allowed to use fields not formulas. I did get the formula to diplay in detail properly.

Posted By: DBlank
Date Posted: 30 Mar 2009 at 1:40pm

Hmmm. I was concerned about that so I tested it in a sandbox report (before i posted the suggestion) and was able to use a similar formula field to get summaries in a crosstab...

In your crosstab you have Columns on the UserID field and rows on the Payment_link or CheckNumber field, correct?

There should be an X1 Unique_Amount field that you can insert into the Summarized Fields and set it as a Sum.

It is not listed in the "available fields" for you at all?

Posted By: craigm
Date Posted: 31 Mar 2009 at 6:18am

I have user user_id for row and delivery_route for the column. I am currently summarizing ar_check_amount(adding too many times becaause of duplicate data). In the cross tab expert I only have available fields (regular database fields that are in the report no formulas) and odbc field (regular database fields). I don't have any formulas. I am running crystal reports XI. I am grouping by

#1 transaction_date

#2 route_number

#3 payment_link_number

I am placing the cross tabe in group #2.

Transaction_date & Route number will be the same for each payment_link_number.

I don't see the unique_amount formual field to insert

Print Page | Close Window