none
SSRS Expression to compare two values and get data from another dataset

    Question

  • Hi,

    I have a requirement where in i need to compare a value of a dataset (not the one which table is poiting to)

    with the value of a text box, and return the count if it matches else 0, the expression is as below. Here 

    industryName and recCount are from the dataset ds_MedimOrLowImpactEntities.  whats wrong in this expression


    =Sum(IIF(Fields!industryName.Value = reportItems!hpapra_name.Value, Fields!RecCount.Value, 0), "ds_MediumOrLowImpactEntities")

    This is the error that i am getting:

    

    Any help on this is appreciated


    Kruthi Hegde

    Friday, May 09, 2014 6:05 AM

Answers

  • Hi Kruthi,

    After testing the issue in my local environment, I can reproduce it. Just the error message said, “Aggregate function can be used only on report items contained in page headers and footers”. So the sum function that contains report items should be stayed at page headers or footers. Besides, you said the hpapra_name textbox contain a field from another dataset, not the ds_MedimOrLowImpactEntities dataset. While in Reporting Services, Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope. So consider the two issues, we should use some other expression to instead the “reportItems!hpapra_name.Value” expression.

    Take a look at the "Looking up values from another dataset" topic here:
    http://msdn.microsoft.com/en-us/library/ms159673.aspx#LookupFunctions

    Hope this helps.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Monday, May 12, 2014 8:23 AM

All replies

  • What does the textbox contain? An expression or constant value?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, May 09, 2014 6:52 AM
  • it contains a value from a dataset..

    Kruthi Hegde

    Friday, May 09, 2014 11:22 AM
  • Hi Kruthi,

    After testing the issue in my local environment, I can reproduce it. Just the error message said, “Aggregate function can be used only on report items contained in page headers and footers”. So the sum function that contains report items should be stayed at page headers or footers. Besides, you said the hpapra_name textbox contain a field from another dataset, not the ds_MedimOrLowImpactEntities dataset. While in Reporting Services, Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope. So consider the two issues, we should use some other expression to instead the “reportItems!hpapra_name.Value” expression.

    Take a look at the "Looking up values from another dataset" topic here:
    http://msdn.microsoft.com/en-us/library/ms159673.aspx#LookupFunctions

    Hope this helps.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Monday, May 12, 2014 8:23 AM