Grouping Help 1

[CoSpringsGuy]

I have a requirement to group data in a way I have never done before.

I have 2,000 records each containing a score. I need to group the data in equal groups of ten. the resulting data would need to look like this.

Score number of records
0 to 218 100
219 to 400 100
401 to 450 100
451 to 550 100
551 to 650 100
651 to 722 100
723 to 800 100
810 to 850 100
851 to 950 100
901 to 999 100

The score ranges would change each time the report is run for different groups of records depending upon where they fall in the scoring model.

Anyone think of an easy way (or hard way I guess) to accomplish this?

I'm using Crystal XI

Thanks in advance


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

Groups of 10 or 100?

-LB

 

[CoSpringsGuy]

equal groups of 10 percent. There could be well over 2,000 records or even less but i need the groups to contain 10 percent each of the total number of records AND the top group will be the records with higher scores down to the last 10 with lower scores. that part is easy i think if the are sorted correctly because I will just use min and max function on the score for each group.

good to see you by the way LB .. haven't logged on here in quite some time.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

my first example should have had groups of 200 since there were 2,000 records. Hopefully thats explained correctly.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

While you can group by intervals like that, it has to occur "whilereadingrecords", so that the problem becomes your desire to sort the records in descending order (a later pass) AND grouping by intervals like this. I'm not sure this is possible.

-LB

 

[CoSpringsGuy]

I considered writing SQL code to sort the data in descending order before it gets to Crystal but I am not sure that will solve my desire to group by equal percentage of accounts. I just cant come up with a way for Crystal to do this. The problem I keep running into is that I need to know the COUNT of total records but as soon as you start using that group functionality in a formula, it becomes impossible to group because Crystal groups in the first pass. Appreciate your thought on this. If something else pops in your head let me know... Thanks again

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

Another approach just occurred to me. If you don't need actual groups, but only the appearance of groups, I think you could insert a section that is conditionally suppressed except when it meets a criterion in order to create a cluster. But I am unclear on the overall look--if it is as you show above, then what is the point? Are you summarizing the records within each group in some way other than to name them by the beginning and end record of the group? I can't work on this right now, but let me know.

-LB

 

[CoSpringsGuy]

background on the project -

The score assigned to each record is based on forecast or potential revenue. I am trying to segment these into equal percentile of accounts so that managers and representatives can focus effort on the segments which will produce more revenue with less expenditure. The lower the score, in theory, the less potential revenue and the more costs incurred trying to generate revenue. The higher the score the higher the likelihood of generating revenue while using less resource.

This method of grouping scored data to predict and manage work flow is quickly becoming a standard in my industry and I am hoping that Crystal can help me monitor it. Personally it makes more sense to group the accounts into sections based on the score 0 to 100, 101 to 200 etc... Crystal allows me to do that very simply! But I'm not calling the shots.....



_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

looks like I came up with a solution that seems to work pretty good.... let me know if your itnerested and I will share

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

You should always post solutions so that other readers can benefit from the thread.

-LB

 

[CoSpringsGuy]

The biggest problem i was having was the ability to use the total count of records so that a formula could be created and a group created from that formula. So I created a command table and let the server count the records for me. The SQL command sent the count of selected records back on each record in essence giving me a variable I could use. Below is my SQL sample.

SELECT count(*) AS totalrecords, *
FROM SQLUser.SkTr_Scoring_Model SkTr_Scoring_Model
order by SkTr_Scoring_Model.Score_Value ASC

To get things working I didnt use a WHERE statement but for future I will and should get the same effects even if they are passed with parameters from Crystal.

I sorted the records in the SQL but I dont think I really needed to do that.

The next part was coming up with a formula that would give me 10 equal groups based on the count of records. Im certain there is a better formula for this but what I came up with works until I get the chance to try something different.

if (({Command.totalrecords}*.10)+({@counter}-({Command.totalrecords} mod 10)))
\
({Command.totalrecords}*.10)> 10
then 10
else
(({Command.totalrecords}*.10)+({@counter}-({Command.totalrecords} mod 10)))
\
({Command.totalrecords}*.10)

If someone wanted to troubleshoot that formula and come up with a better solution that would be great!





_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

What is the content of {@counter}? Were the records still in descending order after grouping?

-LB

 

[CoSpringsGuy]

{@counter}

whilereadingrecords;
Global NumberVar x;

x := x + 1

yes they were .. here were my results with details suppressed

score range # of records
1 to 219 204
219 to 304 204
305 to 382 205
383 to 450 205
451 to 522 205
522 to 582 205
582 to 653 204
653 to 730 204
730 to 810 204
810 to 968 203



_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

I think they do need to be sorted before reading records. Glad you got it to work, and thanks for explaining.

-LB