how to parse/substr in birt query

i am working on BIRT report for ITM, i got the ITM data through SOAP method.

the result of SOAP method call is below:

field one date : IT_NT_SYS_CPU_CRIC
Field two data : *IF *VALUE NT_Processor.%_Processor_Time *LT 50 *AND *VALUE NT_Processor.Server_Name *EQ 'Primary:myserver:NT'

Now looking to get the portion value from field to after *LT that is 50 and after EQ that is 'Primary:myserver:NT'.

plese tell how i can do that. query is as follow:

SELECT SITNAME, PDT FROM O4SRV.TSITDESC WHERE SITNAME = 'mysituation'

thx

Find more posts tagged with

Comments

cypherdj

The first question is, is your field 2 string always following the same format? Is the LT value always the same length? Same for EQ value?

If so, you could add 2 computed columns to your data set:

LTValue -> expression: row["field2"].substr(45, 2)
EQValue -> expression: row["field2"].substr(89, 20)

If your string can have different formats or values vary in length, you should be able to use the RegExp built-in functions instead.

hope that helps,
Cedric

imughal

I am trying to do that can you please tell me If my string can have different formats or values vary in length, how do i use RegExp built-in functions.

cypherdj

Hi, 
 
I've never used regular expressions in Birt, but there is a match(expr) function in the String section of the native Birt functions. This returns an array of strings matching all the pattern groups. You'll simply have to iterate through the array to find the value you're after. 
 
For info on how to create your pattern, see 
<a class='bbc_url' href='http://java.sun.com/j2se/1.5.0/docs/api/java/util/regex/Pattern.html'>http://java.sun.com/j2se/1.5.0/docs/api/java/util/regex/Pattern.html</a> 
 
Another way you could do this quite simply is to use the split function. 
You might do something like: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
// split the string using * as separator
tokens = row["field2"].split("*");
value = null;
idx=0;
while (idx < tokens.length && value==null){
curToken = tokens[idx];

// split the token using space as separator
subTokens = curToken.split(" ");

if (BirtComp.equalTo(subTokens[0], "EQ"))
value = subTokens[1];

idx++;
}

value
</pre>
 
this should also do what you're looking for, or close to it (another way of saying I haven't tested any of this).

imughal

lot of thx its rock. need more help reg joining.

1st data set query is:

select "Server_Name","AVG_%_Total_Processor_Time", "WRITETIME", "MAX_%_Total_Processor_Time", "MIN_%_Total_Processor_Time", "AVG_%_Total_Privileged_Time" , "AVG_%_Total_User_Time", "AVG_Context_Switches/Sec", "AVG_Total_Interrupts/Sec"
from "NT_System_HV"
where "Server_Name" = ? and "WRITETIME" between '0000000000000000' and'9999999999999999'
and "SHIFTPERIOD" = ? and "VACATIONPERIOD" = ?

It returns many rows

2 data set query is :
SELECT SITNAME, PDT FROM O4SRV.TSITDESC WHERE SITNAME = 'IT_NT_SYS_CPU_CRIC'
it return 01 row.

there are some computed colums in both queries.

problem in joining how to join them which join type use. i tried but no luck. pls help.

cypherdj

Well, what column do you want to join the 2 data sets on?

To be honest, I can't quite see how you could join the 2 tables together, considering one table has only 1 row in it.

What is the purpose of joining those 2 data sets? What are you trying to achieve exactly?

imughal

Hi,

i want to join 2 queries on the basis of server name. 1st query has server name as field wheres 2nd query i picked server name using through computed colum as u have mentioned.

what i am trying to achieve is that i have displayed threshold in my report. now my report also has graph to show stats, graph data comes from one dataset which is based on jdbc connection. wheres threshold comes from another dataset which based on SOAP. method.

now i have to draw a line on graph on the basis on threshold value. is there any way to draw line on graph on the basis of threshold .

is there any way to reference one dataset field from or within one dataset.

i hope i have clear what i am trying to achieve.

thx

cypherdj

Hi, and sorry for not replying any earlier, 
 
first of all, since you now have the server name as a computed column of your second data set, you should now be able to join data set 1 and data set 2 on server name, by creating a joint data set (see retrieving data -> how to join data sets in the field guide to reporting) 
<a class='bbc_url' href='http://www.birt-exchange.com/documentation/BIRT_220/wwhelp/wwhimpl/js/html/wwhelp.htm'>http://www.birt-exchange.com/documentation/BIRT_220/wwhelp/wwhimpl/js/html/wwhelp.htm</a> 
 
With regards to presenting your graph with a threshold, you can create multiple series in a chart. Double click on the chart to bring up the edit chart dialog. In the select data tab, you will be using your newly created joint data set as the data set for this chart. On the left hand side of your chart is the value (y) series drop down list. In this drop down select New Series to add another series to your chart and assign it the row["threshold"] from your joint data set. This is in addition to the other column from your first data set, which defined your other data series. 
 
See this post for using combined charts (eg, bar and line charts combined): 
<a class='bbc_url' href='http://www.birt-exchange.com/modules/vbulletin/showthread.php?t=6693&highlight=combined+chart'>http://www.birt-exchange.com/modules/vbulletin/showthread.php?t=6693&highlight=combined+chart</a> 
 
Hope this helps you with your problem, 
Regards, 
Cedric

iffizin

thanks for ur kind reply.

i have done through global variable and javascript.

thx

235383

<blockquote class='ipsBlockquote' data-author="'cypherdj'" data-cid="34252" data-time="1215098375" data-date="03 July 2008 - 08:19 AM">
Hi, 
 
 
I've never used regular expressions in Birt, but there is a match(expr) function in the String section of the native Birt functions. This returns an array of strings matching all the pattern groups. You'll simply have to iterate through the array to find the value you're after. 
 
 
For info on how to create your pattern, see 
 
<a class='bbc_url' href='http://java.sun.com/j2se/1.5.0/docs/api/java/util/regex/Pattern.html'>http://java.sun.com/j2se/1.5.0/docs/api/java/util/regex/Pattern.html</a> 
 
 
Another way you could do this quite simply is to use the split function. 
 
You might do something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>

// split the string using * as separator

tokens = row["field2"].split("*");

value = null;

idx=0;

while (idx < tokens.length && value==null){

curToken = tokens[idx];

// split the token using space as separator

subTokens = curToken.split(" ");

if (BirtComp.equalTo(subTokens[0], "EQ"))

value = subTokens[1];

idx++;

}

value

</pre>
 
 
this should also do what you're looking for, or close to it (another way of saying I haven't tested any of this). </blockquote>
 
 
 
Hi, 
 
I have a doubt which is somewhat related to the above thread. 
 
I have a variable as following. 
 
valtxt=aaa/bbb/cc/ee/ff/ggg/hh/iii 
 
The above variable is generated at runtime and the values for the variable will change for groups. 
 
Now i need to split up the value and store it in diferent variables.I need as below 
valtxt1=aaa 
valtxt2=bbb 
valtxt3=cc 
valtxt4=ee 
valtxt5=ff 
valtxt6=ggg 
valtxt7=hh 
valtxt8=iii 
 
Since we are unable to determine the length of the value(bcause it will change for group), how can we do this. 
Is there any function to implent this or do we need to use looping. 
 
I think "/" is the key to split the value. 
 
Please help me on this...