Export Repeater Table data in CSV or XML Output

[Slosurdo]

Hello,

I have a BI Report that I created with a repeater table. If I run the report in Excel, the repeater table data returns as expected. However, it takes almost 15 minutes to run.

When I run the report in CSV or XML format, it runs in seconds but it drops the repeater table data.

Any ideas why this happens? Is this a setting somewhere in BI?

I am using this report for an API integration and I am hoping the system is capable of exporting my report in XML format.

[dougp]

Why would you use a repeater table if CSV is the desired output format?

[MFGF]

Hello,

I have a BI Report that I created with a repeater table. If I run the report in Excel, the repeater table data returns as expected. However, it takes almost 15 minutes to run.

When I run the report in CSV or XML format, it runs in seconds but it drops the repeater table data.

Any ideas why this happens? Is this a setting somewhere in BI?

I am using this report for an API integration and I am hoping the system is capable of exporting my report in XML format.

For CSV, XML and Excel Data rendering formats, the report only delivers the contents of the first query in the report. I'm guessing your repeater table is fed from a separate query?

Cheers!

MF.

[dougp]

I think the repeater technically IS a separate query, the way Cognos writes SQL.

[MFGF]

I think the repeater technically IS a separate query, the way Cognos writes SQL.

By default a repeater will use a separate query, but (if my ever-aging memory isn't playing tricks) you can control which query feeds it. I'm pretty sure you get a dialog when you drop it on the page which asks what it should be named and what query it should use? And/or you can go to its properties after the fact and change the query it uses?

Cheers!

MF.

[Slosurdo]

The report has multiple queries that I built out to produce an integration file to a 3rd party vendor.

Attempted to use BI for this and the repeater table is perfect for what I need.

The Repeater table does use a separate query, so I was trying to understand if there's a way for the XML export to run ALL queries instead of just the first one that is called.

When I run the report in XML output, my list query returns the data and then the data for the Repeater table query is dropped.

The repeater table query is necessary as this returns enrollments data and one employee may have none or one and others may have 15. The data needs to be one row per employee with repeating columns for each respective enrollment.

[MFGF]

The report has multiple queries that I built out to produce an integration file to a 3rd party vendor.

Attempted to use BI for this and the repeater table is perfect for what I need.

The Repeater table does use a separate query, so I was trying to understand if there's a way for the XML export to run ALL queries instead of just the first one that is called.

When I run the report in XML output, my list query returns the data and then the data for the Repeater table query is dropped.

The repeater table query is necessary as this returns enrollments data and one employee may have none or one and others may have 15. The data needs to be one row per employee with repeating columns for each respective enrollment.

Hi,

The only rendering options that will return formatted output based on all queries are HTML, PDF and Excel. If you opt for Excel Data, CSV or XML, you get only the data from the first query - this is the way the product was written. There's no way to force multiple queries to be included using these rendering options.

It sounds to me like you're trying to use Cognos as an ETL tool for this task? That's really not what it was designed for, and you'd be much better finding a suitable tool for the job. Maybe look at something powerful but inexpensive, like Alteryx?

Cheers!

MF.

[Slosurdo]

Hi,

The only rendering options that will return formatted output based on all queries are HTML, PDF and Excel. If you opt for Excel Data, CSV or XML, you get only the data from the first query - this is the way the product was written. There's no way to force multiple queries to be included using these rendering options.

It sounds to me like you're trying to use Cognos as an ETL tool for this task? That's really not what it was designed for, and you'd be much better finding a suitable tool for the job. Maybe look at something powerful but inexpensive, like Alteryx?

Cheers!

MF.

Yes, I am trying to use this as an integration tool, assuming I can get it to work.

Is what I am trying to accomplish even possible in BI in another fashion? The data needs to be one row, per record with repeating columns for every iteration.

In example:

Employee1, Name, Location,...,Benefit 1, Benefit 1 amt, Effective Date, Benefit 2, Benefit 2 amt, Effective Date, Benefit 3, Benefit 3 amt, Effective Date
Employee2, Name, Location,...,Benefit 1, Benefit 1 amt, Effective Date
Employee3 ,Name, Location,...,Benefit 1, Benefit 1 amt, Effective Date, Benefit 2, Benefit 2 amt, Effective Date

I can do this in SQL Server and SSMS, independent of BI. I believe there's a way to write SQL in BI to create a report? Does it support writing SQL to produce the format I need (if I wrote this in one SQL query, would it work?). If so, I am happy to go that route.

Problem I am trying to solve for is doing this through BI and utilize an API to call the report.

Thanks for all your expertise and advice!

[dougp]

Define "BI".  Do you mean IBM Cognos Analytics?

[cognostechie]

Yes, I am trying to use this as an integration tool, assuming I can get it to work.

Is what I am trying to accomplish even possible in BI in another fashion? The data needs to be one row, per record with repeating columns for every iteration.

In example:

Employee1, Name, Location,...,Benefit 1, Benefit 1 amt, Effective Date, Benefit 2, Benefit 2 amt, Effective Date, Benefit 3, Benefit 3 amt, Effective Date
Employee2, Name, Location,...,Benefit 1, Benefit 1 amt, Effective Date
Employee3 ,Name, Location,...,Benefit 1, Benefit 1 amt, Effective Date, Benefit 2, Benefit 2 amt, Effective Date

I can do this in SQL Server and SSMS, independent of BI. I believe there's a way to write SQL in BI to create a report? Does it support writing SQL to produce the format I need (if I wrote this in one SQL query, would it work?). If so, I am happy to go that route.

Problem I am trying to solve for is doing this through BI and utilize an API to call the report.

Thanks for all your expertise and advice!

Yes, you can write your own SQL and use that in the report query but that will only give you the control over retrieving the data, not control over how the data is displayed. For what you want, using repeater is not the correct way to go. A simple master-detail arrangement can display all benefit info for every employee. If one employee has one benefit record, it will show one record for that employee , no benefits then will not show any benefits but still show the employee name, if it has 15 benefits then will show 15 benefit records for that employee while still showing one line for employee's name. If this is what you want then do this:

Create one list report which will be Query 1, rename the query name to 'Employee Master'. Put Employee Code, Name and Location as the columns.

Create another list which will be Query 2, rename it to 'Employee Benefit Details'. Put Employee Code, Benefit 1, Benefit Amt 1 etc. in this query.

Select the entire query 'Employee Benefit Details' and drop it as a column inside the 'Employee Master' . Now you have two queries which are one list inside another and now Cognos will let you specify a relationship between the two (join between two queries which would be on Employee Code). Select the query which has the benefit info. From the ancestor option on the top right, select 'List' to highlight the entire list. In the properties, you will see a property 'Master Detail'. Select that, click 'New Link' and select the Employee Code from both sides. Make sure you create an inner join. That's all you need.

This is the same concept as an outer query and an inner query.

[cognostechie]

You can also create an outer join if you want to see the Employees who do not have any benefits