Aggregate function using having

[Former Member]

Can somebody tell me In universe designer how to write a condition like

in select:

sum(Shop_facts.Amount_sold)

and since aggregate function is used in select I need to use "having" clause instead of where.

Basically I need to use something like

sum(Shop_facts.Amount_sold) > 0 in the condition.

[Former Member]

Hi Shridhar,

aggregate functions will not allow to write in where condition

but we can assiagn LOVs for Measure objects

goto object(Amountsold) properties and click on Edit drag and drop the Amountsold object in condition pane and select operator as greater than and type 0

"now if you can see the sql it will display having sum(amountsold)>0"

click and save

hope it will use ful to you

try it

Thanks

[Former Member]

Hi Sand,

Adding to your Point

There is no normal circumstances under which we will be able to use this condition through customized LOVS.

The customized LOVS will not work in this case because our requirement is to build a standalone filter that will filter all the records based on a value which is greater then a certain value and that also in u201CHAVINGu201D and not in the where Condition.

Basically the measures are defined to be used with dimension Objects with an aggregation

The only way to achieve this functionality is provide a Constant value at report level.

I have tried modifying the LOV although it gives me no issues on parsing however it does not fetch correct data at report level

Same happens if I try to create a derive table out of the sql that is been generated by the report SQL.

I am performing some more tests but I am pretty sure this may not be achievable through Designer

Cheers

Kultar

[Former Member]

Thanks a lot Rachna,Sand and Kultar for your really valuable inputs.Can I conclude this is not possible from Designer then?

[Former Member]

I could not figure out a way to achieve this from designer.

[Former Member]

hi Sridhar,

while you are using measure, i think you may also use a dimension for grouping in your query.

simply i mean your query contain atleast a dimension along with measure.

For example, your query contains 2 objects Article_Id (dimension) and Amount_Sold (measure)

now you want to create a filter sum(Shop_facts.Amount_sold) > 0.

You could try the below code in filter definition

@Select(Shop_facts\Article_id) IN (SELECT Article_id FROM Shop_facts GROUP BY Article_id HAVING sum(Shop_facts.Amount_sold) > 0)

you can place your required dimension instead of Article_id based on your query requirements

Hope it helps.

Regards,

Vamsee

[Former Member]

You would write it as a predefined condition (filter) or as a condition at the report level.

[Former Member]

Thanks for your reply.Currently I have handled the condition at the report level and since there are millions of records I thought of moving the calculations to universe level to speed up the performance.

Is there no way of handling this at universe level?

[Former Member]

By doing it in the query, you are handling it at the correct level. You are returning the data that has been restricted correctly.

Filtering at the report itself isn't ideal but would work, but using a condition in the data provider against the measure will generate your having statement.

[Former Member]

Thanks a lot for your valuable suggestion. This option looks fine at the report level but can you confirm if we cannot achive this anyway at the universe level?

[Former Member]

You can do it in the universe as well.

Click on the objects property.

In the where clause you cn right this condition "sum(Shop_facts.Amount_sold) > 0"

Now clcik second tab in the LOV click on edit

You can find Query Panel. If you click on SQL you can find that having clause has automatically came up.

Second option you can add having clause in this sql sataement, save the sql and run it. The value for the column will automatically filter "sum(Shop_facts.Amount_sold) > 0"

Hope this what you are looking out for.

[Former Member]

I tried but get an error "An aggregate function is used in the WHERE clause" using the first option mentioned by you.

Second option gives "SQL error".Can you please expalin in detail?

[Former Member]

- Click on the objects property.

- Now clcik second tab in the LOV, click on edit LOV.

- You can find Query Panel. If you click on SQL.

- Add having clause in this sql sataement, Parse the SQL.

- Save the sql and run it.

- Result/ LOV automatically filter the value "sum(Shop_facts.Amount_sold) > 0"

Rachna

[Former Member]

Adding sum(Shop_facts.Amount_sold) > 0 in the sql, it doesn't make any sense.

You can add this condition in the SQL of the report.

I think predefined filter may solve your purpose but not sure because currently i don't have BO environment to test it.

Thanks,

SandS

[Former Member]

Hi Sand,

Thanks for your input.

We have already disscused about all the location in the reports where we can put this condition.I would suggest you to please read out the complete conversation related to this thread.

As he was looking out for some option in the universe so I have given him my opinion. As we have done few LOV customization by htis method. May be for you it will not make any sence.

We have already discussed about predefined filter in earlier post.

Thanks for your inputs.