none
How can we add new field in the column contain Expression in different fields?

    Question

  • Hi,

    Below are sample table data in ssrs report,

    ValueFor               1Hrs    2Hrs    3Hrs

    Total Contacts      128    101     79
    Sales VALUE            0      0        0
    Number of Sales       0      0        0
    Conversion %          0      0        0
    Abandons               8     10        4

    Above,I need to add "Contacts Handled"  filed under the "ValueFor" Column,its values depend on the same column in the different fields?

    Contacts Handled=Total Contacts+Abandons            

    My Result is , 

    ValueFor                      1Hrs    2Hrs    3Hrs

    Total Contacts           128    101     79
    Sales VALUE                 0      0        0
    Number of Sales            0      0        0
    Conversion %               0      0        0
    Abandons                    8     10        4

    Contacts Handled    136    111       83

    How can i able to write Expression in 1Hrs  columns?

    please give any suggestions i need its urgent requirement.

    please help me anyone?


    Wednesday, November 13, 2013 6:00 AM

Answers

  • Hi Samba,

    Suppose you have a dataset as same data as Uri Dimant posted above. Please refer to the steps below:
    1. Add a table in the report body.
    2. With the same structure as your post above to add fields in the table.
    3. Right click (Details) in the Row Groups dialog box.
    4. Click Add Total. We can get the sum value in the table.

    If we just need to sum “Total Contacts”, “Abandons” values, we can use below expression:
    =Sum(IIF(Fields!ValueFor.Value="Total Contacts" or Fields!ValueFor.Value="Abandons",Fields!ID1Hrs.Value,0))

    If there are any misunderstanding, could you please post your dataset with sample data? It is benefit for us to do further analysis.

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Thursday, November 14, 2013 5:14 AM
    Moderator

All replies

  • hi 

    you can use matrix or table 

    end of table or matrix you can use (Sum)

    • Proposed as answer by Rupi.k Wednesday, November 13, 2013 6:28 AM
    Wednesday, November 13, 2013 6:22 AM
  • Is that possible to do that via T-SQL?

    create table #t (ValueFor varchar(20),[1Hrs] int, [2Hrs] int ,[3Hrs] int)
    insert into #t values
    ('Total Contacts',128,101,79),
    ('Sales VALUE ',0,0,0),
    ('Number of Sales',0,0,0),
    ('Conversion %',0,0,0),
    ('Abandons',8,10,4)

    select ValueFor ,[1Hrs] , [2Hrs]  ,[3Hrs] from #t
    union all
    select top 1 'Contacts Handled',
       sum([1Hrs]) over () s1h ,sum([2Hrs]) over () s2h, sum([3Hrs])over () s3h 
    from #t


    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

    Wednesday, November 13, 2013 7:29 AM
  • Hi Uri Dimant,

    Can you tell me How to do in SSRS Level,

    we dont have permission to change the SP in my backend.

    please any one give me suggestions?

    Wednesday, November 13, 2013 9:05 AM
  • Hi Samba,

    Suppose you have a dataset as same data as Uri Dimant posted above. Please refer to the steps below:
    1. Add a table in the report body.
    2. With the same structure as your post above to add fields in the table.
    3. Right click (Details) in the Row Groups dialog box.
    4. Click Add Total. We can get the sum value in the table.

    If we just need to sum “Total Contacts”, “Abandons” values, we can use below expression:
    =Sum(IIF(Fields!ValueFor.Value="Total Contacts" or Fields!ValueFor.Value="Abandons",Fields!ID1Hrs.Value,0))

    If there are any misunderstanding, could you please post your dataset with sample data? It is benefit for us to do further analysis.

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    Thursday, November 14, 2013 5:14 AM
    Moderator