Newbie Question on Parameter & Dates 2

[Myla944]

Hello,

I am working on a report that will show the dozens sold of our product by bakery. It must contain a parameter so the user can select the correct bakery and the correct Week Ending Date (Saturday's Date (Week is from Sun to Sat) and I must show on the report the current week ending date with the amount of dozens manufactured for that week, the last 3 weeks prior to the current WE date with the amt of dz's sold, the total sold for the QTr and the Total for the Year.
I figured out how to set-up the parameter for selecting the week for the user to use, but I am confused about how to add a parameter for the user to select the correct bakery (tbl.Company.CompanyName)
Also what formula would I use to get the parameter date on the current weekending date? (tblbudgetmanufactured.EndDate)
Do you need to know all the tables that I am using on this report? Do I need to explain more? Sorry but I am new to this.
Also thank you for such a great web site. This has been the most help to me. Oh yes I am using Crystal Reports 9.

Sincerely,

Myla

 

[synapsevampire]

Your best bet when psoting is minimizing text descriptions and supplying:

Crystal version: 9
Database used:
Example data
Expected output

It generally requires less typing, but more discovery and thinking.

Crystal's default is to use Sunday to Saturday, so you're covered there.

But it you group on a date, set the group options to weekly, and the date does not exist within the data, the date shown will be the greatest date within the data.

I'm not even going to make a stab at this report here as there's so many unknowns, please supply something that demonstrates what you have in the database, and what you want output.

-k

 

[Myla944]

Thanks okay here is my info:
Crystal Version: 9
Database Used: SQL
Example Data:
Manufactured Dozens
WE 3/6/04 WE 2/28/04 etc QTR to Date YeartoDate

REGS 127,000 150,000 277,000
QTRS 58,000 40,000 98,000

I have set up by the weekending date using a parameter so the user can select the weekending date to be used in the first column, the second column would read the previous weekending date and so on.
I am trying to figure out a formula to show the weekending date that is selected in the parameter that I created using a beginning date and an ending date. I have to get this information from {tblBudgetManufactured.EndDate}
I also have to calculate the dozens of Buns that were made for that particular week.

Do you need more information? I am sorry but I am new to this and Crystal. Also thank you for your help.

Sincerely,
Myla

 

[synapsevampire]

I guess I should assume that when people state that their database is SQL, they mean SQL Server (SQL is a language common to many databases).

When supplying an example of the data, don't show what will happen to it AFTER the report, show what is in the database, as in:

tblbudgetmanufactured:
EndDatedate qty
1/7/2004 5000
1/14/2004 10000

etc.

Then show what the result would be, such as in your last post as the example data.

What you're looking to do is what's commonly termed a Manual Cross-tab.

This means that you'll need to derive a lot of information.

If the users are going to enter a date range of say 4 weeks of data, then you would create 4 Running Total fields with the evaluate->Use a Formula to limit the first 4 Running Totals to only pull data for their periods, and then 2 additonal Running Tottals which have the Evaluate->Use a Formula which have the Quarter to date and Year to Date criteria within.

So if they enter 2 date ranges, do you make the assumption that they want the complete weeks activities for those dates, or starting and ending on those dates?

You should be getting a better idea of how to approach this, at issue is how you intend to handle the weeks summaries.

Hope this helped, and try posting specific examples and the intent, we're almost there..

-k

 

[lbass]

Just to add some miscellaneous thoughts, it sounds like you have a date range parameter. To get the parameter ending date you would use:

maximum({?date})

If the parameter can be entered for any day of the week, and you always want it to reflect the Saturday ending that week, you can use a formula like {@weekendingdate}:

maximum({?date})-dayofweek(maximum({?date}))+6

If you are creating a manual crosstab, you could use running totals, but, as long as you don't have duplicate data, I think it is easier and the report will run faster if you use formulas instead, such as the following for your detail fields:

if {table.date} in {@weekendingdate}-6 to {@weekendingdate} then {table.amt}

You can insert summaries on these formulas and then suppress the details.

You can insert a group on bakery product so that these formulas can be summarized by product group, like buns.

If you want to add a parameter to the record selection for bakeries, then create a discrete (if you only want to see one at a time) or a multiple string (if it is being used to select by bakery name) parameter {?BakeryName} and then in the edit selection formula area add:

(Company.CompanyName) = {?BakeryName}

For date criteria in the record selection formula, you would need to include all dates referred to in your report, so at a minimum you would probably want to add something like:

and {table.date} in YearToDate

-LB

 

[Myla944]

I really appreciate your help and thank you both for helping me. I now have a better understanding on how to work on this file! Again thank you and have an awesome day today!

Sincerely,

Myla