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!

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

Drill through reports and prompt page 1

Status
Not open for further replies.
MissReportNet

MissReportNet

Programmer
Joined
Dec 11, 2005
Messages
29
Location
NL
Hi all,

I am trying to create a drill through from one crn report to another. Everyting goes well Except my promptpage. I dont know how i can drill through so that the target report is filtered with the prompts i use in my drill through report.

Thx!!


Cheers,

MissReportNet
 
Sort by date Sort by votes
Create required filters in the target report and make them as optional filter. Now in Drill through report( the report from which u wanna drill thru) create the appropriate drill thru's.

Anand.
 
Upvote 0 Downvote
Hi Anand,

Thats not exactly what i meant. A drill through is not a problem. I wanna drill through so that the prompts in my promptpage in drill through reports is also used in the target report!!!

example: I have got organisation, month and year prompts in my prompt page. If i run my drill through report i am prompted to choose from organisation, month and year. I want that my target report uses also these prompts without choosing again the same prompts in the target report.

Thx

Cheers,

MissReportNet
 
Upvote 0 Downvote
You need to create prompts & calculated fields for each of your prompts in both your reports. You must ensure the calculated field names and the prompt names you create are exactly the same in both reports.
When you select your drill through report, you need to then include these calculated fields you've created for your prompts as 'Columns to be included in the drill through filter'.

Brian
 
Upvote 0 Downvote
you can not have a prompt page in a drill-through report (target report).
 
Upvote 0 Downvote
Hi All,

Miss ReportNet is this still an issue?

What you need to do is create data items in your query in your main report that capture the parameter values the user selected, for example, create a data item that captures ?year? and name it year_param. Do the same for ?month? and ?organization?

In your "target report" you have 3 parameters that need to be satisfied, ?target_year? ?target_month? ?target_organization?

Assuming that the drill through link in the main report is organization, create a drill through link on organization to your target report, when prompted to "hook up" the parameters in the target report select the "data items" (year_param) you created in the main report to capture the parameter values your user selected and hook it to (?target_year?)in the drill through report. Do the same for organization and month.

BTW, it doesn't matter if the target report has a prompt page, as long as you pass all the values from the main report it will work.

Let me know if this works for ya.....


Nuffsaid.
 
Upvote 0 Downvote
Hi Nuffsaid,

Thx for your information.

Your first 2 alinea's i can manage. The drill throug link will be outside the main report body. That makes it diffucult for me. Can you tell me what to do from now on.

Thx in advance!!

MissReportNet
 
Upvote 0 Downvote
Hi,

"Your first 2 alinea's i can manage."
Click to expand...

I'm sorry, what does "alinea's" stand for?

"The drill throug link will be outside the main report body."
Click to expand...

Where exactly will the link be?

Nuffsaid.
 
Upvote 0 Downvote
So been messing around and I created 2 sample reports (code below) using the Go Sales and Retailers package.
Copy the code to the clipboard, open Report Studio and under the Tools menu select “Open Report From Clipboard”. Save the reports as MAIN and TARGET within the Go Sales and Retailers folder. (I hooked up the parameters but I’m not sure it’ll work when you save the reports to your system.)

So, assuming we live in a perfect world……………..

Open the Main report with Report Studio and run it. You should be prompted for a date range. Select a date from as early as 2004 to toady. When the report renders (if it does) click the Product Line link to drill through to the Target Report.

You’ll notice that the Target Report returns info based on the Product Line you drilled on for the date range you specified in the Main Report.

Oh, BTW, both reports contain prompt pages and can be run independent of each other. However, as mentioned before, if you satisfy the Target report’s parameters when you’re drilling through you wouldn’t even know a prompt page was present in the Target report.

If all this works you should be able to adapt this to your situation, and hey if you have problems, you can always come back!!

Ready? So here we go…….

Create the Target report first, (because if you create the Main report first and VALIDATE it might error out because it can’t find the Target report)

Target Report Code

Code: 
<report xml:lang="en-ca" xmlns="[URL unfurl="true"]http://developer.cognos.com/schemas/report/1/"><!--RS:1.1-->[/URL]
	<modelConnection name="/content/package[@name='GO Sales and Retailers']/model[@name='model']"/>
	<querySet xml:lang="en-ca">
		<BIQuery name="Query1">
			<cube><factList><item refItem="Product name" aggregate="none"/><item refItem="Quantity" aggregate="total"/></factList><dimension name="Product type"><level name="Product type"><item key="true" refItem="Product type" aggregate="none"/></level></dimension></cube>
		<tabularModel><dataItem name="Product type" aggregate="none"><expression>[gosales_goretailers].[Products].[Product type]</expression></dataItem><dataItem name="Product name" aggregate="none"><expression>[gosales_goretailers].[Products].[Product name]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[gosales_goretailers].[Orders].[Quantity]</expression></dataItem><filter use="required" summary="false"><condition>[gosales_goretailers].[Orders].[Order date] &gt;= ?Target From?
