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

Cross tab with difference as aggregate

Hi All,
I am trying to create a crosstab but i am having trouble with the detail value
I am reporting on the meter consumption for month and quaters. for my cross tab, i would like the months on the horizontal header, the meter name on the vertical one and the consumption in the detail.

The problem i have is that i only have actual reading values in the database so i need to do lastReading-firstReading to get the consumption and also taking into account when the meters rollover.

In the cross tab builder i only see aggregation to sum up my reading nothing to do a difference and when i try Total.max(row[])-Total.min(row[]) i get nothing

Find more posts tagged with

Comments

Hi magic_bern,

Since difference isn't an option, you'll probably have to replace the summary field in the crosstab with a dynamic text element where you do script to store the last value in a variable to compare to the current value, then display the difference. I use persistentGlobalVariables so that I can name the variables after their row dimension. Let me know if you have questions.

That sounded a bit confusing to me, can you please give me an example of what the script in that dynamic text will look like?
Thanks in advanve!!

Take a look at this devShare example. It's of a "runningSum", but it uses the same idea of keeping track of the last value. I can make a more specific example to "difference" if needed. Let me know!

http://www.birt-exchange.org/org/devshare/designing-birt-reports/1255-birt-creating-your-own-running-sum-in-a-crosstab/

thank you,
i'll let you know how it goes.

Hi MWilliams,
I have been working on that running difference with crosstab and i am unable to tweek your code to give me what i am looking for. i always get some strange results.
here is the problem i am facing. i have attached a sample report highlighting the problem along with a sample data source.
I am using the Eclipse Platform Version: 3.4.2 Build id: M20090211-1700. and BIRT version= 3.2.17

Thanks in advance for your help
PS. how do you set the decimal place for a dynamic text?
...

I changed the script in the text box to do what I think you're wanting. Let me know.

Thanks MWilliams,
Thank you very much for the help.
I am getting the following error about not being able to convert to string
The following items have errors:

ReportDesign (id = 1):
+ There are errors evaluating script "
if (reportContext.getPersistentGlobalVariable(data["metername"]) == null){
temp = data["reading_MeterName/metername_Dates/month"];
reportContext.setPersistentGlobalVariable(data["metername"], data["reading_MeterName/metername_Dates/month"].toString());
}
else{
temp = data["reading_MeterName/metername_Dates/month"] - parseInt(reportContext.getPersistentGlobalVariable(data["metername"]));
reportContext.setPersistentGlobalVariable(data["metername"], data["reading_MeterName/metername_Dates/month"].toString());
}
temp;
":
TypeError: Cannot call method "toString" of null (<inline>#4).

*****
The values being returned are not quite the monthly consumption. for example, the actual heat consumption for february is 1000 - march is 1500 - april is 1500 - May is 2000 (because a roll over occured at 6500)
thanks in advance

I don't believe I got an error. Are you using the same data that I was using? Or are you using different data with null values in it? All you should need to do is wrap the script in a check for null values in your data.

As for having a roll over. You'd just have to figure that out in a little more of a complex script than "y-x". I didn't know your meter specs.

Oh, maybe I did have the error but just didn't scroll down to see it. I forgot the last line of the .txt file had a null reading in it. I'm guessing you won't have null readings in your actual data.

Hi MWilliams,<br />
Thanks again for the reply, i did indeed have a null value on the last line which caused the error.<br />
it's probably a bit frustrating for you, but the script is still not returning the consumption for each month but giving the difference between the running sum of a month and that of the previous month.<br />
with the given data, i should get something like this<br />
Month <strong class='bbc'>2 3 4 5 6 </strong><br />
Heat 1000 1500 1500 2000 1000<br />
Volume 1000 1500 1500 1500 1500 (this last value is by putting the missing 7500 on lastLine)<br />
<br />
my unprogrammer's mind can't quite look at your code, understand it well enough to do the difference between this month last reading and the previous month last reading (which i what i think is required)<br />
<br />
Thanks in advance

Ah, I was looking at it all wrong. I was still adding values and finding the difference. I'll make a quick fix and post tomorrow.

Thank you very much you will save me

Regards

Quick question. Will the first month's reading always be from first reading to last reading and the rest from last reading to last reading? Also, the max is always 6500, then it rolls over?

Hi MWilliams,
The first month consumption will also be the last reading of the first month less the lass reading of the previous month. so the consumption for jan will be Last_Jan_Reading - Last_Dec_Reading.
And yeah for the heat meter the max reading is 6500 and there is a roll over. the rollover value for the other meters is a lot higher and the meter wont be getting to it anytime soon.

Thanks in advance

Check this one out.

Thank you very much MWilliams, you are a legend.
At the moment, the report is giving me what i am looking for so i can produce something.
one improvement that i'll need to work on is the initial date. at the moment we hard code it on the report so at the begining of the year i need to modify it to correspond to the last reading of the previous december.
I will looking at houw to get this value in sql and try to store it in a variable so i dont have to set it everytime. Please let me know if there is an another way to make those initial values dynamic that i can look into.
Also,how do i format of the dynamic text element so that it consistently gives 1 or two decimal places. at the moment it seems to be random and different for each month.

Thanks again for your help.
Berny

You should be able to do something like:

<value-of format="formatString">

As for the last month of last year, if you can access the value in your application before you run the report, you could pass it through as a parameter. Otherwise, you could run a separate dataSet that only takes the last value from last year and store that in a variable to use in your calculations.

Im back again with a follow up from this thread.
Now that i have the consumption i was after i need to have a row below the crosstab giving a percentage between a couple of the reading in the crosstab.
i have a query that returns total_heat and CHP_heat. below it i am looking for a computed field returning how much percentage of the total heat was produced by the CHP.
additionally i have to produce a graph with all those 3 consumptions.

i have create variable on which i assigned the consumption and try to use it to use it to compute percentage but it only computes the last percentage and copies it accross the crosstab. (i suspect that may be because i am using the space usually reserve for totals)
any help would be very welcome.

Can you explain a little further with a simple example?

thanks for the quick reply.<br />
this is how the data is in the database table<br />
<br />
metername<p class='bbc_indent' style='margin-left: 40px;'>reading</p><p class='bbc_indent' style='margin-left: 40px;'>readingdate</p>
chp_heat<p class='bbc_indent' style='margin-left: 40px;'>25</p><p class='bbc_indent' style='margin-left: 40px;'>30/12/10</p>
total_heat<p class='bbc_indent' style='margin-left: 40px;'>80</p><p class='bbc_indent' style='margin-left: 40px;'>30/12/10</p>
chp_heat<p class='bbc_indent' style='margin-left: 40px;'>35</p><p class='bbc_indent' style='margin-left: 40px;'>15/01/11</p>
total_heat<p class='bbc_indent' style='margin-left: 40px;'>90</p><p class='bbc_indent' style='margin-left: 40px;'>15/01/11</p>
chp_heat<p class='bbc_indent' style='margin-left: 40px;'>50</p><p class='bbc_indent' style='margin-left: 40px;'>31/01/11</p>
total_heat<p class='bbc_indent' style='margin-left: 40px;'>105</p><p class='bbc_indent' style='margin-left: 40px;'>31/01/11</p>
total_heat<p class='bbc_indent' style='margin-left: 40px;'>50</p><p class='bbc_indent' style='margin-left: 40px;'>28/02/11</p>
chp_heat<p class='bbc_indent' style='margin-left: 40px;'>120</p><p class='bbc_indent' style='margin-left: 40px;'>28/02/11</p>
...<br />
the aim is to produce a crosstab showing the consumption per month as well as a graph as shown on the picture<br />
<br />
i have managed to get the crosstab to give the consumption based on the example you provided here and on devshare. but struggling to add the extra row for the percentages and the grap<br />
<br />
Thanks in advance

If I'm understanding correctly, the total row should be able to be used for this. Can you use a CSV file of data and create a report from it showing what you're doing?

Hi Mwilliams,
I have attached a sample txt file with the report i have so far.
I am struggling with the percentage row and creating the line graph with the three readings.
I am using BIRT Designer Version 2.3.2.r232_20090202 Build <2.3.2.v20090218-0730.
but will be switching to BIRT designer Pro 11 when i decide to install it on my machine.

Thanks

Take a look at the report now. I set new PGV's in the data element expressions and recalled them in the dynamic text box in the total area to do the calculation. The first one doesn't seem to be correct, but it seems that there's an error with the data in that cell for some reason anyways. The others are all right though.

good thank you very much i will look into that first reading.

how can i now plot them into a line graph. i am not able to access them and when i try to store it in a variable that i sent as 0 in before open i only get the last reading

I have a feeling you'll have get the values into your dataSet to be able to put them in a chart, but I could be wrong. Could try changing the dynamic text I used in the total area to a data binding and see if you use the crosstab as a source for a chart if you can use those values.

i have change the dynamic text to a data element. but i am not able to use the crosstab as a source for the chart.
i have created a table and grouped it by meter then month and got a variable to store the consumption but not able to to calculate the percentage.
i have attached what i have done so far

Thanks

I thought that dataCube was an option for use as a chart's dataSource. Maybe I was wrong or maybe that isn't until a later version than 2.3.2. I'll look into that and into your crosstab issue.

The percentage works for me by removing "per=" from the expression.

Also, if you name your crosstab in the property editor, you can use the report item as the source of your chart. You only have access to the dataCube though, so the bindings you added are not available. You'd have to do the same in the series expressions from the "reading" value as you did from it in the crosstab bindings. You should be able to recall the same PGV's in the chart as were used in the crosstab.

Let me know if you have issues.