SSRS 2012 Set a chart series legend from another dataset based upon the column name

0
votes

I'm back with a question about the feedback card application.

For my final chart, I have a stored procedure with a dynamically built pivot query that has columns named Col1, Col2, Col3, ... Col 30, CardDate and FormattedCardDate. The columns either have data for a specific card feedback type (up to 30), as selected on the report setup form.

I also have another dataset with the display titles for the selected feedback types with the following columns Feedback ID, DisplayTitle and ColName.

I have tried to use a lookup for the legend like:

=Lookup(Fields!Col1.UniqueName, Fields!ColName.Value, Fields!DisplayTitle.Value, "dsGetLabels")

With the following data:

ID     DisplayTitle         ColName
================================================
1      Room Cleaned         Col1
4      Kitchenette Counter  Col2
...

But it only dislpays Col1 for Col1, not "Room Cleaned" as in the column name dataset.

How do I make the lookup work? Barring that, how can I add the correct column names to my stored procedure, but still access the data?

As an aside, I can pass the correct column names to the SSRS Chart as a parameter, but how would I display them as a legend?

1
sql-serverreporting-servicescharts
Would it be possible to join the pivoted data on the types table in your dataset stored procedure so you end up with one dataset? – Ross Bush
That is one of the solutions I'm thinking of. I can add a column for each series column with the correct legend title. It just makes for a big dataset with a lot of repeated information. – user6373040
If it is just hundreds/thousands of rows then you should see no difference between adding four extra fields to your main dataset, however, your type dataset solution is just as valid and would be ideal for super huge datasets. – Ross Bush
The dataset will be several hundred rows at most. I added 30 columns with the names and pointed the legend to use =Fields!ColNameN.Value for each of the potential 30 data columns and it works fine. Thanks. If you repost as a solution, I'll accept it. – user6373040
Glad I could help. – Ross Bush

1 Answers

0
votes

If your main dataset is returning only a few hundred or a few thousand rows then you should see no difference between adding four extra fields to your main dataset, however, your type dataset solution is just as valid and would be ideal for super huge datasets.