Reporting services

I created a report with the following query and parameter in reporting services 2008.
Query:

SELECT  
A.State,
A.PostalCode as [Zip code],
Schweser_Warehouse.dbo.initcap(A.City) as City,
CASE WHEN  
OD.TaxablePrice IS NULL OR  OD.TaxablePrice = 0
THEN  SUM(OD.CustomerPrice)
ELSE SUM(OD.TAXABLEPRICE)
END
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
WHERE OD.TaxablePrice>0
        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.


Thanks.















sas77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pssandhuCommented:
If you run the query by itself in SSMS query window, do still get the same results? If not then we know some setting is not correct in SSRS.
Try that to see if this is the case.
P.
0
sas77Author Commented:
Yes i run the query in ssms same problem: see the following

SELECT  
A.State,
A.PostalCode as [Zip code],
Schweser_Warehouse.dbo.initcap(A.City) as City,
CASE WHEN  
OD.TaxablePrice IS NULL OR  OD.TaxablePrice = 0
THEN  SUM(OD.CustomerPrice)
ELSE SUM(OD.TAXABLEPRICE)
END
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
WHERE OD.TaxablePrice>0
        AND O.TaxRate <>0
        and A.Country IN('US','USA','UNITED STATES','UNITED STATES  ')
       AND OD.ShippingDate BETWEEN '04/01/2009' AND  '04/01/2009'
       GROUP BY A.State,A.PostalCode,A.City, O.TAXRATE,OD.TaxablePrice

Results:

State      Zip code      City                  Taxable sales      Percent            Tax Charged      Freight Tax
CA      92648            Huntington Beach      123.56            0.0775            9.5759            2.068475



SELECT  
A.State,
A.PostalCode as [Zip code],
Schweser_Warehouse.dbo.initcap(A.City) as City,
CASE WHEN  
OD.TaxablePrice IS NULL OR  OD.TaxablePrice = 0
THEN  SUM(OD.CustomerPrice)
ELSE SUM(OD.TAXABLEPRICE)
END
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
WHERE OD.TaxablePrice>0
        AND O.TaxRate <>0
        and A.Country IN('US','USA','UNITED STATES','UNITED STATES  ')
       AND OD.ShippingDate BETWEEN '04/02/2009' AND  '04/02/2009'
       GROUP BY A.State,A.PostalCode,A.City, O.TAXRATE,OD.TaxablePrice


Results:

State      Zip code      City            Taxable sales      Percent            Tax Charged      Freight Tax
CA      94019            Half Moon Bay      79.00            0.0925            7.3075            2.0794
CA      94019            Half Moon Bay      100.00            0.0925            9.25            2.0794
NY      10502            Ardsley            599.00            0.07375            44.17625      0


SELECT  
A.State,
A.PostalCode as [Zip code],
Schweser_Warehouse.dbo.initcap(A.City) as City,
CASE WHEN  
OD.TaxablePrice IS NULL OR  OD.TaxablePrice = 0
THEN  SUM(OD.CustomerPrice)
ELSE SUM(OD.TAXABLEPRICE)
END
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
WHERE OD.TaxablePrice>0
        AND O.TaxRate <>0
        and A.Country IN('US','USA','UNITED STATES','UNITED STATES  ')
       AND OD.ShippingDate BETWEEN '04/01/2009' AND  '04/02/2009'
       GROUP BY A.State,A.PostalCode,A.City, O.TAXRATE,OD.TaxablePrice





