SSRS Help - Sort Chart Results by Month

Author
badk4life
Starting Member
  • Total Posts : 20
  • Scores: 0
  • Reward points: 1100
  • Joined: 2017/02/10 09:09:02
  • Status: offline
2017/03/02 15:06:18 (permalink)
0

SSRS Help - Sort Chart Results by Month

This might be an easy one for the SSRS experts. 
 
I have a simple chart that is returning the results of my monthly patch deploys. Problem is if I sort it alphabetically it shorts by the name of the month and that's not what I'm looking for. What I need is to sort by date Example: November 2016, December 2016, January 2017, February 2017, March 2017, etc.... left to right. That would be easy if I could setup an expression to sort it by Month according to a calendar year and the field was an actual date and not a variable loaded into the chart. But its getting the data from the SCCM collectionid name. See screen shot below. Sorry if my lingo is a bit off I'm new to SSRS.
 
Below is the Query for DataSet1. As you can see it pulls in the collectionid which is named "Patch Tuesday - August 2016" or "Patch Tuesday - January 2017" etc. I'm guessing the "order by 1" at the very end is my sort option?
 
select
count(*) [Total Clients], li.title,coll.name,
SUM (CASE WHEN ucs.status=3 or ucs.status=1  then 1 ELSE 0 END ) as 'Installed / Not Applicable',
sum( case When ucs.status=2 Then 1 ELSE 0 END ) as 'Required',
sum( case When ucs.status=0 Then 1 ELSE 0 END ) as 'Unknown',
round((CAST(SUM (CASE WHEN ucs.status=3 or ucs.status=1 THEN 1 ELSE 0 END) as float)/count(*) )*100,2) as 'Success %', round((CAST(count(case when ucs.status not in('3','1') THEN '*' end) as float)/count(*))*100,2) as 'Not Success%'
From v_Update_ComplianceStatusAll UCS
inner join v_r_system sys on ucs.resourceid=sys.resourceid
inner join v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
inner join v_collection coll on coll.collectionid=fcm.collectionid
inner join v_AuthListInfo LI on ucs.ci_id=li.ci_id
where coll.CollectionID=@collname
and LI.CI_UniqueID in (@AuthListID)
group by li.title,coll.name
order by 1
 
See this screenshot for what I'm talking about:

https //i.imgsafe.org/887fec587b.jpg - add a semicolon after https [/style]
 
Please let me know if you need any more info to help solve this puzzle. Any help is appreciated.  [/style]
 
Thanks in advance, 
Andy K 
[/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style][/style]
post edited by badk4life - 2017/03/02 15:14:39
#1

