Results 1 to 4 of 4

Thread: Count distinct

  1. #1
    Join Date
    Apr 2008
    Posts
    6

    Unanswered: Count distinct

    Dear all,

    I have stored procedure in MSSQL and am using Crystal Reports which linked to the stored procedure with below SQL.
    select TRANSACTION_ID, BAGGAGE_ID, LapsedTime from trans_Details where ...............

    DATA like below:
    00001, 00001, 2 hours
    00001, 00002, 2 hours
    00002, 00003, 3 hours

    I want below result to show in the report
    Elapsed Time Num of Baggage Num of Transaction
    2 Hours 2 1
    3 Hours 1 1

    Currently I have Num of Baggage and need to add Num of Transaction.
    It seems to be distinct count of Transactin in the report. How can I do?
    Please help.

  2. #2
    Join Date
    Apr 2008
    Posts
    6

    Current method on Baggage

    Dear all,

    On Baggage, I am creating formula as below and sum the subTotal.

    if ( {SP_RPTLuggageDurationReport.DWELL} == 2) then // 2 hours
    formula = 1
    else
    formula = 0
    end if

    How about Transaction ID?? Need distinct count and except "0" string? How to do??

    if ( {SP_RPTLuggageDurationReport.DWELL} == 2) then // 2 hours
    formula = ToText( {SP_RPTLuggageDurationReport.Transaction_ID})
    else
    formula = ToText("0")
    end if

  3. #3
    Join Date
    Apr 2008
    Posts
    6
    I am creating the formula named as "3Cust":
    if ( {SP_RPTLuggageDurationReport.DWELL} <= 3) then
    formula = {@ReceiptID}
    end if

    And then insert summary field with "3Cust" and select distinct count.

    I found out that this field is more than 1 when compared to actual data.

    Do you know Why??

  4. #4
    Join Date
    Apr 2008
    Posts
    6

    Attach the screen

    File is attached
    Attached Thumbnails Attached Thumbnails Untitled.png  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •