1

I've recently started using SCCM in our Company and I'm now on to building reports through Report Builder. Currently I'm trying to do 2 things, I am trying to build a pie chart that shows the number of files that exists on a drive and a category groups of "older than five years" "Newer than five years". So far the best I can do is list the files and individual years. So it currently shows up like this:

Pie Chart

enter image description here

The Group Expression I'm using to make the category group show up as years is:

=Year(Fields!FileModifiedDate.Value)

I'm not real sure how I would modify that to show up as two categories "anything older than five years" and "anything newer than five years"

My next chart is a bar graph that would be a breakdown of the pie chart. I want to create a chart that shows the different file types and how many of them there are. I need help with this part, but can try to figure it out after I get the pie chart working.

Thanks,

J

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
Jack9
  • 27
  • 1
  • 3

1 Answers1

0

Try using:

=IIF(
Datediff(DateInterval.Year,Fields!FileModifiedDate.Value,Today)>5,
"anything older than five years",
"anything newer than five years")

If any file is exactly five years old it will be include in the anything newer than five years category, if you want to change that behaviour use >= instead.

Hope it helps.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • That really helps. Thank you for that. Any ideas on the bar graph situation? I basically want to make a graph that will show files like "%.DOCX, %.PST, %.XLSX, etc." I know it must have something to do with editing the line "SF.FileName" and making it group those files together and listing them simply as the extension, I just don't know how it should look like, if it should be in group expression or filter? So it looks like the X axis will show the file types and the Y axis will show number of files. – Jack9 Feb 23 '17 at 18:42
  • @Jack9, you are welcome. About the bar chart you require there is no much I can tell you without knowing your dataset and your expected result. If my answer solved your issue you can [mark it as the correct answer](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) – alejandro zuleta Feb 23 '17 at 18:46
  • thank you for your help. Here's my code with my dataset. It's not a very big script, so I don't have too much to look through _Select Distinct SYS.Netbios_Name0, SYS.Resource_Domain_OR_Workgr0, SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath from fn_rbac_GS_SoftwareFile(@UserSIDs) SF LEFT JOIN fn_rbac_R_System(@UserSIDs) SYS on SF.ResourceID = SYS.ResourceID WHERE SYS.Netbios_Name0 Like @variable AND SF.FilePath Like 'E:\Home-Folders\%' Order by SF.FileName_ – Jack9 Feb 23 '17 at 23:22
  • @Jack9, At a glance what I can see is that you might want to use group by for grouping your data by extension then use COUNT() in the Values pane of the bar chart. You should create an additional question detailing your expected result and posting your query there. Good luck! – alejandro zuleta Feb 24 '17 at 00:05