19 Replies Related Threads

    gjones
    Expert Member
    • Total Posts : 2816
    • Scores: 146
    • Reward points: 248130
    • Joined: 2001/06/05 12:32:00
    • Location: Ottawa, Ontario, Canada
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/02 16:21:25 (permalink)
    0
    I will make a suggestions.. Before I can even look at the question I need to clean all the style stuff... as it just just too hard to read it as is.
     
    Add/Use the SU List DateCreated field as you sort order. this will sort everything by when the SU List was created, effectively give you what you want.
    #2
    badk4life
    Starting Member
    • Total Posts : 20
    • Scores: 0
    • Reward points: 1100
    • Joined: 2017/02/10 09:09:02
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/02 16:23:20 (permalink)
    0
    I'll give that a try. Thank you very much for your help!!!
     
    Andy K
    #3
    gjones
    Expert Member
    • Total Posts : 2816
    • Scores: 146
    • Reward points: 248130
    • Joined: 2001/06/05 12:32:00
    • Location: Ottawa, Ontario, Canada
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/02 16:23:48 (permalink)
    0
    BTW, I love how much effort you are putting into your dashboards / reports.
    #4
    badk4life
    Starting Member
    • Total Posts : 20
    • Scores: 0
    • Reward points: 1100
    • Joined: 2017/02/10 09:09:02
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/03 08:56:27 (permalink)
    0
    -First of all sorry for the style junk in my orig post. First time using this forum and I'm not used to it. I won't try and use the red text color anymore  
    -Yeah I started off not liking SSRS and these custom reports much but the more I learn the more I really enjoy it. I'm trying to make a really nice custom report for management to bring to the CIO and board etc. So I want it to look nice.
    -Last question is I've never tried to "Add/Use the SU List DateCreated field as you sort order". Do you know of any documentation that can start me off in the right direction? I google it and SU comes up as the Linux command mostly. Or can you give me any more detail as to how I start?
     
    Again thanks for the help I very happy to see someone takes time out of their busy day to help someone else. Its rare these days and makes me realize I need to help others with the knowledge I have.
     
    Thanks,
    Andy K
    #5
    badk4life
    Starting Member
    • Total Posts : 20
    • Scores: 0
    • Reward points: 1100
    • Joined: 2017/02/10 09:09:02
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/20 10:05:32 (permalink)
    0
    I've done a little more research but I still cannot figure out exactly what you mean when you say "Add/Use the SU List DateCreated field as you sort order. this will sort everything by when the SU List was created, effectively give you what you want." Is there any more info you can give me on this issue?
     
    Thanks in Advance!
    Andy K
    #6
    gjones
    Expert Member
    • Total Posts : 2816
    • Scores: 146
    • Reward points: 248130
    • Joined: 2001/06/05 12:32:00
    • Location: Ottawa, Ontario, Canada
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/20 10:17:53 (permalink)
    0
    Sorry I missed your last post, I can tell you exact where to look or I can point you in the right direction.
     
    For now point you in the right direction, I will tell you to look at all the columns within each of the SQL views listed within your query above, which one give you the DateCreated for a SU list?
    #7
    badk4life
    Starting Member
    • Total Posts : 20
    • Scores: 0
    • Reward points: 1100
    • Joined: 2017/02/10 09:09:02
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/20 10:35:30 (permalink)
    0
    I don't see "DateCreated" anywhere in my dataset. Do I need to add a query so that shows up in my dataset so I can then add that to my chart as the sort field? Again I really appreciate the help!!!!
     

     
    Andy K
    #8
    badk4life
    Starting Member
    • Total Posts : 20
    • Scores: 0
    • Reward points: 1100
    • Joined: 2017/02/10 09:09:02
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/20 10:45:46 (permalink)
    0
    If I try and add a new field called DateCreated to the Dataset1 via expression I get lost. I'm not sure how to add that since it doesn't show up in the list. Do you know how to add that? My guess is I need to add a SQL query to my main dataset to pull that info and add it as a dataset field correct? Then I can use that to be my sort order for my chart? Right? Again I'm new to this so my lingo might be way off 
     

    #9
    gjones
    Expert Member
    • Total Posts : 2816
    • Scores: 146
    • Reward points: 248130
    • Joined: 2001/06/05 12:32:00
    • Location: Ottawa, Ontario, Canada
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/20 11:55:10 (permalink)
    0
    Ok, stop looking at SSDT. Use SSMS to edit your query. In SSMS, you can look at each SQL view that you already have within the query and see all the columns you can add to the query.
    #10
    badk4life
    Starting Member
    • Total Posts : 20
    • Scores: 0
    • Reward points: 1100
    • Joined: 2017/02/10 09:09:02
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/20 14:55:56 (permalink)
    0
    I'll be honest I don't know what the difference is or how to switch. Again I'm a newbie. Any other explanation on how I can "Add/Use the SU List DateCreated field as you sort order. this will sort everything by when the SU List was created, effectively give you what you want."
     
    Just looking to sort this one chart by date and not the name of the collectionID.
     
    Thanks,
    Andy K
    #11
    badk4life
    Starting Member
    • Total Posts : 20
    • Scores: 0
    • Reward points: 1100
    • Joined: 2017/02/10 09:09:02
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/20 15:04:47 (permalink)
    0
    As you can see below the sort by only lists 7 options and none of them are DateCreated. You know anyway to add that to the list?
     

     
    Thanks,
    Andy K
    #12
    gjones
    Expert Member
    • Total Posts : 2816
    • Scores: 146
    • Reward points: 248130
    • Joined: 2001/06/05 12:32:00
    • Location: Ottawa, Ontario, Canada
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/20 15:32:16 (permalink)
    0

    select
     count(*) [Total Clients], li.title,coll.name,
     SUM (CASE WHEN ucs.status=3 or ucs.status=1  then 1 ELSE 0 END ) as 'Installed / Not Applicable',
     sum( case When ucs.status=2 Then 1 ELSE 0 END ) as 'Required',
     sum( case When ucs.status=0 Then 1 ELSE 0 END ) as 'Unknown',
     round((CAST(SUM (CASE WHEN ucs.status=3 or ucs.status=1 THEN 1 ELSE 0 END) as float)/count(*) )*100,2) as 'Success %', round((CAST(count(case when ucs.status not in('3','1') THEN '*' end) as float)/count(*))*100,2) as 'Not Success%',
     LI.DateCreated as 'Datecreated'
    From
     dbo.v_Update_ComplianceStatusAll UCS
     inner join v_r_system sys on ucs.resourceid=sys.resourceid
     inner join v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
     inner join v_collection coll on coll.collectionid=fcm.collectionid
     inner join v_AuthListInfo LI on ucs.ci_id=li.ci_id
    where
     coll.CollectionID = @collname
     and LI.CI_UniqueID in (@AuthListID)
    group by
     li.title,coll.name
    order by
     LI.DateCreated

     
    BTW Are you based on Ottawa?
    #13
    badk4life
    Starting Member
    • Total Posts : 20
    • Scores: 0
    • Reward points: 1100
    • Joined: 2017/02/10 09:09:02
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/20 15:48:10 (permalink)
    0
    Thanks! So I took the query you gave me and replaced my Dataset1 query completely with it. I clicked ok and got this window:

     
    I didn't do anything I just clicked OK and got this error:

     
    Then I tried to save it and got this error:

     
    Any ideas what I'm doing wrong? Her is the full query now and another error:

     
    Thanks,
    Andy K
     
     
     
    Andy K
    #14
    badk4life
    Starting Member
    • Total Posts : 20
    • Scores: 0
    • Reward points: 1100
    • Joined: 2017/02/10 09:09:02
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/20 15:55:08 (permalink)
    0
    Oh and no I'm not based in Ottawa. Was it my accent [:D We have an office there as well as 27 other locations around the world. I'm based in Shakopee, MN at our HQ just south of the twin cites.
     
    If I add just your two lines to my original query I get this error:

     
     
    #15
    gjones
    Expert Member
    • Total Posts : 2816
    • Scores: 146
    • Reward points: 248130
    • Joined: 2001/06/05 12:32:00
    • Location: Ottawa, Ontario, Canada
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/20 17:18:06 (permalink)
    0
    Sorry I didn't test the query, I edited it by hand.
     
    Add "LI.DateCreated" to the group by section of the query and all will be good.
     
    I asked because I thought our main office was close to my house, in the west end of Ottawa.
     
    BTW, I'm coming to Minneapolis for https://mmsmoa.com in May.
     
    #16
    badk4life
    Starting Member
    • Total Posts : 20
    • Scores: 0
    • Reward points: 1100
    • Joined: 2017/02/10 09:09:02
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/21 11:00:46 (permalink)
    0
    Good news and bad news. 
     
    Bad news I used the query below and nothing changed:
     
    select  count(*) [Total Clients], li.title,coll.name,  SUM (CASE WHEN ucs.status=3 or ucs.status=1  then 1 ELSE 0 END ) as 'Installed / Not Applicable',  sum( case When ucs.status=2 Then 1 ELSE 0 END ) as 'Required',  sum( case When ucs.status=0 Then 1 ELSE 0 END ) as 'Unknown',  round((CAST(SUM (CASE WHEN ucs.status=3 or ucs.status=1 THEN 1 ELSE 0 END) as float)/count(*) )*100,2) as 'Success %', round((CAST(count(case when ucs.status not in('3','1') THEN '*' end) as float)/count(*))*100,2) as 'Not Success%',  LI.DateCreated as 'Datecreated'From  dbo.v_Update_ComplianceStatusAll UCS  inner join v_r_system sys on ucs.resourceid=sys.resourceid  inner join v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid  inner join v_collection coll on coll.collectionid=fcm.collectionid  inner join v_AuthListInfo LI on ucs.ci_id=li.ci_id where  coll.CollectionID = @collname  and LI.CI_UniqueID in (@AuthListID) group by  li.title,coll.name,LI.DateCreatedorder by  LI.DateCreated
     
    Then I modified the CategoryGroups to the new DateCreate field. Good news is that kind of works. 

     
    I can now sort the updates by when they were deployed. Issue is it now shows the data aka date it was deployed, and NOT the actual Software Update Group name. Do you know if there is anyway I can still sort by this date field but still show the update group name\title?
     
    As you can see it worked, kinda.

     
    That conf looks pretty cool. I didn't even know it existed and I've lived in MN most of my life. I've been to EMC world in Vegas about 4 times but that's about for me and conferences. If you want to have a local show you the town or take you to dinner while your up here let me know. I'd be happy to show you around or take you to dinner whatever. I hope the weather is nice while your up here. Weather is pretty important up here. Can be really nice or make ya wanna stay indoors. 
     
    Thanks again for all your help!!! Its pretty rare plp or strangers help out and get nothing in return. So its noticed and appreciated. 
     
    Thanks,
    Andy K
     
    #17
    badk4life
    Starting Member
    • Total Posts : 20
    • Scores: 0
    • Reward points: 1100
    • Joined: 2017/02/10 09:09:02
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/21 11:07:56 (permalink)
    0
    Or even better. Now that I think about it whats the easiest way to just change date output format from mm/dd/YYY H:MM:SS TT to say "March 2017" aka MM/YYYY instead? I don't need the actual name of the software update group as that just reflects the month\year anyway. I'll take a look and see if I can use some type of expression to change the format. If you know please tell me.
     

     
    Thanks,
    Andy K
    #18
    badk4life
    Starting Member
    • Total Posts : 20
    • Scores: 0
    • Reward points: 1100
    • Joined: 2017/02/10 09:09:02
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/21 11:24:15 (permalink)
    0
    I figured it out myself using this expression:
     
    =Format(Fields!Datecreated.Value,”MMM-dd-yyyy”)
     
    It now looks like this and its PERFECT! Thanks again for all the help! I'm not bluffing on my offer. If you want I'll take you to dinner and show you the town while your up here for the conf. Just PM me if you wanna take me up on my offer. Again I appreciate the help as I learn more about SSRS etc. 
     
    End Result!

     
    Thanks,
    Andy K
    #19
    badk4life
    Starting Member
    • Total Posts : 20
    • Scores: 0
    • Reward points: 1100
    • Joined: 2017/02/10 09:09:02
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month 2017/03/22 09:11:51 (permalink)
    0
    I cannot reply to you via PM due to the forum restrictions  I've joined the MNSCUG and I've emailed them to see if they are having a meet and greet. I'll let you know what I hear from them.
     
    Thanks,
    Andy K
    #20
    Jump to:
    © 2018 APG vNext Commercial Version 5.5