Solved

Crystal Reports 2011 (Formula assistance)

Posted on 2012-03-21
12
510 Views
Last Modified: 2012-03-24
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..
0
Comment
Question by:wshcraft70
  • 5
  • 4
  • 3
12 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 150 total points
ID: 37749946
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?

mlmcc
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37749984
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37750069
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?

mlmcc
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37750078
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..
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37750093
Either would work.  Attach the report as well if you want.

mlmcc
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37750156
Which version would you prefer for the database?  Access or Excel?  I could possibly export the SQL as well..
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 100

Expert Comment

by:mlmcc
ID: 37750467
Access is a bit easier to use in Crystal

mlmcc
0
 
LVL 34

Accepted Solution

by:
James0628 earned 350 total points
ID: 37750988
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.

 James
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37753338
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)
view.accdb
ACD-ICD-VIEW.rpt
0
 
LVL 34

Expert Comment

by:James0628
ID: 37755967
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.

 James
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37757033
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..
0
 
LVL 34

Expert Comment

by:James0628
ID: 37760172
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.

 James
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now