?
Solved

calculating field in ssrs

Posted on 2010-08-26
24
Medium Priority
?
477 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:k1ng87
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 7
  • 4
24 Comments
 
LVL 27

Expert Comment

by:planocz
ID: 33538229
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
 
LVL 1

Author Comment

by:k1ng87
ID: 33538374
yes...but what do I use for the expression since my dataset looks like this:

group | entry type | jan | feb | mar | apr | etc...
-----------------------------------------------------------
0
 
LVL 27

Expert Comment

by:planocz
ID: 33538554
group | entry type | jan | feb | mar | apr | etc...
            |  Sale %:   | Exp| Exp| Exp| Exp|etc.
-----------------------------------------------------------

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:k1ng87
ID: 33540948
??
0
 
LVL 1

Author Comment

by:k1ng87
ID: 33540954
oh...under entry type the values are "sales", "cogs", transbilling, and "transcost". then the total spend in the month columns
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33541308
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
 
LVL 1

Author Comment

by:k1ng87
ID: 33542271
get this error:
entr.png
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33545970
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
 
LVL 1

Author Comment

by:k1ng87
ID: 33558933
i placed it in the cirlced box
plcament.png
0
 
LVL 1

Author Comment

by:k1ng87
ID: 33559014
is it possible to reference "fields!group.value" for "my value" in
=sum(iif(Fields!entrytype.Value = "MyValue",Fields!Jan.Value,nothing))
0
 
LVL 27

Expert Comment

by:planocz
ID: 33561428
what is the name or type of group you have for group footer #3 ?
0
 
LVL 1

Author Comment

by:k1ng87
ID: 33561536
don't have a group 3 footer...just group 1 and 2....should've taken a better screenshot
plcament.png
0
 
LVL 27

Expert Comment

by:planocz
ID: 33561818
what formula are you wanting to use to get your percents?
0
 
LVL 1

Author Comment

by:k1ng87
ID: 33562234
transcost/sales for monthly gross as a % to sales

and

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

Expert Comment

by:itcouple
ID: 33564967
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
 
LVL 1

Author Comment

by:k1ng87
ID: 33566951
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
 
LVL 10

Accepted Solution

by:
itcouple earned 2000 total points
ID: 33567499
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
 
LVL 1

Author Comment

by:k1ng87
ID: 33567907
still getting an error
errr.png
lyout.png
0
 
LVL 10

Assisted Solution

by:itcouple
itcouple earned 2000 total points
ID: 33568024
Hi

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

Regards
Emil
0
 
LVL 1

Author Comment

by:k1ng87
ID: 33568044
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
 
LVL 1

Author Comment

by:k1ng87
ID: 33568049
pic
lyout.png
0
 
LVL 10

Assisted Solution

by:itcouple
itcouple earned 2000 total points
ID: 33568272
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
 
LVL 1

Author Comment

by:k1ng87
ID: 33568417
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
 
LVL 10

Assisted Solution

by:itcouple
itcouple earned 2000 total points
ID: 33568455
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

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question