How do I include sysdate and to_char function in a condition

[tdfreeman]

I am a new Business Objects developer. I have not had a class in Business Objects and I don't know much about it. My new task is to modify existing Business Objects reports. All this is to say that I may need a more detailed answer than others may need.

I need to add a condition to a query. In SQL my condition would be:

SELECT FISC_MNTH FROM MF_CLDR_DT WHERE CLDR_DT = TO_DATE(TO_CHAR(SYSDATE, 'MM/DD/YYYY'), 'MM/DD/YYYY');

I know how to put the FISC_MNTH field in the Result Objects pane and to put the CLDR_DT in the conditions pane. I added Equal to to get "CLDR_DT Equal to", but I can't get any further. How do I add the to_date function and the sysdate variable as specified above? None of the operands on the left look to work with my request.

Thank you for your help.

Tammy

 

[jimbopalmer]

I am assuming you can add to the universe, then you can make TO_DATE(TO_CHAR(SYSDATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') an object like all others and it will just be a blue ball in the data provider screen. By the way, trunc(sysdate) may be easier. I tried to remain child-like, all I acheived was childish.

 

[blom0344]

Hello TDFreeman,

Modifying existing reports can be a bit hazardous if you're not familiar with universe design. Always strive to solve SQL issues by modifying the universe you are working on (like jimbopalmer also suggests) . This is because of a couple of reasons: changes to a universe touches all respective reports, adding new objects will not cause problems with existing reports. Second reason is that if you modify the SQL by hand (activiting the 'do not generate SQL' mark) , you will LOSE all modifications if you later on start dragging in some new objects.

Take some advice; spent some time reading the excellent PDF manual concerning universes (under help if you start up designer) , because universe design is really what Business Objects is all about.

blom0344