Tips on a simple ERQL query

[tebones]

Hi all 🙂

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

[SVanSchoonlandt]

As mentioned, I never tried to construct a csv file like this. It takes me 5 minutes to come up with this script, dirty but fine for the job at hand. Lionel will be sure to be able to help you out with some best practices to use the descriptors for it

oRoot = megaDb()

set colOperation = oRoot.GetCollection("Operation")
For each oOperation in colOperation

set colOrgUnit = oOperation.GetCollection("Org-Unit")

if colOrgUnit.Count > 0 then
for each oOrgUnit in colOrgUnit
print oOperation.GetProp("Short Name") & ";" & oOrgUnit.GetProp("Short Name") & ";"
next
else
print oOperation.GetProp("Short Name") & ";;"
end if
next

[tebones]

Yes, I need a compleately flat csv-file:

Org-Unit                      Operation

Org-unit 1                    Operation 1

Org-Unit 1                   Operation 2

Org-Unit 2                   Opertaion 1

And so forth. See the attaced file, thats my goal. I have tried filddeling with the options you are mentioning (New line, same line and new paragraph) but can't get it right. I always have get one line pr org-unit and all operations on the same line.

Best regards

Trond E

[SVanSchoonlandt]

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

[SVanSchoonlandt]

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

[tebones]

Hi Stijn 🙂

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

[SVanSchoonlandt]

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

[tebones]

Hi all!

And thanks for your replies

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

[jhorber]

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

[SVanSchoonlandt]

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.

[lmazurie]

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.