FerencMantfeld
11th January 2010, 02:51 PM
IF you list a set of tables that conform to some rule, and then you want a drilldown or master-detail report that exposes the data in the table, BI Plus offers a way to do this.
Business Case: Display all the table names in a Sql Server database and the number of rows in the table. If a user expands any particular table, select everything in the table to show the contents.
top level (parent Report) :
SELECT [TableName] = so.name, [RowCount] = MAX(si.rows)
FROM sysobjects so, sysindexes si
WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY 2 DESC
Create a drilldown key (MASTER_KEY) on "TableName".
In the child report, create a hidden parameter called P_TABLE, where the expression = <<MASTER_KEY>>
The query of the child is:
select * from <<P_TABLE#DISPLAY>>
Business Case: Display all the table names in a Sql Server database and the number of rows in the table. If a user expands any particular table, select everything in the table to show the contents.
top level (parent Report) :
SELECT [TableName] = so.name, [RowCount] = MAX(si.rows)
FROM sysobjects so, sysindexes si
WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY 2 DESC
Create a drilldown key (MASTER_KEY) on "TableName".
In the child report, create a hidden parameter called P_TABLE, where the expression = <<MASTER_KEY>>
The query of the child is:
select * from <<P_TABLE#DISPLAY>>