Could anyone kindly tell me if I could use something like this in Designer
"SELECT emp_no, SUM(leave_accrued)
from emp_leave_years
WHERE scheme_code = 'Annual'
GROUP BY scheme_code,emp_no"
This query basically gives the total Annual leave for all employees over the years.
How can I get this kind of query into a Object of a Universe?
If this cannot be achieved, should I be looking at the idea of using a View?
I donât think this is the way you should be thinking about this. You donât âput queries into universesâ ⊠you put objects into universe, with each object having itâs own little piece of SQL in order to retrieve/use that object, and when a bunch of objects are used together, then it builds up a bigger and bigger piece of SQL.
To take your example, you would (should!) probably create the following objects:
*** This next part assumes a BASIC level of universe design knowledge - if you donât have that, then open Designer and have a play about ***
Object Name: Employee Number
Select: emp_leave_years.emp_no
Where: [leave empty]
Now if you drag both of these objects into your query panel it will produce a query that looks a little like what you wanted ⊠it will be something like this:
SELECT emp_no, leave_accrued
FROM emp_leave_years
WHERE scheme_code = âAnnualâ
You will get a list of ALL employees and their âaccrued leaveâ.
In Business Objects you can simply SUM the âaccrued leaveâ column now and you will get what you are looking for ⊠a TOTAL figure of accrued leave for all employees.
I know there are other ways of getting this, but this seems the most flexible Expert designers feel free to critique
I would always shy away from using the where clause in an object, itâs too easy to add two mutually exclusive objects which then cancel each other out.
I would use something like:
case emp_leave_years.scheme_code when 'Annual' then emp_leave_years.leave_accrued else null end
The idea is always to keep a Universe as flexible as possible and not create very report sepcific objects.
So, another solution for the same would be to create a Dimension Object for scheme_code.
Remove the where condition in the Employee Accrued Annual Leave object and make it
Object Name: Accrued Leave
Select: sum(emp_leave_years.leave_accrued)
Where: Empty
In the result objects pane, pull Emp no, schema code and Accrued Leave
In the Condition objects pane, simply pull in scheme_code and give the condition scheme_code = âAnnualâ
The resulting query will be
select emp_leave_years.emp_no, emp_leave_years.scheme_code,sum(emp_leave_years.leave_accrued)
from emp_leave_years
WHERE emp_leave_years.scheme_code = 'Annual'
GROUP BY emp_leave_years.emp_no, emp_leave_years.scheme_code
Can someone please throw some light on this :When to use the Aggregate functions SUM() etc in the Objects itself and when to leave this for the BO/Report to do aggregationâŠ
As BO too applies the Aggregate method on a Measure level âŠif we have set oneâŠ
If you ânormallyâ want to have the ability to see the discrete values then you would not apply an aggregate.
If you are aggregating most of the time, then put the appropriate function in the object definition.
I tend to always put an aggregate function on my measures, but there are always exceptions.
NOTE:
If you do not use an aggregate then you will get âlotsâ of data returned over the network and you will have large documents which require local processing power to calculate the aggregates.
If you do use an aggregate then your big powerful database server will give you the answer you asked for in the smallest possible data set and your report just has to display it.
Hi,
Apart from the explanation Ross gave for using aggregate functions on an object, there is another reason too - We risk getting incorrect results if aggregate functions are not used.
Before we go into the explanations, there is one important point to note -
Even if we set this option, the option works only at the report level, not at the data fetch level.
Case 1:
Let us consider this,
Following are the records in Table1 Day-Region-Sales
Sunday-R1-100
Monday-R1-100
We need region wise sales i.e., the result should be R1, 200
Suppose in the Universe, Region is a dimension object and Sales is a measure object defined as Table1.Sales.
Suppose the option âChoose how the measure will be projected when aggregatedâ is set to âSumâ and Sum() is not used in the definition.
Now, while building the report,
The option âRetrieve Duplicate Rowsâ is checked and Region and Sales are pulled into the result pane.
The query generated will be Select Table1.Region,Table1.Sales from Table1
The result set will be
R1, 100
R1, 100
So when aggregated against R1, the report will show R1, 200 - Correct result
The option âRetrieve Duplicate Rowsâ is unchecked and Region and Sales are pulled into the result pane.
The query generated will be
Select DISTINCT Table1.Region,Table1.Sales from Table1
The result set will be
R1, 100 - Incorrect results.
Using Sum() in the object definition itself would easily solved this issue.
Case 2:
The problem will also occur if there are multiple measure objects, with some having aggregate functions used in the definition and some not using aggregate functions in the definition, and these are pulled together in the result pane
Example, Day-Region-Sales-Discounts
Sunday-R1-100-20
Monday-R1-100-10
Suppose Discounts in defined as Sum(Table1.Discounts) in the Universe while all other definitions remain the same as in Case 1. We require total sales and total discounts by region i.e., R1, 200, 30
If Region, Sales and Discounts are pulled together, the query will be
Select [DISTINCT] Table1.Region,Table1.Sales,Sum(Table1.Discounts) from Table1 Group By Table1.Region,Table1.Sales
Result Set: (irrespective of whether Retrieve Duplicate Rows is checked or not)
R1, 100, 30 - Incorrect Result
This is why it is always advisable to use aggregate functions for measures
(BOB member since 2006-11-28)
***
(BOB member since 2005-11-21)
(BOB member since 2002-06-20)