Dynamic parameter in Crystal Reports with filter

Question

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

if you have correct linking between tables then you can create the parameter as required... while creating parameter use static instead of dynamic — Siva
Correct me if i misunderstood your question. Its about displaying filtered values(i.e. places from a table's column) for selection for a dynamic parameter(i.e. place). The filtered values depends on the value of invoice id and customer id. So, the expectation is crystal report should know the available customer ids for the invoice id when the report is shown/refreshed. As far as I know its not possible to show filtered values while customer id yet to be specified. — Mukul Varshney
Siva: I believe I have a correct linking and it still shows all values so your answer doesn't help me. — RRM

Khalid Mahmood Khalid Mahmood · Accepted Answer · 2017-01-30T10:21:41

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.

Dynamic parameter in Crystal Reports with filter

1 Answers