Solved

Reporting services

Posted on 2009-07-08
13
730 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
13 Comments
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:sas77
Comment Utility
Conversion failed when converting character string to smalldatetime data type.

shippingdate is  smalldatetime and allows null
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
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
Comment Utility
Try to change the between function  OD.ShippingDate>=@StartDate AND  OD.ShippingDate<= @EndDate
 
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:sas77
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 150 total points
Comment Utility
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
Comment Utility
Thanks all
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now