I am trying to work out datediff for just mon-fri between datefiel

I am trying to find the number of working days between two date fields.
I have datediff(dd, first date, second date) as turnaround time,
But this includes saturdays and sundays in the output. I need to find the
number of days, only including monday to friday, between the two date fields.
It helps to have a Calendar table in your database:
CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY,
workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')
Populate it with as many years as you'll ever need:
INSERT INTO Calendar (caldate) VALUES ('20000101')
WHILE (SELECT MAX(caldate) FROM Calendar)<'21001231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar
Set the non-working days:
UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')
You'll probably want to record any public holidays in the same way.
Now you can easily compute the number of working days between two dates:
SELECT COUNT(*)
FROM Calendar
WHERE caldate BETWEEN @.first_date AND @.second_date
AND workingday = 'Y' ;
David Portas
SQL Server MVP
|||Hi Shaun,
Check if this helps...
SELECT DATEDIFF(dd, 'startdate', 'enddate')
- DATEDIFF(ww, 'startdate', 'enddate') *2
Thanks
Yogish

I am trying to work out datediff for just mon-fri between datefiel

I am trying to find the number of working days between two date fields.
I have datediff(dd, first date, second date) as turnaround time,
But this includes saturdays and sundays in the output. I need to find the
number of days, only including monday to friday, between the two date fields
.It helps to have a Calendar table in your database:
CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY,
workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')
Populate it with as many years as you'll ever need:
INSERT INTO Calendar (caldate) VALUES ('20000101')
WHILE (SELECT MAX(caldate) FROM Calendar)<'21001231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate)
,
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar
Set the non-working days:
UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')
You'll probably want to record any public holidays in the same way.
Now you can easily compute the number of working days between two dates:
SELECT COUNT(*)
FROM Calendar
WHERE caldate BETWEEN @.first_date AND @.second_date
AND workingday = 'Y' ;
David Portas
SQL Server MVP
--|||Hi Shaun,
Check if this helps...
SELECT DATEDIFF(dd, 'startdate', 'enddate')
- DATEDIFF(ww, 'startdate', 'enddate') *2
Thanks
Yogish

I am trying to work out datediff for just mon-fri between datefiel

I am trying to find the number of working days between two date fields.
I have datediff(dd, first date, second date) as turnaround time,
But this includes saturdays and sundays in the output. I need to find the
number of days, only including monday to friday, between the two date fields.It helps to have a Calendar table in your database:
CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY,
workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')
Populate it with as many years as you'll ever need:
INSERT INTO Calendar (caldate) VALUES ('20000101')
WHILE (SELECT MAX(caldate) FROM Calendar)<'21001231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar
Set the non-working days:
UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')
You'll probably want to record any public holidays in the same way.
Now you can easily compute the number of working days between two dates:
SELECT COUNT(*)
FROM Calendar
WHERE caldate BETWEEN @.first_date AND @.second_date
AND workingday = 'Y' ;
--
David Portas
SQL Server MVP
--|||Hi Shaun,
Check if this helps...
SELECT DATEDIFF(dd, 'startdate', 'enddate')
- DATEDIFF(ww, 'startdate', 'enddate') *2
--
Thanks
Yogish

Hyperlinking to another report

Im trying to create a link to another report from one of my fields (Budget_Reporting_Group_Description) but i only want the hyperlink to be visible and clickable for one value (Actual), how would i write out the code and where would i put it in the properties box for navigation. The report i want to link to is called Details.

I have this so far:

=Iif(Fields!Budget_Reporting_Group_Id.Value = "Actual", "Details", Nothing)

whats wrong?

Maybe you're not putting it in the correct place. The expression has to be placed in the expression for "Jump to report" under textbox properties -> Navigation tab.

-Aayush

|||

Hello,

Put this in the expression for your field, and in the "Jump To Report" of the navigation properties.

=Iif(Fields!Budget_Reporting_Group_Id.Value = "Actual", "Details", Nothing)

Now, if the Id is "Actual", then you will see "Details" in the textbox and it will be clickable, which will take you to your Details report. If it's not "Actual", then the textbox will be blank, with no hyperlink.

Hope this helps.

Jarret

|||I have it in the jump to report Expression box. But its not showing a hyperlink when i roll the mouse of the field. Is there another expression, or something to link it to another report. I just want the report to show a hyperlink if the value in the field is "Forecast" if not, it should not have a link to the other report.
|||thanks alot, i figure out what it was. I put the code in the jump to report, and changed the field from ID to Description, i made the mistake i putting ID , when i needed Description. Thanks for the help!

Hyperlinking to another report

Im trying to create a link to another report from one of my fields (Budget_Reporting_Group_Description) but i only want the hyperlink to be visible and clickable for one value (Actual), how would i write out the code and where would i put it in the properties box for navigation. The report i want to link to is called Details.

I have this so far:

=Iif(Fields!Budget_Reporting_Group_Id.Value = "Actual", "Details", Nothing)

whats wrong?

Maybe you're not putting it in the correct place. The expression has to be placed in the expression for "Jump to report" under textbox properties -> Navigation tab.

-Aayush

|||

Hello,

Put this in the expression for your field, and in the "Jump To Report" of the navigation properties.

=Iif(Fields!Budget_Reporting_Group_Id.Value = "Actual", "Details", Nothing)

Now, if the Id is "Actual", then you will see "Details" in the textbox and it will be clickable, which will take you to your Details report. If it's not "Actual", then the textbox will be blank, with no hyperlink.

Hope this helps.

Jarret

|||I have it in the jump to report Expression box. But its not showing a hyperlink when i roll the mouse of the field. Is there another expression, or something to link it to another report. I just want the report to show a hyperlink if the value in the field is "Forecast" if not, it should not have a link to the other report.
|||thanks alot, i figure out what it was. I put the code in the jump to report, and changed the field from ID to Description, i made the mistake i putting ID , when i needed Description. Thanks for the help!

Hyperlinking to another report

Im trying to create a link to another report from one of my fields (Budget_Reporting_Group_Description) but i only want the hyperlink to be visible and clickable for one value (Actual), how would i write out the code and where would i put it in the properties box for navigation. The report i want to link to is called Details.

I have this so far:

=Iif(Fields!Budget_Reporting_Group_Id.Value = "Actual", "Details", Nothing)

whats wrong?

Maybe you're not putting it in the correct place. The expression has to be placed in the expression for "Jump to report" under textbox properties -> Navigation tab.

-Aayush

|||

Hello,

Put this in the expression for your field, and in the "Jump To Report" of the navigation properties.

=Iif(Fields!Budget_Reporting_Group_Id.Value = "Actual", "Details", Nothing)

Now, if the Id is "Actual", then you will see "Details" in the textbox and it will be clickable, which will take you to your Details report. If it's not "Actual", then the textbox will be blank, with no hyperlink.

Hope this helps.

Jarret

|||I have it in the jump to report Expression box. But its not showing a hyperlink when i roll the mouse of the field. Is there another expression, or something to link it to another report. I just want the report to show a hyperlink if the value in the field is "Forecast" if not, it should not have a link to the other report.
|||thanks alot, i figure out what it was. I put the code in the jump to report, and changed the field from ID to Description, i made the mistake i putting ID , when i needed Description. Thanks for the help!

hyperlink in reporting services

