Solved

SSRS Total a column based on a calulated field - Report Writer 3.0

Posted on 2016-10-19
12
110 Views
Last Modified: 2016-10-26
Newbie here so go easy.

I am creating a report based off a share point list. I have three columns total. The first two columns are based off of tables. The first column is called Created and the second is Completion_Date. The third column I added as a calculated field and what it does is looks at the dates in the first two columns and gives me the number of days between the two. This column is called Days to complete and has the following expression: =DateDiff("d",Format(Fields!Created.Value,"Short Date"),Format(Fields!Completion_Date.Value,"Short Date")) + 1

So when I run the report I get the following which is correct:

Created               Completion Date          Days To Complete
10/1/2016            10/3/2016                                  3
10/1/2016            10/4/2016                                  4

I also have the report group (don't know if that matters). Here is my issue. I would expect the total for days to complete to total 7 in the example above. I insert a row and on a single cell I put in the following expression [Sum(DaysToComplete)] but when I run the report I receive an error. What am I missing? I have attached a screen shots as well. How do I simply get a total on the days to complete based on it being a calculated field?
screenshot.docx
0
Comment
Question by:DJ P
[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
  • 6
  • 6
12 Comments
 
LVL 15

Expert Comment

by:Megan Brooks
ID: 41851202
If you want to sum DaysToComplete then you need to have a column by that name defined in the dataset. You can add it there as a calculated field, using the same expression that you used to calculate the value on the detail row. Once you do that, the detail row can also be changed reference DaysToComplete instead of embedding the expression.

If you bring up the dataset properties dialog and go to the Fields panel, you can add the calculated field there. Make sure it is positioned after the other fields that it references.
0
 

Author Comment

by:DJ P
ID: 41851847
I have tried and know its operator error on my part due to lack of experience but I cannot get your suggestion to work. I created a calculated field called total days. I gave it a sum expression and when I add it as a column and attempt to run it gives me an error. Please if you could provide step by step instructions on how to create.
0
 

Author Comment

by:DJ P
ID: 41851849
I do have that name defined in the dataset and its still a no go
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:Megan Brooks
ID: 41853245
Adding a calculated field to the dataset makes the RDL more readable and avoids duplicating the expression, but there is something else going on here.

I didn't look closely at your expression earlier, but you are doing a DateDiff on two formatted dates? Try doing the diff on the unformatted date values. I am assuming that Created and Completion_Date are DateTime values. If not, you may need to convert them.
0
 

Author Comment

by:DJ P
ID: 41853599
Megan I appreciate the help but I have no clue what you are talking about in regards to formatted and unformatted dates. As I stated I am beyond a newbie so some of what you are saying is a little over my head at the moment which is why I requested steps. It is pointless to go forward as I am frustrated. I will simply dump this into excel and have it calculated in less than ten seconds.
0
 
LVL 15

Expert Comment

by:Megan Brooks
ID: 41853910
You are calling the Format function, which converts a datetime value to a human-readable formatted text string. I have never tried doing this with DateDiff, and I can't say whether it might work under some circumstances, but I certainly would not recommend it.

I don't have much information to go on at this point, apart from the RDL expression you provided. What does the data source itself look like? You mentioned a SharePoint list, and this is something I have not worked with, but if you can tell me a little more about what you are doing I can figure it out. If you are able to post the RDL file itself, that would be ideal.
0
 
LVL 15

Expert Comment

by:Megan Brooks
ID: 41854039
I'll ask a different way. You have an expression
=DateDiff("d",Format(Fields!Created.Value,"Short Date"),Format(Fields!Completion_Date.Value,"Short Date")) + 1
Why are the two date parameters supplied to DateDiff wrapped in calls to Format? Why not just
=DateDiff("d", Fields!Created.Value, Fields!Completion_Date.Value) + 1
I'm not saying there isn't any reason for using Format; I just don't know what it might be. Did you try the simpler version at first and it failed also? If so, there may be a data type issue with the values returned by the dataset, which is why I asked about that.
0
 

Author Comment

by:DJ P
ID: 41854434
No specific reason I used what I had. I tried your line and still got the error.
0
 
LVL 15

Expert Comment

by:Megan Brooks
ID: 41854529
If the detail row expression [DaysToComplete] displays correctly, assuming that the calculation has been moved to the dataset as a calculated field, then so should [Sum(DaysToComplete)] in the group row. Is that the case?
0
 

Author Comment

by:DJ P
ID: 41856926
See attached. I don't know how to explain this any better.
results.docx
0
 
LVL 15

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 41859595
It would appear that there is a problem with the DaysToComplete column, and not with the expression used on the detail row. Assuming that the DaysToComplete column represents the calculation that you want to be applied in both places, try replacing whatever expression you are using in the detail row (<Expr>) with [DaysToComplete] (=Fields!DaysToComplete.Value) and see if you still see correct values in the detail rows. If you don't then you know that there is a problem with DaysToComplete.
0
 

Author Comment

by:DJ P
ID: 41861279
I figured it out.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 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