How to create 2 conditions from 2 datasets in row visibility SSRS

Hi Experts,

 In ssrs, I want to create an expression for the row visibility. But, the expression will contain 2 conditions from different 2 datasets (DealStarts & RowofTrendingVisibility). I have found a solution online, but got an error message is
 "The Visibility.Hidden expression for the tablix ‘Tablix9’ contains an error: [BC30451] Name 'launchdate' is not declared. "
I think that there is a minor issue in the syntax. Can some one help me to correct it?  Thank you.

=iif ((Last(MonthName("DealStarts"))=monthname(month(today())) or launchdate ("RowofTrendingVisibility")<Parameters!StartDate.Value), true, false)
tanj1035Asked:
Who is Participating?
 
Nico BontenbalCommented:
I'm sorry but this is not a minor issue. The first problem is with the MonthName("DealStarts") part. It you add a text field to your table and use this expression for the field:
=MonthName("DealStarts")

Open in new window

You'll see that it returns an error. This is because you are using " which means DealStarts is just a piece of text. Try it by using:
="DealStarts"

Open in new window

as an expression for a text field. It will not return the value of the field "DealStarts". If you place a text field inside a table or tablix you can refer to the fields in the dataset that is linked to that table or tablix. But you do that like this:
=Fields!DealStarts.Value

Open in new window

If you put that in the (test) text field you should get the value of the field. But the function MonthName wants a number as input, not a date. Thats why you use monthname(month(today())) and not monthname(today()). So if DealStarts is a date then you also need to use:
=MonthName(Month(Fields!DealStarts.Value))

Open in new window

Then you can try something like:
=iif((Last(MonthName(Fields!DealStarts.Value))=monthname(month(today()))), true, false)

Open in new window

Put it in the test textbox first so you can check if the expression returns the right result.

And then you also want to include a value from a different dataset in the expression. I suppose thats the value of RowofTrendingVisibility of the 'launchdate' dataset. You can't refer to that field like you tried. That syntax is for calling a function. So SSRS now thinks launchdate is a function, which it of course isn't.
You can refer to fields in another database by using Lookup, or by using an aggregate function with the scope. If the dataset only has one row, or if the value is the same on all the rows you can use First to retrieve the value. The expression:
=First(Fields!RowofTrendingVisibility .Value, "launchdate")

Open in new window

Returns the first value of the field RowofTrendingVisibility in the dataset launchdate.
Try this expression also in the text textbox. If everything returns the right result you can combine all the parts into one expression and text that in the textbox:
=iif ((Last(MonthName(month(Fields!DealStarts.Value)))=monthname(month(today())) or First(Fields!RowofTrendingVisibility .Value, "launchdate")<Parameters!StartDate.Value), true, false)

Open in new window

If that works you can put the expression in the hidden property for the row.
0
 
tanj1035Author Commented:
It works!!! Thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.