locked
Group on a new calculated field in a tablix RRS feed

  • Question

  • Hello everyone,
    I have a dataset within a Report where I calculate the count of the lines (values of a measure)

    E.g.
    Customer           #trades              #TradesText
    A1                        2                          2 trades
    A2                        5                          5 trades

    A3                        7                          7 trades

    A4                        2                          2 trades
    A5                        5                          5 trades

    #Trades is a counted field within the tablix.

    How do I sum on that new field #trades or #tradesText in a summary line ?

    The report should look like this, doing an aggregation on the new calculated field:

    #Trades Text                   Count

    2 trades                            2

    5 trades                            2

    7 trades                            1

    Any help on that is much appreciated.

    Thursday, August 13, 2020 8:34 AM

All replies

  • How do I sum on that new field #trades or #tradesText in a summary line ?

    The result isn't a line, so add an additional tablix with the same dataset, group on "#Trades Text" and add a column with a count.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 13, 2020 9:02 AM
  • Thanks for your reply.
    #TradesText is an already calculated field in the Tablix1. In the Dataset I have the data on single row level.
    And I cannot refer to a Grouped By- or aggregated field in a new Aggregate-Tablix-

    Thursday, August 13, 2020 9:12 AM
  • Hi,

    Hope that I get your meaning correctly.

    #Trades Text is a calculated field in the Tablix1.

    #Trades is a count fields in the Tablix1.

    I did some test locally to do a similar setup as yours'.

    Add group with the calculated field #Trades Text.

    Add Total of the count fields:

    Delete the Details and you'll get the reports.

    If this way could not meet your needs,I'd like to suggest to process these data beforehand by T-SQL in dataset.

    We could easily design the report after dealing with these data in T-SQL.

    Regards,

    Zoe


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    • Edited by Zoehui-MSFT Friday, August 14, 2020 7:27 AM typo
    • Proposed as answer by Zoehui-MSFT Monday, August 17, 2020 1:27 AM
    Friday, August 14, 2020 7:26 AM