topic Re: SQL Stored Proc to Qlikview in QlikView

SQL Stored Proc to Qlikview

EQ — Sat, 16 Nov 2024 21:14:17 GMT

Hi Expert,

I have an SSRS report and it calls a stored procedure which is like below. Each query has different date filters of different tables(Date1, Date2, Date4), in SSRS users can just retrieve the data from the database directly via the SSRS interface and the most important thing is that they can choose any from_dte and to_dte to get that period of data, but QlikView will load all the data to QVW file firstly. My question is how to achieve the report same as SSRS in QlikView via using the params to get the data by users themselves?

ALTER PROC usp_GetInfo
(@from_dte DATE,
@TO_dte DATE
)

SELECT T1.Name as Name,
T2.Amount as Amount,
T2.Type as AmountType
FROM T1 JOIN T2 ON T1.ID = T2.ID
WHERE T1.Date1 > @from_dte
AND T1.Date1 < @TO_dte

UNION

SELECT T2.Name as Name,
T3.Amount2 as Amount
T3.Type2 as AmountType
FROM T2 JOIN T3 ON T2.ID = T3.ID
WHERE T2.Date2 > @from_dte
AND T2.Date2 < @TO_dte

UNION

SELECT T4.Name as Name,
T5.Amount3 as Amount
T5.Amount3 as AmountType
FROM T4 JOIN T5 ON T4.ID = T5.ID
WHERE T4.Date4 > @from_dte
AND T4.Date4 < @TO_dte

Re: SQL Stored Proc to Qlikview

amrinder — Fri, 15 Mar 2019 06:17:10 GMT

Hi,

Define vFromDate and vToDate variables and try below script:

Table1:

Load

ID ,

Name

From T1 where Date1>$(vFromDate) and Date1<$(vToDate);

Inner Join

Load

ID,

Amount ,

Type as AmountType

From T2;

Similarly do for other tables and concatenate them.

Re: SQL Stored Proc to Qlikview

EQ — Fri, 15 Mar 2019 07:15:48 GMT

Hi,

Thanks for your suggestion. It looks like the users need to reload the QV every time when choosing different params in QV interface.

I forgot to tell the real situation, the users can only access the report via Access Point. It seems they are unable to reload the document by themselves.

Currently, users can choose any from_date and to_date via SSRS interface, but in Qlikview we need to load all the data firstly, so looks like I cannot return the same result as SSRS report cause each block code has different filters.