Zero Display ... [message #888974] |
Mon, 18 June 2012 20:59 |
A R Messages: 104 Registered: April 2012 |
Senior Member |
|
|
Hi I am working on report which will display the count for 8 quarters. When I am creating data set I am getting the count for the records that are available. The results are as follows.
QUARTER_START_DATE CNT
------------------ ---
Oct-11 1
Jul-12 34
Oct-12 3
Jul-13 1
I am using the following query:
SELECT
TO_CHAR((TRUNC ("TARGET-DEPLOYMENT-DATE",'Q')+93),'MM-YYYY') AS quarter_start_date ,COUNT (ASSETURN) AS cnt
FROM ERRUSER.RPT_CLASSIFIERS_36
WHERE
"TARGET-DEPLOYMENT-DATE" >= ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9 )
AND "TARGET-DEPLOYMENT-DATE" < ADD_MONTHS ( TRUNC (SYSDATE, 'Q'),15)
GROUP BY (TRUNC ("TARGET-DEPLOYMENT-DATE" ,'Q'))
ORDER BY TRUNC ("TARGET-DEPLOYMENT-DATE" , 'Q')
Can u suggest me how can I achieve the following result:
QUARTER_START_DATE CNT
----------------- ---
Q3 - 2011 0
Q4 - 2011 1
Q1 - 2012 0
Q2 - 2012 34
Q3 - 2012 0
Q4 - 2012 3
Q1 - 2013 0
Q2 - 2013 1
Thanks in advance,
AR
|
|
|
Re: Zero Display ... [message #888978 is a reply to message #888974] |
Mon, 18 June 2012 21:04 |
A R Messages: 104 Registered: April 2012 |
Senior Member |
|
|
Hi I am working on report which will display the count for 8 quarters. When I am creating data set I am getting the count for the records that are available. The results are as follows.
QUARTER_START_DATE CNT
------------------ ---
Oct-11 1
Jul-12 34
Oct-12 3
Jul-13 1
I am using the following query:
SELECT
TO_CHAR((TRUNC ("TARGET-DEPLOYMENT-DATE",'Q')+93),'MM-YYYY') AS quarter_start_date ,COUNT (ASSETURN) AS cnt
FROM ERRUSER.RPT_CLASSIFIERS_36
WHERE
"TARGET-DEPLOYMENT-DATE" >= ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9 )
AND "TARGET-DEPLOYMENT-DATE" < ADD_MONTHS ( TRUNC (SYSDATE, 'Q'),15)
GROUP BY (TRUNC ("TARGET-DEPLOYMENT-DATE" ,'Q'))
ORDER BY TRUNC ("TARGET-DEPLOYMENT-DATE" , 'Q')
Can u suggest me how can I achieve the following result:
QUARTER_START_DATE CNT
----------------- ---
Q3 - 2011 0
Q4 - 2011 1
Q1 - 2012 0
Q2 - 2012 34
Q3 - 2012 0
Q4 - 2012 3
Q1 - 2013 0
Q2 - 2013 1
Please let me know if any information required.
Thanks in advance,
AR
|
|
|
|
Re: Zero Display ... [message #889239 is a reply to message #888979] |
Tue, 19 June 2012 03:54 |
|
Does the BIRT designer see the first column of this:
QUARTER_START_DATE CNT ------------------
---
Oct-11 1
Jul-12 34
Oct-12 3
Jul-13 1
as a date or a string? If it is a date you could use a crosstab setup
to show empty values as described here:
http://www.eclipse.org/birt/phoenix/project/notable2.5.php#jump_11
See attached example.
Jason
On 6/18/2012 5:07 PM, A R wrote:
> Please note that in the above post both QUARTER_START_DATE and CNT are
> two different columns. The values are as follows:
>
> QUARTER_START_DATE
>
> Q3 - 2011
> Q4 - 2011
> Q1 - 2012
> Q2 - 2012
> Q3 - 2012
> Q4 - 2012
> Q1 - 2013
> Q2 - 2013
>
> CNT
>
> 0
> 1
> 0
> 34
> 34
> 3
> 0
> 0
>
> Thanks
> AR
>
|
|
|
|
|
Re: Zero Display ... [message #889848 is a reply to message #889843] |
Tue, 19 June 2012 22:04 |
|
Does this mean you got it to work? BTW you can make empty values
display 0 in your example. There is a row/column setting in the
crosstab for that. You can also create a chart view from a the crosstab
that should display the chart.
Jason
On 6/19/2012 5:40 PM, A R wrote:
> Hi Jason - I am able to create a dataset which is giving the following
> results :
>
> JUL-2011 0
> OCT-2011 1
> JAN-2012 0
> APR-2012 0
> JUL-2012 35
> OCT-2012 3
> JAN-2013 0
> APR-2013 0
> JUL-2013 1
> OCT-2013 0
>
> Thanks,
> AR
|
|
|
|
|
Re: Zero Display ... [message #889863 is a reply to message #889854] |
Wed, 20 June 2012 03:17 |
|
What data type is quarter in your data table?
Jason
On 6/19/2012 6:56 PM, A R wrote:
> Jason can you tell me how we can achieve the following : This is the
> input to x-axis in chart.
>
> Data as is :
>
> QUARTER
> Jul-11
> Oct-11
> Jan-12
> Apr-12
> Jul-12
> Oct-12
> Jan-13
> Apr-13
>
>
> Expected:
>
> QUARTER
> Q3-2011
> Q4-2011
> Q1-2012
> Q2-2012
> Q3-2012
> Q4-2012
> Q1-2013
> Q2-2013
>
>
> - AR
|
|
|
|
|
Re: Zero Display ... [message #890077 is a reply to message #890064] |
Wed, 20 June 2012 22:35 |
|
I do not have a timestamp example but look at the attached. I return a
date and then use a cube to split it into quarters. I have three
examples in the report. The first is just a stander crosstab. The
second is a crosstab that I have created a chart view from and the third
is a crosstab with a contained chart for the measure.
Jason
On 6/20/2012 5:42 PM, A R wrote:
> Jason please let me know if u want me to provide any other details.
>
> -AR
-
Attachment: quarters.zip
(Size: 6.53KB, Downloaded 192 times)
|
|
|
|
|
Re: Zero Display ... [message #890291 is a reply to message #890285] |
Thu, 21 June 2012 18:55 |
|
Have you tried in 3.7.2? That is version I wrote it in.
On 6/21/2012 2:25 PM, A R wrote:
> Jason - I m not able to execute the report. When ever I am trying to
> execute , the BIRT environment closes automatically. I have downloaded
> BIRT 3.7.2 environment to execute the report. Please suggest.
>
> Thanks
> AR
|
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.04863 seconds