none
Error en Reporte RDL al agregar campo de procedimiento almacenado RRS feed

  • Pregunta

  • Tengo el siguiente procedimiento almacenado

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    ALTER PROCEDURE [dbo].[uspx_SIS_reportShippingReportRDL]
    	@RouteID int ,
    	@PickTicket bit = 0,
    	@ShowOrderedSize bit = 0
    AS
    	SET NOCOUNT ON
    
    	DECLARE @RetVal varchar(255)
    
    	DECLARE @ReturnManufacturedDimensions bit
    
    	IF @ShowOrderedSize = 1
    		SET @ReturnManufacturedDimensions = 0
    	ELSE
    		SET @ReturnManufacturedDimensions = 1
    
    	DECLARE @RoutesTable TABLE(RouteID int, RouteName nvarchar(50), Employee nvarchar(50), ShippingRoute nvarchar(20), TargetShipDate smalldatetime, ShipDate smalldatetime, RouteWeight real, RouteLocationID varchar(6), TruckID varchar(50), PRIMARY KEY(RouteID))
    	DECLARE @StopsTable TABLE(RouteID int, StopID smallint, CustomerName nvarchar(255), ExpectedArrivalDate smalldatetime, DeliveredDate smalldatetime, DeliveredComment nvarchar(255), Signatory nvarchar(50), ShipsToVendor bit, ContainersToPickup nvarchar(4000), Shipment nvarchar(500), [Address] nvarchar(255), PRIMARY KEY(RouteID, StopID))
    	DECLARE @OrdersTable TABLE(RouteID int, StopID smallint, OrderNumber nvarchar(20), OrderType tinyint, Contact nvarchar(50), Phone nvarchar(50), PONumber nvarchar(20), OrderQtyShip int, OrderWeight real, OrderShipComment nvarchar(1000), OrderComment nvarchar(255), COD money, oKey int, Sequence int, CultureInfo varchar(20), CustomerRef nvarchar(50), PRIMARY KEY(RouteID, StopID, OrderNumber))
    	DECLARE @ItemsTable TABLE(RouteID int, StopID smallint, OrderNumber nvarchar(20), LineItem smallint, SubLineItem smallint, FormattedLineItem nvarchar(20), ItemSequence int, PartGUID uniqueidentifier, Part varchar(255), PartNo varchar(35), PartNoSuffix varchar(4), Description nvarchar(255), Size nvarchar(50), ItemQty smallint, ItemQtyPrev smallint, ItemQtyShip smallint, ItemQtyLoad smallint, odKey int, osdKey int, oKey int, ShipsSeparately bit, ItemQtyDeliver smallint, Width real, Height real, Thickness real, SerialNumber nvarchar(16), ItemWeight real, Options nvarchar(4000), ItemLocationID varchar(6), ItemComment nvarchar(255), ItemType tinyint, Location nvarchar(4000), PRIMARY KEY(RouteID, StopID, OrderNumber, LineItem, SubLineItem, PartGUID, Size))
    	DECLARE @UnitsTable TABLE(RouteID int, StopID smallint, OrderNumber nvarchar(20), LineItem smallint, SubLineItem smallint, PartGUID uniqueidentifier, PartNo varchar(35), PartNoSuffix varchar(4), Description nvarchar(255), Size nvarchar(50), ContainerID varchar(16), ContainerKey int, UniqueID nvarchar(50), BarCode nvarchar(50), UnitQtyShip smallint, ShipsSeparately bit, SchedID int, UnitID smallint, MasterKey int, ParentKey int, odKey int, osdKey int, UnitWeight real, UnitQtyDeliver smallint, UnitComment nvarchar(255), HasImages bit, PRIMARY KEY(RouteID, StopID, OrderNumber, LineItem, SubLineItem, PartGUID, Size, ContainerID, UniqueID))
    
    	DECLARE @MasterTable TABLE(RouteID int, RouteName nvarchar(50), Employee nvarchar(50), ShippingRoute nvarchar(20), TargetShipDate smalldatetime, ShipDate smalldatetime, RouteWeight real, RouteLocationID varchar(6), TruckID varchar(50),
    					StopID smallint, CustomerName nvarchar(255), ExpectedArrivalDate smalldatetime, DeliveredDate smalldatetime, DeliveredComment nvarchar(255), Signatory nvarchar(50), ShipsToVendor bit, ContainersToPickup nvarchar(4000), Shipment nvarchar(500), [Address] nvarchar(255),
    					OrderNumber nvarchar(20), OrderType tinyint, Contact nvarchar(50), Phone nvarchar(50), PONumber nvarchar(20), OrderQtyShip int, OrderWeight real, OrderShipComment nvarchar(1000), OrderComment nvarchar(255), COD money, oKey int, Sequence int, CultureInfo varchar(20), CustomerRef nvarchar(50),
    					LineItem smallint, SubLineItem smallint, FormattedLineItem nvarchar(20), ItemSequence int, PartGUID uniqueidentifier, Part varchar(255), PartNo varchar(35), PartNoSuffix varchar(4), Description nvarchar(255), Size nvarchar(50), ItemQty smallint, ItemQtyPrev smallint, ItemQtyShip smallint, ItemQtyLoad smallint, odKey int, osdKey int, ShipsSeparately bit, ItemQtyDeliver smallint, Width real, Height real, Thickness real, SerialNumber nvarchar(16), ItemWeight real, Options nvarchar(4000), ItemLocationID varchar(6), ItemComment nvarchar(255), ItemType tinyint, Location nvarchar(4000),
    					ContainerID varchar(16), ContainerKey int, UniqueID nvarchar(50), BarCode nvarchar(50), UnitQtyShip smallint, UnitQtyPrevShip smallint, SchedID int, UnitID smallint, MasterKey int, ParentKey int, UnitWeight real, UnitQtyDeliver smallint, UnitComment nvarchar(255), HasImages bit)
    
    	INSERT @MasterTable
    		EXEC usp_shippingMasterData @RouteID, default, default, default, default, @ReturnManufacturedDimensions
    
    	INSERT @RoutesTable
    		SELECT DISTINCT RouteID, RouteName, Employee, ShippingRoute, TargetShipDate, ShipDate, RouteWeight, RouteLocationID, TruckID
    		FROM @MasterTable
    
    	INSERT @StopsTable
    		SELECT DISTINCT RouteID, StopID, CustomerName, ExpectedArrivalDate, DeliveredDate, DeliveredComment, Signatory, ShipsToVendor, ContainersToPickup, Shipment, [Address]
    		FROM @MasterTable
    
    	INSERT @OrdersTable
    		SELECT DISTINCT RouteID, StopID, OrderNumber, OrderType, Contact, Phone, PONumber, OrderQtyShip, OrderWeight, OrderShipComment, OrderComment, COD, oKey, Sequence, CultureInfo, CustomerRef
    		FROM @MasterTable
    
    	INSERT @ItemsTable
    		SELECT DISTINCT RouteID, StopID, OrderNumber, LineItem, SubLineItem, FormattedLineItem, ItemSequence, PartGUID, Part, PartNo, PartNoSuffix, Description, Size, ItemQty, ItemQtyPrev, ItemQtyShip, ItemQtyLoad, odKey, osdKey, oKey, ShipsSeparately, ItemQtyDeliver, Width, Height, Thickness, SerialNumber, ItemWeight, Options, ItemLocationID, ItemComment, ItemType, Location
    		FROM @MasterTable
    
    	INSERT @UnitsTable
    		SELECT DISTINCT RouteID, StopID, OrderNumber, LineItem, SubLineItem, PartGUID, PartNo, PartNoSuffix, Description, Size, ContainerID, ContainerKey, UniqueID, BarCode, UnitQtyShip, ShipsSeparately, SchedID, UnitID, MasterKey, ParentKey, odKey, osdKey, UnitWeight, UnitQtyDeliver, UnitComment, HasImages
    		FROM @MasterTable
    		WHERE UniqueID IS NOT NULL AND ContainerID IS NOT NULL
    
    	--this ensures that we only display barcodes for ordered, non-manufactured items
    	DELETE ut
    	FROM @ItemsTable it
    		JOIN @UnitsTable ut ON it.RouteID=ut.RouteID AND it.StopID=ut.StopID AND it.OrderNumber=ut.OrderNumber AND it.LineItem=ut.LineItem AND it.SubLineItem=ut.SubLineItem AND it.PartGUID=ut.PartGUID AND it.Size=ut.Size
    	WHERE it.ShipsSeparately=1 OR it.ItemType NOT IN (2,3,4)
    
    	--this makes sure to remove manufactured items & ship separate items from the Pick Ticket
    	DELETE @ItemsTable
    	WHERE @PickTicket=1 AND (ShipsSeparately=1 OR ItemType NOT IN (2,3,4))
    
    	SELECT DISTINCT rt.RouteName, rt.TargetShipDate, ISNULL(rt.RouteWeight,'') AS Peso, --<< ALDF 09/07/18: RouteWeight requested by TECESA
    		st.StopID, st.CustomerName, st.ShipsToVendor, st.Shipment,
    		ot.OrderNumber, ot.OrderType, ot.Sequence, ot.OrderShipComment,
    		it.FormattedLineItem, it.LineItem, it.SubLineItem, it.ItemSequence, it.Part, it.Size, it.ItemQty, it.ItemQtyPrev, it.ItemQtyShip, it.ItemQtyLoad, it.ItemComment, it.Options, it.ShipsSeparately, it.Location,
    		ISNULL(ut.UniqueID,'') AS Barcode, 
    		ISNULL(PC.Name,'') as ProjectName , ISNULL(PC.SiteID,'') as ProjectID, --<< EF 11/10/17: Project Name and ID requested by TECESA
    		PO.AckDate --<< EF 03/26/18: TECESA requested to have the Acknowledge Date to be added as an additional field.
    	FROM @RoutesTable rt
    		JOIN @StopsTable st ON rt.RouteID=st.RouteID
    		JOIN @OrdersTable ot ON st.RouteID=ot.RouteID AND st.StopID=ot.StopID
    		JOIN @ItemsTable it ON ot.RouteID=it.RouteID AND ot.StopID=it.StopID AND ot.OrderNumber=it.OrderNumber
    		LEFT JOIN @UnitsTable ut ON it.RouteID=ut.RouteID AND it.StopID=ut.StopID AND it.OrderNumber=ut.OrderNumber AND it.LineItem=ut.LineItem AND it.SubLineItem=ut.SubLineItem AND it.PartGUID=ut.PartGUID AND it.Size=ut.Size
    		LEFT JOIN Orders PO on PO.Okey = ot.Okey
    		LEFT JOIN Customers PC on PC.CustomerGUID = PO.ProjectGUID
    	ORDER BY st.StopID DESC, ot.Sequence DESC, it.ItemSequence DESC, ot.OrderNumber, it.LineItem, it.SubLineItem, it.ShipsSeparately, it.Part

    Al ejecutarlo en VS.NET no hay problema muestra el Peso.

    Al ver la vista previa me marca error en el apartado donde estoy agregando el Peso.

    miércoles, 26 de septiembre de 2018 18:56

Respuestas

  • Hola analaura151:

     Es posible, que no sepa preveer el tipo de dato de rt.RouteWeight, dado que en la variable de tabla lo has definido como real, pero el isnull(rt.RouteWeigth,'').

    DECLARE @real REAL;
    SELECT SQL_VARIANT_PROPERTY(@real, 'BaseType') AS 'Base Type',
           SQL_VARIANT_PROPERTY(@real, 'Precision') AS 'Precision',
           SQL_VARIANT_PROPERTY(@real, 'Scale') AS 'Scale';
    SET @real = 0;
    SELECT SQL_VARIANT_PROPERTY(@real, 'BaseType') AS 'Base Type',
           SQL_VARIANT_PROPERTY(@real, 'Precision') AS 'Precision',
           SQL_VARIANT_PROPERTY(@real, 'Scale') AS 'Scale'; 

    Ya comentas.

    Un saludo

    miércoles, 26 de septiembre de 2018 19:25