Crystal Reports select two out of many

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: select two out of many

Author	Message
jjjb Newbie Joined: 10 Aug 2009 Online Status: Offline Posts: 13	Topic: select two out of many Posted: 19 Aug 2009 at 10:02am
	hi, i want to create a report that pulls about 2 file numbers per customer. is there a function in crystal that will do that? thanks,
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 19 Aug 2009 at 10:57am
	You will have to describe that more. There are diffenerent approaches to this issue for example... manipulating the data at the DB level and then using that as the source; trimming the data via the select statement in crystal (individual or group level formulas); hiding data in crystal even though it exists in the report
	IP Logged

jjjb Newbie Joined: 10 Aug 2009 Online Status: Offline Posts: 13	Posted: 19 Aug 2009 at 11:04am
	say i have 3 customers; abc xzy 123 each customer has 25 files associated to them for the month of august. is there a way to only get 2 files per customer rather than all 25 for each customer? i'm trying to create this report for auditing purposes, so i only need to see a few files per customer. thanks !
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 19 Aug 2009 at 12:23pm
	Does is matter which 2 records or will any 2 work? You can write a Command to get the MAx or MIn values of some field from the table with the 25 records and then join that into the report instead of the original table.
	IP Logged

jjjb Newbie Joined: 10 Aug 2009 Online Status: Offline Posts: 13	Posted: 19 Aug 2009 at 12:32pm
	any 2 will work, thanks again!
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 19 Aug 2009 at 12:48pm
	YOu can use a Crystal Command to write a SQL statement that would give you the data from the customer table joined to the other table that is grouped and doing a MIN or MAX on some unique key or date time field from that table. The Command can be found in the Database Expert. Where you added your tables from a DB source...There is a Add COmmand option above those. Double click on it and you can write your SQL there. Sorry but I cannot write that for you. Can you use this solution or do you need a different one?
	IP Logged

jjjb Newbie Joined: 10 Aug 2009 Online Status: Offline Posts: 13	Posted: 19 Aug 2009 at 1:11pm
	sadly i dont know how to write sql statements. any other ideas?
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 19 Aug 2009 at 1:42pm
	Yes but you are stuck with this grouping in the report to make this work... Group on Customer Use a unique an incremental field from table to (for this example it will be called "table.PK"). Go into the Slect Expert. Click on Show Formula. Click on "group Selection" Click on Formula Editor. Add this formula (altered to match your data types). {table.PK}=MINimum({table.PK},{table.groupcustomerfield}) or {table.PK}=MAXIMUM({table.PK},{table.groupcustomerfield})
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum