Hi guys, I need a little help I have one chart with Orders information, I created this chart using editor and I convert to SQL Command because I Need to add FieldFilter..
The command generated:
SELECT DateFromParts(year("source"."DateCreated"), month("source"."DateCreated"), 1) AS "DateCreated", "source"."Installer" AS "Installer", count(*) AS "count"
FROM (SELECT "dbo"."Orders"."OrderID" AS "OrderID", "dbo"."Orders"."UserID" AS "UserID", "dbo"."Orders"."DateCreated" AS "DateCreated", "dbo"."Orders"."UserType" AS "UserType", "dbo"."Orders"."OrderStatus" AS "OrderStatus", "dbo"."Orders"."GUID" AS "GUID", "dbo"."Orders"."FirstName" AS "FirstName", "dbo"."Orders"."LastName" AS "LastName", "dbo"."Orders"."Company" AS "Company", "dbo"."Orders"."Email" AS "Email", "dbo"."Orders"."Address1" AS "Address1", "dbo"."Orders"."Address2" AS "Address2", "dbo"."Orders"."Address3" AS "Address3", "dbo"."Orders"."City" AS "City", "dbo"."Orders"."State" AS "State", "dbo"."Orders"."Country" AS "Country", "dbo"."Orders"."ZipCode" AS "ZipCode", "dbo"."Orders"."Phone1" AS "Phone1", "dbo"."Orders"."Phone2" AS "Phone2", "dbo"."Orders"."BillingFirstName" AS "BillingFirstName", "dbo"."Orders"."BillingLastName" AS "BillingLastName", "dbo"."Orders"."BillingCompany" AS "BillingCompany", "dbo"."Orders"."BillingEmail" AS "BillingEmail", "dbo"."Orders"."BillingAddress1" AS "BillingAddress1", "dbo"."Orders"."BillingAddress2" AS "BillingAddress2", "dbo"."Orders"."BillingAddress3" AS "BillingAddress3", "dbo"."Orders"."BillingCity" AS "BillingCity", "dbo"."Orders"."BillingState" AS "BillingState", "dbo"."Orders"."BillingCountry" AS "BillingCountry", "dbo"."Orders"."BillingZipCode" AS "BillingZipCode", "dbo"."Orders"."BillingPhone1" AS "BillingPhone1", "dbo"."Orders"."BillingPhone2" AS "BillingPhone2", "dbo"."Orders"."CreditCard" AS "CreditCard", "dbo"."Orders"."CreditCardNumber" AS "CreditCardNumber", "dbo"."Orders"."ExpirationDate" AS "ExpirationDate", "dbo"."Orders"."CVV" AS "CVV", "dbo"."Orders"."IP" AS "IP", "dbo"."Orders"."ShippingMethod" AS "ShippingMethod", "dbo"."Orders"."ShippingCost" AS "ShippingCost", "dbo"."Orders"."Tax" AS "Tax", "dbo"."Orders"."xmlConfig" AS "xmlConfig", "dbo"."Orders"."OrderNotes" AS "OrderNotes", "dbo"."Orders"."OrderSource" AS "OrderSource", "dbo"."Orders"."InstallerID" AS "InstallerID", "dbo"."Orders"."old_id" AS "old_id", "dbo"."Orders"."new_id" AS "new_id", "dbo"."Orders"."EAS" AS "EAS", "dbo"."Orders"."Total" AS "Total", "dbo"."Orders"."NumberEdit" AS "NumberEdit", "dbo"."Orders"."QuickBooksStatus" AS "QuickBooksStatus", "dbo"."Orders"."CancelationReasonID" AS "CancelationReasonID", "dbo"."Orders"."OrderSubTotal" AS "OrderSubTotal", "dbo"."Orders"."QuickbooksClassId" AS "QuickbooksClassId", "dbo"."Orders"."ShippingDiscount" AS "ShippingDiscount", "dbo"."Orders"."ShippingToInstaller" AS "ShippingToInstaller", "dbo"."Orders"."WhiteOut" AS "WhiteOut", "dbo"."Orders"."ShoppingCartHeaderId" AS "ShoppingCartHeaderId", "dbo"."Orders"."CouponAmount" AS "CouponAmount", "dbo"."Orders"."Salesperson" AS "Salesperson", "dbo"."Orders"."NotificationInstallerReviewType" AS "NotificationInstallerReviewType", "dbo"."Orders"."AmazonOrderId" AS "AmazonOrderId", "dbo"."Orders"."QbUpdateRequired" AS "QbUpdateRequired", "dbo"."Orders"."NoTaxShipping" AS "NoTaxShipping", "dbo"."Orders"."CSRDiscount" AS "CSRDiscount", "dbo"."Orders"."RouteProtectionValue" AS "RouteProtectionValue", "dbo"."Orders"."RouteProtectionData" AS "RouteProtectionData", "dbo"."Orders"."CountryIDBillingPhone1" AS "CountryIDBillingPhone1", "dbo"."Orders"."CountryIDBillingPhone2" AS "CountryIDBillingPhone2", "dbo"."Orders"."CountryIDShippingPhone1" AS "CountryIDShippingPhone1", "dbo"."Orders"."CountryIDShippingPhone2" AS "CountryIDShippingPhone2", "dbo"."Orders"."appointmentID" AS "appointmentID", CASE WHEN "dbo"."Orders"."InstallerID" = 9971970 THEN "dbo"."Orders"."Company" WHEN "dbo"."Orders"."InstallerID" = 200000 THEN "dbo"."Orders"."Company" WHEN "dbo"."Orders"."InstallerID" = 100000 THEN '1010 Richmond' WHEN "dbo"."Orders"."InstallerID" = 44359 THEN '1010 Mississauga' WHEN "dbo"."Orders"."InstallerID" = 42806 THEN '1010 Coquitlam' WHEN "dbo"."Orders"."InstallerID" = 42805 THEN '1010 In-Store Marine Dr.' WHEN "dbo"."Orders"."InstallerID" = 9986746 THEN '1010 Mobile 1' WHEN "dbo"."Orders"."InstallerID" = 9987111 THEN '1010 Mobile 2' WHEN "dbo"."Orders"."InstallerID" = 9987112 THEN '1010 Mobile 3' WHEN "dbo"."Orders"."InstallerID" = 9989717 THEN '1010 Mobile 4' ELSE 'Other Orders' END AS "Installer", "Users"."UserID" AS "Users__UserID", "Users"."GUID" AS "Users__GUID", "Users"."DateCreated" AS "Users__DateCreated", "Users"."FirstName" AS "Users__FirstName", "Users"."LastName" AS "Users__LastName", "Users"."Password" AS "Users__Password", "Users"."Company" AS "Users__Company", "Users"."Phone1" AS "Users__Phone1", "Users"."Phone2" AS "Users__Phone2", "Users"."Address1" AS "Users__Address1", "Users"."Email" AS "Users__Email", "Users"."Address2" AS "Users__Address2", "Users"."State" AS "Users__State", "Users"."City" AS "Users__City", "Users"."ZipCode" AS "Users__ZipCode", "Users"."Country" AS "Users__Country", "Users"."BillingFirstName" AS "Users__BillingFirstName", "Users"."BillingLastName" AS "Users__BillingLastName", "Users"."BillingCompany" AS "Users__BillingCompany", "Users"."BillingEmail" AS "Users__BillingEmail", "Users"."BillingAddress1" AS "Users__BillingAddress1", "Users"."BillingAddress2" AS "Users__BillingAddress2", "Users"."BillingCity" AS "Users__BillingCity", "Users"."BillingState" AS "Users__BillingState", "Users"."BillingCountry" AS "Users__BillingCountry", "Users"."BillingZipCode" AS "Users__BillingZipCode", "Users"."BillingPhone1" AS "Users__BillingPhone1", "Users"."BillingPhone2" AS "Users__BillingPhone2", "Users"."UserType" AS "Users__UserType", "Users"."AdminLevel" AS "Users__AdminLevel", "Users"."isSubscribe" AS "Users__isSubscribe", "Users"."isActive" AS "Users__isActive", "Users"."SaltKey" AS "Users__SaltKey", "Users"."xmlconfig" AS "Users__xmlconfig", "Users"."UserTypeID" AS "Users__UserTypeID", "Users"."LoginGUID" AS "Users__LoginGUID", "Users"."LoginDate" AS "Users__LoginDate", "Users"."UserIP" AS "Users__UserIP", "Users"."UserAgent" AS "Users__UserAgent", "Users"."PasswordResetExpiration" AS "Users__PasswordResetExpiration", "Users"."PasswordResetToken" AS "Users__PasswordResetToken", "Users"."IsGuest" AS "Users__IsGuest", "Users"."GuestSession" AS "Users__GuestSession", "Users"."CountryDialCodePhone1" AS "Users__CountryDialCodePhone1", "Users"."CountryDialCodePhone2" AS "Users__CountryDialCodePhone2", "Users"."CountryDialCodeBillingPhone1" AS "Users__CountryDialCodeBillingPhone1", "Users"."CountryDialCodeBillingPhone2" AS "Users__CountryDialCodeBillingPhone2", "Users"."CountryIDBillingPhone1" AS "Users__CountryIDBillingPhone1", "Users"."CountryIDBillingPhone2" AS "Users__CountryIDBillingPhone2", "Users"."CountryIDShippingPhone1" AS "Users__CountryIDShippingPhone1", "Users"."CountryIDShippingPhone2" AS "Users__CountryIDShippingPhone2" FROM "dbo"."Orders" INNER JOIN "dbo"."Users" "Users" ON "dbo"."Orders"."InstallerID" = "Users"."UserID") "source"
WHERE ("source"."DateCreated" >= DateFromParts(year(dateadd(month, -12, getdate())), month(dateadd(month, -12, getdate())), 1)
AND "source"."DateCreated" < DateFromParts(year(dateadd(month, 1, getdate())), month(dateadd(month, 1, getdate())), 1) [[ AND {{OrderSource}} ]] AND ("source"."OrderStatus" = 'Installed'
OR "source"."OrderStatus" = 'New' OR "source"."OrderStatus" = 'PaymentVerification' OR "source"."OrderStatus" = 'Paid' OR "source"."OrderStatus" = 'Pending' OR "source"."OrderStatus" = 'PickedUp' OR "source"."OrderStatus" = 'Processing' OR "source"."OrderStatus" = 'ReadyAtStore' OR "source"."OrderStatus" = 'ReadyForMobile' OR "source"."OrderStatus" = 'Shipped'))
GROUP BY year("source"."DateCreated"), month("source"."DateCreated"), "source"."Installer"
ORDER BY year("source"."DateCreated") ASC, month("source"."DateCreated") ASC, "source"."Installer" ASC
I Receive "Incorrect syntax near 'dbo'." if I add: [[ AND {{OrderSource}} ]]
If I move {{OrderSource}} after Where example:
WHERE [[ {{OrderSource}} ]] ("source"."DateCreated" >= DateFromParts(year(dateadd(month, -12, getdate())), month(dateadd(month, -12, getdate())), 1)
AND "source"."DateCreated" < DateFromParts(year(dateadd(month, 1, getdate())), month(dateadd(month, 1, getdate())), 1) AND ("source"."OrderStatus" = 'Installed'
OR "source"."OrderStatus" = 'New' OR "source"."OrderStatus" = 'PaymentVerification' OR "source"."OrderStatus" = 'Paid' OR "source"."OrderStatus" = 'Pending' OR "source"."OrderStatus" = 'PickedUp' OR "source"."OrderStatus" = 'Processing' OR "source"."OrderStatus" = 'ReadyAtStore' OR "source"."OrderStatus" = 'ReadyForMobile' OR "source"."OrderStatus" = 'Shipped'))
GROUP BY year("source"."DateCreated"), month("source"."DateCreated"), "source"."Installer"
ORDER BY year("source"."DateCreated") ASC, month("source"."DateCreated") ASC, "source"."Installer" ASC
I receive: Incorrect syntax near 'source'. If I Where {{OrderSource}} AND ("source"."DateCreated" >..... add The multi-part identifier "dbo.Orders.OrderSource" could not be bound.
on the first post