VB Crystal Report >> Select Expert problem

by Dr. IEM » Sun, 09 Apr 2006 11:37:25 GMT

Hello,

I have created reports using this tool. What I've noticed is that if you use
a date/time field with the "is between" option, all the records are not
retrieved. For example, if the last data is 1/14/2004, you may or may not
get any records that have 1/14/2004 returned. It seems to depend on the time
in the date/time field. Other than enter the date AFTER the date that you
really need, in this example enter 1/15/2004, is there another way to get CR
XI to return records on the last date that you enter?

--
Indera
* * * * * * * * * *
Don't just live life.
Live life well.





VB Crystal Report >> Select Expert problem

by Wayne Wengert » Sun, 09 Apr 2006 20:06:15 GMT


Indera;

I am not an expert and you'll probably get more advice but think you have
the right idea of the problem. On the end date, set the time portion to
something like "23:59:59" to include that whole day.

HTH

Wayne








VB Crystal Report >> Select Expert problem

by Iain Sharp » Mon, 10 Apr 2006 16:16:29 GMT

On Sat, 8 Apr 2006 23:37:25 -0400, "Dr. IEM"



Use date({field}) is between {param1} and {param2} where the two
parameters are date fields rather than date time.

Iain


Select Expert problem

by Dixon » Mon, 10 Apr 2006 16:53:01 GMT

If u r using a date field in a grouping condition then u need to
specify on what basis u need to group these fields....like in Annually,
Biweekly, Monthly, Daily.......or wat ever......

Ive done this in code as below....

Cr.AddGroup intGroupAdd, Cr.Database.Tables(1).Fields(intField),
crGCDaily, crAscendingOrder



Select Expert problem

by Dr. IEM » Thu, 20 Apr 2006 08:55:20 GMT

thank you wayne. i was thinking i couldn't count the records (smile)

--
Indera
* * * * * * * * * *
Don't just live life.
Live life well.




: Indera;
:
: I am not an expert and you'll probably get more advice but think you
have
: the right idea of the problem. On the end date, set the time portion to
: something like "23:59:59" to include that whole day.
:
: HTH
:
: Wayne
:


: > Hello,
: >
: > I have created reports using this tool. What I've noticed is that if you
: > use
: > a date/time field with the "is between" option, all the records are not
: > retrieved. For example, if the last data is 1/14/2004, you may or may
not
: > get any records that have 1/14/2004 returned. It seems to depend on the
: > time
: > in the date/time field. Other than enter the date AFTER the date that
you
: > really need, in this example enter 1/15/2004, is there another way to
get
: > CR
: > XI to return records on the last date that you enter?
: >
: > --
: > Indera
: > * * * * * * * * * *
: > Don't just live life.
: > Live life well.
: >
: >
: >
:
:




Select Expert problem

by Dr. IEM » Thu, 20 Apr 2006 08:55:24 GMT

hi iain,

are you saying that i can change a date/time field to a date field on the
fly?

--
Indera
* * * * * * * * * *
Don't just live life.
Live life well.




: On Sat, 8 Apr 2006 23:37:25 -0400, "Dr. IEM"

:
: >Hello,
: >
: >I have created reports using this tool. What I've noticed is that if you
use
: >a date/time field with the "is between" option, all the records are not
: >retrieved. For example, if the last data is 1/14/2004, you may or may not
: >get any records that have 1/14/2004 returned. It seems to depend on the
time
: >in the date/time field. Other than enter the date AFTER the date that you
: >really need, in this example enter 1/15/2004, is there another way to get
CR
: >XI to return records on the last date that you enter?
:
: Use date({field}) is between {param1} and {param2} where the two
: parameters are date fields rather than date time.
:
: Iain




Select Expert problem

by Dr. IEM » Thu, 20 Apr 2006 08:55:31 GMT

thank you dixon. i was hoping that i could do this without writing code.

--
Indera
* * * * * * * * * *
Don't just live life.
Live life well.




: If u r using a date field in a grouping condition then u need to
: specify on what basis u need to group these fields....like in Annually,
: Biweekly, Monthly, Daily.......or wat ever......
:
: Ive done this in code as below....
:
: Cr.AddGroup intGroupAdd, Cr.Database.Tables(1).Fields(intField),
: crGCDaily, crAscendingOrder
:




Select Expert problem

by Iain Sharp » Thu, 20 Apr 2006 17:03:23 GMT

On Wed, 19 Apr 2006 20:55:24 -0400, "Dr. IEM"



Pretty much,

I don't know if there's a performance hit because the database server
can't process the where clause and Crystal has to do it for you.

date({datetime field}) will return a date only value, which is
therefore testable against date only parameters.

Iain


Similar Threads

1. Crystal 8.5 - SQL generated by 'Select Expert' loses logical meaning

Hello,

I have created the following formula in Crystal 8.5:

({S_SRV_REQ.X_QA_HELD_DATE} in {?Start Date} to {?End Date})
Or
(ISNULL({S_SRV_REQ.X_QA_HELD_DATE}) And
ISNULL({S_SRV_REQ.X_HELD_FLAG}))

i.e. this should return True if EITHER of the bracket enclosed AND
statements are True.

This is translating to the following ODBC (Oracle) SQL:

(S_SRV_REQ."X_QA_HELD_DATE" >= {ts '2004-07-14 00:00:00.00'} AND
S_SRV_REQ."X_QA_HELD_DATE" < {ts '2004-07-15 00:00:00.00'} OR
S_SRV_REQ."X_QA_HELD_DATE" IS NULL AND
S_SRV_REQ."X_HELD_FLAG" IS NULL)

As you can see, Crystal is stripping out the brackets surrounding the
two separate AND clauses. The correct SQL should be:

((S_SRV_REQ."X_QA_HELD_DATE" >= {ts '2004-07-14 00:00:00.00'} AND
S_SRV_REQ."X_QA_HELD_DATE" < {ts '2004-07-15 00:00:00.00'}) OR
(S_SRV_REQ."X_QA_HELD_DATE" IS NULL AND
S_SRV_REQ."X_HELD_FLAG" IS NULL))

The two SQL statements have different logical meanings and the results
of the report are incorrect:

should be:

(x AND y) OR (a AND b)

Crystal is evaluating this as:

x AND y OR a AND b = ((x AND y) OR a) AND b

Can anyone tell me how to force Crystal to translate this formula
correctly, without having to resort to manually updating the SQL
(which works, incidentally) and thereby losing the benefits of the
Select Expert?

Cheers!

mroshaw

2. Date Selection using Report Select Expert (V8) - VB Crystal Report

3. programmatically modify value in select expert.

Hi All,

I have this program that can let the user print the crystal report from my
asp.net application. It works find with report without need to alter query
value.

but some reports need to be compilie based on different values (specify in
SELECT EXPERT) each time it is run. How do i write my codes to do that?

Many thanks in advance for any help

below is my code for the simple export to PDF file:

----------------------------------------------------------------------------
----------------------------------------------------------------------------
--------------------------------------------
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.ReportSource
Imports CrystalDecisions.Shared
Imports System.IO

Public Class ReportPrinter
    Inherits System.Web.UI.Page

    Public crReportDocument As ReportDocument
    Public crExportOptions As ExportOptions
    Public crDiskFileDestinationOptions As DiskFileDestinationOptions
    Public Fname As String

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    '<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()

    'End Sub

    Public Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        '   InitializeComponent()

        crReportDocument = New ReportDocument()


crReportDocument.Load("C:\Inetpub\wwwroot\CrystalWeb\CrystalReport\QtyOnHand
QtyAvailable.rpt")

        Fname = "C:\Inetpub\wwwroot\CrystalWeb\Download\" &
Session.SessionID.ToString & ".pdf"

        crDiskFileDestinationOptions = New DiskFileDestinationOptions()
        crDiskFileDestinationOptions.DiskFileName = Fname

        crExportOptions = crReportDocument.ExportOptions
        With crExportOptions
            .DestinationOptions = crDiskFileDestinationOptions
            .ExportDestinationType = ExportDestinationType.DiskFile
            .ExportFormatType = ExportFormatType.PortableDocFormat
        End With

        Try

            crReportDocument.Export()

            Response.ClearContent()
            Response.ClearHeaders()
            Response.ContentType = "application/pdf"
            Response.WriteFile(Fname)
            Response.Flush()
            Response.Close()

            System.IO.File.Delete(Fname)

        Catch err As Exception
            Response.Write("<BR><BR>")
            Response.Write(err.Message.ToString)
            Response.Write("<BR><BR>")
            Response.Write(err.StackTrace.ToString)
        End Try
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
    End Sub

End Class
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--------------------------------------------

Regards,
Bernard Goh
IT Administrator
Success Electronics & Transformer Manufacturer Sdn Bhd
No.5 & 7,Jalan TSB 8
Taman Industri Sg. Buloh
47000 Sg.Buloh
Selangor Darul Ehsan


4. Summary of data not in range of SELECT EXPERT - VB Crystal Report

5. Formula Field Not Available in Select Expert

I am fairly new to Crystal Reports 10.  I created a Formula Field
called Duplicates that checks an ID number in one record to the ID
number in the next record and if they match it is marked as a duplicate
otherwise it isn't.  Now I want to pull only those records that are NOT
duplicates, but when I click on select expert, my formula field is not
available.  Am I missing something or do I need to do this a different
way.

6. Select expert - VB Crystal Report

7. Select Expert and SQL Query Conflict?

I'm a relative newbie with Crystal Reports, so any help/guidance
anyone can provide is greatly appreciated.

I have created a report that seems to work properly except for one
issue. I used the Select Expert to identify a Customer Name and Date
Range for the report (for example, ABC Corporation, 2nd Quarter 2004)
after creating it. I then ran the report and everything
worked/displayed fine. Now I go into the same report definition and go
back to the Select Expert to run the report for another customer (for
example, XYZ Corporation, 2nd Quarter 2004). Once I close the Select
Expert window, Crystal Reports displays the following message:

"The SQL Query has previously been manually overridden (refer to :
Database Menu - Show SQL Query). Your changes to the selection formula
and the overridden SQL query will both be used to generate records for
your report."

If I open the Select Expert window again, I see my new data (XYZ
Corporation, 2nd Quarter 2004), but if I open the SQL Query, I see the
old data (ABC Corporation, 2nd Quarter 2004). It seems as if the
information from the Select Expert is not updating the SQL Query. I
can manually edit the SQL Query to include the correct information
from the Select Expert, but I shouldn't have to do that.

Has anyone seen this before? Any ideas or suggestions?

Thanks in advance for your help,
Yellowbird

8. Select Expert disagrees with Selection Formula - VB Crystal Report