?
Solved

Reporting services

Posted on 2009-07-08
13
Medium Priority
?
751 Views
Last Modified: 2012-05-11
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.















0
Comment
Question by:sas77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 24804891
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
 

Author Comment

by:sas77
ID: 24804969
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24805055
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:sas77
ID: 24805108
Conversion failed when converting character string to smalldatetime data type.

shippingdate is  smalldatetime and allows null
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24805146
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
 
LVL 8

Assisted Solution

by:Hadush
Hadush earned 400 total points
ID: 24805194
Try to change the between function  OD.ShippingDate>=@StartDate AND  OD.ShippingDate<= @EndDate
 
0
 

Author Comment

by:sas77
ID: 24805207
Yes, now i am getting the results appropriately, how to fix this in ssrs or at parameter level.

please suggest.

thanks.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24805266
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24805283
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
 

Author Comment

by:sas77
ID: 24805345
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
 
LVL 17

Accepted Solution

by:
pssandhu earned 1000 total points
ID: 24805428
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
 
LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 600 total points
ID: 24805487
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
 

Author Closing Comment

by:sas77
ID: 31601169
Thanks all
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question