on 05-03-2011 7:36 AM
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?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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?
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
User | Count |
---|---|
80 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.