SSRS calculate on a group total

I have a report where I have the details rolled up into totals grouped on invoice dates.  If you look at the attachd screen shot it will show you the info I'm detailing here.  I have a calculation in the query to take the cost and divide it by sales to give me a percent (column OpPct).  What I need to accomplish is to calculate that percentage on the totals section.  I have a Total Cost (summed) and a Sales totals (summed) - but every thing i've tried in to calculate these 2 columns to give me a percentage on the totals errors out.  I can't sum them using the OpPct column because I want the percentage of the total cost / sales total for the day.   column layout
Please let me know how this can be done.
tgfo4927Asked:
Who is Participating?
 
tgfo4927Author Commented:
I figured this out.  closing question
0
 
tgfo4927Author Commented:
Should also note that when I try to do an expression and choose multiple days to review it gives me an error about reading from the dataset.  the user has the option to choose a start and end date on the report to review.
0
 
planoczCommented:
the calculation in the query  needs to become a paramtere sent back to the report then placed
in the Ops textbox
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
tgfo4927Author Commented:
And how do I do that?
0
 
SThayaTechnical MAnagerCommented:
Can you try this expression


=FormatPrecent(iif(sum(Sales totals)>0,sum(Total Cost )/sum(Sales totals),0),2)
0
 
tgfo4927Author Commented:
I tried the expression as an expression in the text box and as a parameter and continue to get errrors
'Expression' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.

I also had to modify to correct -
=FormatPercent(IIF[Sum(Sales Total)]>0,[Sum(Total Cost )]/[Sum(Sales Total)],0),2)

how did you want me to try the expression?
0
 
SThayaTechnical MAnagerCommented:
i am sorry there is an typo error happened . your modified code is correct ...

Can you please tell still are you getting Error in the Total . if yes share the error screen shot
0
 
tgfo4927Author Commented:
Yes, I am still getting error -
the screenshot is attached. error message
0
 
tgfo4927Author Commented:
Does anyone have an answer on this?  I'm desperately needing help
0
 
tgfo4927Author Commented:
No one was able to help me but after days of trial and error I was able to figure this out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.