Hi all
I am working on Crystal reports 8.5
In one report we need to display weekdays and weekends in every month for the given date range
I mean
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Days of Service
Weekdays Number of weekdays in the month
Weekends Number of weekends in the month
And I need to calculate total of some items from Friday after 3 pm
2) In another report I am using stored procedure to get fields
As per my knowledge we cannot use other than one stored procedure in crystal reports 8.5
So I am using sub report to get some more fields other than stored procedure fields
But in main report I need to get sub- total of fields in sub report.
Could any one please help me out?You would use the Crystal Reports (http://www.saveonsupport.com) shared variable functionality. In your subreport you would have a formula field something like this:
WhilePrintingRecords;
Shared numbervar myTotal;
myTotal := myTotal + {field name}
Add this field to the subreport and suppress it.
Then in the main report you need another formula something like this:
WhilePrintingRecords;
Shared numbervar myTotal;
myTotal
The subreport keeps adjusting the total while printing records and the main report simply displays this value.
Hop this helps!|||Thanks for your reply
it works for me
Showing posts with label crystal. Show all posts
Showing posts with label crystal. Show all posts
Friday, March 30, 2012
help in crystal report & dataset
I'm the new beginer in C#,I have some problem in doing crystal report:
In my application I have 2 form named Form1 and Form2.
I create one dataset named ds in Form1 and in Form2 I want to creat a crystal report that displays the data in the dataset ds in Form1.
I relly get stuck.Can anyone help me?
Thanx b4 hand!See if you find answer here
www.asp.net
http://support.businessobjects.com/sql
In my application I have 2 form named Form1 and Form2.
I create one dataset named ds in Form1 and in Form2 I want to creat a crystal report that displays the data in the dataset ds in Form1.
I relly get stuck.Can anyone help me?
Thanx b4 hand!See if you find answer here
www.asp.net
http://support.businessobjects.com/sql
Help in Crystal Report
Hi,
I am new in crystal report (using vb.net). Currenlty, my report format
looks like below:
School : ABC
Class : A1
======================================
Details Stud1 IC No1 Address1
Stud2 IC No.2 Address2
Page Footer: Page 1
School : ABC
Class : A2
======================================
Details Stud3 IC No1 Address1
Stud4 IC No.2 Address2
Page Footer: Page 2
School : DBF
Class : A1
======================================
Details Stud1 IC No1 Address1
Stud2 IC No.2 Address2
Page Footer: Page 3
School : DBF
Class : A2
======================================
Details Stud3 IC No1 Address1
Stud4 IC No.2 Address2
Page Footer: Page 4
Both School and Class formula were placed in Page Header. Also, I have
two group headers ie. School then follows by Class (before the details
section). Currently, the report prints at new page for every different
Class.
How do I print the all classes of the same School continuously without
page break; and only move to new page if the current page has been
filled up. I need both the Class and School names to appear in every
new page. Also, For every new school, it should be a fresh new page ie.
should not mix the details with other school. Can anyone help in this
problem? Many thanks!Hi,
I hope you are using two groups. That means the grouping is made on "School" field as well as "Class" field. uncheck the "NEW page AFTER" under the "Class " group footer and check the same in "School" group footer.
For heading should be shown in all pages, check the "Repeat Group header on each page" Group Expert option
I hope , you got what you wanted
I am new in crystal report (using vb.net). Currenlty, my report format
looks like below:
School : ABC
Class : A1
======================================
Details Stud1 IC No1 Address1
Stud2 IC No.2 Address2
Page Footer: Page 1
School : ABC
Class : A2
======================================
Details Stud3 IC No1 Address1
Stud4 IC No.2 Address2
Page Footer: Page 2
School : DBF
Class : A1
======================================
Details Stud1 IC No1 Address1
Stud2 IC No.2 Address2
Page Footer: Page 3
School : DBF
Class : A2
======================================
Details Stud3 IC No1 Address1
Stud4 IC No.2 Address2
Page Footer: Page 4
Both School and Class formula were placed in Page Header. Also, I have
two group headers ie. School then follows by Class (before the details
section). Currently, the report prints at new page for every different
Class.
How do I print the all classes of the same School continuously without
page break; and only move to new page if the current page has been
filled up. I need both the Class and School names to appear in every
new page. Also, For every new school, it should be a fresh new page ie.
should not mix the details with other school. Can anyone help in this
problem? Many thanks!Hi,
I hope you are using two groups. That means the grouping is made on "School" field as well as "Class" field. uncheck the "NEW page AFTER" under the "Class " group footer and check the same in "School" group footer.
For heading should be shown in all pages, check the "Repeat Group header on each page" Group Expert option
I hope , you got what you wanted
Friday, February 24, 2012
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}') A
nd
('{?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}') A
nd
> ('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCod
e}')
> 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}) An
d
...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
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}') A
nd
('{?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}') A
nd
> ('{?AccountCode}' = '*' Or Master.AccountCode Like '{?AccountCod
e}')
> 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}) An
d
...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
Sunday, February 19, 2012
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.departmentn umber)) 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.departmentn umber)) 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
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.departmentn umber)) 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.departmentn umber)) 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
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!!!!
>
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!!!!
>
Hellp Please: Running 2 projects under deferent crystal versions
Please need help.
I have spent 2 days and still did not find a solution. I have 2 differnt web applications. One has reports contracted unde Cr 10 and the other Cr 11. For some reasons i can not make it run together. I can install both version and I see all files of both versions in GAC but only one runs at a time if I unintsall lets say Cr 11 and install 10 then only application running under 10 will run, then if I install 11 application under 11 will only run. So it looks like when I install Cr it overrides some file. I cheked machine.cfg nothing in there. I do not have web.cfg in default web site.
This is the error I get:
[COMException (0x80040154): Class not registered - DTSConnection]
CrystalDecisions.ReportAppServer.ClientDoc.ReportClientDocumentClass.Open(Object& DocumentPath, Int32 Options) +0
CrystalDecisions.ReportAppServer.ReportClientDocumentWrapper.Open(Object& DocumentPath, Int32 Options) +72
CrystalDecisions.ReportAppServer.ReportClientDocumentWrapper.EnsureDocumentIsOpened() +218
I searched entire web, nothinig helpfull.
If anybody knwos how to solve thise issue, i will be very very thankfullI got some more insight on teh problem that gave me an idea but still no answr. I can set up both web application ussing 2 different crystal versions either on deferent web sistes on the same PC like one on default and the other on some other site and it works and also it works when I set up both of then on other web site than default one. It looks like default site using some regestry or cfg file settings on higher hierarchy level that overrerides web.cfg in virtual dircetory. I cheked machine.cfg what did not seem to have any info for crystal and also I do not have any default web.cfg in default site, at least I do not see it.
Anybody, ideas, please!!!|||See if you find solution here
http://support.businessobjects.com/
I have spent 2 days and still did not find a solution. I have 2 differnt web applications. One has reports contracted unde Cr 10 and the other Cr 11. For some reasons i can not make it run together. I can install both version and I see all files of both versions in GAC but only one runs at a time if I unintsall lets say Cr 11 and install 10 then only application running under 10 will run, then if I install 11 application under 11 will only run. So it looks like when I install Cr it overrides some file. I cheked machine.cfg nothing in there. I do not have web.cfg in default web site.
This is the error I get:
[COMException (0x80040154): Class not registered - DTSConnection]
CrystalDecisions.ReportAppServer.ClientDoc.ReportClientDocumentClass.Open(Object& DocumentPath, Int32 Options) +0
CrystalDecisions.ReportAppServer.ReportClientDocumentWrapper.Open(Object& DocumentPath, Int32 Options) +72
CrystalDecisions.ReportAppServer.ReportClientDocumentWrapper.EnsureDocumentIsOpened() +218
I searched entire web, nothinig helpfull.
If anybody knwos how to solve thise issue, i will be very very thankfullI got some more insight on teh problem that gave me an idea but still no answr. I can set up both web application ussing 2 different crystal versions either on deferent web sistes on the same PC like one on default and the other on some other site and it works and also it works when I set up both of then on other web site than default one. It looks like default site using some regestry or cfg file settings on higher hierarchy level that overrerides web.cfg in virtual dircetory. I cheked machine.cfg what did not seem to have any info for crystal and also I do not have any default web.cfg in default site, at least I do not see it.
Anybody, ideas, please!!!|||See if you find solution here
http://support.businessobjects.com/
Subscribe to:
Comments (Atom)