topic Re: Data Manager - Project Reports in AutoCAD Plant 3D Forum

Data Manager - Project Reports

Anonymous — Fri, 27 Jul 2012 00:29:44 GMT

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.

Re: Data Manager - Project Reports

PeterQuinn — Fri, 27 Jul 2012 17:02:03 GMT

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.

Re: Data Manager - Project Reports

Anonymous — Sat, 28 Jul 2012 13:12:39 GMT

Hi, Peter Thanks for reply. I do understand that one can sort the field, but that is only when one view it in Data Manger. No matter how I sort it when I view the data in Data Manger. The exported the excel is always sorted based on PnPID. I didn't find any setting that can help with this in Plant 3D so far. (Plant 3D 2012 is what I used.) I also checked about the Report Creator before, But I understand the Report Creator only allow you to create the report, not export to excel and read back? Moreover, I believe that Report Creator doesn't allow you to union the class like Access. With the report that we are creating, we need to union some of the class. With the amount of information we deal with, we need to be able to export to excel, put in the informations and import back to Plant 3D. Let me explain why I have to do these. 1. P&IDs are always changed during the design process. There are new valves, instruments added. Some valves, instruments are deleted. This is happened like every week. In order to make sure the information is not missed or wrong, we are export the schedule, edit it and put back to plant 3D database (This is the process filling the data for schedules). 2. in every submission of schedules to the client, the number of items in Data Manager is always different. It is needed to identify the new one, deleted one, etc. With the PnPID number order output, it just very hard to find the information, understand the data. That's why we think about sorting the excel output of the Data Manager. 3. during the life the design, there might be different engineers who process the schedules. 4. excel file outputted from Data Manger is not allowed to sort and some of the other basic excel function. In order to sort the data, we had to copy the whole sheet out and copy to a new sheet, edit it and copy it back, time consuming and dangerous. So, we are thinking, it is easier to have the excel output ordered as we needed to speed up the process. Cheers, Peggy -- Best Regards, Peggy Lin

Re: Data Manager - Project Reports

AndyDavisArkance — Mon, 30 Jul 2012 16:10:29 GMT

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

Re: Data Manager - Project Reports

Anonymous — Mon, 30 Jul 2012 22:19:52 GMT

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

Re: Data Manager - Project Reports

Anonymous — Mon, 30 Jul 2012 22:40:54 GMT

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

Re: Data Manager - Project Reports

AndyDavisArkance — Tue, 31 Jul 2012 09:10:05 GMT

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

Re: Data Manager - Project Reports

Anonymous — Wed, 01 Aug 2012 01:35:48 GMT

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.

Re: Data Manager - Project Reports

AndyDavisArkance — Thu, 02 Aug 2012 08:20:37 GMT

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

Re: Data Manager - Project Reports

Anonymous — Tue, 07 Aug 2012 13:20:15 GMT

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,

Re: Data Manager - Project Reports

AndyDavisArkance — Tue, 07 Aug 2012 15:52:24 GMT

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

Re: Data Manager - Project Reports

AndyDavisArkance — Wed, 08 Aug 2012 09:47:32 GMT

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

Re: Data Manager - Project Reports

Anonymous — Wed, 08 Aug 2012 23:20:09 GMT

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

Re: Data Manager - Project Reports

Anonymous — Thu, 09 Aug 2012 00:59:25 GMT

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

Re: Data Manager - Project Reports

Anonymous — Wed, 20 Nov 2013 00:05:46 GMT

Hi Andy,

I am looking into this again.. do you know what is the format on apply filters in "project report tempalte" xml?

Thanks,
Peggy Lin

Re: Data Manager - Project Reports

Anonymous — Thu, 27 Oct 2016 12:55:47 GMT

Hello,

is there an Editor or Tool to build such SQL XML scripts?