Report Net Drill Down 1

[TheShenkler]

Hi All,

I'm using Report Studio to create summary report that just shows high level totals. I need my users to be able to drill down to the underlying details.

In Crystal Reports it was simple to just hide the sections to drill down on the data.

My boss really wants the same functionality when viewing the ReportNet reports.

Any tips on how to do this would be appreciated.

Thanks,

Hank

 

[Nuffsaid]

Hey Hank,

I was a Crystal reporter up until recently also.

ReportNet is still a bit of a "culture shock" for me.

ReportNet does not "drill down" like Crystal. Have a look at the "Drill Through" functionality. It's the closest thing I've found to Crystal's drill down.

Welcome.......

Nuffsaid.

 

[cncom]

TheShenkler,

Linked reports, or drill through reports allow you to see more detail on a particular area of interest.

You can create a drill through report by adding a parameter to a child report and a drill through connection from the parent to the child.

Note : a filter must exist on the child report that is the same as the element that you want to drill through on.

<><><><><><><><><><><><><><><><><><><><><>
COGNOiSe.com

The world's first dedicated COGNOS portal

 

[mfgf]

Hi TheShenkler,

You can even use a variation on COGNOISEcom's solution above to drill to another object in the same report. For example, add your summary data as a list, then drag a second list alongside and use this to display your detail data. Add a filter to the detail list that uses a parameter, save the report, then add a drill-through from the summary list to a report, and select the same report. If you're clever with conditional hiding, you can get the detail list to appear only after you have selected a drillthrough value from the summary list.

MF.

 

[dmunson]

Try this:
copy to clipboard and, open report studio and select tools >> open report from clipboard.

It was written using the GO Sales and Retailers samples.

<report xml:lang="en-us" xmlns="

http://developer.cognos.com/schemas/report/1/"><!--RS:1.1-->

<modelConnection name="/content/package[@name='GO Sales and Retailers']/model[@name='model']"/>
<querySet xml:lang="en-us">
<BIQuery name="Query1">
<cube><factList><item refItem="Quantity" aggregate="none" isHeader="true"/></factList><dimension name="Order month"><level name="Order month"><item refItem="Order month" aggregate="none" key="true" isHeader="true"/></level></dimension><dimension name="Product line"><level name="Product line"><item refItem="Product line" aggregate="none" key="true" isHeader="true"/><item aggregate="none" refItem="prodfiltercol"/><dataItem aggregate="none" name="prodLevel"><expression>?prodlevel?</expression></dataItem><dataItem aggregate="none" name="downlevel" isHeader="true"><expression>CASE ?prodlevel?
WHEN 'prodline' THEN 'prodtype'
WHEN 'prodtype' THEN 'product'
WHEN 'product' THEN 'product'
END</expression></dataItem><dataItem aggregate="none" name="filter"><expression>?prodfilter?</expression></dataItem></level></dimension><filter><condition>((?prodfilter? = 'all' ) or ([prodfiltercol] = ?prodfilter?))</condition></filter></cube>
<tabularModel><dataItem name="Product line" aggregate="none"><expression>CASE ?prodlevel?
WHEN 'prodline' THEN [gosales_goretailers].[Products].[Product line]
WHEN 'prodtype' THEN [gosales_goretailers].[Products].[Product type]
WHEN 'product' THEN [gosales_goretailers].[Products].[Product name]
END</expression></dataItem><dataItem name="prodfiltercol"><expression>CASE ?prodlevel?
WHEN 'prodline' THEN [gosales_goretailers].[Products].[Product line]
WHEN 'prodtype' THEN [gosales_goretailers].[Products].[Product line]
WHEN 'product' THEN [gosales_goretailers].[Products].[Product type]
END</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[gosales_goretailers].[Orders].[Quantity]</expression></dataItem><dataItem name="Order month" aggregate="none"><expression>[gosales_goretailers].[Orders].[Order month]</expression></dataItem></tabularModel></BIQuery>
<BIQuery name="Query1_crosstab"><cubeReference refQuery="Query1"/><summary><rowEdge><level refLevel="Product line"><item refItem="Product line"/><item refItem="prodfiltercol"/><item refItem="prodLevel"/><item refItem="downlevel"/><item refItem="filter"/></level></rowEdge><columnEdge><level refLevel="Order month"><item refItem="Order month"/></level></columnEdge><dataCells><item refItem="Quantity"/></dataCells></summary></BIQuery></querySet>
<layoutList>
<layout>

<pageSet>

<page name="Page1">
<pageBody>
<crosstab refQuery="Query1_crosstab"><style>
<CSS value="border-collapse:collapse"/>
</style><crosstabCorner><HTMLItem>
<HTML>&lt;a href="#" onclick="history.go(-1)"&gt;Up a level&lt;/a&gt;</HTML></HTMLItem>
</crosstabCorner><crosstabRows/><crosstabColumns/><crosstabLevel refLevel="Product line"><textItem class="hyperlink"><queryItemRef refItem="Product line"/><drillTarget name="DrillTarget1" path="/content/package[@name='GO Sales and Retailers']/report[@name='drill test']"><label>Drill Through1</label><drillParameter name="prodfilter"><queryItemRef refItem="Product line"/></drillParameter><drillParameter name="prodlevel"><queryItemRef refItem="downlevel"/></drillParameter></drillTarget></textItem></crosstabLevel><crosstabLevel refLevel="Order month"><textItem><queryItemRef refItem="Order month"/></textItem></crosstabLevel><crosstabCell><textItem><queryItemRef refItem="Quantity"/></textItem><style><CSS value="text-align:right"/></style><member refMember="Quantity"/></crosstabCell></crosstab>
</pageBody>
<pageHeader>
<block class="reportTitle">
<textItem class="reportTitleText">
<text/>
</textItem>
</block>
<style>
<CSS value="padding-bottom:10px"/>
</style>
<table><tableRow><tableCell><textItem><expression>ParamDisplayValue("prodfilter")</expression></textItem></tableCell><tableCell><textItem><expression>ParamDisplayValue("prodlevel")</expression></textItem></tableCell></tableRow><style><CSS value="border-collapse:collapse"/></style></table></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>

 

[JGirl]

Now THAT is a report..... Love your work

J

 

[dmunson]

Thanks,

I'm new to the forums, is there a better way to send a report spec like that?

 

[JGirl]

Not that I know of....

If you really want to get fussy you could format your posting so that it differentiates between the message and the report spec, for example, pasting the report spec in between [&#91;]Code]blahblah[&#91;]/Code] tags so that your code appears

 within its own section

but I guess it just depends on how:
(a) bored you are
(b) much you care
(b) much spare time you have

J

 

[tbtcust]

Does anyone have an approach for drilling down from pie charts? Cognos support said this would come in a later release.

 

[dmunson]

tbt103,

You can do it now, but it's a little bit of work.

You have to turn on tooltips for the pie chart so CRN will create an imagemap. This gives you something to attach the drill through code to. Then you can do on of two things:

create a list or crosstab with the same drill through links in the same page of your report.Use javascript to search the resulting html table for your value using the DOM. and set the imagemap areas onclick = the drillthrough links onclick.
OR
Create your own drill down code for it and use attachEvent. The drill functions you need to call are in webcontent/rv/rv.js, they are called, doSignleDrill and doMiltipleDrills. you aso need to use createArray.

doSingleDrill(target,args,method,format,locale,bookmark)
doMultipleDrills(drillTargets)
Here is what the code looks likefor a single drill:

doSingleDrill('',createArray(createArray('obj','<<Encoded report path>>),createArray('p_<<parmameter>>','<<parameter value>>'),createArray('p_'<<parmeter>>,'<<parameter value>>')),'execute','HTML','en-us','');

For example:
doSingleDrill('',createArray(createArray('obj','%2fcontent%2ffolder%5b%40name%3d%27Drill+test%27%5d%2freport%5b%40name%3d%27Target%27%5d'),createArray('p_value+1','test value'),createArray('p_value+2','2005-05-11T00%3a00%3a00.000')),'execute','HTML','en-us','');

(I only replied so I could use the code thing.)

P.S. This works for all chart types.

 

[tbtcust]

Thanks so much dmunson. I'll give it a try.

 

[dmunson]

tbt103, I created a new post with the code for this:

http://www.tek-tips.com/viewthread.cfm?qid=1096774&page=1