State      Zip code      City      Taxable sales      Percent      Tax Charged      Freight Tax
AZ      85085      Phoenix      99.00      0.056      5.544      0.67704
AZ      85086      Phoenix      22.97      0.056      1.28632      0.71232
AZ      85086      Phoenix      97.86      0.056      5.48016      0.71232
AZ      85260      Scottsdale      125.10      0.056      7.0056      0.67704
AZ      85711      Tucson      392.40      0.056      21.9744      0.97328
CA      90405      Santa Monica      297.00      0.0825      24.5025      3.269475
CA      92108      San Diego      123.5837      0.0775      9.57773675      0
CA      92109      San Diego      74.72      0.0875      6.538      1.187375
CA      92109      San Diego      179.73      0.0875      15.726375      1.187375
CA      92614      Irvine      97.88      0.0875      8.5645      1.101625
CA      92648      Huntington Beach      123.56      0.0775      9.5759      2.068475
CA      94019      Half Moon Bay      79.00      0.0925      7.3075      2.0794
CA      94019      Half Moon Bay      100.00      0.0925      9.25      2.0794
CA      94025      Menlo Park      123.56      0.0825      10.1937      1.038675
CA      94104      San Francisco      44.92      0.085      3.8182      1.38975
CA      94110      San Francisco      149.00      0.095      14.155      1.19605
CA      94121      San Francisco      71.7979      0.085      6.1028215      1.07015
CA      94301      Palo Alto      322.29      0.0825      26.588925      1.25895
CA      94618      Oakland      70.00      0.0875      6.125      1.0325
CA      94920      Tiburon      142.54      0.0925      13.18495      1.164575
CA      94925      Corte Madera      359.46      0.0775      27.85815      4.5198
CA      95814      Sacramento      123.56      0.0775      9.5759      0.975725
CA      96007      Anderson      132.68      0.0825      10.9461      1.723425
CO      80207      Denver      999.00      0.0772      77.1228      1.08852
CT      06032      Farmington      97.88      0.06      5.8728      0.7236
0
pssandhuCommented:
Is there a timestamp on the datefield. Try running your queries with dates like this:
For April 1st:     AND OD.ShippingDate BETWEEN '2009-04-01 00:00:00.' and '2009-04-01 23:59:59'
For April 2nd:     AND OD.ShippingDate BETWEEN '2009-04-02 00:00:00.' and '2009-04-02 23:59:59'  
and then all together:
AND OD.ShippingDate BETWEEN '2009-04-01 00:00:00.' and '2009-04-02 23:59:59'  
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

sas77Author Commented:
Conversion failed when converting character string to smalldatetime data type.

shippingdate is  smalldatetime and allows null
0
pssandhuCommented:
Okay no problem, change the dateformat like this:
For April 1st:     AND OD.ShippingDate BETWEEN '2009-04-01 00:00.' and '2009-04-01 23:59'
P.
0
HadushCommented:
Try to change the between function  OD.ShippingDate>=@StartDate AND  OD.ShippingDate<= @EndDate
 
0
sas77Author Commented:
Yes, now i am getting the results appropriately, how to fix this in ssrs or at parameter level.

please suggest.

thanks.
0
pssandhuCommented:
Change your StartDate and EndDate parameters in the where clause to this:
AND OD.ShippingDate BETWEEN CONVERT(VARCHAR(10), @STARTDATE, 120)+' 23:59' AND  CONVERT(VARCHAR(10), @ENDDATE, 120)+' 23:59'
P.
0
pssandhuCommented:
Sorry, made a typo. Correct to this:
AND OD.ShippingDate BETWEEN CONVERT(VARCHAR(10), @STARTDATE, 120)+' 00:00' AND  CONVERT(VARCHAR(10), @ENDDATE, 120)+' 23:59'
P.
0
sas77Author Commented:
When  i try with the above format i ma not getting any values if i select stardate as april 1 2009 and end date as april 1 2009, but i ma getting results if i select in range.

04/01/2009 - 04/01/2009 -------No results
04/02/2009 - 04/02/2009 ---------No results
04/01/2009 -04/02/2009 --------- Results coming

Thanks.
0
pssandhuCommented:
Did you made the correction I suggest above. Anyway, try it this way:
AND OD.ShippingDate BETWEEN  @STARTDATE  AND  CONVERT(VARCHAR(10), @ENDDATE, 120)+' 23:59'  
I took the convert off've @StartDate since it does not really needs it.
P.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris LuttrellSenior Database ArchitectCommented:
Your two date parameters in SSRS will by default just be the date portions with no time portions.  It will be more efficient to use
      AND OD.ShippingDate BETWEEN @STARTDATE AND DATEADD(mi,-1,DATEADD(d,1,@ENDDATE))
to get the end of the day on your end date instead of converting it to a string, the string will work but the database will have to implicitly convert it back to a date.
SELECT  
A.State,
A.PostalCode as [Zip code],
Schweser_Warehouse.dbo.initcap(A.City) as City,
CASE WHEN  
OD.TaxablePrice IS NULL OR  OD.TaxablePrice = 0
THEN  SUM(OD.CustomerPrice)
ELSE SUM(OD.TAXABLEPRICE) 
END
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
WHERE OD.TaxablePrice>0
        AND O.TaxRate <>0
        and A.Country IN('US','USA','UNITED STATES','UNITED STATES  ')
       AND OD.ShippingDate BETWEEN @STARTDATE AND DATEADD(mi,-1,DATEADD(d,1,@ENDDATE))
       GROUP BY A.State,A.PostalCode,A.City, O.TAXRATE,OD.TaxablePrice

Open in new window

0
sas77Author Commented:
Thanks all
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.