SSRS Sparkline category with Lookup

Question

Hi,

I have a table in which I have to use SSRS Sparklines. But the requirement is that, I have to lookup the Y axis values and Category groups from another dataset, using a unique identifier in both the datasets. Lets say DS1 is the parent dataset and DS2 has got the Y values and Category (12 months) for the Sparkline.

I cannot use DS2 as Default dataset for the table, because it will result in writing lookup expressions for around 20-25 cells other than Sparkline cell.

So I used the below expressions for Sparkline.

Value axis : 

=lookupset(Fields!RowID.Value,Fields!RowID.Value,Fields!Trend.Value,"DS2")

Category axis : 

=lookupset(Fields!RowID.Value,Fields!RowID.Value,Fields!MonthName.Value,"DS2")

I used lookup set, because I believe we have to map a set of values to the Sparkline chart.  But it throws me the below error.



Any suggestions to solve the error? Is it possible to configure a Sparkline without Category axis? I asked because I can manage my chart if SParkline can plot the trend values alone, without a category axis also.

Answer 1

Hi,

I have a table in which I have to use SSRS Sparklines. But the requirement is that, I have to lookup the Y axis values and Category groups from another dataset, using a unique identifier in both the datasets. Lets say DS1 is the parent dataset and DS2 has got the Y values and Category (12 months) for the Sparkline.

I cannot use DS2 as Default dataset for the table, because it will result in writing lookup expressions for around 20-25 cells other than Sparkline cell.

So I used the below expressions for Sparkline.

Value axis : 

=lookupset(Fields!RowID.Value,Fields!RowID.Value,Fields!Trend.Value,"DS2")

Category axis : 

=lookupset(Fields!RowID.Value,Fields!RowID.Value,Fields!MonthName.Value,"DS2")

I used lookup set, because I believe we have to map a set of values to the Sparkline chart.  But it throws me the below error.



Any suggestions to solve the error? Is it possible to configure a Sparkline without Category axis? I asked because I can manage my chart if SParkline can plot the trend values alone, without a category axis also.

Answer 2

Hi Ghees,

According to your description, you are using LookUpSet function in your SQL Server Reporting Services report without success, right?

In SSRS, lookupset function returns the set of matching values for the specified name from a dataset that contains name/value pairs. So we cannot use the results of this function directly. We need to use Join function to create a list to display the results of LookUpSet function. Here is a sample expression for you reference.
=Join(LookupSet(Fields!TerritoryGroupID.Value, Fields!ID.Value, Fields!StoreName.Value, "Stores"),",")

https://msdn.microsoft.com/en-IN/library/ee240819.aspx?f=255&MSPPError=-2147217396

If you have any questions, please feel free to ask.

Regards,

---

Charlie Liao
TechNet Community Support

Answer 3

Many Thanks for the suggestion, Charlie.

But the chart goes null when I do join like you mentioned above :(

Answer 4

You can avoid null error by using Join Keyword and Vbcrlf funcation as below.

=Join(LookupSet(Fields!TerritoryGroupID.Value, Fields!ID.Value, Fields!StoreName.Value, "Stores"),"," + vbcrlf).

But the problem now is, All the storenames(in this case) are displaying at one place(point) in x-axis(Category Axis). 

Appreciate any help to get the storenames individually rather than at 1 point on Category axis?