Developing report off ORACLE designer repository table

system · October 9, 2003, 5:55pm

Any one familiar with Oracle designer repository tables?

I have created a universe using views of the oracle designer repository tables. Our univese has 4 tables and 2 aliases. One table is to get
the application name. It’s detail table is to get the table names and it’s detail table is to get the column names.

The aliases are used to get the description from one alias table(cdi_description) and notes from the other alias table(cdi_notes). Both of
these aliases are details of the column table.

The way designer stores the description could be multiple rows for each column ie:

column descrip. notes

case [list] 1 [/list] [list][list] 1
2
3
4
[/list][/list]
[list] 2 [/list] [list][list] 1
2
3
4
[/list][/list]

bachman (BOB member since 2002-09-24)

system · October 9, 2003, 6:27pm

What version of Oracle designer are you using?

Karen Burton (BOB member since 2002-08-22)

system · October 9, 2003, 8:21pm

Version 6.5.52.1.0

bachman (BOB member since 2002-09-24)

MarkP · October 10, 2003, 8:18am

If I recall, Oracle Designer has a good set of inbuilt reports (unused entities, etc.) I’m not sure what extra you would want away from these (unless it has changed a lot in the last 2 years) Have you had a look through the reports available in there? Unfortunately I don’t have access to it anymore, so I can’t provide more detailed assistance.

Mark

system · October 10, 2003, 8:32am

I don’t understand what you are trying to tell me. We are building a universe so that other developers can get the information out of designer that they need. We are trying to do this via BO we already have the report developed in Oracle reports. We wanted to create the report in BO so that the users would have for flexibility in what they wanted to see.

Any ideas on how to get rid of the repeating values?

Thanks
Lori

bachman (BOB member since 2002-09-24)

MarkP · October 10, 2003, 11:53am

Apologies for any confusion Lori. I was talking about the list of reports within Designer itself and wondering how suitable those were.

In terms of your problem here, have you considered using a CASE or DECODE statement to concatenate the lines? Without have access to a Des Repository database these days to have a look myself I’m afraid that’s as far as I can help I think.

Have you had a look at http://download-east.oracle.com/otn_hosted_doc/designer/misc/24870_272269.pdf ?
It won’t help your problem, but was the last source data diagram that I used.

Regards,
Mark

system · October 10, 2003, 12:27pm

Lori,
I don’t think I understand your question. Do want to concatenate the notes together into one string? If so, why not write a PLSQL function to do this?
I saw a presentation at the Mid-Atlantic User’s group meeting on a universe with Oracle Designer tables. This universe was on 6.0 tables. Since the tables have changed between versions, the information I have won’t do you much good.
Karen

Karen Burton (BOB member since 2002-08-22)

system · October 10, 2003, 2:27pm

No, I don’t want to concatate the fields together. The fields are repeating and I don’t want them to repeat for the continuation of the second note. The notes are descriptions are unrelated. I want to print the full note and the full description without the description repeating for continuation of the second note.

Thanks
Lori

bachman (BOB member since 2002-09-24)

MarkP · October 10, 2003, 2:37pm

So, at the moment Lori, does it look like:


Description   Note
Desc 1        Notes 1 part 1
Desc 1        Notes 1 part 2
Desc 1        Notes 1 part 3
Desc 1        Notes 1 part 4
Desc 2        Notes 1 part 1
Desc 2        Notes 1 part 2
Desc 2        Notes 1 part 3
Desc 2        Notes 1 part 4

If so, would it be feasible to insert a break on Description?

system · October 10, 2003, 2:54pm

Close

[/code]Description Note
Desc 1 notes part 1
Desc 1 notes part 2
Desc 1 notes part 3
Desc 1 notes part 4
Desc 2 notes part 1
Desc 2 notes part 2
Desc 2 notes part 3
Desc 2 notes part 4
[/code]

I don’t want the notes to repeat the second time for desc 2, it just needs to print one time.

bachman (BOB member since 2002-09-24)

MarkP · October 11, 2003, 11:00am

Lori,

Instead of


Description Note 
Desc 1 notes part 1 
Desc 1 notes part 2 
Desc 1 notes part 3 
Desc 1 notes part 4 
Desc 2 notes part 1 
Desc 2 notes part 2 
Desc 2 notes part 3 
Desc 2 notes part 4

What would you want to see?

Please note - it’s Saturday lunchtime. I’m off out shortly to find a life.

system · October 11, 2003, 12:52pm

This is what I want to see

Description Note 
Desc 1 notes part 1 
Desc 1 notes part 2 
Desc 1 notes part 3 
Desc 1 notes part 4 
Desc 2 
Desc 2 
Desc 2  
Desc 2

Thanks
Lori

bachman (BOB member since 2002-09-24)

MarkP · October 13, 2003, 8:08am

Hi Lori, trust you had a good weekend.

Looking at what you want, is it simply a case of breaking on the Notes field or am I missing something?
Either that or use a column to determine the first occurence of the Notes field and then an alerter to hide the subsequent ones?

If you want to send me the report to have a look at, please feel free.

Regards,
Mark

system · October 13, 2003, 4:27pm

Lori,

Can you use the RANK function and then set the Descriptions to NULL if the RANK > 1? That should (in essence) hide all but the first descriptions.

Hmmm, Of course… You can probably do this by setting flags to determine if you are using the first Description or not (depending on your dataset).

-RM

digpen (BOB member since 2002-08-15)

system · October 15, 2003, 2:56pm

Can you explain this to me a little more? I still am not understanding how to get it to work. Just try something simple…tell me how to get the notes not to repeat the second time.

Thanks
Lori

bachman (BOB member since 2002-09-24)