I modified the command from:
SELECT 'dbr.search', 0;
to
SELECT 'dbr.search', '0';
Thanks for helping with the toggle functionality in the column. However, when toggling in the row, I only see the CSS color change, but the row isn't hidden. Perhaps I missed something?
-------------------------------------------------------------
this is source code:
if object_id('sp_DBR_test2','P') is not null
drop procedure sp_DBR_test2
go
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) = ''
AS
BEGIN
DECLARE @user_group_id INT;
DECLARE @noteTyGia nvarchar(255);
DECLARE @notePriceFormat nvarchar(255);
DECLARE @exchange_rate varchar(50);
SET @exchange_rate = (SELECT rate FROM cdc_KT1S_Release_App_2024..exchange_rate WHERE currency_from = @currencyType);
SET @user_group_id = (SELECT group_id FROM mydbr..mydbr_groupsusers WHERE username = @inLogin);
SET @noteTyGia = N'Tỷ giá: ' + @exchange_rate;
SET @notePriceFormat = N'Định dạng tiền: ' + @priceFormat;
SELECT 'dbr.title', 'dbr.html:<span style="font-size:1.4em">Profit and Loss Statement: January - February<span>';
IF @priceFormat != 'One'
BEGIN
SELECT 'dbr.text', 'dbr.html:<span style="text-align: left;font-size:1.1em;margin-right: 750px">' + @notePriceFormat + '<span>';
END
IF @currencyType != 'VND'
BEGIN
SELECT 'dbr.text', 'dbr.html:<span style="text-align: left;font-size:1.1em;margin-right: 800px;">' + @noteTyGia + '</span>';
END
SELECT 'dbr.parameters.show';
SELECT 'dbr.css', '
td.cell { white-space:nowrap; padding-left: 2px; padding-right: 5px; }
td.prev_year, td.prev_year a { color: #777 !important; }
th.TableHdrTableData {padding-left: 2px; padding-right: 2px}
td.cell a {text-decoration:none}
td.this_year a { color: #444 }
.whitebg {background-color:white !important; border-bottom: 1px solid #DDDDDD !important}
td.group_allocation {color: #666;}
.toggledrows .row, .toggledrows .row.odd, {background-color:white;}
.toggledrows tr.level1 td {border-bottom:none;background-color:white;}
.pl_report .toggle_img {display:none}
.acc_header {font-weight: 500;background: #F4F4F4;}
.acc_header td {border-top: 1px solid #CCC;border-bottom: 1px solid #CCC;}
.acc_header td {border-top: 1px solid #CCC;border-bottom: 1px solid #CCC;}
tr.acc_header td {border-bottom: 1px solid #666;}
.account td {padding-left: 2em !important}
.level1 td:first-child {
color: #006600; /* Màu xanh lá cây cho level1 */
padding-left: 2em;
}
.level2 td:first-child {
color: #663300; /* Màu nâu đất cho level2 */
padding-left: 4em;
}
.level3 td:first-child {
color: #330099; /* Màu xanh đậm cho level3 */
padding-left: 6em;
}
.level4 td:first-child {
color: #990033; /* Màu đỏ tối cho level4 */
padding-left: 8em;
}
';
select 'dbr.purehtml', '<input type="button" value="Hide/show detail rows" onclick="$(''.rowbutton'')[0].click();">';
select 'dbr.purehtml', '<input type="button" value="Hide/show extra columns" onclick="$(''.colbutton'')[0].click();">';
select 'dbr.javascript', 'function myfunc(checkbox) { alert("Callback"); }';
SELECT 'dbr.search', '0';
select 'dbr.toggle','[
{
"name":"Show/Hide rows",
"class":"rowbutton",
"resultclass":"toggledrows",
"rows":["account"]
}]
';
select 'dbr.toggle','[
{
"name":"Show/Hide columns",
"class":"colbutton",
"cols":["Budget"]
}]
';
select 'dbr.toggle.options', 'speed', 700;
SELECT 'dbr.resultclass', 'pl_report';
SELECT 'dbr.hidecolumns', 'rowclass';
SELECT 'dbr.crosstab', 'Month';
SELECT 'dbr.colclass', 'account', 'padded_account border_left freeze';
SELECT 'dbr.lockcolumns', 'account';
SELECT 'dbr.colclass', 'ty_ga', 'border_left border_right this_year group_allocation';
SELECT 'dbr.colclass', 'Budget', 'budget';
SELECT 'dbr.colclass', 'ty', 'this_year';
SELECT 'dbr.colclass', '%', '%';
SELECT 'dbr.colclass', 'ly', 'border_right prev_year';
SELECT 'dbr.colclass', 'chg', 'border_right chg';
SELECT 'dbr.header.colclass', 'ty_ga', 'group_allocation';
SELECT 'dbr.header.colclass', 'ly', 'prev_year';
SELECT 'dbr.header.colclass', 'chg', 'chg';
SELECT 'dbr.rowclass', 'rowclass';
SELECT 'dbr.lockcolumns', 'account';
SELECT 'dbr.summary.calc', 'chg', '[ly]==0 ? 0 : ([ty]-([ly]))/abs([ly])*100';
SELECT 'dbr.colstyle', 'ty_ga', '%.0f; ;%.0f';
SELECT 'dbr.colstyle', 'ty', '%.0f; ;%.0f';
SELECT 'dbr.colstyle', 'ly', '%.0f; ;%.0f';
SELECT 'dbr.colstyle', 'chg', '%.0f %; ;%.0f %';
SELECT 'dbr.colstyle', '%', '%.0f %; ;%.0f %';
SELECT 'dbr.nosort', 0;
SELECT 'dbr.hidecolumn', 'ty_ga';
SELECT 'dbr.report', 'sp_DBR_pl_child', '[account]', 'inAccountName=account';
--có đoạn code window, new popup
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, ',')))
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;
END
go
