Showing null as an option in report parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Showing null as an option in report parameters

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2008-07-24 : 04:59:28

Hi,
I have a report parameter which shows the names of Managers.
There are null values too.
i have written the query like:

(select distinct e.emp_seq_no val,e.emp_name label
from employee e
inner join project p on p.emp_seq_no = e.emp_seq_no)
union
(select 0 as val, 'No Value' as label)

But this is not retrieving records. i have done a similar report in which i have used
select ' ' as val, 'No value' as label
but here i cannot use as datatypes wont match....
Pls suggest how to solve this

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-24 : 10:20:26

When I have not misunderstood But this is not retrieving records:
You need to select data for your dataset and want to select Managers with emp_seq_no is null and your Parm is 0.
select blabla
where isnull(emp_seq_no,0) = @YourParm

Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 13:11:22

quote:
Originally posted by mrm23

Hi,
I have a report parameter which shows the names of Managers.
There are null values too.
i have written the query like:

(select distinct e.emp_seq_no val,e.emp_name label
from employee e
inner join project p on p.emp_seq_no = e.emp_seq_no)
union
(select 0 as val, 'No Value' as label)

But this is not retrieving records. i have done a similar report in which i have used
select ' ' as val, 'No value' as label
but here i cannot use as datatypes wont match....
Pls suggest how to solve this

Thanks

why do you think its not retrieving records. did you try calling this query in a dataset and running? didnt dataset get populated?

mrm23
Posting Yak Master

198 Posts

Posted - 2008-07-25 : 00:59:28

hi Webfred,
thanks for the reply. My problem is i am calling a subreport which uses project id.
Actually i want to see the no of projects each Manager has.
So my drop down has 7 managers. I have 140 projects. but only 50 of them have GMs.
For the rest, gm_id is null.
So along with these 7 members, i want an 8th option to capture those projects without GM under NOVALUE option.
But the subreport i call depends on project id and not on gm_id.
so i am not able to used this query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 11:53:37

quote:
Originally posted by mrm23

hi Webfred,
thanks for the reply. My problem is i am calling a subreport which uses project id.
Actually i want to see the no of projects each Manager has.
So my drop down has 7 managers. I have 140 projects. but only 50 of them have GMs.
For the rest, gm_id is null.
So along with these 7 members, i want an 8th option to capture those projects without GM under NOVALUE option.
But the subreport i call depends on project id and not on gm_id.
so i am not able to used this query.

what are parameters subreport's having now?

mrm23
Posting Yak Master

198 Posts

Posted - 2008-07-28 : 01:03:44

Hi visakh,
The subreport shows the employee details of a particular project.
So the subreport depends on project_id.
Now tht i have written the above query, (posted above) i want to use that condition in this dataset:

select p.prj_seq_no as a,p.prj_project_name project,count(rra.emp_seq_no) total
from rpmg_resource_allocations rra
inner join project p on rra.prj_seq_no = p.prj_seq_no
inner join sys_business_code_detail sb on sb.sbd_item_code = p.prj_status_item_code and sb.sbm_type_code = 'SZ' and sb.sbd_item_desc not in ('Completed','Scrapped')
and rra.prj_seq_no in (select prj_seq_no from project where prj_gm_seq_no in (@emp_seq_no))
inner join employee e on e.emp_seq_no = rra.emp_seq_no and e.emp_status_item_code = 1 and rra.rra_status = 1
where ((@from_date) between (rra.rra_start_date) and (rra.rra_end_date)
or @to_date between (rra.rra_start_date) and (rra.rra_end_date))
group by p.prj_seq_no,p.prj_project_name

At the point where i have used @emp_seq_no (in red),
i want it to assume null when the value passed is 0.
How to include that? i think that makes it work correctly....

mrm23
Posting Yak Master

198 Posts

Posted - 2008-07-28 : 09:35:43

I have got solution to this. I used "if" condition.
Thanks to Visakh and webfred for their suggestions

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 11:07:02

quote:
Originally posted by mrm23

I have got solution to this. I used "if" condition.
Thanks to Visakh and webfred for their suggestions

if doesnt seem to be the best solution seeing your earlier description what you want is probably a OR condition in your where.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources