Record Selection

[jkellington]

I have a small (newbie) problem.
CR10, SQL2K

I'm pulling software inventory records from a table and putting them into a crosstab. The pertinent information contained in each record is as follows: 'Workstation Label', 'Software Title', 'Software Version'. My cross tab has software titles running as columns with each Workstation showing up as a row. I use select expert to pick the particular software titles I want in the table and then if that software title shows up in a record, it shows the version number in the row of the workstation referenced in that record. My problem is that for some pieces of software, more than one version is installed, so the software title is duplicated for those workstations which house more than one version. So......in my cross tab summary, which gives a count of the number of times its column's 'Software Title' appears in the table counts the duplicated versions of the software (same 'Software Title', different 'Software Version'), when I really only want to know what the most current version installed is and a count of the number of machines that have that particular 'Software Title' installed, regardless of version, and not more than once per workstation.

Hope this all makes sense.

Thanks,
Jason

 

[synapsevampire]

It sort of makes sense, in general try posting example data and expected output for the best results.

Group by the Software Title.

Use Report->Selection formulas->Group

Add something like:

{table.version} = maximum({table.version},{table.software})

Suppress the Details section, place all fields in the Software group footer, insert the machine field into the details and select insert->Summary->Distinct Count, making sure that it's summarizing at the Software grouping.

Place the software in the details and select Insert->Summary->Count, making sure that it's summarizing at the Software grouping.

-k

 

[lbass]

Since this is a crosstab, try using maximum of {table.softwareversion} as one summary, and distinctcount of {table.softwaretitle} for the other.

-LB