Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
Am working on creating a control check report to reconcile columns of my straight table report.
I have a straight table which has say rows like below (arrows denote indentation i.e. parent/child)
-------------------------------------
Region Sales
-------------------------------------
Maryland 1000
--> Baltimore 800
--> --> Branch A 600
--> --> Branch B 200
--> Columbia 200
--> --> Branch X 180
--> --> Branch Y 20
Maryland 1000 is not expression total but like rest of the Sales figure is coming from DB.
So the check required is on Sales column, to show below as a column in the same straight table :
Baltimore Sales - ( Branch A Sales + Branch B Sales) = 0
Columbia Sales - ( Branch X Sales + Branch Y Sales) = 0
Maryland Sales - ( Baltimore Sales + Columbia Sales) = 0
Where there is a difference, the difference amount will be displayed.
I am able to create the parent child relationship via procedure as below:
----------------------------------------------------------------------------------------------------------------------------
Branch Id Branch Parent Branch Id
----------------------------------------------------------------------------------------------------------------------------
1 Maryland 0
2 Baltimore 1
3 Branch A 2
4 Branch B 2
5 Columbia 1
6 Branch X 5
7 Branch Y 5
----------------------------------------------------------------------------------------------------------------------------
I even tried for example, creating a set analysis variable for branch_id = {3,4} but the moment I place it as a column in the straight table it appears against rows 3 and 4 instead of row 2 which is the expected but not desired behavior. I want it to be for 2 so that I can deduct this column (children total) from the prior column (parent total).
I desire to do this in the same straight table however, am open for other suggestions too.
Any guidance/help is appreciated.
Hi,
Not convinced that this is the most robust solution, as very dependent on load order, but worth a try.
I extended your tables a bit & came up with the below script;