Report Studio - using summaries in calculations 1

[GJParker]

I have a profitabilty report where I have summaries at a number of different levels i.e.

Licence 80
Training 75
Third Party 100
Total revenue 255

Charges (40)
Expenses (60)
Total Costs (100)

Gross Margin 155

What I now need to do is calculate the Gross Margin % which is calculated as Gross Margin / Total Revenue (155/255)

Total revenue and total cost use the same summary at a group level, how can I create a new calculation to show my gross margin %

I've been trying the different variations of

total ( expr for expr { , expr } )

to no avail.

anyone got any bright ideas.





Gary Parker
MIS Data Analyst
Manchester, England

 

[MissReportNet]

Hi GJParker,

Insert a calculation and use
total([Gross Margin]for report)
call it total GrossMargin
Then insert a new calculation in expr. def.;
Gross Margin/Total GrossMargin, set data format as percentage.

Good luck!!

MissReportNet

 

[MissReportNet]

Hi GJParker,

I was a little bit to fast
In your case
total([Gross Margin] for report) is enough

Cheers,

MissReportNet

 

[GJParker]

MissReportNet

Thanks for trying to help, although it wasn't th etotal gross margin I was after.

I have found a solution which gives me the Gross Margin% by creating the required formulas which contain a test for the revenue group level.



Gary Parker
MIS Data Analyst
Manchester, England

 

[MissReportNet]

Hi Gary,

Can you share the solution you found, i am very curious!!

Cheers,

MissReportNet

 

[GJParker]

There are a number of groups on the report one of which is [Acc Type]

this is either Revenue or Cost so my test data is this which shows amount totalled at different levels.

                             Amount
Revenue   Licence            80
          Training           75
          Third Party        100
Total revenue                255

Cost      Charges            (40)
          Expenses           (60)
Total Costs                  (100)

Gross Margin                 155 (this is grand total of the group)

To create the %Margin calculation I created a new calculation (Revenue Amount)

Total(if ([Query1].[Acc Type] = 'Revenue')
then ([Query1].[Base Amount])
else (0))

then created a 2nd calculation to calculate the %margin

[Query1].[Amount]/[Revenue Amount]

Hope this makes sense.

Gary Parker
MIS Data Analyst
Manchester, England