Crystal Reports Query

Discussion in 'Microsoft ASP .NET' started by Guest, May 21, 2004.

  1. Guest

    Guest 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
     
    Guest, May 21, 2004
    #1
    1. Advertisements

  2. 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
     
    Steven Cheng[MSFT], May 22, 2004
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Sending SQL query to Crystal Reports

    Guest, Mar 31, 2004, in forum: Microsoft ASP .NET
    Replies:
    0
    Views:
    198
    Guest
    Mar 31, 2004
  2. Peri
    Replies:
    1
    Views:
    789
    Guest
    Jul 21, 2005
  3. Learner
    Replies:
    1
    Views:
    894
    Richard Dudley
    Jan 11, 2006
  4. Rod
    Replies:
    2
    Views:
    1,249
  5. Replies:
    2
    Views:
    538
Loading...

Share This Page