Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help in creating unique ID's in cog report studio. 2

Status
Not open for further replies.

ejaycognos

Technical User
Dec 4, 2007
36
US
I have a requirement to create a report on a daily basis, in which the result data should appear only once.
For eg.,
If I need to create a daily financial report to XY company, in which I need to determine which customers have exceeded their duedate from paying their credit card bill.Their name should appear only once in the report ,thus should not be repeated next day.
how to do implement it ?? do we need to create a unique id for customer id's ? if so how to do it in report studio?
Thanks
 
A customer will always have an unique id in the source system. This is the way a transactional system works. The id may be a meaningless key or a number that is used for correspondence. Is is quite possible that this key is not defined in the framework model (in your case you may want it)

What do you mean by result data appearing once? Do you mean that if a customer is reported on monday his name will NOT appear on the report that is produced on tuesday?

If that is the requirement then the source system (transactional or datawarehouse) should store the date when excess first occured , or in case of a duedate you can filter data based on the comparison between the systemdate (= reportdate) and the defined duedate..

Ties Blom

 
Thank you for your reply.
Yes. If a customer is reported on Monday, his name should NOT appear on the report that is generated on Tuesday.
I already created a filter,to determine which records exceeds due date and it works fine(current date > due date) but only thing is previous day records are repeating the next day also,which should be avoided.

For eg
Report 1 - Daily report for 8/18
cust_id bankname due_date
100012 abc 08/17/09
Report 2 - Daily report for 8/19
cust_id bankname due_date
100012 abc 08/17/09
100059 abc 08/19/09

In this above report , the record with cust_id 100012 is repeated the following day also which should be avoided.
How to acheive it.
I appreciate your help.

Thanks


 
Provided that the report is produced EVERY day, you can simply change the filter and look back just one day:

current_date = _add_days(duedate,1)

Better still , is to make this prompt driven.

Create a prompt for the off-set and use the associated parameter like:

current_date = _add_days(duedate,?dateparameter?)

Your prompt query can then be populated with the following selections:

Use value display value
0 SameDay
1 Expired 1 day
2 Expired 2 days

etc

Ties Blom

 
Additionally, you can use Event Studio so that you only get the list of customers the FIRST time they appear no matter how many days pass.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top