Grouping based on date 1

[sskeen]

Using Crystal Reports XI R2

I have a report with a grouping based on date. The date is setup as a formula for a date range, month to date, and year to date. I need the report to show a date range based on a start and end date parameter entered with a list of names and their total amount for that date range. After the date range, I need it to show the same for month to date and year to date with the names and totals after each. It should show the date range with list of names, month to date with list of names and then year to date with the list of names. I could set it up as a subreport and split them out, but unfortunately the names drill into a subreport with a drill down within the subreport. I can get it to work with month to date and year to date or either it will only show the date range. I can understand why it's not working, but wondering if there is a way around this. Hope this makes sense. Thanks for any help you can give.

 

[Madawc]

You're wanting to show the same record in more than one place without using a subreport (because the detail itself uses a subreport). Is that the problem?

Consider if you can avoid using a subreport. Much more efficient without. You can add datasets more than once as an alias.

Failing that, with an SQL database you could do an SQL Command (Stored Procedure). Select the same records more than once and have a sequencing field so that the Crystal shows them correclty.

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

 

[sskeen]

Thanks for your response. Yes, that is the problem I am having. The report reads a DB2 table. I added the dataset in as an alias and it didn't pickup the date range section. It only showed the Month to Date and Year to Date. Thanks for you help.

 

[sskeen]

Correction to my response above. If I put 1/1/09-08/14/09 in the parameters, the report comes back showing only the section with a Date Range. If I put in 08/01/09-08/14/09, it comes back with the Date Range and Year to Date sections of the report. I need it to show the Date Range, Month to Date, and Year to Date sections all the time with whatever date range I key in the parameter. Any other suggestions?

 

[lbass]

You have to allow in the broadest range of dates in your selection formula, i.e., at least year to date. Then use conditional formulas to return results specific to a date range or month to date, e.g.,

if {table.date} in MonthToDate then {table.amount}

You would place formulas like this in the detail section and then insert summaries (sums) on them at the group level (name), and then suppress the detail section.

-LB

 

[sskeen]

I'm trying to get it to show something like this.

heading - 3/8/2009-07/31/2009 (parameters entered)
Smith $60.00
Johnson $45.00
Brown $70.00

heading - Month to Date
Smith $50.00
Johnson $10.00
Brown $30.00

heading - Year to Date
Smith $130.00
Johnson $65.00
Brown $120.00

Each of these sections have to be at the top of the report with their totals based on the date. Month to Date and Year to Date are based on the End Date parameter that is entered. I've got a grouping on the date using a formula and then on the name. Here is what the date formula looks like.

If {table.date} >= {?StartDate} and {table.date} <= {?EndDate} then "" & ToText(({?StartDate}), "M/d/yyyy") & " - " & ToText(({?EndDate}), "M/d/yyyy") else
If month({table.date}) = month({?EndDate}) then "Month to Date" else
If year({table.date}) = year({?EndDate}) then "Year to Date"

 

[lbass]

You can't group on the dates because one record will not appear in more than one group. You can get the desired display by using my suggestion. Why not try it?

-LB

 

[sskeen]

lbass,
I tried your suggestion, but wasn't able to get it to work. I need each date section to be stacked. For example, I need a box with the headings for the date range at the top with the names listed under it, next I need the month to date headings with the names under it, then I need the year to date headings with the names under it After that I have another report that starts and the names also have drilldowns under them. I also tried setting up a group for each of the three date options. Thanks for any suggestions.

 

[lbass]

You could use a command as your datasource where you use a union all statement that combines three queries, one for each date range, like this:

select 'Selected Date Range' as dtinterval, table.date,table.amt,table.name
from table
where table.date >= {?start} and
table.date < {?end}+1
union all
select 'Month To Date' as dtinterval, table.date,table.amt,table.name
from table
where month(table.date) = month(today) and
year(table.date) = year(today)
union all
select 'Year To Date' as dtinterval, table.date,table.amt,table.name
from table
where year(table.date) = year(today)

You will have to replace the month, year, and today functions with those that work with your datasource, and apply the appropriate punctuation/syntax.

Using the command, you can now group on {command.dtinterval} and then on {command.name} to get the desired results.

-LB

 

[sskeen]

Thanks for your help LB. This works great!