Solved

Using Dates to Filter Crystal Reports 2010 with VS2010 and c#

Posted on 2012-03-21
10
3,402 Views
Last Modified: 2012-03-21
I have the following code in my submit button to filter the data that is shown in the crystal report 2010/vs 2010 c#.

protected void Button_Submit_Click(object sender, EventArgs e)
    {
          DateTime startdate = Convert.ToDateTime(TextBox_StartDate.Text);
          DateTime enddate = Convert.ToDateTime(TextBox_EndDate.Text);string factory = ddlFactory.SelectedValue;
          string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}=" + "'" + factory + "' and " + "{VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [" + "'" + startdate + "' TO " + "'" + enddate + "']";

         crystalReportViewer.SelectionFormula = selectFormula;
         crystalReportViewer.Visible = true;
         crystalReportViewer.DataBind();
    }

Open in new window


This gives me the following error:

A date-time is required here. Details: errorKind
Error in File emptymixers {E2F90D89-36FB-4550-99C2-64409FED7E8B}.rpt: Error in
formula Record_Selection: '{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='01' and
{VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN ['3/20/2012 6:00:00 PM' TO
'3/21/2012 6:00:00 AM']' A date-time is required here. Details: errorKind

The dates look formatted correctly, but asp.net or CR is not recognizing them as dates.
Thanks for any help or direction you can provide.
0
Comment
Question by:Hers2keep
  • 5
  • 3
  • 2
10 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 37748321
WHy are you putting the ' ' around the datetime?
Crystal takes that to be a string.
Try removing the ' ' in the datetime part

Rather than changing the selection formula have you tried using parameters to pass the values in?

mlmcc
0
 
LVL 19

Expert Comment

by:GJParker
ID: 37748348
crystal needs to see the statdate and enddate as a datetime data type where youar currently using a string.

Try replacing your selectformula  with this

"{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}=" + "'" + factory + "' and " + "{VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [CDATETIME("+  startdate + ") TO CDATETIME(" +  enddate + ")]";
0
 

Author Comment

by:Hers2keep
ID: 37748380
It looks like you are right - I didn't even catch that I was turning it back into a string. I'm trying to avoid using parameters because I have to use these same Crystal Reports in another application that won't recognize the parameters, so I'm filtering the reports instead. I modified my selectFormula to this:

    protected void Button_Submit_Click(object sender, EventArgs e)
    {
        DateTime ds = Convert.ToDateTime(TextBox_StartDate.Text);
        DateTime de = Convert.ToDateTime(TextBox_EndDate.Text); 
        string factory = ddlFactory.SelectedValue;
        string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='" + factory + "' and  {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [" + ds + " TO " + de + "]";
        crystalReportViewer.SelectionFormula = selectFormula;
        crystalReportViewer.Visible = true;
        crystalReportViewer.DataBind();
    }

Open in new window


Now I'm getting this error:

The ] is missing. Details: errorKind Error in File emptymixers {3B587F13-3E1F-42FD-9C59-95460B31DE2B}.rpt: Error in formula Record_Selection: '{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='01' and {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [3/21/2012 6:00:00 AM TO 3/21/2012 12:00:00 PM]' The ] is missing. Details: errorKind

This doesn't make sense to me because it looks like I've got it in there?
0
 

Author Comment

by:Hers2keep
ID: 37748405
GJParker - so I tried your solution and now my code is this:
    protected void Button_Submit_Click(object sender, EventArgs e)
    {
        DateTime ds = Convert.ToDateTime(TextBox_StartDate.Text);
        DateTime de = Convert.ToDateTime(TextBox_EndDate.Text); 
        string factory = ddlFactory.SelectedValue;
        string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='" + factory + "' and  {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [CDATETIME(" + ds + ") TO CDATETIME(" + de + ")]";
        crystalReportViewer.SelectionFormula = selectFormula;
        crystalReportViewer.Visible = true;
        crystalReportViewer.DataBind();
    }

Open in new window

and I'm getting the same type of error but now it's complaining about the closing paren.
The ) is missing. Details: errorKind Error in File emptymixers {77C9A833-E1C3-4CD8-BBE0-2FD8CA476D52}.rpt: Error in formula Record_Selection: '{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='01' and {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [CDATETIME(3/21/2012 6:00:00 AM) TO CDATETIME(3/21/2012 12:00:00 PM)]' The ) is missing. Details: errorKind

