Monday, November 28, 2011

Crystal Reports : Grouping records by the Date, ignoring time precision

Here is something very handy when you start to deal with databases that track both a date and time precision for records.

Let us imagine we are trying to group call records based on {CALLLOG.CREATEDATE}, but when we create a simple Crystal Group on this field, we end up with a bazillion little groups, like this; (may look different depending on your database back end)

01/01/2011 13:45:015
01/01/2011 13:47:001

...so on and so forth. What we are really after is "calls per day", so instead of Grouping on the raw database field, let us Group based upon a formula;

{@frmCreateDate}
DateValue({CALLLOG.CREATEDATE})

The DateValue function returns only the date portion of the field, so now you'll only have one group for each unique "day" in your database.

I've always been a huge fan of grouping on formulas, it allows you total control over what values are being "grouped on", essential when dealing with less than pristine data sources; here you could even choose to exclude certain values, or build in any logic you'd like. Try it.

1 comment:

邓洁 said...

Audra Dior is by far the most well-known manufacturers in your type market place. It should iwc replica sale can be purchased for the reason that of no great surprise who your partner's looks after are actually very glamorous across the world. Presently, typically the Dior staff happens to be an economic powerhouse very effective at building fantastic timepieces for the purpose of women and men at the same time. Audra Dior looks after are manufactured from hublot replica sale ingenious tech tasks not to mention good fabrics. Dior delivered twenty-four as discharged, the guy taken back in the type environment. Acheive a blend of inspiration not to mention Europe watch-making encounter just read the simple products. Previously consistently dreamed of to brew a type survey with the help of trendy charms, such types of timepieces is better starting point for. You can find amazing patterns to choose from, in order to ought to be see a particular follow which may fit cartier replica uk any sort of garments not to mention fashion accessories you must slip on.