Ggrouping by Date Range Parameter 2

[alectek]

Hi!

Please help with grouping by Date Range Parameter.

?Start Date = Date(2003, 2, 01)
?End Date = Date(2004, 1, 25)

The group should looks like:

Feb, 03
Mar, 03
Apr, 03
May, 03
Jun, 03
Jul, 03
Aug, 03
Sep, 03
Oct, 03
Nov, 03
Dec, 03
Jan, 04

Than I’ll need to send this parameter in the sub-reports to do calculation by Months.

Thanks.
Alex.

 

[synapsevampire]

You don't group by a parameter, as with SQL programming, you group on your data.

In the reports or subreports select Insert->Group->Select the date and change it to For Each Month.

Your post shares nothing technical, so I can't suggest more at this point.

If you share your:
Crystal version
Database
Example Data
Expected output (not what a group should look like, state what the actual report output needs to be).

Someone may be able to asssist you further with your requirements.

-k

 

[alectek]

Sorry, I wasn't clear.
CR 8.5 with SQL Server.
And I really need to do grouping base on Month from user selected date range as I show above.

Thanks.
Alex.

 

[synapsevampire]

Here's how to limit rows in subreports, and have the subreport data grouped by months:

Create the parameter in the main report.

Insert the supreport and link the subreport date field to the main report date parameter.

Go into the subreport and insert a group by the date, and make sure that you state For Each Month as the group type.

Again, grouping has nothing to do with parameters. Grouping is a means to take existing DATA and place it into like buckets.

Since you don't want to post example data and expected output, it's difficult to help you.

BTW, Subreports are generally a bad idea as the performance is typically bad.

-k

 

[dtempleton]

Create a formula that gets the year from your date field. Do the same for the month. Then create a firstofmonth date formula:

date({@Year},{@Month}, 1)

Insert a group by year, then by month. Place the firstofmonth field in the month header, then customize its formatting.

 

[alectek]

Thank you very much, synapsevampire and dtempleton.
If you can please help:

The Data looks like this.

Client Table Client Related Service Table
Client OID Client Related Service OID
Provider OID ------> Provider OID
Client Related Service Start DateTime
Client Related Service End DateTime

Other Data model looks like this:

ProvederTable Other Service Table
Other Service OID
Provider OID -----> Provider OID
Other Start DateTime
Other Service End DateTime
User Select Start and End Date for Client Related and Other Services:


?Start Date = Date(2003, 2, 01)
?End Date = Date(2004, 1, 25)

The Report should look like this:

Month Cl.Rel.Services Other Services

Feb, 03 20(hrs) 10(hrs)
Mar, 03 0 (hrs)
Apr, 03
May, 03
Jun, 03 15 (hrs)
Jul, 03
Aug, 03
Sep, 03 50 (hrs)
Oct, 03
Nov, 03
Dec, 03
Jan, 04
Total 70 hrs 25 hrs

OVERALL TOTAL 95 HRS

Thanks again,
Alex

 

[dtempleton]

Ick...

You have outer joins on the tables that need the date selections, and not every month in your report has a value. I think my simple answer won't work for you. It may require some programming.

 

[alectek]

So, no way I can get help for this?

Thanks.
Alex.

 

[dtempleton]

I'm sure you can, but I don't know how to help. Sorry.

 

[alectek]

Thanks anyway.
Alex.

 

[dtempleton]

I found a link that might help.

Thread767-526332

 

[lbass]

What you need here is a table that contains all dates. You would group on the date field from this new table and then either use left joins to the other two tables or use subreports that are linked to the main table by formulas in each that look like:

date(year({table.date}),month({table.date}),01)

You would use the parameters to select the dates in the main report record selection formula:

{maintable.date} in {?start} to {?end}

Synapsevampire has an FAQ on creating a periods table. You could also create a dates table in Excel and use the subreport method.

-LB

 

[alectek]

Thank you very much, dtempleton and lbass.