Posted on 2009-07-08
I created a report with the following query and parameter in reporting services 2008.
A.PostalCode as [Zip code],
Schweser_Warehouse.dbo.initcap(A.City) as City,
OD.TaxablePrice IS NULL OR OD.TaxablePrice = 0
AS "Taxable sales",
O.TAXRATE AS "Percent",
SUM(OD.TAXABLEPRICE) * O.TAXRATE AS "Tax Charged",
SUM(O.FreightCharge) * O.TaxRate AS "Freight Tax"
FROM Production.dbo.ADDRESS A
INNER JOIN Production.dbo.ORDERADDRESS OA ON (A.ADDRESSID = OA.ADDRESSID)
INNER JOIN Production.dbo.ORDERS O ON OA.ORDERID=O.ORDERID
LEFT OUTER JOIN Production.dbo.OrderDetail OD ON O.OrderID= OD.OrderID
AND O.TaxRate <>0
and A.Country IN('US','USA','UNITED STATES','UNITED STATES ')
AND OD.ShippingDate BETWEEN @STARTDATE AND @ENDDATE
GROUP BY A.State,A.PostalCode,A.City, O.TAXRATE,OD.TaxablePrice
When running the report, if I select one day, (April 1st), only 2 states show up. When I select April 2nd, 3 states show up. When I select the range of April 1st 2nd, 40 states show up. Something about the date selection isnt working right, i dont know if ineed to put any settings in the date parameter, any suggestions or help will be appreciated.