Click here to Skip to main content
16,007,610 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hello Friends,
I have 2 parameter in my report
DECLARE @ARTWRKTYP INT<br />
DECLARE @IARTWRKTYP INT

and the dependance of both parameter as follows
IF @ARTWRKTYP = 1
BEGIN
	SET @IARTWRKTYP = 2
END
ELSE IF @ARTWRKTYP = 4
BEGIN
	SET @IARTWRKTYP = 5
END
ELSE IF  @ARTWRKTYP = 3
BEGIN
	SET @IARTWRKTYP = 6
END

How i use this parameter in Query
select M.jobno [JobNo],clientname [Client],brandname [Brand],projectname [Project],awcategoryname [Artwork],CONVERT(VARCHAR(10),M.mechanicaldate,103)[Job Date],
case 
	when M.artworktype = 1 then 'Artwork Master' 
	when M.artworktype = 4 then 'Creative Master'
	when M.artworktype = 9 then 'Studio Master'
end [JobType]
from tbl_mechanicalbrief M
INNER JOIN tbl_client C ON C.clientid= M.clientid
INNER JOIN tbl_brand B ON B.brandid=M.brandid
INNER JOIN tbl_project P ON P.projectid=M.projectid
INNER JOIN tbl_awcategory AW ON AW.awcategoryid=M.artworkcategoryid
where (CONVERT(date, M.Mechanicaldate,103) between CONVERT(date,@fromdate,103) and CONVERT(date,@todate,103))
AND M.artworktype = @ARTWRKTYP and M.statusid = 45
UNION ALL
select M.jobno [JobNo],clientname [Client],brandname [Brand],projectname [Project],awcategoryname [Artwork],CONVERT(VARCHAR(10),M.iterationdate,103)[Job Date],
case 
	when M.artworktype = 2 then 'Artwork Iteration' 
	when M.artworktype = 5 then 'Creative Iteration'
	when M.artworktype = 7 then 'Repro'
	when M.artworktype = 8 then 'Alia Central'
	when M.artworktype = 10 then 'Studio Iteration'
end [JobType]
from tbl_iteration M
INNER JOIN tbl_client C ON C.clientid= M.clientid
INNER JOIN tbl_brand B ON B.brandid=M.brandid
INNER JOIN tbl_project P ON P.projectid=M.projectid
INNER JOIN tbl_awcategory AW ON AW.awcategoryid=M.artworkcategoryid
where (CONVERT(date, M.iterationdate,103) between CONVERT(date,@fromdate,103) and CONVERT(date,@todate,103))
AND M.artworktype = @IARTWRKTYP and M.statusid = 45


How can I define such dependance in SSRS??

Any idea?

Thanks in advance. .
Posted

1 solution

I am not sure if i understand the queustion correctly, but i will put i what i under stood:

You are trying to develop a report, which uses one parameter(@ARTWRKTYP), depending on that parameter you will update another parameter(@IARTWRKTYP), then you will get data using the logic.

Is this what you are trying to do...?

Thank you
V.
 
Share this answer
 
Comments
dhage.prashant01 15-Jun-11 3:31am    
Thanks for your reply
Actually what you said is right, I have taken an extra parameter @IARTWRKTYP whose value depend on @ARTWRKTYP.
I was not able to update value of @IARTWRKTYP in SSRS.
I have solved the problem by using case statement in SQL Query, which reduced the use of @IARTWRKTYP parameter.
But surely will like to know, how to set value of one parameter based on value of other parameter in SSRS

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900