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

crosstab formating

Srijib Mandal

I am doing a crosstab report attach is the excel data and format i am looking. I have managed to add the columns as the month-year and rows as category and promotion but what i am looking is to show category and promotion in same column with slight indentation see image.
 
I am also struggling in shortening the month name and showing a merged cell for 'monthly actual' as in the image. How do i add additional column like the Target that only varies across catagory
 
How do i add a calculated column to show MoM, I know there is a grand total option but it does not have difference option. I want MoM to be difference of the last two month. and do a conditional formatting to indicate RAG.
 
How do i show last three months for the selected month ? Is there any easy solution ? 
 
Any sort of help will be great as i am new to BIRT
<div> </div>

Find more posts tagged with

Comments

shamo

I am not sure I'm understanding you. Can you elaborate further?

Srijib Mandal

I am trying to achieve something like as shown in the image. in the left most column dimensions are shown in hierarchy form. .
Category > promotion-type 
Normally we add two levels which shows something like this
+++++++++++++++++++++++++++++++++++++
+Catagoty1 + promotiontype1
+Catagoty1 + promotiontype2
+Catagoty1 + promotiontype3
+Catagoty2 + promotiontype4
+Catagoty2 + promotiontype5 
+Catagoty2 + promotiontype3
+++++++++++++++++++++++++++++++++++++
However I want category and promotion type in single column but with slight indentation like a tree.
 
I want to show only 3 months from the selected month(parameter passed by the user)
 
MoM change: I have seen a function called grand total but it add up row element across columns but i want a difference between last to months and do a conditional formatting like red, amber, green ?

Srijib Mandal

Anybody please ??

JFreeman

What version of BIRT are you using?

PaulCooper

I have a report I originally developed for one of our customers that did something similar. I had to push everything back to the SQL statement to create a difference column of the last two periods. (Ours had a parameter for choice of period type and number of periods to show) For this I had to create a dummy sort order date column and a display column. The SQL returned aggregation of the data with start of each period for the dummy sort column and a date range in text form for the display column. This aggregation was done this way rather than getting BIRT to do that for me. Then I did some data calculations in Javascript in the reports Initialize script to calculate the the start of period of the last two columns and used those in SQL as parameters in a UNION ALL section of the SQL that returned the last column as a positive amount and the penultimate column as a negative amount that then get summed to together with a dummy sort column of the next period start date after the last one in the data and a display column of something like "Month on Month". The crosstab then was top grouped by the dummy sort column and then the display column with the dummy sort column field hidden. The body would contain a SUM aggregate (I would have preferred FIRST if it was supported as method aggregation is done via SQL so you only have one record per left/top grouping combinations (cell).)
 
Via this dummy sort column method you can create further columns this way for other purposes.
 
As to the indented left groupings you can do this by adding both levels as grouping to the crosstab then move the lower level field into the upper level field cell leave the lower field cell empty. (I believe you need the two grouping fields as levels of the same grouping hierarchy rather than seperate groupings in the cube. I will check this and get back) Now that both left grouping fields are in the same cell you can give the lower level field a left margin that indents it.
 
I also added an additonal field to the body of cell that was hidden unless the top dummy sort grouping equalled the extra, and now final, date so it only appeared in the "MoM" column. This field was either the â–², â–¶ or â–¼character depending on the numeric value in that cell. Highlighting Rules were also applied to colour this character.
 
Regards,
 
Paul