Solved

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

Posted on 2016-10-19
12
58 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
  • 6
  • 6
12 Comments
 
LVL 14

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
 
LVL 14

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 14

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

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 14

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 14

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now