and
[gosales_goretailers].[Orders].[Order date] &lt;= ?Target To?</condition></filter><filter use="required" summary="false"><condition>[gosales_goretailers].[Products].[Product line] = ?Line?</condition></filter></tabularModel></BIQuery>
	<BIQuery name="Line"><cube><factList><item refItem="Product line" aggregate="none" isHeader="true"/><item refItem="Product line1" aggregate="none" isHeader="true"/></factList></cube><tabularModel><dataItem name="Product line" aggregate="none"><expression>[gosales_goretailers].[Products].[Product line]</expression></dataItem><dataItem name="Product line1" sort="ascending" aggregate="none"><expression>[gosales_goretailers].[Products].[Product line]</expression></dataItem></tabularModel></BIQuery></querySet>
	<layoutList>
		<layout>
			
		<promptPageList><page name="Prompt Page1">
					<pageHeader>
						<block class="reportTitle">
							<textItem class="reportTitleText">
								<text/>
							</textItem>
						</block>
						<style>
							<CSS value="padding-bottom:10px"/>
						</style>
					</pageHeader>
					<pageBody>
						<style>
							<CSS value="padding:12px"/>
						</style>
					<table><tableRow><tableCell><selectDate required="true" parameter="Target From" multiSelect="false"/>
								<style><CSS value="vertical-align:top;text-align:center"/></style></tableCell><tableCell><selectDate required="true" parameter="Target To" multiSelect="false"/>
								<style><CSS value="vertical-align:top;text-align:center"/></style></tableCell></tableRow><tableRow><tableCell colSpan="2"><selectValue refQuery="Line" required="true" selectUI="dropdown" parameter="Line" multiSelect="false"><useItem refItem="Product line"/><displayItem refItem="Product line1"/></selectValue>
									<style><CSS value="vertical-align:top;text-align:center"/></style></tableCell></tableRow><style><CSS value="border-collapse:collapse;width:100%"/></style></table></pageBody>
					<pageFooter>
						<style>
							<CSS value="padding:12px;border-top:1pt solid #999999"/>
						</style>
						<promptButton type="cancel">
							<style>
								<CSS value="margin-right:7px"/>
							</style>
						</promptButton>
						<promptButton type="finish"/>
					</pageFooter>
				</page>
			</promptPageList><pageSet>
				
			<page name="Page1">
					<pageBody>
						<list refQuery="Query1">
							<listColumnTitles><listColumnTitle><textItem><queryItemRef content="label" refItem="Product type"/></textItem></listColumnTitle><listColumnTitle><textItem><queryItemRef content="label" refItem="Product name"/></textItem></listColumnTitle><listColumnTitle><textItem><queryItemRef content="label" refItem="Quantity"/></textItem></listColumnTitle></listColumnTitles>
							<listColumns><listColumn refLevel="Product type"><textItem><queryItemRef refItem="Product type"/></textItem></listColumn><listColumn><textItem><queryItemRef refItem="Product name"/></textItem></listColumn><listColumn><textItem><queryItemRef refItem="Quantity"/></textItem><style><CSS value="text-align:right"/></style></listColumn></listColumns>
							<style>
								<CSS value="border-collapse:collapse"/>
							</style>
						<XMLAttribute name="RS_ListGroupInfo" value="Product type"/><groupFooter refLevel="Product type"><rowCells><rowCell class="inlineFooter" colSpan="2"><textItem><queryItemRef refItem="Product type"/></textItem></rowCell><rowCell class="inlineSummary"><style><CSS value="text-align:right"/></style><textItem><queryItemRef refItem="Quantity"/></textItem></rowCell></rowCells></groupFooter><overallFooter><rowCells><rowCell class="inlineFooter" colSpan="2"><textItem><text>Summary</text></textItem></rowCell><rowCell class="inlineSummary"><style><CSS value="text-align:right"/></style><textItem><queryItemRef refItem="Quantity"/></textItem></rowCell></rowCells></overallFooter></list>
					<style><CSS value="vertical-align:top;text-align:center"/></style></pageBody>
					<pageHeader>
						<block class="reportTitle">
							<textItem class="reportTitleText">
								<text>Target Report</text>
							</textItem>
						</block>
						<table><tableRow><tableCell><textItem><expression>ParamDisplayValue("Line")</expression></textItem><style><CSS value="vertical-align:top;text-align:center"/></style></tableCell></tableRow><tableRow><tableCell><textItem><expression>ParamDisplayValue("Target From")+' To '+ParamDisplayValue("Target To")</expression></textItem><style><CSS value="vertical-align:top;text-align:center"/></style></tableCell></tableRow><style><CSS value="border-collapse:collapse;width:100%"/></style></table><style>
							<CSS value="padding-bottom:10px"/>
						</style>
					</pageHeader>
					<pageFooter>
						<table>
							<tableRow>
								<tableCell>
									<textItem>
										<expression>AsOfDate()</expression>
									</textItem>
									<style>
										<CSS value="vertical-align:top;text-align:left;width:25%"/>
									</style>
								</tableCell>
								<tableCell>
									<textItem>
										<text>- </text>
									</textItem>
									<textItem>
										<expression>PageNumber()</expression>
									</textItem>
									<textItem>
										<text> -</text>
									</textItem>
									<style>
										<CSS value="vertical-align:top;text-align:center;width:50%"/>
									</style>
								</tableCell>
								<tableCell>
									<textItem>
										<expression>AsOfTime()</expression>
									</textItem>
									<style>
										<CSS value="vertical-align:top;text-align:right;width:25%"/>
									</style>
								</tableCell>
							</tableRow>
							<style>
								<CSS value="border-collapse:collapse;width:100%"/>
							</style>
						</table>
						<style>
							<CSS value="padding-top:10px"/>
						</style>
					</pageFooter>
				</page></pageSet></layout>
	</layoutList>
