Advanced Prompts, Passing Variables between 2 reports 1

[in2view]

Hi all, This is my first post so please excuse my ediquette.

I have successfully passed information from a detail report to a summary report using a "dataset" filter in the final report (Report 2) from a data subset filtered in Report 1.

My question is, can I pass the "Date To" and "Date From" variables captured in the first report prompts to the second report for display without having two prompts run???

I have tried storing the variable in columns in Report 1, but I am not sure how I can transfer them to report #2.

I would appreciate any help on this, as it would help in many reports I need to create.

Thanks much.

 

[griffindm]

This is a Cognos sample macro from their knowledgebase that accomplishes this via saving the data from the first report as Pipe delimeted ASCII data into a file, and then doing a low-level file open and reading that data into the prompt of the second report. While the example uses two reports with identical prompts, the logic can be applied to your situation with some adjustments.

KB:74227
Date Published: 07-JUN-99

Problem Description

You are trying to pass prompt values from one report to another; both the reports have identical prompts. You do not want to enter the prompt in the second report and the
subsequent reports. It is possible to select multiple values from one of the prompts. Is it possible to capture the prompt values and pass it to the second report?

Solution Description

The following macro example will accomplish this task.

EXAMPLE

Sub Main

Dim objimp As Object
Dim objrep As Object
Dim bf As String
Dim pv As String
Dim tv As String

