Parameter in SSRS report - SQL Server Forums

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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Parameter in SSRS report

Author

Topic

Nivas
Starting Member

6 Posts

Posted - 2009-01-28 : 15:36:18

Hi,

Im facing problem in creating a parameter which is a column in the Report.This column is derived from a formula on multiple columns from the dataset.

Let me explain with the example.

Student_Table

StudentID CountryCode Age
1 I 10
2 US 20
3 UK 30
4 AUS 22
5 CHN 11
6 US 22
7 US 21
8 US 23
9 US 24
10 US 25

Country_Table

CountryID CountryName
I INDIA
US USA
UK UNITEDKndm
AUS AUS
CHN CHINA

Query to combine these two table:

select studentid,countrycode,age,countrynmae

from dbo.student INNER JOIN dbo.country

ON countrycode=countryId

StudentID CountryCode Age CountryName
1 I 10 INDIA
2 US 20 USA
3 UK 30 UNITEDKndm
4 AUS 22 AUS
5 CHN 11 CHINA
6 US 22 USA
7 US 21 USA
8 US 23 USA
9 US 24 USA
10 US 25 USA

Now in SSRS

first dataset "Master"

Dataset query :

select studentid,countrycode,age,countrynmae

from dbo.student INNER JOIN dbo.country

ON countrycode=countryId

where countrycode IN(@country)

second dataset "Countrycode"

dataset Query:

select countryId from country

The table in layout contain StudentId,countrycode,CountryName,Age.

Now in the table i need a new column "ValidValue"

condition in "ValidValue" column:

=iif(Fields!countrycode.Value="US" and Fields!age.Value> 18,"Y","N")

The question is

How to create a Multi-value-parameter as "ValidValue" with ( "Y"/"N") values.

The report should display the data based on the "countrycode" and "Validvalue" parameters.

Please respond !!

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-28 : 19:29:16

Did you mean this?

Select studentid,countrycode,age,countryname,(Case when countrycode ='US' and age>18 then 'Y' else 'N' end)as Validvalue
from dbo.student INNER JOIN dbo.country
ON countrycode=countryId
where countrycode IN(@country)

Nivas
Starting Member

6 Posts

Posted - 2009-01-29 : 12:44:57

Yes This works!!!

But which is better option to use between the following

writing the condition in textbox expression or in the query in Data tab

1)=iif(Fields!countrycode.Value="US" and Fields!age.Value> 18,"Y","N") writing this in Validvalue Textbox
OR
2)(Case when countrycode ='US' and age>18 then 'Y' else 'N' end)as Validvalue
and drag this dataset value in to the Validvalue textbox in the layout?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 13:01:06

if this is only for display in report better do this in report textbox expression

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources