Cross Tab/Drill Down Question

[obryant32]

I have Cr 9.0. I recently created a cross tab report that lists the count of all applications taken by our employees and then breaks it down by each step in our workflow process. My boss wants to be able to click on each count number in the chart and have that list all of the corresponding applications. I've been reading about doing drill downs but I don't fully understand it. I created a group that displays all th info on each application. However when I click on format crosstab and go to the hyperlink tab, Report Part Drilldown is greyed out. So how do I drill down something from a cross tab? I'm completely lost. Any help is appreciated. Thanks

Matt

 

[MColeman]

I'm not sure, but you may have to create a manual cross-tab report to get the drill down capabilities.

 

[obryant32]

Please forgive my ignorance, but what's a manual crosstab and how do you set one up?

 

[lbass]

Please tell us what you are currently using for your rows, columns, and summary fields in your inserted crosstab. This way, we can give you more specific help with creating a manual crosstab.

Also, please note that a drilldown in a manual crosstab would give you the detail for an entire "row" of cells, not just one cell at a time. For detail on one "cell," I think you would need to insert a separate on-demand subreport for each column of data.

-LB

 

[obryant32]

The rows are grouped, in order, by employees then target close date on the sale. The columns are the various steps in the application process. We then summarize the data by a count on the transaction number. Is that what you're looking for lbass?

 

[lbass]

Do you want a drilldown per cell or per row?

-LB

 

[obryant32]

I'd like to drill down per cell, but if that's not possible then per row is fine. Thanks

Matt

 

[lbass]

To do a manual crosstab, first group on {table.employee} and then on {table.targetclosedate}. Place {table.transactionno) and other application info in the detail section. For a drilldown per row, you would then create a series of formulas, one per column, like the following:

//{appstep1}:
if {table.appprocess} = "step 1" then 1 else 0

//{appstep2}:
if {table.appprocess} = "step 2" then 1 else 0

Right click on each formula and choose "Insert summaries on all groups" and insert a grand total if you wish. Then hide the details section. You can now drill down on the summaries to see the detail section with application info.

To get a drilldown per "cell" at the target date close group level, you would need to create a separate subreport for each column. The subreport would contain the detail info you want, and would be linked to the main report by {table.employee} and {table.targetclosedate}. In the record selection formula of the subreport, you would limit the records to those that match the column field in the main report, e.g., for subreport "Step1", the record selection formula would look like:

{table.appprocess} = "step 1" //the subreport links would automatically be added to this

You would suppress all sections of the subreport except those you want available for drill down. I would format the subreport to be on-demand (format->subreport->subreport->on-demand subreport. Then also choose "subreport caption"->x+2 and enter:

totext(sum({@appstep1},{table.targetclosedate}),0,"")

Place the subreport in the group #2 (target close date} header or footer (whatever you are displaying) under the column to which it corresponds. The summary for the cell will act as a hyperlink to the details of the subreport.

You would repeat this subreport for each column for the close date group, only changing the record selection and the caption for the hyperlink.

If you also want to have the capacity for drill down at the employee group level, you would repeat the above steps, except your link would only be on employee, and you would place the subreport in the employee group header or footer.

I didn't test this out, but I think it should work.

-LB