Please help me update UI and Sparkline!

myDBR Forums » General discussion

Please help me update UI and Sparkline!

(6 posts) (2 voices)

Started by vannc
Latest reply from vannc

Tags:

No tags yet.

vannc, Member

Dear Team!

Since my data has columns for date, month, and year, I would like to update the UI in the report. Specifically, I need the year to be above the month.
https://drive.google.com/file/d/1p0dIFyiAZn9K60DXK3Ur26Mio6rwr9GM/view?usp=sharing

example
year
2023 | 2024
month
1|2|3 | 1
-------------------------
And I am struggling with the sparkline; I don't know how to accomplish this with the sum(b.present) function within the date range. For example, if I select a date range from 2023-01-01 to 2024-03-31, then I would like to have 'sum(b.present) of 202301, sum(b.present) of 202401, sum(b.present) of 202402, sum(b.present) of 202403' displayed as the sparkline.
Thanks Team!
vannc, Member

I'm sorry, this is my 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) = '',
@filterAccount varchar(255) = ''
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 khns..dim_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.acc_header 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}
--tr.account td.padded_account {padding-left: 30px; background-color: white;}
--tr.account {background-color: white;}
.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;
}
.account td:first-child {
color: #990033; /* Màu đỏ tối cho level4 */
padding-left: 8em;
}
.spark {padding:0}
';
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.javascript', '
--<script type="text/javascript">
--function toggleRows() {
-- $(".rowclass").toggle();
--}
--</script>';

--select 'dbr.javascript', '
--function toggleDetailRows() {
-- var detailRows = document.getElementsByClassName("toggledrows")[0].getElementsByClassName("rowclass");
-- console.log(detailRows);

-- var isHidden = detailRows[0].style.display === "none";

-- for (var i = 0; i < detailRows.length; i++) {
-- detailRows[i].style.display = isHidden ? "" : "none";
-- }

-- var button = document.getElementById("toggleDetailRows");
-- button.value = isHidden ? "Hide detail rows" : "Show detail rows";
--}
--';

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', 'account_code';
--select 'dbr.colclass', 'Sparkline', 'spark';
SELECT 'dbr.colclass', 'ty_ga', 'border_left border_right this_year group_allocation';
SELECT 'dbr.colclass', 'ty', 'this_year';
SELECT 'dbr.colclass', 'Budget', 'budget';
SELECT 'dbr.colclass', '%', '%'; --=recent/budget_value
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', 'MOM', 'MOM';
SELECT 'dbr.colclass', '%MOM', '%MOM'; --(recent/value_lastmonth)-1
SELECT 'dbr.header.colclass', 'ly', 'prev_year';
SELECT 'dbr.header.colclass', 'chg', '%YOY'; --(recent/value_lastyear)-1
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_test2', '[account]', 'new_window', 'filterAccount=account';
--có đoạn code window, new popup
--'new_window', '[new_window]'
--declare table sparkline code, chart_value (string)
--insert into bangphu (tu bang chinh lay recent)

--bang chinh
--select chart_value as 'Sparkline' from bangphu join bangchinh on a.code = b.code
-- Kiểm tra nếu bảng tồn tại thì xóa đi
DROP TABLE IF EXISTS pl_sparkline_temple;

-- Tạo bảng mới
CREATE TABLE pl_sparkline_temple (
account_code VARCHAR(255),
actual DECIMAL(10,2),
month VARCHAR(50)
);

-- Insert dữ liệu vào bảng mới
INSERT INTO pl_sparkline_temple (account_code, actual, month)
SELECT
b.code as 'account_code',
--sum(b.recent) as 'Sparkline',
--CONCAT(
-- SUM(CASE WHEN b.month = 1 THEN b.recent ELSE 0 END), ',',
-- SUM(CASE WHEN b.month = 2 THEN b.recent ELSE 0 END)
--) AS 'Sparkline',
--11, 12, 1, 2, 3--
--STRING_AGG(CAST(b.recent AS VARCHAR(20)), ',') WITHIN GROUP (ORDER BY b.month) AS 'Sparkline[max]',
--'8,4,1,0,6,0,1,4,4,10,10,8,10,0,2,0,4,6,5,9,10' as 'Sparkline',
sum(b.recent) as 'actual',
b.month as 'month'
FROM cdc_KT1S_Release_App_2024..pl_report 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 (b.date BETWEEN @startDate AND @endDate)
GROUP BY
CASE
WHEN @language = 'vni' THEN b.description
WHEN @language = 'eng' THEN b.e_name
END,
b.row_class,
b.pos,
b.date,
b.month,
b.year,
b.code
ORDER BY b.pos, b.date, b.month, b.year;
------------------------------------------------------------
SELECT
CASE
WHEN @language = 'vni' THEN b.description
WHEN @language = 'eng' THEN b.e_name
END AS '[account]',
b.code as 'account_code',
--sum(b.recent) as 'Sparkline',

--CONCAT(
-- SUM(CASE WHEN b.month = 1 THEN b.recent ELSE 0 END), ',',
-- SUM(CASE WHEN b.month = 2 THEN b.recent ELSE 0 END)
--) AS 'Sparkline',
--11, 12, 1, 2, 3--

--STRING_AGG(CAST(b.recent AS VARCHAR(20)), ',') WITHIN GROUP (ORDER BY b.month) AS 'Sparkline[max]',

--'8,4,1,0,6,0,1,4,4,10,10,8,10,0,2,0,4,6,5,9,10' as 'Sparkline',
b.month AS Month,
3 AS 'GA[ty_ga]',
--ACTUAL
CASE
WHEN @currencyType = 'USD' THEN
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.recent) / 1000 / @exchange_rate
WHEN @priceFormat = 'Million' THEN sum(b.recent) / 1000000 / @exchange_rate
WHEN @priceFormat = 'Billion' THEN sum(b.recent) / 1000000000 / @exchange_rate
ELSE sum(b.recent) / @exchange_rate
END
ELSE
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.recent) / 1000
WHEN @priceFormat = 'Million' THEN sum(b.recent) / 1000000
WHEN @priceFormat = 'Billion' THEN sum(b.recent) / 1000000000
ELSE sum(b.recent)
END
END AS 'Actual[ty]',
--BUDGET
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',
--%BUDGET ACTUAL
sum(b.recent) / sum(b.budget_value) * 100 as '%',
--MOM
CASE
WHEN @currencyType = 'USD' THEN
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.value_lastmonth) / 1000 / @exchange_rate
WHEN @priceFormat = 'Million' THEN sum(b.value_lastmonth) / 1000000 / @exchange_rate
WHEN @priceFormat = 'Billion' THEN sum(b.value_lastmonth) / 1000000000 / @exchange_rate
ELSE sum(b.value_lastmonth) / @exchange_rate
END
ELSE
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.value_lastmonth) / 1000
WHEN @priceFormat = 'Million' THEN sum(b.value_lastmonth) / 1000000
WHEN @priceFormat = 'Billion' THEN sum(b.value_lastmonth) / 1000000000
ELSE sum(b.value_lastmonth)
END
END AS 'MOM',
--%MOM
(sum(recent)/sum(value_lastmonth)-1) * 100 as '%MOM',
CASE
WHEN @currencyType = 'USD' THEN
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.value_lastyear) / 1000 / @exchange_rate
WHEN @priceFormat = 'Million' THEN sum(b.value_lastyear) / 1000000 / @exchange_rate
WHEN @priceFormat = 'Billion' THEN sum(b.value_lastyear) / 1000000000 / @exchange_rate
ELSE sum(b.value_lastyear) / @exchange_rate
END
ELSE
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.value_lastyear) / 1000
WHEN @priceFormat = 'Million' THEN sum(b.value_lastyear) / 1000000
WHEN @priceFormat = 'Billion' THEN sum(b.value_lastyear) / 1000000000
ELSE sum(b.value_lastyear)
END
END AS 'Year before[ly]',
(SUM(b.recent) - SUM(b.value_lastyear)) / NULLIF(SUM(b.value_lastyear), 0) * 100 AS 'Chg%[chg]',
b.row_class AS 'rowclass'
FROM cdc_KT1S_Release_App_2024..pl_report 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 (b.date BETWEEN @startDate AND @endDate)
GROUP BY
CASE
WHEN @language = 'vni' THEN b.description
WHEN @language = 'eng' THEN b.e_name
END,
b.row_class,
b.pos,
b.date,
b.month,
b.year,
b.code
ORDER BY b.pos, b.date, b.month, b.year;
select 'dbr.javascript', "$('.spark').sparkline('html', { type: 'line', height: '30px', width: '220px' });", 'onload';
select @startDate,'and', @endDate;
END
go
myDBR Team, Key Master
Since my data has columns for date, month, and year
'

Shoud't yuo be able to derive the year and month from tge date?

Instead of using separate year and month, you could use YYYY/MM as the crosstab field and you can construct from the date.

P.S. Instead of using
```
DROP TABLE IF EXISTS pl_sparkline_temple;

-- Tạo bảng mới

CREATE TABLE pl_sparkline_temple )
```
You can use a temporary table:
```
CREATE TABLE #pl_sparkline_temple )
```
The beneft is that if mutiple users run the report at same time, they will not interfere eachother.

--
myDBR Team
vannc, Member

Thank you to the team for the information about temporary tables and the concat(month,'/',year) function. However, my P&L chart is still encountering difficulties. The report is showing duplicate or triplicate values. I suspect there might be an issue with my two temporary tables or join section from pl_report to pl_sparkline_temple_2 .
Here is my code, and Sparkline chart care about b.recent
----------------------------------------------------------
this is pl_report table columns
| code | description | e_name | pos | ga | row_class | group_id | month | year | Version | ma_bp | recent | budget_value | department_name | date | lastmonth | lastyear | value_lastmonth | value_lastyear |

------------------------------------------------------------
This is link image: https://drive.google.com/file/d/19LGxf8UZAUtzkVMhKhqq2dJ3ukGrzB0j/view?usp=drive_link
------------------------------------------------------------
-- Create first temporary table
CREATE TABLE #pl_sparkline_temple (
account_code VARCHAR(255),
actual DECIMAL(10,2),
month_value VARCHAR(50),
year_value int
);

-- Insert data to temporary table
INSERT INTO pl_sparkline_temple (account_code, actual, month_value, year_value)
SELECT
b.code as 'account_code',
sum(b.recent) as 'actual',
b.month AS 'month_value',
b.year AS 'year_value'
FROM cdc_KT1S_Release_App_2024..pl_report 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 (b.date BETWEEN @startDate AND @endDate)
and b.group_id = 1
GROUP BY
CASE
WHEN @language = 'vni' THEN b.description
WHEN @language = 'eng' THEN b.e_name
END,
b.row_class,
b.pos,
b.date,
b.month,
b.year,
concat(b.month,'/',b.year),
b.code,
b.group_id
ORDER BY b.pos, b.date, b.month, b.year;
------------------------------------------------------------
-- create the second temporary table
CREATE TABLE #pl_sparkline_temple2 (
account_code NVARCHAR(50),
Actuals NVARCHAR(MAX)
)
INSERT INTO pl_sparkline_temple_2 (account_code, Actuals)
SELECT DISTINCT
account_code,
STUFF((
SELECT ',' + CAST(Actual AS NVARCHAR(MAX))
FROM pl_sparkline_temple AS t2
WHERE t1.account_code = t2.account_code
FOR XML PATH('')
), 1, 1, '') AS Actuals
FROM pl_sparkline_temple AS t1
------------------------------------------------------------
--SELECT full of report join pl_sparkline_temple_2 on b.code = temp.account_code
SELECT
CASE
WHEN @language = 'vni' THEN b.description
WHEN @language = 'eng' THEN b.e_name
END AS '[account]',
b.code as 'account_code',
temp.Actuals as 'Sparkline',
concat(b.month,'/',b.year) AS Month,
3 AS 'GA[ty_ga]',
--ACTUAL
CASE
WHEN @currencyType = 'USD' THEN
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.recent) / 1000 / @exchange_rate
WHEN @priceFormat = 'Million' THEN sum(b.recent) / 1000000 / @exchange_rate
WHEN @priceFormat = 'Billion' THEN sum(b.recent) / 1000000000 / @exchange_rate
ELSE sum(b.recent) / @exchange_rate
END
ELSE
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.recent) / 1000
WHEN @priceFormat = 'Million' THEN sum(b.recent) / 1000000
WHEN @priceFormat = 'Billion' THEN sum(b.recent) / 1000000000
ELSE sum(b.recent)
END
END AS 'Actual[ty]',
--BUDGET
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',
--%BUDGET ACTUAL
sum(b.recent) / sum(b.budget_value) * 100 as '%',
--MOM
CASE
WHEN @currencyType = 'USD' THEN
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.value_lastmonth) / 1000 / @exchange_rate
WHEN @priceFormat = 'Million' THEN sum(b.value_lastmonth) / 1000000 / @exchange_rate
WHEN @priceFormat = 'Billion' THEN sum(b.value_lastmonth) / 1000000000 / @exchange_rate
ELSE sum(b.value_lastmonth) / @exchange_rate
END
ELSE
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.value_lastmonth) / 1000
WHEN @priceFormat = 'Million' THEN sum(b.value_lastmonth) / 1000000
WHEN @priceFormat = 'Billion' THEN sum(b.value_lastmonth) / 1000000000
ELSE sum(b.value_lastmonth)
END
END AS 'MoM',
--%MOM
(sum(b.recent)/sum(b.value_lastmonth)-1) * 100 as '%MoM',
CASE
WHEN @currencyType = 'USD' THEN
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.value_lastyear) / 1000 / @exchange_rate
WHEN @priceFormat = 'Million' THEN sum(b.value_lastyear) / 1000000 / @exchange_rate
WHEN @priceFormat = 'Billion' THEN sum(b.value_lastyear) / 1000000000 / @exchange_rate
ELSE sum(b.value_lastyear) / @exchange_rate
END
ELSE
CASE
WHEN @priceFormat = 'Thousand' THEN sum(b.value_lastyear) / 1000
WHEN @priceFormat = 'Million' THEN sum(b.value_lastyear) / 1000000
WHEN @priceFormat = 'Billion' THEN sum(b.value_lastyear) / 1000000000
ELSE sum(b.value_lastyear)
END
END AS 'YoY[ly]',
(SUM(b.recent) - SUM(b.value_lastyear)) / NULLIF(SUM(b.value_lastyear), 0) * 100 AS 'Chg%[chg]',
b.row_class AS 'rowclass'
FROM cdc_KT1S_Release_App_2024..pl_report b join pl_sparkline_temple_2 temp on b.code = temp.account_code
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 (b.date BETWEEN @startDate AND @endDate)
and b.group_id = 1
GROUP BY
CASE
WHEN @language = 'vni' THEN b.description
WHEN @language = 'eng' THEN b.e_name
END,
b.row_class,
b.pos,
b.date,
b.month,
b.year,
concat(b.month,'/',b.year),
b.code,
b.group_id,
temp.account_code,
temp.Actuals
ORDER BY b.pos, b.date, b.month, b.year;
---------------------------------------------
myDBR Team, Key Master

If you create a temporary table as #pl_sparkline_temple, you should also use it with the same name (#pl_sparkline_temple). The pl_sparkline_temple and #pl_sparkline_temple are two separate tables. First the a normal table and the second is the temporary one.

--
myDBR Team
vannc, Member

Thanks, Team! I have solved the problem now.

I've noticed two issues:

- The difference between temporary tables and normal tables.
- The data types of columns in table #pl_sparkline_temple are different from table pl_report, so only some data can be cast, otherwise, it results in null values.
Best regards,
Vannc

Reply

You must log in to post.