Export to CSV, need to include Dynamic Text items

hoyt

Hello,

I have a table with a group, and in the group I'm running a subtable off of a different data set. I'm pushing the results of that subtable to an array (pCode.push(row["Code"])). Then in the detail row, I show what I need by calling the array pCode[parentCnt]. It works great when I run the report within BIRT, I see the text that I expect. If I export to Excel, it also works great. However, if I export to CSV, the columns with Dynamic Text are blank.

I found one result for this on Google that suggested I needed to set the property of the Dynamic Text item to Auto, instead of the default HTML. I made that change, but still do not see the values in the CSV export.

Is there something that needs to be done to make the Dynamic Text items show in the CSV export? Thanks!

jfranken

I am not totally clear on what you are trying to do with the Dynamic Text elements and arrays. My guess is that your design can be simplified. The best way to add a sub-table and have it display only the data associated with the outer table row is to add a filter to the sub-table. You will be able to reference the data from the outer table in the filter expression, i.e. row["Code"] Equal To row._outer["Code"] (where row._outer gets the value from the outer table row). Using this method, the inner table should display the correct data without having to use Dynamic Text elements or write code to save data to arrays. The CSV export should work as well.

hoyt

Thanks for the response Jeff! I probably could have done a better job explaining what I'm trying to accomplish. I need to match a CSV format which I disagree with, but the spec isn't mine, so I'm sort of stuck.

I have a 'parent' SQL that gets data about cases. Each case has between 12 and 20 detail items, for the sake of description, let's assume 15 items. In the CSV output, I need the case to be represented with 15 rows. In addition to these 15 things, there are 3 other items related to the case that I need to show, which will have between 1 and 8 items each (this will vary). Those three other things will always be less than the 15 rows about a case. The desired output is something like this:

case,A,B,C,D
case,A,,C,D
case,A,,C,D
case,A,,C,D
case,A,,,D
case,A,,,D
case,A,,,D
case,A,,,
case,A,,,
case,A,,,
case,A,,,
case,A,,,
case,A,,,
case,A,,,
case,A,,,

(etc with more cases)

So if I include all 4 of those case level details in my parent SQL, I could end up with 15 * 1 * 4 * 7 rows about a case. I can summarize that in BIRT by grouping on the case, but then I still have too many detail rows and the number of B, C and D things varies, so I can't really group to get rid of that duplication.

My thought here was to run my parent SQL and then at the grouping of the case_id, run 3 additional SQLs to get that sub-table data of B, C and D. Then in the detail rows, loop through my array and insert the data using Dynamic Text. I originally put the subtables in the detail rows where I wanted them to show, and hide them where I didn't want them, but this drastically slowed down my report because the SQLs were being executed 14 times more per case.

Does that help clarify? I'm open to suggestions if my approach is very wrong.

I think I see what you're suggesting and I'll have to try it out. You would suggest that instead of running this per case_id, run it at the parent level of the report, and then add a filtered table in the detail. I'll give that a go, but it'll take me a bit to get back to that.

Thanks!

jfranken

The sample output is helpful, but I am not following how the data is structured. What are the tables and columns?

hoyt

@jfranken said:
The sample output is helpful, but I am not following how the data is structured. What are the tables and columns?

In the database itself?

There's a CASE table with lots of stuff about the case and the other data is stored on relational tables using the case_id, so something like

CASE_A
CASE_B
CASE_C
CASE_D

If what you mean is the report itself, this is what I've tried to do to get the subtable and arrays (I only got through A, B and C in this example). The grey cells are set for the visibility to be hidden, I just do that for my own tracking.:

Thanks!