Date Formula

[Gizmo0917]

I need to create a Date formula so that I can use to pull up the right data in my report. I am trying to pull up a report that has a date field in for a report we will call "ProdMonthly Total Report" for Jan 1 2004 -Jan31 2004. The report has the follwoing fields:

Date
Prod 1
Prod 2
Prod 3
prod 4
prod 5

I tried setting up the Parmeters and it is still not pulling up the right information. I am thinking if I create a formula that will say let the "Date" equal the ProdMonthDate Field that I will be able to pull up the accurate information. But how do I do this? or are there any other suggestions on what I could do?

 

[synapsevampire]

Create a date range parameter:

Insert->Field Object->Right click parameters and select New

Make it a date type and select range value.

Now use the record selection formula:

Report->Edit Selection Formula->Record

{table.date} = {?MyDateParameter}

The report will prompt for the dates and will limit the rows.

-k

 

[Gizmo0917]

I have two dates a StDate and EndDate that need to pull from the same field in my Prod Table so when I create the params which I did and drop them in my report. It only lets me create a formula for the one StDate field I tried this

{PFTTotals.Date}=["{?PFTReport Date}"and
"{?PFTReportDateEnd}"]

and it is giving me an error. What to do?

Thanks!

 

[SmpTraining]

Try

{PFTTotals.Date} in {?PFTReport Date} to {?PFTReportDateEnd}

Steve Phillips, Crystal Consultant

 

[Gizmo0917]

Thanks Steve that works Great!!

 

[synapsevampire]

You don't need 2 date parameters was the point, and most prefer the look and feel of the date range prompt.

Just because you've already created them doesn't mean that you should use them (though I often build 2 date parameters, but that's for allowing user friendly prompts for last month, last quarter, etc.)

-k

 

[Gizmo0917]

I just have one more question for this report. The products are Checked. The checkmark value is coming as True and if there is not checkmark then the value is false. I am trying to get a Grand total for each True but it returns a bold True. How can I get it to do this if true=1 if not then 0 and then give me a ground total for each product used?

 

[Gizmo0917]

Sum (IIf([PFT.prodone]=True,1,0))

I tried to create a formula like the one above and it gives me an error.

 

[synapsevampire]

Consider sharing what the error is...

Use a Running Total field and place your criteria in the evaluate use a formula:

[PFT.prodone]=True

-k

 

[SmpTraining]

Create a formula:

if {PFT.prodone} then 1 else 0

Place it in the report and create a Grand Total on it.

Steve Phillips, Crystal Trainer/Consultant

http://www.smptraining.co.uk

 

[Gizmo0917]

This works great!!! Thanks so much!