Parameter List under Add Command Option

[Lhuffst]

I have a lengthy query that is supposed to retrieve data between dates given. I created two parameters in the parameter list StartDate and EndDate. This was to reduce the amount of records that are retrieved. This works fine.
I have a parameter in the crystal report that gives them an option to select one or more employees.

When I run the report, I get two sets of prompts. The first for the command parameter list (StartDate, EndDate) and the 2nd for (StartDate, Enddate and Employee) (I believe this is from the report side).

I removed the startDate and EndDate paramter from being placed on the report but these fields are used in formulas.

The reason I do not have the employee as a command parameter list is because they can choose "all" or individual people.
The report runs but how do I get one set of prompts?
here is the query I use

select * from 
(
    Select * from (
    Select b.lastname,b.FirstName, b.userid, b.dept,
    a.mydate, a.mthshorttitle, 
    max(a.ref_id_1) as r1,  max(a.ref_id_2)as r2,   max(a.ref_id_3)as r3,   max(a.ref_id_4)as r4,
    max(a.ref_id_5)as r5,   max(a.ref_id_6)as r6,   max(a.ref_id_7)as r7,   max(a.ref_id_8)as r8,
    max(a.ref_id_9)as r9,   max(a.ref_id_10)as r10, max(a.ref_id_11) as r11,max(a.ref_id_12)as r12,
    max(a.ref_id_13)as r13, max(a.ref_id_14)as r14, max(a.ref_id_15)as r15, max(a.ref_id_16)as r16,
    max(a.ref_id_17)as r17, max(a.ref_id_18)as r18, max(a.ref_id_19)as r19, max(a.ref_id_20)as r20,
    max(a.ref_id_21) as r21,max(a.ref_id_22)as r22, max(a.ref_id_23)as r23, max(a.ref_id_24)as r24,
    max(a.ref_id_25)as r25, max(a.ref_id_26)as r26, max(a.ref_id_27)as r27, max(a.ref_id_28)as r28,
    max(a.ref_id_29)as r29, a.refid
    from (
        select t3.refid,t2.userid,trunc(t2.PROCESSDATE) mydate, 
            T3.SHORTTITLE, T3.MTHSHORTTITLE,
            decode(t1.refid,1,nvl(t1.desc_count,0),0) ref_id_1,
            decode(t1.refid,2,nvl(t1.desc_count,0),0) ref_id_2,
            decode(t1.refid,3,nvl(t1.desc_count,0),0) ref_id_3,
            decode(t1.refid,4,nvl(t1.desc_count,0),0) ref_id_4,
            decode(t1.refid,5,nvl(t1.desc_count,0),0) ref_id_5,      
            decode(t1.refid,6,nvl(t1.desc_count,0),0) ref_id_6,      
            decode(t1.refid,7,nvl(t1.desc_count,0),0) ref_id_7,      
            decode(t1.refid,8,nvl(t1.desc_count,0),0) ref_id_8,      
            decode(t1.refid,9,nvl(t1.desc_count,0),0) ref_id_9,
            decode(t1.refid,10,nvl(t1.desc_count,0),0) ref_id_10,
            decode(t1.refid,11,nvl(t1.desc_count,0),0) ref_id_11,
            decode(t1.refid,12,nvl(t1.desc_count,0),0) ref_id_12,
            decode(t1.refid,13,nvl(t1.desc_count,0),0) ref_id_13,
            decode(t1.refid,14,nvl(t1.desc_count,0),0) ref_id_14,      
            decode(t1.refid,15,nvl(t1.desc_count,0),0) ref_id_15,      
            decode(t1.refid,16,nvl(t1.desc_count,0),0) ref_id_16,      
            decode(t1.refid,17,nvl(t1.desc_count,0),0) ref_id_17,      
            decode(t1.refid,18,nvl(t1.desc_count,0),0) ref_id_18,
            decode(t1.refid,19,nvl(t1.desc_count,0),0) ref_id_19,      
            decode(t1.refid,20,nvl(t1.desc_count,0),0) ref_id_20,      
            decode(t1.refid,21,nvl(t1.desc_count,0),0) ref_id_21,      
            decode(t1.refid,22,nvl(t1.desc_count,0),0) ref_id_22,      
            decode(t1.refid,23,nvl(t1.desc_count,0),0) ref_id_23,
            decode(t1.refid,24,nvl(t1.desc_count,0),0) ref_id_24,
            decode(t1.refid,25,nvl(t1.desc_count,0),0) ref_id_25,      
            decode(t1.refid,26,nvl(t1.desc_count,0),0) ref_id_26,      
            decode(t1.refid,27,nvl(t1.desc_count,0),0) ref_id_27,      
            decode(t1.refid,28,nvl(t1.desc_count,0),0) ref_id_28,      
            decode(t1.refid,29,nvl(t1.desc_count,0),0) ref_id_29
        from TRACKDETAIL t1, TRACKMASTER t2, TrackREF t3 
        where t1.TRACKID =  t2.TRACKID
              and t2.PROCESSDATE >= to_date({?StartDate})
              and t2.PROCESSDATE < to_date({?EndDate}) 
              and t3.refid=t1.refid
        order by PROCESSDATE,refid
        ) a,
        (select lastname,firstname, userid, dept from USERMASTER) b
         where a.userid=b.userid
         group by b.lastname,b.firstname, b.userid, b.dept,a.mydate,a.mthshorttitle, a.refid
)
union all
select lastname, firstname,userid, dept,mydate, '',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 from
(
select lastname,firstname,userid,dept,mydate
from
usermaster x1,
(
select level +1  + trunc(to_date({?StartDate})) mydate from dual
connect by level <(select to_date({?EndDate}) - to_date({?StartDate}) from dual)
)  x2
) y1
where mydate not in
(
select trunc(PROCESSDATE) sy_date from TRACKDETAIL t1, TRACKMASTER t2, TrackREF t3 
  where t1.TRACKID =  t2.TRACKID
    and t2.PROCESSDATE >= to_date({?StartDate})
    and t2.PROCESSDATE < to_date({?EndDate}) 
    and t2.userid=y1.userid
    and t3.refid=t1.refid 
)
)
order by userid,mydate

 

[Lhuffst]

Oh forgot to mention that this query, by default, returns all of the employee records in the given time frame. So on the report, the user would be selecting to display all employees or just the ones they want to see.

 

[lbass]

When you create the date parameters within the command, they will appear in your main report parameter list and they can be used in the formulas in the main report. Try removing the formulas that reference the date parameters and then remove the parameters created in the main report. Then recreate the formulas using the command-created parameters.

-LB