Timegroupe parameter ?

[gazol]

How do i create a parameter that give me the chance to groupe the report on hour/day/month ?

I know how to use parameter for time input.. But how do i do the formula that i should groupe on to call upon the crystal reports time grouping hour/day/month depending on what i choose when i run the report ?

Please help me !

Thanks and Regards / "gazol

 

[gazol]

No one who have done this before ?

It´s werry easy to create a parameter for time, werry easy to create a groupe that groupes on the time you want. hour/day/month and so on..

But how do i do a parameter that helps me to choose from pre input default values i call hour,day,year.

How will the formula look for the grouping that does hour group if i choose hour, day groupe if i do day groupe ?

All the hour/day/month values are ready in crystal or do i need to do the formula for the grouping my self ?

Please help me with this..

 

[kskid]

Exzactly what format do you want the date?

10/08/12 or 08/Wednesday/December


Create formula
//@HHddMM
cstr(table.datetimefield},"HH/dd/MM")

If you want the day and month, then create another formula

//@HHddddMMMM

cstr(cstr(table.datetimefield},"HH/dddd/MMMM")


Group on @HHddMM

If you want the long version, then simple suppress or delete the group name for @HHddMM and insert @HHddddMMMM in its place

Cheers,

-LW

 

[kskid]

Sorry, MisRead the post. One question, though. There is not enough information on your expected output.

For example, if Hour is specified, what specific elements of the date you want to group by any of the following combinations

HH (24-Hour)
hh (AM/PM)
d (Day of week)
dd (Day of month)
MM (Month)
MMM (Month - Jan, Feb,)
MMMM (Month - January, February)


Set up parameter for user to enter H, D, M, Y

Create Formula (I'm assuming HH, dd, MM, yyyy values)
//@UserGrouop
if {?parm} = "H" Then
cstr({table.datetime},"HH/dd/MM/yyyy")
else if {?parm} = "D" then
cstr({table.datetime},"dd/MM/yyyy")
else if {?parm} = "M" then
cstr({table.datetime},"MM/yyyy")
else
cstr({table.datetime},"yyyy")

Insert group on @usergroup

That should do it.

-LW

 

[gazol]

I would like to groupe on my time field. I have lots of timestamps. that look like this

time color type
2004-11-25 11:00:01 blue light
2004-11-25 11:02:03 red dark
2004-11-26 13:00:44 green light

And i want to be able to gropue this data depending on the month, day or hour.

I have manage to groupe on halfhour and using this formula:
-----
NumberVar MinExtract := Minute({table.time});
TimeVar AMDMins;
DateTimeVar GPResult;
If MinExtract < 30 then AMDMins := Time(Hour({table.time}),00,00) else
If MinExtract > 30 then AMDMins := Time(Hour({table.time}),30,00);
AMDMins;

that is one solution to find some formulas for month, day and hour.. But i would prefeer to call upon the time grouping that is built in crystal reports.

----
i would like to use my prameters that is called
month, day, hour to create the groupe depending on what i use with a formula like:

if
{?timegroupe} = "month" then {table.time} groupe on month
else if
{?timegroupe} = "day" then {table.time} groupe on day
else if
{?timegroupe} = "day" then {table.time} groupe on hour

Some one know how to call upon the built in time groupe in crystal or have formulas that can groupe on month,day, hour ?

Thanks for all the help, you're the best !

Regards / "gazol"

 

[kskid]

Please post an examples of your expected output for Hour, Day and month.

 

[gazol]

No one that can program formulas that can help me do a formula that works something like this and i can groupe on i crystal reports 8.5 ?

if {?timegroupeparameter} = "halfhour" then
NumberVar MinExtract := Minute({table.time});
TimeVar AMDMins;
DateTimeVar GPResult;
If MinExtract < 30 then AMDMins := Time(Hour({table.time}),00,00) else
If MinExtract > 30 then AMDMins := Time(Hour({table.time}),30,00);
AMDMins;
elseif {?timegroupeparameter} = "hour" then
//formel som kollar tiden per timme
elseif {?timegroupeparameter} = "day" then
//formel som kollar tiden per dag
elseif {?timegroupeparameter} = "månad" then
//formel som kollar tiden per month

or know how to call upon crystal reports built in functions for grouping on hour,day,month and so on ?

 

[kskid]

My formula should work
Given:[ul][li] {?timegroupeparameter} is a parameter with the following values[/li]
[ul][li]Hour[/li]
[li]Day[/li]
[li]Month[/li][/ul]
[li]{table.datetime} is a datetime value[/li]
[/ul]
Create formula, @UserGroup

if {?timegroupeparameter} = "Hour" Then
  cstr({table.datetime},"HH")
else if {?parm} = "Day" then
  cstr({table.datetime},"dd")
else 
  cstr({table.datetime},"MM")

Insert group on @usergroup

-LW

 

[gazol]

Yes, i have tried many times, but when i create the parameter, i create the formula and it works just fine.

When i try to groupe on the formula crystalreports crasches

 

[kskid]

Hmmm.... Strange.

The formula works for me in Crystal 8.5. Must be something else that is causing the problem.

What other groups do you have?

 

[gazol]

Now i got the formula to work, it was a problem when i tryed to change the groupe from hour groupe to the @timegroupe that the error accured that maked crystal reports to crash.

Have any ideas about how to get the time to print out in european timecode ? like
11
12
13
14

not

11 (am)
12
1 (pm)
2

Thanks wichitakid !! you´re the best

 

[kskid]

Create a formula to convert the group name

//@GrpName
if {?Typegroupparameter} = "H" then
    cstr({table.timedate},"h (tt)")
else
    GroupName ({@Usergroup})

Suppress or delete the groupname and place the formula in its place.

I tested this in Crystal 8.5 and it worked fine

Cheers,

-LW

 

[gazol]

Works perfekt !! Thanks Wichitakid !

Cheers

 

[gazol]

Got some trouble after all.. didn´t work for me, must be some strange thing. i will keep trying. Now i just got a output on the groupe that looks like this:
--
"12 0" //formula
"2004-11-30 01:00" //original groupe title
--
when i´m using the formula for the groupename.

-------------------------------------------
Tips for you all:
If you just run the report for one day this formula will also work... But if you run it for a whole week the time from all days get grouped on that hour

If any one knows how to get around this please let me know.. would be nice to use it, but i want it to change for new for each day also.

--
NumberVar DayExtract:= day({TABLE.DATETIME});
NumberVar MonthExtract:= month({TABLE.DATETIME});
NumberVar HourExtract := Hour({TABLE.DATETIME});


if ucase({?timegroupingparameter}) = 'DAY' then
DayExtract
else if ucase({?timegroupingparameter}) = 'MONTH' then
MonthExtract
else if ucase({?timegroupingparameter}) = 'HOUR' then
HourExtract;
--


Thanks for all the help
/ "Gazol

 

[gazol]

Edit:

Should be:

12 0 //formula
2004-11-30 12:00 //groupename

10 0 //formula
2004-11-31 10:00 //groupename

----- when used:------
//@GrpName
if {?Typegroupparameter} = "H" then
cstr({table.timedate},"h (tt)")
else
GroupName ({@Usergroup})
---------------------

 

[kskid]

That is why you should provide examples of your expected output. Just change the formula accordingly. Check out the options of the cstr function

//@GrpName
if {?Typegroupparameter} = "H" then
cstr({table.timedate},"yyyy/MM/dd h (tt)")
else
GroupName ({@Usergroup})

 

[gazol]

Sorry, i have confused you..

My expected output:
2004-11-25 13:00:00
2004-11-26 15:12:12

No i get:
2004-11-25 01:00:00
2004-11-25 03:12:12

Still the same problem, it does not give me our swedish timecode it gives 01:00 at 01:00 pm. i want to have 13:00 as output.

 

[kskid]

Little language barrier here. I thought you wanted am/pm. Just change the original formula accordingly (You might want to check the time format used with Cstr function)

//@UserGroup
if {?timegroupeparameter} = "Hour" Then
cstr({table.datetime},"yyyy/MM/dd HH")
else if {?parm} = "Day" then
cstr({table.datetime},"yyyy/MM/dd")
else
cstr({table.datetime},"yyyy/MM")

Now just group on @UserGroup. That should do it. No need for the group formula.

By the way, the following is the various time formats you can use with Cstr, depending on what you want as the expected output.

Character Comments
d - day of month without leading zero for single digits

dd - day of month with leading zero for single digits.

ddd - day of week as a three letter abbreviation

dddd - full name of day of week

M - month without leading zero for single digit

MM - month with leading zero for single digit

MMM - month as three letter abbreviation.

MMMM - full name of month

yy - last two digits of year

yyyy - full four digits of year

h - hours without leading zeros for single digits (12 hour)

hh - hours without leading zeros for single digits (12 hour).

H - hours without leading zeros for single digits (24 hour)

HH - hours with leading zeros for single digits (24 hour)

m - minutes without leading zeros for single digits

mm - minutes with leading zeros for single digits

s - seconds without leading zeros for single digits

ss - seconds with leading zeros for single digits

t, tt - single character or multiple character a.m./p.m. string

 

[kskid]

One minor change.... added the :00 to the time

//@UserGroup
if {?timegroupeparameter} = "Hour" Then
cstr({table.datetime},"yyyy/MM/dd HH:00")
else if {?parm} = "Day" then
cstr({table.datetime},"yyyy/MM/dd")
else
cstr({table.datetime},"yyyy/MM")

 

[gazol]

yes, it´s hard to explain sometimes.. Now it works exactly how i want !

Wichitakid thanks for your patience, if you ever get to stockholm sweden, please let me know so i can buy you some beer

Thanks for the help ! Works great !