Displaying data from different records on same line 2

[jkellington]

Hi all, I'm reporting from a software inventory table from my IBM Director database. The table lists each specific peice of software as a separate record containing software name, software version and machine ID among along with other data not relevant to this discussion.

What I'd like to do is list each Machine ID down the left hand side of the page with specifically selected peices of software (MS-Office, IBM Director, IE, etc.) listed horizontally in the header and the particular version of that software for the machine listed in the machine's row in the Software name column. I've been playing with the selection expert, but I can't find a way to put the version data from several different records on one row. I've heard that Crystal is fairly robust and I'm assuming that this can be done; after all it would only takea few lines of PHP&SQL to pull the data I need. I'm relatively new to Crystal, but not to programming and SQL. Any help you could provide would be greatly appreciated.

Thanks,
Jason

 

[synapsevampire]

The version of your software and database are critical when requesting technical information, as a PHP-SQL coder, you must understand this.

Since you've omitted basic technical information, I'll give you the kludge version that works for most:

Insert->Group->select the MachineID

Use the 3 formulas method to contain the software:

Group header (Suppress this):
whileprintingrecords;
Stringvar Software := ""

Details section (Suppress this):
whileprintingrecords;
Stringvar Software;
If not({table.software} in Software) then
Software := Software+{table.software}+", ";

Group Footer (You'll display all fields here):
whileprintingrecords;
Stringvar Software;
left(Software,len(Software)-2)

Hope this does it for you.

Note that CR 8.5 and below can only support 254 chars output from a formula, so it might blow if you have an old version.

-k

 

[jkellington]

Sorry about the ommission of details, I'm using CR10 & SQL2K.

Your post looks like it makes sense, I'll try it out tomorrow AM. I'm finding that Crystal has an almost infinite number of places where you can stuff a formula, and an almost as infinite number of ways to get to those places.

 

[synapsevampire]

Yeah, Crystal has a lot of band aids on band aids, but overall it's a very flexible, mature product.

Good luck,

-k

 

[lbass]

You could try inserting a crosstab and using {table.machineID} as the row field, {table.software} as the column field, and {table.version} (if you have a field like that) as the summary field. If there could be more than one version of a specific software loaded per machine, you could use NthMostFrequent as the summary and add it repeatedly with N = 1,2,3 up to the number of maximum number of versions per software per machine.

If one of these ideas doesn't do the trick, it would help to have an idea of what fields you are working with and maybe a sample layout of the report you are trying to achieve.

-LB

 

[chelseatech]

Another approach is a subreport to select the different software entries and format the details section in the subreport with "Format with Multiple Columns"

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[jkellington]

Thanks lbass, that did it. Now how do I make my cross tab groups (Grouped by physical location) print on separate pages (ie: page break after each crosstab group)

 

[synapsevampire]

Place the crosstab into the group footer, now it will print for each group, and right click the section and select format section->New Page After

Many formatting options are lost using cross-tabs, hence I didn't suggest it.

-k