locked
Sorting a report RRS feed

  • Question

  • Hi,

    I've a got a report which is technically divided in three parts. 1st part where Spent Hours are shown but Earned Hours are not shown, 2nd part shows both Spent and Earned Hours (which is logically the correct report) and the 3rd part which shows where Spent Hours are not shown but Earned Hours are shown.

    I'm looking to sort this in an order that 2nd part comes to the top, while 1st and 3rd part are shown below it so that their total time is added to the Total Column sitting at the top of the report labelled as 'Period Total'.

    if you guys take a look at current report, it is divided into two groups for the same date... 1st part and 2nd and 3rd part.

    I'm attaching few screenshots below, including the screenshot of a report design canvas. I've exported the report in Excel 2010 format and posting a full report link for 1st of January, 18. Screenshots will help understanding the excel file pasted below.

    https://drive.google.com/open?id=1V3RRN9jmi_-7DpdUPMc00dB226_m6EYu

    Thanks for any help.

    Canvas:

    1st Part of the report:



    Wednesday, January 17, 2018 6:35 AM

Answers

  • Solved the issue myself by changing the main query and set it up in a way that allowed better grouping in report. Thanks all for help.
    • Marked as answer by KhurramKZ Sunday, February 25, 2018 5:49 AM
    Sunday, February 25, 2018 5:49 AM

All replies

  • Hi KhurramKZ,

    According to your description, I try to reproduce your design. I find that your entire 2nd part seems to be grouped in  [Project] , right? If so, you might could achieve your goal by adding a parent group on [Project].

    Below is my design:

    My dataset:(the [period] is like your [project]) 


    Then I create table like below

    After that I add a parent group in table, and configure its sort like below

    =switch(Fields!SUP.Value="B",1,Fields!SUP.Value="A",2,Fields!SUP.Value="C",3)

    Then you will get the result like below(you could hide the [sup1] column)

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, January 18, 2018 5:29 AM
  • As far as I understand since you've the report setup correctly now all you need is to apply sorting like below for the each group which shows the hour values as

    IIF(Sum(Fields!SpendHours.Value) >0 AND Sum(Fields!EarnedHours.Value) >0,1,2)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 18, 2018 5:40 AM
  • Hi Zoe,

    Thanks for all your efforts but I still could not make it work. If I've understood correct, I've added another column for Sup. i.e. SupervisorID and used SWITCH as you suggested above... but what's happening is i'm posting in screenshots below.

    In first screenshot 'Period Total' is now divided based on values used in SWITCH because there's another Parent Group now.
    In second screenshot , I've added Group Header on top of 'Period Total' for the Sup, but total values in this header are also not correct. I've tried to show couple of supervisors in screenshots.

    I've tried hard to understand what you suggested and spent good time to make it work... and also tried bit of other idea appeared to my mind that moment. let me know please if I've still missed something from what you've suggested?

    Thank you.

    Thursday, January 18, 2018 9:59 AM
  • Hi Visakh,

    Thank you, at least this has fixed sorting issue but group total issue remains. 'Period Total' works ok for 'Earned Hours' and shows total of two but 'Spent Hours' it is still not adding up for the activities with missing Earned Hours.

    I'm uploading excel file so you understand what I mean. I also post couple of screenshots from the same excel file to understand a bit, in case you're not able to use Excel one.

    https://drive.google.com/open?id=1QvwwZro_HUu0v1qmkg7WP9VQpZxkDalD

    Thank you. Hoping that I'm close to solution with the help provided by you guys.


    • Edited by KhurramKZ Thursday, January 18, 2018 10:10 AM
    Thursday, January 18, 2018 10:08 AM
  • For understanding what is happening in this, we would need some sample data and your rdl. Otherwise we cant understand whether its because of underlying data, expression issue SSRS or grouping problem

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 18, 2018 10:33 AM
  • Thanks for consideration. I'm uploading a part of SQL Server database and RDL file to the report in a zip file.

    Please select the filter to 1st of Jan, 18 for the Start and End date. Although there's other data available but this is was it posting earlier, 1st Jan.
    Hope it'll help to get me somewhere.

    here's the link to zip file: https://drive.google.com/file/d/1PbbyYDu47_p-B7lZIpMHM3GRX7FSVGLt/view?usp=sharing

    Thank you


    • Edited by KhurramKZ Thursday, January 18, 2018 12:31 PM
    Thursday, January 18, 2018 12:25 PM
  • Hi KhurramKZ,

    My sup group is not like yours, so you need to red circle's group to replace your supervisorID1's group, and use switch to see whether it works or not.

    But the total value will calculated based on group, so it will get the different value for you.

    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 19, 2018 8:36 AM
  • Yes, it does give a different value and that's the problem because in this case it doesn't work for me (
    Sunday, January 21, 2018 11:33 AM
  • Hi Visakh, did you get a chance to look into RDL file please?

    Sunday, January 21, 2018 11:34 AM
  • Hi Visakh, did you get a chance to look into RDL file please?

    Sorry I didnt get a chance

    When I tried now, it looks like its using a shared data source and its missing in the zip.

    Can you please share the rds? Alternatively post the query or SP which your dataset uses for the report


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 21, 2018 12:11 PM
  • Thanks. Here's the Procedure used for this query

    USE [PCMS_T1]
    GO
    /****** Object:  StoredProcedure [dbo].[ssrs_procDailySEFull]    Script Date: 1/21/2018 5:41:56 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[ssrs_procDailySEFull](@StartDate datetime, @EndDate datetime)
    AS
    BEGIN
        WITH cteDailySEWorkPackEmployee AS
        (    
            SELECT
                CONVERT(DATE, tblEmployee_WorkPack.Assignment_Date) AS DateAdded
                ,tblEmployee_WorkPack.SupervisorID
                ,tblEmployee_WorkPack.AreaID
                ,tblEmployee_WorkPack.WorkPackID
                ,tblEmployee_WorkPack.EmployeeID
                ,DateDiff(N
                    ,Case IsDate([signedin])    When 1 Then CONVERT(DATETIME,[signedin])   Else Null End
                    ,CASE IsDate([signedout])    When 1 Then CONVERT(DATETIME, [signedout]) Else Null End )/60.0 AS Spent_Hours
                ,CASE IsDate([Start_time])        When 1 Then CONVERT(TIME, [Start_time])  Else Null End AS [Start Time]
                ,CASE IsDate([End_time])        When 1 Then CONVERT(TIME, [End_time]) Else NULL End AS [End Time]
                ,DateDiff(N
                    ,Case IsDate([Start_time])    When 1 Then CONVERT(DATETIME,[Start_time]) Else Null End
                    ,CASE IsDate([End_time])    When 1 Then CONVERT(DATETIME, [End_time]) Else Null End )/60.0 AS Spent_HoursWP
                ,tblEmployee_WorkPack.Lost_Hours
                ,IIF(((DATEDIFF(MI, SignedIn, SignedOut)/60 >= 8) AND (CONVERT(Time, Start_Time) <= '12:30:00' AND CONVERT(Time, End_Time) >= '13:30:00')), 1.0, 0.0) AS Rest_Hours
                ,CONVERT(DATE, [tblEmp_WP_Clocked_Time].[SignedIn]) AS WP_SignedIn
            FROM
                tblEmployee
                    INNER JOIN
                tblEmp_WP_Clocked_Time    ON tblEmployee.ID = tblEmp_WP_Clocked_Time.EmployeeID
                    INNER JOIN
                tblEmployee_WorkPack    ON tblEmployee.ID = tblEmployee_WorkPack.EmployeeID
                    AND CONVERT(DATE, tblEmployee_WorkPack.Assignment_Date) = CONVERT(DATE, tblEmp_WP_Clocked_Time.SignedIn)
            WHERE
                --tblEmployee_WorkPack.Role_Type = 1 **Only Directs are present in this table
                SignedOut        IS NOT NULL
                AND End_time    IS NOT NULL
                AND tblEmp_WP_Clocked_Time.ID = (SELECT MAX(ID) FROM tblEmp_WP_Clocked_Time AS CS WHERE CS.EmployeeID = tblEmp_WP_Clocked_Time.EmployeeID AND CONVERT(DATE, CS.SignedIn) = CONVERT(DATE, tblEmp_WP_Clocked_Time.SignedIn))
                AND Start_time    >= @StartDate
                AND End_time    <  @EndDate + 1
        ),
        cteDailySEWorkPackEmployeeGroupBy AS
        (
        SELECT
            cteDailySEWorkPackEmployee.DateAdded
            ,cteDailySEWorkPackEmployee.SupervisorID
            ,cteDailySEWorkPackEmployee.AreaID
            ,cteDailySEWorkPackEmployee.WorkPackID
            ,cteDailySEWorkPackEmployee.WP_SignedIn
            ,Sum(cteDailySEWorkPackEmployee.Spent_Hours)    AS [Spent HoursSum]
            ,Sum(cteDailySEWorkPackEmployee.Spent_HoursWP)    AS [Work HoursSum] --Work HoursSum is for Spent Time Sum for a WP
            ,Sum(cteDailySEWorkPackEmployee.Rest_Hours)        AS [Rest HoursSum]
            ,Sum(cteDailySEWorkPackEmployee.Lost_Hours)        AS [Lost HoursSum]
        FROM
            cteDailySEWorkPackEmployee
        GROUP BY
            cteDailySEWorkPackEmployee.DateAdded
            ,cteDailySEWorkPackEmployee.SupervisorID
            ,cteDailySEWorkPackEmployee.AreaID
            ,cteDailySEWorkPackEmployee.WorkPackID
            ,cteDailySEWorkPackEmployee.WP_SignedIn
        ),
        cteDailySEP1 AS
        (
        SELECT
            cteDailySEWorkPackEmployeeGroupBy.DateAdded AS [Activity Date]
            ,dprocDailySEProgressGroupBy.DateAdded
            ,dprocDailySEProgressGroupBy.Region
            ,dprocDailySEProgressGroupBy.Location
            ,dprocDailySEProgressGroupBy.Project
            ,dprocDailySEProgressGroupBy.PM
            ,dprocDailySEProgressGroupBy.SupervisorID
            ,cteDailySEWorkPackEmployeeGroupBy.AreaID
            ,cteDailySEWorkPackEmployeeGroupBy.WorkPackID
            ,cteDailySEWorkPackEmployeeGroupBy.WP_SignedIn
            ,dprocDailySEProgressGroupBy.[Earned HoursSum]
            ,dprocDailySEProgressGroupBy.[Earned ValueSum]
            ,CONVERT(DECIMAL(14,2), cteDailySEWorkPackEmployeeGroupBy.[Spent HoursSum]) AS [Spent HoursSum]
            ,cteDailySEWorkPackEmployeeGroupBy.[Work HoursSum]
            ,cteDailySEWorkPackEmployeeGroupBy.[Rest HoursSum]
            ,CONVERT(DECIMAL(14,2), cteDailySEWorkPackEmployeeGroupBy.[Lost HoursSum]) AS [Lost HoursSum]
            ,CONVERT(DECIMAL(14,2), ([Work HoursSum]-[Rest HoursSum]-[Lost HoursSum])) AS [Effective Hours]
            ,IIF(Round(cast(([Spent HoursSum]/(IIF([Earned HoursSum] = 0, NULL, [Earned HoursSum]))) as float),2) IS NULL, 0, Round(cast(([Spent HoursSum]/(IIF([Earned HoursSum] = 0, NULL, [Earned HoursSum]))) as float),2)) AS BaseSE
            ,IIF(Round(cast((([Spent HoursSum]-[Rest HoursSum]-[Lost HoursSum])/(IIF([Earned HoursSum] = 0, NULL, [Earned HoursSum]))) as float),2) IS NULL, 0, Round(cast((([Spent HoursSum]-[Rest HoursSum]-[Lost HoursSum])/(IIF([Earned HoursSum] = 0, NULL, [Earned HoursSum]))) as float),2)) AS TrueSE
            ,IIF([Spent HoursSum] = 0, 0, Round(cast(([Earned HoursSum]/IIF(([Spent HoursSum]-[Rest HoursSum]-[Lost HoursSum]) = 0, NULL, ([Spent HoursSum]-[Rest HoursSum]-[Lost HoursSum]))) AS Float) ,3)) AS Efficiency
        FROM
            cteDailySEWorkPackEmployeeGroupBy
            INNER JOIN (SELECT
                            CONVERT(Date, tblProgress_Daily.Date_Added) AS DateAdded
                            ,tblWBS_Lev0_Project.Region
                            ,tblWBS_Lev0_Project.Location
                            ,tblWBS_Lev0_Project.ID                        AS Project
                            ,tblEmployee.FullName                        AS PM
                            ,tblProgress_Daily.SupervisorID
                            ,tblWBS_Lev2_Area.ID                            AS AreaID
                            ,tblWBS_Lev3_WorkPack.ID                        AS WorkPackID
                            ,Sum(tblProgress_Daily.Today_ManHour)        AS [Earned HoursSum]
                            ,Sum(tblProgress_Daily.Today_Earned_Value)    AS [Earned ValueSum]
                        FROM            
                            dbo.tblWBS_Lev0_Project
                                INNER JOIN
                            dbo.tblROC_InstallationType ON tblWBS_Lev0_Project.ID = tblROC_InstallationType.ProjectID
                                INNER JOIN
                            dbo.tblWBS_Lev3_WorkPack    ON tblROC_InstallationType.WorkPackID = tblWBS_Lev3_WorkPack.ID
                                INNER JOIN
                            dbo.tblWBS_Lev2_Area        ON tblROC_InstallationType.AreaID = tblWBS_Lev2_Area.ID
                                INNER JOIN
                            dbo.tblBoQ                    ON tblROC_InstallationType.ID = tblBoQ.InstallationTypeID
                                INNER JOIN
                            dbo.tblBoQ_Progress            ON tblBoQ.ID = tblBoQ_Progress.BoQID
                                INNER JOIN
                            dbo.tblProgress_Daily        ON tblBoQ_Progress.ID = tblProgress_Daily.BoQProgressID
                                INNER JOIN
                            dbo.tblProject_Supervisor    ON tblProgress_Daily.SupervisorID = tblProject_Supervisor.ID
                                INNER JOIN
                            dbo.tblProjectManager        ON tblProject_Supervisor.ProjectManagerID = tblProjectManager.ID
                                INNER JOIN
                            dbo.tblEmployee                ON tblProjectManager.ProjectManager = tblEmployee.ID
                        WHERE
                            tblProgress_Daily.Date_Added >= @StartDate
                            AND tblProgress_Daily.Date_Added < @EndDate + 1
                        GROUP BY
                            CONVERT(Date, [tblProgress_Daily].[Date_Added])
                            ,tblWBS_Lev0_Project.Region
                            ,tblWBS_Lev0_Project.Location
                            ,tblWBS_Lev0_Project.ID
                            ,tblEmployee.FullName
                            ,tblProgress_Daily.SupervisorID
                            ,tblWBS_Lev2_Area.ID
                            ,tblWBS_Lev3_WorkPack.ID) AS dprocDailySEProgressGroupBy
            ON cteDailySEWorkPackEmployeeGroupBy.WorkPackID    = dprocDailySEProgressGroupBy.WorkPackID
                    AND cteDailySEWorkPackEmployeeGroupBy.AreaID        = dprocDailySEProgressGroupBy.AreaID
                    AND cteDailySEWorkPackEmployeeGroupBy.SupervisorID    = dprocDailySEProgressGroupBy.SupervisorID
                    AND cteDailySEWorkPackEmployeeGroupBy.DateAdded        = dprocDailySEProgressGroupBy.DateAdded

        )
            SELECT
            cteDailySEP1.[Activity Date]
            ,cteDailySEP1.DateAdded
            ,cteDailySEP1.Region
            ,cteDailySEP1.Location
            ,cteDailySEP1.Project
            ,cteDailySEP1.PM
            ,cteDailySEP1.SupervisorID
            ,cteDailySEP1.AreaID
            ,cteDailySEP1.WorkPackID
            ,cteDailySEP1.WP_SignedIn
            ,cteDailySEP1.[Earned HoursSum]
            ,cteDailySEP1.[Earned ValueSum]
            ,cteDailySEP1.[Spent HoursSum]
            ,cteDailySEP1.[Work HoursSum]
            ,cteDailySEP1.[Rest HoursSum]
            ,cteDailySEP1.[Lost HoursSum]
            ,cteDailySEP1.[Effective Hours]
            ,cteDailySEP1.BaseSE
            ,cteDailySEP1.TrueSE
            ,cteDailySEP1.Efficiency
        FROM         
            cteDailySEP1
    END

    Sunday, January 21, 2018 1:44 PM
  • I had a look at your report and understood the issue. The expression you've for the 2nd level grouping is different from the actual grouping expression. That was the reason why the values were not adding up properly

    The expression used in group header column was

    =IIF(IsNothing(Fields!Activity_Date.Value), "Daily Total: " & Fields!DateAdded.Value,"Daily Total: " & Fields!Activity_Date.Value)

    Whereas grouping was only based on Activity_Date   column

    To make it work correctly just change group expression to be same as the above

    for second group. Then values will all add up correctly

    link of modified rdl attached

    https://drive.google.com/file/d/1f7g0LVORR_VDJeoHEgHWYXJ-zuIQgb6O/view?usp=sharing


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 21, 2018 6:02 PM
  • Could not open the RDL file. I'm using Data Tools for 2012 and SQL Server 2014.

    I've got this error message initially:

    The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition' which cannot be upgraded.

    Edit code

    i went into the code and changed the line to:
    <Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">

    ...then i get another error message:

    Deserialization failed: The element 'Report' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' has invalid child element 'ReportParametersLayout' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'. List of possible elements expected: 'Description, Author, AutoRefresh, InitialPageName, DataSources, DataSets, ReportParameters, Code, EmbeddedImages, Language, CodeModules, Classes, CustomProperties, Variables, DeferVariableEvaluation, ConsumeContainerWhitespace, DataTransform, DataSchema, DataElementName, DataElementStyle, ReportSections' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' as well as any element in namespace '##other'. Line 2779, position 4.

    ...and I could not figure out how to get it fixed please?
    I also have report builder v3.0. if possible to edit the code in that version so that I could run exactly that please??

    Thank you.

    Monday, January 22, 2018 6:54 AM
  • Could not open the RDL file. I'm using Data Tools for 2012 and SQL Server 2014.

    I've got this error message initially:

    The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition' which cannot be upgraded.

    Edit code

    i went into the code and changed the line to:
    <Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">

    ...then i get another error message:

    Deserialization failed: The element 'Report' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' has invalid child element 'ReportParametersLayout' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'. List of possible elements expected: 'Description, Author, AutoRefresh, InitialPageName, DataSources, DataSets, ReportParameters, Code, EmbeddedImages, Language, CodeModules, Classes, CustomProperties, Variables, DeferVariableEvaluation, ConsumeContainerWhitespace, DataTransform, DataSchema, DataElementName, DataElementStyle, ReportSections' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' as well as any element in namespace '##other'. Line 2779, position 4.

    ...and I could not figure out how to get it fixed please?
    I also have report builder v3.0. if possible to edit the code in that version so that I could run exactly that please??

    Thank you.

    I dont have 2012 here

    We're on 2016

    Let me find a 2012 box and I'll send the modified rdl across


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, January 22, 2018 7:03 AM
  • Can you try this?

    https://drive.google.com/file/d/1AkEiaZLbc6e-QH60NaJXX--OABocn1PZ/view?usp=sharing

    Just managed to get a 2012 box from a colleague to do the change

    Didnt get time to test properly. Revert if you find any issues


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, January 22, 2018 11:08 AM
  • Thanks Vikash,

    I could open the file now but still not getting the required results. When I run with the INNER JOIN the query runs OK, like it was running earlier... but when you change the last INNER JOIN to FULL OUTER JOIN, and it is how we get the Earned Hours values with missing Spent Hours and Spent Hours values with missing Earned Hours values with FULL OUTER JOIN, portion with the missing Earned Hours still appearing at the bottom of the page but still not adding up to the Period Total at the top of the page... while values with missing Spent Hours are completely disappeared from the new report.

    here's the zip file with excel export with old and new query results for 1st of Jan, along with SQL file with FULL OUTER JOIN applied. Hope this will get me sorted.

    https://drive.google.com/open?id=1BzktHRMmdU5NrclWKITLh_HioJFrcEsG

    Thank you so much for your help in advance.

    Regards,
    K

    Tuesday, January 23, 2018 2:02 PM
  • Solved the issue myself by changing the main query and set it up in a way that allowed better grouping in report. Thanks all for help.
    • Marked as answer by KhurramKZ Sunday, February 25, 2018 5:49 AM
    Sunday, February 25, 2018 5:49 AM