[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2016-10-19
12
Medium Priority
?
126 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 16

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 16

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 16

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 16

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 16

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 16

Accepted Solution

by:
Megan Brooks earned 2000 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

Industry Leaders: 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!

Question has a verified solution.

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

In case you ever have to remove a faulty web part from a page , add the following to the end of the page url ?contents=1
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

656 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