Search code examples
reporting-servicesssrs-2008ssrs-2012

How to take % value for Line graph for series Groups ?/How to plot graph by sql table?

Approach 1:How to take % value for Line graph for series Groups?

I am setting up a ssrs report like with x axis(Category Group) as Finished Week, QualityPercent( as Series groups-RFT%,REwork%,Scrap%) and Values as Sum of Quantity. enter image description here

In the above graph the quatities are shown in percentages based upon weeks(the actual result whose plot values are given at left side of image). Respective tables structure:
enter image description here But I am getting the chart like this
enter image description here

Here the y axis is not plotting well asper the category values, sometimes shoots upto 250%!! or 1400% !!! (this is embarrassing).

For the above graph i used expression as: IIF(Sum(Fields!QTY.Value,"Chart11_SeriesGroup"),Sum(Fields!QTY.Value)/Sum(Fields!QTY.Value, "DataSet_Production"),0) What i am missing? I even used #Percent. Kindly help me.

Approach 2: How to plot the ssrs graph using below result from sql query?

FinishedWeek    QualityPercent  QTY   Percentage
1                 Rework (%)    844   0.109724
1                 RFT (%)       6811  0.885465
1                 Scrap (%)      37   0.004810
2                 Rework (%)    742   0.094618
2                 RFT (%)       7096  0.904871
2                 Scrap (%)      4    0.000510
Solution

  • After much work done with second approach, I wrote the separate query for the above table asper the link: Calculating percentage within a group

    select t1.FinishedWeek,t1.QualityPercent,Sum(QTY) as QTY,Sum(QTY)/ t2.TOTAL_QTY  as Percentage   from @temp 
AS t1
  JOIN (
    select FinishedWeek,Sum(QTY) as TOTAL_QTY   from @temp
    group by FinishedWeek
  ) AS t2
  ON t1.FinishedWeek= t2.FinishedWeek
 group by t1.FinishedWeek,QualityPercent,t2.TOTAL_QTY

    From above query , took the Finished Week as Category, QTY as Values, and QualityPercent as Series Groups