sqlsql-serversql-viewpentaho-report-designer

Creating View with parameter [SQL,Pentaho Report Designer]


I have a SQL Query that accepts parameters for filtering report (${p_year, ${p_month}, ${p_comp}) in Pentaho Report Designer.

[In Pentaho Report Designer before using view]

SELECT INSERT_DATE,EXT,DURATION_S
FROM TABLE1
WHERE CONVERT(VARCHAR(6),INSERT_DATE,112) = CONCAT(${p_year}, ${p_month}) 
AND DIRECTION = 'OUT'
AND EXT IN (
SELECT DISTINCT TABLE2.EXT
FROM TABLE2
WHERE ((COMP = ${p_comp} OR 'ALL' = ${p_comp}))
)

I am trying to create the view to include that query in SQL Server. However, I am not sure this is a correct view or not. [In SQL Server]

CREATE VIEW CALLING AS
SELECT  INSERT_DATE,EXT,DURATION_S
FROM  TABLE1
WHERE DIRECTION = 'OUT' AND EXT IN (
SELECT DISTINCT TABLE2.EXT
FROM TABLE2
)

After that in Report Designer, I change the code to

select * from CALLING

However, I don't know how to add parameter (${p_year}, ${p_month}, ${p_comp}) into the new code. Any idea please advice.


Solution

  • Your current view seems to correct for me but use JOIN inside the VIEW

    CREATE VIEW Calling
    AS
       SELECT  
              t1.INSERT_DATE, t2.EXT, t1.DURATION_S, t2.COMP  
       FROM  table1 t1
       INNER JOIN table2 t2 ON t2.EXT = t1.EXT
       WHERE t1.DIRECTION = 'OUT'  
    

    And, you would require to call the view with WHEREclause

    SELECT * FROM Calling
    WHERE INSERT_DATE = @parameter AND COMP = @parameter 
    

    Or, you would also go with stored procedure where you could pass multiple parameters