>>>Hi,
>>>
>>>I need to create a variable in a report that will count the number of distinct values. For example, if the report has a column EMPLOYEE_ID the variable should count how many employees are in the report. How? I tried to enter in the value to store "DISTINCT( EMPLOYEE_ID )" but it gives me error. TIA for any help.
>>
>>The key to any task like this is using two variables, one to track the changing value and one to do the counting. Typically, you want to set them up with the counter first and the tracker second, along these lines:
>>
>>Name: nEmployees
>>Initial value: 0
>>Value to store: IIF(Employee_ID = nLastEmpID, 0, 1)
>>Calculate: Sum
>>
>>Name: nLastEmpID
>>Initial value: {some value that can't occur in the data}
>>Value to store: Employee_ID
>>Calculate: None
>>
>>Tamar
>
>What I decided to do is set a PRIVATE variable before calling the report. And populate this private variable by selecting count(distinct()) from the base cursor for the report. The reason I made this variable private is so that it would be in the scope of the report. And the code for the sql select is as following:
>
>select count(distinct(EMPLOYEE_ID)) as TOT_NUM from BaseTable into cursor CursorName
>
Actually, you don't need the variable to be private if it's declared local in the same routine that runs the report. Reports can see locals from the calling routine.
Tamar