Hello,
I am trying to get this to work - but it only returns minutes & seconds:
Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = (seconds - ss) / 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function
Can anyone help me out? I am not that familiar with VB.
Thanks,
Deb
Hi all.If I've got a query which has a field with seconds in it... how will I usethe Convert function to get my field converted to the format: HH:MM:SS ?The field with the seconds in is called: "Diff"Thanks alotRudi
View 2 Replies View RelatedI'm querying a database table that creates a time stamp in seconds only. I have a starting time of
1099725928 = 11/6/2004 12:25:28 AM. So that if another entry is made 1 second later the time stamp value entered into the table is 1099725929. The front end application does the converstion from the seconds counter to the datetime format. The query I am writing calls information from this table for a different application that does not have the conversion capability. If I know the starting point (I don't want to create a conversion table) which is 1099725928 = 11/6/2004 12:25:28 AM, is it possible to write into my query script a function to convert the seconds value to the correct datetime format? It would need to accurately account for leapyear and even/odd months.
************************************************************************************************
Select Case_ID_, Region, Assigned_To_Group_, Assigned_To_Individual_,
Status, Priority, Category, Type, Item, Affiliate, Hours_to_resolve, Resolved_Time, Assign_Time, Create_Time
From HPD_HelpDesk
Where Region = 'Central Valley'
and Assigned_To_Group_ = 'CVSA Desktop Support'
and Status In (1, 2, 3, 4, 5)
and Priority In (0, 1, 2, 3)
Order by Case_ID_ Desc************************************************************************************************
Hello,
I am attempting to modify an existing matrix report into something a bit more diverse and aesthetically appealing to our users. The problem is changing the size of columns, rows, border types, etc.
So far I have not yet been able to locate any custom code samples that refer to ways of changing the overall appearance and properties of the matrix. I am not concerned with the data but in some cases a column's width will need to be larger or smaller based on the size of the data inside it.
The strings can range from 3 characters to 15 or more and the number of columns can be just as dynamic in number. If that happens I need to shrink or expand a column to match.
I am familiar with using custom code just not in using it to access the matrix properties.
Thanks.
Hello, I am about out of hair from pulling it out. If someone could please show me what I'm doing wrong I would really appreciate it. I have this function for a SSRS 2005 report:
Public Function funAdditions(pFields As Fields) As Double
if pFields !FA00902_AMOUNT.Value > 0 and
pFields !FA00902_TRANSACCTTYPE.Value = 3 and
pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and
pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and NOT
instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1
then return pFields !FA00902_AMOUNT.Value
end
else if pFields !FA00902_TRANSACCTTYPE.Value = 3 and
pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and
pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and
instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1
then return pFields !FA00902_AMOUNT.Value
end
End Function
...and after much research cannot figure out the solution to this error:
[rsCompilerErrorInCode] There is an error on line 1 of custom code: [BC30201] Expression expected.
I'm new to SSRS so is there a syntax error I'm missing?
Thanks in advance,
Buster
I want to display Days Hours Mins Format.
I am Having two columns Like below,
Col1 (in days) col2 (In Hours : Mins)
3days 4:5
In this first have to add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5
From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.
Hi There,
I would like to find the sum of a column with a date format of '01:10:10' which is the hours:minutes:seconds from multiple rows.
For instance, "01:50:10" + "01:20:5" = "3:10:15"
Any ideas?
Using SQL 2000
Hi Guys,
I have data in minutes which is integer value ex 14454.I wanto convert it to days,hours,minutes & seconds.
Is there any simple way?
Regards.
Hi All,
I want to get the date Difference with Day,hours,minutes,seconds of the given 2 dates.(Say the difference of
12/6/2007 7:00:00 AM, 12/8/2007 8:00:00 AM as 2 days 1:00:00)
Is there any inbuilt function in SSRS is available to implement this. Or any other way to do this.Please help me with this .Thanks in advance.
With Thanks
M.Mahendra
In BIDS, I created a time dimension using the wizard. Unfortunately, the template used by the wizard does not contain Hours, Minutes and Seconds; so, I added them using the designer. Now, when I try to build, deploy and process I get the error: Unable to find matching TimeAttributeType. Cannot anyone explain why this is happening and what can be done about? Is there a template available somewhere that does create time dimensions with time and not just dates?
View 5 Replies View RelatedGiven the two datetimes below, what's the best way to obtain the total duration in hours, minutes and seconds (HH:mm:ss)?
Start Time: 2014-03-02 20:55:00.000
End Time: 2014-03-03 07:00:00.000
Duration = 10:05:00
I found in another forum that if I take the seconds and divide them by 15 then round up and multiply them by 4 I can get this done, but I can't figure out how to work it into my select statement. Anyhelp would be greatly appreciated. dbo.SLPTRANS.TimeSpent is the field I am trying to convert.
SELECT dbo.SLPTRANS.ClientID, SUM(dbo.SLPTRANS.TransValue) AS Expr1, dbo.SLPTRANS.TimeSpent AS Expr2
FROM dbo.SLPTRANS INNER JOIN
dbo.INVOICE ON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordID
GROUP BY dbo.SLPTRANS.ClientID
HAVING (dbo.SLPTRANS.ClientID = 405)
I have the following SQL query where i want thease to be populate to GridView, but the Duration field is in Second format, I want it would be in HH:MM:SS format.
cmd = "select subscriber_id as Subscriber_no,,amount,duration from MyTable" ;
Please help me how to format this within the Query to display in GridView.
I have a column of data that is the number of seconds. I need to format this column into the HH:MMS format. If there are 130 seconds the second column should read 00:02:10. How can I accomplish this in report builder or is it even possible in Report builder? I can use the following formula in SQL but is there a way to do it in Report Builder?
CONVERT(varchar(6), talktime / 3600) + ':' + RIGHT('0' + CONVERT(varchar(2),
talktime % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), talktime % 60), 2)
Thanks
can any one tell me how to change seconds to minutes by using if condition in rss...
for example i have value of seconds=50, minutes should come like this 50/60=0.833333.... i got the answer ... but the problem is how to roundoff or truncate the minutes to 0.83 or 0.84 from 0.8333333...
Thanks in anticipation
raj
Please consider enhancing SQL 2005 Reporting Services to provide the ability to disable Clickthrough Reports (Live Links) at the Report and Export Function level. Today you can only enable or disable Clickthrough Reports at the View/Model Level for all Report Builder reports.
View 1 Replies View Relatedi have a quite strange condition...when i add some value in database with getdate() function it only returns date and minute not the seconds...does somebody have an experience about this
View 3 Replies View Related
Hi all,
Could someone tell me if custom code function can capture the event caused by a user? For example, onclick event on the rendered report?
Also, can custom code function alter the parameters of the report, or refresh the report?
Thanks.
Hello,I need to create a column that will store hours bigger than 24. Forexample '25:00:00', '129:23:12', etc).That column will be used too, for perform calculations betweendatetime intervals: 'time'.In MySQL there is a datatype that perfect fits that necessity.Does anyone know what is the corresponding datatype in SQL Server?Thanks a lot!
View 4 Replies View RelatedI'm developing one leave application form in that I've declared "No of days"column as nvarchar type..If the user taking half(1/2) day leave then have to covert that 1/2 into 4 hours itseems..since I'm trying to display the table data on datagridview while displaying that half day in gridview again it has to conver 4 hours into 1.2 format..
My Table design:
Create table Leave_Form
(
Employee_id int primary key,
Emp_Date date,
Emp_name nvarchar(50),
[Code] ....
Windows form:
I have a data file listing open and close hours for the day of the week. I need to format it like
Mon-Thurs 7:00 AM - 5:00 PM
Fri 8:00 AM - 5:00 PM
Sat 8:00 AM - 1:00 PM
Sun Closed
Here is what the data looks like now.
Mon OpenMon ClosedTue OpenTue ClosedWed OpenWed ClosedThu OpenThu ClosedFri OpenFri ClosedSat OpenSat ClosedSun OpenSun Closed
7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PMClosed Closed
7:00 AM6:00 PM7:00 AM6:00 PM7:00 AM7:00 PM7:00 AM6:00 PM7:00 AM6:00 PM8:00 AM5:00 PMClosed
There seems like I would end up 5 lines max?
Hello,
I have this expression DATEDIFF(HOUR, startdate,enddate) which only shows the hours. I need to show the hours and minutes too , exp. 9.17. Any way to convert the expression to do this. The startdate and enddate fields are mm/dd/yyy hh:mms. I am using report builder.
Can anyone help me with this, I need to search for all records from the current date - 72 hours. How can this be done?
Thanks,
www.myspace.com/drew22299
Is it possible to convert for the following SQL statement into SSRS Expression:
SELECT
RIGHT('00' + CONVERT(VARCHAR(2), FLOOR(SUM(Hours))), 2)
+ ':' + RIGHT('00' + CONVERT(VARCHAR(2), FLOOR((SUM(Hours) - FLOOR(SUM(Hours))) * 60)), 2) + ':' + RIGHT('00' + CONVERT(VARCHAR(2),
FLOOR((SUM(Hours) - FLOOR(SUM(Hours))) * 60 - FLOOR((SUM(Hours) - FLOOR(SUM(Hours))) * 60)) * 60), 2)
FROM TableTime
For example I need SSRS expression for converting 1.75 hours into 01:45:00.
is it possible to use the window functions to count the number of rows in the previous 24hours from the current row.
I have a table of events like:
User, TimeStamp, etc...
I want to identify the row which is the event number 50 in the past 24 hours.
does the window functions can do this? and how?
the ROW PRECEDING etc... appear to not have enough feature to support time related function.
Stream-insight is able to create this type of query, but I have to do it directly in SQL.
I am trying to use custom code on a report which is:
Public Function Percentage(decValueOne As Double, decValueTwo As Double) As Object
If (decValueOne = 0 Or decValueTwo = 0) Then
Percentage = 0
Else
Percentage = (decValueOne - decValueTwo)
End If
End Function
In my textbox I have:
=Code.Percentage(SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_Sales"), SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_CGS"))
When I run the report in design mode, I get the "#Error" on that report field.
Any help with why this is happening would be greatly appreciated!
Hey guys I have a problem I am trying to solve using custom code but I am open to other solutions.
Situation: I have multiple reports that the user can see. Example (Move In Corp. report, Move In Region report,Move In Division report, Mid Year Corp. report, Mid Year Region report,Mid Year Division report etc.) I have a stored procedure that returns all the information needed based upon the UserID being passed from the report to the stored procedure.
Goal: When the user logs in I want to be able to foward the user to a specific report based upon their userid (global in RS). I currently have three textboxes(Move In, Mid Year, Year End) with navigation enabled to jump to another report. The report to which the user is passed to is determined by an expression which is something like this for the Move In text box =IIF(Fields!orgtypeid.Value = 1,"Move In Corp","Move In Region"). The orgtypeid.value is returned by the stored procedure. I want to be able to forward the user a specific Move In report (Move In Corp, Move In Region, etc) without having the user select the textbox.
Any suggestions are greatly appreciated.
Hello, this is probably a pretty easy one. I have made a report, and added custom code in the code tab on the report properties. But when I type code.whatever in a text box expression my function does not show. Does anyone know what I am doing wrong?
Thanks
I am trying to find ways around the limitation of not being able to resize columns based on the text input. I am dynamically setting what values a column is getting from my query. I am wondering if I can access the table (and thus, the columns) from within the custom code.
For example:
Public Function Whynot() As Integer
Report.table1.TableColumn1.Width = ....
table1.TableColumn.Width = ....
Return 0
End Function
Neither of these seem to work, anyone else have any ideas?
Thanks
Is it possible to do a redirect to an arbitrary page in by using custom code?
eg.
Public sub myRedirect()
response.redirect(http://www.microsoft.com)
End sub
/Alex
I developed a ConnectionString assembly in custom code a while back. Now it won't work. I've changed boxes since. My question is do I have to have SQL Server and Reporting Services on my development machine to run Custom Code assemblies?
I put my dll in the PublicAssemblies folder. I see that I need to put my dll in C:Program FilesMicrosoft SQL Server80ToolsReport Designer but I don't have that folder.
When I try and set the ConnectionString to "=ReportingLibrary.ReportingLibraryFunctions.ConnectionString(User!UserID)", I get "Unrecognized Identifier." I checked the reference and it was still there from when the project was working before.
Code Snippet
using System;
using System.Security.Permissions;
using System.Configuration;
namespace ReportingLibrary
{
public class ReportingLibraryFunctions
{
public static string ConnectionString(string userName)
{
System.Data.SqlClient.SqlClientPermission oPerm = new System.Data.SqlClient.SqlClientPermission(PermissionState.Unrestricted);
oPerm.Assert();
string catalog;
string query;
string connectstring;
string datasrc = String.Format("Data Source={0};", ConfigurationManager.AppSettings["DataSource"]);
string userpwd = String.Format("User ID={0};Password={1};", ConfigurationManager.AppSettings["DBUsr"], ConfigurationManager.AppSettings["DBPwd"]);
query = "SELECT TOP 1 MyAcctKey FROM dbo.MyUserAcct WHERE UserName=@username;";
connectstring = String.Format("{0}Initial Catalog=MyMaster;", datasrc);
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(String.Format("{0}{1}", datasrc, userpwd));
connection.Open();
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(query, connection);
command.Parameters.Add("@username", System.Data.SqlDbType.VarChar);
command.Parameters["@username"].Value = userName;
catalog = String.Format("MyDb_{0};", command.ExecuteScalar());
connectstring = String.Format("{0}Initial Catalog={1}", datasrc, catalog);
connection.Close();
return connectstring;
}
}
}
I have been stuck in a problem for a while now. I am trying to count the number of groups. I tried soo many things and nothing worked . Now I want to try to write a function that does the counting for me.
Can somebody write me an example of a cutom code that can be called from expression.
So my custome code should be something like that
Public int CountFun(int counter)
{
Return counter= counter +1;
}
Also how do I call this method from my expression?
i am going to call it from a field inside the groups, so each time the group is implemented i add one to the counter.
thanks
Is it not possible to have nested ifs in a custom code function? I keep getting an error message when I try it.
View 1 Replies View Related