Creating count from queried fields

[Former Member]

Post Author: John

CA Forum: Formula

Hello all. Newbie here to the forum and CR.

Running CR 9

I have a report that extracts certain data from the database. A field that displays "test code", is not part of the original search criteria. I have been asked to total "test code" but, I don't know how to count the records from the extracted data.

For example, I'm pulling in everyone with a last name that begins with a "J" and who came in the store from 01/01/07 - 01/31/07. I then list the data and also print their address, city, state, zip, and "test code". The "test code" is one of 3 possible codes. I want to have 1 running total for each of these 3.

My knowledge allows me to create a running total on the field but, the total is from ALL of the records that match.

So, if we had 45 people with "J" and 01/01/07 - 01/31/07, the running total will be 67 because it is finding all data sets that match it's code. It isn't pulling from the original search criteria of "J" and 01/01/07 - 01/31/07.

Any help is appreciated.

John

[Former Member]

Post Author: John

CA Forum: Formula

So.. here is the official fix.

2 formulas for each item you are totaling.

1st. place this code in the detail section.... where PRODUCT_ID is the field you are trying to total and .UNIT_NO is the field that you are only displaying once.

WhilePrintingRecords;numbervar a;If {PRODUCT_INVENTORY.PRODUCT_ID} = "FFP" and {PRODUCT_INVENTORY.UNIT_NO} <> Previous ({PRODUCT_INVENTORY.UNIT_NO})Thena := a + 1

2nd. place this code in the GF section..

whileprintingrecords;numbervar a;a

***Note... for multiple counts, you need to change the variable for each "count".

Hope this helps someone and thanks to everyone that pushed me in this direction!

John

[Former Member]

Post Author: V361

CA Forum: Formula

Whoops, sorry, wrong post....

[Former Member]

Post Author: V361

CA Forum: Formula

not sure about CR 9 I have CR XI. In your running total, can you evaluate using a formula, if so, you can add the formula to the running total, you should only get the sum of those numbers that meet the formula's criteria.

[Former Member]

Post Author: John

CA Forum: Formula

OK... well.. this is still a problem for me.

So, I have 6 instances of a sale and the report is suppressing 5 of those instances because they are all by the same person on the same day. So, when I put my running totals in, it is counting 6 of them instead of the 1 that is displayed. I have tried the "WhilePrintingRecords" formula and have tried every form of total that has been mentioned. None of them will count only what is displayed.

Anyone have any ideas on how to total only the printed instances?

Also, I placed a formula on the Detail level that said

WhilePrintingRecords;

NumberVar Count1;

If {ProductInventory.ProductID} = "FFP" Then

Count1 := Count1 + 1

This still gave me a count of all instances.

[Former Member]

Post Author: John

CA Forum: Formula

Ok... sorry for not responding sooner, I was on vacation last week and didn't want to think about this stuff.

I found the problem this morning. The person that wrote this is using a "Previous" formula. This allows him to list only 1 line for every 6 instances or however many there are.

That's why the report was saying there are 692 records but only displaying 50 or so.

Thank you all for your time and help. I appreciate it!

John

[Former Member]

Post Author: Jagan

CA Forum: Formula

But earlier you wrote "...it is not counting only the records I

display..." so, as Charliy suggested earlier, it seems you have some

suppression

going on somewhere - this would explain the 36 instead of 6. It could also indicate a problem with the way you are joining the tables. In the bottom right of the preview screen it says how many records are in the report - is this number what you'd expect? Add some extra record selection restrictions so that you'd only expect a few, or even one, record and see what results you get.

[Former Member]

Post Author: yangster

CA Forum: Formula

A running total runs along side the data so it should never be placed in a header or the detail section.It must always reside in a group footer, page footer or report footer.Your basic running total in crystal will initilize at the report header, evaluate at the detail level and display the value in the report footer.So for your generalized case you have a simple running total that starts a count at 0 to start the report.You running total should be setup like thisField to summarize -> Product_IDType of summary -> countEvaluate - using formula -> inside formula typed in (if product_id = 4344211 then true else false)Reset -> neverAnd it won't matter if you put it in the GF2, GF1, PF, or RF the value will always be 4. If your report spans multiple pages though you should not put the formula in the PF as it report the running total as of the page but keep on totaling to the next page.My suggestion to you would be to test your report with a manual running total to see exactly where your numbers counting is going astray.To do that create 3 formulas@init_count (place in report header)whileprintingrecords;numbervar a;a:= 0@eval_count (place at the detail level)whileprintingrecords;numbervar a;if product = 4344211 thena := a + 1@display_count (place in the report footer)whileprintingrecords;numbervar a;athis will show you exactly what is being counted for every single item in your reportnormally you'd use a manual running total to get a summary of a summary or to sum a value at a group level but this should help you decode what is causing you buggy datahope this helps

[Former Member]

Post Author: John

CA Forum: Formula

That doesn't work. It gives me an answer of 36 instead of 6.

That's what has me so confused because this is what I tried the first several times and nothing has seemed to work.

Thanks for your time.

[Former Member]

Post Author: Charliy

CA Forum: Formula

you would put a Running Total in the Report Footer that does a Count, with an Evaluate formula of {talbe.productid} = 4344211

[Former Member]

Post Author: John

CA Forum: Formula

Here is the best example I can give as I work for a hospital and I am not allowed to give patient data.

Store #1

1234556 (Account #)

Steven, Peter Bag of Rocks (Product ID) 4344211 (Amount) 4.50

Steven, Peter Bag of Salt 3433322 5.60

Steven, Peter Bag of Rocks 4344211 4.50

23497345

Peter, Steven Bag of Rocks 4344211 4.50

Peter, Steven Case of Beer 1325454 8.00

Peter, Steven Bag of Rocks 4344211 4.50

(At bottom of report) # of 4344211 Products = ? (This is where I need to put the total in.) (In the above example, the total should be 4)

In my current total... if I put the total in the detail section (Next to the Product ID)... I get a total of 1 for the first and then 7 for the second and so on. If I put the total in the GF2 section, I would get a total of 2. I'm assuming this is due to the # of account #'s. If you put the total in GF1... you get a total of 1 because it is counting the store. (Or so I think)

Hope this helps.

Thanks

[Former Member]

Post Author: yangster

CA Forum: Formula

a running total and a manual running total are not the same thingputting the running total field in the detail level doesn't make much sense to meI don't really understand what you mean by its incrementing 6 and 4can you post your example somewhere and your expected results

[Former Member]

Post Author: John

CA Forum: Formula

I think I have. I believe the code I used above is a manual running total.. seeing as how I built it in a formula field. But, there is a good chance I am WAY OFF!

I put the formula field in the detail level which gave me the 6 and 4 increments. When I put it in the GF2, it incremented by 1. (like it was counting the # of groupings and not the data.

So, for example.

Jeffs Store = GH1

Account 123456 = GH2

Name, Address, Product ID Purchased, Total Amount = Detail Level (2 instances of this account)

*I put the total in the Detail Level and got increments of 6 and 4. When I put it at the GF2 level, I got an increment of 1.

Don't know if any of this helps or not, but, I can't get anything to work.

Thanks

[Former Member]

Post Author: yangster

CA Forum: Formula

have you tried creating a manual running total instead of just a running totalit sounds like your evaluation formula is based on a group level not at the detail level to get the proper sum

[Former Member]

Post Author: John

CA Forum: Formula

OK.. I fount the help topic on that and I created a formula that looks like this...

WhilePrintingRecords;NumberVar FFPCount;

If {PRODUCT_INVENTORY.PRODUCT_ID} = "FFP" Then FFPCount := FFPCount + 1

But, here is the problem, it is not counting only the records I display, it is counting ever time {PRODUCT_INVENTORY.PRODUCT_ID} = "FFP" in the database.

The second instance of the code = FFP says it is a count of 7.

Thanks for getting me closer!

*EDIT: As a side note, I noticed that the count was incrementing by 6 or 4. Mostly 6. This leads me to believe it is counting all the lines in the detail section because I moved the running total to GF2 and it was incrementing by 1.

[Former Member]

Post Author: Jagan

CA Forum: Formula

Yep, see the help on Running Totals, Using Formula

[Former Member]

Post Author: John

CA Forum: Formula

Can you give me an example of "running totals emulators with formulas with variables". I'm not really sure what you mean by that.

Thanks

*Edit: Also, is there a way to program your own count in CR? In most of the programming languages I have used, you can write something like..

If string = "X" then

Count = Count + 1

End If

[Former Member]

Post Author: RobertoMS

CA Forum: Formula

Your description of the problem is not completely clear for me, but it seems that you may solve your problem using running totals emulators with formulas with variables. This technique is widely publicized in the net. It will only count "whileprintingrecords" (if you set it to do so) and any kind of particular condition you are able to write

Roberto

[Former Member]

Post Author: John

CA Forum: Formula

Well... I'm not sure if that is what I am doing or not. Below is the actual code from the Select Expert.

{ORDER_ITEM.ORDER_ITEM_STATUS_CD} = "C" and(({ORDER_ITEM.ITEM_TYPE_CD} = "D" OR ({ORDER_ITEM.ITEM_TYPE_CD} = "P") AND ({ORDER_PRODUCT_INVENTORY.ORDER_PRODUCT_INV_STAT_CD} = "T") or {ORDER_PRODUCT_INVENTORY.ORDER_PRODUCT_INV_STAT_CD} = "I")) and{ORDER_PRODUCT_INVENTORY.ISSUE_DATETIME} in lastfullmonth andLEFT({ORDERS.ORDER_LOCATION_ID},2) = {?Facility}

The field I am trying to generate a running total on is (PRODUCT_ID}. As you can see, it is not part of my select criteria. I am trying to generate a running total from my selection criteria.

Also, I'm grouping on {ORDERS.ORDER_LOCATION_ID} and another field not listed above.

Don't know if this will help or not

Thanks for your time.

John

[Former Member]

Post Author: Charliy

CA Forum: Formula

Are these additional search criteria being used in Record Selection? It sounds like maybe your record selection is allowing in more records than you want and you're possibly print suppressing some of them. If that's the case, you need to add the print suppress formula (actually the opposite of it) to the Evaluate formula for the Running Total.

So if your Select brings in records in the store between the specified dates, and the print suppress is for first letter not J, then the Running Total evaluate formula needs to include a test for the first letter = 'J'

[Former Member]

Post Author: John

CA Forum: Formula

Thanks for the response Charliy. That's what I did and it doesn't work. The output for that is 36. (number of instances). However, in the actual output, there are only 6 instances. So, it is giving me a "correct" count from the database, but, I need a "correct" count from the data that I am going to output.

Make sense?

Thanks again!

John

[Former Member]

Post Author: Charliy

CA Forum: Formula

If you're using Running Total then you're more than halfway there.

To only count the ones with the test code, edit your running total and click the box that say Evaluate on Formula, the box next the on you click will turn red, click on the red box and you'll be in a formula editor. create a formula like: {table.codefield} = 'testcodevalue" now clidk the Save and close button and you're done.

You might also want to consider whether you want Count or Distinct Count. If there could be more than one record per person, and your counting on a customer id, then Count is the number of records, while Distinct Count is the number of customers.