cancel
Showing results for 
Search instead for 
Did you mean: 

Tips on a simple ERQL query

New Contributor

Tips on a simple ERQL query

Hi all Smiley Happy

 

Probably a basic question, but:

 

I need a report (export to XL) which can show me all org-units in a specific library and all Business and Organisational processes they are used in.

 

I would be very happy on some quidance on how to create such a query in ERQL.

 

Best regars

Trond Erik

11 Replies
Regular Contributor

Re: Tips on a simple ERQL query

If you are working in MEGA 2009 this is not possible with the query tool. You need to write a script that can do this for you or use the excel export feature which is available in 2009 R7 if I'm not mistaken.

 

regards

 

stijn

Frequent Contributor

Re: Tips on a simple ERQL query

Hi,

 

For this kind of a task I am creating a .rtf descriptor (that is enabling a tree of queries) which is generating a "csv" like string.

Then the text generated opened in MS Excel is making directly a well-formated document ...  

 

Regards,

 

 

Otherwise, yes, you can develop a complete VBscript command or macro including directly MS Excel APIs.

 

 

 

Lionel Mazurié
Regular Contributor

Re: Tips on a simple ERQL query

Rtf descriptor, never tried that one. I'll have to give it a go on my next request

 

I usually write a script for these things that output the csv string in the script execution window. I copy and paste it in a textfile I save as csv and done.

MEGA

Re: Tips on a simple ERQL query

Hello Erik

 

An ERQL query build a set of object for a given MetaClass

It is not a report as Lionel and Stijn told you

It does not enable to compute a set with various MetaClass (ex: business process, organizational process...)

 

To query the business process objects directly owned by a given library, you can use the following query

Select [Business Process] Where [Owner Library]= &L

The variable %L will be solved at runtime

 

To create a report, you need to choose a technology and to code the report

RTF descriptor

HTML descriptor

Report template

API Code

See MEGA documentation to go further on report

 

 

 

 

Jerome
New Contributor

Re: Tips on a simple ERQL query

Hi all!

 

And thanks for your replies Smiley Happy

 

I have concluded on my own that a rtf-descriptor probably is the best sollution, but nice to get comfirmation.

 

A csv-file with these coloums is what I am looking for:

 

Org-Unit-Business Process lvl 1-Business Process lvl 2-Organizational Process-Operation-SAP Transaction Code(SolMan)

 

in a flat structure.

 

I have tried following the Publisher Manual from page 46 and have the following question:

 

To accomplish the above csv, do I need to insert the attributes as Groups or can I use the Object neighborhood folder in any way? When I build a descriptor with Org-Unit as the "Described Object" and try to use fields from the Object neighborhood folder nothing appeares on the output. Is there a setting or something important I am missing?

 

Can you please give me some advice that will take me forward?

 

Thanks in advance

 

Best Regards
Trond Erik

Regular Contributor

Re: Tips on a simple ERQL query

After you put something from the object neighbourhood folder you need to go and look into the tree structure of the descriptor, he will have added an extra text part. You need to go into that text part (double click) and then add the necessary attribute(probably short name) that you like to display.

 

Regards

 

stijn

New Contributor

Re: Tips on a simple ERQL query

Hi Stijn Smiley Happy

 

That did the trick! Thanks!!!

 

One final question:

 

This simple code (An operation descriptor):

 

<Short Name/>-<Org-Unit><Short Name/></Org-Unit>

 Gives me an output that looks like this:

 

Identifiser type endring-KontraktsforvalterSalgskontraktforvalter
Informer kunde-KontraktsforvalterKontraktsgodkjennerSalgskontraktforvalter
Utfør interne tiltak-ØkonomisjefRegnskapssjef
Forespør om midler-ØkonomisjefRegnskapssjef
Motta midler-ØkonomisjefRegnskapssjef

(First Short name=Operation and then the Org-Units "back-to-Back)

 

Is it possible to make a resultfile that looks like this:

 

Identifiser type endring-Kontraktsforvalter
Identifiser type endring-Salgskontraktforvalter
Informer kunde-Kontraktsforvalter
Informer kunde-Kontraktsgodkjenner
Informer kunde-Salgskontraktforvalter
Utfør interne tiltak-Økonomisjef
Utfør interne tiltak-Regnskapssjef
Forespør om midler-Økonomisjef
Forespør om midler-Regnskapssjef
Motta midler-Økonomisjef
Motta midler-Regnskapssjef

 With one line pr Operation with the corresponding Org-Unit?

 

Best regards

Trond E

 

Tags (1)
Regular Contributor

Re: Tips on a simple ERQL query

certainly possible. First of all, what you could already do is when you right click on a text part in the descriptor you can set the format to be same line, new line, paragraph. That will already make it more readable if you do that for the org-unit

 

 

Regular Contributor

Re: Tips on a simple ERQL query

Sorry forgot you are looking for a csv file format. No need to make it look nice like I mentioned in my previous mail.