[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

MonthWise Summary in SummaryBand.

I have a report datawindow [rdw_contract_balance].

For a certain criteria It shows

delivery_date      qty
dd/mm/yyyy              
---------------    ------------
01/04/2005          5
07/04/2005          4
02/05/2005          6
06/05/2005          3
07/05/2005          1
04/06/2005          7
05/06/2005          5


In summary band I want
MonthName   QtyOnMonths
April                    9
May                    10
June                   11

The problem is months are not fixed.

Is it possible to control this in datawindow or I will have to do something in sql.


0
Mateen
Asked:
Mateen
  • 6
  • 5
  • 5
2 Solutions
 
gafoor78Commented:
Hi,

i don't think that u can achieve this in summary band...

but i think you can achieve this by a composite datawindow.

create a datawindow for the first report
that is for,

delivery_date      qty
dd/mm/yyyy              
---------------    ------------
01/04/2005          5
07/04/2005          4
02/05/2005          6
06/05/2005          3
07/05/2005          1
04/06/2005          7
05/06/2005          5

then create another datawindow for the summary report,
that is for,

MonthName   QtyOnMonths
April                    9
May                    10
June                   11

then create a composite datawindow and put the first datawindow in the top and the second datawindow just below that and use this composite datawindow to show the report.

let me know if you need more assistant

regards
Gafoor
0
 
MateenAuthor Commented:
Then I would like to do it in Sql.

Thanks 4 your comment.
0
 
MateenAuthor Commented:
Hi Gafoor:

Now I realise, Even If I adjust my sql to show summary data, then It will show only one month in summary band.

Is it possible to do in single dw (However tough and complex).

I tend to keep single dw as out some utility does not perform on multiple dws.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gafoor78Commented:
i am trying out for any other possible way

You need report exatly in the same format?

that is first detailed and then below only summary?


delivery_date      qty
dd/mm/yyyy              
---------------    ------------
01/04/2005          5
07/04/2005          4
02/05/2005          6
06/05/2005          3
07/05/2005          1
04/06/2005          7
05/06/2005          5

MonthName   QtyOnMonths
April                    9
May                    10
June                   11

let me know if you need exactly like this or you can create a group in DW with the month name and can put the month name and sum in the trailer for that group.

regards
gafoor

0
 
gajender_99Commented:
hi mateen which database are you using
0
 
gajender_99Commented:

his is for oracle

select
to_char(delivery_date,'MONTH')  as delivery_date
sum(qty) as qty
from your_table
group by
to_char(delivery_date,'MONTH')

gajender
0
 
gajender_99Commented:
this is for microsoft sql server

select  DATENAME([MONTH],delivery_date )
sum(qty)
 from yourtable
group by DATENAME([MONTH],delivery_date)



gajender
0
 
MateenAuthor Commented:
Hi Gafoor

<<You need report exatly in the same format?
that is first detailed and then below only summary?>>

Yes, exactly like this.

<<hi mateen which database are you using>>

Sql Server 2000



0
 
gajender_99Commented:
hi mateen
if you want the result from one sql then you won't have update facility on the data window.
 but the result would be like this
delivery_date      qty
dd/mm/yyyy              
---------------    ------------
01/04/2005          5
07/04/2005          4
02/05/2005          6
06/05/2005          3
07/05/2005          1
04/06/2005          7
05/06/2005          5
April                    9
May                    10
June                   11

if you want in this way let me know
thanks

0
 
gajender_99Commented:
hi mateen

then try this

select  delivery_date
,qty
 from yourtable
union
select  DATENAME([MONTH],delivery_date )  as delivery_date
sum(qty) as qty
 from yourtable
group by DATENAME([MONTH],delivery_date)

Gajender
0
 
MateenAuthor Commented:
Hi Gajendar

I want summary in summary band.

I can write sql which will give

delivery_date      qty          MONTH_NAME MONTH_QTY
dd/mm/yyyy                                                              
---------------    ------------  ------------------ ------------------
01/04/2005          5             APRIL                9
07/04/2005          4             APRIL                9
02/05/2005          6             MAY                 10
06/05/2005          3             MAY                 10
07/05/2005          1             MAY                 10
04/06/2005          7             JUNE                10
05/06/2005          5             JUNE                10

If I put month_name,month_qty in summary band then
it will show one row.




0
 
gafoor78Commented:
Hi,

yes, you can try the way Gajender suggested... but you won't get a formated one out of that.
if you want a formated report with heading and seperated by blan rows, then the best way is to create a composite DW.
i don't find any other way

regards
gafoor

0
 
gafoor78Commented:
hi,

could you please explain your comment, that blocking you the usage of composite dw,

<< I tend to keep single dw as out some utility does not perform on multiple dws. >>

some times i can give some light on that.

regards
gafoor

0
 
MateenAuthor Commented:
Hi Gafoor
Do u remember, u provided me a smart search in every column of datatawindow?


I call every report from a form called w_resp_criteria_report and the from knows
the report name as a variable.

In the open event I have written

for ijk = 1 to Long(dw_1.Describe('datawindow.column.count'))
       ls_colname = dw_1.Describe('#'+string(ijk)+'.Name')
    ls_coltype = Lower(Left(dw_1.Describe(ls_colname + ".ColType"),4))
    if pos('chardatenumblongintdecireal',ls_coltype) > 0 then
             sle_1.of_RegisterCol(ls_colname,'ANY')  
    end if
next

For composite report, the name of the report for my form will be, compiste dw, the find will not work. Find will also work misleadingly if we hide objects.

I think u have got what I mean.

BTW, your search object is really very smart and have been much appreciated by my boss.
Thanks

0
 
gafoor78Commented:
Hi,

ya, i understood..

but now i am not getting any other workaround for you.........
so wait 4 other postings.....

all the best

regards
gafoor
0
 
gafoor78Commented:
Thanks Mateen :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now