The affect you are after is called a "drill-through" - which means take the high level stuff I'm looking at  and go and dredge up the transactional or granular detail behind it. 

If you are using Microsoft OLAP or Tabular cubes, this functionality can be designed into the cube using the "drill-through" action. You can also create the drill-through on ANY data source using Pyramid's 'jump to report' actions. This will allow you to attach a detail report to the summary report.  When you trigger the action from the cell, it will launch the detail report (keep the context from the summary) and effectively show you what you are after. 

The same technique can be used to chain together any reports. 

The Excel approach is highly problematic when working on real databases. When you get this effect in Excel, you are typically pulling the data from another Excel spreadsheet - which is small and defined. There could be tens or hundreds of columns that relate to a cell on a broad analytic database. If we automatically pushed a drill-through, we could dredge up a huge amount of data and columns that are not relevant and kill the database and Pyramid in doing so.

The right answer requires a curated selection of columns that you want to see in the detail. That requires picking the columns you want to see in the drill-through - which also happens to be a report building exercise....which is what the Discover tool is designed for! So build your prototypical detail report in Discover and save it. Attach it to the action. It doesn't matter what cell you click on, the report will always work.

If you have multiple summary reports, yes you need to attach the action each time. But do you need to rebuild a new report each time? mostly likely no. Build 1 detailed report then every time you execute the action, you can reuse the same detail report (off multiple summary reports) and it will show different transactions matching your selections. Better yet, you may have slightly different types of detail you want to see in different summary reports. Maybe have 2 different actions. You can control that too.

The only 'headache', is attaching the action to each summary report. This is something we will address in the future with actions defined in a model - so they are generically available all the time.

Angela,

I'm currently writing up a blog on this topic, I'll post a link here when it is ready.

Meanwhile, I notice from your example report that you are connecting to a SQLServer Analysis Services Cube, Cube\SSAS\Sales. Pyramid exposes what are called SSAS Cube Actions, through the Actions option on the right click menu you show.

SSAS Cube actions are defined within the SSAS Cube itself and can offer a number of different action types including Drill Through. This will give you exactly what you want,  a mechanism to Drill Through to the detail records for any aggregate value you pick in your report, without you having to define a report to do so.

Your cube designers can simply  enable Drill Through, in which case you get all the data fields returned, or can specific the columns to return, or can create specific SQL queries to the underlying detail data to return specific answers.

Defining cube Actions in the SSAS cube using Microsoft Visual Studio:

 

You should contact your cube administrator and ask them if they can add the actions you need. This will then flow though into Pyramid and you can execute those Actions through the Action option in the right click menu.

Accessing the SSAS Cube Actions in Pyramid:

 

Output of Drill Through action for the selected cell value:

Hope that helps.

Ian