Solved

Reporting services

Posted on 2009-07-08
13
743 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

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 100 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 250 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 150 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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

688 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