Subtotaling / totaling data that meet a criteria within a group 1

[MJV8198]

I have pulled data for items sold to all of our customers. I do a distinct count of SKUs sold to each customer. The customers are grouped by sales rep. I have a parameter that allows me to show only the customers that have purchased more than "X" sku. The report works fine except for the subtotal for the dollars and distinct count of customers that meet the criteria. Instead of only totaling and counting the customers that meet the criteria it totals all of the customers within that group regardless of their SKU count. so if a sales rep had 25 accounts but only 5 purchased more than "X" SKUs I would want the customer count to be 5 and the sales count to be the total for those 5 customers. I am currently getting a customer count of 25 and a total for all the customers.

Thank you for your help

 

[CoSpringsGuy]

How are you implementing the parameter? Selection criteria? Suppression?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[MJV8198]

In the Group Select Expert I have the following:

DistinctCount ({oeel1.shipprod}, {oeel1.custno}) >= {?SKU Count}

{?SKU Count} is the parameter that can be changed at run time

 

[CoSpringsGuy]

I see two groups .. one for custno (in group select) above and one for sales rep

Which group did you put your selection formula?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[MJV8198]

Please be a little patient with me, I am basically teaching myself crystal at this point and so I may not be fully understanding your question this is my record selection:


If I am understanding correctly - the selection formula I listed above is in the CustNo group

the report looks like this

Sales rep 1 SKU Count Sales $
Customer 1 50 2,500.00
Customer 2 53 2,700.00

Total for rep 2 5,200.00 this is what I want - the 2 is the count of the customers that meet the criteria
Total for rep 17 37,850.00 This is the total I am getting


Thank you for being patient

 

[CoSpringsGuy]

I was hoping someone else would jump in and help you with this because I know there is probably an eaiser way to accomplish this but since I started I feel obligated to see it through... SO SOMEONE ELSE JUMP IN ANYTIME! the solution I would use might be confusing if you are just learning Crystal but I will throw it out anyway. Group Selection does not actually deselect the records associated with the group so summary functions still see the records. I duplicated this exact issue with a table in my database before I posted this and here is how I fixed it.

Lets start over on your report and use an SQL Command table instead of how you are doing it now. This may be tricky and I will help modify it as best I can..
After starting a new report and in the database expert, click the plus sign next to your datasource. You will see an option called Add Command. Double click this to open the area where you will create your sql code. Here is where it could get a little tricky depending on the database you are using.(Please anyone more familiar with SQL chime in as I am not familiar with a lot of the different flavors) It should be something similar to the following:

SELECT DistinctCount(oeel1.shipprod) as skuqty, sum(oeel1.sales) as sales, oeel1.custno, oeel1.salesRep
FROM oeel1
Group by oeel1.custno

This should return four fields already grouped (counted and summed) for each customer. (NOTE: If a salesrep can share the same customer that will have to be modified a little by adding oeel1.salesRep to the beginning of the GroupBy clause)

Once we get this SQL working. (cross my fingers)
Create the same paramter as before and in selection record enter
skuqty > {?SKU Count}
Group by oeel1.salesRep

Place your fields in the details section. Right click and insert a SUM Summary to the group footer.

Run the report and see if you get the results you desire





_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[MJV8198]

Ok I understand where you are going but we may be stepping beyond my capabilities. However I am going to play with it a little and see where it gets me. Fundamentally I understand where we need to go. I should have mentioned this in my initial post, I am working on Crystal 2008 I also have a more complicated record select. In addition I am pulling from order line item data so I need to take into account item discounts, order stage etc when calculating sales. I am currently doing the math for the sales data within the report. If I use an SQL command to only pull the records for the accounts that meet the criteria the the data should the rest of the report may work properly. I have saved off my current report do do this testing. We are using a progress database. You have been very helpful but the complexity of the record select and sales calculation may be more than we can put together with my limited capability and I certainly don't want to take all your time so feel free to bail out anytime.

In taking some time and working with your suggestion as a basis is it possible to write an SQL Expression that will perform the count of customers and sum of sales and insert it into the report. to perform the summary? So on the sales rep group summary line I would perform a DistinctCount of customers that meet the criteria or Sum sales for customers that meet the criteria?

 

[CoSpringsGuy]

Let me know the fields, criteria and if there are other tables involved. On my iPhone now but will check back in when I get to work.

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[MJV8198]

Here is the complete record select I took this from the Record select formula editor. Some of the parameters are entered at runtime.

{arsc1.slsrepout} = {?Sales Rep Range} and
{oeel1.prodcat} = "045" and
{oeeh1.transtype} = "so" and
{oeeh1.enterdt} in {@Entered Date} to CurrentDate and
{oeel1.statustype} <> "c" and
{oeeh1.cono} = 1 and
{arsc1.salesterr} in ["0001", "0002", "0003", "0100", "0200", "0250", "0330", "0400"]

 

[CoSpringsGuy]

