cancel
Showing results for 
Search instead for 
Did you mean: 

Object in universe to fetch latest date

Former Member
0 Kudos

How to create an object which fetches latest date from the date column? My requirement is to use this object as filter in my Deski report. Actually I tried out the following code to create object in universe : max(table.date) and used this as filter in the report but this fetches all the date values available in the DB. Can anybody suggest a solution for this?

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Yes, It would fetch all the records. As it would calculate max (date) with the objects you select.

So what you should do is, use subqueries infilter.

I believe in DESKI subqueries can be used in filter as same as WEBI.

Regards

Gowtham

Answers (3)

Answers (3)

Former Member
0 Kudos

Hello.

One option is to create a function in the database that is just designed to fetch this date, along the lines of (and this is a SQL Server example, it will differ for other databases):


CREATE FUNCTION dbo.getmaxsaledate (inputno int)
RETURNS datetime
AS
BEGIN
     DECLARE @maxdate datetime
     SELECT @maxdate = (max(date) from salestable)
     RETURN(@maxdate);
END;

Grant execute privileges on the function to the user defined in your universe connection.

Create an object "Max Sale Date", defined as dbo.getmaxsaledate(1)

You can then use this object in your condition - Date Object equal to Max Sale Date for example

Former Member
0 Kudos

1. Create a object like table.date

2. Go to properties and g to properties tab.

3. Click on edit.

4. go to SQL

4. check the do not generate sql box.

5. Paste below sql into the query panel.

select max(table.date) from table

6. click on OK and Run.

7. Click on display to varify.

hope this helps.

Regards,

Rakesh K

Former Member
0 Kudos

Hi Rakesh,

Thanks a lot for your detailed explanation.

The logic you provided works at universe level but once I apply this object in conditions in report panel it fetches all the values.

Former Member
0 Kudos

Sri... you can either do it in you DB itself with a script running that would update in a column somewhere the max date. This way you dont have to calculate at the time of running report as this might slow your report.

If you want to add a object in universe then you will have to create a derived table that will have the max value. The Object from this Derived Table can then be used in filters or anywhere you wish

Former Member
0 Kudos

Did you check what is the value coming in the universe. Is it showing all the value or only one value.

If it is showing all the value in the universe that means your Max functionis not working. if so than you need to add this function in PRM file.

Mean while what is your BO version.

Former Member
0 Kudos

Hi Rachna,

In universe it is showing the max date but in the report filter it is not working as it is displaying all values.The version is BO XI R2 SP6 and can you let me know how to add the function in the PRM file?

Former Member
0 Kudos

Hi Sridhar,

Could you please check out the property of objects. In advance tab did you select the property "Can be used in Condition."

Also did you re-exported the universe after creating this objects.

Thanks

Rachna

Former Member
0 Kudos

Hi Rachna,

The option "Can be used in condition" is checked by default and yes I have exported the universe after creating this object.

Even the database fetches all the records since the SQL it is generating is not correct.

Former Member
0 Kudos

could you please let me know exactly where you are adding MAX funtion in object.

Former Member
0 Kudos

I define the code in select of the object(Max(table.date_column)) and use this object in the filters of the report

Former Member
0 Kudos

As stated above you have to use the MAX function in the sub-query. So, that it will filter the data according to Max Date.

The below code is showing as sample SQL that would get generate to achieve the requirement.

Select A,B From Table Where Date = Select Max(Date) From Table

The above code can be written with the help of sub-queries at the report level. OR create a predefined condition at the universe

level to generate a code similar to above one.

Regards,

Rohit

Former Member
0 Kudos

Thanks Gowtham and Rohit. I tried to do it from Deski report by cerating a sub-query.

Once I drag my objects and apply filter and click on sub-query it opens another query panel.

How are the filter objects need to be selected here so that proper query gets generated?

If possible can you guys let me know how can it be achived in the universe level?

Former Member
0 Kudos

Yes, You can do this in universe level also.

You can create a conditional object and can define as follows:

@select(time/date)= ( select Max (date) from table_date)

And the explanation you have mentioned for creating subquery is correct in deski. You can try that. If not, you can create it in universe level as I mentioned above. So you can re-use that condition as rquired in multiple reports.

Regards

Gowtham