Show all containing tables names for Columns

Hi, 
I am in the process of designing a report for our datamodel created in IDA. And, I am new to BIRT reports! Attached doc shows the format required. 
 
Report should list all columns within the datamodel alphabetically. However, the tricky part in this report is showing ALL TABLES THAT THE COLUMN IS PRESENT in ('Available in:' part in the attached format). I could get other details on the report (not many though) but could not figure out how to configure it to get tables. Any suggestion in this regard will be of great help. 
 
Thanks in advance for your time. 
 
Cheers.

Find more posts tagged with

Comments

mwilliams

Are you talking about listing the tables within a report that have a certain column? Or is this within your database?

arvindsr

It is within the database. If ColumnA is present in TableA, TableB, TableY then I have to list all these 3 tables for ColumnA. Attached template will show the format required.

Thanks for your time @mwilliams.

mwilliams

So, you're bringing in all tables, in your database, into different dataSets and need to get the above format as an output? Is this correct?

arvindsr

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="109449" data-time="1347910877" data-date="17 September 2012 - 12:41 PM">
So, you're bringing in all tables, in your database, into different dataSets and need to get the above format as an output? Is this correct? </blockquote>
 
Yes & No. I have all the tables in the database but for the purpose of reporting, I have only one dataset named Column. In fact, I am trying to customize the built-in IDA report 'Column Report' (Column.rptDesign).

mwilliams

Here's an example showing how to create a summary similar to that, for the Sample DB fields:

arvindsr

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="109519" data-time="1347994270" data-date="18 September 2012 - 11:51 AM">
Here's an example showing how to create a summary similar to that, for the Sample DB fields: </blockquote>
 
@mwilliams, thanks for the sample. However, I am getting 'The report file of version 3.2.22 is not supported' error when tried to open this sample report. 
 
I'm using: 
Version: 7.5.5.1 
Build ID: 20100323_1744

mwilliams

Can you look in the XML source of one of your report designs and let me know the version info, out of there? I made the report above in 3.7. Previous versions were 2.6.x, 2.5.x, 2.3.x, 2.2.x, etc. Let me know.

To describe the report, I queried the database for the table and column names, then I simply grouped a table on column, so that the table names listed for each column name.

arvindsr

This is the info in my Report design XML:
Eclipse version=3.2.17
BIRT Designer Version 2.3.2.r232_20090202 Build 2.3.2.v20090204-0730

Looks like I've a lower version!

mwilliams

Here's the same report, for 2.3.2:

arvindsr

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="109560" data-time="1348066167" data-date="19 September 2012 - 07:49 AM">
Here's the same report, for 2.3.2: </blockquote>
 
Thank you so much @mwilliams. This is the exact report I was looking for. However, being a newbie for both IDA & BIRT, I want to know how to change the datasource property of your report to reflect my datamodel(refer to 'SomeData Physical Model' in the attached screenshot - it shows the table structures also). I have attached my model/structure as taken from IDA. Please let me know how/what/where to change the properties of the report based on that. 
 
Also, how do I make this report prompt for a table name before throwing up the report so that it can print columns only from the selected table OR all tables. Multiple table selection, if possible, would be a big big help. 
 
Sorry that the questions may seem trivial but, they certainly are helping me streamline critical things in my department. 
 
Thanks in advance

mwilliams

I haven't used IDA, so I'm not sure how to query the tables there. If you look at the dataSet in the report I posted, you'll see I queried the sys.systables. You'll need to figure out how this is done in IDA. As for selecting a specific table, you can use script in your beforeOpen of your dataSet to use a multi-select parameter value in a where statement to limit your query. I can make you an example of that, when you're ready for it, if you need.

arvindsr

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="109652" data-time="1348240669" data-date="21 September 2012 - 08:17 AM">
I haven't used IDA, so I'm not sure how to query the tables there. If you look at the dataSet in the report I posted, you'll see I queried the sys.systables. You'll need to figure out how this is done in IDA. As for selecting a specific table, you can use script in your beforeOpen of your dataSet to use a multi-select parameter value in a where statement to limit your query. I can make you an example of that, when you're ready for it, if you need. </blockquote>
Thank you so much @mwilliams for your help. I got the report going in IDA with created data models as the source. I will try to implement the multiple table selection now.

Thanks again.

mwilliams

You're welcome. Let me know if you get stuck.

arvindsr

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="109710" data-time="1348507964" data-date="24 September 2012 - 10:32 AM">
You're welcome. Let me know if you get stuck. </blockquote>
 
Hi @mwilliams!; I have got struck in implementing the multiple table selection. Can you please provide a sample for this? 
 
And, I have a new roadblock for this report: 
Views for a particular table have to be shown along with the table details. A list will do without any other details regarding the View(s). I could get the entire Views list (like Tables) but, still trying to figure out how to relate a view to a table. Can you please show me how to get this going? 
NOTE: I am using IDA & here, Views are treated as independent entities like Tables (i.e. they will not go under a Table like Indexes - refer the attached screenshot). A generic dependency can be setup between a View & a Table but, this information seems to be not readily available while mapping columns in Report Designer.

mwilliams

Is there anything in the tables or views that can show which ones should be linked?

As for the multi-table select example, are you just wanting an example that shows selecting only certain tables, rather than all tables?

arvindsr

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="110458" data-time="1350057618" data-date="12 October 2012 - 09:00 AM">
Is there anything in the tables or views that can show which ones should be linked? </blockquote>
The only thing that establishes the linkage (relationship) is the Dependency. However, Dependency has only URI as its member in dataset [instanceof(//*,"Schema:ViewTable")]. 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="110458" data-time="1350057618" data-date="12 October 2012 - 09:00 AM">
As for the multi-table select example, are you just wanting an example that shows selecting only certain tables, rather than all tables? </blockquote>
I need to allow selecting one, multiple or all tables (like first option being 'All').

mwilliams

To create a report which allows the user to select one, many or all tables, you simply need to create a separate dataSet that brings in all table names, then use this dataSet in a dynamic list parameter, with multi-select enabled. Make the default value "all". Then, you can use this parameter value in the beforeOpen of your main dataSet to add a where clause something like:

//find out if all is one of the values selected

if(allIsNotSelected){
this.queryText = this.queryText + " where tableName in ('" + params["myparam"].join("','") + "')";
}

As for the views deal. The best way for me to help on that will probably be for you to make a flat file of what your data looks like, in your dataSet, and attach it in here, so I can see the relationship. It can be fake data, just make it similar, so I can see the relationship.

arvindsr

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="110541" data-time="1350348493" data-date="15 October 2012 - 05:48 PM">
To create a report which allows the user to select one, many or all tables, you simply need to create a separate dataSet that brings in all table names, then use this dataSet in a dynamic list parameter, with multi-select enabled. Make the default value "all". Then, you can use this parameter value in the beforeOpen of your main dataSet to add a where clause something like: 
 
//find out if all is one of the values selected 
 
if(allIsNotSelected){ 
this.queryText = this.queryText + " where tableName in ('" + params["myparam"].join("','") + "')"; 
} </blockquote>
 
Thanks @mwilliams. I'll try this. 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="110541" data-time="1350348493" data-date="15 October 2012 - 05:48 PM">
As for the views deal. The best way for me to help on that will probably be for you to make a flat file of what your data looks like, in your dataSet, and attach it in here, so I can see the relationship. It can be fake data, just make it similar, so I can see the relationship. </blockquote>
 
Hmmm... I will post the flat file...

mwilliams

Not a problem. Let me know if you get stuck. I'll be waiting on the flat file.