We can create SQL to accomplish that.. What field or fields link the two tables? Almost to work! Lol

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[MJV8198]

Oeeh1.cono innerjoin Oeel1.cono
OEEH1.Orderno innerjoin oeel1.orderno
OEEH1.Ordersuf innerjoin oeel1.ordersuf
oeeh1.cono innerjoin ARSC1.Cono
oeeh1.custno innerjoin arsc1.custno

all not enforced and =

there are other links to other tables but it is not dependent on the select

 

[CoSpringsGuy]

Your SQL would look something like this depending on the DB flavor but this is what works on mine.. Be sure and mention the DB you are working from and someone on here can probably help with syntax errors I might have..

SELECT DistinctCount(oeel1.shipprod) as skuqty, sum(oeel1.sales) as sales, oeel1.custno, oeel1.salesRep, Oeeh1.cono, OEEH1.Orderno, oeel1.orderno, OEEH1.Ordersuf, oeel1.ordersuf,ARSC1.Cono, arsc1.custno, arsc1.slsrepout, oeel1.prodcat, oeeh1.transtype,oeeh1.enterdt, arsc1.salesterr

FROM oeel1, Oeeh1, ARSC1


WHERE (Oeeh1.cono = Oeel1.cono) 
AND (OEEH1.Orderno = oeel1.orderno) 
AND (OEEH1.Ordersuf = oeel1.ordersuf) 
AND (oeeh1.cono = ARSC1.Cono) 
AND (oeeh1.custno = arsc1.custno) 
AND arsc1.slsrepout = (?Sales Rep Range) 
AND oeel1.prodcat = "045" 
AND oeeh1.transtype = "so" 
AND oeeh1.enterdt > (?Entered Date) 
AND eel1.statustype <> "c" and oeeh1.cono = 1 
AND arsc1.salesterr in ("0001", "0002", "0003", "0100", "0200", "0250", "0330", "0400")

Group by oeel1.custno

For (?Entered Date) and (?Sales Rep Range) you will need to create those.. In the dialogue box where you put this sql code, on the right hand side is where this is done. Click create, type the name and select the value type. place your cursor in the code where you need the parameter and then double click the parameter name to insert.

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[MJV8198]

Thank You very much I will test this out.

We have a progress database for anyone who may be able to help with syntax

 

[CoSpringsGuy]

one difference i see just browsing through an online reference

DistinctCount(oeel1.shipprod) as skuqty

would be

COUNT(DISTINCT oeel1.shipprod) as skuqty



_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[MJV8198]

I simplified your suggestion so I do not need to address the parameters while trying to get the basic script to work. I initially received an error 20005. When I researched this article

http://knowledgebase.progress.com/articles/Article/P11495

suggests adding some changes to the select command so now I have the following. The red font is what I changed I also changed the distinct count as you suggested. I am now receiving a new error 210056 but after researching I am not sure what I need to do. I will reach out to the progress programmers that handle the software because this is getting deeper into programming. If someone here has a solution that would be great. When I get it working completely I will post a solution

I really appreciate the help

SELECT COUNT(DISTINCT oeel1.shipprod) as skuqty, sum(oeel1.qtyship) as sales, oeel1.custno, oeel1.salesRep, Oeeh1.cono, OEEH1.Orderno, oeel1.orderno, OEEH1.Ordersuf, oeel1.ordersuf,ARSC1.Cono, arsc1.custno, arsc1.slsrepout, oeel1.prodcat, oeeh1.transtype,oeeh1.enterdt, arsc1.salesterr

Select * FROM pub "oeel1", "Oeeh1", "ARSC1"


WHERE (Oeeh1.cono = Oeel1.cono)
AND (OEEH1.Orderno = oeel1.orderno)
AND (OEEH1.Ordersuf = oeel1.ordersuf)
AND (oeeh1.cono = ARSC1.Cono)
AND (oeeh1.custno = arsc1.custno)
AND arsc1.slsrepout = 0114
AND oeel1.prodcat = "045"
AND oeeh1.transtype = "so"
AND oeeh1.enterdt >= 07/01/2012
AND eel1.statustype <> "c" and oeeh1.cono = 1
AND arsc1.salesterr in ("0001", "0002", "0003", "0100", "0200", "0250", "0330", "0400")

Group by oeel1.custno

 

[CoSpringsGuy]

you might try this forum

http://www.tek-tips.com/threadminder.cfm?pid=292&page=1

for help with syntax. Im a bit confused on the table references I see when I look at progress SQL references but I think you are getting closer.

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[lbass]

Try using running totals where you do a distinctcount of the customer ID (or in the second case, sum of sales), evaluate using a formula:

count({table.sku},{table.customer ID})>={?Parameter}

Reset on change of sales rep.

Place the running totals in the group footer for sales rep. This assumes you have a group#1 on sales rep, and a group#2 on customer ID.

-LB

 

[MJV8198]

Hello Ibass

Thank you for your suggestion. It worked!!

MV