myITforum and Windows IT Pro Forums

 SSRS Help - Sort Chart Results by Month

Author Message
badk4life

  • Total Posts : 20
  • Scores: 0
  • Reward points : 1100
  • Joined: 2/10/2017
  • Status: offline
SSRS Help - Sort Chart Results by Month Thursday, March 02, 2017 4:06 PM (permalink)
0
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]
<message edited by badk4life on Thursday, March 02, 2017 4:14 PM>
 
#1
    gjones

    • Total Posts : 2764
    • Scores: 147
    • Reward points : 215290
    • Joined: 6/5/2001
    • Location: Ottawa, Ontario, Canada
    • Status: offline
    Re:SSRS Help - Sort Chart Results by Month Thursday, March 02, 2017 5:21 PM (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

      • Total Posts : 20
      • Scores: 0
      • Reward points : 1100
      • Joined: 2/10/2017
      • Status: offline
      Re:SSRS Help - Sort Chart Results by Month Thursday, March 02, 2017 5:23 PM (permalink)
      0
      I'll give that a try. Thank you very much for your help!!!
       
      Andy K
       
      #3
        gjones

        • Total Posts : 2764
        • Scores: 147
        • Reward points : 215290
        • Joined: 6/5/2001
        • Location: Ottawa, Ontario, Canada
        • Status: offline
        Re:SSRS Help - Sort Chart Results by Month Thursday, March 02, 2017 5:23 PM (permalink)
        0
        BTW, I love how much effort you are putting into your dashboards / reports.
         
        #4
          badk4life

          • Total Posts : 20
          • Scores: 0
          • Reward points : 1100
          • Joined: 2/10/2017
          • Status: offline
          Re:SSRS Help - Sort Chart Results by Month Friday, March 03, 2017 9:56 AM (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

            • Total Posts : 20
            • Scores: 0
            • Reward points : 1100
            • Joined: 2/10/2017
            • Status: offline
            Re:SSRS Help - Sort Chart Results by Month Monday, March 20, 2017 11:05 AM (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

              • Total Posts : 2764
              • Scores: 147
              • Reward points : 215290
              • Joined: 6/5/2001
              • Location: Ottawa, Ontario, Canada
              • Status: offline
              Re:SSRS Help - Sort Chart Results by Month Monday, March 20, 2017 11:17 AM (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

                • Total Posts : 20
                • Scores: 0
                • Reward points : 1100
                • Joined: 2/10/2017
                • Status: offline
                Re:SSRS Help - Sort Chart Results by Month Monday, March 20, 2017 11:35 AM (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

                  • Total Posts : 20
                  • Scores: 0
                  • Reward points : 1100
                  • Joined: 2/10/2017
                  • Status: offline
                  Re:SSRS Help - Sort Chart Results by Month Monday, March 20, 2017 11:45 AM (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

                    • Total Posts : 2764
                    • Scores: 147
                    • Reward points : 215290
                    • Joined: 6/5/2001
                    • Location: Ottawa, Ontario, Canada
                    • Status: offline
                    Re:SSRS Help - Sort Chart Results by Month Monday, March 20, 2017 12:55 PM (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

                      • Total Posts : 20
                      • Scores: 0
                      • Reward points : 1100
                      • Joined: 2/10/2017
                      • Status: offline
                      Re:SSRS Help - Sort Chart Results by Month Monday, March 20, 2017 3:55 PM (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

                        • Total Posts : 20
                        • Scores: 0
                        • Reward points : 1100
                        • Joined: 2/10/2017
                        • Status: offline
                        Re:SSRS Help - Sort Chart Results by Month Monday, March 20, 2017 4:04 PM (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

                          • Total Posts : 2764
                          • Scores: 147
                          • Reward points : 215290
                          • Joined: 6/5/2001
                          • Location: Ottawa, Ontario, Canada
                          • Status: offline
                          Re:SSRS Help - Sort Chart Results by Month Monday, March 20, 2017 4:32 PM (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

                            • Total Posts : 20
                            • Scores: 0
                            • Reward points : 1100
                            • Joined: 2/10/2017
                            • Status: offline
                            Re:SSRS Help - Sort Chart Results by Month Monday, March 20, 2017 4:48 PM (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

                              • Total Posts : 20
                              • Scores: 0
                              • Reward points : 1100
                              • Joined: 2/10/2017
                              • Status: offline
                              Re:SSRS Help - Sort Chart Results by Month Monday, March 20, 2017 4:55 PM (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

                                • Total Posts : 2764
                                • Scores: 147
                                • Reward points : 215290
                                • Joined: 6/5/2001
                                • Location: Ottawa, Ontario, Canada
                                • Status: offline
                                Re:SSRS Help - Sort Chart Results by Month Monday, March 20, 2017 6:18 PM (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

                                  • Total Posts : 20
                                  • Scores: 0
                                  • Reward points : 1100
                                  • Joined: 2/10/2017
                                  • Status: offline
                                  Re:SSRS Help - Sort Chart Results by Month Tuesday, March 21, 2017 12:00 PM (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

                                    • Total Posts : 20
                                    • Scores: 0
                                    • Reward points : 1100
                                    • Joined: 2/10/2017
                                    • Status: offline
                                    Re:SSRS Help - Sort Chart Results by Month Tuesday, March 21, 2017 12:07 PM (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

                                      • Total Posts : 20
                                      • Scores: 0
                                      • Reward points : 1100
                                      • Joined: 2/10/2017
                                      • Status: offline
                                      Re:SSRS Help - Sort Chart Results by Month Tuesday, March 21, 2017 12:24 PM (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

                                        • Total Posts : 20
                                        • Scores: 0
                                        • Reward points : 1100
                                        • Joined: 2/10/2017
                                        • Status: offline
                                        Re:SSRS Help - Sort Chart Results by Month Wednesday, March 22, 2017 10:11 AM (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
                                          Online Bookmarks Sharing: Share/Bookmark

                                          Jump to:

                                          Current active users

                                          There are 0 members and 5 guests.

                                          Icon Legend and Permission

                                          • New Messages
                                          • No New Messages
                                          • Hot Topic w/ New Messages
                                          • Hot Topic w/o New Messages
                                          • Locked w/ New Messages
                                          • Locked w/o New Messages
                                          • Read Message
                                          • Post New Thread
                                          • Reply to message
                                          • Post New Poll
                                          • Submit Vote
                                          • Post reward post
                                          • Delete my own posts
                                          • Delete my own threads
                                          • Rate post

                                          2000-2017 ASPPlayground.NET Forum Version 3.9