PC Review

Thread Tools Rate Thread

Crystal Reports Query

Posts: n/a
      21st May 2004

I have a Crystal Report that I need to base on a set of 4 tables, as listed and described below:


Basically, is for Timekeeping functionality. Each Company can have many Projects.
TimeEntries table records a set of hours details, as well as a ProjectId and a TimeEntryDate per record.
ProjectExpenses stores an Expenseamount, a ProjectId and an ExpenseDate.

All this is rather simple, but the problem comes in when I try to combine Expenses and Timeentries on the same report!!

I limit TimeEntries by a Start and End date (in the RecordSelection formula in Crystal), AND I need to limit Expenses to only those between those dates.
Now, if theres at least one TimeEntry for that time-period, then Expenses also show up fine. BUT, if there are no TimeEntries for that time-period, then the link seems to also block ALL Expenses for that project, regardless of whether there are any Expenses in that period!!

My Links are basically as follows:
Company -->Project -->Timeentries, and also Project -->ProjectExpenses.

Is there any way around this issue in Crystal reports? All I need to for the SelectionFormula to be along the lines of ((TimeEntries.TimeEntryDate >= @DateFrom) and (TimeEntries.Timeentrydate <= @DateTo)) somehow tied in with the same formula, but instead limiting Expenses - but I want them not to influence each other!!
Also, the above Selection Formula prevents those projects that have no entries in that period from being included, even though I have a Left-Outer Join from Projects to TimeEntries!!!

I've also tried using a SP as the source for the TimeEntries table, to avoid the complications by filtering the TimeEbtries before, but there are huge hassles with passing dates from CR to SQLSRV...and when I finally managed (by pasisng in the dates as text, which is apparently the accepted 'fix'), then SQLSrv misinterpreted the date format I was passing in; and I could find nooo way around it!! SQL Server only accepts numerics for varchars that you want it to implicitly convert to Date...and it keeps mixing up D/M!!!

And as for using a Dataset as datasource, also doesnt seem to help matters....

I'm feel like I'm right at the end of my tether here, so please excuse my rant...!!

If anyone has any ideas, please give me a shout, would love to hear!!!

Thanks in advance

David Crone
Reply With Quote
Steven Cheng[MSFT]
Posts: n/a
      22nd May 2004
Hi David,

Thank you for posting to the newsgroups, unfortunately the resolution you
are seeking is out of the bounds of this technology and this newsgroup... I
think maybe you can attempt to locate a resolution from the Crystal support
guys. In the future for quicker response you may want to post questions of
this nature in the Crystal Reports' newsgroup.

#HOWTO: Get Help with Crystal Reports - International and U.S.



Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no

Get Preview at ASP.NET whidbey

Reply With Quote

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calling Crystal Reports in ASP.NET using the rptserver.asp given by Crystal Reports Peri Microsoft ASP .NET 1 21st Jul 2005 01:56 PM
Re: crystal reports 9 and crystal reports.net Boyd Ferris Microsoft VB .NET 0 3rd Sep 2004 08:03 PM
Re: crystal reports 9 and crystal reports.net Boyd Ferris Microsoft VB .NET 2 3rd Sep 2004 10:16 AM
crystal reports 9 and crystal reports.net jaYPee Microsoft VB .NET 0 3rd Sep 2004 06:41 AM
Crystal Reports.NET vs. Crystal Reports 9 B. Harwood Microsoft Dot NET 0 28th Nov 2003 10:04 PM




All times are GMT +1. The time now is 01:25 AM.