Help with drill down

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

Help with drill down

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> Help with drill down

Page: [1]

Message

<< Older Topic Newer Topic >>

Help with drill down - 7/19/2006 10:18:11 AM

jbrown742

Posts: 52
Score: 0
Joined: 8/12/2005
From: Indiana
Status: offline

I have the following web report for chassis types and I want to add a drill downs for each type to actually show netbios name and last logged on user for each chassis type. Right now all I get is a list of chassis types with number of clients. The drill down take me to the generic computer details interface. How do I get the drill down to list the computers under each chassis type?
Thanks for any help.

SELECT
Case v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
when '1' then 'Other'
when '2' then 'Unknown'
when '3' then 'Desktop'
when '4' then 'Low Profile Desktop'
when '5' then 'Pizza Box'
when '6' then 'Mini Tower'
when '7' then 'Tower'
when '8' then 'Portable'
when '9' then 'Laptop'
when '10' then 'Notebook'
when '11' then 'Hand Held'
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 'SubChassis'
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-Case PC'
else 'Undefinded'
end as 'PC Type',
Count(v_GS_SYSTEM_ENCLOSURE.ChassisTypes0) AS 'Total'
FROM
v_GS_SYSTEM_ENCLOSURE v_GS_SYSTEM_ENCLOSURE
GROUP BY
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
Order by
'PC Type'

Post #: 1

RE: Help with drill down - 7/19/2006 10:51:30 AM 1 votes

abhai

Posts: 27
Score: 3
Joined: 7/17/2006
Status: offline

First Replace your given report Query with the new Query(Slight Change)
SELECT v_GS_SYSTEM_ENCLOSURE.ChassisTypes0,
Case v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
when '1' then 'Other'
when '2' then 'Unknown'
when '3' then 'Desktop'
when '4' then 'Low Profile Desktop'
when '5' then 'Pizza Box'
when '6' then 'Mini Tower'
when '7' then 'Tower'
when '8' then 'Portable'
when '9' then 'Laptop'
when '10' then 'Notebook'
when '11' then 'Hand Held'
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 'SubChassis'
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-Case PC'
else 'Undefinded'
end as 'PC Type',
Count(v_GS_SYSTEM_ENCLOSURE.ChassisTypes0) AS 'Total'
FROM
v_GS_SYSTEM_ENCLOSURE v_GS_SYSTEM_ENCLOSURE
GROUP BY
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
Order by
'PC Type'
---------------------
create another report
Put below Query for new report--

SELECT distinct SYS.Netbios_Name0, SYS.User_Name0,CSYS.Model0,bios.serialnumber0 as 'Serial No',Processor.Name0 as 'Processor Name',MEM.TotalPhysicalMemory0/1024 as 'Total Physical Memory(MB)',
OPSYS.Caption0 as C054, OPSYS.Version0
FROM v_R_System SYS
LEFT JOIN v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM.ResourceID
LEFT JOIN v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID
LEFT JOIN v_GS_PROCESSOR Processor on Processor.ResourceID = SYS.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID
LEFT JOIN v_GS_PC_BIOS Bios on SYS.ResourceID=Bios.ResourceID
LEFT JOIN v_GS_SYSTEM_ENCLOSURE otype on SYS.ResourceID=otype.resourceid
WHERE otype.chassisTypes0=@variable
ORDER BY SYS.Netbios_Name0
Create new prompt for this report---
name- variable
Prompt Text- Enter Chassis Type
Prompt SQL(Enable Check Box)
Prompt SQL--
begin
if (@__filterwildcard = '')
SELECT DISTINCT SYS.ChassisTypes0 from v_GS_SYSTEM_ENCLOSURE SYS ORDER By SYS.ChassisTypes0
else
SELECT DISTINCT SYS.ChassisTypes0 from v_GS_SYSTEM_ENCLOSURE
WHERE SYS.ChassisTypes0 like @__filterwildcard
ORDER By SYS.ChassisTypes0
end
Save this report.Note down the report number .Example new report number is 300.Now open old report, Click on Link to another report,select report 300 for connection.Now run ur old report.U will get in left pane , one connector,Click on it.It will give u detailed info regarding that particular chassis.

_____________________________

Abhai Srivastava
Project Engineer,SMS 2003

(in reply to jbrown742)

Post #: 2

RE: Help with drill down - 7/19/2006 11:49:02 AM

jfunderburg

Posts: 295
Score: 32
Joined: 10/26/2004
From: Nashville, TN
Status: offline

(removed)

(in reply to jbrown742)

Post #: 3

RE: Help with drill down - 7/21/2006 4:33:40 PM

jbrown742

Posts: 52
Score: 0
Joined: 8/12/2005
From: Indiana
Status: offline

This worked!! Thanks for your help.

(in reply to jfunderburg)

Post #: 4

RE: Help with drill down - 11/11/2008 8:19:36 PM

pmurray

Posts: 38
Score: 0
Joined: 11/1/2008
Status: offline

Sorry to dig this up but I stumbled across this and I was wondering what some of these when's mean;

Like
when '5' then 'Pizza Box'
when '15' then 'Space-Saving'
when '16' then 'Lunch Box'

Or does anyone have a URL to all the meanings?

Thanks

quote:

ORIGINAL: abhai
Case v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
when '1' then 'Other'
when '2' then 'Unknown'
when '3' then 'Desktop'
when '4' then 'Low Profile Desktop'
when '5' then 'Pizza Box'
when '6' then 'Mini Tower'
when '7' then 'Tower'
when '8' then 'Portable'
when '9' then 'Laptop'
when '10' then 'Notebook'
when '11' then 'Hand Held'
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 'SubChassis'
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-Case PC'
else 'Undefinded'
end as 'PC Type',

(in reply to abhai)

Post #: 5

RE: Help with drill down - 11/12/2008 6:12:55 AM

Tom_Watson

Posts: 126
Score: 10
Joined: 9/13/2006
Status: offline

"Pizza Box" was a form factor that Sun was famous for using. See http://en.wikipedia.org/wiki/Pizza_box_form_factor

"Space-Saving" is just another term for "Small Form Factor" in HP's parlance. http://www.google.com/search?hl=en&q=small+form+factor+site%3Ahp.com gives some examples. Dell has a similar chassis type for some of its desktops.

"Lunch Box" is a strange one. According to http://en.wikipedia.org/wiki/Small_form_factor#Lunch_Box a "Lunch Box" is high and narrow, designed to sit a monitor on top - sort of like a mini-tower sitting on its side. However, http://www.lunchboxcomputers.com has a slightly different take on this. Looking at the pictures at the bottom, they almost looked like the first available portable computers. Kind of like mini-towers with LCD screen attached and a fold down keyboard.

Regards,
Tom Watson

(in reply to pmurray)

Post #: 6

RE: Help with drill down - 11/12/2008 6:48:42 AM

pmurray

Posts: 38
Score: 0
Joined: 11/1/2008
Status: offline

Thank you Tom

(in reply to Tom_Watson)

Post #: 7