There is a possibility in Crystal Reports to use dynamic parameters - list of choices for parameter isn't fixed and typed in the report but is taken from some database table. It is described for example here:
https://www.youtube.com/watch?v=kuHs89yyuEc
My problem is that a parameter created in such way allows to choose from ALL values in the table.
I'd like to filter it in relevance with data in report.
For example: My report represents an invoice. It is filtered to a single invoice by invoice id. A parameter allows to select place of delivery for the invoice. But I don't want to choose from all places of delivery in the table. Instead I'd like the parameter to display only places of delivery for the customer from the invoice.
Let's say customer_id is a formula field in a report and place of delivery is a table like
id customer_id street city ... Is it possible to filter the dynamic parameter in the way I describe?
EDIT: Maybe a simple example helps. I've created a test database with two tables (I'm using Sql Server):
CREATE DATABASE TEST USE TEST CREATE TABLE [dbo].[DELIVERY_PLACE]( [ID_DELIVERY] [int] NULL, [ID_CUSTOMER] [int] NULL, [ADDRESS] [varchar](50) NULL ) ON [PRIMARY] INSERT [dbo].[DELIVERY_PLACE] ([ID_DELIVERY], [ID_CUSTOMER], [ADDRESS]) VALUES (1, 1, N'Address A1') INSERT [dbo].[DELIVERY_PLACE] ([ID_DELIVERY], [ID_CUSTOMER], [ADDRESS]) VALUES (2, 1, N'Address A2') INSERT [dbo].[DELIVERY_PLACE] ([ID_DELIVERY], [ID_CUSTOMER], [ADDRESS]) VALUES (3, 2, N'Address B1') INSERT [dbo].[DELIVERY_PLACE] ([ID_DELIVERY], [ID_CUSTOMER], [ADDRESS]) VALUES (4, 2, N'Address B2') CREATE TABLE [dbo].[CUSTOMER]( [ID_CUSTOMER] [int] NULL, [NAME] [varchar](20) NULL ) ON [PRIMARY] INSERT [dbo].[CUSTOMER] ([ID_CUSTOMER], [NAME]) VALUES (1, N'Customer A') INSERT [dbo].[CUSTOMER] ([ID_CUSTOMER], [NAME]) VALUES (2, N'Customer B') And I have made a report using this database. you can get it here: https://www.sendspace.com/file/907wq9
The report filters to CUSTOMER_ID=1
DELIVERY_PLACE table links in report to CUSTOMER table by foreign key: CUSTOMER_ID
I have a dynamic parameter that takes address from DELIVERY_TABLE
But it shows all addresses while I want it to show only addresses filtered to current customer
1 Answers
Answers 1
I should've posted this as comment, but i dont have points etc.
As per my understanding you need to connect the tables on CustomerID. This will automatically link the customer to its address. Secondly you do not need parameter to select the right customer's address, you should place the database field Address from DELIVERY_PLACE, instead. I dont have your database so am lacking knowledge if both tables are already linked. Please right click the databasefileds node and show sql query.. and then post it for better understanding. You can also change your RecordSelection formula to {CUSTOMER.ID_CUSTOMER}=1 and {DELIVERY_PLACE.ID_CUSTOMER} = 1
This will show two addresses for each customer as per entries in the tables. if you want to select single address from the addresses table during runtime. you need to put it in record selection formula say .. {DELIVERY_PLACE.ID_DELIVERY} = {?DeliveryPlace} moreover it is better to give ID_DELIVERY to the parameter instead of complete address.
0 comments:
Post a Comment