Reporting Parameters

Question

Post reply

Reporting Parameters

Raghavender Chavva

SSCrazy Eights

Points: 8669
More actions
September 29, 2010 at 5:01 am

#224449

Hi All,
I am developing one Report. I got stuck in middle. Please help me ?
I have a report in which I need to run report based on the values supplied to the parameters. I have developed this report upto I can select a parameter value from drop down list of the report. But Here I need to develop it as if I select "ALL" it should give us total report and If i select any value instead of "ALL" result should be based on that parameter value.
Please help me how to do it ?
Thank You.
Regards,
Raghavender Chavva

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

veeren4urs SSCrazy Points: 2763 More actions · Answer 1

hi,

try below one

http://forums.devarticles.com/microsoft-sql-server-5/sql-reporting-services-parameters-90768.html

You can create parameters of your reports in SSRS whose data sources are datasets created in the data tab of the report.

In this dataset, by modifying your sql codes you can add "ALL" by adding an extra record which has a description "ALL" and a value "0" or "-1", etc.

I mean, if you set country parameter by

SELECT CountryId, Code FROM Countries

modify your script like

SELECT CountryId, Code FROM Countries

UNION

SELECT 0, 'ALL'

You can further modify the script by adding an order by clause

And where this parameters are sent, you can check the @CountyId parameter passed and if it is 0 set it to null

The easiest way of using such parameters is like

SELECT ....

WHERE

(@CountryId ISNULL OR CountyId = @CountryId)

I hope these will help you start solving the problem

Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 2

Select distinct servername from temp2 is the query in the dataset.

Will below code works?

SELECT distinct servername from temp2

UNION

SELECT 0, 'ALL'

or do I need to do any modifications ?

Thank You.

Regards,
Raghavender Chavva

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 3

select distinct servername from temp2

UNION

SELECT 'ALL'

Not giving required result.

Thank You.

Regards,
Raghavender Chavva

Abs-225476 Hall of Fame Points: 3483 More actions · Answer 4

Hi

Like Veeren4urs said you need to have a dataset which will provide a pick list for the parameters, something like :

select distinct country_name from customers

union all

select 'ALL'

Then you need to specify this in the parameter as the available values.

In the dataset which provides the data for the report you will need code something like this in the where clause . .

Select customer_id, customer_name,country_name . . .etc from customers

where country_name in

(case when @parameter = 'all then country_name else @parameter end)

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 5

Abs-225476 (9/30/2010)
Hi
Like Veeren4urs said you need to have a dataset which will provide a pick list for the parameters, something like :
select distinct country_name from customers
union all
select 'ALL'
Then you need to specify this in the parameter as the available values.
In the dataset which provides the data for the report you will need code something like this in the where clause . .
Select customer_id, customer_name,country_name . . .etc from customers
where country_name in
(case when @parameter = 'all then country_name else @parameter end)

Yes, all are there in my report already. But not getting the required results.

Thank You.

Regards,
Raghavender Chavva

Abs-225476 Hall of Fame Points: 3483 More actions · Answer 6

Hi,

Have you tried the query below in query analyser so you can determine whether the issue is with the report or the sql query?

Select customer_id, customer_name,country_name . . .etc from customers

where country_name in country_name

VinceV Right there with Babe Points: 791 More actions · Answer 7

Abs-225476 (9/30/2010)
Hi
Like Veeren4urs said you need to have a dataset which will provide a pick list for the parameters, something like :
select distinct country_name from customers
union all
select 'ALL'
Then you need to specify this in the parameter as the available values.
In the dataset which provides the data for the report you will need code something like this in the where clause . .
Select customer_id, customer_name,country_name . . .etc from customers
where country_name in
(case when @parameter = 'all then country_name else @parameter end)

Abs-225476's suggestion should work. A single quote was missing from case when @parameter = 'all then country_name else @parameter end but that's obvious. Did you get any kind of error messges?

As a side note, I would rather generate the SQL conditionally by an expression. The condition being whether the user chose 'ALL' or some other item. Your query will run much faster but YMMV depending on your table size and other factors.

H4K Hall of Fame Points: 3374 More actions · Answer 8

hi,

write following query in a dataset and then add that dataset to the report parameter named say => ServerName

select distinct servername from temp2

union

select 'ALL'

Add the following query to the already existing query which will bring the details for a selected value of report parameter 'ServerName' which exist in another dataset.

select * from ServerDetails a

where a.ServerName = coalesce(nullif(@ServerName,'ALL'), a.ServerName)

Regards,

Amar Sale

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

Ganesh Lohani Hall of Fame Points: 3091 More actions · Answer 9

Hi,

One of the approcahes is to apply multivalue parameter in SSRS report.

Check the following link.

http://msdn.microsoft.com/en-us/library/aa337396.aspx

Thanks

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 10

amarsale (10/2/2010)
hi,
write following query in a dataset and then add that dataset to the report parameter named say => ServerName
select distinct servername from temp2
union
select 'ALL'
Add the following query to the already existing query which will bring the details for a selected value of report parameter 'ServerName' which exist in another dataset.
select * from ServerDetails a
where a.ServerName = coalesce(nullif(@ServerName,'ALL'), a.ServerName)
Regards,
Amar Sale

Sorry Guys, Nothing is working fine for me.

to give you a better scenario , I am pasting my 2 datasets queries

Main one:

SELECT servername, dbname, CollectionDateTime, datafileinmbs, logfileinmbs, databaseinmbs

FROM temp2

WHERE (CollectionDateTime BETWEEN @time1 AND @time2) AND ( servername = coalesce(nullif('ALL')), @ServerName)

and also tried below one:

SELECT servername, dbname, CollectionDateTime, datafileinmbs, logfileinmbs, databaseinmbs

FROM temp2

WHERE (CollectionDateTime BETWEEN @time1 AND @time2) AND ( servername = coalesce(nullif(@Servername,'ALL')), ServerName)

Second dataset is:

select distinct servername from temp2

union

select 'ALL'

Thank You.

Regards,
Raghavender Chavva

VinceV Right there with Babe Points: 791 More actions · Answer 11

Your second main query should work but you had a parenthesis in the wrong place. Here's the corrected version:

SELECT servername, dbname, CollectionDateTime, datafileinmbs, logfileinmbs, databaseinmbs

FROM temp2

WHERE (CollectionDateTime BETWEEN @time1 AND @time2) AND ( servername = COALESCE(NULLIF(@Servername,'ALL'), ServerName) )

veeren4urs SSCrazy Points: 2763 More actions · Answer 12

Hi ,

If you can able to achive this thats well and good man:)

thanks,

Veeren

Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉

veeren4urs SSCrazy Points: 2763 More actions · Answer 13

Hi,

Make it simple your query like below logic

If (parameter=ALL)

{

Select * from table}

else

{

select * from table where =@param

}

Thanks,

Veeren

Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 14

vixvu (10/5/2010)
Your second main query should work but you had a parenthesis in the wrong place. Here's the corrected version:
SELECT servername, dbname, CollectionDateTime, datafileinmbs, logfileinmbs, databaseinmbs
FROM temp2
WHERE (CollectionDateTime BETWEEN @time1 AND @time2) AND ( servername = COALESCE(NULLIF(@Servername,'ALL'), ServerName) )

Looks like my problem got solved from above query.

but will confirm after some more testings.

Thanks everyone for your valuable suggestions.

Thank You.

Regards,
Raghavender Chavva