0

I have a table setup with the following columns:

Product Name
SalesMonth1
SalesMonth2
SalesMonth3
SalesMonth4
SalesMonth5

An example of a row is as follows:

Bread
300
600
800
900
1000

I am trying to put this into a line graph in SSRS but am having trouble figuring out which fields go where. In my dataset, I have a field for each "SalesMonth" column. So in my dataset, I have 6 fields total including the product name. Will this work? On which axis should the product name go and the sales fields go?

Ian Preston
  • 38,816
  • 8
  • 95
  • 92
Jeff
  • 427
  • 1
  • 14
  • 31

1 Answers1

1

I hope your dataset uses SQL.

I would "unpivot" the data by writing a 5-part UNION SELECT, e.g.

SELECT Product_Name , 1 AS Month , SalesMonth1 AS Sales UNION ALL SELECT Product_Name , 2 AS Month , SalesMonth2 AS Sales UNION ALL ... SELECT Product_Name , 5 AS Month , SalesMonth5 AS Sales

Then in the SSRS Chart definition:

Month = Category Group

Product Name = Series Group

Sales = Values

But the real real answer is that your data is probably not in a useful shape for reporting. Will there only ever be 5 months? I suspect not...

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • It actually does only keep records for sales for the first 5 months of a products launch, thats all we care about. Im going to try this today. Also, they will be able to select multiple products when running the report (we do use sql). Is this going to change anything? – Jeff Apr 17 '14 at 12:53
  • Just wrap the UNIONs in a derived table e.g. `SELECT * FROM ([union selects]) AS D1 WHERE Product_Name IN ( @Product_Name )` – Mike Honey Apr 18 '14 at 03:08