I want to create report monthly view for volume capacity of past 6 months

maruthappan

I have created report for past 6 months of Volume capacity, getting the report result view as all the months in same row, but I want to project the months in every column.

Getting Report as...

Display Name	Volume Name	Volume Percent Available	Used space	Available space	Timestamp
xxxx1	C:	92.6 %	18.83 GB	236.34 GB	Jun-17
xxxx1		27.3 %	178.88 GB	67.19 GB	Jun-17
xxxx2	C:	30.7 %	170.59 GB	75.49 GB	Jun-17
xxxx2		30.3 %	171.52 GB	74.55 GB	Jun-17
xxxx1	C:	92.6 %	18.83 GB	236.34 GB	May-17
xxxx1		27.3 %	178.88 GB	67.19 GB	May-17
xxxx2	C:	30.7 %	170.59 GB	75.49 GB	May-17
xxxx2		30.3 %	171.52 GB	74.55 GB	May-17
xxxx1	C:	92.6 %	18.83 GB	236.34 GB	Apr-17
xxxx1		27.3 %	178.88 GB	67.19 GB	Apr-17
xxxx2	C:	30.7 %	170.59 GB	75.49 GB	Apr-17
xxxx2		30.3 %	171.52 GB	74.55 GB	Apr-17

Looking report as.. Can some one please help me.

Server	Drive	April	May	June
xxxx1	C:	89.57	93.59	93.70
xxxx1		99.74	99.92	99.91
xxxx2	C:	21.03	17.51	16.31
xxxx2		9.79	8.83	8.07

mesverrum

SQL is not very efficient at tweaking the presentations but this particular one is doable, depending on your tables might take a longer time to execute.

For those monthly aggregate values, are you using averages or peak values?

Try this query to get averages

Select v.node.caption as [Node], v.Caption as [Volume], round(v.Size/1073741824,1) as [Size (GB)] , tostring(m0.[AVG])+ ' %' as [This Month Used], tostring(m1.[AVG])+ ' %' as [Previous Month Used], tostring(m2.[AVG])+ ' %' as [2m Ago Used]from orion.Volumes vleft join (SELECT NodeID, VolumeID, (tostring(year(datetime)) + ' - '+ tostring(month(DateTime))) as [yyyy-mm], round(avg(PercentDiskUsed),0) as [AVG], max(percentdiskused) as [Peak]FROM Orion.VolumeUsageHistorywhere monthdiff(datetime,getdate())=0group by NodeID, VolumeID, (tostring(year(datetime)) + ' - '+ tostring(month(DateTime)))) m0 on m0.nodeid=v.NodeID and m0.volumeid=v.VolumeIDleft join (SELECT NodeID, VolumeID, (tostring(year(datetime)) + ' - '+ tostring(month(DateTime))) as [yyyy-mm], round(avg(PercentDiskUsed),0) as [AVG], max(percentdiskused) as [Peak]FROM Orion.VolumeUsageHistorywhere monthdiff(datetime,getdate())=1group by NodeID, VolumeID, (tostring(year(datetime)) + ' - '+ tostring(month(DateTime)))) m1 on m1.nodeid=v.NodeID and m1.volumeid=v.VolumeIDleft join (SELECT NodeID, VolumeID, (tostring(year(datetime)) + ' - '+ tostring(month(DateTime))) as [yyyy-mm], round(avg(PercentDiskUsed),0) as [AVG], max(percentdiskused) as [Peak]FROM Orion.VolumeUsageHistorywhere monthdiff(datetime,getdate())=2group by NodeID, VolumeID, (tostring(year(datetime)) + ' - '+ tostring(month(DateTime)))) m2 on m2.nodeid=v.NodeID and m2.volumeid=v.VolumeID