Count Distinct as a measure in a BO-universe

[Gunnien]

Hello,

I am trying to create a webi report that are supposed to show the number of early customer orders by counting the unike order numbers. The reason I want to do this in webi is to distribute the report to a bigger number of users with the possibility to drill down in the report.

In order to achieve this I am trying to create a mesure in the universe that are supposed to count the odrder numbers in. I am using the SQL syntax as a template for this measure.

Template: COUNT(DISTINCT CASE WHEN [Order type] IN ('H01', 'H02') AND Datediff(dd, [Entry date], Requested deliverydate]) >= 14 THEN [Customer order number] ELSE NULL END) This syntax works fine when I am using a SQL tool directly against the database.

When I am trying to do the same by using the BO designer with this syntax:

COUNT(DISTINCT CASE WHEN Datamarts.dbo.Sales_OrderStatistics."Customer order type" IN ('H01','H02') AND Datediff(dd,Datamarts.dbo.Sales_OrderStatistics."Entry date",Datamarts.dbo.Sales_OrderStatistics."Requested delivery date") >= 14 THEN Datamarts.dbo.Sales_OrderStatistics."Customer order number" ELSE NULL END)

It parses perfectly, but it counts every order number.

When I run a test on this by:

CASE WHEN Datediff(dd,Datamarts.dbo.Sales_OrderStatistics."Entry date Head",Datamarts.dbo.Sales_OrderStatistics."Requested delivery date 1") >= 14 AND Datamarts.dbo.Sales_OrderStatistics."Customer order type" IN ('H01','H02') THEN 1 ELSE 0 END

It also parses perfectly, ans delivers the correct values where they are supposed to be (1 whitin the criteris, else it is 0).

So are there anyone who can tell me why the count syntax dont work in BO?

Or if there are other ways to achieve the result I am looking for?

Gunnien

 

[Gunnien]

It's strange how much help I can find here. Even when I am reading my own issue posted a litle bit earlyer here.

One way round my problem is as simple as cretate a dimension by:

CASE WHEN Datediff(dd,Datamarts.dbo.Sales_OrderStatistics."Entry date Head",Datamarts.dbo.Sales_OrderStatistics."Requested delivery date 1") >= 14 AND Datamarts.dbo.Sales_OrderStatistics."Customer order type" IN ('H01','H02') THEN Datamarts.dbo.Sales_OrderStatistics."Customer order number" ELSE NULL END

Then I can do the counting in WEBI by COUNT([My dimension];DISTINCT)

But I will be grateful for other ways I can solve this issue.

Gunnien