calculating field in ssrs

I have a dataset which brings in sales, transcast, transbilling, and cogs in by group. I want to calculate net spend as a percent to sales and gross spend as a percent to sales. but can't seem to figure it out since sales, cost, transbilling, and cogs are all in one column...

 previewcalc2.png
LVL 1
k1ng87Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
itcoupleConnect With a Mentor Commented:
Hi
IF I understand correctly then this:
Monthly Gross as a % to sales =sum(iif(Fields!entrytype.Value = "TransCost",Fields!Jan.Value,nothing))/
sum(iif(Fields!entrytype.Value = "Sales",Fields!Jan.Value,nothing))

net as a % to sales = (sum(iif(Fields!entrytype.Value = "Transcost",Fields!Jan.Value,nothing))-sum(iif(Fields!entrytype.Value = "transbilling",Fields!Jan.Value,nothing)))/sum(iif(Fields!entrytype.Value = "Sales",Fields!Jan.Value,nothing))

and this I think should be place in first group (next to AIG in your first screenshot)

Hope that helps
Regards
Emil
0
 
planoczCommented:
Try.....
Just right click on the 2 block and add a row in your row add a entry type label like % sales then in the next column add your expression code for % sales.
0
 
k1ng87Author Commented:
yes...but what do I use for the expression since my dataset looks like this:

group | entry type | jan | feb | mar | apr | etc...
-----------------------------------------------------------
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
planoczCommented:
group | entry type | jan | feb | mar | apr | etc...
            |  Sale %:   | Exp| Exp| Exp| Exp|etc.
-----------------------------------------------------------

0
 
k1ng87Author Commented:
??
0
 
k1ng87Author Commented:
oh...under entry type the values are "sales", "cogs", transbilling, and "transcost". then the total spend in the month columns
0
 
itcoupleCommented:
Hi
Thanks for the link from previous question.

I would try this:
=sum(iif(Fields!entrytype.Value = "MyValue",Fields!Jan.Value,nothing))

Should work

Regards
Emil
0
 
k1ng87Author Commented:
get this error:
entr.png
0
 
itcoupleCommented:
Hi

This has to be placed in the right place to work. Can you send a screenshot of where it is on your report?

Regards
Emil
0
 
k1ng87Author Commented:
i placed it in the cirlced box
plcament.png
0
 
k1ng87Author Commented:
is it possible to reference "fields!group.value" for "my value" in
=sum(iif(Fields!entrytype.Value = "MyValue",Fields!Jan.Value,nothing))
0
 
planoczCommented:
what is the name or type of group you have for group footer #3 ?
0
 
k1ng87Author Commented:
don't have a group 3 footer...just group 1 and 2....should've taken a better screenshot
plcament.png
0
 
planoczCommented:
what formula are you wanting to use to get your percents?
0
 
k1ng87Author Commented:
transcost/sales for monthly gross as a % to sales

and

(transcost - transbilling)/sales for net as a % to sales
0
 
itcoupleCommented:
Hi

You are using group 2 to display data so maybe it can't be placed there, try group above (group 1)just to see if that works. I know it might not be ideal.

Regards
Emil
0
 
k1ng87Author Commented:
what equation do i use though...the first issue is figuring out what equation to use...


transcost/sales for monthly gross as a % to sales

and

(transcost - transbilling)/sales for net as a % to sales

how owuld I write these out in ssrs.....

transcost, transbilling, and sales are in the same field....they just get grouped....but how do I calculate specific to one of those?
0
 
k1ng87Author Commented:
still getting an error
errr.png
lyout.png
0
 
itcoupleConnect With a Mentor Commented:
Hi

The layout shows slightly different grouping then before? Which fields are used for grouping?

Regards
Emil
0
 
k1ng87Author Commented:
sorry about that...i edited it alittle....there is only one group now and it groups the field called "group"....the detail section groups this field =Fields!Entry_type.Value
0
 
k1ng87Author Commented:
pic
lyout.png
0
 
itcoupleConnect With a Mentor Commented:
Hmmmm

I must admit I have tested it on 2008. And I just tried it with different places and it works fine everywhere I place it. The only time I get the error you have is when I mispell the field name or use different case for instance my field is entrytype if I change it to EntryType I get the same error as you.

I hope that is the issue in your case because I run out of options :)

Regards
Emil
0
 
k1ng87Author Commented:
i think we're getting closer...it looks like the expressions were case sensitive (using visual 2005) but now all it get are zeros...
lyout.png
design.png
0
 
itcoupleConnect With a Mentor Commented:
If you have the entire expression then split it into separate cells and see which ones don't work. Check if entry type contain spaces (or is case sensitive).

Regards
Emil
0
All Courses

From novice to tech pro — start learning today.