March 15, 2012 at 2:02 am
I have a doubt here,
I have a table consists of a column "Marks", from this marks column values I've to display Pass if the mark >=50, else Fail. and in my report I've a parameter "Parem", if I give parem value as 0 it should display only the Fails marks and if it is 1 it should display Pass only, if it is 2 both pass and fail marks should be displayed.For this am writing the query as
SELECT MARKS,
CASE WHEN MARKS >= 50 THEN 'PASS'
ELSE 'FAIL'
END AS STATUS
FROM TableName
WHERE (@Param = 1 AND MARKS >= 50)
OR (@Param = 0 AND MARKS < 50)
OR (@Param is null )
when I execute this query in my management studio am getting the expected results, the same query when I execute in my SSRS, it seperates the where conditions as
Where (@Param = 1) AND (Marks >= 50) OR
(@Param = 0) AND (Marks < 50) OR
(@Param IS NULL)
so if I give 0 it shows even the pass marks, how can I give the parameters here, can anyone help me out...thanks in advance
March 15, 2012 at 3:32 am
i have just created a sample report based on the query you provided using both a procedure and also the sql text embeded into a data set using a parameter of boolean allowing nulls and I get the required output
can you zip and upload the rdl file so I can take a look
create table t1 (marks int)
insert into t1 (marks) values (32),(40),(51),(68)
CREATE PROCEDURE getmarks (@param bit)
AS
SELECT MARKS,
CASE WHEN MARKS >= 50 THEN 'PASS'
ELSE 'FAIL'
END AS STATUS
FROM t1
WHERE (@Param = 1 AND MARKS >= 50)
OR (@Param = 0 AND MARKS < 50)
OR (@Param is null )
March 15, 2012 at 4:12 am
Hi , thanks for ur response
I got the solution,
SELECT
MARKS, STATUS
FROM
(
SELECT MARKS,
CASE WHEN MARKS >= 50 THEN 'PASS'
ELSE 'FAIL'
END AS STATUS
FROM TableName
)
AS Tmp
WHERE
STATUS = CASE
WHEN @Param = 0 THEN 'FAIL' -- Fail Results
WHEN @Param = 1 THEN 'PASS' -- Pass Results
WHEN @Param = 2 THEN STATUS -- All Results
END
but the problem is when I execute this in SSRS am getting error like "the CLR type doesn't exist or you don't have the permissions to access it', how can I fix this
March 15, 2012 at 4:27 am
i have attached the report rdl which I used, just give it a data source which points to the data and then create the query as a procedure and then modify the data sets to point to the data source and it should work
March 15, 2012 at 4:29 am
thanks anthony.green I'll check it and then let you know
March 15, 2012 at 4:33 am
again with the test1.rdl i still get the right results back from the test data I created in the eailer post using the first query you gave after modifing the parameter to accept a null value
March 15, 2012 at 4:34 am
is it....wait I'll check
March 15, 2012 at 4:56 am
ya its working, I think the problem is when joining the tables, here I've to join more than 3 tables, I'll check it....thanks for ur respone anthony
March 15, 2012 at 4:58 am
please post the DDL of the tables, the full query and test data and we can help you further.
March 15, 2012 at 5:06 am
thanks anthony for ur kind, the problem is am a newbie to sql I dunno how to take the DML. the report is working now, I found the problem, I'll let you know once I complete this, thanks again for ur kind response
March 15, 2012 at 5:40 am
in that query if I give 2 it shows nothing, what can I do to display both pass and fail
March 15, 2012 at 6:12 am
you either need to pass in a null value so that it gets everything, or change the last statement from @parm is null to @param = 2
March 15, 2012 at 6:15 am
you are awesome anthony.....thanks alot
March 15, 2012 at 6:22 am
as a follow on from this, read the first link in my signature block, it will help you in the future on how to post code for tables procedures and sample data so that we can re-create a testing area to help you out quicker in the future on anything t-sql related
March 15, 2012 at 6:36 am
how can we write the same query using else condition
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply