February 17, 2010 at 9:07 am
I am trying to create a pie chart in SSRS 2008 to use with the following table:
IF OBJECT_ID(N'tempdb..#DriveInfo') IS NULL
BEGIN
CREATE TABLE #DriveInfo(
[pkDriveRecordID] [int] NOT NULL,
[fkServerID] [int] NOT NULL,
[DriveLetter] [char](1) NOT NULL,
[CapacityMB] [bigint] NOT NULL,
[UsedSpaceMB] [bigint] NOT NULL,
[FreeSpaceMB] [bigint] NOT NULL,
[FreeSpacePercent] [tinyint] NOT NULL,
[ClusterSizeKB] [smallint] NULL,
[FileFragmentationPercent] [tinyint] NULL,
[TotalFragmentationPercent] [tinyint] NULL,
[FreeSpaceFragmentationPercent] [tinyint] NULL,
[FileFragmentCount] [bigint] NULL,
[ExcessFolderFragmentCount] [bigint] NULL,
[EntryDateTime] [datetime] NOT NULL
)
END
GO
INSERT INTO #DriveInfo
SELECT 1, 3, N'C', 14324, 11126, 3198, 22, 4096, 43, 22, 1, 63988, 2150, CAST(0x00009D1401149A41 AS DateTime)
UNION
SELECT 2, 4, N'C', 14324, 11652, 2672, 19, 4096, 48, 25, 2, 56794, 1798, CAST(0x00009D1401149A41 AS DateTime)
UNION
SELECT 3, 7, N'C', 20466, 8901, 11565, 57, 4096, 38, 19, 0, 48119, 1762, CAST(0x00009D1401149A41 AS DateTime)
UNION
SELECT 4, 9, N'C', 14331, 11785, 2546, 18, 4096, 46, 23, 0, 39201, 2441, CAST(0x00009D1401149A41 AS DateTime)
UNION
SELECT 5, 10, N'C', 30726, 17078, 13648, 44, 4096, 10, 5, 0, 25927, 613, CAST(0x00009D1401149A41 AS DateTime)
UNION
SELECT 6, 10, N'E', 40947, 30770, 10177, 25, 4096, 74, 37, 0, 158, 3, CAST(0x00009D1401149A41 AS DateTime)
UNION
SELECT 7, 11, N'C', 122880, 81963, 40917, 33, 4096, 0, 0, 0, 0, 0, CAST(0x00009D1401149A41 AS DateTime)
UNION
SELECT 8, 12, N'C', 40963, 18677, 22285, 54, 4096, 49, 24, 0, 122265, 2040, CAST(0x00009D1401149A41 AS DateTime)
UNION
SELECT 9, 12, N'E', 30726, 18011, 12715, 41, 4096, 31, 15, 0, 522, 162, CAST(0x00009D1401149A41 AS DateTime)
UNION
SELECT 10, 13, N'C', 14324, 10972, 3352, 23, 4096, 43, 22, 1, 65933, 2094, CAST(0x00009D1401149A41 AS DateTime)
I am simply trying to take 1 row from the above table and create a pie chart showing free space and used space for it but am struggling to do so.
Am I right in assuming that what I need to do this is to use a dataset query returning a table in the following format (Example assumes I am trying to generate a pie chart for pkDriveRecordID = 1 from the above table)
IF OBJECT_ID(N'tempdb..#DriveSpace') IS NULL
BEGIN
Create Table #DriveSpace(
Category varchar(20),
SizeInMB bigint
)
END
GO
INSERT INTO #DriveSpace
SELECT 'FreeSpace', 3198
UNION
SELECT 'UsedSpace', 11126
If this is the case, can you please give me advice on how to transform my table into the desired format?
Many thanks,
Paul.
March 11, 2010 at 10:57 am
Quick and dirty stored procedure psuedocode:
scan
Insert into Pietable
"freespace" as category,
FreeSpaceMB as drivespace,
endscan
scan
Insert into Pietable
"UsedSpace" as category,
UsedSpaceMB as drivespace,
endscan
I haven't done much T-SQL work in a while, but that gives you the bones. Even if you're dealing with a massive datacenter with thousands of drives, it's improbable that the clunkiness of the above will make any meaningful difference in performance (does it really matter if the procedure takes 700 milliseconds vs 200? I doubt it). Therefore, simply making two passes through will get you past this and on to something else.
Grace and peace,
BD
March 15, 2010 at 9:17 am
That's pretty much what I ended up doing 🙂
Thanks for your reply.
Paul.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply