Group Sort issue

[krpurcel]

In Crystal Reports 2008 I am taking Helpdesk Tickets for YTD and grouping the tickets by category. I am summing the tickets for yesterday and the tickets for the rest of the year separately, then calculating each category’s % of the total tickets for yesterday or the remainder of the year. Finally I am calculating the delta between each category’s % for yesterday and the same category’s % for the remainder of the year. The end product looks like this:

CATEGORY DELTA CALLS YESTERDAY CALLS REST OF YEAR
STATUS 0.9% 46 3.6% 7200 2.7%
TELECOM -5.2% 32 2.5% 2085 7.7%
SOFTWARE 0.7% 27 2.1% 3923 1.5%

Now that I have this in place, I want to sort by DELTA, descending, so we can see which categories of yesterday’s tickets had the greatest variance. My problem is that at the group level, DELTA is not available in the group sort expert. The only item available in the group sort expert are these calculations:
Sum of @Calls Yesterday
Sum of @Calls Remainder of Year
Percentage of Sum of @Calls Yesterday
Percentage of Sum of @Calls Remainder of Year

I don’t know how to do this. Can someone point me down the right path?
Thanks as always.

 

[lbass]

Take a look at the SQL expression method in thread767-1489425. I'm not familiar with CR2008, but in previous versions, the only way to do this is by eliminating the summaries from the percentage calculation by replacing them with SQL expressions which are summaries that behave like fields, i.e., summaries can in turn be inserted on them which is a prerequisite to activating the group sort feature.

-LB

 

[krpurcel]

Thanks LBass. I'll check out that thread.

 

[krpurcel]

I have tried to do this a new way, and am confused by the results. I now have an SQL query in ADD COMMAND that brings in the call type counts for 2009 and 2010 YTD. So the add command brings in 3 fields: SCIM (the call type) YESTERDAY (count of each call type from yesterday) and LASTYEAR (count of each call type from last year.)

These three fields are on my detail line. Next I do a grand total summary for both YESTERDAY and LASTYEAR to get the total records for those time frames. I then add two % calculation fields (again on the detail line) dividing the count of calls for each call type by the grand total of calls for each period, ending up with the following:

CALL TYPE CALLSYESTER %YESTER CALLSLASTYEAR %LASTYR
cso~signon 51 2.81% 19,526 6.16%

Finally I subtract the % from YESTERDAY from the % for LASTYEAR to determine the DELTA ( in the example above -3.35%). My final objective is to be able to sort this descending by DELTA.

I have done it this way because my initial efforts (see original post in this thread) had the DELTA at the GROUP level and I couldn't sort by it.

Now everything, the three fields (SCIM, YESTERDAY and LASTYEAR) and the three calculated fields (CALL % YESTERDAY, CALL % LASTYEAR and DELTA) are on the detail line.

I was shocked after doing this when I went to the record sort expert intending to sort by DELTA, only to find that the three calculated fields (CALL % YESTERDAY, CALL % LASTYEAR and DELTA)are not available. The only thing listed under record sort expert are the three fields being brought in by the add command (SCIM, YESTERDAY and LASTYEAR).

I have looked at other reports I have built and in those reports both the fields and the formulas and available in record sort expert. I don't understand why in this report none of the formulas appear in record sort expert.

Can someone shed some light on this? Why is it happening and is there a way to correct it?

Thanks!

 

[lbass]

Please show the content of your calculation formula.

-LB

 

[CR4Reid]

I used a sample data and was able to come up with a Crosstab solution. I'm not sure if that's what you're looking for.

I can't seem to copy/paste images but here's what I have:

Delta y/day rest/year
TOTAL 0.15 105 13,208
SOFTWARE -0.04 27 3,923
STATUS -0.11 46 7,200
TELECOM 0.15 32 2,085

I used a "maximum" summary for the Delta calc. Hence "0.15" as its Total amount.

Is this close to what you're looking for? Or...

CR4R

 

[krpurcel]

