on 01-07-2015 8:22 PM
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
See my last response - you should be able to get the data from fields that are already in the Command.
-Dell
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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!
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
User | Count |
---|---|
85 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.