Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Use values from other detail row Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Rick
Newbie
Newbie


Joined: 14 Mar 2007
Online Status: Offline
Posts: 22
Quote Rick Replybullet Topic: Use values from other detail row
    Posted: 21 Jun 2007 at 6:32am
Hi,
 
I am having a hard time figuring out the following:
In an existing group, I have several detail rows. One field, say X, in these rows is only filled for one of the rows in the group, the others are null.
However, I need to summarise data from a paticular detail row that has the null-value in X. And this value is crucial.
How can I "tell" this detail row that it must have have the same value for X that is only present in one, other, row in its group?
If I am not explaining the problem coherently, please let me know, I can send an attached example. Thanks!
 
Rick
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 21 Jun 2007 at 11:16am
I"m a little lost here. Can you rephrase or post a couple sample rows?
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 21 Jun 2007 at 4:28pm
If the record that has the value in the field is the first record that displays in the details, you can try using a formula something like this:
 
StringVar fieldX;
If notIsNull({table.fieldX}) then
  fieldX := {table.fieldX};
else
  fieldX := fieldX;
fieldX
 
Change the "StringVar" to whatever the actual type of the field is.
 
-Dell
 
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Rick
Newbie
Newbie


Joined: 14 Mar 2007
Online Status: Offline
Posts: 22
Quote Rick Replybullet Posted: 22 Jun 2007 at 2:15am
Thanks Hilfy,
 
I tried something vaguely similar, which seemed to work to a certain extent:
if {table.fieldX} <> 0
    then 0
    else previous ({table.fieldX})
 
Anyway, I'll try to describe the report for better understanding. The report contains financial bookings from incoming invoices. The amount of each incoming invoice is booked on one or more cost accounts. I am interested in one particular cost account (#4622), accumulated per supplier.
 
Group = booking
Details: invoice#, supplier#, financial account#, amount
 
The details per booking might look like the following examples (1600 is a standard financial account# for incoming invoices, total amount):
 
Booking 1:
70001, 12345, 1600, -$ 12.000
null, null, 4622, $ 10.000 (4622 is the cost account I am interested in)
null, null, 4635, $ 2.000
 
Booking 2:
70002, 23456, 1600, -$ 4.000
null, null, 4788, $ 1.000
null, null, 4850, $ 3.000
 
So, not all bookings will contain account# 4622, and if it does, it will not be the only account# the invoice amount is distributed to.
 
Now, if I want to have all the amounts booked on account# 4622 for each supplier, how can I do that?
 
I have done this so far:
- Selected: {table.account#} in ["4622", "1600"]
- Grouped on booking#
- Sorted on booking#, then on account#
- Used the above formula to get the supplier# in the detail row of account# 4622
- Made a crosstab to get the total amounts per supplier
 
The amounts are in the crosstab now, but I cannot use them as a subreport in a main report (evaluation time error). And I cannot sort the data on supplier# either, or else my formula won't work.
 
Any suggestions please? And, a small question besides: how can I select all booking# that contain account# 4622 AND account# 1600?
 
Thanks very much! Hope I've not made the issue more complicated instead of easier to understand. I apologise in advance.
 
Rick
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 22 Jun 2007 at 7:09am
What other information is in the table that will link the booking records together - e.g. how do you know that a specific 4266 record goes with a specific 1600 record?
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Rick
Newbie
Newbie


Joined: 14 Mar 2007
Online Status: Offline
Posts: 22
Quote Rick Replybullet Posted: 25 Jun 2007 at 12:17am
There are really just 2 tables that are relevant: invoices table and bookings table.
 
Invoices table has (among others) an invoice#, a supplier# and an a total amount.
Bookings table has (a.o.) an invoice#, a supplier#, account# and amounts.
 
So, both invoice# and supplier# can be used as a link between the 2 tables.
When an invoice is entered in the invoice table, the total amount of the invoice is booked on account# 1600.
Later, when it is determined what kind of costs are on the invoice, the invoice amount is distributed to various cost accounts (in the bookings table).
The problem here is that, in the bookings table, the original invoice# and supplier# are only in the detail row of account# 1600. These fields are empty for the various cost accounts, e.g. 4622.
Each invoice will result in a unique booking#.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 25 Jun 2007 at 7:12am
If the invoice# and supplier# are not in the booking record, how do you know that a specific non-1600 record belongs to a specific invoice?
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Rick
Newbie
Newbie


Joined: 14 Mar 2007
Online Status: Offline
Posts: 22
Quote Rick Replybullet Posted: 25 Jun 2007 at 11:45pm
An invoice and a booking have a 1 on 1 relation (although there are also bookings non-related to invoices).
The invoice# and supplier# are created in the invoice table. Here, the amount of the invoice is booked on account# 1600.
When distributing the amount of the invoice to various cost accounts, an entry in the booking table is made.
 
An entry in this table creates a booking#. It has several detail rows:
 
1 row with account# 1600 (the invoice amount to be distributed)
several rows with different account# where the cost amounts are booked to.
 
The invoice# and supplier# are only filled in the 1st row, with account# 1600. These fields are null for the other rows in the booking record.
 
What links the 1st row with the other rows is the booking#, which is unique and relates to 1 invoice#.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 26 Jun 2007 at 8:24am

Cool...I think I've got it now.

You'll  have to use something like the formula I originally posted.  Your formula using "previous" will only work if the account you're trying to summarize is immediately after the 1600 account.  If you set things up so that the 1600 account is always first, then the formula that I posted that uses a variable will always give you the corresponding invoice#.

To do a summary on a field for the specific account number, create a formula something like this:

if {booking.account} = 4622 then {field to summarize} else 0

You would then summarize the formula instead of the field.  Or you could use a running total field with a condition on it so that it will only work with values for the specific account.

If you only want to display records for the specific account, you could set select filter.  However, you'll need to include both the account you're looking for AND the 1600 account so that you can get the information invoice information.  You would then suppress the details section where the account number = 1600.

.....I just thought of an easier way to get the invoice number, etc. on the report.  This will only work if you are only displaying one specific account number....

Details1 - put the invoice number and any other fields that you need from the 1600 record.  Supress the section if the account number <> 1600.  Set it to "Underlay Following".

Details 2 - put the information that you need from from the 4622 account, leaving space for the invoice #.  Suppress this section if the account number <> 4622. 

Because of the "Underlay Following", the two sections will "merge" together and you'll have your invoice # on the same line as the rest of the account data.

-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Rick
Newbie
Newbie


Joined: 14 Mar 2007
Online Status: Offline
Posts: 22
Quote Rick Replybullet Posted: 27 Jun 2007 at 3:19am
Thanks for your help Hilfy, trying it now...
I get an error however, when writing your "fieldX" formula.
 
My formula looks like this:
 
NumberVar Supplier;
If not IsNull ({Table.FieldX})
    Then Supplier := {Table.FieldX};
    Else Supplier := Supplier;
    Supplier
 
When checking this formula, CR tells me that the part from "Else" on does not seem to be part of the formula. Is my syntax wrong?
 
Small question besides: I have now selected all bookings, and only selected account# 1600 and 4622. That means I also get all bookings with account# 1600 that do not also have account# 4622.
How can I change the selection formula to make it select only the bookings that contain BOTH 1600 AND 4622? I have no experience with SQL whatsoever. Thanks!!!
IP IP Logged
Page  of 2 Next >>
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.031 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.