help nedded for MS reporting service 2000

Hi,
I am working on microsoft SQl server 2000 reporting services. I am
developing an application in c#.net in VS2003 which
will call reports in windowds forms and ask for user to select the
parameters for them.
Till now i had finished . Now I want to select multiple values for
parameters. and wants that for each combination a seprate report will
be drawn and it the end all the reports are merged in to one.
i.e.
suppose there are two parameters in a report and there are 10 available
values for first one and 20 for second parameter.
now i have selected 2 values for first parameter and 4 for second
parameter.
how this i can do in my program.
Please help me. because i am not finding any way to do it.
Thanks And Regards
DineshYou are not finding it because you cannot do this in RS 2000. RS 2000 does
not support multi-select parameters. RS 2005 does.
If it is possible to go to RS 2005 I would. For one thing, VS 2005 comes
will new controls that make integrating with RS much much easier. Plus RS
2005 has many good improvements: multi-select parameters, end user sorting,
performance improvements, etcs.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1164259279.407811.135710@.l12g2000cwl.googlegroups.com...
> Hi,
> I am working on microsoft SQl server 2000 reporting services. I am
> developing an application in c#.net in VS2003 which
> will call reports in windowds forms and ask for user to select the
> parameters for them.
> Till now i had finished . Now I want to select multiple values for
> parameters. and wants that for each combination a seprate report will
> be drawn and it the end all the reports are merged in to one.
> i.e.
> suppose there are two parameters in a report and there are 10 available
> values for first one and 20 for second parameter.
> now i have selected 2 values for first parameter and 4 for second
> parameter.
> how this i can do in my program.
> Please help me. because i am not finding any way to do it.
> Thanks And Regards
> Dinesh
>|||hi.......
i think u could not understand my problem properly.....
i know it is possible........
I had done the rendering and pring part of this for RS2000.
I had developed an application which will show all the reports on
report Server in a combobox.
here user will select a report if it contains parameters then it will
draw that many checklist boxes on the form if it does not have any
parameter then it will ask from user to select a format in which he
wantthe report.
now user will select a value for each parameter from checklistbox by
checking a single value in each check list box.
now those valuse will be passed in the rendor method to draw the
report.
after that report is drawn and printed.
till now i had finished.
now i wan to selct multiple values in checklistboxes.
suppose there are 2 parameters and user has selected 3 values in first
one and 2 values in second one
then i will get 6 parameters to pass in render method.
then after passing one set i will get one report i will store it in a
string varible and append others after it. ie. i will got total 6
reports which will be merged one after others.
if it is always fixed then no problem i can do it in for loop.
but since number of parameters are not fixed so i want to know how can
get the parameters set dynamically. for any combimation of parameters.
i.e.
i want each combination of parameter and want to pass it to render
method whioch i wrote in a seprate function.
Hope i have explained my problem completely.
if any body have any idea please tell me.
Regards
Dinesh
Bruce L-C [MVP] wrote:
> You are not finding it because you cannot do this in RS 2000. RS 2000 does
> not support multi-select parameters. RS 2005 does.
> If it is possible to go to RS 2005 I would. For one thing, VS 2005 comes
> will new controls that make integrating with RS much much easier. Plus RS
> 2005 has many good improvements: multi-select parameters, end user sorting,
> performance improvements, etcs.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dinesh" <dinesht15@.gmail.com> wrote in message
> news:1164259279.407811.135710@.l12g2000cwl.googlegroups.com...
> > Hi,
> >
> > I am working on microsoft SQl server 2000 reporting services. I am
> > developing an application in c#.net in VS2003 which
> > will call reports in windowds forms and ask for user to select the
> > parameters for them.
> > Till now i had finished . Now I want to select multiple values for
> > parameters. and wants that for each combination a seprate report will
> > be drawn and it the end all the reports are merged in to one.
> >
> > i.e.
> > suppose there are two parameters in a report and there are 10 available
> > values for first one and 20 for second parameter.
> > now i have selected 2 values for first parameter and 4 for second
> > parameter.
> > how this i can do in my program.
> > Please help me. because i am not finding any way to do it.
> >
> > Thanks And Regards
> >
> > Dinesh
> >|||Hello,
I am trying to have both comma delimited and tab delimited output
option from sql 2000 reporting services. I have updated the config file
to have 2 output option in the render section of the config file. But I
always get 2 options listed on the dropdown as CSV - Comma Delimited, I
didn't get the tab-delimited option. Why my code in the config file
didn't get affected? Anything else I have to do, or is it all possible
in RS 2000.
Any help would be really appreciated.
Thanks,
Ravi

Help me to write this query

Hi Everybody,
I have a table call Employee like this
EmpId DOB Sex
1 01/02/1982 Male
2 01/01/1983 Female

And also i have got Contacts Table looks like this
ConId EmpId ConDate Description
1 1 02/02/2007 Not Specified
2 1 02/03/2007 Not Specified
3 2 01/01/2007 Personal

Now i need to display like this

Description Male Female Age 0-10 Age 10+
Not Specified 1 0 0 1
Personal 0 1 0 1

When calculation the age If one employee has got more than one contacts then we need to get the maximum ConDate from the Contacts table and then get the DOB from the Employee table and and get the DateDiff by year

How can can i do this query ?
Any Idea ?

regards
suis

Here You go..

Code Snippet

Create Table #employee (

[EmpId] int ,

[DOB] datetime ,

[Sex] Varchar(100)

);

Insert Into #employee Values('1','01/02/1982','Male');

Insert Into #employee Values('2','01/01/1983','Female');

--Completed :: employee

--Sample Table :: employeecontact

Create Table #employeecontact (

[ConId] int ,

[EmpId] int ,

[ConDate] datetime ,

[Description] Varchar(100)

);

Insert Into #employeecontact Values('1','1','02/02/2007','NotSpecified');

Insert Into #employeecontact Values('2','1','02/03/2007','NotSpecified');

Insert Into #employeecontact Values('3','2','01/01/2007','Personal');

--Using SQL Server 2000

Select

Description,

Case When Sex='Male' Then 1 Else 0 End Male,

Case When Sex='Female' Then 1 Else 0 End Female,

Case When Datediff(YY,DOB,getdate()) <=10 Then 1 Else 0 End [Age 0-10],

Case When Datediff(YY,DOB,getdate()) >10 Then 1 Else 0 End [Age 10+]

from

#employee E

Join (Select

EC.EmpID,EC.Description

From

#employeecontact EC

Join (

Select

[EmpId]

,Max([ConDate]) ConDate

From

#employeecontact

Group By [EmpId]

) as Data On Data.ConDate =EC.ConDate and Data.EmpID = EC.EMPID) C On E.EmpID=C.EmpID

--Using SQL Server 2005

;With CTE as

(

Select EmpId,Description, ROW_NUMBER() Over (Partition By EmpId Order By [ConDate] Desc) RowId From #employeecontact

)

Select

Description,

Case When Sex='Male' Then 1 Else 0 End Male,

Case When Sex='Female' Then 1 Else 0 End Female,

Case When Datediff(YY,DOB,getdate()) <=10 Then 1 Else 0 End [Age 0-10],

Case When Datediff(YY,DOB,getdate()) >10 Then 1 Else 0 End [Age 10+]

from

#employee E

Join CTE C On E.EmpId = C.EmpId

Where

C.RowID=1

|||

Code Snippet

create table #emp

( EmpId int, DOB datetime, Sex varchar(10))

insert into #emp values (1, '01/02/1982', 'Male')

insert into #emp values (2, '01/01/1983', 'Female')

insert into #emp values (3, '10/01/1999', 'Female')

create table #contacts

( ConId int, EmpId int, ConDate datetime, Description varchar(100))

insert into #contacts values (1, 1, '02/02/2007', 'Not Specified')

insert into #contacts values (2, 1, '02/03/2007', 'Not Specified')

insert into #contacts values (3, 2, '01/01/2007', 'Personal')

insert into #contacts values (3, 3, '5/01/2007', 'Personal')

;with contact1 as

(

select c.EmpId, c.Description, max(c.ConDate) as ConDate

from #contacts c

group by c.EmpId, c.Description

),

contact2 as

(

select c.EmpId, c.Description, c.ConDate, e.Sex,

datediff(yy, e.dob, c.ConDate)

+ case when (month(e.dob)*100)+day(e.dob)>(month(c.ConDate)*100)+day(c.ConDate)

then -1 else 0

end as Age

from contact1 c

inner join #emp e

on c.EmpId = e.EmpId

)

select Description,

sum(case Sex when 'Male' then 1 else 0 end) as Male,

sum(case Sex when 'Female' then 1 else 0 end) as Female,

sum(case when Age <=10 then 1 else 0 end) as 'Age 0-10',

sum(case when Age > 10 then 1 else 0 end) as 'Age 10+'

from contact2 c

group by Description

|||Hi Manivannan.D.Sekaran
Many Many thanks for your quick response.this is great help for me,
i will have a look this query and let u know ASAP the result,cos this query is bit tau ff for me,

again thank you so much for your valuable response.

this is great forum.....i love this forum........

regards
suis
|||hi Dale and Sekaran
Its very useful u r comments to sort out my problem
now my query is working perfectly,i had to do some modification for that,becase my database structure is not like as i post ,becase its just sample database format,

anyway thanks for the quick response from you two .

many thanks to .Net forum......nice work

regards
suis

help me to get this out put ?

I everybody

I have table Call EmpTran like this

EmpId TranId Opt S_Date Ar_Date
1 2 1 01/01/2007 NULL
1 2 15 03/02/2007 NULL
1 2 12 04/03/2007 01/04/2006
2 3 55 01/01/2006 NULL
3 4 53 01/01/2006 NULL
3 4 58 01/01/2006 01/04/2007

SQL for Create Table and values--
create table EmpTran
(
EmpId int,
TranId int ,
Opt int,
S_Date DATETIME,
Ar_Date DATETIME
)

insert into EmpTran values(1,2,1,'01/01/2007',NULL)
insert into EmpTran values(1,2,15,'03/02/2007',NULL)
insert into EmpTran values(1,2,12,'04/03/2007','01/04/2006')
insert into EmpTran values(2,3,55,'01/01/2006',NULL)
insert into EmpTran values(3,4,53,'01/01/2006',NULL)
insert into EmpTran values(3,4,58,'01/01/2006','01/04/2007')

No i am bit stuck when applying these condition in to this table and pupulate the out put
so my conditions is like this
I need to find out a EmpId,TranId,Opt,S_Date,Ar_Date where Ar_Date Is Null
and then if a one employee is get reapeated morethan once and if that EmpId got Ar_Date then i need to get earliest Ar_Date in that emp_Id and check that Ar_Date is less than the S_date of that Perticular Row then display the values

bellow i explained by each row data condition,

EmpId TranId Opt S_Date Ar_Date
1 2 1 01/01/2007 NULL 'Should display,cos Ar_date is null + Max Ar_date is <S_Date
1 2 15 03/02/2007 NULL 'Should display,cos Ar_date is null + Max Ar_date is <S_Date
1 2 12 04/03/2007 01/04/2006 'should not include cos this got Ar_date
2 3 55 01/01/2006 NULL 'Should not include this cos EmpId not repeated
3 4 53 01/01/2006 NULL 'should not get this row,cos Ar_date is > S_Date
3 4 58 01/01/2006 01/04/2007 'should not include cos this got Ar_date

so after all these requirement satisfy my table should looks like this
EmpId TranId Opt S_Date Ar_Date
1 2 1 01/01/2007 NULL
1 2 15 03/02/2007 NULL

any idea ?

regards
suis

If you use sql server 2005,

Code Snippet

Create Table #EmpTran (

EmpId int,

TranId int ,

Opt int,

S_Date DATETIME,

Ar_Date DATETIME

)

insert into #EmpTran values(1,2,1,'01/01/2007',NULL)

insert into #EmpTran values(1,2,15,'03/02/2007',NULL)

insert into #EmpTran values(1,2,12,'04/03/2007','01/04/2006')

insert into #EmpTran values(2,3,55,'01/01/2006',NULL)

insert into #EmpTran values(3,4,53,'01/01/2006',NULL)

insert into #EmpTran values(3,4,58,'01/01/2006','01/04/2007')

;With CTE

as

(

Select

*

, Max(Ar_Date) Over (Partition By EmpId) Max_Ar_Date

, Count(EmpId) Over(Partition By EmpId) Count_EmpId

From

#EmpTran

)

Select

EmpId

, TranId

, Opt

, S_Date

, Ar_Date

from

CTE

Where

Count_EmpId > 1 And Ar_Date is NULL And S_Date > Max_Ar_Date

If you are using Sql Server 2000,

Code Snippet

Select

EmpId

, TranId

, Opt

, S_Date

, Ar_Date

From

#EmpTran Main

Join (Select

EmpId MaxEmpId,

Max(Ar_Date) Max_Ar_Date,

Count(EmpId) Count_EmpId

From #EmpTran

Group By EmpId) as MaxData On MaxData.MaxEmpId = Main.EmpID

Where

Count_EmpId > 1

And Ar_Date is NULL

And S_Date > Max_Ar_Date

|||Hi i have a small question out of that SQL ?
my question is what is the reason u start to write u r sql using

;With CTE

as

?

Whats that mean ?

I am using Sql server 2000 ? not 2005 ?

can i write the same sql in sql2000 ?

regards

Suis

|||

This is one of the new feature in SQL Server 2005 called Common Table Expression.

You can use derived table also here. But I like to use CTE; it has more advantages than Derived tables.

|||Hi
Problem is i am using sql server 2000 ?
how can i write the same sql in SQL server 2000 ?
regards
suis|||check my first post i edited it; you can get the query for sql server 2000 also.|||hi
sorry i didnt read the bottom part of u r comments,
i will try the sql server 2000 part and let u know the result
thanks
regards
suis|||Hi kent,
According to my data SQL looks alright ? i got the correct result !
thanks
regards
suis