SSRS Total a column based on a calulated field - Report Writer 3.0
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(Field
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
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(Field
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
ASKER
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.
ASKER
I do have that name defined in the dataset and its still a no go
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.
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.
ASKER
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.
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.
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.
I'll ask a different way. You have an expression
=DateDiff("d",Format(FieldWhy are the two date parameters supplied to DateDiff wrapped in calls to Format? Why not justs!Created. Value,"Sho rt Date"),Format(Fields!Compl etion_Date .Value,"Sh ort Date")) + 1
=DateDiff("d", Fields!Created.Value, Fields!Completion_Date.ValI'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.ue) + 1
ASKER
No specific reason I used what I had. I tried your line and still got the error.
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?
ASKER
See attached. I don't know how to explain this any better.
results.docx
results.docx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I figured it out.
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.