Sum of a column in crosstab(BIRT 2.5)

Hello,

I need some help. I'm new to BIRT and I try to calculate sum of a column using BIRT 2.5 but i don't know how.
I must to put the value into a Data Cell. It's correct to say, Total.Sum(data["Percent"]) where data["Percent"] is (VALUE from a cell)/SUM(VALUE from a cell) or how can i use Total.Sum in BIRT 2.5?

Thanks in advance!

Find more posts tagged with

Comments

Hi nick29,

Crosstabs have grand total rows/columns to take care of summing across dimensions. Is what you're trying to do not a standard sum of a column?

I have a table like this: <br />

<br />
| 1 | 1/10 = 10.00% |<br />

<br />
| 10 | Sum of 10% |<br />

<br />
I need cu calculate sum of a column who is calculated like this: <strong class='bbc'>value 1</strong> / <strong class='bbc'>value 10</strong> where 10 and Sum of 10% represent a subtotal. I calculate the value 1/10 like a data binding not like an aggregation, because i don't know how to calculate this report otherwise. I attach the report file and the xml file to create datasource. I highlight with red the column who need to be calculated. Maybe someone can help me to calculate sum of this column. Thanks!<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'><?xml version="1.0"?>

<requests>
<request>
<request_state>1</request_state>
<priority>7</priority>
<time_interval>Time Interval 3</time_interval>
<value>2</value>
</request>

<request>
<request_state>2</request_state>
<priority>3</priority>
<time_interval>Time Interval 3</time_interval>
<value>7</value>
</request>

<request>
<request_state>2</request_state>
<priority>5</priority>
<time_interval>Time Interval 2</time_interval>
<value>1</value>
</request>

<request>
<request_state>4</request_state>
<priority>2</priority>
<time_interval>Time Interval 4</time_interval>
<value>1</value>
</request>

<request>
<request_state>4</request_state>
<priority>3</priority>
<time_interval>Time Interval 4</time_interval>
<value>2</value>
</request>

<request>
<request_state>4</request_state>
<priority>4</priority>
<time_interval>Time Interval 4</time_interval>
<value>3</value>
</request>

<request>
<request_state>4</request_state>
<priority>5</priority>
<time_interval>Time Interval 4</time_interval>
<value>4</value>
</request>
</requests>

</pre>

nick29,

Run the report and let me know if this is what you're wanting.

Thanks! Yes, that is what i want to obtain but i have the following two problems:<br />
<br />
1) the column <strong class='bbc'>Percentage</strong> must be calculated like this <strong class='bbc'>Value</strong> column / <strong class='bbc'>TotalValue</strong> column (<strong class='bbc'>Percentage</strong> =<strong class='bbc'> Value</strong>/<strong class='bbc'>TotalValue</strong>) and then <strong class='bbc'>TotalPercentage</strong> must be Sum of <strong class='bbc'>Percentage</strong>.<br />
This is my problem, i don't know how to put the condition in the Aggregation to calculate the <strong class='bbc'>Percentage</strong> and then to calculate the <strong class='bbc'>TotalPercentage</strong> depending on <strong class='bbc'>Percentage</strong> cell value.<br />
<br />
2) If the value from <strong class='bbc'>Percentage</strong> cell is null i don't want to display value 0.0%, empty cell must be displayed.<br />
<br />
I've modified and attached the new file. The XML file remains the same.<br />
<br />
Thank you very much!

#2 can be solved by removing the script I put in the onCreate of the aggregation for percentage total to make 0% appear. I think I understand what you mean for #1 now. Let me see what I can do.

Ok, thanks! <br />
For the #2 I left the code that you put in method onCreate , but I remove the 0 value and it works fine. <pre class='_prettyXprint _lang-auto _linenums:0'>if (this.getValue() == null){
this.setDisplayValue("");
}</pre>
Now I must think at #1.

So, anyone who can help me? Please,..

It's gonna be pretty messy to do this, I think, because of the way a crosstab is formed. There will be a lot of storage in persistentGlobalVariables of values and I'm not even 100% sure that will work. I started it last night and will continue to work on it today to try to see if I can do it. I'll post the example if I get it to work.

