How do we setup a query to look at items from a previous date?

[smccoy]

We have the need to create some delta reports based on the history of item(s). The reporting part is well undestood. We need a method to get at the historical data on an item from a query. How do we do this?

[mrump]

Hi Sean,

I do not know what you mean by "We need a method to get at the historical data on an item from a query", but there is a computation you can use to get any historic value from a given Item in a report.

My previous post

http://communities.ptc.com/thread/44308

describes a report that uses this computation to gather a 10 weeks history of some metric values for a single item and also transfors this data set into a trend chart.

If you take a look at the report recipe attached to that post, you'll find some code that generates a value-History-table using the computation.

HTH Matthias

[smccoy]

Hi Matthias,

Thanks for the quick reply. I've talk this over with our team and here's better phrasing of the need.

What we need to be able to do is to have a query which will return a document/list of documents. However, we do not want the current version of the documents. We want the documents returned to be the documents as of a date or a label. The manual way to do this is to select a document, then select “View document as of”, and then select the desired date or label.

The ultimate goal is to use this "View Document as of" capability somehow in a query... So that that resulting output can be included in a Report. And there are multiple flavors of the reporting on this data.

-Sean

[mrump]

Hi Sean,

IMHO what you want is not possible with queries, sorry .

As far as I understand Integrity a query more or less only returns a set of IDs; based on a filter and the current DB table content. To some extend the Filter can utilize the Items History values but still the result is never an "Object".

Reports are some what different as they can actually "process" the content of the item. If you run a historical report ("runAsOf") the report will even process the historical content of the item.

So in your case for example, let's assume you have a query "all closed documents".

That query will always give you a set of document's IDs that are in closed state at the very moment the query runs.

If you use this query in a simple "Details-Table" report and you run this report e.g. as of 01.01.2011. You will get a report that only contains the IDs of documents that were in closed state at 01.01.2011. Of course you can extend this Report with some additional Buttons that will run a "Document Content" report for the given ID and time frame, but I'm afraid that is not possible in one single "embedded" report.

HTH Matthias