Hello,
First of all, I am using BusinessObjects 6.5.1.
I have a litle problem with a mesure in my sales order statistics universe. I am interested in the number of customers orders with a horizom greater then or equal to 14 days. Each customer order may have more than one order line so I am interested in counting the distinct number of “order nummers”.
I therefore have created two objects/measures in my universe.
The property of both is measure and function is sat to COUNT.
And here is the SQL-syntaxes for my two measures:
NbOfOrdersGrth14days (Distinct number of “order numbers” >= 14 days)?
COUNT(DISTINCT CASE WHEN Datediff(dd, @Select(OrderStatistics\Entry Date Head), @Select(OrderStatistics\Requested Delivery Date 2 (Head)))>=14 AND @Select(OrderStatistics\Customer Order Type) IN ('H01','H02') THEN @Select(OrderStatistics\Customer Order Number) ELSE NULL END)
And:
NumberOfOrders (Distinct number of “order numbers”):
COUNT (DISTINCT CASE WHEN @Select(OrderStatistics\Customer Order Type) IN ('H01','H02') THEN @Select(OrderStatistics\Customer Order Number) ELSE NULL END)
Anyone who can tell med why those two syntaxes don’t work when I am trying to use them in WEBI while this syntax from the datasource works perfect when I run it from the source with SQL-analyzer?
COUNT(DISTINCT CASE WHEN Datediff(dd,[Entry date Head],[Requested delivery date 2 (head)]) >= 14 AND [Customer order type] IN ('H01','H02') THEN [Customer order number] ELSE NULL END)
First of all, I am using BusinessObjects 6.5.1.
I have a litle problem with a mesure in my sales order statistics universe. I am interested in the number of customers orders with a horizom greater then or equal to 14 days. Each customer order may have more than one order line so I am interested in counting the distinct number of “order nummers”.
I therefore have created two objects/measures in my universe.
The property of both is measure and function is sat to COUNT.
And here is the SQL-syntaxes for my two measures:
NbOfOrdersGrth14days (Distinct number of “order numbers” >= 14 days)?
COUNT(DISTINCT CASE WHEN Datediff(dd, @Select(OrderStatistics\Entry Date Head), @Select(OrderStatistics\Requested Delivery Date 2 (Head)))>=14 AND @Select(OrderStatistics\Customer Order Type) IN ('H01','H02') THEN @Select(OrderStatistics\Customer Order Number) ELSE NULL END)
And:
NumberOfOrders (Distinct number of “order numbers”):
COUNT (DISTINCT CASE WHEN @Select(OrderStatistics\Customer Order Type) IN ('H01','H02') THEN @Select(OrderStatistics\Customer Order Number) ELSE NULL END)
Anyone who can tell med why those two syntaxes don’t work when I am trying to use them in WEBI while this syntax from the datasource works perfect when I run it from the source with SQL-analyzer?
COUNT(DISTINCT CASE WHEN Datediff(dd,[Entry date Head],[Requested delivery date 2 (head)]) >= 14 AND [Customer order type] IN ('H01','H02') THEN [Customer order number] ELSE NULL END)