locked
SSRS Sum of a field based on other field RRS feed

  • Question

  • Hi All,

    I have a requirement of converting crystal report in to SSRS Report.In crystal report , a field is calculated by taking the sum of that field based on other field in the same dataset.Can you please let me know how to achieve this in SSRS?

    In crystal it is written like below:-

    If Count ({@# Rptd Stores},{@grp01})>0 then 
    sum({CR_Surveys_PID.SurveyPrice},{@grp01})/Count ({@# Rptd Stores},{@grp01})

    Here grp01 and # Rptd Stores are calculated fields.Please let me know how to achieve this in SSRS Report?

    Thanks,

    Surya

    Tuesday, December 25, 2018 7:07 AM

All replies

  • See if this link helps you

    https://stackoverflow.com/questions/636573/create-sum-of-calculated-rows-in-microsoft-reporting-services


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 25, 2018 7:49 AM
  • Hi Surya RS

    What does the Count ({@# Rptd Stores},{@grp01}) mean in crystal report ?

    Does it mean count({@# Rptd Stores})+count({@grp01})? Or what other meaning ?

    You need to do use the full calculate expression to instead of the calculate filed in the expression.

    IIF(Count(<calculated_filed_expression>)>0,Sum((<calculated_filed_expression>)/Count((<calculated_filed_expression>),Field!grp01.Value)

    You could offer more detailed information about your calculated filed and the data sample to us for more further research.

    Hoping for your reply.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if 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.

    Tuesday, December 25, 2018 8:21 AM
  • Hi Eric,

    Count ({@# Rptd Stores},{@grp01})  in crystal means taking the count of # Rptd Stores based on other field grp01 within the same dataset.

    Thanks,

    Surya

    Tuesday, December 25, 2018 5:00 PM
  • In SSRS you can write like this

    IIf(Count(Fields!Rptd_Stores.Value,"grp01") > 0, 
    sum(Fields!SurveyPrice.Value,"grp01")/IIF(Count(Fields!Rptd_Stores.Value,"grp01") > 0, Count(Fields!Rptd_Stores.Value,"grp01"),1),0)


    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

    Tuesday, December 25, 2018 5:40 PM
  • Hi Eric,

    Count ({@# Rptd Stores},{@grp01})  in crystal means taking the count of # Rptd Stores based on other field grp01 within the same dataset.

    Thanks,

    Surya

    it looks like below to me

    IIf(Count(Fields!Rptd_Stores.Value,"grp01") > 0, 
    sum(Fields!SurveyPrice.Value,"grp01")/IIF(Count(Fields!Rptd_Stores.Value,"grp01") > 0, Count(Fields!Rptd_Stores.Value,"grp01"),1),0)


    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

    Tuesday, December 25, 2018 5:41 PM
  • Hi Surya RS

    So what the detailed logic inside , from @grp01 to @# Rptd Stores and @grp01  to CR_Surveys_PID.SurveyPrice.

    You could offer the detailed logic about your expression step by step and with the data sample.

    That would help a lot.

    Hoping for your reply.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if 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.

    Wednesday, December 26, 2018 2:15 AM