Hello Michael,<br />
<br />
Ok, i calculate the cell value <strong class='bbc'>Percentage</strong> = <strong class='bbc'>Value</strong>/<strong class='bbc'>TotalValue</strong> but <strong class='bbc'>Percentage</strong> cell it's not an Aggregation, it's a Data Binding cell. <br />
Now I must calculate the SUM of a column <strong class='bbc'>Percentage</strong> but i don't know how to do this. <br />
I put a highlight condition on cell who calculate <strong class='bbc'>TotalPercentage</strong> and a text with character "?". <br />
The cells will be red when cell <strong class='bbc'>Percentage</strong> is not null. In that way we will see more easy what column we must to calculate and if the value is correct. If we will calculate this sum the report is DONE.<br />
I attached the new file(the xml file remains the same). Now I need your help more than ever. <br />
Maybe we need to remove the rest of the files because other users will be confused.<br />
<br />
Thank you!

Nick,

Ah, I did that exact same thing, I think, but didn't do the null check. That must've messed up my calculations or something. Quick question: Is there ever going to be an instance where these don't add up to 100%?

Michael,<br />
<br />
I don't think so, in my Excel all <strong class='bbc'>TotalPercentage</strong> columns are 100% in all cases but if i don't calculate this sum i have a real problem because i must calculate the Grand Total for <strong class='bbc'>Value</strong> column which is not standard Grand Total is Sum of Value from following intervals of time: Time Interval 1 + Time Interval 2 + Time Interval 3. I attached a printscreen with an example.<br />
****, I agree, this report is a mess.<br />
<br />
I hope we can calculate successfully that sum.<br />
<br />
Regards,<br />
Nick

Nick,

Based on your calculations to get the percentages, it will be 100% every time. If your total value is a sum of all of your values in the column. The percent that each value makes up will always add up to 100, so you could just put a static 100% in this field if the value field isn't null. As for the "row total", you only want to sum intervals 1,2, and 3? Not 4 and 5?

Michael,

I think it's a good ideea to put 100% for all cell which is not null but that sum must be calculated.
Yes, only 1, 2 and 3 not 4 and 5 and I think I have an ideea but if you have another ideea and you finish the report, please post it, maybe your ideea is better than mine. If I will finish the report, i will post it.

Thanks!

Nick,

Here's one way to do it. I didn't do the static 100% values in this, but I have the totals for only inteverals 1-3.

Hello Michael,

Oh, impressive. Excelent ideea to put that code into a Text Component.
You helped me very, very much! Thanks!

Regards,
Nick

Nick,

Not a problem. Let us know whenever you have questions!

Hello Michael,<br />
<br />
Yes I have three new questions:<br />
<br />
#1 I have an embedded report <pre class='_prettyXprint _lang-auto _linenums:0'><b:birt designType="embed" embeddable="true" masterpage="false" designName="myReport.rptdesign" >List of params</b:birt></pre>. I want to put the export buttons (to Excel, Pdf, etc) but i don't know how to do this.<br />
<br />
#2 How can I sent a list of parameters to BIRT. For example I have <strong class='bbc'><h:selectManyListbox></strong> component and I want to send the values to BIRT and put the values in my report like this:<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>select * from myTable where someColumn in (?)</pre> where ? it's my list from <strong class='bbc'>selectManyListbox</strong>.<br />
<br />
3# If I don't select any parameter in my form, the report is generated with default values but in Eclipse console is thrown a lots of errors(paramX it's invalid, BIRT cannot convert from "null" to java.sql.Date, or to Integer, depends of type of parameter) even if in BIRT checkbox "Is required" is unchecked. BIRT still needs a value for all the parameters.<br />
<br />
Thanks!

Nick,

For 1, are you not embedding the viewer?

For 2, you'll need to separate the list object into a comma separated string to pass it through the query by adding this part of the where clause in the beforeOpen script. You can find examples of this in the devShare. Here is one: http://www.birt-exchange.org/org/devshare/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause/

