count distinct alternative

[Guest_imported]

hello all,

how do one get distinct count of a column value in Business Objects if the underlying
RDBMS doesn't allow using count(distinct col1) in select statement like MS Access.

creating a measure like the one below gives me an error while executing the report.

count(distinct column1).

i think there should be someway to get around this.

thanks for your replies

jack

 

[blom0344]

Hello Jack,

Are you sure that you cannot achieve what you want just by using the way BO displays data at report level?

Example , I have a table with a years sales to customers. So, in this case a customer can occur 0,1,n times in this table , depending on the amount of orders placed. However, if I just retrieve the customernumbers in a BO report every customer will only be once displayed in a reporttable. If I count these occurences with the standard count function I implicitly get the desired effect :

count (distinct(customer))

This is a area where BO and MS Access work very dissimilar. Access gives you exactly the output of the defined query, while BO displays the effect of the distinct version of the query. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[Guest_imported]

no its not working because access doesn't allow count(distinct(customer_id)). still counting multiple occurences of customer_id

thanks

 

[blom0344]

Hello Jackie,

Obviously my feedback had no impact.

I'm was trying to point out to you that you can solve this without using explicit SQL like "count(distinct(cust_id))"

Just select cust_id in your query panel , run a report and then look at the resulting table. No matter how often a cust_id will reside in your source, in the report you will only see it once in the resulting table.

This is only valid if you only retrieve cust_id, retrieve more details (with a n to 1 relation to cust_id) and the report will show every unique combination. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com