Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Red face Unanswered: Total Hours Per category

    Hi guys I am trying to create a report on Reporting Services that will give me the totals category. Like a total for THE Contact_tbl.[Catagory for hours] and in those Catagories there is the ITP, THE parenting, the Job Skills hrs, the Travel hrs, the Cultural, and the Educational. I need to provide a total for each of these categories for each Clients. Like how many ITP hours did [Parent First Name] do and then the grand total on the footer of the report. Does that makes sense?? I have an attachment of what it is suppose to look like. Hope that helps

    This is the query I am working with the categories come from the


    Code:
    SELECT     Contact_tbl.[Catagory for hours] AS ITP, Contact_tbl.[Services Covered], Contact_tbl.[Earned hours], Parent_tbl.[Parent Last Name], 
                          Parent_tbl.[Parent First Name], Contact_tbl.[Contact Date], Parent_tbl.[Parent ID], Parent_tbl.[Weekly Hrs], Parent_tbl.[Mo Hours]
    FROM         Parent_tbl INNER JOIN
                          Contact_tbl ON Parent_tbl.[Parent ID] = Contact_tbl.[Parent ID]
    WHERE     (Contact_tbl.[Contact Date] BETWEEN @Beginning_ContactDate AND @End_ContactDate) AND (Contact_tbl.[Catagory for hours] = N'ITP')
    ORDER BY Parent_tbl.[Parent Last Name]
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Methinks you should be doing this Grouping & Summarizing in the Report Designer.

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    thats what I am tryig to do, but I must be doing something wrong

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Sorry I don't have 2005 reporting services handy at mo - if i remember rightly you just add a group to your grid & then insert the Group.SUM in the Group Total Line
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Aug 2009
    Posts
    16
    Try this out, I think it will help you. I have created quite a few reports myself in SSRS, Crystal Reports etc. I find it that creating your subtotals and groupings in SQL is the most efficient and fastest way.

    how-to-use-grouping-and-the-with-rollup-operator-to-create-sub-totals/

Posting Permissions

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