Crystal Reports 2008 combining Command and db together

[Former Member]

Hi,

I'm new at Crystal Reports and I need to make a report where I need to combine Command and direct db together.

Can you please tell me how can I do that in SQL query? With INNER JOIN or?

How can I integrate mCallEnd and mOpInterval in the SQL query in Command so I can have all fields and formulas from all 3 tables in Command?

Thank you in advance

[DellSC]

See my last response - you should be able to get the data from fields that are already in the Command.

-Dell

[Former Member]

thank you for your response. But how can I create a TimeStamp if I don't have that field in the Command (first column) with hourly interval.

DateTime formula in mCallEnd looks like this:

dim dDate as datetime

dDate = DateTimeValue(({@Interval} / 1440) + 1)

if second(ddate) > 0 then

    ddate = dateadd("s", 1, ddate)

end if

formula = ddate

But @Interval consists of

(({mCallEnd.Timestamp} \ {?Interval}) * {?Interval}) + {?Interval}

and I don't know how to create that Timestamp that I don't have?

[Former Member]

Hi,

You can link the command fields and db fields in the following way:

Database -> Database Expert -> Link tab -> you can link the fields as you like and also click on link then goto Link options set Inner join. For example:

Thanks,

DJ

[DellSC]

However, unless tables included in your report are small, you really don't want to do this!  When you join anything to a command, Crystal cannot push the join to the database.  Instead, it will pull ALL of the data into memory and do the joins there.

Best practice with Commands is to include ALL of the data required for the report in a single command.  Other Command best practices:

1.  DO NOT use the Select Expert.  This will also pull the data into memory and filter it there.  Instead, put all of your filters in the Where clause.

2.  If you're using parameters in the filter conditions, you MUST create the parameters in the Command Editor.  Once you've created and used them there, you can edit them in the Field Explorer, but the Command Editor cannot see parameters that have been created in the Field Explorer.

3.  You cannot use SQL Expressions with a command.  Instead, include the code for the equivalent of the SQL Expression in the command.

-Dell

[Former Member]

The query for Command is really long and complicated and it's used in another report. I'm trying to use this Command with another table called mCallEnd because I need couple of fields from mCallEnd table to make my report.

I attached the picture of report that I need to make along with my Command query and links between Command and mCallEnd.

The first 3 columns I need to pull out from mCallEnd table, and Total Billable Minutes is made in Command query.

Thanks!

command1.txt.zip

[DellSC]

I would add the mCall table into the command and join it there.  From there, this is a SQL issue, not a Crystal issue.  However, if you'll post the following I'll see what I can do to help you out with this:

- Current Command

- Definition of mCall table - what fields do you need and how does it link to other data.

-Dell

[Former Member]

I attached the current Command.

And from mCallEnd table I need these fields to make the first 4 columns in my report.

Timestamp

Call

Account

Billing

Duration

Initials

CallKind

OpTime

NoMessage

Ring

Xdial

Wait

Zero

Hold

Ahold

Talk

Disc

Abandoned

Assigned

Answered

SysAbandon

TTY

HiTalk

Command01.txt.zip

[DellSC]

What is the data type of the Abandoned and Answered fields?

I figured out how to do this with a couple of simple tweaks to the command, but I need to know the data type of those fields before I can finish it up.

-Dell

[Former Member]

Abandoned and Answered are both Number.

[DellSC]

What are the valid values?  Is it 1 and 0 or something else?

-Dell

[DellSC]

Never mind, it looks like the fields are already there - look for OpAssigned, OpAnswered and OpAbandoned in the fields being returned from the command.  I assume that total calls will be OpAssigned + OpAnswered + OpAbandoned.

-Dell