cancel
Showing results for 
Search instead for 
Did you mean: 

crystal report weekly summaries

Former Member
0 Kudos

hello

i am using oracle 11g /crystal reports 10 version

i have the following tables



users
=======
user_id  number
username varchar2

orders 
========
order_id number
user_id number
order_date date

select u.username,o.order_id,o.order_date
from orders o,users u
where o.user_id=u.user_id and order_date between '11-MAY-2011' and '30-MAY-2011'

i have the three columns in my crystal report and users will be prompted to enter date range they need to generate the report

when they do

it has to calculte the following


username,week_1_orders,week_2 orders,rest_orders



week_1_orders=first 7 days orders(in the above example ,its 11,12,13,14,15,16,17 of may 2011 total orders)

week2_2_orders=8-14 days of orders (in the above example ,18,19,20,21,22,23,24 of may 2011)

week_3_orders=rest of the date range


how do i get these summaries

any help is appreciated

thanks

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

create 3 formula

@1st7Days

If Orderdate <= dateadd("d", 7, {?startdateparam} then 1 else 0

@2nd7Days

If Orderdate > dateadd("d", 7, {?startdateparam} and

Orderdate <+ dateadd("d", 14, {?startdateparam}

then 1 else 0

@rest ofmonth

If Orderdate <= dateadd("d", 14, {?startdateparam} then 1 else 0

Then do simple sum summaries on these formula and they will give you the counts you require.

Ian

Former Member
0 Kudos

hello

thank you for your time

in here {?startdateparam) = order_id correct or is it order_date

Also say i have another column

amount and i want to calucalte sum of amounts for week1, how do i calcuate

secondaly i am getting same values for both columns

Edited by: atlvg123 on Jun 7, 2011 5:41 PM

Former Member
0 Kudos

Hello,

You can compare date field against your date parameter. So, it is OrderDate.

Also you can get summaries for your amounts by following same formula which was suggested by Lan

Also you can follow below formula to calculate summary on amount .

@Week1_Amt

if {Sheet1_.OrderDate} >= {?Startdate} and {Sheet1_.OrderDate} <={?Startdate}+7 then

else 0

Now insert a summary on this formula.

Thanks,

Sastry

Former Member
0 Kudos

thank you

we pass our parameters through webpage using ASP

we don't create any parameters from crystal, is there a way one can achieve this without passing the parameter ({?Startdate)

thanks again

Former Member
0 Kudos

Hi,

If we don't pass a parameter to crystal report, then how it will know from which date onwards you are trying to pull data. There should be some filteration to extract the required data.

If you want to hard code date values then it is possible, but that is not the right way to pull data from a database.

Thanks,

Sastry

Answers (0)