Drill Thru Using Date Ranges

[Nuffsaid]

Hi All,

Fairly new to Cognos. Using Report Net / Report Studio.

Trying to Drill Through from a summary report to a detailed report using date ranges from the summary report. Not having much success. All help appreciated!!

In order to demonstrate what I'm trying to do follow these steps using the sample Go sales and retailers.

First create the DETAIL report:

Create a simple list report and add the following query items;

Product line
Product type
Product name
Order date
Order number
Quantity
Revenue
Staff name

Add 2 filters as follows;

[gosales_goretailers].[Sales reps].[Staff name]= ?Name?

[gosales_goretailers].[Orders].[Order date] in_range ?Detail_Date_Range?

Save the file as DETAIL.

Next, create the SUMMARY report;

Create another simple list report and add the following query items;

Staff name
Revenue

Add a prompt (parameter) that will prompt the user for a date range and filter based on the user's response.
i.e. [gosales_goretailers].[Orders].[Order date] in_range ?Users_Date_Range?

Save the file as SUMMARY.

HERE COMES MY PROBLEM.....

Make the detail report a drill through based on Staff name and the DATE RANGE provided by the user in the SUMMARY report.

I have no problem "hooking up" the Staff name in the SUMMARY report to the ?Name? parameter in the detail report but I
cannot "pass" the date range. I've tried everything I can think of.

I tried adding the order date query item to the SUMMARY report work area and hooked it to the ?Detail_Date_Range? parameter.
I then CUT it from the work area because I don't want it displayed on the summary report.
Two things happened when I did this.

1) It screwed up my aggregation in the Summary report.
2) The Detail report only returned records for the first date in the range.

Idea's ????



Nuffsaid.

 

[Draoued]

When you drill-through from your summary report to the detail report , it selects the order-date of the selected line where you click to drill-through.
It contains only one date not a range , that s why you don't have an order date range in your detail report.

 

[Nuffsaid]

Thanks for the reply Draoued.

Is there no way to pass a range when drilling through? Or am I beating a dead horse?



Nuffsaid.

 

[Draoued]

I would try something like:
Adding a fake field in the query containing the Date range concatenated like "20050101-20050201" , this value would be the same for all rows retrieven.

Then in the detail report the Order date would be using this field with an extraction of the dates.
the filter in detail report will be something like :
order_date between ( to_date(left(?date?, 8)) and ( to_date(right(?date?, 8))

 

[mfgf]

Hi Nuffsaid,

You can get this to work fairly easily if you do the following:

1. Change your detail report filter to be
[gosales_goretailers].[Orders].[Order date] between ?d1? and ?d2?

2. Change your summary report filter to be [gosales_goretailers].[Orders].[Order date] between ?d1? and ?d2?

3. Add two new items to the query of your summary report called d1 and d1. Drag your ?d1? parameter into the expression for d1, and the ?d2? parameter into the expression for d2.

4. When setting up the drillthrough from Staff Name, it will now pick up the 3 parameters from the detail report - ?name?, ?d1? and ?d2?, but it will try to pass the Staff Name item into all three. Change ?d1? to receive your d1 query item, then change ?d2? to receive your d2 query item.

This seems to work fine with GO Sales and Retailers.

Best regards,

MF.

 

[Nuffsaid]

Hi MF,

That's exactly what I ended up doing, although I went through a little aggrevation. I find the Cognos docs a little lacking in so far as examples go. As I mentioned in my previous post ReportNet is a new experience for me (Crystal was my previous claim to fame). I appreciate your reply however.

I'm sure "I'll be back" for more advise....

Thx


Nuffsaid.