How to use a table with stored dates and period info as the start and end dates for selection

Posted on 2011-10-21
Last Modified: 2012-05-12
Using Crystal XI, I am trying to use a "reporting periods" table to designate the start and end dates for use in the data selection. The table will list the reporting periods,(ex. "Aug 2011") and the start & end dates for that period in a row. I am using the reporting period in a dynamic parameter. So the user would select the reporting period. How do I incorporate the dates associated with that reporting period as the date range in the data selection?

Thank you!
Question by:Goosie1
    LVL 100

    Accepted Solution

    I assume the data is in another table.

    Try something like this
    Add both tables to the report
    Ignore the error of unjoined and multiple starting points

    Add a parameter to select the record from the period table

    In the Crystal select expert
    {?Period} = {PeriodTable.PeriodField}
    {PeriodTable.StartDate} <= {DataTable.Date}
    {DataTable.Date} <=  {PeriodTable.EndDate}

    LVL 34

    Expert Comment

    FWIW, we handled this by using stored procedures for our reports and letting them do the work.  The SP's had period parameters and would call functions that would read the period date table and return the corresponding starting or ending date, and those dates were then used to select the records in the SP.

     If you don't want to use SP's, the only alternative that I can think of to mlmcc's suggestion would be to forget the "reporting periods" table and convert the period to dates manually in the record selection formula.  You'd lose the flexibility of having the dates defined in a table, but if your periods are consistent (eg. always based on the month), then maybe that doesn't really matter.


    Author Closing Comment

    Awesome! It worked. Thanks very much.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    761 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

    11 Experts available now in Live!

    Get 1:1 Help Now