Hi
I have developed a report using microsoft reporting
services with certain fields.
In my report the user enters name (which is a parameter) and the
report is displayed
Inside my report. I have a field studentID which should be a link
which when clicked should take me to a new report which is a report
in extranet.
Currently I dont have access to that rdl and for certain reasons, I am
asked to link to that report by
coding a hyperlink in development
I know that in the text box under action properties I need to give the
url , but its not working
Should i specify the student id anywhere .
What and where should I code'
ThanksYou want to use Jump to URL (since you don't have the RDL). You have to make
it an expression and create the appropriate URL string (and yes, you need to
reference your parameter when creating this string.).
The best way to develop this is to have a report with nothing on it except a
text box and the report parameter that you will be using. Then set the
textbox to an expression and create the appropriate string. This will allow
you to see the string and whether it works.
Note the first thing you should be able to do is put the URL into IE and
make sure you have it working prior to trying to create the URL.
In books online search on URL
Once you know how to create the appropriate expression right mouse click,
properties on the field and go to the navigation tab.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<sowmyamanjunath@.gmail.com> wrote in message
news:1187195866.765576.139610@.w3g2000hsg.googlegroups.com...
> Hi
> I have developed a report using microsoft reporting
> services with certain fields.
> In my report the user enters name (which is a parameter) and the
> report is displayed
>
> Inside my report. I have a field studentID which should be a link
> which when clicked should take me to a new report which is a report
> in extranet.
> Currently I dont have access to that rdl and for certain reasons, I am
> asked to link to that report by
> coding a hyperlink in development
>
> I know that in the text box under action properties I need to give the
> url , but its not working
> Should i specify the student id anywhere .
> What and where should I code'
>
> Thanks
>|||Hi
Thanks for the reply. I have the url and in my
report in the acion of the textbox i have(jump to url) the URL for the
report
The problem is this url has a report which takes student ID as a
parameter. how do I pass this parmeter from my report to this url
The url is something like this
https://extranet........net/ReportServer?/studentportfolio/STUDENT_PORTFOLIO&rs:Format=PDF&rs:Command=Render&rc:parameters=true&STUDENT_ID=#########|||On Aug 15, 11:46 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> You want to use Jump to URL (since you don't have the RDL). You have to make
> it an expression and create the appropriate URL string (and yes, you need to
> reference your parameter when creating this string.).
> --
I was never able to make this work (all the javascript and all that
which the BOL recommended). What did work for me was to have my SQL
Query return my link text for me...
SELECT
field1
, field2
, field3
, 'http://server.domain.com/path/doc.script?params=' +
rtrim(ltrim(field3)) as field3Link
FROMtable
...and then merely select 'field3Link' from the Jump to URL
dropdown.
Its a bit clunky, but works flawlessly.
Brian|||The problem i am facing is with the parameter part
If i try to get this from my query as a field
say field3 = url
the url I have been provided is something like this
https://extranet........net/ReportServer?/studentportfolio/STUDENT_PORTFOLIO&rs:Format=PDF&rs:Command=Render&rc:parameters=true&STUDENT_ID=########
How do i pass the parameter in the end of URL|||Your URL looks correct. A couple of notes, ensure the parameter name is
spelled correctly (I believe it is case sensitive) and you are passing valid
values. Also, as recommended earlier, get the url working properly outside
of the report, then try to make it dynamic.
This link might also provide some help:
http://msdn2.microsoft.com/en-us/library/ms152835.aspx
"sowmyamanjunath@.gmail.com" wrote:
> The problem i am facing is with the parameter part
> If i try to get this from my query as a field
> say field3 = url
> the url I have been provided is something like this
> https://extranet........net/ReportServer?/studentportfolio/STUDENT_PORTFOLIO&rs:Format=PDF&rs:Command=Render&rc:parameters=true&STUDENT_ID=########
>
> How do i pass the parameter in the end of URL
>
>|||This is very easy. First you are not putting this in as a hard coded string.
You need it to be an expression
="https://extranet........net/ReportServer?/studentportfolio/STUDENT_PORTFOLIO&rs:Format=PDF&rs:Command=Render&rc:parameters=true&STUDENT_ID="
& Parameters!ParameterName.Value
Note the parameter name in your URL is case sensitive. I used the expression
builder when doing this.
If it is not a report parameter then just use the expression builder to pick
the appropriate field.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<sowmyamanjunath@.gmail.com> wrote in message
news:1187197871.255888.162550@.k79g2000hse.googlegroups.com...
> Hi
> Thanks for the reply. I have the url and in my
> report in the acion of the textbox i have(jump to url) the URL for the
> report
> The problem is this url has a report which takes student ID as a
> parameter. how do I pass this parmeter from my report to this url
> The url is something like this
> https://extranet........net/ReportServer?/studentportfolio/STUDENT_PORTFOLIO&rs:Format=PDF&rs:Command=Render&rc:parameters=true&STUDENT_ID=#########
>

hyperlink fields

Hello fellows,
Is it possible to make a hyperlink field like we can do in MS Access?
If it is possible then how can I do it? Any suggestions please? Thankx
in advance.
Geri
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!I am not aware of MS Access thing.. can you explain this feature..be able to
relate that and find out what we can do in sql server..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Geri Gavertz" <gerific@.yahoo.com> wrote in message
news:ub3dAk7HFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hello fellows,
> Is it possible to make a hyperlink field like we can do in MS Access?
> If it is possible then how can I do it? Any suggestions please? Thankx
> in advance.
> Geri
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Geri Gavertz wrote:
> Hello fellows,
> Is it possible to make a hyperlink field like we can do in MS
> Access? If it is possible then how can I do it? Any suggestions
> please? Thankx in advance.
> Geri
That sounds like a front-end feature. The URL itself is just a character
string. SQL Server doesn't do anything with the data, 'cept store it.
You could put the URL in a varchar(255).
Where are you looking to use the URL?
David Gugick
Imceda Software
www.imceda.com

hyperlink

Hi everyone, I am trying to hyperlink a file to the report. We are running visual and in one of the fields, we have a pdf file linked to it. When I tried making a report that pulls that information so we can click on it and be linked directly to the file, but all it gives me is the path to the file. Is it possible to link it that way? I notice under properties, there are option for linking but its just for a report, bookmark, and URL. but none for direct files. Please help.

Thanks

abz

I am not so sure that this is a supported feature in Business Intelligence Studio. It is quite simple to do using Visual Studio, though.

You would need to write code to programmatically open the file.

|||

I am running visual studio .net 2003. but where can I get info so I can write code to open it like you said?|||

You want to handle an event such as a linkbutton click event.

What language do you prefer? C#, VB? I'll dig up some sample code for you.

What type of file are you opening and what program do you want to open it with?

|||

Here is how to open a file in notepad.

C#

System.Diagnostics.Process.Start( "notepad.exe", "text.txt");

VB

Process.Start("notepad.exe", "c:\info.txt")

|||

Oh ok, well any language will be fine as long as it works, and the file is a PDF.

Thanks

abz

|||

OK, then it would probably be something like this:

Process.Start("Acrobat.exe", "c:\myFile.pdf")