Reports with Dates

rbuser

My database contains contact information (i.e. name, phone, address,
history, etc...). I would like to have a report that gets a list of the
last week's worth of histories for a particular contact.

The part that I am having difficulty with is the selection of histories. I
can go in to the Query Wizard and create a search criterion that specifies
the histories based on a date; however, that date would then be hard coded.
Is there any way I can create a report that would use the date the report
was run.

In other words, I need a report that selects all the histories within the
last seven days of the current date (the day the report is run). Any
suggestions?

I investigated this once before, and I remember someone telling me that I
could create a report that, when run, would ask for the value to search on.
The only problem with that is that the end user would have to know how to
format the date for their SQL provider (we support MSSQL, Oracle, Access,
and Pervasive).

Another possible solution would be to create a report that simply displayed
the last 5 or 10 or whatever number of histories. Would something like that
be possible?

Any suggestions would be greatly appreciated. Thanks in advance.


Donovan Rogers

digitalmetaphors

Use autosearch and let your user choose the last week. You can default the
autosearch value to be the Now function result. Also, you can create a
custom autoserach dialog. There is a custom autosearch form demo in the
autosearch demoes of the installation. We show the date time picker control
in this demo. To default the autosearch field value to Now, in the
Report.OnAutosearchDialogCreate event, call Report.CreateAutoSearchField
passing DateTimeToStr(Now). Otherwise, if you have DADE created autosearch
fields, then you can call this after the report loads and the search
criteria have caused the autosearch fields to be created.

ppReport1.AutoSearchFields[0].SearchExpression := DateTimeToStr(Now);



Cheers,

Jim Bennett
Digital Metaphors

rbuser

Jim,

Thanks for the information. We were able to get that to work pretty well. My only concern is that our users might struggle with entering the date in the correct format. We looked at the example and discovered that Calc tab. We realized that we have the enterprise version so we started playing with that. We are considering writing some code in the DetailBeforeGenerate function which would eliminate the histories we don't want. First, is this a good way to solve the problem? I realize that the SQL statement would be returning all of the histories, and we would be filtering them out. I know it is better to let the DB handle that; however, I know the date format will confuse a lot of people.

I wanted to see if you had any additional suggestion based on this new information. I am sure I didn't make it clear that we have the Enterprise version. Anyway, thanks for all the help.


Donovan Rogers

digitalmetaphors

If you want to stop the detail bands at a specified position in the data,
then use the datapipeline properties. These are RangeBegin, RangeEnd, and
RangeEndCount. You are using DADE, then you'll have to reference
Report.Datapipeline to get at these properties. Access this in the
Report.Template.OnLoadEnd event handler you'll have to assign at runtime.
Here I just used a button click.

uses
ppDBPipe, ppTypes;

procedure TForm1.Button1Click(Sender: TObject);
begin
TppDBPipeline(ppReport1.DataPipeline).RangeEnd := reCount;
TppDBPipeline(ppReport1.DataPipeline).RangeEndCount := 5;

ppReport1.Print;
end;

Cheers,

Jim Bennett
Digital Metaphors