simple query

[YoungManRiver]

I can't run a simple query in my CR to do:

select count(code) from transactions
where code
in ('001', '002', '003', '004', 'E01', 'E02', 'E03', 'E04')
and userid = groupname.userid
and datediff('day',cdate,NOW(*)) =0

This is where my transaction table contains the following:

userid ==> derived from my user table
code ==> the transaction code derived from the codes table
cdate ==> day transaction was recorded/created

I need to count these transactions from today back 14 days and put them into my report in fields of:

today, today-1, today-2, etc.

I just can not get the query to run or save without errors, but it works fine in SyBase Central or Interactive SQL.

Please help!

YMR

 

[lbass]

Are you trying to set this up as a command? Or as a SQL expression?

What version of CR are you using?

-LB

 

[Madawc]

If you put select count(code) from transactions where in Reports > Record Selection, of course it won't work. Crystal handles that sort of thing itself, except where you write an SQL Command.

Try just using the selection, and then Crystal's own counts. The use of Crystal totals is outlined at FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[CR85user]

Try a record selection of:

{table.code} in ('001', '002', '003', '004', 'E01', 'E02', 'E03', 'E04')
and userid = groupname.userid
and {table.cdate} > currentdate-15

Then use totals as Madawc suggested.

'J

 

[YoungManRiver]

CR85user,

Tried that but does not work.

Posting reason why with HTML simile of report output.

YMR

 

[YoungManRiver]

All,

Thanks for the feedback. Only thing is I cannot use this for "record selector" only to make a field.
I simulated the output in the attached html table. All fields other than the user are a count or sum.
I have a view that does this and does it right but somehow does not bring this to the reports side
correctly.

I called tech support and they said I should re-install so did. I'm on Ver 11. Had 6-9 previously,
but deleted them on re-install, to make sure there are no conflicts.

Any help appreciated!

Thanks!

YMR

 

[lbass]

It is unclear what you are trying to do. If the view already incorporates the summaries, then are you saying the fields from the view are not appearing in CR?

You could accomplish the same thing within CR by using a record selection formula of:

{transactions.code} in ['001', '002', '003', '004', 'E01', 'E02', 'E03', 'E04'] and
{transaction.cdate} >= currentdate-14

Then you could group by {user.userID}, and then create 14 separate formulas like:

if {transaction.cdate} = currentdate-1 then 1 //yesterday

Place these in the detail section and insert summaries (sums, not counts) at the group level and then suppress the details.

You could also accomplish this with a command, if you want to return the summaries directly, but the syntax would be particular to your datasource. You could try copying your query into the Add Command area, and see if that works.

-LB

 

[YoungManRiver]

All,

Not sure yet, but having some luck with Running Total Fields.


Here is the code I'm using for two fields:

# Running Total Field Name = "TC-Wk" Label = "Wk. Total"
count {desk_Perf_TUS.Act_CD}
# RT Field Formula
{desk_Perf_TUS.Act_CD} IN ('001 002 003 004 E01 E02 E03 E04') 
and {desk_Perf_TUS.CDate} >= currentdate-7

# Running Total Field Name = "TC-00" Label = "Today"
count {desk_Perf_TUS.Act_CD}
# RT Field Formula
{desk_Perf_TUS.Act_CD} IN ('001 002 003 004 E01 E02 E03 E04') 
and {desk_Perf_TUS.CDate} = currentdate

The error I kept having with raw SQL showed when I started using these as I got an error "Missing ')'" from the save on the Running total, though I could not get this message from the formula editor whether here (inside Running Totals) or in any of the other field types or areas.

This showed me the error was on the string inside the "IN" statement. SQL requires it as a comma delimited set of strings and CR takes it only as a single multi-word string.

Working with this now to create all my fields and hope it let's me finish this today.

Any other suggestions or input that improves this is still appreciated.

Thanks!

YMR

 

[lbass]

You don't need to use running totals--they are slower than conditional formulas, and there is nothing you are doing that cannot be handled as I indicated in my last post. However, either way, you need to be aware that Crystal syntax requires square brackets around arrays, not the parens you would use in a command or query.

-LB

 

[YoungManRiver]

LB,

On the report I'm writing, at first no matter what I did I kept getting errors, so I finally did enough debugging to find an error in my CR install so I went through the un-install/re-install process and got most of it working. I still am getting "missing libcs.dll, libct.dll" errors on boot-up from the machine so wondering if this is why some things don't work right, but these .dlls are mostly used by Apache when calling Sybase, so not sure.

I went to both the Business Objects and SyBase Forums and opened trouble tickets on this install issue there, but still not response on how to fix this nor any link to download the missing .dll files.

Forgive my syntax! Since I spend 98% of my time developing in PHP/MySQL or MS-Access, the method I want to use was writting all this in VB code. However everytime I try this I get syntax errors, though CR is suppose to take VB code straight up. I also apologize for my Syntax and I will write in those and forget where I'm at and syntax, until I get errors to correct me, since I'm currently forced to write in CR syntax.

With all the errors I was getting I was forced to do other things. First I tried queries in the report, but they were not working right so I created views in SyBase to fix this. Finally I found some success using "Running Totals", but know this is not the best method.

I can not get the report to organize correctly by grouping which should be:

UserName/UserID - Master Group
Days of Week (Today - Today-6) - Individual Date Groups

Something is wrong in the either the grouping or the passing of groups/filters to the database. I'm doing a un-install/re-install on SyBase this morning, hoping that might cure my problems. I thought this should be in the Detail section using the record selector, but the grouping was not responding when I put my fields into the detail section, so moved them into the Group section.

Design wise, and according to CR docs and past experience, I should be able to declare all the groups above and then be able to pass their filtering into my fields within the detail section, but it's not working here in ver 11 or I'm not holding/twisting my mouth right. I'm pretty frustrated with all this right now.

I also implemented a Tech Support suggestion to unload/load the database in SyBase, but see noting different, after executing that.

I did get all the counts working right (I think, still verifying counts) but ran into problems with the (SUM)s for monetary transactions. What is going on with that is I can not correctly pass the filter of UserID and Date to the SUM for Promises and Posted Monies. I found that I was having a problem, within Sybase of a subquery not picking up the filtering of the parent query in SyBase, so wonder if these are related.

I know the Promise & Posted Monies are in tables not in my current view, and all attempts to either JOIN or UNION on them, goes south.

Anyway this is where I'm at. Forgot what code I submitted so going over this to update where needed.

Thanks!

OMR

 

[YoungManRiver]

All,

One other note. The test DB I'm working on has stale data and this report is suppose to run each night, after midnight, so the first date in the report, with label "Today" is actually

curentdate -1

Since the data is stale in the test DB, I added a hidden/suppressed field {@DateDiff], where I can enter a date offset so now all my date calculations are:

Today => {datefield} = currentdate-{@DateDiff}
Today-1 => {datefield} = currentdate-({@DateDiff}+1)
...

Then I can run the report back over to the production side and change the value in my {@DateDiff] field to "1" and the report runs correctly on fresh data.

YMR

 

[lbass]

So you're all set now?

-LB

 

[YoungManRiver]

Not really, but did find out the problems were all in the install.

With libcs.dll and libct.dll missing, which handle all the filtering, no wondering all the filtering is screwed.

Well got to find a copy of CT-LIB, which contains the files, download and fix.

Thanks!

YMR

 

[YoungManRiver]

All,

Reloading a pre CR V.11 and Sybase pre V.9 so I can get past this.

Obviously the two versions of this from mfg was not check for completeness of the install. Couldn't believe their tech support stiff armed me over this also.

No wonder no one uses SyBase.

YMR