[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-10-21
Medium Priority
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 101

Accepted Solution

mlmcc earned 2000 total points
ID: 37007670
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 35

Expert Comment

ID: 37010795
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

ID: 37034715
Awesome! It worked. Thanks very much.

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 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