Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to give top and bottom filter expressions in one data set Expand / Collapse
Author
Message
Posted Friday, August 22, 2014 6:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 11:15 PM
Points: 6, Visits: 15
Hi Team,

This is Naveen Maroju.I am trying to do but m not getting please help me on this scenario.

I have a parameter called "VendorOrder", that parameter has two available values 1. Top 2. Bottom

If I select Top in that , then i should enter "Limit to top parameter" parameter value is 10 or give any value then

should display 10 or given value rows of Top or

If i select Top in that , then i was not entered any value then i checked check box is null it should returns for Descending order of the data.


If i select Bottom then i should enter "Limit to top parameter" parameter value is 10 or give any value then should display the Bottom 10 or given values rows of Bottom to the data set.

If i select Bottom in that , then i was not entered any value then i checked check box is null it should returns for Ascending order of the data.

If i select both top and bottom then it should returns for Descending order of the data

How can i do this. any information will be helpful.

Please refer to the attachment.


  Post Attachments 
image.png (11 views, 45.56 KB)
Post #1606251
Posted Friday, August 22, 2014 1:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 10,282, Visits: 13,265
The way I would handle this would be by passing the parameters as parameters to the query in the data set. So the query would look something like this:

IF @OrderParameter = 'TOP'
BEGIN;
SELECT TOP (@TopParameter)
*
FROM
sys.all_columns AS AC
ORDER BY
AC.name DESC;

END;
ELSE
BEGIN;
SELECT TOP (@TopParameter)
*
FROM
sys.all_columns AS AC
ORDER BY
AC.name;
END





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1606475
Posted Friday, August 22, 2014 2:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 1:33 PM
Points: 67, Visits: 237
You may ran this query,
Just change the name of the table: [dbo].[VB8_DA] with yours.
or call it from SSRS

create Procedure dbo.VendorReport
(@VendorOrders varchar(10),
@LimitToTopParameter int,
@Flag int
)
As
Begin
--if @Flag=1 mean NULL checkbox checked
declare @SQL nvarchar(max)
declare @SQLtop nvarchar(max)

if isnull(@LimitToTopParameter, 0)>0 and @Flag = 1
begin
print ' You cannot use both LimitToTopParameter and Checkbox checked as same time'
return
end

--If I select Top in that , then i should enter "Limit to top parameter" parameter value is 10 or give any value then
--should display 10 or given value rows of Top or
If (@VendorOrders='Top' and isnull(@LimitToTopParameter, 0)>0 and @Flag <> 1)
begin set @VendorOrders='' end
--If i select Top in that , then i was not entered any value then i checked check box is null it should returns for Descending order of the data
else If (@VendorOrders='Top' and isnull(@LimitToTopParameter, 0)=0 and @Flag = 1)
begin set @VendorOrders='Desc' end

--If i select Bottom then i should enter "Limit to top parameter" parameter value is 10 or give any value then should display the Bottom 10 or given values rows of Bottom to the data set.
If (@VendorOrders='Bottom' and isnull(@LimitToTopParameter, 0)>0 and @Flag <> 1)
begin set @VendorOrders='Desc' end
--If i select Bottom in that , then i was not entered any value then i checked check box is null it should returns for Ascending order of the data.
else If (@VendorOrders='Bottom' and isnull(@LimitToTopParameter, 0)=0 and @Flag = 1)
begin set @VendorOrders='Asc' end


if @LimitToTopParameter is null
begin set @SQLtop='' end
else
begin Set @SQLtop='TOP '+cast(@LimitToTopParameter as varchar(100)) end

set @sql='SELECT '+@SQLtop+' * FROM [dbo].[VB8_DA] order by 1 '+@VendorOrders

print @SQL
exec sp_executeSQL @SQl
end

exec dbo.VendorReport 'Top' , 10, 0
exec dbo.VendorReport 'Top' , NULL , 1
exec dbo.VendorReport 'bottom' , 10, 0
exec dbo.VendorReport 'bottom' , NULL, 1

Post #1606497
Posted Wednesday, September 3, 2014 5:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 11:15 PM
Points: 6, Visits: 15
Hi Team,

Thanks for giving reply.It is executing properly through procedure but as per my new requirement i am doing grouping for the one of the column to data set then it is giving result wrong .

I have a parameter called "VendorOrder", that parameter has two available values 1. Top 2. Bottom

If I select Top in that , then i should enter "Limit to top parameter" parameter value is 10 or give any value then

should display 10 or given value of sum of total ticketed by grouping of vendor names for Descending order of the data .

If i select Top in that , then i was not entered any value then i checked check box is null it should returns for of sum of total ticketed by grouping of vendor names for Descending order of the data.


If i select Bottom then i should enter "Limit to top parameter" parameter value is 10 or give any value then should display the Bottom 10 or given values rows of Bottom of sum of total ticketed by grouping of vendor names for Ascending order of the data.

If i select Bottom in that , then i was not entered any value then i checked check box is null it should returns of sum of total ticketed by grouping of vendor names for Ascending order of the data.

If i select both top and bottom then it should returns of sum of total ticketed by grouping of vendor names for Descending order of the data

How to give filter Bottom and Top operators in Group properties.

How can i do this. any information will be helpful.

Please refer to the attachments.
I am looking for favorable reply from your side..


  Post Attachments 
parameters.png (0 views, 17.74 KB)
Dataset1.png (3 views, 55.24 KB)
Post #1609970
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse