Monthly Reports

Hi,
Actually I am generating report by stored procedure by passing two parameters like START date and END date.
Now my problem is I need to generate report for every month seperately that fall in range of START date and END date.
thanks
IndiraIndira,
Group by Date, and when creating the group, if you use a date, Crystal will, by default, give the grouping options:
the date, in a dropdown
the order to sort by, in a dropdown,
the grouping frequency, in a dropdown (defaults to "for each day.")
change the last one to "for each month."
Crystal will then produce your summaries for each calendar month within the range.
You can then add a page break after this date group, to start anew page with a new month.
That's how you'd produce one report covering many months, aggregated by each month.
YOur question says you want to produce separate monthly reports.
If they are paper copies, you just restart your page numbering for each new group, print the report out, and then distribute the separate months pages as separate reports.
If you want to produce separate files, you will need to outline which method you want to use to distribute the separate reports, because thismight require code via VB or C etc...

Dave

Month & Year comparison

Hi,

The problem here in hand is comparing the month & year ranges rather than the entire date. I am passing a start month and start year along with the end month and end year, now the query that i have written does not seem to be appropriate

My Query:

MONTH(Date_Added) BETWEEN @.StartMonth AND @.EndMonth

AND YEAR(Date_Added) BETWEEN @.StartYear AND @.EndYear

For some reason this works fine if i specify the startmonth as 1 and endmonth as 12, whereas if i specify 1 and 1 it doesnot work. Can anyone help me by refining my query so that i can search a particular date within mm/yyyy and mm/yyyy.

Thanks in advance.

Santosh

So if you put in between January and March and between 2004 and 2006, and then select for April 2005, it wouldn't qualify, right?

Do you really mean to select that way, or do you want to select between January 2004 and March 2006?

If you want to select continuous dates, set your first date to the first day of the month and your last date to the last day of the month and then search between those two values.

Here's a function to set a date to the first day of the month. You can use the same logic to set a date to the last day of the month.

http://www.sql-server-helper.com/functions/get-first-day-of-month.aspx

|||I would like to select between January 2004 and March 2006?|||

That'swhat I figured you want. You can't unlink the logic. Basically you have to have your year/months combined into a single variable and then do a between for those dates. Set the earlier date to the first of the month and your later date to the end of the month.

|||

WHERE Date_Added >= CAST(CAST(@.StartYear AS VARCHAR(4)) + '/' + CAST(@.StartMonth AS VARCHAR(2)) + '/01' AS DATETIME) AND Date_Added < DATEADD(month,1,CAST(CAST(@.EndYear AS VARCHAR(4)) + '/' + CAST(@.EndMonth AS VARCHAR(2)) + '/01' AS DATETIME))

|||

Yeah, that's the idea.