Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2012-03-21
10
Medium Priority
?
3,482 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:Carla Romere
[X]
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
  • 5
  • 3
  • 2
10 Comments
 
LVL 101

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:Carla Romere
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Carla Romere
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:Carla Romere
ID: 37748440
I still get "The ) is missing."
0
 

Author Comment

by:Carla Romere
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 2000 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:Carla Romere
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 101

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

636 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