Create structure for Cross Tab

[avvy]

Hi, 

I want to create a cross tab for which the data is in below format :-

State	District	Sub_District	Region_Type	Hired	Non_hired
HR	HR1	HR11	Urban	3	4
HR	HR1	HR11	Rural	2	4
HR	HR1	HR13	Rural	0	0
HR	HR2	HR21	Urban	3	1
…	…	…	…	…	…

Now the cross tab has to create in following format :-

Rural

Urban

Combined

State/District/Sub district(Drillable)

Hired

Non_Hired

Total

Hired

Non_Hired

Total

Hired

Non_Hired

Total

HR/HR1/HR11

3

4

7

2

4

6

5

8

13

….

…

…

…

…

…

…

…

…

…

I am able to do till rural and urban but I am little bit confused how to get the combined(Hired,Non_Hired etc). Can somebody please help me on this.

Thanks & Regards,

Abhishek Pathak

[TejaSurapaneni]

Hello Abhishek..,

At persent it is not possible in frontend, you have to do it in backend...

State	District	Sub_District	Region_Type	Hired	Non_hired
HR	HR1	HR11	Urban	3	4
HR HR	HR1 HR1	HR11 HR11	Rural Combined	2 5	4 8
HR	HR1	HR13	Rural	0	0
HR	HR2	HR21	Urban	3	1

Let me know....

Thanks & Regards,

Teja Surapaneni.

[avvy]

Thanks Teja for the reply.... Even I was thinking to do the same but Bruno solution seems to look good which is quite easy and will not increase load on server by incerasing the size of the data set.

Thanks & Regards,

Abhishek Pathak

[BrunoMueller · Accepted solution]

How about adding row totals in the crosstab, this will give you the numbers, the heading will be Total, see screenshot below.

Bruno

[avvy]

Thank you Bruno fot the prompt reply and solution too....The only way to get the heading of the column as desired is to add up the new rows in the dataset itself which is again a very huge process and makes the table unneccessarly big.

Thanks & Regards,

Abhishek Pathak