Groups and stored procs

[Danster]

Aim: To get a customer price list.

I have a stored procedure which finds a price for an item given a customer and itemID.

All items are grouped (not in Crystal, I'm getting to that soon) by a description. Lets say the items are screws so I have "Coach screws", "Wood screws", Metal screws", "roofing screws" as the groups in my app.

My app is able to store specific prices for specific customers & it's stored by itemID (not the items group) All like items within a group have been configured in the app to have the correct price.
So
MrX gets Screw123 for $10
MrY gets screw123 for $10.50
MrZ gets screw123 for $10
and screw123 happens to be within the "Metal screws" group
so all "Metal screws" are priced at $10 for MrX.

So in summary the database is configured thus...
ITEMID GROUP
Screw123 Metal
Screw124 Metal
Screw125 Metal
Screw200 Roofing
Screw201 Roofing

MrX gets
Screw123 for $10
Screw124 for $10
Screw125 for $10
Screw200 for $11
Screw201 for $11



I already have the stored proc working when I pass it a
1) customer name and 2) a item group

It finds a sample item from the db using a SELECT TOP 1 ItemID WHERE GROUP = @MyGroup. The stored proc works fine in Query Analyser (yep I'm using a SQL database) and displays the price for the item.

Now to my Crystal dilemma...I just want the user to enter
a customer only.
The report will display
GROUP PRICE

so if MrX is entered the rpt will display

Metal $10
Roofing $11
Wood $8
Coach $10.50
et al.....

I've attempted a few variations of the report but just can't get it right. I'm thinking a subreport is the way to go.
The main report will ask the user for a customer name which is stored in a formula field. Then I think the main report is based on a table of all my item groups and is also grouped by the group description. The subreport, in the group footer is based on the stored procedure. It needs to get the customer and group from the main report. I know about Shared StringVar but the subreport doesn't display any data.
Can anyone shed any light? If you need further info pls let me know.
cheers

Danster

 

[IdoMillet]

If you wish to use a Stored Procedure as the data source, use an argument of Customer and return:

Select Item_Group, Max(Price) FROM ...
Group By Item_Group
WHERE...

Of course, you could achieve the same result directly from Crystal. In such a scenario where no detail is needed in the report, Crystal can perform the Grouping on the back end server, so performance should be as good as a Stored Procedure or a View...

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com