how to manipulate dates to show Monthly sales 1

[jadepatel]

Hi,

Im new to these crystal reports. I have created a few basic reports that show the sale of certain products between 2 dates that are specifed as parameters to the record selection formula.
The dates are stored like this in my database: 24/02/2004.
(this represents the date the product was sold on)

what i wanted to ask is how do i create a report that shows the sales for each month in the year?
i.e so i can see the best month for sales of a particular product?

I want the months of the year to form the X-axis and the number of sales along the y axis.

Im confused on how i manipulate the dates as they are stored in my database so that they can be chunked into groups according to the month the sale occured in.

can anyone advise me how to do this?

 

[lbass]

Are you talking about creating a crosstab or a chart? For a crosstab, use {table.date} as your column field, and then select it and choose "Group options" and choose "print for each month." Use {table.products} as your row, if you want a report by product, and use {table.salesamt} as the summary field.

-LB

 

[jadepatel]

Hi,

Im talking about creating a chart.
This code that you have given me...where do i write this?
I normally use the wizard to create my reports and charts.

 

[lbass]

For a chart, using the "Advanced" layout, you would use {table.date} as your "on change of" field. Then select it, choose "Order", and select "Chart values shown for each month." This assumes that your date field is actually a date, not a string. If you are not sure, look at the tooltip when you run your mouse over the date field--it will tell you the datatype in parentheses. If it is not a date, you would need to convert it to a date.

Then select {table.sales} for your summary field.

-LB

 

[jadepatel]

this is great. Thx for your help. i will try this.
Do u know if the report can accept parameters at runtime.
i.e if i wanted a combobox that lets the user select a product they wanted to see the monthly sales for. Is this possible? the only controls i can see in the menu when creating a crystal report are Text Object, Line Object, Box Object!?!

 

[lbass]

You can insert a parameter (insert->field object->parameter field). Choose either a string or a number datatype depending on the database field for the product, and then go to report->edit selection formula->record and enter:

{table.product} = {?Product}

-LB

 

[jadepatel]

If i use this method u have just stated, i will get the standard pop-up box that asks for the parameter to be input before the report is displayed. Is there anyway i can pass the parameters via a combobox or something similar that resides on my windows form instead??

 

[lbass]

Sorry, I can't help you with that--someone else will have to jump in. I think it would help if you provided more specifics about your reporting environment though.

-LB