##How to add custom SQL reports to uStore

Refer to the following knowledgebase article for information on how to add custom SQL reports to uStore

##Overview

This report covers all the basic info typically required by shipping departments. It is a customization of standard report with the addition of:

• Customer Name - uStore user

• Billing Address information - can be different than the uStore user’s default as it is whatever was inputted during check out.

• Product Information – Name, Id, qty, and cost

• Order Information – Id, qty, cost, submitted date

• Shipping Address – whatever was inputted during check out

• Delivery Information – Method, service, ship date, and tracking number

The store procedure below (Report_ListOrderItems_Tracking) has been tested against the uStore Database and is ready to have the @StoreID, @StartDate, and @EndDate parameters added to the WHERE statement in order to retrieve the necessary records.

Installation steps:

1. Open MS SQL Server Mngt Studio

2. Connect to SQL

3. Click on the + next to Databases

4. Click on the + next to uStore

5. Click on the + next to Programmability

6. Click on the + next to Store Procedures

7. Right click on anything in this section

8. A pop-up menu will appear, click Stored Procedure…

9. The main SQL window is now ready to accept the report code

10. Click into the main SQL window

11. Press ctrl+a to select all the text in the main window and then click backspace or the del key

12. Copy the entire contents of the code at the end of the write up and paste it into the now blank SQL window

13. 8 rows from the top you should see the word ALTER, change this to CREATE

14. Remove any fields you do not wish to have in your report then click Execute in the SQL toolbar

1. Log into the admin portal and click presets at the top of the page

2. Click System Setup

3. Click Report

4. Click Create New

5. Name: Name the report

6. Report Type: Stored-Procedure Query

7. ParentReportID: No Selection

8. DisplayOrder: This can be whatever you prefer, lower numbers will show up at the top of the list in the Reports section

9. Status: Active

10. Click Save

11. Find your newly created report at the end of the System Setup- Report list

12. Make note of your ReportID and click edit

13. Click edit localized text

14. Click new

15. ReportID: input your reportID if needed

16. Culture: select whatever language you are using in your store from the drop down

17. DisplayName: input your report name

18. Description: input a description of your report if desired (optional)

19. ReportComand: Report_ListOrderItems_Tracking

20. Click save

21. From Presets Click System Setup

22. Click Report Parameters

23. Click Add New

24. ReportID: Input your reportID

25. Name: StoreID

26. Description & Default Value can be left blank

27. IsUserEditable: True

28. DisplayOrder: 1

29. Status: Active

30. IsViewable: True

31. ControlName: ParamListBox

32. ValueLookUpQuery: select 'Value' = S.StoreID, 'Name'=SC.Name, 'Selected' = 0 from Store S join Store_Culture SC on (S.StoreID=SC.StoreID and SC.CultureID=dbo.fn_StoreSetupCulture(S.StoreID)) JOIN fn_UserStores(@ActiveUserId, 12) US ON S.StoreID = us.StoreId Where S.StatusID <> 2

33. Click save

34. Find your newly created report parameter at the end of the list

35. Make a note of the ReportParameterID number and click edit

36. Click edit localized text

37. Click Add new

38. ReportParameterID: Input your ReportParameterID number

39. Culture: select whatever language you are using in your store from the drop down

40. DisplayName: Store Name

41. Click save

42. Click back to list

43. Click Add New

44. ReportID: Input your reportID

45. Name: StartDate

46. Description & Default Value can be left blank

47. IsUserEditable: True

48. DisplayOrder: 1

49. Status: Active

50. IsViewable: True

51. ControlName: ParamTextBox

52. ValueLookUpQuery:

53. Click save

54. Find your newly created report parameter at the end of the list

55. Make a note of the ReportParameterID number and click edit

56. Click edit localized text

57. Click Add new

58. ReportParameterID: Input your ReportParameterID number

59. Culture: select whatever language you are using in your store from the drop down

60. DisplayName: Start Date

61. Click save

62. Click back to list

63. Click Add New

64. ReportID: Input your reportID

65. Name: EndDate

66. Description & Default Value can be left blank

67. IsUserEditable: True

68. DisplayOrder: 1

69. Status: Active

70. IsViewable: True

71. ControlName: ParamTextBox

72. ValueLookUpQuery:

73. Click save

74. Find your newly created report parameter at the end of the list

75. Make a note of the ReportParameterID number and click edit

76. Click edit localized text

77. Click Add new

78. ReportParameterID: Input your ReportParameterID number

79. Culture: select whatever language you are using in your store from the drop down

80. DisplayName: End Date

81. Click save

82. Click back to list

1. From the Admin portal click Reports at the top of the window

2. Pick your new report from the drop-down list

3. From the store name drop down pick the store you want to run the report on. This can also be left blank.

4. Input start and end dates for your report. These can be left blank.

5. Click generate

6. Orders are selected based on order submitted date

7. The report can be downloaded by clicking download in the upper righthand corner of the window

USE [uStore] 

GO 

/****** Object:  StoredProcedure [dbo].[Report_ListOrderItems_Tracking]    Script Date: 3/14/2022 2:13:08 PM ******/ 

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

 

ALTER PROCEDURE [dbo].[Report_ListOrderItems_Tracking] 

