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

How to add differences in cross tab

spulapalli

Hello,

I have created a crosstab report which generates dynamic row dimesionss. Also I need to add differences between columns in report. Please suggest me how to achieve this?

I have attached the developed report. I need to generate the report exactly look like in the attached excel.
Please help me to achieve this? crosstab is not mandatory, also appreciated if any other approach to build this kind of report.

Regards,
Suresh

Find more posts tagged with

Comments

JaVas

Could you please explain it little more clear??

spulapalli

I have attached a report and excel sheet. excel sheet is my prototype final version.

I need to generate a report look like an excel sheet, my report should show the o/p similar to excel.
if you look at excel sheet there are two columns with difference. how can i get these kind of difference columns in birt report.

Please let me know if you need more details.

Regards,
Suresh

Hans_vd

Hi Suresh,

If you have a crosstab you can do this:
Select the crosstab, go to the properties Editor and choose the column area tab: add a total column (by default it will be a SUM column). Now you can change the expression and the function to your need.

Regards
Hans

spulapalli

thank you Hans,

with scripting I am able to get the difference, If i have only 2 values for column dimension. but column dimension(product line) can have more than two also, in this case how can i add difference columns dynamically to crosstab. I have given sample excel how final version looks like, please refer the sample excel.

Regards,
Suresh

Hans_vd

Hi Suresh,

I took a look at the excel, but I don't see the logic.
Why do you make the difference between Classic Cars and Motorcycles and between Classic Cars and Planes but not between Motorcycles and Planes and not between Planes and Classic Cars and so on...

Please, tell me what the logic is you want to implement.

Regards
Hans

spulapalli

Hans,

I just created sample report to explain what I want to achieve. here is my business logic requirement.

select sum(a.value),sum(a.value1),region,state,city,id from table1 a group by region,state,id where id='base_run'
union all
select sum(b.value),sum(b.value1),region,state,city,id from table1 b group by region,state,id where id='child_run'
.
.
etc...

I have an input parameter where users can pass multiple values for id with "," seperated. on Dataset I am splitting string with "," and storing in array. based on the array length I am generating the query
ex: if array length is three base_run,child_run and child_run1

my final query will looks like in report.

select sum(a.value),sum(a.value1),region,state,city,id from table1 a group by region,state,id where id='base_run'
union all
select sum(b.value),sum(b.value1),region,state,city,id from table1 b group by region,state,id where id='child_run'
union all
select sum(c.value),sum(c.value1),region,state,city,id from table1 c group by region,state,id where id='child_run1'

in out put report I wanted to find the differences between base run and child runs i.e
base_run-child_run baser_run-child_run1

Regards,
Suresh

Hans_vd

Hi Suresh, 
 
This is a tough one. 
I couldn't come up with anything else then this modified SQL, that introduces a view calld base_row (constructed in the with clause) and a new field in your query (diff), that you can use as a summary field in the crosstab. 
 
I haven't tested it, but I think this can work: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
WITH base_row AS (
SELECT sum (base.value) base_sum
FROM table1 base
WHERE base.id = 'base_run'
)
select sum(a.value),sum(a.value1),0 as diff,region,state,city,id from table1 a, base_row base group by region,state,id where id='base_run'
union all
select sum(b.value),sum(b.value1),sum(b.value) - base.base_sum as diff,region,state,city,id from table1 b, base_row basegroup by region,state,id where id='child_run'
union all
select sum(c.value),sum(c.value1),sum(c.value) - base.base_sum as diff,region,state,city,id from table1 c, base_row base group by region,state,id where id='child_run1'
</pre>
 
Hope it helps 
Hans