Help

I am using a SQL Server database to write a report on Crystal Reports
9. I am having trouble with the sql to set the parameters. namely from
the master.datetime field. I am trying to take the datetime field and
set it into 4 different parameters (BeginDate, EndDate, BeginTime,
EndTime).
I have tried this...
((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
({?EndDate} + TimeValue({?EndTime}))) And
this gives give me an error stating that timevalue does not exist
and I've tried this...
((Master.DateTime) Between ({?BeginDate} + ({?BeginTime})) And
({?EndDate} +({?EndTime}))) And
no errors occur, but the report comes up completely blank...
Can anyone help me here...please!!!I think you are looking for getdate()
To get the current server time in sql server use getdate(). Also, you will
want to convert your sql concatenation back into a datetime type.
so it would be something like the following
WHERE getdate() BETWEEN CAST(BeginDate + ' ' + BeginTime AS DateTime) AND
CAST(EndDate + ' ' + EndTime AS DateTime)
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> I am using a SQL Server database to write a report on Crystal Reports
> 9. I am having trouble with the sql to set the parameters. namely from
> the master.datetime field. I am trying to take the datetime field and
> set it into 4 different parameters (BeginDate, EndDate, BeginTime,
> EndTime).
> I have tried this...
>
> ((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
> ({?EndDate} + TimeValue({?EndTime}))) And
>
> this gives give me an error stating that timevalue does not exist
> and I've tried this...
>
> ((Master.DateTime) Between ({?BeginDate} + ({?BeginTime})) And
> ({?EndDate} +({?EndTime}))) And
> no errors occur, but the report comes up completely blank...
> Can anyone help me here...please!!!
>|||Thanks for the reply. That didn't work either. What I am trying to do
is create a report for that allows the user to pick a BeginDate, an
EndDate, a BeginTime, and an EndTime. All of this comes from the field
DateTime on the master DB. I need to set those BeginTime, EndTime, etc
parameters so the user can bring up data for a particular time period.|||I guess I am a little confused.
Can you explain what you mean by DateTime field in the Master database?
What table in the master database are you looking at? Why are you querying
the master database at all?
I would imagine that you have a user database that contains the actual data
that you need. Can you explain what you are trying to get from the master
database.
You definitely can not reference a field in the master database without also
referencing the table that it resides in (and for that matter a specific row
in that table). But, I can imagine what you need to refer to the master
database for.
Can you send some more specifics?
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> Thanks for the reply. That didn't work either. What I am trying to do
> is create a report for that allows the user to pick a BeginDate, an
> EndDate, a BeginTime, and an EndTime. All of this comes from the field
> DateTime on the master DB. I need to set those BeginTime, EndTime, etc
> parameters so the user can bring up data for a particular time period.
>|||It is the master table in the user database. Sorry...I have been
pulling my hair out over this for hours now. We have a giant
database...and all the info I need comes from the master table. I
will show you all the code written so far, so you have an idea what is
going on. I am writing a specific report to track local and toll
calls, which comes from the main (user) database. The one and only
field I am having trouble setting the parameters for is the DateTime
field in the master table. It was set up as two different fields as an
Access database, but now we switched over to an SQL Server database,
and it is all in one field. Out of master.DateTime, I need to be able
to set BeginDate, EndDate, BeginTime, and EndTime as parameters to make
it easier for the user to locate calls by date and time. It is all
grouped by departments.
Here is the code I have with the code that is crashing....I will a
major amount of spaces around the code I am having problems with:
Select
sum(case when Master.CallType='2' Then 1 else 0 end),
sum(case when Master.CallType='2' Then Master.Duration else 0 end) ,
sum(case when Master.CallType='2' Then Master.CostOfCall else 0 end),
sum(case when Master.CostOfCall > 0 Then 1 else 0 end),
sum(case when Master.CostOfCall > 0 Then Master.Duration else 0 end),
sum(case when Master.CostOfCall > 0 Then Master.CostOfCall else 0 end),
count(master.recordcontrolnumber), sum(master.duration),
sum(master.costofcall),
department.departmentname
FROM master LEFT JOIN (extension LEFT JOIN department ON
(extension.sitecode=department.sitecode) AND
(extension.departmentnumber=department.departmentnumber)) ON
(master.extension=extension.line) AND
(master.sitecode=extension.sitecode)
Where ('{?SiteCode}' = '*' Or Master.SiteCode Like '{?SiteCode}') And
((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
({?EndDate} + TimeValue({?EndTime}))) And
<--problem here!!!!!!!!
('{?DivisionName}' = '*' Or Department.DivisionName Like
'{?DivisionName}') And
('{?CostCenterName}' = '*' Or Department.CostCenterName Like
'{?CostCenterName}') And
('{?DepartmentName}' = '*' Or Department.DepartmentName Like
'{?DepartmentName}') And
('{?UserName}' = '*' Or Extension.UserName Like '{?UserName}') And
('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCode}')
And
('{?City}' = '*' Or Master.City Like '{?City}') And
('{?State}' = '*' Or Master.State Like '{?State}') And
('{?TrunkGroup}' = '*' Or Master.GroupUsed Like '{?TrunkGroup}') And
('{?Trunk}' = '*' Or Master.TrunkUsed Like '{?Trunk}') And
('{?CallType}' = '*' Or Master.CallType Like '{?CallType}') And
('{?DirectionFlag}' = '*' Or Master.DirectionFlag Like
'{?DirectionFlag}')
Group By Department.DepartmentName|||It only needs to be between 2 dates....for example....1/1/06 to
1/4/06....my boss just explained it to me now after a meeting...he
said not to worry about the time|||Okay. It is making more sense now.
Can you provide me a couple more things?
1) What datatype is DateTime in the Master table?
2) What is the datatype of the 4 parameters that you are using to construct
the begin and end dates?
3) Can you give me an example of what each parameter might typically look
like?
With this information, I will hopefully be able to help.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> It is the master table in the user database. Sorry...I have been
> pulling my hair out over this for hours now. We have a giant
> database...and all the info I need comes from the master table. I
> will show you all the code written so far, so you have an idea what is
> going on. I am writing a specific report to track local and toll
> calls, which comes from the main (user) database. The one and only
> field I am having trouble setting the parameters for is the DateTime
> field in the master table. It was set up as two different fields as an
> Access database, but now we switched over to an SQL Server database,
> and it is all in one field. Out of master.DateTime, I need to be able
> to set BeginDate, EndDate, BeginTime, and EndTime as parameters to make
> it easier for the user to locate calls by date and time. It is all
> grouped by departments.
> Here is the code I have with the code that is crashing....I will a
> major amount of spaces around the code I am having problems with:
> Select
> sum(case when Master.CallType='2' Then 1 else 0 end),
> sum(case when Master.CallType='2' Then Master.Duration else 0 end) ,
> sum(case when Master.CallType='2' Then Master.CostOfCall else 0 end),
> sum(case when Master.CostOfCall > 0 Then 1 else 0 end),
> sum(case when Master.CostOfCall > 0 Then Master.Duration else 0 end),
> sum(case when Master.CostOfCall > 0 Then Master.CostOfCall else 0 end),
> count(master.recordcontrolnumber), sum(master.duration),
> sum(master.costofcall),
> department.departmentname
> FROM master LEFT JOIN (extension LEFT JOIN department ON
> (extension.sitecode=department.sitecode) AND
> (extension.departmentnumber=department.departmentnumber)) ON
> (master.extension=extension.line) AND
> (master.sitecode=extension.sitecode)
> Where ('{?SiteCode}' = '*' Or Master.SiteCode Like '{?SiteCode}') And
>
>
> ((Master.DateTime) Between ({?BeginDate} + TimeValue({?BeginTime})) And
> ({?EndDate} + TimeValue({?EndTime}))) And
> <--problem here!!!!!!!!
>
>
> ('{?DivisionName}' = '*' Or Department.DivisionName Like
> '{?DivisionName}') And
> ('{?CostCenterName}' = '*' Or Department.CostCenterName Like
> '{?CostCenterName}') And
> ('{?DepartmentName}' = '*' Or Department.DepartmentName Like
> '{?DepartmentName}') And
> ('{?UserName}' = '*' Or Extension.UserName Like '{?UserName}') And
> ('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCode}')
> And
> ('{?City}' = '*' Or Master.City Like '{?City}') And
> ('{?State}' = '*' Or Master.State Like '{?State}') And
> ('{?TrunkGroup}' = '*' Or Master.GroupUsed Like '{?TrunkGroup}') And
> ('{?Trunk}' = '*' Or Master.TrunkUsed Like '{?Trunk}') And
> ('{?CallType}' = '*' Or Master.CallType Like '{?CallType}') And
> ('{?DirectionFlag}' = '*' Or Master.DirectionFlag Like
> '{?DirectionFlag}')
> Group By Department.DepartmentName
>|||That will make it a bit easier.
Can you still post the items that I previously requested? (ie, the
datatypes and sample data). Thanks.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> It only needs to be between 2 dates....for example....1/1/06 to
> 1/4/06....my boss just explained it to me now after a meeting...he
> said not to worry about the time
>|||1)DateTime
2)there are only 2 now...BeginDateTime and EndDateTime
3)it will be a date/time like 1/4/06 14:30|||1)DateTime
2)there are only 2 now...BeginDateTime and EndDateTime
3)it will be a date/time like 1/4/06 14:30
Thank you for all of you help.....I am trying this right now...
Master.DateTime Between ({?BeginDateTime}) And ({?EndDateTime}) And
...for the line of code that is erroring....I am not getting an
error...but page 2 of my report where the data is grouped by
departments is all blank...I don't know if this is supposed to
happen...This is the first real Crystal Report I have ever written|||Have you tried running the SQL outside of Crystal just to look and verify
that they look like you want?
I'm not sure if this is SQL passed to the db or in a stored procedure. But,
either way, one way to do this would be to use sql-profiler to profile either
the sp execution or TSQL execution. You can then use the TextData to see the
exact call being made on the SQL server.
You can then run this in query analyzer to view the result set that is being
returned.
From what I know, you can not regroup you data to redisplay on separate
pages of the reports. Since, Crystal would have already walked through your
data to bind to the report using the first grouping.
I definitely am not a Crystal expert. But, I would recommend running the
SQL outside of Crystal so you have a good idea of the Record Set that you are
ultimately binding to your report and to verify that your joins and WHERE
clause is working as you expect.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> 1)DateTime
> 2)there are only 2 now...BeginDateTime and EndDateTime
> 3)it will be a date/time like 1/4/06 14:30
>
> Thank you for all of you help.....I am trying this right now...
> Master.DateTime Between ({?BeginDateTime}) And ({?EndDateTime}) And
> ....for the line of code that is erroring....I am not getting an
> error...but page 2 of my report where the data is grouped by
> departments is all blank...I don't know if this is supposed to
> happen...This is the first real Crystal Report I have ever written
>|||Thank you so much for your help. You have been very helpful. I
finally got it all to work out with that last line of code...I just
had to check the dates on the db...that is why i was getting a blank
report. Right now I am just writting the formula fields and I will be
done with this report. Couldn't have done it without your help!!!!
Thanks again!!!!|||Cool. Glad I could help.
Best of luck.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark" wrote:
> Thank you so much for your help. You have been very helpful. I
> finally got it all to work out with that last line of code...I just
> had to check the dates on the db...that is why i was getting a blank
> report. Right now I am just writting the formula fields and I will be
> done with this report. Couldn't have done it without your help!!!!
> Thanks again!!!!
>