I Created a Stored Procdure In SQL Server To Do a Select From Multiple Table With Multiple Optional Parameters and Retrieve This Data in Rdlc Report in Report Viewer
Here Is The Code Of Proce
ALTER PROC [dbo].[SP_SalesReport]
@Sheet Nvarchar(50) = NULL,
@Plot Nvarchar(50) = NULL,
@Category Nvarchar(50) = NULL,
@Type Nvarchar(50) = NULL,
@Variety Nvarchar(50) = NULL,
@Sector Nvarchar(50) = NULL,
@Owner Nvarchar(50) = NULL,
@Company Nvarchar(50) = NULL,
@Country varchar(2) = NULL,
@Statues Nvarchar(50) = NULL,
@Activity Nvarchar(7) = NULL,
@UnitPrice Decimal(18,3) = NULL,
@Week INT = NULL,
@Date DATE = NULL
AS
BEGIN
SELECT
Sheets.SheetNo AS 'SheetNumber',
Plots.PlotName AS 'Plot',
Plots.GrossArea AS 'GrossArea',
RootStocks.RootStockName AS 'RootStock',
Categories.CategoryName AS 'Category',
Types.TypeName AS 'Type',
Varieties.VarietyName AS 'Variety',
Sectors.SectorName AS 'Sector',
Owners.OwnerName AS 'Owner',
CONVERT(Varchar(3),DATENAME(WEEKDAY, Sheets.Date)) + ' - '+
CONVERT (varchar(30),
+ DATENAME(DAY, Sheets.Date) + ' '
+ CONVERT(Varchar(3),DATENAME(MONTH, Sheets.Date)) + ' '
+ DATENAME(YEAR, Sheets.Date)) AS 'Date',
DATEPART( wk, Sheets.Date) AS 'Week',
CONVERT(VARCHAR(5), Sheets.Time,108) AS 'Time',
Cars.CarNo AS 'Car',
Cars.Driver AS 'Driver',
Companies.CompanyName AS 'Company',
(CASE WHEN Companies.CompanyActivity = 0 THEN 'Local'
ELSE 'Export'
END) AS Activity,
Companies.CountryRegionCode AS 'Country',
Representatives.RepresentativeName AS 'Representative',
Orders.Boxes AS 'Boxes',
Orders.QuantityKG AS 'Quantity',
CONVERT(NVARCHAR(18),Orders.UnitPrice) + ' ' + Orders.CurrencyCode AS 'UnitPrice',
CONVERT(Nvarchar(50),(CONVERT(DECIMAL(18,3), Orders.UnitPrice * Orders.QuantityKG)))+ ' ' + Orders.CurrencyCode AS 'TotalPrice',
CONVERT(DECIMAL(18,3), Orders.QuantityKG / Orders.Boxes) AS 'BoxAverage',
ExportType.TypeName AS 'ExportType',
(CASE WHEN Orders.Statues = 0 THEN 'Paid'
ELSE 'Pending'
END) AS 'Statues',
Orders.PendingPeriod AS 'PendingPeriod',
CONVERT(DECIMAL(18,3),Orders.QuantityKG/Plots.GrossArea) AS 'AverageQuantityGArea',
CONVERT(DECIMAL(18,3), Orders.QuantityKG/(SELECT NetArea FROM PlotDescriptionByYear WHERE PlotID = Orders.PlotID AND Season = YEAR(Sheets.Date)) ) AS 'AverageQuantityNArea',
CONVERT(DECIMAL(18,3), Orders.QuantityKG / (SELECT PlotDescriptionByYear.NumberOfPlants FROM PlotDescriptionByYear WHERE PlotDescriptionByYear.Season = YEAR(Sheets.Date) AND PlotID = Orders.PlotID)) AS 'AverageQuantityPlantsNumber',
CONVERT(NVARCHAR(50),CONVERT(DECIMAL(18,3),CONVERT(DECIMAL(18,3), Orders.UnitPrice * Orders.QuantityKG)/CONVERT(DECIMAL(18,3),(Plots.GrossArea)))) + ' ' + Orders.CurrencyCode AS 'AveragePriceGArea',
CONVERT(NVARCHAR(18),CONVERT(DECIMAL(18,3),CONVERT(DECIMAL(18,3), Orders.UnitPrice * Orders.QuantityKG)/CONVERT(DECIMAL(18,3),(SELECT NetArea FROM PlotDescriptionByYear WHERE Season = YEAR(Sheets.Date) AND PlotID = Orders.PlotID)))) + ' ' + Orders.CurrencyCode AS 'AveragePriceNArea',
CONVERT(NVARCHAR(18),CONVERT(DECIMAL(18,3), Orders.UnitPrice * Orders.QuantityKG / (SELECT PlotDescriptionByYear.NumberofPlants FROM PlotDescriptionByYear WHERE Season = YEAR(Sheets.Date) AND PlotDescriptionByYear.PlotID = Orders.PlotID))) + ' ' + Orders.CurrencyCode AS 'AveragePricePlantsNumber'
FROM
Sheets
INNER JOIN
Orders ON Sheets.SheetID = Orders.SheetID
INNER JOIN
Plots ON Orders.PlotID = Plots.PlotID
INNER JOIN
PlotDescriptionByYear ON PlotDescriptionByYear.PlotID = Plots.PlotID
INNER JOIN
Companies ON Orders.CompanyID = Companies.CompanyID
INNER JOIN
Representatives ON Orders.RepresentativeID = Representatives.RepresentativeID
INNER JOIN
ExportType ON Orders.ExportTypeID = ExportType.TypeID
INNER JOIN
RootStocks ON PlotDescriptionByYear.RootStockID = RootStocks.RootStockID
INNER JOIN
Categories ON PlotDescriptionByYear.CategoryID = Categories.CategoryID
INNER JOIN
Types ON PlotDescriptionByYear.TypeID = Types.TypeID
INNER JOIN
Varieties ON PlotDescriptionByYear.VarietyID = Varieties.VarietyID
INNER JOIN
Cars ON Orders.CarID = Cars.CarID
INNER JOIN
Sectors ON Plots.SectorID = Sectors.SectorID
INNER JOIN
Owners ON Plots.OwnerID = Owners.OwnerID
WHERE PlotDescriptionByYear.Season = YEAR(Sheets.Date)
AND (@Sheet IS NULL OR (Sheets.SheetNo = @Sheet)) AND
(@Plot IS NULL OR (Plots.PlotName = @Plot)) AND
(@Category IS NULL OR (Categories.CategoryName = @Category)) AND
(@Type IS NULL OR (Types.TypeName = @Type)) AND
(@Variety IS NULL OR (Varieties.VarietyName = @Variety)) AND
(@Sector IS NULL OR (Sectors.SectorName = @Sector)) AND
(@Owner IS NULL OR (Owners.OwnerName = @Owner)) AND
(@Company IS NULL OR (Companies.CompanyName = @Company)) AND
(@Country IS NULL OR (Companies.CountryRegionCode = @Country)) AND
(@Statues IS NULL OR ((CASE WHEN Orders.Statues = 0 THEN 'Paid' ELSE 'Pending' END) = @Statues)) AND
(@Activity IS NULL OR ((CASE WHEN Companies.CompanyActivity = 0 THEN 'Local' ELSE 'Export' END) = @Activity)) AND
(@UnitPrice IS NULL OR (Orders.UnitPrice = @UnitPrice)) AND
(@Week IS NULL OR ((DATEPART( wk, Sheets.Date)) = @Week)) AND
(@Date IS NULL OR (Sheets.Date = @Date))
END
and The User Want To be able to search with 1 param , 2 params and 3 params
so i created a win form that have the report viewer and putted 3 combo boxes each one of them have the parameters of the proc so when the user choose paramters it send the values of choosen parameters and the other parameter value will be NULL so the problem is i tried to do this with NESTED IF i found that i will make a huge amount of IF statements due to the Combinations of the parameters so i am asking about finding any other short way to do this ?!