Pre report prompt - 2012

[malomay]

OK, the site is called windows-noob....and I fit right into that category, so apologies in advance.

 

I am just having trouble getting my head around what I thought should be a relatively simple report.

 

I'm trying to display a count summary of computer Models and the Manufacturer and would like to be prompted for a collection prior to running the report,

So for example, in the collection "All Windows XP Systems"

-------------------------------------------------------------

Manufacturer Model Count

Dell Inc. Latitude E5520 145

------------------------------------------------------------

 

The SQL I'm using for the basic report is:

------------------------------------------------------------

select
Manufacturer0, Model0,
Count(*)
from
dbo.v_GS_COMPUTER_SYSTEM CS

GROUP BY
Manufacturer0, Model0
Order by Manufacturer0, Model0

------------------------------------------------------------

 

But I'm having real trouble getting my head around the collection prompt, the join clauses etc.

 

Is this something really simple....or am I out of my depth (which is pretty shallow I can tell you !)

 

Any assistance gratefully & humbly received.

 

[GarthMJ]

You can do this using dbo.v_FullCollectionMembership the SQL view and preforming a inner join on ResourceID between both SQL views.

[malomay]

Cheers Garth,

 

So would I need two Datasets to do this, or can it be done with 1 only ?

 

BTW, if it's easier to have the collection name hardcoded into the SQL, I would quite happily go that way at this point.

 

regards

 

Mal.

[GarthMJ]

Yes.. :-)

 

You need to 2 datasets if you want to create a drop down menu.

You need only 1 dataset if you want freeform text box. or if you want to hardcode the Collection.

 

Personally I would go with two, it make the report much clearer looking.

[Chm0d]

You can do this using dbo.v_FullCollectionMembership the SQL view and preforming a inner join on ResourceID between both SQL views.

GarthMJ,

 

I may be more of a NOOB than the person who posted this question. All day today I have been trying to figure out how to be prompted for a collection. I understand created the extra dataset and adding a parameter to point to that dataset. What I am not understanding is how to tie all this in to the Original Query. My original Query basically looks at "All Systems" and tells me the OS that is installed. I have 42 different collections I would like to be able to select from instead of getting a report of All Systems. Here is my query:

 

select all SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0 from vSMS_R_System AS SMS_R_System INNER JOIN Operating_System_DATA AS SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.MachineID = SMS_R_System.ItemKey INNER JOIN _RES_COLL_SCS00235 AS SMS_CM_RES_COLL_SCS00235 ON SMS_CM_RES_COLL_SCS00235.MachineID = SMS_R_System.ItemKey where SMS_G_System_OPERATING_SYSTEM.Caption00 = N'Microsoft Windows 7 Professional'

 

How do I join the other dataset to this to prompt me to choose a collection? I apologize for the dumb question. I have spent all day on google and tried so many different things with no luck. I know this can't be that difficult. Would appreciate any assistance.

 

Rich