Dear Team!
I have a problem with linking the report. When I click on an account in the row, the new tab does not contain the "request param @filterAccount" in the link (report.php?r=8&u1=&u2=&u3=&u5=One&u6=VND&u7=vni&u8=&p9=&m=5&h=b605fa87fc1bb4025670811b88d38c13e57bc51e). This is my source code:
---------------------------
CREATE PROCEDURE sp_DBR_test2
@filterDepartment NVARCHAR(255) = '',
@startDate date,
@endDate date,
@inLogin VARCHAR(128) = '',
@priceFormat VARCHAR(20) = 'One',
@currencyType VARCHAR(20) = 'VND',
@language VARCHAR(50) = 'vni',
@version varchar(50) = '',
@filterAccount varchar(255) = ''
AS
BEGIN
SELECT 'dbr.report', 'sp_DBR_test2', '[account]', 'new_window', '@filterAccount=account';
SELECT
CASE
WHEN @language = 'vni' THEN b.description
WHEN @language = 'eng' THEN b.e_name
END AS '[account]',
b.month AS Month,
3 AS 'GA[ty_ga]',
CASE
WHEN @currencyType = 'USD' THEN
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.budget_value) / 1000 / @exchange_rate
WHEN @priceFormat = 'Million' THEN sum(b.budget_value) / 1000000 / @exchange_rate
WHEN @priceFormat = 'Billion' THEN sum(b.budget_value) / 1000000000 / @exchange_rate
ELSE sum(b.budget_value) / @exchange_rate
END
ELSE
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.budget_value) / 1000
WHEN @priceFormat = 'Million' THEN sum(b.budget_value) / 1000000
WHEN @priceFormat = 'Billion' THEN sum(b.budget_value) / 1000000000
ELSE sum(b.budget_value)
END
END AS 'Budget',
CASE
WHEN @currencyType = 'USD' THEN
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.no2024) / 1000 / @exchange_rate
WHEN @priceFormat = 'Million' THEN sum(b.no2024) / 1000000 / @exchange_rate
WHEN @priceFormat = 'Billion' THEN sum(b.no2024) / 1000000000 / @exchange_rate
ELSE sum(b.no2024) / @exchange_rate
END
ELSE
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.no2024) / 1000
WHEN @priceFormat = 'Million' THEN sum(b.no2024) / 1000000
WHEN @priceFormat = 'Billion' THEN sum(b.no2024) / 1000000000
ELSE sum(b.no2024)
END
END AS 'Period[ty]',
sum(b.no2024) / sum(b.budget_value) * 100 as '%',
CASE
WHEN @currencyType = 'USD' THEN
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.no2023) / 1000 / @exchange_rate
WHEN @priceFormat = 'Million' THEN sum(b.no2023) / 1000000 / @exchange_rate
WHEN @priceFormat = 'Billion' THEN sum(b.no2023) / 1000000000 / @exchange_rate
ELSE sum(b.no2023) / @exchange_rate
END
ELSE
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.no2023) / 1000
WHEN @priceFormat = 'Million' THEN sum(b.no2023) / 1000000
WHEN @priceFormat = 'Billion' THEN sum(b.no2023) / 1000000000
ELSE sum(b.no2023)
END
END AS 'Year before[ly]',
(SUM(b.no2024) - SUM(b.no2023)) / NULLIF(SUM(b.no2023), 0) * 100 AS 'Chg%[chg]',
b.row_class AS 'rowclass'
FROM cdc_KT1S_Release_App_2024..view_detail_v2 b
where b.month is not null and (@filterDepartment = '' OR b.ma_bp IN (SELECT REPLACE(value, '''', '') FROM STRING_SPLIT(@filterDepartment, ',')))
and (@version = '' OR b.Version IN (SELECT REPLACE(value, '''', '') FROM STRING_SPLIT(@version, ',')))
and (@filterAccount = '' OR b.description = @filterAccount)
GROUP BY b.month,
CASE
WHEN @language = 'vni' THEN b.description
WHEN @language = 'eng' THEN b.e_name
END,
b.row_class,
b.pos
ORDER BY b.pos, b.month;
-----------------------------
Thanks Team!
