How to apply FAQ 149-1220 1

[monkeylizard]

I've just read Mr. Hammerman's FAQ 149-1220. I understand what it's doing, but how do I use it?

I've never built an array in Crystal before, so start from the beginning if you can.

I'm building a report that will look sort of like this bad ascii example below:

****************
                   NOV'05   DEC'05   JAN'06 ----- OCT'06
Employee1 
   Units Sold        5        8        0            6
   Units Returned    2        1        0            4
Employee2
   Units Sold        12       15       2            9
   Units Returned    5        3        4            1

********************

The "-------" is just a space holder. I really want all 12 months. the user will be providing an End Date parameter so I can get the Month and Year from that to begin the calcs.

The grouping is not a problem, but the horizontal columns by a rolling 12 is giving me fits.

So, how do I make the array using the code in the FAQ and how do I apply it? How do I call that array and pass in the End Date provided as a parm to get each of the 12 prior months/year periods?

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[synapsevampire]

I wouldn't use an array for a rolling 12 month, perhaps you should post requesting assistance with your requirements rather than pigeon holing people into using a specific methodology, you may find that there are other ways that suit the requirement better.

For instance you could group by employee and create Running Totals, and place the date range in the evaluate->use a formula section

The criteria for a rolling 12 months data can be handled in a record selection formula of:

{table.date} < cdate(year(currentdate),month(currentdate),1)
and
{table.date} >= dateadd("m",-13,cdate(year(currentdate),month(currentdate),1))

Then each running total would have something like:

first month:
{table.date} < dateadd("m",-12,cdate(year(currentdate),month(currentdate),1))
and
{table.date} >= dateadd("m",-13,cdate(year(currentdate),month(currentdate),1))

second month:
{table.date} < dateadd("m",-11,cdate(year(currentdate),month(currentdate),1))
and
{table.date} >= dateadd("m",-12,cdate(year(currentdate),month(currentdate),1))

etc.

-k

 

[kenhamady]

This actually looks like it could work as a cross-tab which would make it really easy.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[monkeylizard]

Thanks to both of you. SV, I had seen the formulas you posted and they looked good, but the FAQ's array just looked very efficient and easier.

The cross-tab looks like it could work well, but I'll need some assitance in creating it properly as I've never used it before. I've made one using the wizard. It generally has the layout that I want so I think this method will work if you fine people can help with a few things.

1. I have the columns grouped into months, now how do I get it to only display the rolling 12 based on a date parm? Is this where I use SV's formulas? Can I get it to show the Month name instead of the Date of the first day of the month?

2. The rows are grouping properly by employee, but I actually have a few values that make up "Sold" and "Returned". An S1, S2, or S3 record is "Sold" and R1, and R2 are "Returned". I created @Sold and @Returned formulas to group these together (IF it's Sx then 'Sold' etc). I have them added in the Cross-Tab GUI as Rows. The numeric values show up properly in the columns, but the row names are breaking into 2 different rows, so they aren't lining up right.

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[kenhamady]

1) It will show whatever months are included in the report so if you put in criteria to include a 12 month period it will show those months in the Xtab. If you right click on the column heading you should be able to format the date to be the month name, or better the month and year.

2) Use one formula instead of 2 and make that your group field. It would be something like:

If {code} startswith "S" then "Sold" else "Returned"

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[monkeylizard]

Very cool.

One more then. Instead of totals at the bottom of each cell (as in Sold + Returned is what it's doing now), I need that to show the Returned/Sold. Do I need to make a new @Sold formula and an @Returned formula, don't place them on the report, but use them in a 3rd formula for @Productivity? This is easy to do, but how do I get it in the cross-tab in place of the TOTAL and not as a new cross-tab row? I want the lable "Productivity" to show directly below "Units Sold" and "Units Returned"

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[monkeylizard]

This is my Record Selection formula. ?vbEnd is the user supplied date where I want to go back from the specified number of months. I'm doing this in the antique Crystal 6.5 and "dateadd" does not turn blue in my formula editor. Is there another function that can do this?

{uv_RESERVE_PRODUCTIVITY.INSERT_DT} >=  dateadd("m",-13,cdate(year({?vbEnd}),month({?vbEnd}),1))

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[kenhamady]

There is a formula on my website's formulas page that shows you how to subtract months without the DateAdd function. The ratio is trickier since crosst-tabs don't directly do this type of calculation. However see the following article for one method:

http://kenhamady.com/news0407.shtml

See technique number 1 since I assume returns are a subset of sales.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[monkeylizard]

Ken, the page linked has the techniques on how to do the productivity as a ratio of sales to returns (helpful, thank you), but does not show anything that I see on date formulas w/o the DateAdd function. Did you mean to link a different issue of your newsletter for that?

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[kenhamady]

See my Formulas page for adding months - formula #5

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[monkeylizard]

Thanks for the great page Ken. - Have a star.

OK, just installed Crystal XI and I already like it.
I was able to setup Technique#1 for my percentages, but I'm getting the same value in every instance of the weighted average formula. I always get 84.98% in each cell.

Here are the formulas:

Formula1:
if {uv_RESERVE_PRODUCTIVITY.ACTION_TYPE} = 'S1' then 1 else
if {uv_RESERVE_PRODUCTIVITY.ACTION_TYPE} = 'S2' then 1 else
if {uv_RESERVE_PRODUCTIVITY.ACTION_TYPE} = 'S3' then 1 else
0

Formula2:
if {uv_RESERVE_PRODUCTIVITY.ACTION_TYPE} = 'R1' then 1 else
if {uv_RESERVE_PRODUCTIVITY.ACTION_TYPE} = 'R2' then 1 else
0

Formula3:
if SUM({@Formula1}) > 0 then
(SUM({@Formula2})/SUM({@Formula1}))*100
else
0

All three are in the Summarized Fields section.
1 and 2 are Sums and 3 is a weighted average with 1 as the weighting.

If I don't have the SUMs in Formula3, then the weighted average of it returns zero in every cell.

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[monkeylizard]

Can I also get the rows to be labeled like in my OP with what each row is?

Units Sold
Units Returned
Net Sales <---- This is the weighted percentage formula.


Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[monkeylizard]

OK, took out Formula1 and Formula from the Summarized Field List and added back in the formula that hass all five IF statements in one. We'll call this Formula4.

With just Formula 4 in the Summarized Field list, the cross-tab looks good. I have my row labels and it's great, I'm just missing the third row per salesman that shows the NetSales percentage. When I add Formula 3 back into the Summarized Fields list, it puts the percentage under both the S types and the R types and it's always 88.42% for every cell. I only want it one time per Salesman.

Salesman
Sales
Returns
NetSales

I'm getting this:
Salesman
Sales
NetSales (wrong value here)
Returns
NetSales (same wrong value here too)

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[kenhamady]

You don't need formula 4. That was my mistake. And after reading the technique again you should not have the second row field in the cross-tab. Put formulas 1-3 in as summarized fields. Formula 3 should not be using subtotals, just the fields themselves.

There is a chance that this technique won't work for you because the returns and sales are separate records, which isn't the example I tested. If all else fails you could use the 2nd technique. Just much more work.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/