Show filtered date on report

kolaboy · 2007-10-18

Hi Expert.
I am trying to show date that have been filter to show on a report that is picking informations from the Cust. ledger Entry table. There are two dataitems: Customer and Cust. ledger Entry.
The request form is the customer. But there is no way i can filter posting date in the cust. ledger entry table because it is not part of the fields in the customer table.

i want the report to show the posting date filtered on the report and show on the customer header in the section designed and filter only transactions as at that filtered date.
Any idea please.
Thanks

Albertvh · 2007-10-18

Hi

In the Customer PreDataItem put the following code

IF "Cust. Ledger Entry".GETFILTERS <> '' THEN
FilterText := 'Filter: ' + "Cust. Ledger Entry".GETFILTERS;

where FilterText is a variable of type text

create a new heading section for customer and insert a textbox
with SourceExpr FilterText

in the PreSection of this new header put this code

CurrReport.SHOWOUTPUT := (CurrReport.PAGENO = 1) AND (FilterText <> '');

Albert

M._Eleveld · 2007-10-18

Add the field 'Date Filter' to the required filterfields on the Customer table dataitem.
Then link the Cust. Ledger Entry to the Customer table by using the property 'DataItemLink' in the Cust. Ledger Entry.
In the DataItem Link-form enter the following :

Field               Reference Field
Customer No.        No.
Posting Date        Date Filter

In the section where you would like to show the filtered date add a textbox with the SourceExpression 'GETFILTER("Date Filter")'

kolaboy · 2007-10-19

Hi

I have tested your codes and they seems to be doing what i wanted. there is a problem.
When you put a date, it only show all the customers with only transcations of that particular date filtered. e.g if you filter 02/28/07, it gives you the transcations of only 02/28/07.
I wanted the report to show all the customers with there transcations from 01/10/07 up to the date that is filtered i.e 02/28/0, not only the date that is filter. Also there shouldn't be a date beyong the filtered date say 02/28/07.
What is the modification going to be to acheive this?
Thanks

Savatage · 2007-10-19

01/10/07..02/28/07

kolaboy · 2007-10-19

I you use the range, the range shows on the report as well. this is not what i want. I want only the end date to show on the report i.e 02/28/07.

It should read as at 02/28/07 not as at 01/01/07...02/28/07
Any idea Pls.
Thanks.

Savatage · 2007-10-19

You want to use a range and then use getfilters but not have the complete filter show. Is that correct?

I guess you could always just throw a blank text box over the section of the text box(that shows the filter) that shows the first date.
or format the FilterText & delchr the beginning of the field or copystr just the end.

you know what i mean ?

kolaboy · 2007-10-22

that exactly what i want. I want only the end date to show.
Can you please explain the steps to me because i don't understand what you mean by:

I guess you could always just throw a blank text box over the section of the text box(that shows the filter) that shows the first date.
or format the FilterText & delchr the beginning of the field or copystr just the end.

Please can you explain in step form for me to understand. Am not that experience.
Thanks.
MLS

kolaboy · 2007-10-29

hi Experts,
I am still tryin g to see how to avoid he start date when a range is taken for the date filter but no luck yet.
can anyone help on suggestions Please.
Thanks.

Savatage · 2007-10-29

to show your filters

OnPreReport() you probably have something like
ItemFilter := Item.GETFILTERS;

if you only CUT OUT what you need it should work.
for example - this will give you the characters starting at 5 & going to 15
ItemFilter := COPYSTR(Item.GETFILTERS,5,15);

I think this should work for you, you just need to figure out when to start & when to stop the COPYSTR.

kolaboy · 2007-10-31

I am someone to tell me how to Modify this code

IF "Cust. Ledger Entry".GETFILTERS <> '' THEN
FilterText := 'Filter: ' + "Cust. Ledger Entry".GETFILTERS;

this is what i am using to not show the start date when a range is filtered
eg. If i filter 020207...030307, 02/02/07..03/03/07 shows on the report. i want only 03/03/07 t0 show i.e the end date.

also how can i format the date say 03/03/07 to 3rd March, 2007 form/
Thanks

CobaltSS · 2007-10-31

:shock:

Try this:

IF "Cust. Ledger Entry".GETFILTERS <> '' THEN
FilterText := 'Filter: ' + COPYSTR(Item.GETFILTERS,5,15);

cheers,

Savatage · 2007-10-31

You have to play around with it

Using Report 1406 as an example change the OnPreReport()

//This Cuts out the Text of the 2nd date

BankAccFilter := COPYSTR("Bank Account".GETFILTERS,24,15);

//Using evaluate I will convert the date text to a date format

EVALUATE(DateVariable, BankAccFilter);

//using format I can change the date to October 30, 2007

BankAccFilter := FORMAT(DateVariable,0,4);

Albertvh · 2007-11-01

Hi Kolaboy

to get the last date use the following code if you are sure that the user
enters a range ie 010108..310108

EndDate := "Cust Ledg. Entry".GETRANGEMAX("Date Filter");

where EndDate is a Date field

Albert

kolaboy · 2007-11-01

I have tried all your codes but still no luck with them.
Right now

IF "Cust. Ledger Entry".GETFILTERS <> '' THEN
FilterText := 'Filter: ' + "Cust. Ledger Entry".GETFILTERS;

and the settings:

Field Reference Field
Customer No. No.
Posting Date Date Filter

is working with the range selection, but it is only displaying records say went you filter 02/03/06, the records in the Cust. Ledger entry with posting date 02/03/06 appears and if you filter 010107....02/03/06, the records in the cust. Ledger entry with date from 01/01/07 ...02/03/06 inclusive will appear.
what i wanted is if lets say a date e.g 02/03/07 is filtered, the report should display records from the Cust.Ledger Entry table from 01/01/07 up to 02/03/07 even if you did not put a date range.

and the report should show 02/03/07 on top of your report as the date filter or as at 02/03/07 .

I tried as an alternative way to continue filtering but i don't want the start date to show on the report, only the end date i.e if the range 01/01/07...02/03/07 is filtered, the report should show only 20/03/07 not 01/01/07...02/03/07. I hope i have made myself clear?
Thanks

[/quote]

Savatage · 2007-11-01

Did you even try this to see if this provides the desired results?

Savatage wrote:
You have to play around with it
Using Report 1406 as an example change the OnPreReport()
//This Cuts out the Text of the 2nd date
BankAccFilter := COPYSTR("Bank Account".GETFILTERS,24,15);
//Using evaluate I will convert the date text to a date format
EVALUATE(DateVariable, BankAccFilter);
//using format I can change the date to October 30, 2007
BankAccFilter := FORMAT(DateVariable,0,4);

You made another statment about perhaps just entering an END DATE and having it automatically filter from 01/01/07 to your END DATE.

You you want a request form that has a box where you enter the end date?

These are two different things than just simply taking the report filters that appear at the top and converting them to a full text date.

kolaboy · 2007-11-01

I will try to play arround with it, but is there any problem of entering a date and the report filter from 01/01/07 to the date filtered. Hardcode sort of.

I think request form will also do, but with only the end date show on the report. I prefer the first method if there will be a breakthrough.
Thanks

Eugene · 2008-01-17

why dont you use GETRANGEMAX instead ?

kolaboy · 2008-01-17

Sorry this was solved longtime. I should have indicated it.
Thanks for you ideas they help me greatly.

Howdy, Stranger!

Quick Links

Categories

Show filtered date on report

Answers