Solved

Sum ONLY Visable Fields

Posted on 2013-05-17
10
491 Views
Last Modified: 2013-05-20
OK, So I have a FoxPro 9.0 Form and I have the box checked off under the field properties "Print When" tab to NO Print Repeated Values!

Works Great...however it still adds it as part of the Group Total.
How can I get the Group Total to ingore any Repeated Fields?

Thanks,
Mark
0
Comment
Question by:smithmrk
  • 6
  • 2
  • 2
10 Comments
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 350 total points
ID: 39175976
Hmm... Good question.

The Print When expression is used at the presentation layer of the report form but not in the calculations.

You have to design the calculated field conditionally, i.e.
if your Print When expression says e.g. "OrderYear = YEAR(DATE())" which means print just values belonging to this year then you have to add the condition to the field in Group total:

The original field:  "OrderQty"  (calculated as SUM in the Group footer)
The new field:   "IIF(OrderYear = YEAR(DATE()), OrderQty, 000000.00)"

BUT that's not all... You've requested to ignore repeated values in the SUM.
I have to say this is not possible in the report engine and you have to generate such data with respect to your requirement, e.g. sum the values in the program before the REPORT FORM command. You'll probably need two tables connected in relation to allow correct printout.

BTW, it is not obvious to omit repeated values from the SUM. Are you 100% sure two equal values must be summed as one number in all possible situations?  Imagine e.g. two different orders having exactly same order total...

Of course, if you are just counting distinct values then you have to omit all redundances. But again, it is easier to count such values in the code prior to the REPORT FORM.
0
 

Author Comment

by:smithmrk
ID: 39176533
OK, Thank You...but let me explain my situation better to get a better idea of where you are coming from.

I have an account number lets say 12345 that account number relates to two different people lets say husband and wife (join account)...I want to show BOTH names on the report but only show the amount paid once.

For Example:

12345     Joe Smith   $450.00
               Mary Smith

                         Total $450.00

What I'm getting is this:
12345    Joe Smith   $450.00
              Mary Smith

                       Total $900.00

I want to only add the amount once based on the account number only showing once.
Like I said I'm hiding the account number and same payment amount by using the Print When Option.

If there is a way to do it using the SQL Statement I'm open to that as well.
I'm currently joining the Payment Table with the single account number and payment amount to the name table where I find account number and match it up to all the names on the account.

Thanks,
Mark
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 350 total points
ID: 39176819
Report calculations count all detail lines independently on fact they are printed or hidden...

What is the Group expression in your report?

And how do you display following data?

12333     Oth Name   $450.00
12345     Joe Smith    $450.00
               Mary Smith

                         Total  $900.00

Does the Print when allow to display two equal amounts?

OK, that's not important now.

Th easiest way is to update data before printing:
Suppose each Payment record has some unique ID and this ID is in the final table. This unique ID is repeated when there are more account owners. Simple loop can clear the duplicate amount values:

SELECT YourReportTable   && this alias must be writable
lnLastID = -999999  && this must be some value which does not exist in the table
SCAN ALL
   IF YourReportTable.uniqueID <> m.lnLastID
      lnLastID =  YourReportTable.uniqueID
   ELSE
      REPLACE Amount WITH  0
   ENDIF
ENDSCAN

and the only change in your report is to suppress zero values printing. To implement this change by SQL command would be more complex.
0
 
LVL 41

Accepted Solution

by:
pcelba earned 350 total points
ID: 39176833
Another soultion is to use report variables...
It requires additional report group based on the payment unique ID if you don't have one already.

Create a report variable named e.g. LineNo and set its Calculation as Count and reset its value when the above group changes - it will contain line numbers inside each single payment:

12333     1   Oth Name   $450.00
12345     1   Joe Smith    $450.00
               2   Mary Smith

Create another variable named e.g. UniqPayment and its Value to store is
IIF(m.LineNo = 1, YourPaymentField, 0)

The effect will be same as if you would update your data in the SCAN loop. The first line will contain the payment amount and all additional lines will be zero.

And now you may calculate the total based on UniqPayment variable instead of your payments.
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 150 total points
ID: 39178323
I think you go wrong about aggregating and joining your data in the first place. This is not something you should solve by this idea "sum only visible amounts".

If you left join many levels of data and the amounts you later want to total are not at the outmost join level, you will have repeated amounts multiplied by the number of group elements.

Let's see for an example. Let's assume you have a report on last quarter orders and want to aggregate all order totals, but also join all order items for some reason, then the grand total would still result as the total of all orderitem amounts, but summing order totals, you get any order total multiplied by the number of order items.

You can easily change the display with the "don't print repeated values", but that can even give wrong results, if two successive mid groups (in the example case the orders) have a same value. You should think about report group bands. In case of reporting a list of orders with each order having a sublist of order items, you have three levels of grouping, the whole report is one grand total, then you have the level of orders in the middle, and the level of an order item.

You don't need to introdue a group for the single row order items, but for the orders. You don't need a group for the whole report, as you have the summary band, but you introduce the mid level group of orders by ordernumber or -id and only use the group header or footer band to print order totals and only sum those totals to the grand total.

That's how you deal with this, not with suppressing repeated values. That feature is just a kludge, like DISTINCT is a kludge for SQL instead of using group by. It's not, that I don't make use of it, but you have to know when it's an option, that applies, or just causes further trouble.

Bye, Olaf.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 350 total points
ID: 39178732
Olaf, why are you presenting example of bad use of orders and order items in the report whereas this question clearly stated what is the report subject?

The report output is defined and table relations are known so how would you create the output without "Print repeated values = No"?

The group based on payment ID was mentioned already and I suppose it is also used already.
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 150 total points
ID: 39180803
>The report output is defined and table relations are known
Is that so? I haven't seen any specification I could build upon, so I used a well know example of orders/orderitems.

Even if the report is already defined, what would it cost (in time) to redesign it correctly and make the right query or queries upfront? You don't get a flag or event, when VFP decides to suppress a repeated value, so unlike an expression you use in "print when", you can't sum visible amounts only by reacting to that, you have to do your calculations different or group different.

As far as I read it  smithmrk hasn't talked about a paymentid, but account number, and he said he joined data, not set relations. To me it's still unclear what exactly to advise, as I don't have any clues on queries ued so far, tables and fields. The specs are missing.

If you join names of account owners, of course that multiplies records with amounts. To sum afterwards is an error, of course. You should find another way to list all names as a sub group.

You can do staged calculations by defining a report variable with initial value = 0, value to store = amountfield,  calculation none, and reset by group and then  a second variable with initial value 0, value to store = firstvariable, calculation sum and reset by report. then you'd only sum up each group amount once.

It's easier to talk about specific tables, field names etc. Not only for me, but also to follow an explanation.

Bye, Olaf.
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 350 total points
ID: 39180893
The specs are there in the question already you just don't see them:
I'm currently joining the Payment Table with the single account number and payment amount to the name table where I find account number and match it up to all the names on the account.
In other words this means "table relations are known"... (not SET RELATION is used)

Yes, the PaymentID is mentioned a an assumption in my message only. Have you eve seen payments without unique ID? I would guess it is impossible because you couldn't decide about invalid data. Yes, you can create a subset of payments without uniqueID if you decide so.

Your answer brings more questions... Could you explain what is the pourpose of the "report variable with initial value = 0, value to store = amountfield,  calculation none, and reset by group"? What group do you mean here? What added value it brings comparing to the amountfield itself?

Also the second variable will behave differently than you describe.

So please be more specific when you know following table structures:

Payment:
PaymentID  int
AccountID  int
Amount  currency

Account:
AccountID  int
AccountNo  char(10)

AccOwner:
AccOwnerID  int
AccountID  int
Name char(20)
0
 

Author Closing Comment

by:smithmrk
ID: 39181267
Thanks Guys!
Both of you were very helpful and I ended up doing it this way:

Olaf_Doschke is right I've had issues in the past with "Print When"...however I decided to do it like this...

I setup two variables in the report and put logic behind it as pcelba suggested:
One called SeqNum and then I calculated the amount using this logic:
IIF(p1_seq_num=SeqNum,0,appl_amt)

My thought process was that I would grab the SeqNumber of the item and if it was the same as the previous one I would zero out the amount if the SeqNumber was different than the previous one I would add to the Amount.

I could have also done this in the SCAN as pcelba also suggested, but it seems to work this way as well in the report using the Report Variables.

Thanks to both of you and I gave some points to Olaf_Doschke for helping with the question!  I appreciate both of you taking the time to help me through my issue.

Until next time...there will surely be a next time!
Mark
0
 
LVL 41

Expert Comment

by:pcelba
ID: 39181354
You are welcome.

And yes, I have to confirm the SCAN works similar way as report variables in this case. Report variables just do the work on the fly.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
In this article, I will show you HOW TO: Suppress Configuration Issues and Warnings Alert displayed in Summary status for ESXi 6.5 after enabling SSH or ESXi Shell.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now