Formula Problem 1

[Myla944]

Crystal Reports 9
Using an SQL server type database


I created two parameters for Week Ending Date and Bakery Name, which thanks to Synaspe & Ibass I was able to get it to work. Then I place the WE Date in the Page Header and created 3 formulas using {?WE Date}-7, etc to change the dates in increments of 7 days each. I am not using a cross-tab.
I want to add the quantity from {tblBudgetManufactured.Qty} for the parameter date {?WEDate} and for the 3 other weekly dates I created {@2ndWeek},{@3rdWeek} & {@4thWeek}. I have tried different formulas and keep getting zeros, so I am apparently doing something wrong. Please Help. I feel like such an idiot!

Example of what I have right now:

WE Date WE Date WE Date WE Date Qtr to Date
3/6/04 3/13/04 etc
{WE Date} {@2ndWeek}

Regulars (Types of Buns)These are group sorted.
Quarters
Muffins
McRibs
etc.

Sincerely,

Myla

 

[lbass]

Myla,

What you are creating is a manual crosstab. I don't remember your exact formulas for weekending, but to get results for one week, you would use something like:

if {table.date] in {?WE date}-6 to {?WE date} then {tblBudgetManufactured.Qty}

For the prior week, the formula would look like:

if {table.date] in {?WE date}-13 to {?WE date}-7 then {tblBudgetManufactured.Qty}

Place these formulas in the detail section.

If you have a group on buns, your results might look like:

3/24 3/17
Regulars
0 3
0 0
2 2
Quarters
2 6
3 0
Muffins
0 2
1 4
McRibs
6 0
2 3

So now you want to insert summaries (sums) on each formula and then suppress the details so that the report looks like:

3/24 3/17
Regulars 2 5
Quarters 5 6
Muffins 1 6
McRibs 8 3

-LB

 

[Myla944]

Thank you Ibass, you have helped me immensley and your formulas worked like a charm. I was showing zero because the date field also including a time in the SQL Database, so I used your formula but changed the in {?WEDate} to = {?WEDate} and it worked! I also did the formula you suggested with the sum next to my group and it worked too. You are the greatest and I really appreciate all your help. I hope you have an awesome day today!

Sincerely,

Myla