Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: grouping and counting Post Reply Post New Topic
Author Message
AdamField
Groupie
Groupie


Joined: 04 Jun 2009
Online Status: Offline
Posts: 88
Quote AdamField Replybullet Topic: grouping and counting
    Posted: 04 Jun 2009 at 12:50am
Hey all,
 
I'm quite new to CR and nobody here in the company that is a expert on this stuff so i'm hoping i can get some hints with a report i have to make.
 
what we have is a SQL db with a couple of fields
 
Date           /      artikel code    /      invoice number / groep   / customer number
 
2009/1/1        NOTHP-302512       F090100001             NOT          12548
2009/1/1        TASBEL301518       F090100001             TAS            32584
2009/1/1        HDDMAX105684      F090100002             HDD          12548
 
basicly what is in this db is all the sell my shop did sorted by invoice number (it has about 20 000 lines)
It's like a big list of all the invoice details (what articles the customers bought)
 
Now they want me to make a cross selling report
so they see for a customer who bought a notebook(NOT) who also bought for example a bag for it (TAS) or a antivirus ....
 
what i made now (and i guess is wrong :p) is 2 counters on record niveau
1 that gives 1 to a formula if the line is from the group notebook
another 1 that gives 1 to another formula if it's from one of the groups that i count in cross selling (anti virus / bags / ...)
 
then i group on invoice number with as formula that the sum of those counters both have to be higher as 1
 
that gives me a list of all the invoices that both have a notebook and one the cross selling groups
 
now i need to count those line seperate but when i do a count on for example the notebooks it gives me a total of all the notebooks not only the ones that also have a cross selling article.
 
I guess i'm starting it the wrong way but hoping on some info here
 
ps: i'm realy new so no clue how to make stuff like dynamic variables and cross record counters and other cool stuff :(
 
Tnx for the help already !!!
 
Greetings Adam Field
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 04 Jun 2009 at 2:19pm
To start with you may want to think about:
1.You gave one example of cross selling. Does this report need to handle that one option  or multiple options of cross selling?
2. Do you need all of the records for this report or can you filter (probably based on answer to question 1).
These two answers would decide how I would approach the report.
If you really are only interested in a single process of if a "NOT" was sold with any related items on that same inventory #...
Use the select criteria to only include records where GROEP field is in your cross selling list.
group on inventory # (group level 1).
Create a formula as "NotebookCount".
if GROEP="NOT" then 1 else 0.
Use a sum function on that formula field at group level1.
Create another formula field as "AccessoryCount"
if GROEP="NOT" then 0 else 1.
Use a sum function on that formula field at group level1.
Do a select on these 2 summay fields.
Click on select expert
Click on Show formula
click on the Group Select toggle button.
Click on the formula editor
Create a select formula grabbing your summary fields from the report  Fields and making each of the be >0. It will end up looking something like:
and
 
This group select statement will filter out all the grouped inventory records where there is not at least one notebook and at least one accessory in that inventory group.
 


Edited by DBlank - 04 Jun 2009 at 2:38pm
IP IP Logged
AdamField
Groupie
Groupie


Joined: 04 Jun 2009
Online Status: Offline
Posts: 88
Quote AdamField Replybullet Posted: 05 Jun 2009 at 1:03am
Hey Dblank,
 
Tnx already for the reply this realy sets me in the good direction
now for your questions:
1:  at the moment i'm only planning on filtering on notebooks but in the future i would like to expand this to other categories (printers : cross sell with cardtridge / paper / kabel's )
2: my report now has formula's on both the record as on the group
record
{ANT-hisarel.h_datum} > {?StartDatum} and {ANT-hisarel.h_datum} < {?StopDatum} and {ANT-hisarel.h_relnum} <> [3 , 25229 , 10229 , 13590]
 
