Drill Through filters

[BlueBrand]

Does anyone know whether it possible to reference values being passed from a cube to a 'drill through' .imr report? For example, if the cube passes Department='101', can I reference that in the .imr report as in 'where division = passed_in_department, in other words '101'.

 

[ADB1]

Sorry, could you elaborate a little, don't really understand what you would like to do.

Drilling through to Impromptu to get 'atomic' level detail will automatically pass through all of the filters (dimension values) from Powerplay.

Adam.

 

[BlueBrand]

Thx for the reply. What I want to do is reference the passed in filters in the Impromptu report. So, if PowerPlay passes something like "Department=101" to my Impromptu drill through, then in Impromptu I want have a filter set up that uses the value "101" that is passed from PowerPlay.

The problem is as follows. I pass a date range from PowerPlay to Impromptu. In the Impromptu report, I use the passed in date range to filter my main query. But I also have a sub-report that needs the same date range. However, the date range from PowerPlay does not pass on into the sub-report. So, I need a way from the sub-report, to reference the date range passed in from PowerPlay to the main query.

 

[ADB1]

BlueBrand,

You need to set up the following filter within your sub report. You must have the column that you wish to filter on (in this case passed-in date) in the main query, visible or not, it doesn't matter:

passed-in date = mainquery(passed-in date)

When you select the '=' sign in the filter window, the main query folder is available. This will then enable you to select the column you require.

Hope this is what you want.

Cheers,

Adam.

 

[BlueBrand]

The date range that is passed in is something like StartDate=(2002-01-01, 2002-02-15). That range is passed from PowerPlay to Impromptu. My main Impromptu query then returns the rows:

ID NAME START_DATE
101 Joe 2002-01-01
103 Jack 2002-02-03

But, what I want in the sub-query is not only those two dates, I want the range, StartDate=(2002-01-01, 2002-02-15). If I reference the Main Query.StartDate, I get only the two dates, not the range. Is there a way to get the drill through filter to pass into the sub-report?

 

[ADB1]

All the knowledge base documents below are from Cognos Support, hope one answers your question......

Description:

Is it possible to create a drill-through on a sub report ?

Solution:

Normally a drill-through is only done on the main query. However, it is possible to make a drill-through on a sub report by putting the column to drill-through to in the main query as well as in the sub report. If the column is not relevant in the main query it can be hidden.
---------------------------------------------------------
Description:

A linked Sub-Report is Required which displays all items contained in the main query. The filtering capabilities in Impromptu for linked Sub-queries do not permit the use of the IN operator and will include only the first element of the required list.

This is the current functionality for filtering between main and linked Sub-queries.


Solution:

Example Using the "Great Outdoors Sales Data"
The main query contains a summary query with the following columns:

Groups: Country, State\Prov, Customer Name, Order No. and Date
Aggregate: Total(Sale Amount)


Sub-Query: Country, Customer Name and Order Number all grouped.
Details: Product, order price, qty and sale amount.

Requirement: To filter the report on a column other than the main grouping and have all associated orders for a given country on a single page.

1) Set scope of data on the primary form frame to 'Data in Group: Country'
2) Rather than filtering on order date in the query itself as this will produce the problem, create another report where the order number is placed in the query and the report is filtered on the order date.
3) Set a filter in the main query where the order number is in a dataset which is the above report.
4) Set the same filter in the Sub-query.
5) Add to the Sub-query filter the link where country = mainquery.country.
---------------------------------------------------------
Problem Description:

How can a prompt from the Main report be synchronized to the Sub-report?

Solution Description:

In the Main Report, define the prompt(s) and filter(s). Insert the Sub-Report and apply a filter relating to the prompted field(s).

The filter expression that links a sub-report to a Main report is as follows:

<column name>=Main Query.<column name>
-----------------------------------------------------------
Description:

A linked Sub-Report is Required which displays all items contained in the main query. The filtering capabilities in Impromptu for linked Sub-queries do not permit the use of the IN operator and will include only the first element of the required list.

This is the current functionality for filtering between main and linked Sub-queries.


Solution:

Example Using the &quot;Great Outdoors Sales Data&quot;
The main query contains a summary query with the following columns:

Groups: Country, State\Prov, Customer Name, Order No. and Date
Aggregate: Total(Sale Amount)


Sub-Query: Country, Customer Name and Order Number all grouped.
Details: Product, order price, qty and sale amount.

Requirement: To filter the report on a column other than the main grouping and have all associated orders for a given country on a single page.

1) Set scope of data on the primary form frame to 'Data in Group: Country'
2) Rather than filtering on order date in the query itself as this will produce the problem, create another report where the order number is placed in the query and the report is filtered on the order date.
3) Set a filter in the main query where the order number is in a dataset which is the above report.
4) Set the same filter in the Sub-query.
5) Add to the Sub-query filter the link where country = mainquery.country.
----------------------------------------------------------

Cheers,

Adam.