Results 1 to 10 of 10
  1. #1
    Join Date
    May 2007
    Location
    Lost in Translation
    Posts
    946

    Unanswered: Unviewable Years

    I have a report that pulls from one specific table, all the summary from all the past history.

    It uses a date select to pull the report.

    It works fine for 2006-2007 but will not pull data from 2003-2005.

    I can do a select on the table using that same SQL id, and it pulls all records.

    It works fine when I am in Developer, but it fails once I compile it.
    "Passion rebuilds the world for the youth. It makes all things alive and significant. ~Ralph Waldo Emerson

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well seeing as nobody else has taken a stab at this, me and my unwieldy skills at CR will have a go.

    Can we clarify a few things:
    1. Are you supplying the date ranges or pulling off all info at once?
      I ask because the following confused me
      Quote Originally Posted by Leibling
      all the summary from all the past history.
      It uses a date select to pull the report.
      It works fine for 2006-2007 but will not pull data from 2003-2005.
      I can do a select on the table using that same SQL id, and it pulls all records.
      So humour me
    2. If you're using criteria, how are you using it (GROUP BY year(date) etc)?

    Oh and fancy posting your relevant SQL as it stands anyhow?

    Sorry I can't be of much use Mo, but I'll give it my best shot!
    George
    Home | Blog

  3. #3
    Join Date
    May 2007
    Location
    Lost in Translation
    Posts
    946
    I am using a standard date:time for all the date fields I am pulling from. There is no difference, and the exact same query, using the same credentials and same context pulls perfectly from Query Analyzer and from the Crystal Report as long as we are using the CR Designer. It loses something when we compile it.

    The only criteria are the specified dates, and it's just the default date pull from CR. Nothing notable or special about it.

    Doing more delving into it, it strangely pulls from 9/2005-Present just fine, but cannot seem to pull 1/2004-8/2005 at all when it is compiled.

    {issues.issue_date}={?dates}

    Date:Time format is DD-MM-YYYY hh:mm:ss.mss

    Just the standard long date:time. But why it's pulling just those dates is weird.

    When I query the table using;

    SELECT * from issues where code = 'CYC' and issue_date < '01-01-2006'

    I get 6000+ rows.

    It's simply losing something in the compiler.
    "Passion rebuilds the world for the youth. It makes all things alive and significant. ~Ralph Waldo Emerson

  4. #4
    Join Date
    Oct 2007
    Posts
    10
    It sounds like you have two dates, but your SQL shows one date.

    When you run for 05-Present, are you saying issue_date >= '01-01-2005' , or are you using a date value for {Present} and saying issue_date between '01-01-2005' and {Present}?

  5. #5
    Join Date
    May 2007
    Location
    Lost in Translation
    Posts
    946
    When I do a between of dates, I still get the same result.

    select * from issues where code = 'CYC' and issue_date between '09-27-2004' AND '10-27-2004'

    Still gives me 187 rows. Which are the same 187 entries that show up in my report when I run it in the designer, and are lost when I compile the report.

    I have also tried this in Crystal Reports XI and have the same issue.
    "Passion rebuilds the world for the youth. It makes all things alive and significant. ~Ralph Waldo Emerson

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I know this is a stab in the dark but I'm stmped on this one...
    Is the problem in the date format?

    Try
    Code:
    BETWEEN '20042709' AND '20042720'
    George
    Home | Blog

  7. #7
    Join Date
    Oct 2007
    Posts
    10
    When you say "compile the report" , what is it that you are doing? If the SQL works and the designer works, then it sounds like that is the step that has something going wrong. Is it being deployed over the web, wrapped into an application? .

    Do you have your parameters directly displayed in the report anywhere? If so, are they showing up properly when you "compile the report"? And.. does your compiled version allow "Show SQL Query"?

    Also as a sidethought, are the only parameters for your report the parameters that are going to the SQL, or do you have extra parameters that don't connect to SQL? I ask since sometimes the parameters can get out of order and depending on the deployment scenario, the data connection may need the parameters to be in the same order as the SQL expects. For example, if "code" is a parameter as well, you want to make sure that your compiled version isn't sending something like "WHERE code = {date1} AND issue_date between 'CYC' and {date2}" I think betweens sometimes compile to "issue_date >= {date1} and issue_date <= {date2}" . If 'CYC' is sent to one of them, it may just remove one of the comparisons before sending the SQL. So.. see if you can somehow catch the SQL that is being sent from the compiled version, either in a debug mode or a SQL log/trace.
    Last edited by devbloch; 10-26-07 at 11:07.

  8. #8
    Join Date
    May 2007
    Location
    Lost in Translation
    Posts
    946
    I am using the Crystal Reports compiler. It's in CR 8 and previous versions. I am currently working with version 8.5.

    However, when I published the same report into CR XI, I get the same results.

    I can take out the 'CYC' select entirely and just use the dates and get the same result, so it's not a matter of where the parameter is.

    The SQL Trace shows me nothing out of the ordinary, it simply doesn't pull the data between the dates of Jan 2003 to August of 2005
    "Passion rebuilds the world for the youth. It makes all things alive and significant. ~Ralph Waldo Emerson

  9. #9
    Join Date
    May 2007
    Location
    Lost in Translation
    Posts
    946
    Ah ha.

    I figured it out.

    There was one hidden field that was pointing to a table that only went back to October of 2005. So when it was pulling data, it wasn't matching anything and was pulling random data in the designer, but wouldn't pull it in the compiled report. Once I tied the dates together in the two tables, it only pulled good data.

    I didn't design the report, and was under the impression that the tables were only linked for one formula and did not have anything to do with dates.

    Thanks for being my sounding board.
    "Passion rebuilds the world for the youth. It makes all things alive and significant. ~Ralph Waldo Emerson

  10. #10
    Join Date
    Oct 2007
    Posts
    10
    Oh! Good!

    I hadn't even thought you might be using linked tables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •