selecting the latest submission

[neil2grow]

Hello All
I am new to cognos impromptu and i am trying to generate a report for file submissions.There can be multiple sumbissions of files .I would like to generate the report only for the latest submission of the file .Say if a file by name 'f10' is submitted 10 times during different dates.I would like to display the latest version of the file 'f10' and its related fields rather then all the 10 files.Is there any way we can achieve this in cognos impromptu

 

[drlex]

Neil2Grow,
On the basis that I understand your query, the filter on the report should suffice. (Report->Query->Filter tab)
Create a report that has the fields you want. Open the filter. With the default setting as detail filter, create a filter with the relevant filename field - e.g. file_name = "f10"
Now change the filter type (drop down menu) to summary and from the summariess folder in the filter component list on the lhs, select maximum. Into this function, put the submission date field - e.g. maximum(submission_d) and then add the component 'for' and then the file_name field.
Hopefully your table will be indexed by file_name to keep response time acceptable. If the data comes from multiple tables, speed could be an issue where summaries are used.
HTH
lex

 

[neil2grow]

lex
Thanks a bunch for your suggestion,but the problem i am having is ,the nam eof the file is not static.There are a 1000 files and i cannot do this for each file .I may be wrong but can you please advice on this issue

thanks
neil

 

[drlex]

neil,
In that case, could you use the prompt facility on the report to get the user to input the name of the file required?
Change the detail filter to remove the static file name ("f10&quot and click on Prompt Manager on the lhs (available components). It should open a new window where you can enter the detail of the field. When you now run the report, it should prompt for a file name and select only the maximum date record for the matching filename.
lex

 

[neil2grow]

lex
Thats what i did.i have a prompt for the file and a couple of other prompts which goes with it.but when i try to apply the summary filter ,i dont have the date in the right format in the table,i am combining 2 fields and applying to_date function to get the date and trying to do
maximum(to_date('')) for file and it bombs(giving error in the expression)
neil

 

[drlex]

Neil,
can you break it down into parts to test?
e.g. if you have a month field and a year field, then will maximum(month) for file_name work? Will Maximum(year) for file_name work?
There are a number of functions that impromptu offer, some work at the database level, some at impromptu level and some at both. It would appear that reports can crash/timeout if you combine all types of function.
Try using other date functions to make your date field.
lec

 

[neil2grow]

lex
will try
thanks
neil