where h_datum the date of the invoice is and start and stop datum give the start and the stop of the periode i want to check (i don't want to check last 4-5 years of sells just a given periode i let the user enter
the h_relnumer are customernumbers i don't count (intercompany sells )
 
 
Then on the group level i have:
IF {?Cross Verkoop op notebook aanwezig} = true
THEN Sum ({@TelCross}, {ANT-hisarel.h_docnr})>=1 and Sum ({@TelNotebook}, {ANT-hisarel.h_docnr}) >=1
ELSE Sum ({@TelCross}, {ANT-hisarel.h_docnr})=0 and Sum ({@TelNotebook}, {ANT-hisarel.h_docnr}) >=1
 
the first parameter is meant to split the report up in
cross sell and not cross sell (one gives me all the records that are cross sell the second one all the records NOT cross sell and the information of our internal sales guy so we know who needs more training on cross selling and who is doing good .
 
now on the rest of your answer:
i don't understand how this will count my total
right now i greyed out the detail and grouped per invoice number and with the formula on group i get a nice list of all the cross sell invoices
problem is that my grand total (sum of telnotebook ) both the cross sells counts as the not cross sells
i'm lookign for a extra foruma that only counts groups displayed not not both (displayed and not displayed)
 
tnx again for the time !!
 
Adam
IP IP Logged
AdamField
Groupie
Groupie


Joined: 04 Jun 2009
Online Status: Offline
Posts: 88
Quote AdamField Replybullet Posted: 05 Jun 2009 at 1:05am
gave it a try with the group select thing but don't realy understand what you mean with that or how to make it
 
also i'm working with a dutch CR and they realy didn't make it easy in the translations :p
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 05 Jun 2009 at 7:10am

My suggestion was a way to filter all of your data down to only including records that meet your conditions. From there it should be pretty simple to do your counts about ONLY data that is notebooks and accessories sold together. If you are hoping to analyze a wider scope I would do this in a SQL view or stored procedure to massage the data and identify these records in new column data somehow so that you can use that flagging in your overall reporting. My approach above was a way to use existing crystal functionality to pare your data down so you can do counts or sums or distinct counts, etc. on what is left. It really is just up to you how you and what you want to count or sum. You can get a total # of Notebooks sold using a running total to conditionally count the GROEP field where it = NOT, or get a counto f customers by doing a distinct count on customer#. Or a count of the GROEP field conditionally as <> NOT to get a total # of all accessories sold. YOu could use crosstabs to display a chart of this data grouped by product and month. Really up to your needs.

Hope this clarifies the approach I suggested.
IP IP Logged
AdamField
Groupie
Groupie


Joined: 04 Jun 2009
Online Status: Offline
Posts: 88
Quote AdamField Replybullet Posted: 08 Jun 2009 at 8:17am
Hey DBlank,
 
I got this part compleet working and it gives me a list of all the invoices with both a notebook and somthing else from my list.
 
For Some reason i can't count the sum fields that i use on the group lvl and when i do a count on the notebook it gives me the grand total of both the ones showing and the ones that i filtered out on the group lvl.
I think i need to count somthing from the group lvl in stead as somthing from the detail lvl.
any idees ?
 
Greetings
Adam
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Jun 2009 at 11:04am

You won't be able to do a Summary function on a SUmmary function (e.g. a Count of a SUM), however now that you have this filtered dowm to the records you need you can do summaries of the data as it is left. You can do a DistinctCount of the Customer# fieldto find outr how many differnt customers bought these combos. You can use a Running total as a count with a conditional  formula to count the number of notebookes. You could add a Crostab in you footer to show counts of all the diiferent accessories and notebooks. Lots of options now that you have pared your data down to only records that you want to evaluate.

You may want to use all Running Totals. I am not sure if the Summary function of a Distinct COunt will evaluate before or after the filter at the group level.
DOes this help clarify differnt options for you?


Edited by DBlank - 08 Jun 2009 at 11:06am
IP IP Logged
AdamField
Groupie
Groupie


Joined: 04 Jun 2009
Online Status: Offline
Posts: 88
Quote AdamField Replybullet Posted: 09 Jun 2009 at 12:46am
Hey DBlank,
 
Exactly what i needed to know
I fixed it now with a shared numberVar like you repleyed in another topic this works perfect for this report.
 
I thank you for the time you took to answer all my questions
 
 
Greetings Adam Field
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.