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.
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.