0

So Here is my dilemma, I have a sql table named Column_Names that has a column that displays names (field names in an application) and another column that displays sort_order.

I then have a view named A_to_B that I created that has all the values from the application, along with sort_order (which was a left outer join in my view).

My issue is this, in SSRS I need to display ALL of the names from the table Column_Names in the specified order (which I can do) and I need to display all the values from the view A_to_B in that specific sort order (which I can do). But the issue is that the view doesn't have all of the column names that are in the column_name table it only displays values that have been selected in the application. I need to be able to join both "tables" in SSRS somehow to display all the column names and the values if exist, if value doesn't exist, I need to show as blank.

Is this something I can achieve through an expression? Obviously not with a lookup seeing as I don't want to join the two, I just want to display both results but also be able to sort them with one sort, so preferably if this can be done in one tablix that would be ideal unless there is another way that would be better.

Any help is greatly appreciated, I've been turning my head on this for a while.

ItalianStallion4215
  • 139
  • 2
  • 11

1 Answers1

1

The Lookup Function should work here. You could create a matrix for the dataset with your Column_Names. Then add a textbox whose expression looks up the appropriate value from the other dataset using the name.

So if the other dataset was called "DataSetValue", and you wanted to include the appropriate "Value" field next to each column you'd use:

=Lookup(Fields!ColumnName.Value, Fields!ColumnName.Value, Fields!Value.Value, "DataSetValue")

That'll find the row with the matching ColumnName and give you the Value.

bitnine
  • 1,752
  • 1
  • 17
  • 20
  • Yes, I know. that is not the outcome I want though. I already have the corresponding values. Basically say Table A has a column with name1, name2, name3 And View B has a column with name2. I want to be able to merge the two basically. say that name1 and name3 from table A have nulls as the values but name2 has a value associated which is pulled from the view B. – ItalianStallion4215 Aug 12 '16 at 16:58
  • In that case, would it work for you if you have the View B as your source, and then use a function like Join(LookupSet(),",") to look up all the names associated with that column from Table A? That would gave a list of all the names alongside the value. – bitnine Aug 12 '16 at 17:34
  • Not familiar with using Join, I assume this would be in an expression? If so, it won't let me do a join with lookupset, it gives an error – ItalianStallion4215 Aug 12 '16 at 18:08