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

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