May 29, 2016 at 2:44 pm
Hi Im not sure im in the right place here but wonder if someone can help me please?
I have just started using reports from a database system at work and have very little knowledge.
In the data base I have 3 fields of hours and 3 fields of minuets I would like to to add.
Using the code below I can convert the hours to minuets and add them all. It produces an excel spreadsheet with the total amount of minuets. Instead of the minute totals I would prefer to be able to have a column display the total hours and minuets together.
(NVL([TASK_BILLABLE_HOURS],0)*60)+NVL([TASK_BILLABLE_MINS],0)+(NVL([TASK_BILLABLE_TRAVEL_HOURS],0)*60)+NVL([TASK_BILLABLE_TRAVEL_MINS],0)+ (NVL([TASK_BILLABLE_BREAK_HOURS],0)*60)+NVL([TASK_BILLABLE_BREAK_MINS],0)
Any help would be appreciated
Thanks
May 30, 2016 at 9:29 am
Not certain I understand what you mean.
If you want to construct an extra column in a tablix, just add the column then right-click and select Expression. (as you would if you wanted to set the format of a column).
In the Expression window, create your formula:
=Fix(Fields!TASK_BILLABLE_HOURS.Value) * 60 + ...
I used [font="Courier New"]Fix ()[/font] because I assumed (!) that in your code [font="Courier New"]NVL[/font] extracts the integer part - could not find it in Excel 2013.
You will have to use SSRS-supported functions so you will need to "translate" your code.
I you are more comfortable with T-SQL, you could create the extra column in T-SQL using the equivalent math on your columns (if SQL Server is your datasource).
Hope this helps.
May 30, 2016 at 11:46 am
Thanks but I don't want an extra column. I would like to convert the total minuets to hours and minuets instead
May 30, 2016 at 11:53 am
You will need a column to display the results.
It does not have to be an "extra" column in the tablix, you can use an existing column, if it does not display what you want and instead of selecting one of the fields in the dataset, create an expression that does the calculation you need.
Or you can do the calculation in T-SQL if your datasource is a table, view or stored proc on an SQL Server database.
Beyond this, I do not have enough info to help you further along. I can only suggest you set up a dummy report to gain experience with using expressions in an SSRS report and take it from there.
May 30, 2016 at 1:07 pm
Are you using SQL Server Reporting Services? Going by the NVL you are using oracle or informix or similar.
May 30, 2016 at 1:09 pm
Did not think of this possibility. I just considered this was posted in the SSRS forum ...
May 30, 2016 at 1:29 pm
Im not sure if its oracle or not. The formula I copied from another report and has got me most of the way there. It outputs to a excel spreadsheet in minuets but I wanted hours and minuets. Im not even sure if this is the right place to post this
May 30, 2016 at 1:33 pm
I suggest you look at your datasource definition. This will tell you where the data is coming from.
May 30, 2016 at 3:05 pm
Ok Will do thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply