Crystal formula help 1

[vjfur]

How do I write a formula that would show total sales for only 1 day out of a particular month? I do not want to use the select expert because I want to have this same formula(or a variation of it) located in multiple places pulling different days on the same report?
I am not skilled with date formulas, can someone help?

 

[lbass]

Why not insert a group on the date field, on change of day, and then insert a summary of sales? You might want to select a specific month in your selection formula.

-LB

 

[vjfur]

I am trying to create a manual crosstab report, I am already grouping by vendor so I am trying to figure out how to write the formula to just pull out the sum of sales by each date of the month.
My problem is that I am not familiar with how to write the formula itself... do I use the Date function or Date time. I know that if I write a formula for the month:
example:
If month ({Orders.Orderdate}) = 1 Then {Orders.OrderAmount})
Else 0
I would only get information for the Month of January
But how do I write it for say just Orders on January 1st?

 

[lbass]

If you select the month and year in the record selection formula by using:

{table.date} in date({?year},{?month},1) to dateserial({?year},{?month}+1,1)-1

Then you can write formulas for your manual crosstab like this:

//{@Day1}:
if day({table.date}) = 1 then {Orders.OrderAmount}

...one for each possible day. You would then insert sums on these at the group level and then suppress the detail section.

-LB

 

[vjfur]

Would I have to set up parameters for the ?year and ?month?

 

[lbass]

You don't have to. You could hard code it in the selection formula:

{table.date} in date(2009,1,1) to date(2009,1,31)

I just used the parameters because then you wouldn't have to keep changing the record selection formula.

-LB

 

[vjfur]

I didn't think about that.. I just assumed that if I changed my selection expert to a different time period then the formulas would calculate based upon the time frame rather than using a parameter?

 

[scottostanek]

Could you have single date param as the start point and build formulas for each day_bucket?

IE
@D1
If ({Orders.Orderdate}) = {?Startdate} Then {Orders.OrderAmount})
Else 0

@D2
If ({Orders.Orderdate}) = {?Startdate}+1 Then {Orders.OrderAmount})
Else 0

@D3
If ({Orders.Orderdate}) = {?Startdate}+2 Then {Orders.OrderAmount})
Else 0

Then summarize on each of these formulas. Sounds kind of painful to setup for a given report...
Alternatively you can make running totals and put the formula in the evaluation portion of it:
RT_D3 would be
If ({Orders.Orderdate}) = {?Startdate}+2 Then {Orders.OrderAmount})
Else 0
and the running total would be the summary form.

Scotto the Unwise

 

[lbass]

vjfur,

Yes, you can use the select expert to change it each time, if you wish, but you are essentially modifying and hard coding the report when you do this. A parameter simply prompts you for that information each time you refresh the report without changing the underlying report.

-LB