crytal report count total records

Question

im using query in crystal report like

if({?User Name}) <>"ALL" then {COMN_USER_RESP_LINK_T.APPL_USER_NAME}={?User Name} else {COMN_USER_RESP_LINK_T.APPL_USER_NAME} ={COMN_USER_RESP_LINK_T.APPL_USER_NAME} and {COMN_USER_RESP_LINK_T.ENABLED}="Y"

this is actual query in sql: SELECT C.APPL_USER_NAME, A.RESP_NAME, B.MENU_NAME, B.DESCRIPTION, B.MODULE_NAME, C.APPL_RESP FROM COMN_RESPONSIBILITY_T A, COMN_RESP_MENU_LINK_T B, COMN_USER_RESP_LINK_T C WHERE A.COMP_CODE = B.COMP_CODE AND B.COMP_CODE = C.COMP_CODE AND C.COMP_CODE = A.COMP_CODE AND A.RESP_NAME = B.RESP AND C.APPL_RESP = A.RESP_NAME AND B.ENABLED = 'Y' AND C.APPL_USER_NAME = c.APPL_USER_NAME CASE @ACCT_CODE_FROM WHEN 'ALL' THEN C.APPL_USER_NAME ELSE @ACCT_CODE_FROM END ORDER BY APPL_USER_NAME, RESP_NAME

i should select user name in parameter field(?user name), if i select ALL then i have to show all records ({COMN_USER_RESP_LINK_T.APPL_USER_NAME})

is the above query correct?

Answer 1

I would probably rephrase that as something like:

SELECT C.APPL_USER_NAME, A.RESP_NAME, B.MENU_NAME, B.DESCRIPTION, 
    B.MODULE_NAME, C.APPL_RESP 
FROM COMN_RESPONSIBILITY_T A 
    INNER JOIN COMN_RESP_MENU_LINK_T B ON A.COMP_CODE = B.COMP_CODE 
        AND A.RESP_NAME = B.RESP
    INNER JOIN COMN_USER_RESP_LINK_T C ON B.COMP_CODE = C.COMP_CODE
        AND C.APPL_RESP = A.RESP_NAME 
WHERE ((@ACCT_CODE_FROM = 'ALL') AND (B.ENABLED = 'Y')) 
    OR (C.APPL_USER_NAME = @ACCT_CODE_FROM) 
ORDER BY APPL_USER_NAME, RESP_NAME 

This gets you the details for either:

• when ALL is selected, all accounts that are enabled; or
• just the user code entered

Answer 2

i don't read your code because it is very dirty! but i think you can use SelectionFormula in crystal report. Certainly you can set it in code :

crystalReportViewer1.SelectionFormula ="(({?User Name}= 'ALL') AND ({B.ENABLED} = 'Y')) OR ({C.APPL_USER_NAME} = @ACCT_CODE_FROM)"