on 06-07-2011 3:44 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.