Solved

Formula help

Posted on 2010-09-05
10
394 Views
Last Modified: 2012-05-10
Hi,

I have record select statement that looks this:

{opencall1.logdatex} in (((today-CDate(1970,01,01))*24*60*60) - 2592000) to (today-CDate(1970,01,01))*24*60*60

Basically the logdatex field is in unixtime. Everytime a call is logged in my helpdesk sytem the time of that call is loggd in that table. That formula calculates the lat 30 days.

The problem I am having is that I cannot use that in record select statement anymore. Part of my reports need to show average calls and when you have that select statement in it only calculates customers that have called and not the ones that havent.

So I need to apply that formula in a seperate formula field and not in the record select statement.

I have created this formula to calculate the number of calls in the last 30 days.

However it does not work, can anyone help?

{opencall1.logdatex} in (((today-CDate(1970,01,01))*24*60*60) - 2592000) to (today-CDate(1970,01,01))*24*60*60
and distinctcount ({opencall1.callref})

Thanks

0
Comment
Question by:Dan560
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33606115
Don't you need a number?

and distinctcount ({opencall1.callref}) > 0
0
 
LVL 2

Author Comment

by:Dan560
ID: 33606128
Ok, that works, although I get an output of false or false

Is there a way I can count if the output is false then put 0 if it is true then put 1 ?

I just would like to then count all of the trues

Thanks
0
 
LVL 77

Expert Comment

by:peter57r
ID: 33606163
I assume that by ' average calls ' you must be calculating the average number of calls per customer.

If so, then you need a single value for the total number of customers.  

To get that you can use a sql expression field which looks something like..

//%CustCount

(Select Count(customerid) from customertable)

This must be written in sql language used by your database.

You need to include the customer table in the report data source although it is not necessary to include any fields from it in the report.

You can then create a formula field to divide the total number of calls by this result
Something like..
Count ({opecall1.callid})/{%custcount}

0
 
LVL 2

Author Comment

by:Dan560
ID: 33606248
All I'm getting with the sql expressions is errors:

Error in compiling SQL Expression:

Database connection error: '42000:[MySQL][ODBC 3.51 Driver][mysqld-4.0.16-log] You have an error in your SQL syntax

(Select Count (cust_id) from opencall.cust_id)
0
 
LVL 77

Expert Comment

by:peter57r
ID: 33606880
(Select Count (cust_id) from opencall)

Assuming your table is called opencall.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Author Comment

by:Dan560
ID: 33606898
Unfortunatley that didnt work.

I am using mysql, if I drag the table I want to use it appears like this:

`opencall`.`cust_id`


0
 
LVL 100

Expert Comment

by:mlmcc
ID: 33607529
Where are you using the SQL?

mlmcc
0
 
LVL 2

Author Comment

by:Dan560
ID: 33607532
SQL expression section in formula editor
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 33607635
What have you entered as the SQL Expression?

A SQL Expression is jsut a SELECT statement that returns a sungle value.

mlmcc
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 33624559
If you just want a count of the records with dates in that range, you could create a formula like the following and do a summary on it:

if {opencall1.logdatex} in (((today-CDate(1970,01,01))*24*60*60) - 2592000) to
 (today-CDate(1970,01,01))*24*60*60 then
  1


 However, your use of DistinctCount ({opencall1.callref}) implies that it's not that simple.  If you could have multiple records with the same callref and only want to count each callref once, it does get a bit more complicated.  If that's the case, would all of the records for a given callref have the same logdatex?  If not, do you include a callref in the count if _any_ of the dates for that callref are in the last 30 days, or only if _all_ of them are in the last 30 days?

 James
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Crystal Reports Formula Field 17 64
C# Crystal Reports Formula 6 79
how to combine a date and time field in a query 9 48
How to Bold a formula within another formula 4 31
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 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

18 Experts available now in Live!

Get 1:1 Help Now