Drill Through From Crosstab With Prompts

[dgleason42]

This has been a very frustrating user adoption issue - I am trying to move my users from very old Excel pivot tables to Reportnet reports, but the drill-through capabilities in CRN crosstab reports are very short, at least out of the box.

I have a crosstab report with 6 optional prompts that the user can use to filter the data in the crosstab. The data columns that are optionally filtered by the prompts are all included in the tabular model and facts of the query for the crosstab, but I don't want to display those columns in the crosstab - they are to be used as filters on the aggregated crosstab. I can create a drill-through to a detailed report that has optional parameterized filters that I can (in the Drill-Through definition) set to the filtered data items in my query, but those parameters never get set. It seems that those drill-through parameters do not get passed unless they actually physically appear in the crosstab report body. I have tried putting those query items as dimensions in the query, but same result. I have even tried unlocking the report and placing the query items in as invisible items, but same result.

So, I thought I would punt and create a Javascript function to pop up a drill-through report with a querystring created from both the values of the prompts and values passed as parms to the function. I unlocked the crosstab, inserted an HTML item before and after my query item (to wrap it with an anchor tag), and pointed the beginning HTML item to a Query Item that constructed the URL for the anchor tag based on other query items in the crosstab (my dimensions). But the HTML item never renders in the crosstab if it is based on a Query Item as the HTML source! It will render fine if I include the HTML as static code, but not from a Query Item! This is very frustrating, as it is how I have constructed plenty of other anchor tags in list reports that point to other reports or other web-based applications so that values can be passed in query strings, but it appears that this functionality isn't enabled for crosstab reports - even though it lets me set the query item as a property of the HTML item in the unlocked crosstab report.

I had asked this question a few months ago, but thought I'd pose it again - has anyone here created a drill-through from a crosstab report using parameters not actually displayed within the body of the crosstab successfully? If so, would you be willing to share how you did it? This is "out of the box" functionality within Powerplay for the web, but I don't want to have to resort to creating cubes for all these - fairly simple - crosstab reports that I'd like to display to my users with drill-through capabilities.

[CoginAustin]

confused me totally..lol

Start here:

You want to drill through to what using what and if possible give a clear example. Thanks

[JoeBass]

These work for me.  Stripped down but, I think it is what you're talking about.  Do they work in your environment?

[JoeBass]

Child

[dgleason42]

OK - I'll try to be a little more clear.Ã,  :)

I have a crosstab report that provides summary data for sales revenue. Columns are months, rows are sales regions, data is revenue.

The crosstab report has three optional prompts - Client Type, Company Division and Product Group. These prompts filter corresponding query items in the crosstab's source query. These query items are not displayed in the crosstab, they are just contained in the query.

I want the user to be able to drill through from this crosstab to a list report that shows all individual sales for the intersection clicked on (the month and sales region) and filtered by any selections made in the optional prompts (type, division and group).

The problem is that the type, division and group are not being sent to the parameters in the drill-through report, even though they are available in the drill-through selections. It appears that only query items actually displayed in the crosstab actually pass values to the drill-through.

I hope that's clearer!Ã,  ???

[dgleason42]

Joe! JOE! That's IT!

The difference was placing my filtered query items as additional subjects within an existing dimension - not in adding them as additional dimensions to the report!!! I just tested this out and it works like a PEACH!

Thank you SO MUCH for the example and the help! I really appreciate it!

Modified: I'm trying to mark Joe's answer as "accepted" by clicking on the check in his message, but nothing seems to be happening...?

These work for me.Ã,  Stripped down but, I think it is what you're talking about.Ã,  Do they work in your environment?

[JoeBass]

Cool!  Glad it helped.

[dgleason42]

I spoke too soon! ACK.

When I try running the examples you provided, on the Parent crosstab, the Company is an optional parameter. If I leave that blank, then drill through to the child report, only one Company's data is shown. My guess is that it is the first company in the data, as it is generally one that starts with "A" or something like that.

[bdybldr]

When you establish your relationships between the parent and child reports, you can only specify one value for a particular data item.  This is because when defining your relationships, the only operator available is "=" (child.?Vendor? = parent.[Vendor]).  So, you can only pass one value for each parameter.  For example, if you click the Vendor XYZ hyperlink in your parent report, it will open the child report for just Vendor XYZ. 

[JoeBass]

Bummer, but its a sensible design.Ã, 

When you click on a cell in the parent, you are setting the parameters to feed the child based on information at that intersection.Ã,