Help needed with the matrix report.

[garry1]

Hi,

I am trying to create a matrix report and I want to export that to excel. But, I am running into some problem with populating the columns. There are two columns in the database table and one tracks number of years and the other tracks the fiscal year. Based on number of years I need to create the columns dynamically, if I have 4 years and the fiscal year is 2005 my report should look like
2005 2006 2007 2008
test1
test2
test3

Any help on this is greatly appreciated.

Thanks.

 

[nagornyi]

You may use subquery in your SQL that would generate the necessary years grid based on fiscal year and number of years. Something like this

select  :fiscal_year + rownum - 1 column_year
from all_tables
where rownum <= :number_of_years

 

[garry1]

Hi Nagornyi,

Thanks for your help regarding populating the years in the matrix report. I was able to get the years as columns and the remaining feild as row value. Is there any way in reports that allows me to populate the values for the year columns dynamically. I want the cost values in this feild populated at run time

example: I have something like this
2004 2005 2006 2007

MtCost
MInstallCost
SerCost
RegCost

I am looking for something like this

2004 2005 2006 2007

MtCost $0.10 $11 $12 $14
MInstallCost $0.12 $11 $12 $14
SerCost $0.10 $11 $12 $14
RegCost $0.10 $11 $12 $14

Below is the query I am using:

select distinct case when a.ACCT_NO_4 in ('06','16') then 'MtCost'
when a.ACCT_NO_4 in ('04','17') then 'MInstallCost'
when a.ACCT_NO_4 in ('02','11') then 'SerCost'
when a.ACCT_NO_4 in ('07','12') then 'RegCost' else '' end as ct_type,
b.ID_COST_TYPE_CODE,d.start_year
from wac a, wce b, wr c,bcaa d, bwr e
where a.W_COST_ID = b.W_COST_ID and
d.id = e.bcaa_id and
a.wr_no = b.wr_no and
a.wr_no = e.wr_no and
a.wr_no = c.wr_no and
d.start_year in (select start_year + rownum - 1 column_year from all_tables where rownum <= NUMBER_OF_YEARS)

Any help on this is very much appreciated.

Thanks a lot.

 

[nagornyi]

That indeed looks like matrix report, with rows being cd_type, columns being start_year and on ther intersectin there should be...sum of..what? or max of ... what? It is not clear from either your question or query what you need in the matrix cells. Also it is not clear what you mean by "dynamic", i.e. what is not dynamic right now?

 

[garry1]

Hi Nagornyi,

I was asking is it possible for me to write the formula columns to populate the data in the cells under the start_year columns. Because this data comes from the other tables. Any help is appreciated.

Thanks.

 

[nagornyi]

You can write stored function and use it in the query. When building your matrix, you will have to specify some summary operation on that field broght by the foromula. Just select "maximum", because the formula will bring same results for the same values of (start_year, cd_type are) pairs.

 

[garry1]

Hi Nagornyi,

Thanks a lot for all your input so far. When I was running the report I found a strange problem with my data.I am trying to dispaly start_year in the report even though there is no data. But it doesn't work and it is displaying year only when the data for the matching reords is found. I have to create the years based on the number_of_years in the feild in a table. if the number of years is 3 I should show on the report as 2004, 2005, 2006, and there can be no data for any of the years but the user wants me to display the data. I tried doing union and it is doing the same thing

For example I am looking for some thing like this
2004 2005 2006
A
B
C

And I report is showing like this
2004 2005
A
B
C

Below is the query I am using

select distinct case when a.ACCT_NO_4 in ('06','16') then 'MtCost'
when a.ACCT_NO_4 in ('04','17') then 'MInstallCost'
when a.ACCT_NO_4 in ('02','11') then 'SerCost'
when a.ACCT_NO_4 in ('07','12') then 'RegCost' else '' end as ct_type,
b.ID_COST_TYPE_CODE,d.start_year
from wac a, wce b, wr c,bcaa d, bwr e
where a.W_COST_ID = b.W_COST_ID and
d.id = e.bcaa_id and
a.wr_no = b.wr_no and
a.wr_no = e.wr_no and
a.wr_no = c.wr_no and
d.start_year in (select start_year + rownum - 1 column_year from all_tables where rownum <= NUMBER_OF_YEARS)

Any help is very much appreciated on this.

 

[nagornyi]

No wonder. What is not selected can not be displayed. Try modifying your SQL like this:

select  distinct case when a.ACCT_NO_4 in ('06','16') then 'MtCost'
      when a.ACCT_NO_4 in ('04','17') then 'MInstallCost'
      when a.ACCT_NO_4 in ('02','11') then 'SerCost'
      when a.ACCT_NO_4 in ('07','12') then 'RegCost'  else '' end as ct_type,
b.ID_COST_TYPE_CODE, [b]F.start_year[/b]
from 
wac a, 
wce b, 
wr c,
bcaa d, 
bwr e,
[b](select  start_year + rownum - 1 column_year from all_tables where rownum <= NUMBER_OF_YEARS) F[/b]
where a.W_COST_ID = b.W_COST_ID and
d.id = e.bcaa_id and
a.wr_no = b.wr_no and
 a.wr_no = e.wr_no and
a.wr_no = c.wr_no and
[b]d.start_year(+) = F.start_year[/b]

 

[garry1]

Hi Nagornyi,

Thanks for your kind reply.I modified my script as per your suggestions and executed it but I am getting ora-00904 number_of_years "invalid Identifier " , ora-00904 start_year "invalid Identifier ". I am sending the script which I am running by including your suggestions.

select distinct case when a.ACCT_NO_4 in ('06','16') then 'Meter Cost'
when a.ACCT_NO_4 in ('04','17') then 'Meter Install Cost'
when a.ACCT_NO_4 in ('02','11') then 'Service Cost'
when a.ACCT_NO_4 in ('07','12') then 'Regulator Cost' else '' end as ct_type,
b.ID_COST_TYPE_CODE,
f.start_year,
sum(AFTER_COND_FCT_AMT),
a.ACCT_NO_4
from wac a,
wce b,
wr c,
bcaa d,
bwr e,
wdr g
(select start_year + rownum - 1 column_year from all_tables where rownum <= NUMBER_OF_YEARS)F
where a.W_COST_ID = b.W_COST_ID and
d.id = e.bcaa_id and
a.wr_no = b.wr_no and
a.wr_no = e.wr_no and
a.wr_no = c.wr_no and
a.wr_no = g.wr_no and
g.status_code = 'O' and
a.D_AB_DA = b.D_AB_DA and
b.D_AB_DA = 'D' and
e.bcaa_id = 68 and
a.ACCT_NO_4 in ('06','16','04','17','02','11','07','12') and
b.ID_COST_TYPE_CODE is null and
d.start_year(+) = F.start_year
group by b.ID_COST_TYPE_CODE,f.start_year,a.ACCT_NO_4

Any help is very much appreciated on this.

Thanks.

 

[nagornyi]

if NUMBER_OF_YEARS is parameter, it should be preceded by a colons. Now sure how the SQL ran without it before.

where rownum <= [b]:[/b]NUMBER_OF_YEARS

 

[garry1]

Hi Nagornyi,

I am sorry to trouble you, but I am still having the problem with query. When I hardcoded the number of years, it is complaining about the start_year in this part of the query

(select start_year + rownum - 1 column_year from all_tables where rownum <= NUMBER_OF_YEARS)F in this line and

d.start_year(+) = F.start_year (this line of my sql query). Any help is really appreciated in this and once I am thankful to you for responding to my questions.

Thanks.

 

[nagornyi]

Do not be sorry to trouble. That's what I come here for and read the questions -- to be traoubled
Looks like the subquery doesn't know where to take the initial_year to build the grid from... In my original suggestion it was a parameter, but if you need it dynamic, it should be made to be one of the fields, not shure which one...
Try to make it a parameter first, let's see what happens..

 

[garry1]

Hi Nagornyi,

I have tried to get the thing working based on your old suggestion, but I am running into some snags with the data I am populating in the year column. I am trying to get the sum of the amount in the year column and I have noticed that the amounts are not changing and they are coming the same for all the years. below is the sql query I am using.

select distinct BC_NAME, BC_TYPE,
case when a.ACCT_NO_4 in ('06','16') then 'Meter Cost'
when a.ACCT_NO_4 in ('04','17') then 'Meter Install Cost'
when a.ACCT_NO_4 in ('02','11') then 'Service Cost'
when a.ACCT_NO_4 in ('07','12') then 'Regulator Cost' else '' end as ct_type,
b.ID_COST_TYPE_CODE,
f.start_year,
sum(AFTER_COND_FCT_AMT),
a.ACCT_NO_4
from wac a,
wce b,
wr c,
bcaa d,
bwr e,
wdr g,
bbc h,
bbcl i

where h.bciid = i.bcid and
d.id = e.bcaa_id and
a.wr_no = b.wr_no and
a.wr_no = e.wr_no and
a.wr_no = c.wr_no and
a.wr_no = g.wr_no and
g.status_code = 'O' and
a.D_AB_DA = b.D_AB_DA and
b.D_AB_DA = 'D' and
e.bcaa_id = 68 and
a.ACCT_NO_4 in ('06','16','04','17','02','11','07','12') and
b.ID_COST_TYPE_CODE is null and
and c.fiscal_year in (select fiscal_year + rownum - 1 column_year from all_tables where rownum <= d.NUMBER_OF_YEARS)
group by c.fiscal_year, b.ID_COST_TYPE_CODE,a.ACCT_NO_4,BC_NAME, BC_TYPE

Any help is appreciated.

 

[nagornyi]

Why do you think that data should be different for each year? Try running query to bring just AFTER_COND_FCT_AMT, not sum of it, and see if the values would change from year to year.

 

[garry1]

Hi Nagornyi,

Thanks a lot for all your help, I am still having the problem and I found that part of the problem is with the data also. But now I have a clean set of data which I am running only for a certain year range and it getting me three distinct different values and I want them to sum into one and display.

I am also sending you my sql

select distinct BC_NAME, BC_TYPE,
case when a.ACCT_NO_4 in ('06','16') then 'Meter Cost'
when a.ACCT_NO_4 in ('04','17') then 'Meter Install Cost'
when a.ACCT_NO_4 in ('02','11') then 'Service Cost'
when a.ACCT_NO_4 in ('07','12') then 'Regulator Cost' else '' end as ct_type,
f.start_year,
case when a.ACCT_NO_4 in ('06','16') then sum(AFTER_COND_FCT_AMT) OVER (PARTITION BY a.wr_no,a.ACCT_NO_4)
when a.ACCT_NO_4 in ('04','17') then sum(AFTER_COND_FCT_AMT) OVER (PARTITION BY a.wr_no,a.ACCT_NO_4)
when a.ACCT_NO_4 in ('02','11') then sum(AFTER_COND_FCT_AMT) OVER (PARTITION BY a.wr_no,a.ACCT_NO_4)
when a.ACCT_NO_4 in ('07','12') then sum(AFTER_COND_FCT_AMT) OVER (PARTITION BY a.wr_no,a.ACCT_NO_4) end sum_amt,
from wac a,
wce b,
wr c,
bcaa d,
bwr e,
wdr g,
bbc h,
bbcl i
where h.bciid = i.bcid and
d.id = e.bcaa_id and
a.wr_no = b.wr_no and
a.wr_no = e.wr_no and
a.wr_no = c.wr_no and
a.wr_no = g.wr_no and
g.status_code = 'O' and
a.D_AB_DA = b.D_AB_DA and
b.D_AB_DA = 'D' and
e.bcaa_id = 323 and
a.ACCT_NO_4 in ('06','16','04','17','02','11','07','12') and
b.cost_type_code in ('MAT','LAB') and
c.fiscal_year = '2005' and
c.fiscal_year in (select fiscal_year + rownum - 1 column_year from all_tables where rownum <= d.NUMBER_OF_YEARS)
group by c.fiscal_year, b.ID_COST_TYPE_CODE,a.ACCT_NO_4,BC_NAME, BC_TYPE

This is result I am getting.
BC_NAME,BC_TYPE,COST_TYPE,FISCAL_YEAR,SUM_AMT
NV HOMES,LSF,Meter Cost,2005,144
NV HOMES,LSF,Meter Install Cost,2005,98.049
NV HOMES,LSF,Service Cost,2005,1245.441
NV HOMES,LSF,Service Cost,2005,1987.761
NV HOMES,LSF,Service Cost,2005,3286.821
PULTE,SF,Meter Cost,2005,144
PULTE,SF,Meter Install Cost,2005,98.049
PULTE,SF,Service Cost,2005,1245.441
PULTE,SF,Service Cost,2005,1987.761
PULTE,SF,Service Cost,2005,3286.821
RYAN,TH,Meter Cost,2005,144
RYAN,TH,Meter Install Cost,2005,98.049
RYAN,TH,Service Cost,2005,1245.441
RYAN,TH,Service Cost,2005,1987.761
RYAN,TH,Service Cost,2005,3286.821

For service cost I am getting three records, is there any way in the sql I can combine this three into one record and show. Any help will be very mcuh appreciated on this.

Thanks a lot.

 

[nagornyi]

select
  BC_NAME
, BC_TYPE
, ct_type
, SUM(sum_amt)
from
(
your sql here
)
group by 
  BC_NAME
, BC_TYPE
, ct_type