locked
Scope Parameter with LookUp Error RRS feed

  • Question

  • Hi,

    I am using SSRS 2012 and I intend to create a column chart combined with a line chart.

    Column Chart is dependent on T1 Dataset

    Line Chart is dependent on T2 Dataset 

    Here is the sample data.  I know that I can achieve that by joining these 2 datasets but my requirement is to have 2 separate datasets ( as my actual datasets have some separate report level parameter filters so I can't combine)

    DECLARE @T1 TABLE  ( [Yr] int, StudentCount INT) 
    
    INSERT INTO @T1 VALUES
    (2013,55 ),
    (2014,77),
    (2015,87),
    (2016,98),
    (2017,69),
    (2018,88),
    (2019,79)
    
    
    
    SELECT * FROM @T1
    
    
    
    
    DECLARE @T2 TABLE  ( [Yr] int, Failed INT) 
    
    INSERT INTO @T2 VALUES
    (2013,12 ),
    (2014,20),
    (2015,27),
    (2016,15),
    (2017,31),
    (2018,10),
    (2019,25)
    
    
    SELECT * FROM @T2

    Now I am looking for a report where the Column chart shows ' Total Number of Students per year"

    Line on the bars should show ' Total Number of Failed Students per year'

    As I can't combine the datasets, I used the approach of  adding an expression to the chart and used the lookup to get the value from T2 (dataset2) Similar to the approach in this Example but I want to keep my datasets separate

    =LookUp((Fields!Yr.Value, "Chart1_CategoryGroup"),Fields!Yr.Value,Fields!Failed.Value,"Dataset2")

    However , I am getting the following error 

    The Y expression for the chart ‘Chart1’ has a scope parameter that is not valid for a lookup function. The scope parameter must be set to a string constant that is the name of a dataset.

    Please advise what's wrong in my lookup expression as I tried to use Dataset1 in place of "Chart1_CategoryGroup" too

    Thanks in advance


    • Edited by John_tay Thursday, August 6, 2020 11:37 PM
    Thursday, August 6, 2020 11:35 PM

Answers

  • Hi John,

    If I understand you requirement correctly, in the lookup function "Chart1_CategoryGroup" , we should not input this scope.

    Try :

    =LookUp(Fields!Yr.Value,Fields!Yr.Value,Fields!Failed.Value,"DataSet2")

    See if this is what you need : 

    Best Regards,

    Joy


    ""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 Joy_Zhao Friday, August 7, 2020 3:16 AM
    • Marked as answer by John_tay Friday, August 7, 2020 3:19 AM
    Friday, August 7, 2020 3:16 AM

All replies

  • Hi John,

    If I understand you requirement correctly, in the lookup function "Chart1_CategoryGroup" , we should not input this scope.

    Try :

    =LookUp(Fields!Yr.Value,Fields!Yr.Value,Fields!Failed.Value,"DataSet2")

    See if this is what you need : 

    Best Regards,

    Joy


    ""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 Joy_Zhao Friday, August 7, 2020 3:16 AM
    • Marked as answer by John_tay Friday, August 7, 2020 3:19 AM
    Friday, August 7, 2020 3:16 AM
  • Thanks
    Friday, August 7, 2020 3:19 AM