locked
Reporting for a specific collection only. RRS feed

  • Question

  • I am trying to run a few reports in the Hardware section of low memory and computer models for my campus alone, which is in a collection. But, there are only options to run a report on all computers or just one.

    I have tried to reverse engineer the selection/filter of a collection based on other built-in reports which have that selection available. However that isn't working out too well since I don't really know sql yet.

    If someone would let me know what needs to be added I would appreciate it.

    Wednesday, June 13, 2012 8:48 PM

Answers

  • Frosty,

    You are right - you'll need to modify the actual report query to include a "where" clause based on a collection.  V_ClientCollectionMembers will do the trick.  Basically you want to create a "join" based on that collection and then it will help you filter based on the collection.  I've designated two parts below to help you create the report.  You'll have to learn a bit about report making - which isn't too bad once you get the hang of it. 

    Basically - you will want to create a new report - then add 2 datasets to the report (one for the table, and one for the collection parameter).

    Part 1:  Dataset for the SSRS main report section.  Basically I modified the low memory report already existing in the console and added a second parameter for the collection.  I joined the v_ClientCollectionMembers to the query.

    SELECT DISTINCT SYS.Netbios_Name0, SYS.Operating_System_Name_and0, MEM.TotalPhysicalMemory0 / 1024 AS C083, v_ClientCollectionMembers.CollectionID
    FROM         v_R_System AS SYS INNER JOIN
                          v_GS_X86_PC_MEMORY AS MEM ON SYS.ResourceID = MEM.ResourceID INNER JOIN
                          v_ClientCollectionMembers ON SYS.ResourceID = v_ClientCollectionMembers.ResourceID
    WHERE     (MEM.TotalPhysicalMemory0 / 1024 <= @variable) AND (v_ClientCollectionMembers.CollectionID = @variable2)

    Part 2:  Dataset for the "collection selector" parameter.  You'll want to edit the parameter to be a "get values from query" rather than single input value.  This will then let you see the list, select the parameter, and then fill the table with the right values.

    Select Name, CollectionID from v_Collection order by Name

    Hopefully this will helps!  I'm assumign you know how to make a report based on your reverse engineering - but if not, follow these quick steps:

    1. Create a new report in the console
    2. Create 2 new dataset (embedded in the report)
    3. Insert a table using the table wizard
    4. Add the Part1 query - then add the columns to the values in the wizard
    5. Back the report - edit the parameter to "Get Values from Query" and add your second dataset

    Hope this helps!  I've been spending some time recently in the reports and figured I'd help out!  I know a lot of people have been struggling to get SSRS reports rolling with 2012.

    -Chris B

    • Proposed as answer by Beamer25 Friday, June 22, 2012 3:18 AM
    • Marked as answer by Frosty5F11 Monday, June 25, 2012 4:06 PM
    Sunday, June 17, 2012 3:00 AM

All replies

  • Hello Frosty5F11!

    I think there is no built in report what is good for you so you have to make it to yourself.

    Use the built in Rerport Builder 3.0 from the Reporting site and if you have made the report upload it to the Site.

    Brgds.

    Tamas

    Friday, June 15, 2012 1:37 PM
  • Frosty,

    You are right - you'll need to modify the actual report query to include a "where" clause based on a collection.  V_ClientCollectionMembers will do the trick.  Basically you want to create a "join" based on that collection and then it will help you filter based on the collection.  I've designated two parts below to help you create the report.  You'll have to learn a bit about report making - which isn't too bad once you get the hang of it. 

    Basically - you will want to create a new report - then add 2 datasets to the report (one for the table, and one for the collection parameter).

    Part 1:  Dataset for the SSRS main report section.  Basically I modified the low memory report already existing in the console and added a second parameter for the collection.  I joined the v_ClientCollectionMembers to the query.

    SELECT DISTINCT SYS.Netbios_Name0, SYS.Operating_System_Name_and0, MEM.TotalPhysicalMemory0 / 1024 AS C083, v_ClientCollectionMembers.CollectionID
    FROM         v_R_System AS SYS INNER JOIN
                          v_GS_X86_PC_MEMORY AS MEM ON SYS.ResourceID = MEM.ResourceID INNER JOIN
                          v_ClientCollectionMembers ON SYS.ResourceID = v_ClientCollectionMembers.ResourceID
    WHERE     (MEM.TotalPhysicalMemory0 / 1024 <= @variable) AND (v_ClientCollectionMembers.CollectionID = @variable2)

    Part 2:  Dataset for the "collection selector" parameter.  You'll want to edit the parameter to be a "get values from query" rather than single input value.  This will then let you see the list, select the parameter, and then fill the table with the right values.

    Select Name, CollectionID from v_Collection order by Name

    Hopefully this will helps!  I'm assumign you know how to make a report based on your reverse engineering - but if not, follow these quick steps:

    1. Create a new report in the console
    2. Create 2 new dataset (embedded in the report)
    3. Insert a table using the table wizard
    4. Add the Part1 query - then add the columns to the values in the wizard
    5. Back the report - edit the parameter to "Get Values from Query" and add your second dataset

    Hope this helps!  I've been spending some time recently in the reports and figured I'd help out!  I know a lot of people have been struggling to get SSRS reports rolling with 2012.

    -Chris B

    • Proposed as answer by Beamer25 Friday, June 22, 2012 3:18 AM
    • Marked as answer by Frosty5F11 Monday, June 25, 2012 4:06 PM
    Sunday, June 17, 2012 3:00 AM
  • Thanks, sorry for just now replying. I am going to try this out, I'll go ahead and mark it as answer too.
    Monday, June 25, 2012 4:06 PM