12 Replies Latest reply on Jun 22, 2016 12:37 AM by fuba

    VM report with DxDatabook properties

    tim.ringborg

      Hi

      I found this script Variant Manager excel report from script in this forum. This script does a part of what i like to do. What im missing is a way to get DxDatabook properties for the parts in the diffrent variants.

       

      The goal of my script is to do an excel file with a sheet for each variant. Im only interested in the substituted parts therefore those are the only ones exported to the excel file.

       

      So far I got everything to work except a way to incude additionell attribute information from the dxdatabook.

       

      If you using the inbuilt report generator in VM its possible to get those attributes by the .Properties property. How do i do that in my script?

       

      An additional question is it possible to get this VM information from a script in PADS?

       

      //Tim

        • 1. Re: VM report with DxDatabook properties
          fuba

          Hi Tim,

          it depends on where your property comes from.

          Some come from DxDatabook, others from Central Library.

           

          Look in your Script for the Code Line:

          vmbom.Properties = "$(Part Number)$(Value)$(Part Name)$(Description)" 'DxDatabook properties
          

          That is your point to start, for DxDatabook Properties.

           

          For Central Library Properties, you have to add below a second line:

          vmbom.UserProps ="$(Cell Name)"
          

           

          But there is also something wrong. I think you have the wrong objects in place.

          I used as a starting point the script CreateVariantBOM_Generic_1_1.zip from here, which is working:

           

          https://communities.mentor.com/message/57052#comment-57052

           

          I extended the .Properties and .UserProps to:

          vmbom.properties ="$(Part Number)$(Value)$(Part Name)$(Description)$(Engineering Description)$(Cell Name)$(Component Height)$(Preference Group)$(Preference Status)"
          
          vmbom.UserProps ="$(Cell Name)"
          
          
          
          
          

           

          Take a look in that script, maybe it helps. If not, then come back, and we see together what we can achive.

           

          Check the Variant Manager Automation Reference from the InfoHup/Help, There they explain that the

           

          "The VMBOM object represents the BOM Reports dialog. Use this object to define the format, settings and variants to report in a BOM.  "

          This is in my eyes only a "Report Engine", which exports *.txt and *.xls files.

           

          Ciao

           

          Stefan

          • 2. Re: VM report with DxDatabook properties
            Patrick.Cashman

            I use the VM automation to get unplaced and replaced part information. That's pretty straightforward and is covered well in the help documentation.  I have an example subroutine I'll share if you'd like it. 

             

            Beyond that, I do everything I need to with component properties by interacting directly with the database that supports DxDatabook.  The database interfaces are well documented all over the web, they are relatively easy to use, and very fast.  Of course one cannot just go about willy nilly adding properties or changing them.  In the DxDatabook configuration, some properties are not applied, some are altered, and so forth.  But if a little care is taken to make anything you do with properties retrieved from the database correspond to the way DxDatabook is configured to use them, no problem. The cool thing about doing it this way is that you are not limited at all by the tools VM or DxDatafbook give you. The code is simple, the structures you work with are simple, and as I said, it's very fast.  I have some examples of how to work with databases also if you are interested in exploring that route. 

            • 3. Re: VM report with DxDatabook properties
              tim.ringborg

              Hi Fuba and Patrick

              Thank you both for answering.

              I have already tried the variant script that you referred to Fuba. When I went that route I found no way to manipulate the Excel file the way I want to.

              The background for my problems is that we use PADs and already have a script that we run from PADs (this includes pick and place files and export files for our PLM system and of course the BOM and revision history).

              Now since we swapped into (integrated flow) we started to use the VM manager and we have designs with several variants and with substituted parts in them. In the PADs script there seems not possible to get more information from VM except that of parts is not mounted or is substituted but not any part information apart from Parttype.

              To get around this my idea was to export an Excel from the schematic side that includes ref designator and additional information of all substituted parts. Then my script in pads could just go get this data.

              This seems a little complicated for this simple task but it’s the only way I thought I could manage to do. But  now the only information I get out of the system are the VM properties. I need the data from the databook.

               

              As Patrick writes it seems to exist a much better way to do this by just go directly to the databook.

              I have never tried to access the databook throw scripts. Is it possible to do this access to the databook even from PADs it would really make my day.

               

               

               

              //Tim

              • 4. Re: VM report with DxDatabook properties
                Patrick.Cashman

                A small point of clarification: I am not accessing dxdatabook.  I am going to the database behind dxdatabook and accessing that directly.   In essence I am doing something similar to what dxdatabook does - reading from the database and doing something with the data. You should be able to do this from any script, so doing it from one that is connected to PADS and getting part information from there is equally possible as doing it from a script connected to DxDesigner.

                 

                There is a ton of information on the web about how to read/write data from a VB application or script to a database.  The cool thing about the way it's done is that it can work with almost any kind of database.  There are some basic building blocks you will need to become familiar with:

                 

                (note: this is done in vb.net.  There will be differences if you are using vba in excel or vbscript, but the basic structure is similar. )

                 

                1. Connection string: this is a string of text that the built-in functionality of Windows and databases recognize as a command to establish a connection to the database.  It will be represented by a string variable.  It will be specific to the kind of database you are using.  Here is the variable assignment for my connection string:

                 

                (in a public area of a module)

                Public con_str As String

                Public my_db_path As String

                 

                (in a subroutine)

                my_db_path = "C:\my_db.accdb"

                con_str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & my_db_path

                 

                2. Connection object: This is part of the thing that handles all the behind the scenes API and system calls that Windows uses to get the work done between your application and the database.  You don't need to worry too much about what is going on in there.  Just create it and let it do its thing.

                 

                (in a public area of a module)

                Public conn As OleDbConnection

                 

                (in a subroutine)

                conn = New OleDbConnection(con_str)

                 

                The connection object makes use of the connection string to know what database to connect to.  Then it needs a command to tell it to open the connection to the database.  This is a simple command:

                 

                conn.Open()

                 

                Then you do more stuff with the open connection, which I'll get to in a moment.  For now, just remember that whenever you open the connection, you need to also close it:

                 

                conn.Close

                 

                You should now be asking yourself how often you should open/close the database.  Once for the entire application or for each time you need it?  The answer is open and close it each time you need it.  If left hanging open, invariably it will get stepped on by something and you'll get a crash.

                 

                3. Data adapter: The other part of the thing that Windows uses to interact with the database.  Think of this and the connection as a handshake. The database on one side, Windows OS (and your application) on the other

                 

                4. Next there are three objects which are a hierarchy.  Dataset, Datatable, Datarow.  A dataset is a collection of data, which can be any of several things.  It can be a group of tables.  It can be a single table.  It can be a subset of a table, or a single datarow.  The easiest way to use it is by querying a table to get some data from it.  While what you get in the dataset isn't a whole table, think of it as a small table made from a larger one.  And think of it as a table. (Just be aware that it can be used many other ways too.)

                 

                So, in a simple scenario, a database is queried and a dataset is the result.. The dataset contains a table.  The table contains rows. The rows are just like the rows of data in the database.

                 

                The tables in a dataset are indexed in a zero-based index.  The rows in a table are indexed the same way, and the fields in a row are also indexed the same way.

                 

                5. Now how do you get the data you want from the database?  SQL statements.  There are other ways, but sql is the simplest and most powerful. It may seem daunting at first, but once you get a handle on how to use sql, you'll find that it's not complicated and pretty easy to use. The cool thing is that it works with many databases.  So if by chance you change to a completely different database, as long as the tables in it are named the same and have the same column names, the sql statements don't need to change.  The only thing you'd change is your connection string.  Thus the use of sql is very portable.

                 

                I use sql as part of a function, so that much of the code only has to be written once.  I have a bunch of such functions, that I apply as needed to run similar queries.  Here is an example of such a function:

                (conn, con_str, da, and sql are declared externally to the function)

                 

                     Public Function select_from_table_where_field_equals_value(table As String, field As String, value As String) As DataTable

                 

                         conn = Nothing

                         conn = New OleDbConnection(con_str)

                         conn.Open()

                         'setup the sql statement

                         sql = "SELECT * FROM " & table & " WHERE " & field & " = " & Chr(34) & value & Chr(34) & ";"

                               

                         'start the data adapter and load the table data

                         da = New OleDbDataAdapter(sql, conn)

                      

                         Dim ds As New Data.DataSet

                         Dim dt As New Data.DataTable

                      

                         Try

                             da.Fill(ds)

                             dt = ds.Tables(0)

                         Catch ex As Exception

                             'Debug.Print("failed with table: " & table & ", field: " & field & ", value: " & value)

                         End Try

                 

                         da = Nothing

                         ds = Nothing

                         dr = nothing

                      

                         conn.Close

                      

                         Return dt

                         dt = Nothing

                      

                     End Function

                 

                Here are some more function calls and the sql statements in them:

                 

                Public Function select_from_table_where_id_is(table As String, field As String, value As String) As Data.DataTable

                     sql = "SELECT * FROM " & table & " WHERE " & field & " = " & value & ";"

                (this value will never have spaces, so no quote characters are needed)

                 

                Public Function select_from_table_with_two_values(table As String, field1 As String, value1 As String, field2 As String, value2 As String) As Data.DataTable

                sql = "SELECT * FROM " & table & " WHERE " & field1 & " = " & Chr(39) & value1 & Chr(39) & " AND " & field2 & " = " & Chr(39) & value2 & Chr(39) & ";"

                (data may have spaces, quote characters are needed)

                 

                Public Function select_from_table_with_two_int_values(table As String, field1 As String, value1 As Integer, field2 As String, value2 As Integer) As Data.DataTable

                sql = "SELECT * FROM " & table & " WHERE " & field1 & " = " & value1 & " AND " & field2 & " = " & value2 & ";"

                (integer values)

                 

                There is some very good information on how to construct sql statements here:  http://www.w3schools.com/sql/default.asp

                 

                If you are using vbscript the methods are similar, and even simpler.  There is a great example here, which bypasses the data adapter:

                 

                How to Connect to Access Database with VBScript

                 

                To do all this, you need to find out where your database is, get the correct connection string for it, and make sure you (and your users) have read access to it.

                • 5. Re: VM report with DxDatabook properties
                  tim.ringborg

                  Tanks Patrik

                  I will go ahead and try this tomorrow, national day today. This realy seems to be super nice way to do this.

                  • 6. Re: VM report with DxDatabook properties
                    fuba

                    Peter,

                    which database are you exactely talking about ? Where is it located ?

                    Is it part of the Project / Mentor Job or ? Does it really have the extension *.accdb ?

                     

                    /S

                    • 7. Re: VM report with DxDatabook properties
                      Patrick.Cashman

                      The way dxdatafbook works is that there is an database that holds all the information, and dxdb reads it from there.  Dxdb does not hold any data itself.  It's more of a sophisticated go-between that reads all the data, constructs database queries as you need them, then executes them, and applies the properties or does comparisons as appropriate.

                       

                      Dxdb can be configured to work with many flavors of database tools.  Such as Microsoft Access, SQL Server, dbase, Oracle, a set of text files, or a number of others.  The configuration of dxdb is normally done by a system admin, and is saved in a .dbc file.  This file can be saved anywhere, but most likely can be found in the central library or somewhere near it.  The path to the dbc file can be found from any DxDesigner project, under Setup > Settings > Project > xDX Datafbook.  The dbc file will most likely refer to an ODBC connection, which is an operating system object that tells the OS it can use a database and provides a standardized protocol to interact with it.

                       

                      (.accdb is the extension for MS Access databases from Office 2010 onwards.  Before that it was .mdb)

                       

                      You can right-click in dxdatabook and select Configure to see the configuration graphically.

                       

                      The ODBC connection is also normally set up by a system admin on your computer.  You can see them by looking in the Control Panel under Administrative Tools > Data Sources (ODBC).  The properties of the ODBC object will include the location of the database and a driver for it.  (One note - 64 bit systems use a different ODBC admin tool than 32 bit systems.  The 64 bit version is C:\Windows\System32\odbcad32.exe, the 32 bit version is at C:\Windows\syswow64\odbcad32.exe.  I'm not quite sure how the system decides which one to use from the admin tools, so I just navigate to the files and run them directly. )

                       

                      So, the overview:

                       

                      DxDesigner  >> DxDatabook >> configuration.dbc >> ODBC connection >> database

                       

                      The scripting method is simpler:

                       

                      Script >> connection object >> connection string >> database.

                       

                      With scripting, you have to know the structure of the database.  There is nothing to tell you the table names, column names, or field data types.  So you can easily break the database or get faulty data if you don't use it the way the database was designed.  Fortunately, the database needed for dxdb is simple, without any complex relationships between tables, or anything of the sort.  Each table is independent of the others, so once you know the table and column names, it's very straightforward.

                      1 of 1 people found this helpful
                      • 8. Re: VM report with DxDatabook properties
                        fuba

                        Dear Peter,

                        I still do not see how you connect to the database / databook.

                        In your last post you mentioned a few times "dxdb" is this the application "dxdb.exe" from here:

                         

                        C:\MentorGraphics\EEVX.1\SDD_HOME\common\win64\bin

                         

                        I also do not know, how you "fill" the file you mentioned in one of the previous posts

                         

                        my_db_path = "C:\my_db.accdb"

                        con_str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & my_db_path

                         

                        The rest after this (querying, ...) is ok for me, but how do I get from DxDatabook the data out ?

                         

                        Thanks again

                        /Stefan

                        • 9. Re: VM report with DxDatabook properties
                          matthias.cosaert

                          In Dxdesinger go to setup settings → DxDatabook Data Source Select a data source
                          There you can find the database location/alias and it might also have a username & password.

                           

                          What I did in Vbscript (Ecad is our database alias):

                          con_str = "DSN=Ecad;Uid=Appl_EcadLibdx_Read;Pwd=Mypasswrd;"

                          Set objConn = CreateObject("ADODB.Connection")

                          objConn.Open con_str

                          • 10. Re: VM report with DxDatabook properties
                            Patrick.Cashman

                            Stefan,

                             

                            Dear Peter,

                            It's Patrick, btw, not Peter

                             

                            I still do not see how you connect to the database / databook.

                            In your last post you mentioned a few times "dxdb" is this the application "dxdb.exe" from here:

                             

                            C:\MentorGraphics\EEVX.1\SDD_HOME\common\win64\bin

                            No.  I was using dxdb as shorthand for DxDatabook.  You will never interact with the executable dxdb.exe.  Forget that it exists; you don't need it at all.

                             

                            I also do not know, how you "fill" the file you mentioned in one of the previous posts

                             

                            my_db_path = "C:\my_db.accdb"

                            con_str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & my_db_path

                             

                            As Matthias indicated, you need to find out what database DxDatabook is using as a source for its data.  DxDatabook does not hold any data at all.  It is just a window into another data source. I suggest that you go talk to the person at your company who is in charge of the setup and configuration of your central library.  That person should know all about the external database DxDatabook is using as a source.  Where it is located, what application it was created with, the table structure, and do on.  You will need to know all this in order to make your own window to it.

                            • 11. Re: VM report with DxDatabook properties
                              fuba

                              Patrick, sorry for the name confusion from my side.

                              I did some testing yesterday, and getting closer now.

                               

                              I know know a little bit better how things work here.

                              Under: setup settings → DxDatabook Data Source Select a data source

                              I only have one entry to a webserver. Accessing this one I get some errors

                              when initializing the OLE or ADO objects. I will try to find out someone

                              from my company, and check with him what I am doing wrong.

                               

                              Thanks for all the effort you put in here so far.

                               

                              I will respond when I solved that.

                               

                              Thanks

                               

                              Stefan

                              • 12. Re: VM report with DxDatabook properties
                                fuba

                                Matthias, thank you,

                                your post from above, made things clearer to me.

                                like I mentioned to Patrick, I have to check with someone

                                from the ECAD Team to find out what I am doing wrong.