LiveReports

[SidBurns]

Firstly, sorry if I am posting in the wrong area but I'm in dire need of some help!?!

I am currently writing a LiveReport, the SQL of which I have completed (I think). I am trying to get a list of results back by 'Modified' or 'Created' Date as Date is not good enough. Some of the documentation was scanned on back in 1999 but want the date it was modified or created on Livelink. Is this possible using the User Input Data Type and if so, how? If not, how would I go about it?

Thanks very much in advance,

Craig.

 

[appnair]

Maybe this will help understanding it.

select createddate,modifydate from dtree where modifydate <='01-FEB-2009'

If this is a valid query if you want to replace the hard code that is where you use the UserInput1

So in the First prompt you would select the date type and enter a prompt "Enter Date" and then in the query you will say

select createddate,modifydate from dtree where modifydate <=%1
and in the parmeters passing section you pick 'UserInput1'
so when the query is submitted livelink takes the query,knowing that it is date submits it to the underlying database(the reason for this is databases are very particular about the datatypes and web form can only do string serialization,so it is just a way of providing input with the right data type

So it goes without saying that you have to devise the query using your intelligence,then all the LR is giving you is the ability to run it parametrized.


Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer and probably certfiable,Livelink ECM Champion 2008

http://www.linkedin.com/in/appunair

 

[SidBurns]

Thanks appunair. I've tried what you've suggested but luck so far. Here is the current SQL:-

select dtree.dataid as "Unique ID",k.valstr as "Sort Code", c.valstr as "Account Number", f.valstr as "Customer Number", h.valstr as "Document Type", l.valstr as "Transaction Type", b.valstr as "Date Scanned", e.valstr as "Date Account Closed", a.valstr as "Batch ID", i.valstr as "FileNet ID" from dtree, llattrdata a, llattrdata b, llattrdata c, llattrdata d, llattrdata e, llattrdata f, llattrdata g, llattrdata h, llattrdata i, llattrdata j, llattrdata k, llattrdata l, llattrdata m where ((dtree.dataid = a.id and dtree.versionnum = a.vernum and a.defid = 342711 and a.attrid = 10) and (b.valdate = %1 and b.id = dtree.dataid and b.defid = 342711 and dtree.versionnum = b.vernum and b.attrid = 9) and (dtree.dataid = c.id and dtree.versionnum = c.vernum and c.defid = 342711 and c.attrid = 3) and (dtree.dataid = d.id and dtree.versionnum = d.vernum and d.defid = 342711 and d.attrid = 13) and (dtree.dataid = e.id and dtree.versionnum = e.vernum and e.defid = 342711 and e.attrid = 14) and (dtree.dataid = f.id and dtree.versionnum = f.vernum and f.defid = 342711 and f.attrid = 8) and (dtree.dataid = g.id and dtree.versionnum = g.vernum and g.defid = 342711 and g.attrid = 12) and (dtree.dataid = h.id and dtree.versionnum = h.vernum and h.defid = 342711 and h.attrid = 4 and h.valstr like 'BFM%%') and (dtree.dataid = i.id and dtree.versionnum = i.vernum and i.defid = 342711 and i.attrid = 11) and (dtree.dataid = j.id and dtree.versionnum = j.vernum and j.defid = 342711 and j.attrid = 7) and (dtree.dataid = k.id and dtree.versionnum = k.vernum and k.defid = 342711 and k.attrid = 2) and (dtree.dataid = l.id and dtree.versionnum = l.vernum and l.defid = 342711 and l.attrid = 5) and (dtree.dataid = m.id and dtree.versionnum = m.vernum and m.defid = 342711 and m.attrid = 6))

Where would/should I place your code? If you can, please base it on me trying to get documents back from yesterday (28/04/2009).

Thanks again,

Craig.

 

[appnair]

What is your database ? as in Oracle or SQLserver

(b.valdate = %1
I am guessing that you have a category with a adate defined that is what you are filtering off and note on dtree.createdate so I really cannot comment on what you are trying to do.

If you now create a date datatype as input when it runs it will fill the value.Note livelink dates have date /time stamps so you have to use a date function to get anything worthwhile

Instead of the complicated llattrdata joins just take the dtree part and run your query then oncve you know you have documents pertaining to a certain date you can run the extra stuff

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer and probably certfiable,Livelink ECM Champion 2008

http://www.linkedin.com/in/appunair

 

[appnair]

also I am confused why you are tying a llattrdata or metadata query into trying to see how old a document is?

I am assuming SQLserver here so syntax is for that

When a document is created in system its datestamp is put in createdate


So look at the logic of the query

Give me all documents in livelink that was created before today

select dataid,name ,getdate()
from les971.livelink.dtree where createdate <= GETDATE()

Note GetDate() is a sqlserver function that returns current date

I could base my query on the modifydate as well as well as filter it on document subtypes

select dataid,name ,getdate()
from les971.livelink.dtree where modifydate <= GETDATE()
and DTree.SubType=144

So if you wanted to specify your date 28/04/09

select dataid,name ,getdate()
from les971.livelink.dtree where modifydate <=
CAST('2009-04-29' AS datetime)
and DTree.SubType=144

Note that I use the cast function to tell sql server that I am providing you a date .This is perhaps one of the differnt hundred ways that a date can be provided to the database but mind you if a column is defined as a date you cannot just expect to pass "2009 apr 28" you will have to use some kind of input mask to do it.

Pls check "sql server data time manip" in google so you get a better handle.



This hard code is what you are supposed to replace with %1.If you are a beginner try to formulate small queries and then understand how dtree ties with llattrdata.



Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer and probably certfiable,Livelink ECM Champion 2008

http://www.linkedin.com/in/appunair