' start impromptu if it's not already running.
Set objimp = CreateObject("Impromptu.Application&quot
' open catalogue
objimp.OpenCatalog "d:\program files\cognos\impromptu 5.0\imp.5.0 samples\reports\great outdoors sales data.cat", "Creator",,,,True
' show impromptu
objimp.Visible True
' open report, and prompt for picklist values
Set objrep = objimp.OpenReport("d:\report1.imr&quot
' save selected values to an output file
objrep.ExportASCII "d:\report1.csv", 0, 0, "|", 0
' close report
objrep.CloseReport

' open output file
Open "d:\report1.csv" For Input Access Read As #1
' read to the eof
Do While Not Eof(1)
Line Input #1, bf
tv = GetField (bf, 2, "|&quot
pv = pv & tv & ","
Loop
' close output file
Close #1

' truncate extra comma from the end
tv = Left(pv, Len(pv)-1)

' construct prompt variable
pv = tv & "|1996-03-26"

' display prompt list
Msgbox pv

' open report
Set objrep = objimp.OpenReport("d:\report2.imr", pv)

Set objrep = Nothing
Set objimp = Nothing

End Sub

This is one method. Others might involve a hotfile with filter joins based on a BETWEEN clause of the dates in the hotfile.

Hope this helps.

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[in2view]

Thanks Dave,

It sort of helps, but I was trying to avoid using macros, hence the use of the dataset. To enable users to manipulate more than one report without macros was the purpose of applying the dataset functionality. I initially had the first report being opened by a macro on the second (final) report and saving the data to a hotfile, then calling the hotfile as data for the final report.

Since most users will not have macro writing experience or ability, we were hoping to avoid macros and hotfiles for simple report combinations.

I am using Admin 5.0, does the newer version (Ver.7) have prompt referencing abilty as I require, or is there another way to do this?

I thought that maybe if I set the prompts values (for ex. "Start Date" and "End Date&quot to columns in the first report (did that), I could reference them in the second report, but was unsure of how to call them from the filter in the final report.

The manual suggests using "in", "all", or "any" with parameters in the filter, although I can only see "in" in my filter choices.

Any suggesions would be greatly appreciated.

Thanx.

 

[DrSimon]

It is probably best to do it via a macro as Dave the Guru suggests. An alternative would be to include the prompts as variables in report 1 as you describe and then save it as a hotfile. You can then use that hotfile as an additional data source for report2 - you'll probably need a join in the filter to make it work which could be an alternative to the dataset filter.
The main problem with this is that you have to remember to update the hotfile each time you run it so a macro is better!

Simon Rouse

 

[gasman009]

in2view-

I had exactly the same problem a few months back. I tried exactly the same thing, but for a drill down report. I think what you are looking for is "smart prompts" or "smart hotfile." There are two Kbase TN's that I eventually found.

Doc Id's:
97007
100481

Basically, what you want to do is to create report1 (the base report) with the prompt. Instead of using an IMS hotfile, you can also use an IMR report as the datasource. (It is sort of like a "pre-query.&quot Make your start/end date prompts there. Add that report1.imr as a hotfile datasource for report2. The prompts that you use will be filtering into the final report2.

Hope this is what you are looking for.
Gaston

 

[griffindm]

You may want to consider using a Drill-Thru report to go from the summary to the detail information. These have been available from version 5 on and allow you to pass prompt info (and row-level data) as filters between reports (using the same catalog).

Try them out and get back to me if you have questions.

Hope this helps,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[gasman009]

Looks like I replied around the same time as DrSimon. I just read Simon's and re-read Dave's response. (I wish I knew about this forum when I had this problem).

BTW, here is the link to cognos kbase if you don't know it:

http://support.cognos.com/servlet-cs/kbsearch

Also, the technote mentions that smart hotfiles start becoming a feature in 6.X, so your best bet is probably go to 7?

-Gaston

 

[griffindm]

I forgot to mention it, but the Drill-Thru approach has the advantage of not requiring a macro at all.

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[in2view]

Thanks for all the suggestions guys.

I've been toying around with this some more and still haven't come up with a sufficient solution. I have used the macro approach to open both reports and pass the hotfile info to the summary report. But to no avail, I need to display (in the header) in the summary report, the prompt info (date range) from the detail report. I would like to avoid the hotfile situation if possible. I do not need to see any of the detail info in the summary report, but use it to filter the needed data. Since the summary report has grouping and sorting needs that conflict with the details, it is not possible to write this as a one report solution. I've tried creating aliases for high level sorting to see if multiple selects might allow the sorts I need, but so far no go.

To further show what I am trying to do. I will give the following example. Consider this to be a delivery company (for comparison purposes).

I have multiple "trips" where I construct a TripCode from the (Van number + Date of Delivery), an ID will be created later, but thats irrelevant.

The first report selects a dataset of "trips" where the goods delivered (details of multiple items) have a percentage of "under 5 lb packages" of more than 50% of the entire deliveries for the "trip". It is sorted by the calculated "TripCode". "Date Range" prompts ("date to" and "date from&quot is used in the detail report to limit results and increase speed. This is the hotfile info.

The second report selects the hotfile info, with the detail info from the hotfile and summarizes (sorts) the report is, based not on the Trip but on the destination (area)and "type" of each individual package.

Using two reports wth the dataset method works great by putting the date range prompt in the first report, but I need that date range to display in the header of the summary report. Since the first and last day may not be included in the report, min and max dates will not suffice.

This is why I wanted to access the prompts of the first report, just to be able to display them in the summary report.

I tried adding them to columns in the first (detail) report, since it doesn't need to be seen, but could not reference them in the summary report.

Any help on this would be appreciated. As I said, I would like to avoid using macros, if possible. Unless of course I could write a small reusable one to capture the prompt variables for this type of situation, and resuse it when needed (i.e. state all possible prompt variables and when ran from a report, capture all that have been prompted).

Again thanx for all the advice.

Dave Chafe

 

[DrSimon]

Dave
An idea to solve a small part of your last post regarding the need to display the date in the summary report header from the detail report. Assuming you mean report header, not column header, I've got round the same problem using macros to update a dummy prompt in the summary report. You make that dummy prompt a data item in the summary query and then insert the data item in the header, the prompt's value can be whatever you set in the macro.

Simon Rouse

 

[in2view]

Thanks Simon,

I have tried this before and it will work, but if I tried to reference more than one data item using the dataset method it prompts multiple times. I want both the start and end dates from the dataset, as well as the Trip ID.

Is there a way to reference more than one data item using the dataset method in the filter, but have only one prompt window show for the summary report?

I tried concatinating the data items together (2 dates converted to strings (formated (yyyy-mm-dd)) + Trip ID (now a number of variable length converted to string)).

This involved many calculations and conversions, and is not a preferred method. I would appreciate a simpler method if one is available.


Thanx
Dave Chafe