none
Display sum values in a column RRS feed

  • Question

  • Hi all,

    I am trying to display SUM of column values based on same type of ITService's in a separate column.

    I have to do SUM of 29 and 38 and display at 67 location based on ITServices. I am not using any grouping on a  column and just a tablix with details.If I use row group on CategoryName's, data doesnt display correctly. So I don't want to create a ROW GROUP on ITServices.

    Please help, how can I achieve. Thanks in advance.


    Maruthi...



    Saturday, July 27, 2013 10:58 PM

Answers

  • Hi Maruthi,

    According to your description, you want to calculate the total [minutes] field group on [ITService] field, display it on the first row if there are duplicate records for [ITService] field, and you don't want to create a row group on [ITServices] field, right? If in this case, we can calculate the total minutes on another dataset, and use Lookup function to get the corresponding value. I have tested it on my local environment, the steps below are for your reference.

    1. Create a new dataset using the query below:
      SELECT     ITSERVICES, SUM(MIMS Total downtime in minutes) AS Sum
      FROM         TABLENAME
      GROUP BY ITSERVICES
    2. Type into the expression below in ”Total downtime in minutes” column.
      =IIF(Fields!ITSERVICES.Value=Previous(Fields!ITSERVICES.Value),"",
      Lookup(Fields!ITSERVICES.Value,Fields!ITSERVICES.Value,Fields!Sum.Value,"DataSet2"))

    The report looks like below:

    For more information about Lookup function, please refer to following link:
    http://msdn.microsoft.com/en-us/library/ee210531.aspx

    If you have any questions, please feel free to ask.

    Regards,
    Nicole Yu


    Nicole Yu
    TechNet Community Support

    Monday, July 29, 2013 11:36 AM

All replies

  • Hi,

    Try this link - http://blogs.msdn.com/b/robertbruckner/archive/2008/07/20/using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspx

    If the dataset is T-SQL query then you can also try like below :

    DECLARE @tmp TABLE (ITService VARCHAR(20),EscalatedIncidents CHAR(5),MIMS INT)
    INSERT @tmp SELECT 'A','A1',19
    INSERT @tmp SELECT 'B','B1',21
    INSERT @tmp SELECT 'C','C1',12
    INSERT @tmp SELECT 'D','D1',29
    INSERT @tmp SELECT 'D','D2',38
    INSERT @tmp SELECT 'E','E1',29
    INSERT @tmp SELECT 'E','E2',38
    SELECT ITService,
    CASE WHEN rn = 2 THEN NULL ELSE TotalDownTime END TotalDownTime,
    EscalatedIncidents,
    MIMS 
    FROM (
    SELECT ITService,
    row_number()OVER(Partition by ITService ORDER BY ITService) Rn,
    SUM(MIMS)OVER(PARTITION BY ITService ORDER BY ITService) TotalDownTime,
    EscalatedIncidents,
    MIMS
    FROM @tmp ) Tmp


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.



    Sunday, July 28, 2013 6:01 AM
    Moderator
  • Hi Sathya,

    Why the SQL query is not working in SQL2008R2 version and also throwing error like:

    Msg 11305, Level 15, State 10, Line 18
    The Parallel Data Warehouse (PDW) features are not enabled.

    Could you please explain? Thanks in advance :)


    Maruthi...

    Sunday, July 28, 2013 9:12 AM
  • Hi Maruthi,

    According to your description, you want to calculate the total [minutes] field group on [ITService] field, display it on the first row if there are duplicate records for [ITService] field, and you don't want to create a row group on [ITServices] field, right? If in this case, we can calculate the total minutes on another dataset, and use Lookup function to get the corresponding value. I have tested it on my local environment, the steps below are for your reference.

    1. Create a new dataset using the query below:
      SELECT     ITSERVICES, SUM(MIMS Total downtime in minutes) AS Sum
      FROM         TABLENAME
      GROUP BY ITSERVICES
    2. Type into the expression below in ”Total downtime in minutes” column.
      =IIF(Fields!ITSERVICES.Value=Previous(Fields!ITSERVICES.Value),"",
      Lookup(Fields!ITSERVICES.Value,Fields!ITSERVICES.Value,Fields!Sum.Value,"DataSet2"))

    The report looks like below:

    For more information about Lookup function, please refer to following link:
    http://msdn.microsoft.com/en-us/library/ee210531.aspx

    If you have any questions, please feel free to ask.

    Regards,
    Nicole Yu


    Nicole Yu
    TechNet Community Support

    Monday, July 29, 2013 11:36 AM
  • Hi Nicole,

    Perfect, Its worked like a thunder storm :)

    Thank you for your help and very kind of you.

    Could you please help me my other post as well?

    http://social.technet.microsoft.com/Forums/sqlserver/en-US/b5585c42-fffc-42c3-a5e7-26e16102df0b/display-number-on-top-of-report


    Maruthi...

    Monday, July 29, 2013 2:47 PM