Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Duplicate data needs calculated once for crosstab Post Reply Post New Topic
Author Message
craigm
Newbie
Newbie


Joined: 30 Mar 2009
Location: United States
Online Status: Offline
Posts: 3
Quote craigm Replybullet Topic: Duplicate data needs calculated once for crosstab
    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
  1234              2.00       7001              joe
  7701            10.00       7002            Mike
  7701            10.00       7002            Mike
  7701            10.00       7002            Mike 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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".


Edited by DBlank - 30 Mar 2009 at 10:29am
IP IP Logged
craigm
Newbie
Newbie


Joined: 30 Mar 2009
Location: United States
Online Status: Offline
Posts: 3
Quote craigm Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?


Edited by DBlank - 30 Mar 2009 at 1:42pm
IP IP Logged
craigm
Newbie
Newbie


Joined: 30 Mar 2009
Location: United States
Online Status: Offline
Posts: 3
Quote craigm Replybullet 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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.049 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.