nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

I need help with showing data

darksylvok

Hello Guys, im new in the forum, i arrived here searching in the web my problems. 
 
I'm working with a mysql database. 
 
i have this query that include projects in guarantee, with their start date and duration, i need to order it by year and month, and when i have nulls, i need to show them too like this image. 
<img src='http://imageshack.us/a/img4/2277/imagen1pwb.jpg' alt='Posted Image' class='bbc_img' /> 
 
the big problem is, i need something like this image in the birt design. 
<img src='http://img585.imageshack.us/img585/6295/imagen1tr.jpg' alt='Posted Image' class='bbc_img' /> 
 
i'll show you the dataset query, is in a stored procedure because Birt couldn't use any commands like "DATE_FORMAT", "PERIOD_DIFF".... 
 
<blockquote class='ipsBlockquote' >SELECT 
FECHAS.ANIO AS ANIO, FECHAS.MES AS MES, SEL_INT . * 
FROM 
(SELECT 
SEL2.PROYECTO, 
SEL2.CLAVE, 
SUM(SEL2.IMPUTADO), 
SEL2.MES_GAR, 
SEL2.INICIO_GAR AS INICIO_GAR, 
SEL2.FIN_GAR, 
SEL2.DURACION_GAR AS DURACION_GAR, 
SEL2.MES_IMP AS MES_IMP, 
SEL2.ANO_IMP 
FROM 
(SELECT 
SEL1.PRO AS PROYECTO, 
SEL1.PROKEY AS CLAVE, 
LOG.`timeworked` AS IMPUTADO, 
PERIOD_DIFF(DATE_FORMAT(LOG.STARTDATE, '%Y%m'), DATE_FORMAT(SEL1.INICIO, '%Y%m')) + 1 AS MES_GAR, 
SEL1.INICIO AS INICIO_GAR, 
SEL1.FIN AS FIN_GAR, 
SEL1.DURACION AS DURACION_GAR, 
DATE_FORMAT(LOG.STARTDATE, '%m') AS MES_IMP, 
DATE_FORMAT(LOG.STARTDATE, '%Y') AS ANO_IMP 
FROM 
(SELECT 
PRO.ID AS PROID, 
PRO.`pname` AS PRO, 
PRO.`pkey` AS PROKEY, 
ISSUE.`pkey` AS PKEY, 
(SELECT 
NUMBERVALUE 
FROM 
JIRA42.`customfieldvalue` AS VAL 
WHERE 
VAL.`customfield` = '10470' 
AND VAL.ISSUE = ISSUE.ID) AS DURACION, 
(SELECT 
DATEVALUE 
FROM 
JIRA42.`customfieldvalue` AS VAL 
WHERE 
VAL.`customfield` = '10081' 
AND VAL.ISSUE = ISSUE.ID) AS INICIO, 
(SELECT 
DATEVALUE 
FROM 
JIRA42.`customfieldvalue` AS VAL 
WHERE 
VAL.`customfield` = '10455' 
AND VAL.ISSUE = ISSUE.ID) AS FIN 
FROM 
JIRA42.`jiraissue` AS ISSUE 
JOIN JIRA42.`project` AS PRO ON (PRO.ID = ISSUE.PROJECT 
AND ISSUE.`issuetype` = '13') 
JOIN JIRA42.`customfieldvalue` AS CFV ON (CFV.ISSUE = ISSUE.ID 
AND CFV.`customfield` = '10087' 
AND CFV.STRINGVALUE = 'Fase 07 - Mantenimiento Correctivo')) AS SEL1, JIRA42.`worklog` AS LOG 
JOIN JIRA42.`jiraissue` AS ISSUE2 ON (LOG.`issueid` = ISSUE2.ID) 
WHERE 
ISSUE2.`project` = SEL1.PROID 
AND LOG.STARTDATE BETWEEN SEL1.INICIO AND SEL1.FIN) SEL2 
GROUP BY PROYECTO , MES_IMP , ANO_IMP) AS SEL_INT 
RIGHT OUTER JOIN 
(SELECT 
ANIO.year_id AS ANIO, MES.a_month_id AS MES 
FROM 
all_years AS ANIO, all_months AS MES) AS FECHAS ON (FECHAS.ANIO = SEL_INT.ANO_IMP 
AND FECHAS.MES = SEL_INT.MES_IMP 
AND FECHAS.ANIO BETWEEN DATE_FORMAT(SEL_INT.INICIO_GAR, '%Y') AND DATE_FORMAT(DATE_ADD(SEL_INT.INICIO_GAR, 
INTERVAL DURACION_GAR MONTH), 
'%Y')) 
ORDER BY ANIO , MES;</blockquote>
 
this sql query shows this,(i attached it in a csv) 
<a class='bbc_url' href='http://www.mediafire.com/?d8c79zagk9bzvkt'>SQL_CSV</a> 
 
Thanks for your help!! 
if you need anything else just ask for it

Find more posts tagged with

Comments

johnw

The attached report design will get you started. The report is basically just grouping based on the project, year, and month columns. If you select the column in the report design table, you can select "Suppress Duplicates". For the "null" icon, I just grabbed a generic error icon from an online image. If you use two report items in the same cell, you can use a Visibility expression to hide one and show the other based on a data condition. In this case I would hide or show the "null" icon based on if the value was null.

johnw

And the report design

darksylvok

Thanks a million for your answer johnw but it can't help my a lot, sorry for make you lose your time. 
 
Maybe my explanation is not specific. 
 
When i tried to make a group to create a table grouped by one project, it repeat all data. 
<img src='http://img405.imageshack.us/img405/7329/image3lll.jpg' alt='Posted Image' class='bbc_img' /> 
 
I need one table for each project. I use a table with the same dataset, whith only the project column and a group by project. Inside i had put the same data set with all columns. But it's no working. 
Sorry for my poor explanation. 
I attach one image more 
 
<img src='http://img248.imageshack.us/img248/9098/image4tvq.jpg' alt='Posted Image' class='bbc_img' /> 
 
Thanks for your help

johnw

So what you can do is have a table inside another table. So the Outside table just gets project numbers and names and displays the outer table. The inside table displays the details formatted like in the wireframe, and have the inside table parameterized to take in the outer tables project ID.

darksylvok

Thanks johnw.

I do it before, i put a table with the IDs proyect, i make a group by ID and put inside the table with all data i need. Usually, the table inside is grouping by the ID, and the look of this is like:

ID 1
ID1 name_proyect1 xxx1
ID1 name_proyect2 xxx2
ID 2
ID2 name_proyect1 xxx1
ID2 name_proyect2 xxx2
ID 3
ID3 name_proyect1 xxx1
ID3 name_proyect2 xxx2
.....

the data of the table inside is referenced to the ID outside. But in this new case, is not working.

ask for anything you need to help me.

thanks