Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Report Builder 3.0 Expression count


Report Builder 3.0 Expression count

Author
Message
kellie.jenkins
kellie.jenkins
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 21
I have got a dataset which is pulling monitoring counter values for logical drives, ram and cpu for multiple machines. From this I have created a table which groups the counters by machine and then gives the max, min and average for each counter instance per machine and then highlights any over 80%

What I now need to do is perform a count of how many machines have a counter which is over 80% and I am going around in circles trying to figure this out. I need something along the lines of: for each machine (if counter instance has avg value > 80, True) Count true
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16467 Visits: 13207
What is your data source?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
kellie.jenkins
kellie.jenkins
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 21
Its an sql database
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16467 Visits: 13207
This is something you can easily add in the source query with a CASE statement.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
kellie.jenkins
kellie.jenkins
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 21
I have got:

SELECT
viewReportMonitorCounterLog.agentGuid
,viewReportMonitorCounterLog.ComputerName
,viewReportMonitorCounterLog.GroupName
,viewReportMonitorCounterLog.[Counter Object]
,viewReportMonitorCounterLog.Counter
,viewReportMonitorCounterLog.[Counter Instance]
,"Critical Utilisation" = CASE
WHEN viewReportMonitorCounterLog.[Value] >= 80 THEN 1
WHEN viewReportMonitorCounterLog.[Value] < 80 THEN 0
END
,viewReportMonitorCounterLog.[Value]
,viewReportMonitorCounterLog.[Time(UTC)]
FROM
viewReportMonitorCounterLog

Should it possible in the case statement to use AVG(viewReportMonitorCounterLog.[Value]) >= 80 THEN 1 etc? I set that and then I was getting the following error:
TITLE: Microsoft SQL Server Report Builder
------------------------------

An error occurred while executing the query.
Column 'viewReportMonitorCounterLog.agentGuid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16467 Visits: 13207
If you want to calculate aggregates in your query, you need to use a GROUP BY clause.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
kellie.jenkins
kellie.jenkins
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 21
Where would the Group By statement go?
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16467 Visits: 13207
I was rereading your original question, and I need some more info.
You said first you would like to have a count of how many machines have a value over 80%. (What is a machine btw, is it viewReportMonitorCounterLog.ComputerName?)

Buy you also want to calculate the average. Over all the machines? Or over viewReportMonitorCounterLog.[Counter Instance]? Does [Counter Instance] have multiple rows in the data set?

Maybe you can give some sample data and your desired output.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
kellie.jenkins
kellie.jenkins
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 21
Yes a machine is viewReportMonitorCounterLog.ComputerName.

The avg needs to be calculated per counter instance per machine - ie avg of c drive values for machine 1, avg of c drive values for machine 2 etc and if any counter instance average is over 80 on one machine that machine is then counted, if no counter instances average above 80, this machine is ignored.

For example:
Machine Counter Instance Avg Value
Server 1 c: 65.27
Server 1 E: 84.64
Server 2 C: 37.27
Server 2 D: 57.00
Server 2 E: 55.61
Server 3 C: 20.00
Server 3 E: 46.89

I'm trying to get an output which is just 1 row in a table:
'Number of Machines Exceeding Critical Utilisation Values' '1'
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16467 Visits: 13207
Sorry for the late reply, I have been busy.

Here is how I would solve it using TSQL:

-- create temp table
CREATE TABLE #viewReportMonitorCounterLog
([Machine] VARCHAR(10) NOT NULL
,[Counter Instance] VARCHAR(10) NOT NULL
,[Counter Object] VARCHAR(10) NOT NULL
,[Value] NUMERIC(4,2) NOT NULL
);

-- populate temp table with sample values
INSERT INTO #viewReportMonitorCounterLog([Machine],[Counter Instance],[Counter Object],[Value])
VALUES ('Server 1','C:','A',70.00)
,('Server 1','C:','B',61.87)
,('Server 1','E:','A',84.64)
,('Server 2','C:','A',37.27)
,('Server 2','D:','A',57.00)
,('Server 2','E:','A',55.61)
,('Server 3','C:','A',20.00)
,('Server 3','E:','A',46.89);

--SELECT * FROM #viewReportMonitorCounterLog;

-- calculate average values per counter instance
SELECT
[Machine]
,[Counter Instance]
,[Avg Value] = AVG([Value])
FROM #viewReportMonitorCounterLog
GROUP BY [Machine], [Counter Instance];

-- calculate critical utilisation
WITH cte_AvgValue AS
(
SELECT
[Machine]
,[Counter Instance]
,[Avg Value] = AVG([Value])
FROM #viewReportMonitorCounterLog
GROUP BY [Machine], [Counter Instance]
) -- same select as before
, cte_Criticals AS
(
SELECT [Machine], MaxValue = MAX([Avg Value]) -- get the highest average value for the current machine
FROM cte_AvgValue
GROUP BY [Machine]
HAVING MAX([Avg Value]) >= 80.00 -- only keep the machines that are critical
)
SELECT [Number of Machines Exceeding Critical Utilisation Values] = COUNT(*)
FROM cte_Criticals;

DROP TABLE #viewReportMonitorCounterLog;





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search