nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Sorting on dynamic text field in table

miwalker

I have a table in my report with multiple columns (A, B, C, D, and E) that reference datasetRows from a couple datasets. I added a column before 'A' that uses dynamic text.

In this dynamic text field I added if statements like:

if ((row["WI_TYPE"] == "apar" && row["SEVERITY"] == "1")){
"1";
} else if ((row["WI_TYPE"] == "apar" || row["WI_TYPE"] == "story") && (row["PRIORITY"] == "Hot")){
"3";
} else if ((row["WI_TYPE"] == "story " || row["WI_TYPE"] == "task") && (row["ITERATION_NAME"]== "I1_Dev")){
"4";
}

I'd like to be able to Sort on this column so all the 1's are at the top, followed by the 2's, and so forth. When I look at the Sort option for the table it doesn't show the dynamic text field, only the columns associated with the datasetRows.

Is there a way I can sort on this dynamic text field? Maybe through the Expressions option in the Table Sort option?

Find more posts tagged with

Comments

mwilliams

miwalker,

I don't know of a way to sort using a dynamic text box as the sort key. Can you use the same code in a data binding on the table? Or do you have to use a dynamic text box?

miwalker

Michael, 
 
Sorry I took so long to respond. I was pulled away to handle something more urgent. 
 
I don't think I can use a Data Binding because the values come from 4 or 5 different datasets. One suggestion I was given was to hide the table, store the data the table collects in a Javascript Array, sort the Array, then expose the sorted data through a scripted dataset to an unhidden table. What do you think of this idea? 
 
 
My table has the following Columns: 'Developer Priority', 'ID', 'SUMMARY', 'WI_TYPE', 'STATE', 'PRIORITY', 'SEVERITY'. ID and Severity are Integers and Developer Priority is a Dynamic Text field. The others are all Strings. 
 
Keep in mind my programming experience is limited, but I know the basic concepts. 
 
I hid the table on my Report, then selected it and under Scripts added the following to On Prepare: 
 
var myArray = []; 
 
var i = 0; 
 
 
 
 
I then added the following to On Create. I read that On Create iterates through each row while On Prepare is one-time before the data is gathered. I also didn't include the Dynamic Text field yet because I'm not sure how to reference it. 
 
 
var w = myArray; 
w.id = row["WI_ID"]; 
w.summary = row["SUMMARY"]; 
w.type = row["WI_TYPE"]; 
w.state = row["STATE"] 
w.priority = row["PRIORITY"] 
w.severity = row["SEVERITY"] 
i++; 
 
 
 
 
Next I created a Scripted Dataset and created Output Columns for each of the rows above. In the Open script for this dataset I added: 
 
 
var index = 0; 
 
 
For the Fetch script I added: 
 
 
if (index == i) { 
return false; 
} else { 
var y = items[index]; 
row["ID"] = y.ID; 
row["SUMMARY"] = y.summary; 
row["TYPE"] = y.type; 
row["STATE"] = y.state; 
row["PRIORITY"] = y.priority; 
row["SEVERITY"] = y.severity; 
index++; 
return true; 
} 
 
 
My assumption is that the variable i now equals the number of rows that will be returned from the On Create script but maybe I have the order of execution for the scripts incorrect. 
 
When I run the report it complains that my variables (myArray and index are undefined). I thought where I defined them was ok because they'd be global variables. 
 
Am I on the right track here? Are there any sample reports that I can look at that would help me with this? 
 
Thank you for your time.

mwilliams

miwalker,

I don't have an example of doing this, so we'll have to work through it. For starters, you might try putting MyArray and index in the initialize script of your report. If you can create a report using the sample database that is set up how you're talking about and post it in here, I'll also be able to work with it to try to figure something out.

miwalker

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="65266" data-time="1275579084" data-date="03 June 2010 - 08:31 AM">
miwalker, 
 
I don't have an example of doing this, so we'll have to work through it. For starters, you might try putting MyArray and index in the initialize script of your report. If you can create a report using the sample database that is set up how you're talking about and post it in here, I'll also be able to work with it to try to figure something out. </blockquote>
 
Michael, 
 
I moved variables to the initialize script of my report and those errors disappeared. I now get this error: 
 
Cannot set property "id" of undefined to "14179" 
 
I created a scaled down version of the report using the sample database that I attached below. With that report I get the error: 
 
Cannot set property "customer" of undefined to "null" 
 
My goal is to re-display the table using the scripted dataset and have the data sorted on the Developer Priority field. For now I'm just trying to get the data displayed from the scripted dataset, then I'll add in the Sort.

mwilliams

Can you attach the sample report using the sample dataSet? Also, what is your BIRT version? Thanks.

miwalker

Here it is. Not sure why it didn't go through the first time.

I'm using Birt 2.3.2 with Rational Team Concert.

mwilliams

It was having an issue with the properties of your array and the way you were trying to set them. I modified the report so that the array just used a different index step for each field. I'm having an upload issue at the moment, so I'll upload it as soon as that is resolved.

mwilliams

Upload problem solved. Here it is. Take a look.

miwalker

Thanks Michael I appreciate it. 
 
How do I add the dynamic text field, Developer Priority, to the array as well? 
 
 
I tried: 
 
myArray = this.getRowData().getExpressionValue(0); 
myArray[i+1] = row["CUSTOMERNAME"]; 
myArray[i+2] = row["CUSTOMERNUMBER"]; 
myArray[i+3] = row["CITY"]; 
myArray[i+4] = row["STATE"]; 
i = i+5; 
 
 
but this tried to return the value from the CUSTOMERNAME row. 
 
I also tried row[0] which returned the BIRT Row # as expected and row[1] returned nothing. I'm assuming the dynamic text field doesn't have a column index because the table is linked to a dataset that this dynamic text column isn't a part of. 
 
I didn't add it as a computed column to the dataset because it needs data from multiple datasets.

mwilliams

I'll see what I can do. I'll let you know what I come up with.

miwalker

Hi Michael,

Were you able to find anything about adding the Dynamic Text field to the array? Is it possible to use the Element ID?

miwalker

Hi Michael,

By chance did you find a way to add a dynamic field to an array? Thanks,