Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2012-03-21
10
3,426 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

839 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