cancel
Showing results for 
Search instead for 
Did you mean: 

Group by issue when MAX & MIN functions used

Former Member
0 Kudos

Hi All,

I have a requirement like this:

e.g. I have a employee table with following columns (empno, ename, state,date, sal). Here I have to create a dashboard analytic/webi report like this.

1. I have to display employee details (ename,eno,sal,...) with given date i.e start date & end date of any month like dat (single prompt). For start date the sal should be given date - 1. For this I know we can do it To_DATE() but in universe I guess it is not available. How to do it in universe for start date.

My backend database is SQL SERVER.

2.I have to display employee details with the max (sal) and min(sal) for the given period (start date & end date). For this I have created two measure objects in universe (max(sal) and min(sal)) but when I used in query panel w.r.to other dimensions like empno, ename then groupby function is used, which displaying all the data but max(sal) and min(sal) columns are not properly calculated out for the given time period.

Any help is highly appreciated.

Thanks in Advance,

Suresh Aluri.

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hello Suresh,

Regarding question 1 you can use the to_date function in the syntax of een universe object infact you can almost use any sql function that is known by your database, just like you can use the min and max functions.

Question 2 can be solved with a derived table in the universe, write a query in the derived table that has the correct group by and join it to the table employee on empno.

e.g:

"select empno, max(sal) as max_sal, min(sal) as min_sal

from employee

where date >= @Variable("1) Start Date?")

and date <= @Variable("2) End Date?")

group by empno, date"

Create 2 (new) objects from the derived table with max_sal and min_sal and use them in the deski query.

Regards

Raymond

Answers (0)