Formulas are:
1. CALL PERCENTAGE LAST YEAR = {Command.YEARLY}/Sum({Command.YEARLY}
2. CALL PERCENTAGE YESTERDAY = {Command.YESTERDAY}/Sum({Command.YESTERDAY}
3. DELTA =
whileprintingrecords;
{@CALL PERCENTAGE YESTERDAY}-{@CALL PERCENTAGE LAST YEAR}

I can accept that whileprintingrecords might cause the formula to be unavailable, but I don't understand why the other two formulas are not available. They are very basic calculations of fields in the detail section.

CR4, thanks for that idea. I hadn't thought about doing it as a cross tab and will try that. Thank you.

LB, I still want to understand why those formulas are not available to me as part of my continuing education.

Thanks to you both for your replies!

 

[lbass]

You can do a record sort on a formula that uses "whileprintingrecords" (which is unnecessary in your formula anyway) or if it contains a summary. You should create that summary in the command as well.

By the way, I wasn't suggesting using a command (although you can do it that way). I was suggesting creating the summaries in SQL expressions.

-LB

 

[krpurcel]

LB,

lol, sorry I didn't understand. The only way I know to enter SQL is through ADD COMMAND. Are you saying I should be writing the formulas in the record selection section so Crystal converts them to SQL when it passes the request to the DB? Could you give me a simple illustration of what you mean?

Thanks as always. You have greatly increased my Crystal knowledge and ability.

K

 

[lbass]

If you don't use a command in your report, you will see an option for SQL expression in the field explorer.

Can you show the content of the command(s) you used to create the summaries?

-LB

 

[krpurcel]

LB,

Here's the add command:

SELECT b.SCIM, a.YESTERDAY, b.YEARLY FROM

(Select SC.INCIDENTSM1.SUBCATEGORY ||'~'|| SC.INCIDENTSM1.PRODUCT_TYPE as
SCIM , Count(*) as YESTERDAY

FROM sc.INCIDENTSM1
WHERE SC.INCIDENTSM1.ENTRY_POINT=11
and
"INCIDENTSM1"."OPEN_TIME" between new_time(TRUNC(current_date-
1),'CST','GMT') and new_time(TRUNC(current_date),'CST','GMT')
GROUP BY SC.INCIDENTSM1.SUBCATEGORY ,SC.INCIDENTSM1.PRODUCT_TYPE ) a,

(Select SC.INCIDENTSM1.SUBCATEGORY ||'~'|| SC.INCIDENTSM1.PRODUCT_TYPE
as SCIM , Count(*) as YEARLY

FROM sc.INCIDENTSM1
WHERE SC.INCIDENTSM1.ENTRY_POINT=11
and
"INCIDENTSM1"."OPEN_TIME" between new_time(to_date ('01/01/2009
23:59:59','mm/dd/yyyy HH24:MI:SS'),'CDT','GMT') and new_time(to_date
('12/31/2009 23:59:59','mm/dd/yyyy HH24:MI:SS'),'CDT','GMT')
GROUP BY SC.INCIDENTSM1.SUBCATEGORY ,SC.INCIDENTSM1.PRODUCT_TYPE ) b

where b.SCIM = a.SCIM (+)

Thanks again!

K

 

[lbass]

I guess you will need to do another command that does the summaries without the group conditions so that you can return a value that is treated like a database field instead of a summary.

-LB

 

[krpurcel]

LB,

Took me awhile to get back to this, but you were correct as always. Once I added the summaries as part of the SQL, I was able to create the Delta and sort by it with no problem.

Just wanted to confirm it worked as say thanks again.

 

[krpurcel]

OK, now I need some help understanding the difference between using SQL in an ADD COMMAND versus using an SQL Expression in the formula editor.

This SQL works fine in an ADD COMMAND:
SELECT a.ASSIGNMENT, a.OPEN_TIME, a.CLOSE_TIME, a.NUMBERPRGN, a.CLOSED_BY,
a.FLAG
FROM SC.PROBSUMMARYM1 a
WHERE a.FLAG = 'f'

However this does not work as an SQL expression in the formula editor. It generates an error saying "missing expression".

If I try to build the SQL using the formula editor I end up with:

SELECT
"PROBSUMMARYM1"."ASSIGNMENT","PROBSUMMARYM1"."OPEN_TIME","PROBSUMMARYM1"."C
LOSE_TIME","PROBSUMMARYM1"."NUMBERPRGN","PROBSUMMARYM1"."CLOSED_BY","PROBSU
MMARYM1"."FLAG"
FROM "SC"."PROBSUMMARYM1"
WHERE "SC"."PROBSUMMARYM1" "FLAG" = 'f'

("SC" is the first part of the full name of the DB. Using just PROBSUMMARYM1 didn't work, so I tried using the full name.)

This also generates the message that there is a missing expression.

Can someone point out my issue? I am not figuring out the correct syntax in formula editor.

I am pretty new to using SQL in Crystal. I understand I want to use SQL to perform calcs at the server end to improve performance. I don't understand the differences between using ADD COMMAND or using SQL expressions in the formula editor. Do people use SQL in the formula editor primarily for initial Record Selection? Or do they use Crystal's Record Selection and then add SQL expressions to manipulate the data?

I learned here in the forum that you can't use ADD COMMAND to pull from two different DBs. If you select tables from multiple DBs in the Database Expert, can you then manipulate them both with an SQL expression from the formula editor?

Thanks for helping!

 

[lbass]

SQL expressions (in the field explorer) access the database directly and are intended for converting fields (instead of using formulas) to be used in selection criteria so that they pass to the SQL query for the report or to return summary values. SQL expressions return one value or if you create them with an alias group, they will return one value per group. You cannot return a set of records with them--commands should be used for that purpose. When using a SQL expression to return a summary, the expression must be enclosed in parentheses. Also note that SQL expressions cannot make use of parameters, but commands can.

In general, you should write one command and then use it as the entire datasource for your report, instead of linking multiple commands to each other or to other tables, since this kind of linking occurs locally and will slow your report.

You can read about SQL expressions in the Help section. There is also information on commands.

-LB

 

[krpurcel]

Thanks for the info LB! I'll check out the info in the Help section as well.

Thanks again!