We have around 25 drawings of P&ID. And because of the client, we need to produce very detailed schedule based on P&ID. It is made easier with the Plant 3D Project Reports, of course. But since we have too much items to be managed. And with this particular client, we need to highlight the new and deleted item in every revision Is anyone know how to order the Project Reports? At the moment, it seems to only ordered based on PnPID. We like it to ordered based on drawings number than the valve number, for example. You can't really do this on the direct output of excel file. A lot of function of excel is disable with the direct output. We have quite a lot of schdules and items to deal with. If I can order the direct output, that can save a lot of time.
Solved! Go to Solution.
Solved by AndyDavisCadline. Go to Solution.
A couple of ideas - You can sort the report by any field by clicking on the header.
Or, if you could use the external Report Creator program to generate your reports.
Hi,
If you're using Project Reports to generate the output, you can manually edit the .xml report templates and sort by any columns you like.
Here's a snippet from the ValveList report to order the output by Drawing Name and then Valve Tag.
<?xml version="1.0" encoding="utf-8"?>
<PnPQueryDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<OrderBy>
<PnPSortItem>
<SortDirection>Ascending</SortDirection>
<SortColumn>Dwg Name</SortColumn>
</PnPSortItem>
<PnPSortItem>
<SortDirection>Ascending</SortDirection>
<SortColumn>HandValves.Tag</SortColumn>
</PnPSortItem>
</OrderBy>
<GroupBy>
<string>PnPID</string>
</GroupBy>
Hope this helps.
Regards,
Andy
Hi, Andy
Thank you for your GREAT help. That is working. and will save up a lot of time. I didn't realize that report format is also controlled by xml file.
Looks like the report table query is also controlled in xml. Do you know how can we union the class for present here? is this possbile? How is this xml work? Please point out where I can find the information to learn from.
Cheers
Hi, Andy
Can we do one step furthur:
I notice that the number that outputed from data manager in excel is regarded as a TEXT instead of Number, is there anyway that we can make it as number?
This effect the result of sorting:
When it save as text, the sorting is like this, e.g.
2354
245
365
456
..
what I like it to be is:
245
365
456
2354
Please help!
Cheers
Hi,
Yes, you can UNION tables together, please see attached example that reports 4 specific types of valve.
Please be aware that you cannot edit this report through the Project Setup UI, but it will preview.
The datatype in Excel depends upon the original datatype in the P&ID database - the value column in the example is defined as a number and is output as such to Excel.
I don't have any documentation to share with you regarding the xml schema, sorry.
Regards,
Andy
Thanks, Andy
The example you are given can be simplify as:
( "ball valve" Union " Butterfly Valve) Union ( "Gate valve" Union "Inline Valve")
What if I need to union "Control Valve" (Instrument Class) & "Knife Gate Valve" (Hand Valve Class), Can this work? I am thinking, with same property, like valve number, in Control Valve is "Loop Number", but Gate Valve is "number". Does it need to rename one of them to be the same with the other one, If we can do something similar in SQL. do somehting like "Loop Number AS number" . Or this doesn't matter at all? As long as there are listed in the order we needed, it will union it?
With the Report number order, I had a look my setting. The Loop Number is "number" type, but the sorting in the Project Report regarded as text. Maybe there is no way we can do anything about it.
Hi,
If you use the fully defined UNION (as per my example) you can 'mix and match' the fields you extract from each table so long as the fields in the <SelectList> are common to all tables.
I have updated the example to include the ControlValve class extracting the ActuatorType and LoopNumber into the Comment and Value fields in the <SelectList>.
If you have any undefined values in the LoopNumber field (showing as ??) the column will be treated as text.
Regards,
Andy
Hi, Andy
Thanks for you information. I tried the mathod that you showed in your example. I come up with this, but it didn't UNION two table for me. In the data manager, I can only see the information from FlowmeterMass table. What did I do wrong? Please see the attachment.
Cheers,
Hi,
If you could send me a copy of the processpower.dcf file from your project I'll have a look for you. The exact query depends upon the database schema.
If you don't want to make the file public, please PM me.
Regards,
Andy
Hi,
Thanks for the PM. Please find the modified report attached. Just need to make sure that the field lists match on both sides of the UNION and with the SelectList.
Regards,
Andy
HI, Andy,
eh... Strange... It still doesn't work on the project. I don't understand. It only still shows information that is from <LeftQuerySpecification>. I did check the order SelectionList and the number of them. I even tried to leave on PnPID on the list, it still only shows the information on <LeftQuerySpecification>.
Any thoughts?
Peggy
Hi, Andy
I get it to work now. It is the --xsi:type="PnPSetQuerySpecification"-- I didn't get it right. I used --xsi:type="PnPRelationshipQuerySpecification"--, Of course, it is not going to work...
Thank you very much!!
Peggy