Search code examples
sqldatabaseoraclecrystal-reports

Retrieve/group multiple rows from the same table as a single record in Crystal Reports

Say I have some tables in my DB as follows. They've been added in Crystal Reports using Database Expert, and they are linked together in the Link tab using the various corresponding ID columns:

CUSTOMER_PUBLIC_ID
------------------
CUSTOMER_ID
STORE_ID
PUBLIC_ID

STORE
-----
STORE_ID
STORE_NAME

PURCHASE
--------
PURCHASE_ID
CUSTOMER_ID
STORE_ID
PRICE

Each customer has a different public ID for each store they shop at. For example, here are some rows for a customer with CUSTOMER_ID = '1' from the CUSTOMER_PUBLIC_ID table:

CUSTOMER_ID / STORE_ID / PUBLIC_ID
1           / 10       / 100500
1           / 11       / 3400
1           / 99       / 900800

STORE_ID 99 is a fictional store that is used to keep a store-independent customer public ID for any particular customer.

Crystal Reports' default autogenerated SQL statement is retrieving all of the customer information per purchase as I would have expected: if a PURCHASE was made at a store with STORE_ID='11', then I get the PUBLIC_ID for that customer for STORE_ID='11'. My report displays something like this:

Purchase ID: 600, Store ID: 11, Customer Public ID: 3400, Price: 5.99

I want to also retrieve the store-independent PUBLIC_ID for my customers (with STORE_ID='99'), so I can display something like this instead:

Purchase ID: 600, Store ID: 11, Customer Public ID: 3400, Customer Global ID: 900800, Price: 5.99

What is the proper way of doing this in Crystal Reports? Right now, I have a Public_ID Formula Field that retrieves the value as follows:

Stringvar CustomerPublicID := {CUSTOMER_PUBLIC_ID.PUBLIC_ID};

This retrieves the PUBLIC_ID that corresponds to the STORE_ID at which the purchase was made. What would the proper way of also retrieving the "global customer ID" (as I've called it above) in Crystal Reports be?

Solution

  • you can solve this using Sub Report.

    1. Since data is retreived per customer, So group the main report per CUSTOMER_ID.

    2. Create a formula @Store place in detail section and supress the detail.

      Shared Numbervar Store;
if STORE_ID=99
Then Store:=PUBLIC_ID

    3. Now take a sub report and place it in group footer of the Customer group.

    4. Link the sub report with @Store using Change sub report links.

    5. In sub report create a formula @Retrive and write below code. Also in sub report create the same grouping hierarchy as main report.

      Shared Numbervar Store;
Store

    Now you can use the formula Retrive inside the sub report for display and show your data in sub report and supress all sections excluding the sub report section in main report.

    Hope this helps