Sparkline in SSRS 2008R2/BIDS with fixed axis

Question

I'm having some difficulty getting a sparkline chart to display correctly and the Microsoft documentation is focused on Report Builder.

I have a collection of items that can be placed in one of a number of bins. I'm trying to show the frequency of occurrence for each item/bin number combination. While I could sum the items by bin number in SQL, I'm letting SSRS do it for now.

For the purposes of explanation, assume the dataset looks something like this:

Item,Bin
-----------
A,1
A,2
A,5
A,2
B,1
B,5

I've got the item bit down -- the table containing the sparkline has a row group that groups on the appropriate variable (Item). For the horizontal axis, I've selected the bin # as the property being graphed. I'm not sure what to use to get the vertical axis though.

Answer 1

Do you have the value set for the sparkline? It's not clear from your explanation if you have done this step. If not, just click the sparkline to display the Chart Date pane. Click the plus sign in the Values section and then select Expression. It sounds like you want to count the number of items by bin so you use this expression:

=Count(Fields!Item.Value)

Because you have sequential bins, you might consider setting the Horizontal Axis properties to a Scalar axis type so that you get the right sequencing and "fill in the blanks" if a bin number is missing for a particular item (such as 3 and 4 for Item A in your example). Also set the Align Axes property to your tablix so that all bin numbers line up in your sparklines in each row. Otherwise you would get 1 and 2 for Item A above 1 and 5 for Item B. With the Align Axes properties the 5 for A and the 5 for B would line up correctly.