September 29, 2010 at 5:01 am
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
September 29, 2010 at 6:32 am
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. 😉
September 29, 2010 at 8:14 am
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
September 29, 2010 at 8:20 am
select distinct servername from temp2
UNION
SELECT 'ALL'
Not giving required result.
Thank You.
Regards,
Raghavender Chavva
September 30, 2010 at 7:50 am
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)
September 30, 2010 at 12:23 pm
Abs-225476 (9/30/2010)
HiLike 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
October 1, 2010 at 2:12 am
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
October 1, 2010 at 8:33 am
Abs-225476 (9/30/2010)
HiLike 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.
October 2, 2010 at 1:58 am
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
October 4, 2010 at 8:05 am
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
October 4, 2010 at 8:40 am
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
October 5, 2010 at 11:23 am
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) )
October 5, 2010 at 12:49 pm
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. 😉
October 5, 2010 at 12:52 pm
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. 😉
October 6, 2010 at 4:40 am
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply