locked
Reports to get the value RRS feed

  • Question

  • I am getting the below data from sharepoint list.

    Section Title Salesvalue sysb
    A Sales-A 1 21
    Sales-Total 1 1
    B Average 3 1
    C MPRStatus 21 1

    Requirement:The Value of MPRStatus should be Sales-Total/MPRStatus (i.e) in the sample it should be 1/21 how to right expresion for the requirement.

    Monday, November 14, 2011 9:06 AM

Answers

  • Hi swathit544,

    In your case, I think that we can try to add calculated fields to achieve this requirement. For example, we can add two calculated fields into your dataset by using the following expression:
    Fields Name               Field Source
    SalesTotal                  =IIF(Fields!Section.Value=”SectionA”, Fields!AOP.Value,Nothing)
    RMSP                         =IIF(Fields!Section.Value=”SectionH”,Fields!AOP.Value,Nothing)

    Then, we can use the following expression to calculate the value:
    =Sum(Fields!Sales-Total.Value)/Sum(Fields!RMSP.Value)

    For more information about calculated field, please see:
    http://technet.microsoft.com/en-us/library/ms345330.aspx

    Thanks,
    Bin Long

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Elvis Long Tuesday, November 22, 2011 7:46 AM
    Friday, November 18, 2011 8:29 AM

All replies

  • CREATE TABLE #t (section CHAR(1),Title VARCHAR(20),val INT)
    INSERT INTO #t VALUES ('A','Sales-Total',1) 
    INSERT INTO #t VALUES ('B','Average',1) 
    INSERT INTO #t VALUES ('C','MPRStatus',21) 
    SELECT * ,CASE WHEN Title='Sales-Total' THEN val*1./(SELECT val FROM #t WHERE Title='MPRStatus')
     END FROM #t

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, November 14, 2011 9:26 AM
  • CREATE TABLE #t (section CHAR(1),Title VARCHAR(20),val INT)
    INSERT INTO #t VALUES ('A','Sales-Total',1) 
    INSERT INTO #t VALUES ('B','Average',1) 
    INSERT INTO #t VALUES ('C','MPRStatus',21) 
    SELECT * ,CASE WHEN Title='Sales-Total' THEN val*1./(SELECT val FROM #t WHERE Title='MPRStatus')
     END FROM #t

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, November 14, 2011 9:26 AM
  •  

    Please find the report in the excel i have already the report looks like below the only field or expression i should get is

    "Sales - Total/RM SP %" 

     

    waiting for the response

     

        Ytd Pr Month Week1
    Section1 Particulars Act AOP Est
    Section A Sales A 0 0 125.7
    Sales - B 0 0 889
    Sales - PS 0 0 0
    Sales E  0 0 565
    Sales - Be  0 0 0
    Sales - LE 53.4 34.5 0.7
    Sales - Total 53.4 34.5 1015.5
    Section H RM SP % 48 83 1
    Section J Cash Generation From Operations 38.9 -0.9 0
    Monday, November 14, 2011 9:36 AM
  • Hi swathit544,

    In your case, I think that we can try to add calculated fields to achieve this requirement. For example, we can add two calculated fields into your dataset by using the following expression:
    Fields Name               Field Source
    SalesTotal                  =IIF(Fields!Section.Value=”SectionA”, Fields!AOP.Value,Nothing)
    RMSP                         =IIF(Fields!Section.Value=”SectionH”,Fields!AOP.Value,Nothing)

    Then, we can use the following expression to calculate the value:
    =Sum(Fields!Sales-Total.Value)/Sum(Fields!RMSP.Value)

    For more information about calculated field, please see:
    http://technet.microsoft.com/en-us/library/ms345330.aspx

    Thanks,
    Bin Long

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Elvis Long Tuesday, November 22, 2011 7:46 AM
    Friday, November 18, 2011 8:29 AM