August 22, 2014 at 6:49 am
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.
August 22, 2014 at 1:04 pm
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
Consultant - Straight Path Solutions
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
August 22, 2014 at 2:12 pm
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
September 3, 2014 at 5:52 am
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..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply