CODEWITHSUNDEEP
sqlsql-serverreporting-services
M.JAY
M.JAY

Reputation: 193

Divide in the same table

I try to show from my 16 machines the highest machine which produced the most waste. And after a lot of efforts I´ve done this.

Now I must do the top 5 of highest problems. And these Problems are listed in the database in the same directory´:

How can I do this?

I use SQL Server Report Builder.

I cannot add a picture I will try to do it so:

Date                        CounterName                  calculationUnitsInitial     
    2016-04-26 00:00:00.000 Prod                     6221   
    2016-04-26 00:00:00.000 Bad                          0  
    2016-04-26 00:00:00.000 ba           0  
    2016-04-26 00:00:00.000 ba       0  
    2016-04-26 00:00:00.000 Ausg     6    
    2016-04-26 00:00:00.000 Au           0  
    2016-04-26 00:00:00.000 Bad                          125    
    2016-04-26 00:00:00.000 Aus                          8  
    2016-04-26 00:00:00.000 Band position                0  
    2016-04-26 00:00:00.000 Fe               0  
    2016-04-26 00:00:00.000 Fe               0
    2016-04-26 00:00:00.000 Hu                       124    
    2016-04-26 00:00:00.000 S                     0 
    2016-04-26 00:00:00.000 Dr               0
    2016-04-26 00:00:00.000 H            4

Upvotes: 2

Views: 294

Answers (1)

Chris Latta
Chris Latta

Reputation: 20560

So we need a list of the problems in descending order of frequency for the worst operating machine. I'd set up your really big query as a dataset and use that to populate the default value of a hidden parameter called Machine. We will also need two parameters DateFrom and DateTo with their default values set to the date range you are interested in.

Then your problem selection is easy:

SELECT CounterName, COUNT(*) AS Problems
FROM Problems
WHERE Machine = @Machine 
  AND ProblemDate >= @DateFrom AND ProblemDate <= @DateTo
GROUP BY CounterName
ORDER BY COUNT(*) DESC

Now we have a nice list of the number of each type of problem for that machine in descending order of occurrence of problems. Let's call this dataset Problems

Add a table to your report with two columns. The first column will have the expression:

=Fields!Problems.Value / SUM(Fields!Problems.Value, "Problems")

This divides the number of problems for that CounterName by the entire number of problems in the dataset Problems. Set the Format property of that cell to P1 to display as a percentage with one decimal place.

The second column is simply the field CounterName.

Upvotes: 1

Related Questions