How do I create a report with drill-down functionalities?
In this article, we will review how to create a report using the powerful drill-down functionality provided by the Altiris console.
For this specific example, we will report on TCP-IP information for computers that have more than one Network Interface Card (NIC) at level one, showing the computer name, domain, and count of rows available for the computer in the "AeX AC TCPIP" dataclass.
The drill-down will be used to show the specific TCP-IP information for the selected computer, such as MAC address, IP address, Subnet, and so on.
TIP: Drilldown Parameters are Case Sensitive. Therefore, it is recommended to use the Column name exactly as specified in the database table that is being used (i.e. _ResourceGuid, State, .etc) in the Select Statement, and in creating and using the Drilldown Parameter.
First of all, we need to create the SQL query that will return the desired information. For ease of use, we generally recommend using the SQL Query Analyzer for SQL 2000* or the Management Studio for SQL 2005* to create the base query (as this is a trial and error process that requires many iterations):
select a._ResourceGuid as 'Computer guid', b.name as 'Computer name', b.domain as 'Computer domain', count (a._ResourceGuid) as 'TCP/IP data rows'
from Inv_AeX_AC_TCPIP a
join Inv_AeX_AC_Identification b
on a._ResourceGuid = b._ResourceGuid
group by a._resourceguid, b.name, b.domain
having count (a._ResourceGuid) > 1
Then we can create the drill-down query, which is a little more simple:
-- Query 2
select a.[Mac address], a.[IP Address], a.[Subnet], a.[Subnet Mask], a.[Default Gateway]
from Inv_AeX_AC_TCPIP a
where _ResourceGuid = '%_ResourceGuid%'
order by [Mac Address]
As you can see above, we have already created a token for the query drill down: '%_ResourceGuid%'.
Now that we have both the base report and the drill-down information, we can go into the Altiris Console > Report tab. In a folder of your choice, right-click New report to create a new report. We have named this report "Computers with multiple NIC report and drill-down".
In the report builder wizard, select Enter SQL directly and copy the first SQL query above (Query 1). Click Finish.
You can now run the base report, which will show you the computers that have more than one TCP-IP configuration. Here is the result shown on a test system:
[34537 Report execution Level 0.png]
In order to create the drill-down we need to return to the report and edit it, in the console report edit page, click on Go next to Insert query at level 1, as shown here:
[34537 Insert Query at Level 1.png]
This will open the query builder. Again, click on Edit SQL Directly and copy the SQL query above (Query 2). Click Finish.
Now we need to finalize the report by linking the level 0 query to the level 1 query using the drill-down option, shown here:
[34537 Drill down button next to Level 0 query.png]
This will open the Configure Drill-downs window, where we click Add. This adds a new drill-down with default values. We fill in the values as shown below:
Click Apply to validated the drill-down configuration. Click Apply once again to save the report.
[34537 Drill down add - fill in values.png]
Now if you execute the report again and double-click on any of the computers (or right-click TCP-IP drill-down per computer) a new report window will open showing the specific information.
The parameter %_ResourceGuid% in the drill-down query is replaced with the value found in the column "_ResourceGuid" (not visible in the report) for the selected computer.
Here is the view from the drill-down on a test system:
[34537 Drill down execution.png]
As for parameter passing it depends upon how the report is written. If it is a single report, the parameters are created directly in the report.
The parameters can be named as needed but should be unique and make sense so they are easy to understand.
Note! The name of the parameter and the variable used in the SQL must match in case, as they are case sensitive.
The next step is to make sure that these named parameters are part of the SQL statement itself. They are identified with the percent characters, i.e. '%parameter%', for example:
%STARTDATE% as stdate,
%ENDDATE% as enddate,
VA.Guid AS CompanyGuid,
This SQL snippet then takes us to the next point if trying to pass parameters into a drill down. Take Notice of the “as” alias for each of the parameters: any parameters whether created as global or local parameters are used only for that report. They are not passed into subsequent drill downs. The difference between global and local is the query level.
The Level can be 1, 2, 3, and so on. The Global parameters will be accessible to all levels while parameters created locally are created for each specific level and are not accessible to other levels. None of these are passed to drill downs. Only the named or aliased columns are passable to a drill down. So when you use an alias in the SQL as show above or for example,
select Name as ComputerName, [OS Name] as OperatingSystem, Guid as ComputerGuid from vComputer
It is these aliased values that serve as parameters for a drill down. If you then open the Views icon
You will see the aliased values as illustrated with the above simple query.
You can see that ComputerGuid, ComputerName, and OperatingSystem, are all known. These are the parameters for any subsequent drill down. When creating the drill down you pass those aliased values as illustrated by the following:
In this case I intend to pass these parameters to the drill down report. This next report has SQL that looks like the following; albeit very simple and not particularly useful it should still show the intent.
select * from Item it
join vComputer on %ComputerGuid% = it.Guid
In this report I am using the 'ComputerGuid' parameter that I created early in the first report. In this fashion the parameters are passed down. The same could be done for any type of SQL query whether it is straight SQL statements or stored procedures, i.e. I have another report that has a drill calling my own stored procedure:
exec sp_vCalculateUtilization %stdate%, %enddate%, %CompanyGuid%