Using Over() and Partition by() in object defination in UDT

building semantic layer
1

Hi,

I am trying to create a measure object with defination like

sum(@Select(Sales\Quantity) * @Select(Sales\Unit sale price)) over (partition by @Select(Sales\Order number))

it is giving me parsing error, If I remove the partition by and make the over block empty it is parsing fine. But I need to include Order no in this case.

sum(@Select(Sales\Quantity) * @Select(Sales\Unit sale price)) over ()

Could you please help me resolve this issue, I need the solution asap and I am stuck. please help.

Regards,
Rajesh Suyal

rajeshsuyal_06 (BOB member since 2011-08-09)

2

Anyone have any solution for this problem, may be it is very simple, but I am not able to figure it out. Need your help on this one.

rajeshsuyal_06 (BOB member since 2011-08-09)

3

What database and version and Service Pack of BO are you using?

Have you tried with the SQL rather than using @Select?
In any case you will need to post the SQL for anyone to help you further.

Mak 1 :uk: (BOB member since 2005-01-06)

4

Moderator note:
Please do not bump your post until at least one business day has passed.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

Hi Mak 1,

Good Morning, below is the Object SQL from object definition.

sum(( SUM(Sales.Quantity)) * (avg(Sales.“Unit sale price”) )) over (partition by (Sales.“Order number”))

I am using SQL Server 2008 as database and Universe Designer 3.1 version 12.6.0.1596.

Any help to resolve this would be much appreciated.

Regards,
RAjesh Suyal

rajeshsuyal_06 (BOB member since 2011-08-09)

6 
sum(( SUM(Sales.Quantity)) * (avg(Sales."Unit sale price") )) over (partition by (Sales."Order number"))

Does this code work in SQL server? Is SQL Server 2008 supported by your patch level of 3.1?

Mak 1 :uk: (BOB member since 2005-01-06)

7

Hi Mak,

If I add a group by in SQL it works, but I don’t know if we can add group by in UDT.

select sum(( sum(Order_details.Quantity)) * (avg(Order_details.unit_sale_price)))
over (partition by (Order_details.order_number ))
from [gosales].order_details order_details
group by Order_details.order_number

Regards,
RAjesh Suyal

rajeshsuyal_06 (BOB member since 2011-08-09)

8

This should help:-

https://bobj-board.org/t/110489/11

Mak 1 :uk: (BOB member since 2005-01-06)

9

Thank you Mak, I have gone through this post, so you mean we cannot use group by in Object definition. If I remove the aggregation from Order_details.Quantity (i.e. Sum) and Order_details.unit_sale_price (i.e. Avg) then I don’t require group by clause. see below SQL.

then it works in UDT, is it only because of ‘group by’, or my assumption is in correct.

select sum(( (Order_details.Quantity)) * ((Order_details.unit_sale_price)))
over (partition by (Order_details.order_number ))
from [gosales].order_details order_details

Regards,
RAjesh Suyal

rajeshsuyal_06 (BOB member since 2011-08-09)

10

The post states clearly when and where a group by is applied. To test properly you should be building the query in Webi.

Mak 1 :uk: (BOB member since 2005-01-06)

11

Hi Everyone,

I am still having similar issue while creating a measure object in UDT. I have a object defination like;

AMOUNT = ( F_COMP_MONTH.COMP_AMT ) / ( Sum(( F_COMP_MONTH.COMP_AMT )) )

here the Amount object is giving error while parsing.

Regards,
Rajesh Suyal

rajeshsuyal_06 (BOB member since 2011-08-09)

12

Does it work using SQL?

In any case, not sure exactly what you are trying to do, but this looks like something that should be calculated at the report level, IMO.

Mak 1 :uk: (BOB member since 2005-01-06)

13

I checked the object SQL In Query Panel, it is not including group by at the end. That might the issue.

I also checked the universe parameter property. It shows Group by for ‘Distinct Values’

I have attached both the screen prints for reference.

Regards,
Rajesh Suyal
Universe Parameter Property Screen Print.jpg
Query Panel Screen Print.jpg

rajeshsuyal_06 (BOB member since 2011-08-09)

14

You are trying to deal with two different levels of aggregation in one object. If you are hoping to generate a ratio or %, this is best done at the report level.

Mak 1 :uk: (BOB member since 2005-01-06)

15

I want to create an object in Universe, can this be done at Universe Level?? What is the purpose of Universe Parameter Property ‘DISTINCT_VALUES’ which is set to ‘GROUP BY’.

In summary is there any way to achieve this in UDT 4.1 Version: 14.1.2.1121.

rajeshsuyal_06 (BOB member since 2011-08-09)

16

To generate a group by you need a dimesnion object in the query and a SQL aggregation statement around the measure being created.
It looks to me like you wiah to process the rows at a transaction level and then divide by the aggregated total of all rows.

If it is possible in the universe the measure would have to be database delegated.

This relates only to the SQL for LOV.

Mak 1 :uk: (BOB member since 2005-01-06)

17

as Mak 1 stated better to do in report. for percentages and averages to ensure proper results you should grab the objects necessary to calculate the percentage or average into the report, then do a variable to get the calculation. sure you may be able to find a way to get this to work in a universe object, but depending on what other objects are selected along with it the results could be inconsistent. i’ve just learned this through experience.

erik.stenson :us: (BOB member since 2012-07-30)