Sign in
 

none
Custom ChassisType Report - wrong counts RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi there

    I have a query which is showing incorrect count results and wonder if someone can help me see where I have gone wrong please.  What I am trying to do is to offer a count of Chassis Types based upon a selection in SSRS on Collection, Manufacturer, OS Version so that I can include this in a Pie Chart.  What I am seeing is the count being skewed by the number of collections in which the client appears.

    Many thanks in advance

    SELECT count (*) as ChassisCount ,
        v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
     
    FROM
      v_GS_SYSTEM
      INNER JOIN v_GS_SYSTEM
        ON v_GS_SYSTEM_ENCLOSURE.ResourceID = v_GS_SYSTEM.ResourceID
      INNER JOIN v_GS_OPERATING_SYSTEM
        ON v_GS_OPERATING_SYSTEM.ResourceID = v_GS_SYSTEM.ResourceID
      INNER JOIN v_FullCollectionMembership
        ON v_FullCollectionMembership.ResourceID = v_GS_SYSTEM.ResourceID
    WHERE
    (v_GS_SYSTEM_ENCLOSURE.Manufacturer0 = @Manufacturer0 or @Manufacturer0 = 'All')
    AND (v_GS_OPERATING_SYSTEM.Version0 = @Version0 or @Version0 = 'All')
    AND (v_FullCollectionMembership.CollectionID = @CollectionID or @CollectionID = 'All')
    GROUP BY V_GS_SYSTEM_ENCLOSURE.ChassisTypes0
       HAVING COUNT(*) >=1

    Friday, March 2, 2012 11:28 AM
    |

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Is it too high or too low? Why have the "Count(*) >=1"?

    http://www.enhansoft.com/

    Friday, March 2, 2012 9:07 PM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi there Garth

    The count that I am getting is equal to the number of collections that the system is in, so I'm sure it's related to the collection logic - just cannot see it myself

    Why the >= 1, well good question - I am planning to put a nice big CASE statement in there to show all 25 chassis descriptions and then to squirt that into a pie chart on a report.  With this in mind I am looking to exclude 0 values - but then in writing the sentance I can see the hole in my logic on that one

    Sunday, March 4, 2012 9:56 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
     So there are a few things wrong with this query..
    1. Nowhere in the query above are you looking at dbo.v_GS_SYSTEM_ENCLOSURE (SE) and therefore ChassisType, I assume it is a typo/Cut and paste error
    2. v_GS_SYSTEM is not needed at all and can be removed, move everything over to SE view
    3. Having count(*) >=1 is redundant, anything with a count of Null (0) will not be displayed anyways.

    Now for your real issue, Why your count are “equal to the number of collections that the system”, the answer is, it is because of the way to have written your query. You have told it to list all systems in all collections and count the Chassistype of each system within each collection. What you want to do is set the Default to the all systems collect instead “SMS00001”, this will solve your problem.

    BTW here is what the query should look like: http://tinyurl.com/7xot7he

    http://www.enhansoft.com/

    Sunday, March 4, 2012 5:30 PM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    I had a similar problem with chassis types.. I found  that clients were reporting twice when it came to Docking station and Laptops. 

    I already created a Chassis Case type in SQL a while ago I'll see if I can find it. 

    http://joejoeinc.com/2012/03/sccm-sql-count-of-chassis-type.html

    • Proposed as answer by JoeJoeInc Friday, March 9, 2012 11:30 AM
    Monday, March 5, 2012 4:28 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Joe

    Thanks for the reply.  I used an almost identical version of your report

    Select  Case SE.ChassisTypes0
      When 1 Then 'Other'
      When 2 Then 'Unknown'
      When 3 Then 'Desktop'
      When 4 Then 'Low Profile Desktop'
      When 5 Then 'PizzaBox'
      When 6 Then 'Mini-Tower'
      When 7 Then 'Tower'
      When 8 Then 'Portable'
      When 9 Then 'Laptop'
      When 10 Then 'Notebook'
      When 11 Then 'Handheld Device'
      When 12 Then 'Docking Station'
      When 13 Then 'All-In-One'
      When 14 Then 'Sub-Notebook'
      When 15 Then 'Space Saving'
      When 16 Then 'Lunch Box'
      When 17 Then 'Main System Chassis'
      When 18 Then 'Expansion Chassis'
      When 19 Then 'Sub-Chassis'
      When 20 Then 'Bus Expansion Chassis'
      When 21 Then 'Peripheral Chassis'
      When 22 Then 'Storage Chassis'
      When 23 Then 'Rack-Mount Chassis'
      When 24 Then 'Sealed PC'
      Else 'Unknown'
     End 'Chassis Type',
     Count(*) 'Chassis Count' 

     From v_GS_System_Enclosure SE
      
      Join v_R_System SD on SE.ResourceID = SD.ResourceID 
      Join V_GS_Operating_System OS on SE.ResourceID = OS.ResourceID
      Join v_FullCollectionMembership CM on SE.ResourceID = CM.ResourceID

     
     Where SE.Manufacturer0 LIKE @Manufacturer0
     AND OS.Version0 LIKE @Version0
     AND CM.CollectionID LIKE @CollectionID

     Group By SE.ChassisTypes0

    So, what we should see is one record in SE for each ResourceID, one record in OS for each ResourceID.  Now if I enter in SMS% for CollectionID what I see is (because the system is in 3 collections) the count going up to 3 instead of remaining at 1 for example.

    What I want to achieve is that we can slice and dice the data by Manufacter, OS Version, CollectionID or be able to offer a null parameter and show everything

    Tuesday, March 6, 2012 10:49 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi folks Count (Distinct  was the way to go
    Thursday, March 8, 2012 11:16 PM
    |