March 16, 2012 at 7:12 am
Hi All,
I'm trying to join two Datasets, so I would be able to retrive the correct Data depending on if the criteria is met; for example:
=Lookup(First(Fields!countrycode.Value, "DataSet1"), First(Fields!countryCode.Value, "DataSet2"),
(First(Fields!sourceCode.Value, "DataSet2")
but I keep getting the following error messages:
[rsExpressionMissingCloseParen] There is a syntax error in the Value expression for the textrun ‘Textbox52.Paragraphs[0].TextRuns[0]’: ‘)’ expected.
Or is there another function I can use to get the Data out please?
Thank you!
March 16, 2012 at 7:49 am
the expression isnt complete, its missing 2 closing brackets
this is just my personal preference but i like to format brackets like this so I can see how many ( and ) I have
=Lookup(
First(
Fields!countrycode.Value, "DataSet1"
),
First(
Fields!countryCode.Value, "DataSet2"
),
First(
Fields!sourceCode.Value, "DataSet2"
)
)
March 16, 2012 at 9:21 am
I changed the query to what you've suggested, but it now gives the following error messages:
[rsWrongNumberOfParameters] The Value expression for the textrun ‘Textbox52.Paragraphs[0].TextRuns[0]’ has an incorrect number of parameters for the function ‘Lookup’.
[rsAggregateInLookupDestinationOrResult] The Value expression for the textrun 'Textbox52.Paragraphs[0].TextRuns[0]' contains an aggregate function in an argument to a Lookup or LookupSet function. Aggregate functions cannot be used for the destination or result Expression parameter of a lookup function.
[rsMissingDataSetName] The tablix 'Tablix3' is invalid. The value for the DataSetName property is missing.
Thank you!
March 16, 2012 at 9:28 am
1st error, lookup requires 4 parameters, if you used my above expression it only passes in 3.
2nd error, you have a extra function like MIN/MAX/AVG/SUM etc somewhere else which is stopping the lookup from happening as lookups cannot be done with aggregate functions
3rd error, tablix 3 hasnt been provided with a data set
March 16, 2012 at 10:04 am
anthony.green (3/16/2012)
1st error, lookup requires 4 parameters, if you used my above expression it only passes in 3.2nd error, you have a extra function like MIN/MAX/AVG/SUM etc somewhere else which is stopping the lookup from happening as lookups cannot be done with aggregate functions
3rd error, tablix 3 hasnt been provided with a data set
Thank you again for your reply!
In one of the error messages you mentioned that functions such as SUM cannot be used when we want to use Lookup functions, but unfortunately I need to use them in my stored procedure. Are there any other functions which can perform the same jobs please?
I really appreciate you help!
March 19, 2012 at 2:40 am
unsure on that, what I would ask you do is post the query in question, along with data DDL and the rdl file, and detail what the results should look like and we can take a look
March 19, 2012 at 4:41 am
anthony.green (3/19/2012)
unsure on that, what I would ask you do is post the query in question, along with data DDL and the rdl file, and detail what the results should look like and we can take a look
The qyery which I'm trying to run is:
=Lookup(Fields!countrycode.Value,Fields!Countrycode2.Value, Fields!sourceCode.Value, "DataSet2")
The Datasets are as follows:
Dataset1:
countryname
countrycode
count
Dataset2:
Countrycode2
sourcecode
PerceTotal
The result should look like:
SourceCode (This is the column which needs to have the Lookup code)
CountOfCountriesPerSourceCode
Percentage
Please let me know if you need further details.
Thank you again!
March 19, 2012 at 4:50 am
Looking at it your going to have to do this in the SQL batch and then pass it into the report already aggregated, I cant see a way to do it at the report level.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply