Crystal Reports 2011 (Formula assistance)

For those of you who have helped with my previous issues..  This is a continuation..  I ran into a road block..

I've got the following pulling for current month which returns all records in 3/2012

if Month({NC_Call_Report_Stats.Dates}) = Month(DateAdd ("m", 0, CurrentDate)) and  
year({NC_Call_Report_Stats.Dates}) = Year(DateAdd ("y", 0, Currentdate))
and {NC_Call_Report_Stats.GroupID} = {?Group}
then ({NC_Call_Report_Stats.BaseID})

Then for Previous Month I've got which pulls all records in 2/2012

if Month({NC_Call_Report_Stats.Dates}) = Month(DateAdd ("m", -1, CurrentDate)) and  
year({NC_Call_Report_Stats.Dates}) = Year(DateAdd ("y", 0, Currentdate))
and {NC_Call_Report_Stats.GroupID} = {?Group}
then ({NC_Call_Report_Stats.BaseID})

Now I'm on December and need to pull all records for 12/2011 and at some point when the end user runs this report they'll be looking for 12/2012 so I don't want to lock this in..  How can I make this formual so it isn't necessarily hard coded yet more flexible so it will perform the ROLLING previous 13 which I'm trying SOOOOOOO hard to come up with...??

Currently I've got this as my display:

March            SUM of distinct BASEID's for current month/year
February        SUM of distinct BASEID's for current month/year
January          SUM of distinct BASEID's for current month/year
December     This is where I get STUCK..
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Need a solution that works regardless of the month.

Based on how you are doing it, it is easy to fix but the fix won't work when it gets to April.

What information is the report trying to show?

Will a crosstab work?

wshcraft70Author Commented:
Yes, I've just learned that..  I removed the year when it came to December and have my data..  But moved forward to thinking about the April issue you just pointed out..  

I've tried a cross tabs report..  However, as you probably know, this is another report using the same CALL CENTER files that you've seen in the past few days..  There are a lot of additional calculations and I know that with a Crosstabs, the user could enter the 13 months they want to view but I'm running into great difficulties creating this crosstabs report with all the additional calculations that need to occur..

Attached is what I need to show..

MTD      Calls      ABA      % ABA      Avg Wait      Avg Talk
March      3459      51      1%      12.28      213.08
Feb      4300      89      2%      12.77      215.34
Jan      3972      41      1%      12.67      212.43
Dec      4513      96      2%      15.20      219.37
Nov      4845      94      2%      16.38      223.08
Oct      4656      109      2%      15.62      178.55
Sept      3942      116      3%      10.43      183.50
Aug      4926      155      3%      12.47      187.40
July      4553      118      3%      11.50      189.90
Jun      4809      184      4%      15.60      200.90
May      5555      208      4%      15.90      187.80
April      3778      110      3%      13.40      199.00
March      9999      999      9%      999.00      999.00
Rolling 13       442.3      114.25      3%      13.83      200.37
I don't know what the various columns are.  Are they calculated fields or fields from the database.  Cross tabs work best with database fields or formulas that can be summarized since the cells are expected to be summary values.

What is the raw data?

Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

wshcraft70Author Commented:
All those columns are calculations based on the raw data..  I could attach a spreadsheet or an access database...  There is nothing confidential about our company so no issue there..  Currently it is all in a SQL database..
Either would work.  Attach the report as well if you want.

wshcraft70Author Commented:
Which version would you prefer for the database?  Access or Excel?  I could possibly export the SQL as well..
Access is a bit easier to use in Crystal

A cross-tab may be good if you can get it to work, but for your original formulas, I think you can use DateSerial.  For example, for 3 months ago:

if Month({NC_Call_Report_Stats.Dates}) = Month (DateSerial (Year (CurrentDate), Month (CurrentDate) - 3, 1)) and  
year({NC_Call_Report_Stats.Dates}) = Year (DateSerial (Year (CurrentDate), Month (CurrentDate) - 3, 1))
and {NC_Call_Report_Stats.GroupID} = {?Group}
then ({NC_Call_Report_Stats.BaseID})

 The nice thing about DateSerial is that if one of the arguments goes out of the normal range (eg. month 3 - 3 = 0), it adjusts the other values to compensate, so year 2012, month 0, day 1 becomes year 2011, month 12, day 1.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wshcraft70Author Commented:
Note, I've attached my report and an ACCESS table...

Also, note there is a crosstab sub-report that I was successful in creating..

What I can't get to work is:  I need a GRAND total that shows me the AVG # Calls for the given period that the parameter calls for..  It continues to give me CRAZY results..

The Crystal REPORT is written from a SQL database but from the same data that the ACCESS table is representing..  I was not able to send the SQL database..

Many, Many, Many thanks for any help you're able to provide with regards to GRAND TOTALS on both the primary report and the subreport in specific (averages)
Sorry.  I don't have a solution for you at this point, but FWIW ...

 Your chart is still based on the cross-tab in the main report, and what's in the cross-tab makes no sense.  You have {@DISTINCT COUNT BASE ID}, which is just a distinct count of BaseID for the whole report; {@Distinct count days}, which is just a distinct count of dates (days) for the whole report; and {@GROUP CALLS PER HOUR}, which divides the number of days by the number of BaseID's, which doesn't make any sense (days per BaseID?).

 The cross-tab has a row for Hours and {@GROUP CALLS PER HOUR}, and since {@GROUP CALLS PER HOUR} produces the same value for every record (approx .0051), you just get one row for that.  The cross-tab column is {@DISTINCT COUNT BASE ID}, and since that produces the same value for every record (4115), you only get one column.  The summary is a sum of {@GROUP CALLS PER HOUR}, so the cross-tab is just adding the days per BaseID, which is approx .0051, once for each record in each hour.

 So, that's where your chart values are coming from.  Unfortunately, like I said, I don't have a solution for you.  I haven't been able to come up with a relatively simple way to get an average of the distinct BaseID's per hour.  The closest thing I've come up with is to have the chart just use the data (instead of that cross-tab), and have it do a distinct count of BaseID's broken down by the hour and then the date (by day).  Then you get a separate bar for each date, grouped by the hour (although, by default, the hours won't be in the order that you want them).  You can maybe get an idea where the average would be from that, but I haven't found a way to actually produce the average figure in the chart.

 Also, FWIW, in some formulas you check for GroupID = ?Group or Dates = {?Date Range}, but you check those in the record selection formula, so there's no need to check them in other formulas.  There's no real harm in it.  It just makes the formulas a little more complicated than they need to be, and could be confusing.  The formulas look like they're only going to produce those values on certain records, but since the record selection does the same tests, those formulas will actually produce values on every record.

wshcraft70Author Commented:
Thank you for the analysis..  It seems you've reached the same point I have..  What a nightmare..  I'm using the distinctcount of BASEID as 1 BASE ID is = to 1 inbound call.  So I only what a count of the actual call, not of all the activity the call went through.  Then I'm using that distinct count to get SUM of all calls per day for a given group.  I understand for an hourly calculation that wouldn't make sense and instead for that calculation, I'm now using the Times field which breaks out the calls by Time.  I think I've made some great progress on the  Chart but the crosstab for the 2nd piece I need to add is ready for the lighter..  lol

Anyway..  Thank you all VERY much for your hard working in trying to help me out.  I sincerely appreciate everything.

Have a fantastic weekend..
For the average chart, a subreport seems like the best bet.  The idea would be to create a subreport and put the chart in the subreport, and have the subreport just use the BaseID, Hours and Dates fields, and use the "Select Distinct Records" setting in the subreport Report Options.  That way you'd start out with just the distinct data and wouldn't have to worry about distinct counts.  I'm honestly not sure if that would give you what you need either, but that's where I'd start.  Ultimately, you might need to create a manual query (eg. a CR Command) that would do the hour counts for you.  Then CR would only need to average those.

 FWIW, I started trying to create a subreport from your report, but when I tried to add the "distinct records" option, CR wanted to connect to the datasource, and I couldn't find a way to connect to your .accdb file, so that put an end to that.  I don't know if it was a problem on my end, or something about the file.  I've created reports that use Access MDB files before, but I don't know if I've ever tried to use an .accdb file before.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.