This is just weird. In both cases, the closing bracket or paren is there.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 37748424
What happens if you paste thsi directly in to the report ?

{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='01' and {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [CDATETIME(3/21/2012 6:00:00 AM) TO CDATETIME(3/21/2012 12:00:00 PM)]
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Hers2keep
ID: 37748440
I still get "The ) is missing."
0
 

Author Comment

by:Hers2keep
ID: 37748468
If I just use the Crystal Reports select record formula and pick "between dates" it gives this: {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} in DateTime (2012, 03, 21, 00, 00, 00) to DateTime (2012, 03, 21, 10, 30, 00) . So I origianlly tried this:
        DateTime ds = Convert.ToDateTime(TextBox_StartDate.Text);
        DateTime de = Convert.ToDateTime(TextBox_EndDate.Text); 
        string factory = ddlFactory.SelectedValue;
        string startdate;
        string enddate;
        startdate = ds.Year + "," + ds.Month + "," + ds.Day + "," + ds.Minute + "," + ds.Second + "," + ds.Millisecond;
        enddate = de.Year + "," + de.Month + "," + de.Day + "," + de.Minute + "," + de.Second + "," + de.Millisecond;
       string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='" + factory + "' and  {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [" + startdate + " TO " + enddate + "]";

Open in new window

and that gives me this error:
A date-time is required here. Details: errorKind Error in File emptymixers {97C75856-8EA2-4AC8-9E61-0B04B45346BF}.rpt: Error in formula Record_Selection: '{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='01' and {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [2012,3,21,0,0,0 TO 2012,3,21,0,0,0]' A date-time is required here. Details: errorKind
and it doesn't pull the minutes, seconds and milliseconds correctly. They all come out as zeroes and they are not really.
0
 
LVL 19

Accepted Solution

by:
GJParker earned 500 total points
ID: 37748517
you shoudl be using Hour, minute and seconds rather than minute, seconds and milliscends and you also still need to tell CR to convert to a datetime, try this


 startdate = ds.Year + "," + ds.Month + "," + ds.Day + "," + ds.hour + "," + ds.minute + "," + ds.Second;
enddate = de.Year + "," + de.Month + "," + de.Day + "," + de.Hour + "," + de.Minute + "," + de.Second;
string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='" + factory + "' and  {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [CDATETIME(" + startdate + ") TO CDATETIME(" + enddate + ")]";
0
 

Author Closing Comment

by:Hers2keep
ID: 37748545
Absolutely perfect!!!! I swear you can look at something until you're blue in the face and still miss simple things (i.e. didn't have Hour in there at all).

Final working code:
    protected void Button_Submit_Click(object sender, EventArgs e)
    {
        DateTime ds = Convert.ToDateTime(TextBox_StartDate.Text);
        DateTime de = Convert.ToDateTime(TextBox_EndDate.Text); 
        string factory = ddlFactory.SelectedValue;
        string startdate;
        string enddate;
        startdate = ds.Year + "," + ds.Month + "," + ds.Day + "," + ds.Hour + "," + ds.Minute + "," + ds.Second;
        enddate = de.Year + "," + de.Month + "," + de.Day + "," + de.Hour + "," + de.Minute + "," + de.Second;
        string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='" + factory + "' and  {VW_MIXER_EMPTY_TIMES_ALL.DATETIME_DUMP} IN [CDATETIME(" + startdate + ") TO CDATETIME(" + enddate + ")]";

        crystalReportViewer.SelectionFormula = selectFormula;
        crystalReportViewer.Visible = true;
        crystalReportViewer.DataBind();
    }

Open in new window

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37748762
Try


       string selectFormula = "{VW_MIXER_EMPTY_TIMES_ALL.FACTORY}='" + factory + "' and  {VW_MIXER_EMPTY_TIMES_ALL.DATETIME IN DateTime(" + startdate + ") TO DateTime(" + enddate + ")";

mlmcc
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now