SSRS Chart for States (Newbie)

• dustinprevatt

  Old Hand

  Points: 383

  More actions

  August 7, 2012 at 7:47 am

  #259718

  I'm trying to create a chart for which products I have in different states. I'm really pretty new to SQL and SSRS so hopefully someone can put me on the right path.

  Here is my query.

  SELECT State.StateAbbr, Station.StationName

  FROM Address INNER JOIN

  Station ON Address.AddressKey = Station.AddressKey INNER JOIN

  State ON Address.StateOrProvince = State.StateID

  Below are some sample results.

  AL3083

  ALNucrewo

  ALEWTRIS

  ALtest2

  ALtest3

  ALtest4

  ALtest5

  ALtest6

  ALtest7

  ALtest8

  ALtest9

  ALtest10

  ALtest11

  ALNB1

  FL3007

  FL314

  FL3085S1

  But what I need is the count of products there is per state. I'm just not exactly sure how to get the right query to put the data in the chart.

  For example;

  I have this query which will return the total count for florida. But how do I insert that into my chart?

  SELECT COUNT(*) AS Total_Florida

  FROM Address INNER JOIN

  Station ON Address.AddressKey = Station.AddressKey INNER JOIN

  State ON Address.StateOrProvince = State.StateID

  where State.StateAbbr = 'FL'

• Spiff

  SSCertifiable

  Points: 7826

  More actions

  August 7, 2012 at 7:49 am

  #1522604

  Look at using a group by:

  SELECT State.StateAbbr, COUNT(*) AS [Total]

  FROM Address INNER JOIN

  Station ON Address.AddressKey = Station.AddressKey INNER JOIN

  State ON Address.StateOrProvince = State.StateID

  GROUP BY State.StateAbbr