</report>

Now the Main Report....
Code: 
<report xml:lang="en-ca" xmlns="[URL unfurl="true"]http://developer.cognos.com/schemas/report/1/"><!--RS:1.1-->[/URL]
	<modelConnection name="/content/package[@name='GO Sales and Retailers']/model[@name='model']"/>
	<querySet xml:lang="en-ca">
		<BIQuery name="Query1">
			<cube><factList><item refItem="Product line" aggregate="none"/><item refItem="Quantity" aggregate="total"/><item aggregate="none" refItem="Master From Parameter"/><item aggregate="none" refItem="Master To Parameter"/></factList></cube>
		<tabularModel><dataItem name="Product line" aggregate="none"><expression>[gosales_goretailers].[Products].[Product line]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[gosales_goretailers].[Orders].[Quantity]</expression></dataItem><filter use="required" summary="false"><condition>[gosales_goretailers].[Orders].[Order date] between ?From? and ?To?</condition></filter><dataItem name="Master From Parameter"><expression>?From?</expression></dataItem><dataItem name="Master To Parameter"><expression>?To?</expression></dataItem></tabularModel></BIQuery>
	</querySet>
	<layoutList>
		<layout>
			
		<promptPageList><page name="Prompt Page1">
					<pageHeader>
						<block class="reportTitle">
							<textItem class="reportTitleText">
								<text/>
							</textItem>
						</block>
						<style>
							<CSS value="padding-bottom:10px"/>
						</style>
					</pageHeader>
					<pageBody>
						<style>
							<CSS value="padding:12px"/>
						</style>
					<table><tableRow><tableCell><selectDate required="true" parameter="From" multiSelect="false"/>
								<style><CSS value="vertical-align:top;text-align:center"/></style></tableCell><tableCell><selectDate required="true" parameter="To" multiSelect="false"/>
									<style><CSS value="vertical-align:top;text-align:center"/></style></tableCell></tableRow><style><CSS value="border-collapse:collapse;width:100%"/></style></table></pageBody>
					<pageFooter>
						<style>
							<CSS value="padding:12px;border-top:1pt solid #999999"/>
						</style>
						<promptButton type="cancel">
							<style>
								<CSS value="margin-right:7px"/>
							</style>
						</promptButton>
						<promptButton type="finish"/>
					</pageFooter>
				</page>
			</promptPageList><pageSet>
				
			<page name="Page1">
					<pageBody>
						<list refQuery="Query1">
							<listColumnTitles><listColumnTitle><textItem><queryItemRef content="label" refItem="Product line"/></textItem></listColumnTitle><listColumnTitle><textItem><queryItemRef content="label" refItem="Quantity"/></textItem></listColumnTitle></listColumnTitles>
							<listColumns><listColumn><textItem class="hyperlink"><queryItemRef refItem="Product line"/><drillTarget name="DrillTarget1" path="/content/package[@name='GO Sales and Retailers']/report[@name='Target']"><label>Drill Through1</label><drillParameter name="Line"><queryItemRef refItem="Product line"/></drillParameter><drillParameter name="Target From"><queryItemRef refItem="Master From Parameter"/></drillParameter><drillParameter name="Target To"><queryItemRef refItem="Master To Parameter"/></drillParameter></drillTarget></textItem></listColumn><listColumn><textItem><queryItemRef refItem="Quantity"/></textItem><style><CSS value="text-align:right"/></style></listColumn></listColumns>
							<style>
								<CSS value="border-collapse:collapse"/>
							</style>
						<overallFooter><rowCells><rowCell class="inlineFooter"><textItem><text>Summary</text></textItem></rowCell><rowCell class="inlineSummary"><style><CSS value="text-align:right"/></style><textItem><queryItemRef refItem="Quantity"/></textItem></rowCell></rowCells></overallFooter><XMLAttribute name="RS_ListGroupInfo" value=""/></list>
					<style><CSS value="vertical-align:top;text-align:center"/></style></pageBody>
					<pageHeader>
						<block class="reportTitle">
							<textItem class="reportTitleText">
								<text>Master Report</text>
							</textItem>
						</block>
						<block><textItem><expression>ParamDisplayValue("From")+' To '+ParamDisplayValue("To")</expression></textItem><style><CSS value="text-align:center"/></style></block>
						<style>
							<CSS value="padding-bottom:10px"/>
						</style>
					</pageHeader>
					<pageFooter>
						<table>
							<tableRow>
								<tableCell>
									<textItem>
										<expression>AsOfDate()</expression>
									</textItem>
									<style>
										<CSS value="vertical-align:top;text-align:left;width:25%"/>
									</style>
								</tableCell>
								<tableCell>
									<textItem>
										<text>- </text>
									</textItem>
									<textItem>
										<expression>PageNumber()</expression>
									</textItem>
									<textItem>
										<text> -</text>
									</textItem>
									<style>
										<CSS value="vertical-align:top;text-align:center;width:50%"/>
									</style>
								</tableCell>
								<tableCell>
									<textItem>
										<expression>AsOfTime()</expression>
									</textItem>
									<style>
										<CSS value="vertical-align:top;text-align:right;width:25%"/>
									</style>
								</tableCell>
							</tableRow>
							<style>
								<CSS value="border-collapse:collapse;width:100%"/>
							</style>
						</table>
						<style>
							<CSS value="padding-top:10px"/>
						</style>
					</pageFooter>
				</page></pageSet></layout>
	</layoutList>
