Problem with 2 Dataset Sharing Parameters

I have a report that will pull out a list of bugs assigned to users for a
period of time (@.StartDate, @.EndDate). Optionally the report has option to
select a single user (@.UserName) or just all of them. There are 2 datasets.
First dataset: Bugs requires 3 parameters: @.StartDate, @.EndDate, @.UserName.
Second dataset: UserName. At first I have a query to list all users. Since
the list has hundreds of users while the result set from dataset Bugs mostly
has less than 10 users, I built another query to list only users having bugs
assigned from @.StartDate to @.EndDate. So it requires 2 parameters @.StartDate
and @.Enddate.
Now both datasets use @.StartDate and @.EndDate. The list to select for
@.UserName is produced after @.StartDate and @.EndDate are entered. I expected
the report to let me select the username after I enter @.StartDate and
@.EndDate. However in the preview tab right at the moment when I clicked on
the box for @.EndDate to enter a value the program hang up (VS 2003). I
uploaded it to a report server to see how it behaves then I got this error
after I click on the EndDate box: The value provided for the report
parameter 'EndDate' is not valid for its type.
(rsReportParameterTypeMismatch).
I don't know what's wrong. If I change the dataset UserName to list all
users (no parameter required) then the report runs fine. I use Visual
Studio .NET 2003 Service Pack 1, report server is SQL 2000 with latest SP on
the O/S, SQL, and SQL RS.
An thought?
Thanks.On May 2, 5:31 pm, "ME" <M...@.mail.com> wrote:
> I have a report that will pull out a list of bugs assigned to users for a
> period of time (@.StartDate, @.EndDate). Optionally the report has option to
> select a single user (@.UserName) or just all of them. There are 2 datasets.
> First dataset: Bugs requires 3 parameters: @.StartDate, @.EndDate, @.UserName.
> Second dataset: UserName. At first I have a query to list all users. Since
> the list has hundreds of users while the result set from dataset Bugs mostly
> has less than 10 users, I built another query to list only users having bugs
> assigned from @.StartDate to @.EndDate. So it requires 2 parameters @.StartDate
> and @.Enddate.
> Now both datasets use @.StartDate and @.EndDate. The list to select for
> @.UserName is produced after @.StartDate and @.EndDate are entered. I expected
> the report to let me select the username after I enter @.StartDate and
> @.EndDate. However in the preview tab right at the moment when I clicked on
> the box for @.EndDate to enter a value the program hang up (VS 2003). I
> uploaded it to a report server to see how it behaves then I got this error
> after I click on the EndDate box: The value provided for the report
> parameter 'EndDate' is not valid for its type.
> (rsReportParameterTypeMismatch).
> I don't know what's wrong. If I change the dataset UserName to list all
> users (no parameter required) then the report runs fine. I use Visual
> Studio .NET 2003 Service Pack 1, report server is SQL 2000 with latest SP on
> the O/S, SQL, and SQL RS.
> An thought?
> Thanks.
The issue sounds kind of strange. Maybe you should check to make sure
that you have the correct @.EndDate mapping as part of the UserName
dataset (via the Data tab -> Edit Selected Dataset button [...] ->
Parameters tab). Also, make sure that the stored procedure/query that
sources the UserName dataset is not expecting a different data type
for @.EndDate (for whatever reason). Sorry I could not be of greater
assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks for the reply. I use datatime as data type for @.StartDate, @.EndDate
in both datasets. Double checked everything but still have no clue why.
When I learned and practiced SQL 2005 I had no problem doing similar thing.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178160515.514988.169100@.e65g2000hsc.googlegroups.com...
> On May 2, 5:31 pm, "ME" <M...@.mail.com> wrote:
>> I have a report that will pull out a list of bugs assigned to users for a
>> period of time (@.StartDate, @.EndDate). Optionally the report has option
>> to
>> select a single user (@.UserName) or just all of them. There are 2
>> datasets.
>> First dataset: Bugs requires 3 parameters: @.StartDate, @.EndDate,
>> @.UserName.
>> Second dataset: UserName. At first I have a query to list all users.
>> Since
>> the list has hundreds of users while the result set from dataset Bugs
>> mostly
>> has less than 10 users, I built another query to list only users having
>> bugs
>> assigned from @.StartDate to @.EndDate. So it requires 2 parameters
>> @.StartDate
>> and @.Enddate.
>> Now both datasets use @.StartDate and @.EndDate. The list to select for
>> @.UserName is produced after @.StartDate and @.EndDate are entered. I
>> expected
>> the report to let me select the username after I enter @.StartDate and
>> @.EndDate. However in the preview tab right at the moment when I clicked
>> on
>> the box for @.EndDate to enter a value the program hang up (VS 2003). I
>> uploaded it to a report server to see how it behaves then I got this
>> error
>> after I click on the EndDate box: The value provided for the report
>> parameter 'EndDate' is not valid for its type.
>> (rsReportParameterTypeMismatch).
>> I don't know what's wrong. If I change the dataset UserName to list all
>> users (no parameter required) then the report runs fine. I use Visual
>> Studio .NET 2003 Service Pack 1, report server is SQL 2000 with latest SP
>> on
>> the O/S, SQL, and SQL RS.
>> An thought?
>> Thanks.
>
> The issue sounds kind of strange. Maybe you should check to make sure
> that you have the correct @.EndDate mapping as part of the UserName
> dataset (via the Data tab -> Edit Selected Dataset button [...] ->
> Parameters tab). Also, make sure that the stored procedure/query that
> sources the UserName dataset is not expecting a different data type
> for @.EndDate (for whatever reason). Sorry I could not be of greater
> assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

problem when addin an extra field to a select

I have a select statement that works fine:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays
from
clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d, ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
ORDER BY E.Surname ASC, A.StartDate

I get 26 rows returned. I need to add an extra field to the select. The Reason field on the Absence table contains a code, the value for this code, which I need, is stored in the Choices table. The Choices table has KEYID and ITEMID as the Primary Key. Reason field links to the ITEMID field.

When I run the following sql:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays, (B.Name) as SLReason
FROM clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K, clockwise.choices B,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d, ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
AND B.KeyID = 'R%' AND B.ItemID = A.Reason
ORDER BY E.Surname ASC, A.StartDate

I get 1 row, the only person from the previous select who has an entry in the Choices table. I understand why.

When I run the following sql:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays, SLReason
FROM (SELECT (B.Name) SLReason
FROM clockwise.choices B,
clockwise.Absence A
WHERE B.KeyID = 'R%'
AND B.ItemID = A.Reason),
clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d, ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
ORDER BY E.Surname ASC, A.StartDate

I get 104 rows, each person from the first select now appears 4 times with 1 of 4 different reasons.

How do I get 26 rows with just the 1 person showing his reason and the 25 other's having blank reasons.

Thanks for any help.Have a look at the Join predicate. Rather than define you realtionship using a where clause use a join. The join can cater for the situation where the value is null.

Sorry, don't have time to examine id detail your SQL but will try to later on tonight, that is if no one else has come up with a better answer in the mean time.|||What you need is a LEFT JOIN.

Based on the SQL that you've posted, you appear to be using Oracle, but I can't tell which version. If your Oracle is current enough to support the SQL-92 syntax, I'd strongly suggest that you switch to it because it makes a lot of things easier than the SQL-89 syntax that you are using in this query.

If you need more help, please post your Oracle version and whatever question(s) you might have.

-PatP|||I'm using Oracle8i, since I posted I've been trying to use a join (left outer) with no success so far.

Thanks for the info, I'll keep trying.|||Got it working by totally ignoring the choices table. I was getting more comlicated than I needed, I just added a decode for the Reason field:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode(A.Cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays,
Decode(A.Reason, '!', 'Certified', '#', 'Hospitalised', '$', 'Uncertified', '%', 'Unwell',
'&', 'Throat Infection', Null) as SLReason
FROM clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d, ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
ORDER BY E.Surname ASC, A.StartDate

I got the 26 rows with the correct value.

Thanks for the help,
Mark.