For 3, If you add the where clause in the beforeOpen script, you can check for null values and not add this part of the clause if there is no parameter, or you can insert default values to limit it to what you want if nothing is chosen.

For #1: Yes.<br />
<br />
For #2 i have the following sql <pre class='_prettyXprint _lang-auto _linenums:0'>where m.CUSCDE = ? and mp.PRDCTGCDE in ('qREQPRDCDE') and mp.SRVCDE in ('qREQSRVCDE')</pre>. I add the following lines of code in <strong class='bbc'>beforeOpen</strong> method: <br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
this.queryText = this.queryText.replace("qREQPRDCDE", params["REQPRDCDE"].value.join("','"));
this.queryText = this.queryText.replace("qREQSRVCDE", params["REQSRVCDE"].value.join("','"));
</pre>. I have an error: "cannot find function <strong class='bbc'>join</strong>".<br />
REQPRDCDE, REQSRVCDE are my parameters.<br />
Please check my attached file!<br />
<br />
For #3 i understand, everything it's very clear but I'm stuck in #2.<br />
<br />
Thanks

The viewer toolbar doesn't embed when you use the viewer tag, so that you can use the export functionality? Sorry, I haven't embedded it in a while, I don't remember if it was there or not.

I'm not sure why the join function isn't working. You could try separating the object manually by stepping through and creating the comma separated string, then replacing the dummy characters in your query with the new string, rather than using "join".

Michael,<br />
<br />
I have another question. I define a variable and i need to assign the value of my query to that variable. I put in <strong class='bbc'>beforeOpen</strong> method of my dataSet the following line <pre class='_prettyXprint _lang-auto _linenums:0'>var sqlQuery = this.queryText;</pre>. But how can i display the value from that variable. I try to put a data binding and add <pre class='_prettyXprint _lang-auto _linenums:0'>vars["sqlQuery"]</pre>, then i try to put <value-of>this.queryText</value-of> but it's not working. I build the where clause in <strong class='bbc'>beforeOpen</strong> method of my dataSet but i can't see the sql. <br />
Please can you help me?<br />
<br />
Thanks,<br />
Nick

Nick,

If you set the variable like:

sqlQuery = this.queryText;

Then, in a dynamic textbox or data element in your report, you put, simply:

sqlQuery;

You should see the query text displayed in your report. You may have to initialize your sqlQuery variable in the report's initialize method to use the variable in the report, I'm not completely sure on that though.

Hello Michael,

Thanks, i have put the code exactly where you say and everything works fine, but i want one more thing,..
how I can hide report table when no data is returned by query? I want to display only a message: "No data was found!"

Thanks,
Nick

Nick,

You can initialize a variable to 0 in your report's initialize script, then increment that variable in your dataSet's onFetch method. You can then check the value of that variable in your visibility for your table and "No data" label, hiding and unhiding the appropriate one for the appropriate value of your variable. Hope this helps.

Hello Michael,<br />
<br />
Thanks for previous answer! It works fine. Now i have another problem. I want to call from my query a function using parameters. It is possible? My query looks like this:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>select Time_Interval,
Value,
GET_RTCOUNT_INTERVAL(char('2010-09-16'),int(1)) as INTERVAL_COUNT
from myTable</pre>
<br />
and i want to replace char('2010-09-16') with ? (? is my parameter). How can I do that?<br />
<br />
Regards,<br />
Nick

Do you need the 'char' designation for the function? Or can you just put in the string date value? You could just do this replacement in your script with the this.queryText.

If you do it the '?' way, you should just be able to replace whatever you need in the query with the '?' as long as the report parameter or binding that you link the parameter to is of the correct type and has all the info you need in it, it should work.

I need to calculate the total of hours as shown in attachment. I am using nested script to display the data.
If you have any ideas, please let me know.

I don't see an attachment in your post. Were you talking about the attachment in a different post?

Hey sorry I had forgotten to click on attach this file button. I have attached the image for the format in which I need to display the data and sum.