Helllo there,
I have a monthly corportae reports which generates around 30 different KPIs (Key Performance Indicators). Each KPI is being generated using a different query from a number of joined tables in a SQL Server database.
In an attempt to automate the report generation process, I've created a table in my database, in which I have stored the variable parts in all the 30 queries for the KPIs.
The table is comprised of the following columns: (This is just a demo table)
KPI_Name Criteria_1 Criteria_2 Criteria_3
======= ======== ======== =======
KPI_1 XYZ ABC KLM
KPI_2 QWE GHJ MNO
KPI_3 ........ ....... ........
The query for KPI_1 is:
Select sum(Sales) from myTable
WHERE 1st_Criteria = 'XYZ' and 2nd_Criteria = 'ABC' and 3rd_Criteria = 'KLM'
The query for KPI_2 is:
Select sum(Sales) from myTable
WHERE 1st_Criteria = 'QWE' and 2nd_Criteria = 'GHJ' and 3rd_Criteria = 'MNO'
and so on and so forth.
The way I plan to generate the report is through a stored procedure, in which I will read the entire contents of my Criteria Table into a Cursor, and then construct my select statement in dynamic SQL:
DECLARE myCursor CURSOR FOR SELECT KPI_Name, Criteria_1, Criteria_2, Criteria_3
FROM myTable
OPEN myCursor
FETCH NEXT FROM myCursor
INTO @KPI_Name, @Criteria_1, @Criteria_2, @Criteria_3
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @SQL as varchar(200)
Set @SQL = 'Insert into Output_Table (KPI_Value) Select sum(Sales) from myTable WHERE
1st_Criteria = ' + @Criteria_1 +
' and 2nd_Criteria = ' + @Criteria_2 +
' and 3rd_Criteria = ' @Criteria_3
exec sp_executesql @SQL
Could you please advise if there is a better to automate the report generation,? Specifically, is there a better alternative to using Cursor to loop through the 30 different KPI queries?
Many thanks in advance for your great support.
Hani
Start Free Trial
