Skip to Content
0

SAP Crystal Reports 2016 Dynamic Parameters not displaying Duplicate Records

May 19, 2017 at 05:10 AM

76

avatar image
Former Member

Hello Developers,

I have created a simple dynamic parameter by using a command prompt

SELECT DISTINCT '*All' [HR Site]
,'*All' [Process Level]
,'' [Process Level Description] 
UNION SELECT DISTINCT SITE_HDT.HR_SITE_CD [HR Site]
,SITE_HDT.PROC_LVL [Process Level]
, SITE_HDT.PROC_LVL_DESC [Process Level Description] FROM SITE_HDT

In SQL Server 2014 it is giving me the result as

where as in Crystal , I created a Multi value dynamic parameter based on following criteria

Parameter Name : {?ProcessLevel}

value : Process Level

Description : Process Level Description

Allow Multiple Values : True

Allow Discrete Values : True

But in Crystal reports the Multi valued dynamic parameter is picking up only Single Process Level with Same Number (Non-duplicate records) with Process Level Description which is highlighted in Yellow from the above screen shot.

I want crystal to pick up the remaining process levels with the Same Number as well (Duplicate Records).

I tried various methods in-order to fix it from couple of days. Today is last day to deploy the code .Please help me out in-order to fix this issue.

Thank You

Best Regards,

Preethi Desai

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Abhilash Kumar
May 19, 2017 at 12:04 PM
0

Hi Preethi,

Concatenate the ID and Name fields in the SQL and use that field as the prompt's source:

SELECT DISTINCT '*All' [ProcessLevel]
UNION
SELECT DISTINCT SITE_HDT.PROC_LVL + "-" + SITE_HDT.PROC_LVL_DESC [ProcessLevel] FROM SITE_HDT

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member May 19, 2017 at 12:49 PM
0

Hi Abhilash,

Thank for Your reply . I already concatenated the fields and passed as Value but it is a Multi-Value parameter. I need to pass into sub-report by spliting the SITE_HDT.PROC_LVL from the SITE_HDT.PROC_LVL+SITE_HDT.PROC_LVL_DESC.

SITE_HDT.PROC_LVL contains both Number and String. It is very hard to split them and pass into sub-report.

Please Advice.

Preethi Desai

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Modify query to show two values

SELECT DISTINCT 
,'*All' [Process Level]
,'' [Process Level Description] 
UNION SELECT DISTINCT SITE_HDT.PROC_LVL [Process Level]
, SITE_HDT.PROC_LVL + "-" + SITE_HDT.PROC_LVL_DESC [Process Level Description] 

FROM SITE_HDT
0