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; } } }

Averages in Crosstab instead of SUM?

Sapphie

Hi all

I think I must be missing something obvious (again!). I am new to BIRT and have created a crosstab. The summary data values are the SUMs of the values. I simply want to make this the *average*. After creating the crosstab, I edit that summary field and change it from SUM to AVE but it still shows the SUM.

What am I doing wrong? I am using 2.6.1

Thanks

Lee

Find more posts tagged with

Comments

mwilliams

Hi Lee,

Go into the dataCube you use in your crosstab and edit the summary value in there as well to figure for average, not sum.

Sapphie

Hi Michael

I don't see an option for AVE option in there, only

SUM
MAX
MIN
FIRST
LAST
COUNT
COUNTDISTINCT

Am I looking in the wrong place?

Lee

mwilliams

My mistake. I just assumed it was there as an option. I think I've made an example for this before. Let me look for it. Otherwise, I'll work on trying to figure something out for you in BIRT 2.6.1. You can always request an enhancement for Average to be an option for a crosstab summary field by going to http://www.birt-exchange.org/org/resources/bug-reporting/.

mwilliams

You want the average for each cell, correct? Not for the row or column?

cypherdj

What you can do here is to create 2 measures in the cube: sum and count. The average should be sum/count.

Then in the crosstab, you simply add a data element. This data element will simply use the measures, but you need to apply it for the given groupings.

I'll attach a sample report using BIRT 2.5 and the models database later on.

Rgds,
Cedric

mwilliams

Exactly the report I have for 2.6.1. I put the average in a derived measure column. You'll just have to hide the other columns if you don't want them to show. Still, asking for an average option for crosstab measures wouldn't be a bad idea. If you request it, be sure to post the info in here so others can vote for it!

Sapphie

Thanks guys. I have looked at the sample you have provided and have now replicated this for my report.

This stuff doesn't seem very intuitive to me but I think I am getting there!

Now I want to add an extra column on the right of the crosstab that is the average for the whole year. I mean I have a report hat now calculates the average for each of the 12 months but also need an average column against the whole year. So I need to sum and count for the whole year, not just for each month and then somehow add that as an extra column on the end. I am not sure how to do this ...

Lee

mwilliams

Lee,

You should be able to click on the icon next to your row dimension, select totals, and add the grand totals for your count and your value. Then, you can do the same calculation in the "grand total" area. Hope this helps.

mehow

Hello guys, 
 
I have the same problem as Lee described above, i.e. I needed an average in the data cube and now I need additional column with a total (average) for the whole row. I tried the solution suggested by Mike and it works, but... I don't know how to hide the two measures I don't need (I only need the derived one). The problem is that as soon as I hide it (using 'Show/Hide Measures' option) my Grand Totals column disappears. Also it is not possible to create the this column when the these measures are hidden. 
 
So again, for the data set like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
a | x | 0
a | x | 2
a | y | 3
a | y | 5
a | y | 10
b | x | 4
b | x | 6
b | y | 8
</pre>
 
I need a data cube like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
| x | y | avg
--+---+---+

a | 1 | 6 | 4
b | 5 | 8 | 6
</pre>
 
But right now I have: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
| x | y | avg
+

| | | | | | | | | |
--+

a | 2| 2| 1|18| 3| 6|20| 5| 4|
b |10| 2| 5| 8| 1| 8|18| 3| 6|
</pre>

Sapphie

Instead of using hide measures option, simply select the cell and in the properties editor select the Visibility section and tick the 'Hide Element' box. Gosh, I am even now providing answers!

This works for me. My only remaining issue is that, even with these elements 'hidden' in this way I still get a very small empty cell on my cross tab. For, say, two hidden fields it looks like a faint border appearing down the crosstab to the left of my visible cell. How do we control the visibility of the crosstab border/intersection lines? I don't mean the borders around the individual cells. With tables you can select the whole thing and control the border lines that way. How do we do this with crosstab?

Thanks

Lee

mehow

I have attached an example report based on the Classic Models database. In this example I'd like to display for each product line and country an average price of products from that product line sold to customers from that country. Additionally, for each country I'd like to display an average price of a product sold to the customers from that country. Right now my crosstab displays too much data and I don't know how to hide it. I marked the excessive data in red.

mehow

Lee,

I'm using BIRT 2.5 and I don't have the Visibility section for cells, only for data elements they contain. Probably it's a 2.6 feature? However I found that instead I can set the 'Display' property to 'No Display' (it's in the Advanced section, under 'Section'). The only problem remaining is that while I now have only one column per product line visible, the header cells still span 3 columns hence sticking out of the right side of the table. To solve this I moved the product line data element down from the dimension header to the measure header and I'm now hiding the dimension header instead. See the attached file.

Sapphie

Hmn, maybe I am not sure after all. You can select the actual crosstab 'cell' and set its width to 0 but I can't consistently get that to work either ...

Lee

mwilliams

If you go to the advanced properties of the cell and change the display value under section to "no display" does it work?

Suresh KR

[color=rgb(40,40,40);font-family:helvetica, arial, sans-serif;]Even i am having same issue ,advanced properties of the cell and change the display value under section to "no display" not working. Is any body found solution? Please share.. Using Birt 2.3[/color]

mwilliams

Can you attach a report using the sample database showing your issue? Thanks!