Skip to Content
avatar image
Former Member

SAP Crystal Reports 2016 Dynamic Parameters not displaying Duplicate Records

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 19, 2017 at 12:04 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 19, 2017 at 12:49 PM

    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

    Add comment
    10|10000 characters needed 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