Report Service output suppressing duplicate info

Hello,
Here is the situation. I have a table with a fieldname called VAL. I
call a stored procedure to return 4 records from the table.
Ok, the 4 records's field name VAL all have the same value , "Horray".
I created a report with a table and only want to display the VAL column.
I was expecting to see 4 rows of 'Horray' but I only see one row. Is there
place in the Report Service to turn off suppressing duplicate values?
product is Sql Server Reporting Service 2000.
Thanks
SteveOn the table, select your val column textbox , right click for properties you
can see "Hide duplicate" so this does the things..
Amarnath
"steve kwon" wrote:
> Hello,
> Here is the situation. I have a table with a fieldname called VAL. I
> call a stored procedure to return 4 records from the table.
> Ok, the 4 records's field name VAL all have the same value , "Horray".
> I created a report with a table and only want to display the VAL column.
> I was expecting to see 4 rows of 'Horray' but I only see one row. Is there
> place in the Report Service to turn off suppressing duplicate values?
> product is Sql Server Reporting Service 2000.
> Thanks
> Steve
>
>|||hi steve
here's another solution
right click the text box, click properties, click on visibility tab. select
Expression radio button and click on the function button. enter this
expression there
=(Previous(Fields!PK_Field1.Value) = Fields!PK_Field1.Value)
replace PK_Field1 with the field name that determines the duplicate value
for you. repeat the same step for all the text boxes that where you want to
suppress the duplicates.
this way, if you had a report like this
field1val 1234 abcd
field1val 1234 xyz
field2val 3988 alsdk
you'll see this output:
field1val 1234 abcd
xyz
field2val 3988 alsdk
hope it helps
"steve kwon" wrote:
> Hello,
> Here is the situation. I have a table with a fieldname called VAL. I
> call a stored procedure to return 4 records from the table.
> Ok, the 4 records's field name VAL all have the same value , "Horray".
> I created a report with a table and only want to display the VAL column.
> I was expecting to see 4 rows of 'Horray' but I only see one row. Is there
> place in the Report Service to turn off suppressing duplicate values?
> product is Sql Server Reporting Service 2000.
> Thanks
> Steve
>
>

Report query

I am trying to create a query which should return 4 columns
Facility | NoOfActiveApplicants | NoOfArchivedApplicants | TotalApplicants
I would be glad to even have just 3 columns since, the Total can be computed
in the display table (ColdFusion interface)

Facility | NoOfActiveApplicants | NoOfArchivedApplicants

So far I have the following query, which returns just 2 rows with both
NoOfActiveApplicants & NoOfArchivedApplicants under the same column.
Am stuck here, any help is apprecialted. Thanks in advance!

select
NoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end)
, Facility = case when c.facility is null then c.JobDBFacilityName else c.
facility end
from tblapplicant a, tblJobDB b, tblfacilities c
where a.jobid = b.jobid
and b.facility = c.facilityid
group by c.facilityid,c.JobDBFacilityName,c.facility,b.faci lity
union all
select
NoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end)
, Facility = case when c.facility is null then c.JobDBFacilityName else c.
facility end
from tblArchiveapplicant a, tblArchiveJob b, tblfacilities c
where a.JobVacancyNumber = b.JobVacancyNumber
and b.facility = c.facilityid
group by c.facilityid,c.JobDBFacilityName,c.facility,b.faci lity
order by facilityOn Wed, 27 Jul 2005 19:28:42 GMT, c0de w via SQLMonster.com wrote:

>I am trying to create a query which should return 4 columns
>Facility | NoOfActiveApplicants | NoOfArchivedApplicants | TotalApplicants
>I would be glad to even have just 3 columns since, the Total can be computed
>in the display table (ColdFusion interface)
>Facility | NoOfActiveApplicants | NoOfArchivedApplicants
>So far I have the following query, which returns just 2 rows with both
>NoOfActiveApplicants & NoOfArchivedApplicants under the same column.
>Am stuck here, any help is apprecialted. Thanks in advance!
>select
> NoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end)
>, Facility = case when c.facility is null then c.JobDBFacilityName else c.
>facility end
> from tblapplicant a, tblJobDB b, tblfacilities c
>where a.jobid = b.jobid
> and b.facility = c.facilityid
>group by c.facilityid,c.JobDBFacilityName,c.facility,b.faci lity
>union all
>select
> NoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end)
>, Facility = case when c.facility is null then c.JobDBFacilityName else c.
>facility end
> from tblArchiveapplicant a, tblArchiveJob b, tblfacilities c
>where a.JobVacancyNumber = b.JobVacancyNumber
> and b.facility = c.facilityid
>group by c.facilityid,c.JobDBFacilityName,c.facility,b.faci lity
>order by facility

Hi c0de,

Hard to say without knowing anything about structure of the tables in
your database (see www.aspfaq.com/5006 for a better way to ask questions
in these groups). But you might see if the following helps you:

SELECT Facility, NoOfActiveApplicants, NoOfArchivedApplicants,
NoOfActiveApplicants + NoOfArchivedApplicants AS
TotalApplicants
FROM (SELECT COALESCE(facility, JobDBFacilityName) AS Facility,
(SELECT COUNT(*)
FROM tblapplicant AS a
INNER JOIN tblJobDB AS b
ON a.jobid = b.jobid
WHERE b.facility = c.facilityid) AS
NoOfActiveApplicants,
(SELECT COUNT(*)
FROM tblArchiveapplicant AS a
INNER JOIN tblArchiveJob AS b
ON a.JobVacancyNumber = b.JobVacancyNumber
WHERE b.facility = c.facilityid) AS
NoOfArchivedApplicants
FROM tblfacilities AS c) AS der
ORDER BY Facility

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi c0de ,
As Hugo correctly pointed out (see www.aspfaq.com/5006 for a better
way to ask questions in these groups). As a friendly advice please do
post DDL,DML as it become easier for others to test their queries .

All I can see you wish to prepare a report which can be done using
corelated subquery (I can be wrong because I am not having data with me
to check the correctness of my query)

Select F.facilityid,
(select count(*) from tblapplicant a, tblJobDB b where a.jobid =
b.jobid and b.facility=F.facilityid),
(select count(*) from tblArchiveapplicant a, tblArchiveJob b where
a.JobVacancyNumber = b.JobVacancyNumber and b.facility=F.facilityid)
from tblfacilities F

May this query solve your problem. As an another advice please use same
column name in all tables that are to be linked (related) .

With warm regards
Jatinder Singh