Solved

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

Posted on 2016-10-19
12
38 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 13

Expert Comment

by:Megan Brooks
Comment Utility
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
Comment Utility
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
Comment Utility
I do have that name defined in the dataset and its still a no go
0
 
LVL 13

Expert Comment

by:Megan Brooks
Comment Utility
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
Comment Utility
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 13

Expert Comment

by:Megan Brooks
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 13

Expert Comment

by:Megan Brooks
Comment Utility
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
Comment Utility
No specific reason I used what I had. I tried your line and still got the error.
0
 
LVL 13

Expert Comment

by:Megan Brooks
Comment Utility
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
Comment Utility
See attached. I don't know how to explain this any better.
results.docx
0
 
LVL 13

Accepted Solution

by:
Megan Brooks earned 500 total points
Comment Utility
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
Comment Utility
I figured it out.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this article I will describe the Copy Database Wizard 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

16 Experts available now in Live!

Get 1:1 Help Now