Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Totals are off on RS

    Hi all this is the query I am running in the Reporting Services and there must be something wrong because everytime i ask for a date range it doesnt give me one it gives me all the records instead. Can anyone tell me whats wrong

    Code:
    SELECT     Contact_sc.[Contact Date], Parent_sc.[Parent First Name], Parent_sc.[Parent Last Name], Contact_sc.[Earned hours], 
                          Contact_sc.[Catagory for hours]
    FROM         Contact_sc INNER JOIN
                          Parent_sc ON Contact_sc.[Parent ID] = Parent_sc.[Parent ID]
    WHERE     (Contact_sc.[Contact Date] BETWEEN @Beginning_ContactDate AND @End_ContactDate) AND (Contact_sc.[Catagory for hours] = N'ITP') OR
                          (Contact_sc.[Catagory for hours] = N'Parenting') OR
                          (Contact_sc.[Catagory for hours] = N'Education') OR
                          (Contact_sc.[Catagory for hours] = N'Travel Time') OR
                          (Contact_sc.[Catagory for hours] = N'Cultural') OR
                          (Contact_sc.[Catagory for hours] = N'Job Skills Training')
    ORDER BY Parent_sc.[Parent Last Name]

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    desireemm, It is because the have the BETWEEEN dates and the first Contact_sc.[Catagory for hours] = check separated by an AND and all the other Contact_sc.[Catagory for hours] = separated by OR. This means the BETWEEN dates AND the first Contact_sc.[Catagory for hours] = check have to be TRUE OR any of the other Contact_sc.[Catagory for hours] = checks are TRUE (regardles of the BETWEEN dates). You need to but the block of OR checks in parenthesis if you want the Between dates is TRUE AND (any of the Contact_sc.[Catagory for hours] = checks are TRUE).
    Code:
    SELECT Contact_sc.[Contact Date]
         , Parent_sc .[Parent First Name]
         , Parent_sc .[Parent Last Name]
         , Contact_sc.[Earned hours]
         , Contact_sc.[Catagory for hours]
    FROM Contact_sc 
           INNER JOIN
         Parent_sc 
          ON Contact_sc.[Parent ID] = Parent_sc.[Parent ID]
    WHERE Contact_sc.[Contact Date] BETWEEN @Beginning_ContactDate 
                                        AND @End_ContactDate
      AND (
              Contact_sc.[Catagory for hours] = N'ITP'
           OR Contact_sc.[Catagory for hours] = N'Parenting' 
           OR Contact_sc.[Catagory for hours] = N'Education'
           OR Contact_sc.[Catagory for hours] = N'Travel Time'
           OR Contact_sc.[Catagory for hours] = N'Cultural'
           OR Contact_sc.[Catagory for hours] = N'Job Skills Training'
          )
    ORDER BY Parent_sc.[Parent Last Name]
    Alternately, I believe you can use the IN clause.
    Code:
    SELECT Contact_sc.[Contact Date]
         , Parent_sc .[Parent First Name]
         , Parent_sc .[Parent Last Name]
         , Contact_sc.[Earned hours]
         , Contact_sc.[Catagory for hours]
    FROM Contact_sc 
           INNER JOIN
         Parent_sc 
          ON Contact_sc.[Parent ID] = Parent_sc.[Parent ID]
    WHERE Contact_sc.[Contact Date] BETWEEN @Beginning_ContactDate 
                                        AND @End_ContactDate
      AND Contact_sc.[Catagory for hours] IN( N'ITP'
                                            , N'Parenting'
                                            , N'Education',
                                            , N'Travel Time'
                                            , N'Cultural'
                                            , N'Job Skills Training'
                                            )
    ORDER BY Parent_sc.[Parent Last Name]

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    thank you so much steath that did it. Now I have to group it by the [category for hours] but I keep getting an error

    Code:
    SELECT     Contact_sc.[Contact Date], Parent_sc.[Parent First Name], Parent_sc.[Parent Last Name], Contact_sc.[Earned hours], Contact_sc.[Catagory for hours], 
                          Parent_sc.[Parent ID], Contact_sc.ID
    FROM         Contact_sc INNER JOIN
                          Parent_sc ON Contact_sc.[Parent ID] = Parent_sc.[Parent ID]
    WHERE     (Contact_sc.[Contact Date] BETWEEN @Beginning_ContactDate AND @End_ContactDate) AND (Contact_sc.[Catagory for hours] = N'ITP' OR
                          Contact_sc.[Catagory for hours] = N'Parenting' OR
                          Contact_sc.[Catagory for hours] = N'Education' OR
                          Contact_sc.[Catagory for hours] = N'Travel Time' OR
                          Contact_sc.[Catagory for hours] = N'Cultural' OR
                          Contact_sc.[Catagory for hours] = N'Job Skills Training')
    ORDER BY Parent_sc.[Parent Last Name] AND Contact_sc.[Catagory for hours] = N'ITP	
          Contact_sc.[Catagory for hours] = N' Parenting AND 
                          Contact_sc.[Catagory for hours] = N'Job Skills Training  AND 
    Contact_sc.[Catagory for hours] = N' Travel Time AND 
                          Contact_sc.[Catagory for hours] = N' Cultural  and
      Contact_sc.[Catagory for hours] = N' Education

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    desireemm, I haven't used the RS yet so I am not sure of the answer. If it is just SQL, I might help. If it is some RS syntax, I can't.

    I think this is RS syntax as with just SQL, you wouldn't Group By a column like Catagory for Hours (assuming this is a value representing some Hours). You would SUM the hours and Group By the other columns.

    Hopefully, someone who does know RS can help you further.

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    on the report the first catagory for hours that should show on the colum is
    ITP/Parenting/Job skills Training/Travel Time/Cultural/Education
    I am just trying to get the report to show the colums in that order. dont I do that in the GROUP BY CLAUSE of SQL?? or No??

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by desireemm
    dont I do that in the GROUP BY CLAUSE of SQL?? or No??
    probably not -- i think you want ORDER BY

    note: the way you are using AND in the ORDER BY clause is wrong

    you want commas instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    ok thank you R937 instead of the and it should be OR ??

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by desireemm
    ok thank you R937 instead of the and it should be OR ??
    um, no

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    here is what I got and keep getting an error message cant see what I am doing wrong

    Msg 102, Level 15, State 1, Line 11
    Incorrect syntax near ','.

    Code:
    SELECT     Contact_sc.[Contact Date], Parent_sc.[Parent First Name], Parent_sc.[Parent Last Name], Contact_sc.[Earned hours], Contact_sc.[Catagory for hours], 
                          Parent_sc.[Parent ID], Contact_sc.ID
    FROM         Contact_sc INNER JOIN
                          Parent_sc ON Contact_sc.[Parent ID] = Parent_sc.[Parent ID]
    WHERE     (Contact_sc.[Catagory for hours] = N'ITP' OR
                          Contact_sc.[Catagory for hours] = N'Parenting' OR
                          Contact_sc.[Catagory for hours] = N'Education' OR
                          Contact_sc.[Catagory for hours] = N'Travel Time' OR
                          Contact_sc.[Catagory for hours] = N'Cultural' OR
                          Contact_sc.[Catagory for hours] = N'Job Skills Training')
    ORDER BY (Parent_sc.[Parent Last Name], 
     Contact_sc.[Catagory for hours] = N'ITP' OR
                           ,Contact_sc.[Catagory for hours] = N'Parenting' OR
                          ,Contact_sc.[Catagory for hours] = N'Education' OR
                          ,Contact_sc.[Catagory for hours] = N'Travel Time' OR
                          ,Contact_sc.[Catagory for hours] = N'Cultural' OR
                          Contact_sc.[Catagory for hours] = N'Job Skills Training')

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please run exactly this query, don't change a thing, just copy it exactly
    Code:
    SELECT Contact_sc.[Contact Date]
         , Parent_sc.[Parent First Name]
         , Parent_sc.[Parent Last Name]
         , Contact_sc.[Earned hours]
         , Contact_sc.[Catagory for hours]
         , Parent_sc.[Parent ID]
         , Contact_sc.ID
      FROM Contact_sc 
    INNER 
      JOIN Parent_sc 
        ON Parent_sc.[Parent ID] = Contact_sc.[Parent ID] 
     WHERE Contact_sc.[Catagory for hours] IN
               ( N'ITP' 
               , N'Parenting' 
               , N'Education' 
               , N'Travel Time' 
               , N'Cultural' 
               , N'Job Skills Training' )
    ORDER 
        BY Parent_sc.[Parent Last Name]
         , Contact_sc.[Catagory for hours]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    thank you but the colum headings have to be in this order
    ITP Parenting Job Skills Training Travel Time Cultural Education

    Is that possible??

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    ORDER 
        BY Parent_sc.[Parent Last Name]
         , CASE WHEN Contact_sc.[Catagory for hours] = N'ITP' THEN 0 ELSE 1 END
         , CASE WHEN Contact_sc.[Catagory for hours] = N'Parenting'  THEN 0 ELSE 1 END
         , CASE WHEN Contact_sc.[Catagory for hours] = N'Education'  THEN 0 ELSE 1 END
         , CASE WHEN Contact_sc.[Catagory for hours] = N'Travel Time'  THEN 0 ELSE 1 END
         , CASE WHEN Contact_sc.[Catagory for hours] = N'Cultural'  THEN 0 ELSE 1 END
         , CASE WHEN Contact_sc.[Catagory for hours] = N'Job Skills Training'  THEN 0 ELSE 1 END
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    sorry R937 it didnt work, not sure what the problem is.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by desireemm
    sorry R937 it didnt work, not sure what the problem is.
    neither am i, since i can't see what you did

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    here is what I did, see its a matrix on RS which has the [catagory for hours] as the colum group and the [Parent Id] is the Rowgroup. I used what you gave me



    Code:
    SELECT     Contact_sc.[Contact Date], Parent_sc.[Parent First Name], Parent_sc.[Parent Last Name], Contact_sc.[Earned hours], Contact_sc.[Catagory for hours], 
                          Parent_sc.[Parent ID], Contact_sc.ID
    FROM         Contact_sc INNER JOIN
                          Parent_sc ON Contact_sc.[Parent ID] = Parent_sc.[Parent ID]
    WHERE     (Contact_sc.[Contact Date] BETWEEN @Beginning_ContactDate AND @End_ContactDate) AND (Contact_sc.[Catagory for hours] = N'ITP' OR
                          Contact_sc.[Catagory for hours] = N'Parenting' OR
                          Contact_sc.[Catagory for hours] = N'Education' OR
                          Contact_sc.[Catagory for hours] = N'Travel Time' OR
                          Contact_sc.[Catagory for hours] = N'Cultural' OR
                          Contact_sc.[Catagory for hours] = N'Job Skills Training')
    ORDER BY Parent_sc.[Parent Last Name], CASE WHEN Contact_sc.[Catagory for hours] = N'ITP' THEN 0 ELSE 1 END, 
                          CASE WHEN Contact_sc.[Catagory for hours] = N'Parenting' THEN 0 ELSE 1 END, 
                          CASE WHEN Contact_sc.[Catagory for hours] = N'Education' THEN 0 ELSE 1 END, 
                          CASE WHEN Contact_sc.[Catagory for hours] = N'Travel Time' THEN 0 ELSE 1 END, 
                          CASE WHEN Contact_sc.[Catagory for hours] = N'Cultural' THEN 0 ELSE 1 END, 
                          CASE WHEN Contact_sc.[Catagory for hours] = N'Job Skills Training' THEN 0 ELSE 1 END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •