March 22, 2018 at 8:33 am
I'm linking the dataset1 with dataset2 using lookup function.
Then I want to pass a condition that if country type is E get the fields about 'E' country types else get the field of 'S' country types.
Following is my formula inside the matrix of ssrs;
=IIF(Lookup(Fields!GROUP_ID.Value, Fields!Group_Id.Value, Fields!Country_Type.Value, "Dataset2"), ="E", Fields!country_E.Value, Fields!country_S.Value)
The above formula is giving me error "Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope."
When I run my lookup without the iif statement, I get no errors.
Kindly please let me know what I'm doing wrong.
June 20, 2018 at 2:21 pm
Just an opinion on this...for me, this is getting a bit complex. I wonder if there is a simpler way to accomplish what you are needing. Have you thought about passing more work to the database engine? Maybe you could pass a parameter value to a stored procedure, write your logic in T-SQL within the procedure and then pass the results back to the report. That would simplify the report.
July 11, 2018 at 11:53 am
soldout6000 - Thursday, March 22, 2018 8:33 AMI'm linking the dataset1 with dataset2 using lookup function.
Then I want to pass a condition that if country type is E get the fields about 'E' country types else get the field of 'S' country types.
Following is my formula inside the matrix of ssrs;
=IIF(Lookup(Fields!GROUP_ID.Value, Fields!Group_Id.Value, Fields!Country_Type.Value, "Dataset2"), ="E", Fields!country_E.Value, Fields!country_S.Value)
The above formula is giving me error "Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope."
When I run my lookup without the iif statement, I get no errors.
Kindly please let me know what I'm doing wrong.
Absolutely no question that this kind of lookup should be handled in the primary data set. JOIN the needed table appropriately and let the SQL perform the lookup for you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply