shery, Member
Hi all,
I want to know if there is any way to assign specific item(column/line) to against specific value of series in MS Graphs.
So my dataset has 3 columns: Date, Series, Value
Date to be displayed on X-Axis.
Series of my dataset has 4 values: A, B, C, D.
I want A, B, D to be displayed as a Column and C to be displayed as Line.
How can I achieve this?
Regards,
myDBR Team, Key Master
Hi,
the one picked to be displayed as Line is the one appearing last in the result set. So, you could add an `ORDER BY` to the query saying:
order by if (series='C', 'X', series)
or, if you update to the latest build, you can use:
select 'dbr.chart.options', 'category_order', 'A', 'B', 'D', 'C';
--
myDBR Tram
shery, Member
Hi,
Thanks for the prompt response. Option 1 worked for me.
The issue came up is that Date is getting ordered as well and ultimately dates on X-Axis of the graph are not in order.
Regards
myDBR Team, Key Master
With dates you might want to take a look at the 'datetimescale'-chart option.
--
myDBR Team
shery, Member
Hi,
Thanks for the prompt support.
How to assign specific color against specific value of series?
Regards
myDBR Team, Key Master
Hi,
by default, the colors are assigned by the order the series appear in the result set. You can either use the default colors or define the colors using the dbr.chart.color-command.
You can also define specific color per series via command:
select 'dbr.chart.options','label_color', 'Series A', '0xFF0000';
--
myDBR Team
shery, Member
Hi,
Using MSColumnLineDY: Maximum series can be 4? 3 Columns and 1 Line? I am trying to accommodate 4 Series in columns and 1 in Line, but it is not working. I am getting different results, sometimes it skips series from columns, sometimes line doesn't appear, sometimes 1 column and 1 line. I am unable to understand and specify.
I need to use MSColumnLineDY?
I need to specify order in 4 columns against 4 series.
I need to specify Series for Line so if some series is missing in dataset, Series in line isn't displayed as Column series or Column series doesn't displayed as Line series.
How can I achieve this?
Regards
myDBR Team, Key Master
There are no limits for the categories/series. There is one line layer with multiple columns.
Can you show the data/code you are using?
You can open a support ticket for this and include the SQL-export of your report to the ticket.
--
myDBR Team
shery, Member
Issue 1:
For below code 1 series is not appearing and graph is giving 3 columns ('Scheduled', 'Completed on Time', 'Net Open') and 1 Line ('Completed Late'):
SELECT 'dbr.chart', 'MSColumnLineDY','', 1200, 500;
SELECT 'dbr.chart.options', 'series', 'Scheduled', 'Completed on Time', 'Completed Late', 'Overdue', 'Net Open';
SELECT 'dbr.chart.options', 'label_color', 'Scheduled', 'Ox95B3D7'; -- BLUE
SELECT 'dbr.chart.options', 'label_color', 'Completed on Time', '0xC4D79B'; -- Green
SELECT 'dbr.chart.options', 'label_color', 'Completed Late', '0x2ECC71'; -- Light Green
SELECT 'dbr.chart.options', 'label_color', 'Overdue', '0xF1C40F'; -- Amber
SELECT 'dbr.chart.options', 'label_color', 'Net Open', '0xDA9694'; -- RED
SELECT 'dbr.chart.options','axis', 'Business Date','Number of Tasks', 'Net Open Tasks';
SELECT 'dbr.chart.options','rotateNames', 45;
Here is the dataset:
SELECT DATE_FORMAT(aa.busdate, '%d-%m-%Y') busdate,
aa.statusKPIs,
aa.`No. of Tasks`
FROM aa
WHERE date(aa.busdate) BETWEEN date(inStartDate) AND date(inEndDate);
No effect if I do this:
SELECT DATE_FORMAT(aa.busdate, '%d-%m-%Y') busdate,
aa.statusKPIs,
aa.`No. of Tasks`
FROM aa
WHERE date(aa.busdate) BETWEEN date(inStartDate) AND date(inEndDate)
ORDER BY aa.busdate ASC,
if (statusKPIs='Scheduled', 'A', statusKPIs),
if (statusKPIs='Completed on Time', 'B', statusKPIs),
if (statusKPIs='Completed Late', 'C', statusKPIs),
if (statusKPIs='Overdue', 'D', statusKPIs),
if (statusKPIs='Net Open', 'X', statusKPIs);
Series in respective Columns and Line are displayed but Dates on X-Axis are disordered if I remove (aa.busdate ASC).
Issue 2:
For below code 1 series is not appearing, 4 columns ('Scheduled', 'Completed on Time', 'Overdue', 'Net Open') are appearing.
SELECT 'dbr.chart', 'MSColumnLineDY','', 1200, 500;
SELECT 'dbr.chart.options', 'series', 'Scheduled', 'Completed on Time', 'Completed Late', 'Overdue', 'Net Open';
SELECT 'dbr.chart.options', 'label_color', 'Scheduled', 'Ox95B3D7'; -- BLUE
SELECT 'dbr.chart.options', 'label_color', 'Completed on Time', '0xC4D79B'; -- Green
SELECT 'dbr.chart.options', 'label_color', 'Completed Late', '0x2ECC71'; -- Light Green
SELECT 'dbr.chart.options', 'label_color', 'Overdue', '0xF1C40F'; -- Amber
SELECT 'dbr.chart.options', 'label_color', 'Net Open', '0xDA9694'; -- RED
SELECT 'dbr.chart.options','axis', 'Business Date','Number of Tasks', 'Net Open Tasks';
SELECT 'dbr.chart.options','rotateNames', 45;
SELECT DATE_FORMAT(aa.busdate, '%d-%m-%Y') busdate,
aa.statusKPIs,
aa.`No. of Tasks`
FROM aa
WHERE date(aa.busdate) BETWEEN date(inStartDate) AND date(inEndDate)
ORDER BY aa.busdate ASC;
I want 4 Columns whether data exists or not: 'Scheduled', 'Completed on Time', 'Completed Late', 'Overdue'
I need 1 Line: 'Net Open'
Please advise.
Regards
myDBR Team, Key Master
Can do take a SQL-export (top right corner when report is run) so we also see the data. And you can send the output to support email.
Btw, you seem to have an minor error in the color codes Ox95B3D7 should start with '0x' not letter 'O'.
--
myDBR Team
shery, Member
Here is one case: Completed Late is not showing in the output
select 'dbr.html', '<div class="dashboard">';
select 'dbr.template', '#BI_Chart_Title';
select 'HPR Trend' as 'bi_title_header1';
select 'dbr.html', '</div>';
select 'dbr.chart', 'MSColumnLineDY', '', 1200, 500;
select 'dbr.chart.options', 'series', 'Scheduled', 'Completed on Time', 'Completed Late', 'Overdue', 'Net Open';
select 'dbr.chart.options', 'label_color', 'Scheduled', 'Ox95B3D7';
select 'dbr.chart.options', 'label_color', 'Completed on Time', '0xC4D79B';
select 'dbr.chart.options', 'label_color', 'Completed Late', '0x2ECC71';
select 'dbr.chart.options', 'label_color', 'Overdue', '0xF1C40F';
select 'dbr.chart.options', 'label_color', 'Net Open', '0xDA9694';
select 'dbr.chart.options', 'axis', 'Business Date', 'Number of Tasks', 'Net Open Tasks';
select 'dbr.chart.options', 'rotateNames', 45;
select '01-10-2020' as 'busdate', 'Scheduled' as 'statusKPIs', 3 as 'No. of Tasks'
union all
select '01-10-2020', 'Completed on Time', 3
union all
select '02-10-2020', 'Scheduled', 3
union all
select '02-10-2020', 'Completed on Time', 3
union all
select '03-10-2020', 'Scheduled', 3
union all
select '03-10-2020', 'Completed on Time', 3
union all
select '04-10-2020', 'Scheduled', 3
union all
select '04-10-2020', 'Completed on Time', 3
union all
select '05-10-2020', 'Scheduled', 3
union all
select '05-10-2020', 'Completed on Time', 3
union all
select '06-10-2020', 'Scheduled', 3
union all
select '06-10-2020', 'Completed on Time', 3
union all
select '07-10-2020', 'Scheduled', 3
union all
select '07-10-2020', 'Completed on Time', 3
union all
select '08-10-2020', 'Scheduled', 3
union all
select '08-10-2020', 'Completed on Time', 3
union all
select '09-10-2020', 'Scheduled', 3
union all
select '09-10-2020', 'Completed on Time', 3
union all
select '10-10-2020', 'Scheduled', 3
union all
select '10-10-2020', 'Completed on Time', 3
union all
select '11-10-2020', 'Scheduled', 3
union all
select '11-10-2020', 'Completed on Time', 3
union all
select '12-10-2020', 'Scheduled', 3
union all
select '12-10-2020', 'Completed on Time', 3
union all
select '13-10-2020', 'Scheduled', 3
union all
select '13-10-2020', 'Completed on Time', 3
union all
select '14-10-2020', 'Scheduled', 3
union all
select '14-10-2020', 'Completed on Time', 3
union all
select '15-10-2020', 'Scheduled', 3
union all
select '15-10-2020', 'Completed on Time', 3
union all
select '16-10-2020', 'Scheduled', 3
union all
select '16-10-2020', 'Completed on Time', 3
union all
select '17-10-2020', 'Scheduled', 3
union all
select '17-10-2020', 'Completed on Time', 3
union all
select '18-10-2020', 'Scheduled', 3
union all
select '18-10-2020', 'Completed on Time', 3
union all
select '19-10-2020', 'Scheduled', 3
union all
select '19-10-2020', 'Completed on Time', 3
union all
select '20-10-2020', 'Scheduled', 3
union all
select '20-10-2020', 'Completed on Time', 3
union all
select '21-10-2020', 'Scheduled', 3
union all
select '21-10-2020', 'Completed on Time', 3
union all
select '22-10-2020', 'Scheduled', 3
union all
select '22-10-2020', 'Completed on Time', 3
union all
select '23-10-2020', 'Scheduled', 3
union all
select '23-10-2020', 'Completed on Time', 3
union all
select '24-10-2020', 'Scheduled', 3
union all
---
select '24-10-2020', 'Completed Late', 1
---
union all
select '24-10-2020', 'Completed on Time', 2
union all
select '25-10-2020', 'Scheduled', 3
union all
select '25-10-2020', 'Completed on Time', 3
union all
select '26-10-2020', 'Scheduled', 3
union all
select '26-10-2020', 'Completed on Time', 3
union all
select '27-10-2020', 'Scheduled', 3
union all
select '27-10-2020', 'Completed on Time', 3
union all
select '28-10-2020', 'Scheduled', 3
union all
select '28-10-2020', 'Completed on Time', 3
union all
select '29-10-2020', 'Scheduled', 3
union all
select '29-10-2020', 'Completed on Time', 3
union all
select '30-10-2020', 'Scheduled', 3
union all
select '30-10-2020', 'Completed on Time', 3
union all
select '31-10-2020', 'Scheduled', 3
union all
select '31-10-2020', 'Completed on Time', 3;
myDBR Team, Key Master
The chart is drawn based on the data you have. In MSColumnLineDY-chart, the multiseries columns are drawn first and then the last series occuring in data or in your case the defined 'series', will become the line layer.
In your data, you have 31 entries for 'Scheduled' and 'Completed on Time', and one entry for 'Completed Late'. As the first two are declare in series-option to be the columns, the 'Completed Late' will become the line layer. The line is drawn, but as it only contains one point the line is not visible (length bewtween a point to itself is 0). You can use the 'show_markers' option to show lines which may have gaps (so, even a line marker with one point).
select 'dbr.chart.options', 'show_markers', 1;
See the result.
You also probably want to define the series values for those series that you actually have in the data (or add NULL entry for the series to the data).
--
myDBR Team
You must log in to post.