records count

[PradeepKumarP]

Hi,

we have a new requirement. we have a set of Products (more than 50) and for every Product, we need to show the results in a different page. we need to show exactly 50 records for for each Product(means, each page should show 50 records exactly). we have achieved the showing in different page with page set and i have created a data with the condition "running-count ([ORDER_TYPE] for [Product]) < 51". this assures that no page will show counts more than 50.

now comes the tricky requirement. above results will be shown based on a selected prompt date. and the requirement is that, if the selected date doesn't have 50 records (less than 50) on that day, report should look for one day earlier to selected date (prior day of selected date) and this should keep on going till the records count should be 50.

don't know how to achieve this. any expert's help would be greatly appreciated. we have already spent almost a week on figuring this out but is of no use.

waiting for a solution for this.

[MFGF]

Hi,

we have a new requirement. we have a set of Products (more than 50) and for every Product, we need to show the results in a different page. we need to show exactly 50 records for for each Product(means, each page should show 50 records exactly). we have achieved the showing in different page with page set and i have created a data with the condition "running-count ([ORDER_TYPE] for [Product]) < 51". this assures that no page will show counts more than 50.

now comes the tricky requirement. above results will be shown based on a selected prompt date. and the requirement is that, if the selected date doesn't have 50 records (less than 50) on that day, report should look for one day earlier to selected date (prior day of selected date) and this should keep on going till the records count should be 50.

don't know how to achieve this. any expert's help would be greatly appreciated. we have already spent almost a week on figuring this out but is of no use.

waiting for a solution for this.

Not sure how viable this is, but my initial thought is that you could add a filter based on the date <= prompted date value, order the results in descending order of date within each product, and apply the filter for running-count < 51. The date filter would need to be before aggregation and the count filter would need to be after.

Might be worth a shot?

MF.

[PradeepKumarP]

Thanks for the quick response MFGF,

we thought of this. the prompt date is an interval period. what i did was initially in the filter, i kept a condition like date <= (prompt  start date - 10) (just in case if we have 50 records for each product in those 10 days) and date < prompt end date. still we couldn't get 50 records. we are not sure if we up to how many we need to go back to get those 50 records.

and the thing is it should go back only for the products that don't have 50 records for selected period. if we do this, it is continuously looking for all the dates that are less than prompt start date and the performance is being hit.

thanks again for your thought.

[MFGF]

Thanks for the quick response MFGF,

we thought of this. the prompt date is an interval period. what i did was initially in the filter, i kept a condition like date <= (prompt  start date - 10) (just in case if we have 50 records for each product in those 10 days) and date < prompt end date. still we couldn't get 50 records. we are not sure if we up to how many we need to go back to get those 50 records.

and the thing is it should go back only for the products that don't have 50 records for selected period. if we do this, it is continuously looking for all the dates that are less than prompt start date and the performance is being hit.

thanks again for your thought.

Yes - performance was a concern when I thought of using this approach - hence my uncertainty about the viability.

Is the data in a data warehouse? If so, is there any scope to have the ETL process mark the 50 rows for each product as they are being written?

MF.

[PradeepKumarP]

MF,

most of our reports are SQL driven. we don't use packages for most of our reports. this is also SQL driven. using a data source from Cognos workspace we connect to the data mart and fetch the data. i tried making this change and ran the report for prior day, it is almost 3 hours i ran it and it is still executing.

Thanks for your concern on this. i really appreciate your help.

[MFGF]

most of our reports are SQL driven. we don't use packages for most of our reports.

I'm not even going to ask why... :)

[cognostechie]

I'm not even going to ask why... :)

Me too  :)

[BigChris]

Oh go on then, I'll ask...why aren't you using packages? Do you use Framework Manager to manage your joins between tables etc?

[PradeepKumarP]

no Chris,

we don't use Framework Manager for most of our work. we create an almost a replica of client's transnational db as our data mart. and then using different data source connections for different environments, we will fetch the data. this is how most of our work is done. only now and then we use packages as we are trying to implement this in future in full fledged process.

about my current issue, still trying to same things by checking the performance by hard coding the time to different times to see which one comes fast. but, no improvement at all.

Thanks