How to sort the List of Table Columns report component by table name then column name

[bog]

Hello,

I am making efforts not to express my frustration dealing with the PD reporting tool 🙂

I want to pull out of my data model a simple a list of tables, and for each table the list of its columns with their information, name, datatype, comments. Simple enough, right?

So:

1. What items from the toolbox do I have to select in order to produce this report (I am using the modern report tool, not the legacy editor):

<Table1>

<Table1 comments>

<Table1 table of columns>

<Table 2>

<Table 2 comments>

<Table 2 table of columns>

... etc.

It's a master detail report where the master represents the tables list and the details are the columns for each table.

2. Because I could not figure #1 out, I settled for the List of Table Columns from the toolbox. But how do I sort the list by table name, column name? I tried to use the Edit Selection dialog. When I check the Table and the Name attributes and run the report nothing gets displayed. The Used checkboxes are clear for these two.

3. How do I sort the columns by their intrinsic order in the table - there seems to be no attribute that stores the Order Index of a column (that is the order the columns are displayed by default in the Table Properties -> Columns tab grid), or whatever other name they use for this value.

The database is MS Sql Server 2012 and the PD version is: 16.6 SP06 PL07 (64 bit), 16.6.6.7 (5753).

Thanks!

[former_member1209767]

I'm not sure which version you are using, but in 16.6 you can easily do it in List Report.

Just add two column: Parent and Position in Parent Collection, then you can sort with them.

[GeorgeMcGeachie]

I can get the list of columns to sort alphabetically. In the report editor, choose 'edit selection' on the toolbar for the list of columns

In the 'define sort and filter' dialogue, select the 'S' column in the first row (next to Name)

Now the list is sorted

I did try including the parent name in the sort sequence, but the output was empty. This is a list of columns in one table, so that wasn't actually necessary, sorting by name is enough

[GeorgeMcGeachie]

It does look like attempting to sort on more than one attribute causes the output to be blank, which is definitely a bug

[bog]

Ok, thanks for confirming.

[bog]

Hi, George,

Thank you for your email. I know that the sorting works in that dialog you attached for the standalone List Report, however, for the Table -> List of Columns item inserted in a report, the Sort and Filter dialog doesn't seem to work. As soon as I check the Name & Column fields the report doesn't return any data anymore.

Bogdan

[GeorgeMcGeachie]

If this is the only content in your report, consider using a List Report. As Ondrej says, the columns in a table will appear by default in the order they are listed in the table. You can change that sort order if you want to, on the Row Filter tab.

In this List Report of Columns I've told it (using the "S" column) to sort the List Report by Table Owner, then by Table. It would be simple to add another entry to sort by the column name - select "Name" on the list on the left, then check the "S" box.

[bog]

Thank you, Ondrej. Just curious, did you try #2 and if you did, did you get the same behaviour? Is it a bug?

[Ondrej_Divis]

In simple lists (=separate list of columns for each table) PD sorts columns according its orginal order in table automatically. No adjustments needed here. If you create one big list of all columns in the whole model then you have to help PD a bit to affect the ordering. Create an extended attribute (datatype Integer) on the Column metaclass and fill it with numbers representing position of the column in the table. In case of List report, you can modify Row filter and put table name at first position and this extended attribute at second position and then set sorting to both of these attributes. Then it will start working.

To not to get crazy by filling this extended attribute with proper values for all columns, you can set it to computed and fill these position values automatically by script. All you need from this script is to simply browse the Columns collection of the parent table and set the ext.attribute values, because order of columns in this collection is the same as order of columns in the table.

HTH,

Ondrej

[bog]

Ok, I figured out #1, I used List of Columns under the Table book in the toolbox.