July 21, 2011 at 5:22 am
Hi,
I am working on a multi-select parameter report which has about 17 parameters for the user to select. However, most of the parameters are pre-selected, i.e. in most of the parameters, all the values are check marked.
The problem I am facing is the 'Select All' option for all the parameters does not show all the data, in fact its missing most of the data. Its only in the case that I select (check) individual values, that the particular row is displayed otherwise the report is blanked out even if that particular value has been included with the 'Select All' option.
Is there some other way to handle multi-select parameters ? Below are the main and sub procedure that I am using:
Main SP
------------------
ALTER PROCEDURE [dbo].[usp_QM_Details]
@Choose_Date [nvarchar](4000),
@From_date [nvarchar](4000),
@To_date [nvarchar](4000),
@direction [nvarchar](4000),
@fe [nvarchar](4000),
@Countryofreceipt [nvarchar](4000),
@POR [nvarchar](4000),
@POL [nvarchar](4000),
@POD [nvarchar](4000),
@Countryofdelivery [nvarchar](4000),
@PDL [nvarchar](4000),
@Trade [nvarchar](4000),
@Service [nvarchar](4000),
@VesselVoyage [nvarchar](4000)
/*
@scnolike [nvarchar](4000),
@CPSHCNNPLike [nvarchar](4000),
@Commoditylike [nvarchar](4000)
*/
--WITH EXECUTE AS CALLER
WITH RECOMPILE
AS
BEGIN
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
if @Choose_Date = 'SAILING'
exec usp_QM_Details_Sailing @From_date,@To_date,@direction, @fe, --@scnolike , @CPSHCNNPLike , @Commoditylike,
@Countryofreceipt, @POR, @POL, @POD, @Countryofdelivery,@PDL, @Trade, @Service, @VesselVoyage
/*
if @Choose_Date = 'ONBOARD'
exec usp_QM_Details_Onboard @From_date,@To_date, @direction, @fe, @scnolike , @CPSHCNNPLike , @Commoditylike,
@Countryofreceipt, @POR, @POL, @POD, @Countryofdelivery, @PDL, @Trade, @Service,
@VesselVoyage
*/
END
SET ANSI_WARNINGS ON
------------------
Sub-SP
------------------
ALTER PROCEDURE [dbo].[usp_QM_Details_Sailing]
@From_date [nvarchar](4000),
@To_date [varchar](4000) ,
@direction [nvarchar](4000),
@fe [nvarchar](4000),
@Countryofreceipt [nvarchar](4000),
@POR [nvarchar](4000),
@POL [nvarchar](4000),
@POD [nvarchar](4000),
@Countryofdelivery [nvarchar](4000),
@PDL [nvarchar](4000),
@Trade [nvarchar](4000),
@Service [nvarchar](4000),
@VesselVoyage [nvarchar](4000)
/*
@scnolike [nvarchar](4000),
@CPSHCNNPLike [nvarchar](4000),
@Commoditylike [nvarchar](4000)
*/
--WITH EXECUTE AS CALLER
WITH RECOMPILE
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
BEGIN
SELECT BLNO, SAILING_DT, ON_BOARD_SAILING_DT, DIRECTION, [F/E], SCNO, SHIPPER,FORWARDER,
CONSIGNEE, NOTIFY, [CONTROL PARTY], [NAYOSE NAME], COMMODITY, ORIGIN, POR, POL, POD, DEST, PDL, TRADE_CD, SAISAN_SERVICE, VESSEL, VOYAGE, LEG, TEU,
(VESSEL + VOYAGE) AS VSLVOY
FROM tbl_TMIS_DATASET
WHERE
SAILING_DT >= Convert(datetime, Convert(varchar, @From_date)) and
SAILING_DT <= Convert(datetime, Convert(varchar, @To_date)) and
DIRECTION IN (select distinct Item From dbo.Split(@direction,',')) AND
[F/E] IN (select distinct Item From dbo.Split(@fe,','))
and ORIGIN IN (select distinct Item From dbo.Split(@Countryofreceipt,','))
and POR IN (select distinct Item From dbo.Split(@POR,',')) and
POL IN (select distinct Item From dbo.Split(@POL,',')) AND
POD IN (select distinct Item From dbo.Split(@POD,',')) AND
DEST IN (select distinct Item From dbo.Split(@Countryofdelivery,',')) and
PDL IN (select distinct Item From dbo.Split(@PDL,',')) and
TRADE_CD IN (select distinct Item From dbo.Split(@Trade,',')) and
SAISAN_SERVICE IN (select distinct Item From dbo.Split(@Service,',')) and
(VESSEL + VOYAGE) IN (select distinct Item From dbo.Split(@VesselVoyage,','))
/* and
((SCNO like '%' + @scnolike + '%' and @scnolike is NOT NULL) or (@scnolike is NULL))
AND
((([CONTROL PARTY] like '%' + @CPSHCNNPLike + '%' and @CPSHCNNPLike is NOT NULL) or (@CPSHCNNPLike is NULL))
or ((SHIPPER like '%' + @CPSHCNNPLike + '%' and @CPSHCNNPLike is NOT NULL) or (@CPSHCNNPLike is NULL))
or ((CONSIGNEE like '%' + @CPSHCNNPLike + '%' and @CPSHCNNPLike is NOT NULL) or (@CPSHCNNPLike is NULL))
or ((NOTIFY like '%' + @CPSHCNNPLike + '%' and @CPSHCNNPLike is NOT NULL) or (@CPSHCNNPLike is NULL)))
AND ((Commodity like '%' + @Commoditylike + '%' and @Commoditylike is NOT NULL) or (@Commoditylike is NULL))
*/
END
SET ANSI_WARNINGS ON
-----------------
Thanks in advance for your help.
Regards,
paul
July 21, 2011 at 9:37 am
In your dataset query parameter values are you using a JOIN?
Does any of your data have commas in the values?
Have you looked at some of the issues, directly feeding your parameter values into the query with dbo.Split to see what comes out to make sure it is everything you expected?
July 25, 2011 at 9:56 am
Just a thought, do you require the input parameters to be defined too big like this.
then why dont you use between for date fields.
and check how it is running on DB using trace and you may get the issue.
Regards
Durai Nagarajan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply