Complex report

I presume the 2 tables are joined by IDCOMPC.  Can you explain the tables a bit ? In your desired layout, you have given dd to be printed against FENVIO and FRECEP ? What is this dd ? Is it the dd component of the date ?

Please explain the above.  Dont worry , the report can be done quite easily.  Just give the above information in as much detail you can, and you will surely get the proper help

Hi,

What I have understood from the question is you want Matrix reports. It is little bit tricky but simple.

In report builder you will have to go to Help - Index -> type "matrix group, creating" will show you simple demo to create matrix for EMP and DEPT table.

Still if this is not clear then can give the details and will be my pleasure to help you.

Cheers !!

JB

Well, i'll explain it better. Yes, the 2 tables are joined by idcompc. I have that join in my query so i can display without problems the fields IDCOMPC, MODELO, NUMSER and OBS. My problem is with FENVIO and FRECEP, but not about obtaining the values from the database, because as you can see, they are in the COMP table. But the problem is that i have to show them in two different rows and in the corresponding column of the month. Yes, the dd is the day of the FENVIO and FRECEP dates. So, if you have a row with these values:

IDCOMPC   CRXY
FENVIO      01/20/2000
FRECEP      02/25/2000

the report should display the following
                                                                                     2005

IDCOMPC    MODELO   NUMSER                  Jan    Feb   Mar  Apr   May  .....   Dec             OBS
  CRXY           ...            ...       FENVIO       20      
                                              FRECEP                25              

I have already used the report wizard to create a matrix report, but the tests that i have done show different results, i don't have any reference manual and the time i have to finish this is quite short, that's the reason for asking help so desperately...

Thanks for your help...

kmocampo,
    You can do it in one query.

select FICH.idcompc,modelo,numser,
        decode(to_char(fenvio,'MM'),'01',to_char(fenvio,'DD'),null) Jan,
        decode(to_char(fenvio,'MM'),'02',to_char(fenvio,'DD'),null) Feb,
        decode(to_char(fenvio,'MM'),'03',to_char(fenvio,'DD'),null) Mar,
        decode(to_char(fenvio,'MM'),'04',to_char(fenvio,'DD'),null) Apr,
        decode(to_char(fenvio,'MM'),'05',to_char(fenvio,'DD'),null) May,
        decode(to_char(fenvio,'MM'),'06',to_char(fenvio,'DD'),null) Jun,
        decode(to_char(fenvio,'MM'),'07',to_char(fenvio,'DD'),null) Jul,
        decode(to_char(fenvio,'MM'),'08',to_char(fenvio,'DD'),null) Aug,
        decode(to_char(fenvio,'MM'),'09',to_char(fenvio,'DD'),null) Sep,
        decode(to_char(fenvio,'MM'),'10',to_char(fenvio,'DD'),null) Oct,
        decode(to_char(fenvio,'MM'),'11',to_char(fenvio,'DD'),null) Nov,
        decode(to_char(fenvio,'MM'),'12',to_char(fenvio,'DD'),null) Dec,
        obs  
from FICH, COMP
where fich.idcompc = comp.idcompc
union all
select FICH.idcompc,modelo,numser,
        decode(to_char(frecep,'MM'),'01',to_char(frecep,'DD'),null) Jan,
        decode(to_char(frecep,'MM'),'02',to_char(frecep,'DD'),null) Feb,
        decode(to_char(frecep,'MM'),'03',to_char(frecep,'DD'),null) Mar,
        decode(to_char(frecep,'MM'),'04',to_char(frecep,'DD'),null) Apr,
        decode(to_char(frecep,'MM'),'05',to_char(frecep,'DD'),null) May,
        decode(to_char(frecep,'MM'),'06',to_char(frecep,'DD'),null) Jun,
        decode(to_char(frecep,'MM'),'07',to_char(frecep,'DD'),null) Jul,
        decode(to_char(frecep,'MM'),'08',to_char(frecep,'DD'),null) Aug,
        decode(to_char(frecep,'MM'),'09',to_char(frecep,'DD'),null) Sep,
        decode(to_char(frecep,'MM'),'10',to_char(frecep,'DD'),null) Oct,
        decode(to_char(frecep,'MM'),'11',to_char(frecep,'DD'),null) Nov,
        decode(to_char(frecep,'MM'),'12',to_char(frecep,'DD'),null) Dec,
        obs  
from FICH, COMP
where fich.idcompc = comp.idcompc
order by 1,2,3


You could do them at once, without the union, but this seems clearer.

Good luck!
DrSQL

I suggested the 3 query approach as I feel that will enable the desired layout to be accomplished more easily.

sapnam,
   I'm not sure why - the query I've given makes it a tabular report with a break on IDCOMPC and MODELO.  Seems simpler than three frames.  You could do it in SQL*Plus, it's so basic.

Good luck!
DrSQL

Dr SQL

If you see the layout given by kmocampo

IDCOMPC    MODELO   NUMSER                  Jan    Feb   Mar  Apr   May  .....   Dec             OBS
                                              FENVIO                dd
                                              FRECEP                               dd

he wants the words FENVIO and the dates corresponding to those to come on the line below IDCOMPC and then the words FRECEP and the dates corresponding to that to come on the next line.  SQL Plus might not be able to achieve that layout.

Dr SQL

But, in SQL Plus, will not the FENVIO come on the same line as IDCOMPC and FRECEP come below that. What our friend wants is 3 lines, one with IDCOMC etc. then FENVIO and then with FRECEP

sapnam,
   No, it's two lines:

IDCOMPC    MODELO   NUMSER                  Jan    Feb   Mar  Apr   May  .....   Dec             OBS
  CRXY           ...            ...       FENVIO       20      
                                              FRECEP                25    

As shown on 08/26/2005 03:29AM CDT.  But, I COULD do that, too.

Good luck!
DrSQL

Well, i have another problem. I also have the possibility of not having any date resulting from those two queries and the latter comparison. And if i don't get a value, then i should not display the record.  So i decided to put a filter into the main group (the items group) specifying that if :CF_DATE was null, then return false. And i get a REP 1251 error about circular dependencies in the query. What can i do then to tell Reports that if i don't found a max(date) in those two tables, then i should not take the whole item record?

You can have a format trigger on the repeating frame corresponding to the main group.  There you can code

IF :date is null then
    return(FALSE);
else
   return(TRUE);
end if;

kmocampo,
    My query doesn't have that issue.

Good luck!
DrSQL

Oops, i have made a mistake posting the comment! Thanks DrSQL, but it's not necessary to do that last thing i asked for this problem. sapnam, i was talking about the other question, the one about the values from different tables in one column (that max(date) problem, remember?) Your comment applies to that? If you agree, we can continue adding comments to the other question, ok?

I think you can post the comment on the other question also so that people participating in that question can also know about it. Anyway, is the format trigger working ?