cancel
Showing results for 
Search instead for 
Did you mean: 

Two date fields in one report

Former Member
0 Kudos

dear all,

I have created a report which filters records according to statuses. The problem I am having is that two different distinct count fields need to filter by using two different date fields from two different tables. For example

DistinctCount() - this needs to look into Order header date created field

whereas

DistinctCount() - this needs to look into Lab Credit Date created field.

Order header is a table, date created is a date field

LabCredit is a table, date created is a date field

My question is, how can I use both date fields to filter my reporting records.

I have used the following under Report Selection formula but it produces results from only Lab Credit Field and 0s from the FOC field.


{lab_credits.credit_date} in currentdate to currentdate OR
{order_header.date_entered} in currentdate to Currentdate

There is something wrong somewhere and I don't know where.

Any ideas?

Regards

Jehanzeb

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

sorted

Former Member
0 Kudos

Please use this formula to compare both the date fields with current date

If you want either of the condition true.

{lab_credits.credit_date} = Currentdate OR

{order_header.date_entered} = Currentdate.

If you want both the conditions true

{lab_credits.credit_date} = Currentdate AND

{order_header.date_entered} = Currentdate.

Regards,

Sastry

Former Member
0 Kudos

The main reason why i am using these is to get combined results of both totals. however both lie on two different dates form two different fields.

So I guess I need to use AND in between the two dates to make it work.

I am going to try it now.

Thanks

Regards

Jehanzeb

Former Member
0 Kudos

Ok now after using AND, I get 0s in both.

It doesn't seem to work when I choose AND however it produces only one set of results when i choose OR.

Former Member
0 Kudos

Can you please remove the record selection and see whether you are getting both the value in your report.

If you are not getting both values please check the database links to check the join type.

regards,

Sastry

Former Member
0 Kudos

Running now without any date filteration...it is counting all the records, i guess all the records from the year.

Ok it has produced all the records, from both record fields.

However one field has alot of data and other field has only 1s and 2s......the main thing is that it has produced the results in both running totals.

Regards

Jehanzeb