Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Needing some help to show data
Needing some help to show data [message #994407] Thu, 27 December 2012 03:00 Go to next message
Luis Daniel Santigosa Rodriguez is currently offline Luis Daniel Santigosa Rodriguez
Messages: 2
Registered: December 2012
Junior Member
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 the first attached image.

index.php/fa/12819/0/

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

index.php/fa/12820/0/

i'll show you the dataset query, is in a stored procedure because Birt couldn't use any commands like "DATE_FORMAT", "PERIOD_DIFF"....

Quote:

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;




this sql query shows this,(i attached it in the topic)


Thanks for your help!!
if you need anything else just ask for it
  • Attachment: sql_datos.rar
    (Size: 0.46KB, Downloaded 46 times)
  • Attachment: imagen1.jpg
    (Size: 71.36KB, Downloaded 180 times)
  • Attachment: image2.jpg
    (Size: 59.15KB, Downloaded 183 times)
Re: Needing some help to show data [message #994905 is a reply to message #994407] Fri, 28 December 2012 10:59 Go to previous messageGo to next message
Michael Williams is currently offline Michael Williams
Messages: 1424
Registered: July 2009
Senior Member

I understand what you're trying to do with the grouped table and the detail values underneath, but what are you wanting to do with all the NULL values? I'm not sure I'm understanding that part. Also, what is your BIRT version?

Michael
Actuate – The BIRT Company™

BIRT Developer Center
Check out the new, free BIRT Viewer Toolkit
Re: Needing some help to show data [message #996486 is a reply to message #994905] Wed, 02 January 2013 02:36 Go to previous messageGo to next message
Luis Daniel Santigosa Rodriguez is currently offline Luis Daniel Santigosa Rodriguez
Messages: 2
Registered: December 2012
Junior Member
Thanks for your response Michael.

What i try to show is information about projects by month, whether there's data for them or not. For null data i should display that month with empty rows, or whatever... The important thing is just displaying them.

The version of Birt is 4.2.1

Regards

[Updated on: Wed, 02 January 2013 03:38]

Report message to a moderator

Re: Needing some help to show data [message #997118 is a reply to message #996486] Thu, 03 January 2013 14:12 Go to previous message
Michael Williams is currently offline Michael Williams
Messages: 1424
Registered: July 2009
Senior Member

Ok. You'll just need to create a dataSet that has values for all of the months and then join that with your data to create the rows that don't exist. Let me know if this explanation is good enough to get you started or if you need a sample that shows forcing each group to have all months, empty cells or not.

Michael
Actuate – The BIRT Company™

BIRT Developer Center
Check out the new, free BIRT Viewer Toolkit
Previous Topic:Displaying arrays in BIRT
Next Topic:Setting Grid's margin in javascript
Goto Forum:
  


Current Time: Fri Apr 18 06:41:32 EDT 2014

Powered by FUDForum. Page generated in 0.04060 seconds