HI i had a requirement where i need to compare two values and change the color of the marker ,one dataset has like 15 rows and other has 1 row , what i need to do is compare the value of 1 row dataset with other dataset and have to change it , the expression i am using now is like this iif(Fields!Time.Value=First(Fields!chart_endtime.Value, "dset_lowcosttrade"),"Black","#00ffffff") but i am having problem here it is only taking the first row value, but my value will be some wehre in th nth row , so is there any way i can compare the endtime value(single row datasetvalue) with time (dataset with 15 rows), is there nay way to acieve it ,please let me know thanks in advance .

one way is to determine the nth row in a separate dataset, theres a number of ways to do this. Here is an example of one way to return the "3rd" row: select col from ( select col, row_number() over(order by col) as col_num from (select 'a' col union select 'b' union select 'c' union select 'd' union select 'e') test_table)stage where col_num = 3

Hi Raju, Try this in place of your present expression: =IIf ( LookUpSet ( Fields!Time.Value, First (Fields!Chart_EndTime.Value, "dset_lowcosttrade"), Fields!Time.Value, "DataSet1" ).Length > 0, "Black", "#00ffffff" ) What we are doing here: We check if for our single record in the DSet_LowCostTrade dataset there exists 1 or more values in the other dataset (I have used DataSet1 in my above expression, replace with the name of your dataset). THe LookUpSet function returns an array, so we do a check if the length of the array is more than 0. Based on this, you can do the conditional coloring. HTH. Please mark correct answers :)

Hi, A suggestion is to change your dataset query such that the Nth row should be with the first row or Last row always in the dataset. So that you can use First or Last functions in SSRS. Hope its helpful....Pavan Kokkula Tata Consultancy Services.

i cannot do that since the data i am getting is based on the dates

Hi Raju, Try this in place of your present expression: =IIf ( LookUpSet ( Fields!Time.Value, First (Fields!Chart_EndTime.Value, "dset_lowcosttrade"), Fields!Time.Value, "DataSet1" ).Length > 0, "Black", "#00ffffff" ) What we are doing here: We check if for our single record in the DSet_LowCostTrade dataset there exists 1 or more values in the other dataset (I have used DataSet1 in my above expression, replace with the name of your dataset). THe LookUpSet function returns an array, so we do a check if the length of the array is more than 0. Based on this, you can do the conditional coloring. HTH. Please mark correct answers :) in the look up set the reuslt should be in the same scope or diffrent scope?

Hi Raju, Try this in place of your present expression: =IIf ( LookUpSet ( Fields!Time.Value, First (Fields!Chart_EndTime.Value, "dset_lowcosttrade"), Fields!Time.Value, "DataSet1" ).Length > 0, "Black", "#00ffffff" ) What we are doing here: We check if for our single record in the DSet_LowCostTrade dataset there exists 1 or more values in the other dataset (I have used DataSet1 in my above expression, replace with the name of your dataset). THe LookUpSet function returns an array, so we do a check if the length of the array is more than 0. Based on this, you can do the conditional coloring. HTH. Please mark correct answers : Tried it but no luck returning #Error, any idea why it is Happenning like that

Hi Raju, Try this in place of your present expression: =IIf ( LookUpSet ( Fields!Time.Value, First (Fields!Chart_EndTime.Value, "dset_lowcosttrade"), Fields!Time.Value, "DataSet1" ).Length > 0, "Black", "#00ffffff" ) What we are doing here: We check if for our single record in the DSet_LowCostTrade dataset there exists 1 or more values in the other dataset (I have used DataSet1 in my above expression, replace with the name of your dataset). THe LookUpSet function returns an array, so we do a check if the length of the array is more than 0. Based on this, you can do the conditional coloring. HTH. Please mark correct answers : Tried it but no luck returning #Error, any idea why it is Happenning like that Try this expression: =IIf ( LookUpSet ( First (Fields!Chart_EndTime.Value, "dset_lowcosttrade"), Fields!Time.Value, Fields!Time.Value, "MANY RECORDS DATASET NAME HERE" ).Length > 0, "Black", "#00ffffff" ) I did not try the previous expression. It was written on the fly. I am quite sure the above one is going to work fine. Just ensure that you have the field names and the dataset names right. The DataSet names are case-sensitive too. Let me know if this does not work for you. HTH. Please mark correct answers :)

Hi Raju, Try this in place of your present expression: =IIf ( LookUpSet ( Fields!Time.Value, First (Fields!Chart_EndTime.Value, "dset_lowcosttrade"), Fields!Time.Value, "DataSet1" ).Length > 0, "Black", "#00ffffff" ) What we are doing here: We check if for our single record in the DSet_LowCostTrade dataset there exists 1 or more values in the other dataset (I have used DataSet1 in my above expression, replace with the name of your dataset). THe LookUpSet function returns an array, so we do a check if the length of the array is more than 0. Based on this, you can do the conditional coloring. HTH. Please mark correct answers : Tried it but no luck returning #Error, any idea why it is Happenning like that Try this expression: =IIf ( LookUpSet ( First (Fields!Chart_EndTime.Value, "dset_lowcosttrade"), Fields!Time.Value, Fields!Time.Value, "MANY RECORDS DATASET NAME HERE" ).Length > 0, "Black", "#00ffffff" ) I did not try the previous expression. It was written on the fly. I am quite sure the above one is going to work fine. Just ensure that you have the field names and the dataset names right. The DataSet names are case-sensitive too. Let me know if this does not work for you. HTH. Please mark correct answers :) i think it is not going to help me out because the one with many rows is in the current dataset and one with one row is not in current scope so how can i use it i tried but it is throwing me an exception saying that there shouldnt be any aggregate function in lookupset function so any suggestions , please let me know if you need more details on the issue

The LookUpSet function is designed to work with one dataset in current scope and a second dataset thats out of scope. Can you post your exact expression, the one thats erroring out for you? Please mark correct answers :)

The LookUpSet function is designed to work with one dataset in current scope and a second dataset thats out of scope. Can you post your exact expression, the one thats erroring out for you? Please mark correct answers :) lookoutset(Fields!tradetime.value,Fields!end_time.value,Fields!price.value,"dset_ltrade") the first one Fields!tradetime.value is the one with lot of values and it is used as a category group in chart the second one is Fields!end_time.value is from second dataset dset_ltrade which is a table the third one Fields!price.value is the value i need which is also in second dataset dset_ltrade what i am trying to do here is if the tradetime and endtime is equal i am showing the marker on chart if not hide the marker that is what actually happening there