( 

@MallID int, 

@ActiveUserID int, 

@StoreID int = NULL, 

@StartDate datetime = NULL, 

@EndDate datetime = NULL 

) 

AS 

DECLARE @CultureID int 

SET @CultureID = dbo.fn_StoreSetupCulture(@StoreID) 

SELECT sc.Name as StoreName, 

u.FirstName + ' ' + U.LastName AS 'CustomerName', 

       O.EncryptedOrderId AS OrderID, 

       O.DateOrdersubmitted, 

O.Bill_Name, 

O.Bill_Company, 

O.Bill_Add1,  

O.Bill_Add2, 

O.Bill_AddressReference, 

O.Bill_City, 

Bill_StateName.Name AS 'Bill_State', 

O.Bill_Zip, 

Bill_CountryName.Name AS 'Bill_Country', 

O.Bill_Phone, 

O.Bill_Fax, 

O.Bill_Email, 

Pc.Name AS 'ProductName', 

       Op.OrderProductID AS 'Item ID', 

Pc.ProductID, 

OP.NumRecipients, 

ISNULL(CAST(DI.QuantityPerRecipient AS nvarchar(50)) 

+ ' ' + dbo.fn_GetProductUnitName(Di.QuantityPerRecipient, OP.ProductUnitID, @CultureID), 

'-') AS 'Quantity Per Recipient', 

OP.Cost AS 'Item Cost', 

OP.ProductPriceSubtotal, 

OP.PricePerRecipient, 

OP.TaxAmount AS 'Item Tax Amount', 

Op.TotalPrice AS 'Total Item Price', 

O.OrderAmount AS 'Subtotal Order Price', 

O.BillAmount AS 'Total Order Price', 

CASE 

WHEN DI.QuantityPerRecipient IS NULL 

THEN CAST(OP.TotalQuantity AS nvarchar(50)) + ' ' + dbo.fn_GetProductUnitName(OP.TotalQuantity, OP.ProductUnitID, @CultureID) 

ELSE CAST(OP.NumRecipients * DI.QuantityPerRecipient AS nvarchar(50)) + ' ' + dbo.fn_GetProductUnitName(Op.NumRecipients * DI.QuantityPerRecipient, OP.ProductUnitID, @CultureID) 

END AS 'Total Quantity', 

DM.Name AS 'Delivery Method', 

DPS.Name AS 'Delivery Service', 

D.ShipDate, 

       D.TrackingNumber AS 'Tracking Number', 

OP.ApprovalRejectNotes, 

DT.Ship_Name, 

DT.Ship_Company, 

DT.Ship_Add1, 

DT.Ship_Add2, 

DT.Ship_AddressReference, 

DT.Ship_City, 

Ship_StateName.Name AS 'Ship_State', 

DT.Ship_Zip, 

Ship_CountryName.Name AS 'Ship_Country', 

DT.Ship_Phone, 

DT.Ship_Fax 

 

FROM OrderProduct op 

JOIN Orders o ON O.OrderID = OP.OrderID 

Join Store_Culture sc on o.storeid = sc.storeid 

JOIN Product_Culture pc ON OP.ProductID = PC.ProductID AND PC.CultureID = 1 

JOIN DOC ON Doc.ProductID = OP.ProductID 

JOIN DeliveryItem di ON DI.OrderProductID = OP.OrderProductID 

join delivery d on d.deliveryid = di.deliveryid 

JOIN DeliveryTentative dt ON DT.DeliveryTentativeID = DI.DeliveryTentativeID 

JOIN DeliveryMethod dm ON DM.DeliveryMethodId = OP.DeliveryMethodId 

JOIN DeliveryProviderService DPS ON DPS.DeliveryProviderServiceID = Dt.DeliveryServiceId 

JOIN Users u ON U.UserID = O.UserID 

JOIN fn_UserStores(@ActiveUserId, 12) US ON O.StoreId = US.StoreID 

LEFT OUTER JOIN Province_Culture AS Bill_StateName ON Bill_StateName.ProvinceId =     O.Bill_State AND Bill_StateName.CultureId = @CultureID 

LEFT OUTER JOIN Province_Culture AS Ship_StateName ON Ship_StateName.ProvinceId = DT.Ship_State AND Ship_StateName.CultureId = @CultureID 

LEFT OUTER JOIN Country_Culture AS Bill_CountryName ON Bill_CountryName.CountryId = O.Bill_Country AND Bill_CountryName.CultureID = @CultureID 

LEFT OUTER JOIN Country_Culture AS Ship_CountryName ON Ship_CountryName.CountryId = DT.Ship_Country AND Ship_CountryName.CultureID = @CultureID 

 

WHERE	O.StatusID = 1 

AND o.IsCart = 0 

AND O.IsSaveForLater = 0 

AND OP.IsDraft = 0 

AND OP.StatusID = 1 

AND OP.ParentOrderProductID IS NULL 

AND (O.StoreID=@StoreID OR (ISNULL(@StoreID, -1) <= 0)) 

AND (@StartDate <= O.DisplayOrderDate OR @StartDate IS NULL OR @StartDate ='') 

AND (DATEADD(day, 1, @EndDate) >= O.DateOrderSubmitted OR @EndDate IS NULL OR @EndDate = '')