Check the for the right syntax to use - Crystal Reports Error

set @variable:=value,@variable1:=value2

Are you certain that is the line that's the issue?

I am, because if I remove it then the error does not appear. However my data does not appear correctly after that as obviously the variables are removed.

Ok I have tried your suggestion but my error remains the same. Thanks though.

http://dev.mysql.com/doc/refman/4.1/en/set-option.html
set @variable=value

Do you have to have 1 set per line?

set @total:='0';
set @avg:='0';

mlmcc

Yes I have tried that. Same error message unfortunately.

try select @variable:=value
set might be for system variables.

See whether you can update the ODBC connector you are using as it might be it that is the issue.
You seem to be using mysql 4.x or at least the ODBC connector that is a bit older.

Do not have a mysql of version 4. to test whether the error is from the mysql server or from the ODBC connector's conversion.

Try using  " " instead of ' '

set @total:="0";
set @avg:="0";

mlmcc

Hi mlmcc,

Same issue unfortunately.

Arnold, I seem to be using the supported ODBC driver for 4.0.16. I have also used the same statement through the mysql command line interface and the command works fine.

Do you have the most current version of the ODBC connector?
http://dev.mysql.com/downloads/connector/odbc/3.51.html#downloads

Enable tracing on the ODBC connector.

Hi Arnold,

Please find my log file attached. I am confident that I am using the correct ODBC driver.
logfiles.txt

The problem I think is that when you set the variables you actually define them as characters, you then use the select and treat them as integers/float respectively.

set @total:=0,@avg:=0.0;

Good thinking. Just tried it though and I get the same error.

When you do not set, does t return the correct value for var and total?
Not sure why you are using a tabulated variable.
I would have thought that a variable would be used for a report used in the where clause.

I am running a very low version of MYSQL and I want to do a cumulative total. I could not find out another way of doing this without using the set commands.

To answer your question , null values are returned -

 Date       | Total Calls Closed | Total Calls | Cumaltive Total | AVG  |
------------+--------------------+-------------+-----------------+------+
 2012-01-28 |                  0 |           1 |            NULL | NULL |
 2012-01-29 |                  0 |           1 |            NULL | NULL |
 2012-01-30 |                  2 |           4 |            NULL | NULL |
 2012-01-31 |                  0 |           2 |            NULL | NULL |
 2012-02-01 |                  1 |           2 |            NULL | NULL |
 2012-02-02 |                  1 |           1 |            NULL | NULL |
 2012-02-03 |                  6 |           7 |            NULL | NULL |
 2012-02-04 |                  0 |           0 |            NULL | NULL |
 2012-02-05 |                  0 |           0 |            NULL | NULL |

use = instead of :=
use group by Date



can you post the show create table

Have you thought of creating a view that will aggregate the data based on the date (group by date)?

Can you post the show create table for the tables used in your query?

I am not familiar with the show create table command. What does it do?

No I have not thought about that, do you mind helping me with the statement?

Does this have any relation to the Crystal zone?
You added it as a zone to the question.

mlmcc

show create table tablename is a MySQL command to display the creation of the table, columns def, keys, etc.

The more complex issue is the average tabulation especially when I have no basis on which you are trying to get data.
select unixtimestamp-Calc-to represent one day as Date,sum(calls),* from table name group by unixtimestamp-Calc-to represent one day

This way to represent  column will be the date on one day and all the calls will be grouped by that parameter.

Hi Arnold,

Please find the outcome of my statement. -

What I am trying to get is a moving average, so for each day there is Cumaltive total which is based on the total number of calls up until that day, this is within a 30 day period. The average is then calculated by dividing each total total by 30. My report only needs to show the last 8 days of data.



------+
| Date       | Total Calls Closed | Total Calls | Cumaltive Total | AVG
      |
+------------+--------------------+-------------+-----------------+-------------
------+
| 2012-01-29 |                  0 |           1 |               1 |
    0 |
| 2012-01-30 |                  2 |           4 |               5 | 0.0333333333
33333 |
| 2012-01-31 |                  0 |           2 |               7 |  0.166666666
66667 |
| 2012-02-01 |                  1 |           2 |               9 |  0.233333333
33333 |
| 2012-02-02 |                  1 |           1 |              10 |
  0.3 |
| 2012-02-03 |                  6 |           7 |              17 |  0.333333333
33333 |
| 2012-02-04 |                  0 |           0 |              17 |  0.566666666
66667 |
| 2012-02-05 |                  0 |           0 |              17 |  0.566666666
66667 |
| 2012-02-06 |                  1 |           8 |              25 |  0.566666666
66667 |
| 2012-02-07 |                  3 |           5 |              30 |  0.833333333
33333 |
| 2012-02-08 |                  0 |           1 |              31 |
    1 |
| 2012-02-09 |                  1 |           4 |              35 |   1.03333333
33333 |
| 2012-02-10 |                  1 |           1 |              36 |   1.16666666
66667 |
| 2012-02-11 |                  0 |           0 |              36 |
  1.2 |
| 2012-02-12 |                  0 |           0 |              36 |
  1.2 |
| 2012-02-13 |                  2 |           6 |              42 |
  1.2 |
| 2012-02-14 |                  2 |           6 |              48 |
  1.4 |
| 2012-02-15 |                  2 |           4 |              52 |
  1.6 |
| 2012-02-16 |                  0 |           1 |              53 |   1.73333333
33333 |
| 2012-02-17 |                  4 |           6 |              59 |   1.76666666
66667 |
| 2012-02-18 |                  0 |           0 |              59 |   1.96666666
66667 |
| 2012-02-19 |                  0 |           0 |              59 |   1.96666666
66667 |
| 2012-02-20 |                  4 |           9 |              68 |   1.96666666
66667 |
| 2012-02-21 |                  0 |           6 |              74 |   2.26666666
66667 |
| 2012-02-22 |                  2 |           7 |              81 |   2.46666666
66667 |
| 2012-02-23 |                  1 |           6 |              87 |
  2.7 |
| 2012-02-24 |                  0 |           2 |              89 |
  2.9 |
| 2012-02-25 |                  1 |           2 |              91 |   2.96666666
66667 |
| 2012-02-26 |                  0 |           0 |              91 |   3.03333333
33333 |
| 2012-02-27 |                  1 |           8 |              99 |   3.03333333
33333 |
| 2012-02-28 |                  1 |           1 |             100 |
  3.3 |
+------------+--------------------+-------------+-----------------+-------------

I understand what the end result you want to get, I need to understand what the data is you have to work with.

You may have to run multiple queries.
i.e. one to get data on total calls within a period you can use data manipulation or since you are using unix time stamp, an integer operation is enough i.e. 8 days will be Within timestamp and timestamp - 86400*8
So one query will get the total number of calls over that period.

I do not get the average what is being devided into what?

ok I am working with the following fields..

maindb.callref - this is the ID given to each call, example 68456
maindb.logdatex - this is the date / time of the call formatted as unixtime
maindb.closedatax - this is the close date / time formatted as unixtime
maindb.fk_company_id  - this is the ID of the company that we are supporting.

daily_report_dates.date this is list of dates that I have left joined to this statement, the purpose of it is to display missing dates. This is purely for display purposes in my report. As quite often there are days where our helpdesk do not log any calls, which then messes up the display of my report.

Ok so I want to show the following in my report -
Total Calls
Total Calls closed on same day
Average Daily Calls

The average is a running / moving average. So I need a count the total number up until each date and then divide this number by 30. This should display the monthly average per day.

Please let me know if you have any more questions. I am new to sql so I am sorry if I am unclear.

set @total:=0;
set @avg:=0;
SELECT
daily_report_dates.date as"Date",
sum(if(FROM_UNIXTIME(opencall.logdatex,"%Y-%m-%d") = FROM_UNIXTIME(opencall.closedatex,"%Y-%m-%d"),1,0 )) As "Total Calls Closed",
count(distinct opencall.callref)as "Total Calls",
@total:=count(opencall.callref) +@total as 'Cumaltive Total',
@avg:= @total / 30 as 'AVG'
FROM mdis_daily_report_dates
LEFT JOIN opencall
ON daily_report_dates.date = FROM_UNIXTIME(logdatex,"%Y-%m-%d")
AND opencall.fk_company_id = "abc"
WHERE daily_report_dates.date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE() 
GROUP BY daily_report_dates.date
ORDER BY daily_report_dates.date
                                           

Select allOpen in new window

Your average is not really an average.  it is to total number of calls up to that point over the 30.

Can you provide sample data?

You can suppress sections or even just add another filter to just get the  last 8 days

Are you doing the SQL in Crystal or the database?

Something like

SELECT * FROM
(
YOUR SQL
)  mydata
Where mydata.Date >= CUrdate - 8

mlmcc

Hi everyone,

Sorry for not getting back to you all. As you know Crystal does not like to have the following user variables in the connection command - set @total:=0; set @avg:=0;

However I have been sneaky and added them into my ODBC file dns connection -

initstmt="set @total:=0"

However when I refresh the report crystal does not read this command again. Is there anyway I can get Crystal to read this parameter again upon refresh?

I don't think so.

mlmcc

James that helped a lot. Thank you

You're welcome.  Glad I could help.

 James