Crystal Reports Query

[Guest]

Hi

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

Companies
Projects
TimeEntries
ProjectExpenses

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

 

[Steven Cheng[MSFT]]

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.
http://support.microsoft.com/?id=100368

Thanks.

Regards,

Steven Cheng
Microsoft Online Support

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

Get Preview at ASP.NET whidbey
http://msdn.microsoft.com/asp.net/whidbey/default.aspx