• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 519
  • Last Modified:

Count rows with different values in crystal report

Dear experts,
I want to make a report that will display an item sale details. This report will show the item and which warehouse its being held and how many stocks are there in each warehouses. There's also a sale status for each of the item (Sold or Not Sold). I grouped the report according to warehouse and sell status. However, i want to know for each warehouse, how many of this item has been sold and how many are still NOT SOLD. How am i suppose to get around to do it? any formula that might help? Thanks experts..
0
Slythie
Asked:
Slythie
  • 3
1 Solution
 
BALMUKUND KESHAVCommented:
First of all let us know your database structure and how data is being stored, is there any field which has calculated stock lying. If not  you will have to calculate the stock item wise, it includes opening stock, purchased during the period, sold during the period, and also credit note raised, all you will have to calculate/consider.

Regards,

Bm Keshav


0
 
SlythieAuthor Commented:
this table is made specifically for 1 item. The item is called "Starter Kit". In this table, it will store:
- sk_id = got this data from invoice number from where the starter kit is being purchased
- sk_supplier = this data is taken from the supplier who supplies the starter kit
- sk_warehouse = this data is to store the warehouse ID where the starter kit is being stored
- sk_member_code = this data is a member code generated for each starter kit
- sk_status = this data is to show whether this particular starter kit has been sold or not.

Example:
warehouse 1 bought 3 starter kits from supplier 02. When the supplier sent that 3 starter kits, they will also provide unique member codes for each of the 3 starter kits. The table will look like this:

sk_id                sk_supplier        sk_warehouse          sk_member_code         sk_status
sup02-w01       sup02                  w01                          mem001                         NOT SOLD
sup02-w01       sup02                  w01                          mem002                         NOT SOLD
sup02-w01       sup02                  w01                          mem003                         SOLD

whenever that warehouse sell the starter kit to that particular member in member_code, the status will change to SOLD. Now how am i suppose to count the number of NOT SOLD and SOLD in crystal report? thanks experts
0
 
XFishXCommented:
Use the "Running Total" feature in Crystal Reports.

Perform a running count where 'sk_status' = SOLD
0
 
SlythieAuthor Commented:
do i need a formula to do that? coz, when i use the running total, it will count all the rows in the field and am not able to distinguish the difference between SOLD and NOT SOLD
0
 
SlythieAuthor Commented:
oh nvm... i got it.. thx u for ur help expert
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now