Month-to-date & week-to-date

[back2tek]

Hi all,
CR9 on SQL2000. A report list sales daily/weekly/previous week/montly/previous month on the same line. Looks like:

Salesrep Sales daily weekly prev-week monthly prev-month

My intention is to use running totals for each sales columns, I am not sure about previous week and previous month formulas. The report will run daily. So if it runs yesterday, the prev week will be the sales from Sep18-21 and prev month to date will be aug1-aug28.

Thanks in advance

 

[Madawc]

Take a look at DateAdd and DateDiff. These can handle calendar months, and also numbers of days, which is what you seem to want.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[kenhamady]

Crystal has a bunch of date range functions for the periods you are after so you can write a formula like this:

If {date} in LastFullWeek
then {Amount}
else 0

then do a normal summary field of this formula. They have functions for MonthToDate, WeekToDate, LastFullMonth, etc.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[back2tek]

Thank you all,
My needs are slightly different. If I ran my report on wednesday for example than I need to report on sales from
Monday to Wednesday for the current week and compare it to Monday to Wednesday sales for the last week.
In this scenario last full week function is not what I need.

 

[synapsevampire]

No reason to use manual formulas anyway, use a Running Total and in the Evaluate->Use a Formula place:

if dayofweek(currentdate) <> 1 then
{table.date} in maximum(lastfullweek)+1 to currentdate
else
{table.date} = currentdate-6 to currentdate

The point of Ken's post is that you can use existing date range functions. Obviously he had no way of knowing that you wanted Monday to Wednesday because Monday isn't the start of a week in Crystal (and many programming languages), and you didn't take the time to post that information.

As you can see, each of the date range functions can use minimum and maximum, so it's easy enough to work out the ranges for any period using them, or as Madawc pointed out, you can use a dateadd function, or even a dateserial function.

-k

 

[kenhamady]

-k,

Actually, there are several reasons to using manual formulas with summaries over running totals with a condition. With a true summary field you can:

See the summary in headers as well as footers.
Do ranking based on the summary
Do Group Selection based on the summary
Do Percent of total based on the summary

And if you ever need to use Evaluate "on change of group" that would still be an option if you used a manual formula. Since report requirements evolve over time I hate to give up options that I might need down the road.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[synapsevampire]

I agree Ken, those can be reasons to use them, however there are reasons why one may elect NOT to do so, such as ease in coding and less formulas, which if you're stating advantages, a thorough intent should include disadvantages.

I generally try to suggest the simplest point from A to B given the requirements, and they specifically asked about Running Totals in the post.

And actually, Ken, if the intent was to post best practices, then you should inform the user that they are best served to return the aggregates in the recordset itself either using a Stored Pocedure, Viwe or a Command Object as it promotes reusability, ease of maintenance and the best performance.

Most coders are simply trying to get things done, and Running Totals are simple to grasp and met these requirements.

Not sure why you felt that I wasn't aware of the advantages to the summaries you outlined, you've certainly read numerous posts of mine indicating that I understand them fully.

-k

 

[kenhamady]

-k,

It was the phrase "no reason" that inspired me. There are reasons to use manual totals. I don't even agree that RTs involve fewer 'formulas'. The formulas are there but are simply in a different place. As a matter of fact if you have to do both group and grand totals then you will have to repeat the same logic in multiple running totals so that you end up doing more formulas. And, once you have figured out the Boolean test then making it into an If-Then formula is no more complex coding. So the user might have considered running totals but I strongly recommend the alternative.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[back2tek]

Hi all,
I personally use running totals a lot in my reports for its ease of use as synapvampire said. In the same time I agree with Keen's opinion and I am planning to adopt it moving forward. I benefit it from the technical debate and I wonder why experts like Ken, Synapvampire and Lbass don't put together a Best practice guidelines for the average CR developer.

Thank you

 

[kenhamady]

I find many users go with running totals, sometimes just because they see them in the menu first. That is what motivated me to write one of my recent 'best practice' guides on totals earlier this year. My expert's guides aren't free but many of my posts are based on ideas developed in my guides. I think of the posts as free samples.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[synapsevampire]

I try to use what fits, and with the Running Total, I type in one formula, the evaluate->Use a formula, whereas the method you outline has 2 formulas.

However I do tend to lean towards coding them, but that's out of habit, not any solid logic behind best practices.

In this case, the Running Total fits the bill and the next coder has one item to deal with when modifying the report.

It would be interesting to see a real world example of the cost of each, it could be that Running Totals have inherent overhead that makes them slower, however the notion of having 2 objects sounds more expensive.

But as I say, I wouldn't code beyond the requirements, and I could more readily argue against doing so as a matter of practice unless presented with a reason to do so aside from personal preferences.

As I mentioned before, providing the aggregates on the database is the best practice, and proper SQL in a Command Object will also outperform either of the methods we've described here.

-k