The way the drill-down tool operates

From Sage Intelligence Knowledgebase
Jump to: navigation, search

The drill down works by taking parameters you specify on your "main" report and passing them as parameters to a second report you specified, running it out and returning the results.

This is pretty much the same as the scheduled report string (except the filter parameters are dynamic) where Alchemex is launched as a service running out a specific report based on defined parameters.

The Drill down plugin is very easy to use, as per the following example:

In the Administrator Module

1.Create a new container linking to the relevant table/tables you want to extract drilldown data from.

2.Create the expressions for each of the fields you wish to extract drilldown data from.

In the Report Manager

1.Create a new report (the one you will use for the drill down) - lets call it MyDrillDown

2.Add the columns you wish to see in your report

3.Add the parameters you wish to filter on (i.e. Account Number & Period)

Go to the .xlt template of the existing report you wish to use as the input source for your dynamic drill down.

Now go to "Report Tools" in the Excel menu bar then select "Drill-Down"

Click on the button next to Report Id/Code and then select the report you created earlier from the list.(MyDrillDown)

Now select your "Source Sheet" (Excel Worksheet") and then type in the column numbers representing the column/s containing your input variables.

e.g. Lets say column 1 contains the Account Number and column 3 the Period.

You would type 1;3 into the "Report Parameters Column" textbox. now select OK and that's it.

(or 3;1 depending on the order of your Parameter in the Report)

Now when you select a cell in your worksheet and then select "Report Tools" in the Excel menu bar then select "Drill-Down" and then "Excecute."

the contents from cell 1 and 3 in the row you selected will be passed on as the input variable for your "MyDrillDown" Report.

Basically you parameters can be columns or even direct cell references (preferably Named Ranges) from e.g. Sheet2 etc.

Questions

1.When you are setting up MyDrillDown, i assume it is a parameter that we set up (i.e. not a filter)?

2.Is it correct that we leave part of the comparison blank (i.e. Account = [blank]) and then Alchemex fills in the blank from the Main Report sheet when you click on the drill down tool?

Answers

1.Yes you set up Parameters (these parameters are the "receptors" of whatever parameter info you pass to them from the drill down report)

2)Yes you would leave the "default input" as blank.

e.g. Your report has a drill down which when activated, launches your Drill Down "Sub Report" and passes the following as variables 1000-200,10 to it.

The sub report has two Parameters called Account and Period (both have blank default inputs) As 1000-200 is the first parameter passed it will be assigned to the Account Parameter and 10 will be assigned to the Period Parameter.