</report>

Let us know if this works for ya....




Nuffsaid.
 
Upvote 0 Downvote
Hi Nuffsaid,

Thx for your suggestion and sorry for my late reply (holiday :-))

I am trying to do what you have written, but how do you copy the code to the clipboard? I can copy the report to clipboard if I have created a report but now i have only the xml code.

I am very curious??

Thanks in advance!!!

MissReportNet
 
Upvote 0 Downvote
Hi,

Highlight the report code in the post above, right click and select copy.

Open Report Studio and from the Tools menu select "Open Report From Clipboard".

Nuffsaid.
 
Upvote 0 Downvote
Hi Nuffsaid,

I wanna thank you (* million times) for your information and effort. It works fine now and I am able to apply this on the reports. :-) :-) :-) :-)

Cheers,

MissReportNet
 
Upvote 0 Downvote
Glad it worked out for ya.....[2thumbsup]

Nuffsaid.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

tushargaurav
  • Locked
  • Question Question
date time prompt not working in arabic language in cognos
Replies
0
Views
536
tushargaurav
tushargaurav
sreevp2020
  • Locked
  • Question Question
Cross tab intersection drill through
Replies
0
Views
467
sreevp2020
sreevp2020
wwconslt
  • Locked
  • Question Question
Wild Card for Nine Digit Zip Codes in Prompt
Replies
0
Views
521
wwconslt
wwconslt
sreevp2020
  • Locked
  • Question Question
dynamic deafult date in date prompt calandar
Replies
0
Views
499
sreevp2020
sreevp2020
Emperatriz Stredel
  • Locked
  • Question Question
Two Cognos reports with two Models (Framework Manager)
Replies
0
Views
502
Emperatriz Stredel
Emperatriz Stredel

Part and Inventory Search

Sponsor

Back
Top