Reporting Services :: Default Path For Saving Reports On File System?
Jun 23, 2015
during period of troubles connecting to Local Servers and Sites location i.e., to [URL], the place where all Reports are saved by default, I have continued to work on some Reports that have been saved to file system of my localhost/developing machine. But, upon recovery of http location, I can not see/open those files?
how to approach those files in order to open them in Report Builder 3.0 and thus, to "save as" them to the http location? Seems that Report Builder has chosen that location, by default, somehow.....
View 2 Replies
ADVERTISEMENT
Oct 31, 2007
Hi everyone, I'm very new to SQL and SQL Reporting Services.
However I've been given a task.
How do I save multiple reporting services reports onto a predetermined location on a users local pc? Are there tools/applications that can be used to push files onto the users local pc?
The application uses .NET but they've created reports using SQL Reporting Services. The user will select which report they want (out of 2). My task is to figure out how can we save those reports that they have selected onto their pc?
Thanks in advance
View 1 Replies
View Related
Sep 30, 2015
I know that SSRS default rendering formats is HTML. is there anyway we can change this behaviour to render in XM format for specific reports.
View 5 Replies
View Related
Sep 2, 2015
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. Â My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.Â
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
View 2 Replies
View Related
Jul 31, 2007
I looked online and couldn't find anything to help me make this change. I want to change the default URL for reporting services to another url. Is this possible? Any assistance would be greatly appreciated.
View 3 Replies
View Related
Oct 9, 2006
I am having an issue with the File System Task.
I was wondering if there is a way to 'Move File' with the File System Task inside of a For Each Loop container but to dynamically set the Destination path variable.
Currently, this is what I have:
FileDestinationPath variable - set to C:TestFiles
FileSourcePath variable - set to C:TestFiles
FileNameAndLocation variable - set to blank
For Each Loop Container €“ Iterates through a folder C:TestFiles that has .txt files in it with dates in the file name. Ex: Test_09142006.txt. Sets the file path (fully qualified) to the Variable Mapping FileNameAndLocation.
Script Task (within For Each Loop, first step) €“ Sets the FileDestinationPath to the correct dated folder within C:TestFiles. For example, if the text files I want to move are for the 14th of September, it takes FileDestinationPath and appends the date folder to the end of it. The text files have a date in the file name (test_09142006.txt) and I am picking this apart (from FileNameAndLocation in the For Each Loop) to get the folder date. (dts.Variables(€œUser::FileDestinationPath€?).Value = dts.Variables(€œUser::FileDestinationPath€?).Value & €œ€? Month & €œ_€? & Day & €œ_€? & Year & €œ€?) which gives me €œC:TestFiles 9_14_2006€?.
File System Task (within For Each Loop, second step) €“ This is where the action is supposed to occur. I want it to take the FileDestinationPath and move the FileNameAndLocation file (from the For Loop) into this folder for each run.
Now as for my problem. I want this package to run everyday but it has to set the FileDestinationPath variable dynamically according to that day€™s date. Basically, how do I get this to work since I can€™t hard code the destination path variable from the start? I have the DestinationVariable on the File System Task set to the FileDestinationPath variable, after the script task builds it. However, using FileNameAndLocation as the SourceVariable on my File System Task tells me that the €œVariable €œFileNameAndLocation€? is used as a source or destination and is empty.€?
Let me know if I need to clarify further€¦...I may be missing something very simple. Any help would be greatly appreciated!
View 10 Replies
View Related
Jun 27, 2007
Hi, I'm a first time user at reporting services and in dire need of some help, I'm having a real hard time finding the information I need.
I have made two reports in Business Intelligence Development Studio that access a sql database and I can deploy them and see them at http://localhost/reports etc.
What I wan't to do is be able to "trigger" the reports to be built from a schedule (e.g. every 8 hour on weekdays, every 12 hours on weekends but should be able to be changed) and after built to be saved to a html file.
I also need to set the sql query for the dataset before generating the report (select * from table where DateTimeType between This and That).
Anyway, I'm completely lost on how to achive this and would greatly appreciate and tips or pointers to where the information can be found.
Kindest Regards
View 2 Replies
View Related
Sep 21, 2015
I have created a custom library(CodeLibrary) which internally references the dlls Microsoft.TeamFoundation.Client and Microsoft.TeamFoundation.WorkItemTracking.Client.
I added this custom Dll codelibrary.dll to my SSRS report. and the expression of one of the field asÂ
=codelibrary.codefunction.GetValue(1000) Â
codefunction is the class and GetValue is the method.
When I preview the report, I get the error "Error while loading code module:
'CodeLibrary,Version=1.0.0.0,Culture=neutral,PublicKeyToken=null'. Could not load file or assembly 'CodeLibrary,Version1.0.0.0, Culture=neutral,PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified."
I am using VS2013, I have placed the custom library DLL in the path
C:Program FilesMicrosoft Visual Studio 12.0Common7IDEPrivateAssemblies
I have tested the custom library with a WPF application and it works fine.
I am not able to figure out what is causing this error.
View 4 Replies
View Related
Jul 19, 2006
We have been storing packages in the File System folder. We had noticed that there seemed to be times when re-importing an existing package did not seem to update it properly. We tried deleting the existing package first rather than overwriting it, but to no avail.
Today we noticed that there were two DTS90Packages folders, one on the C drive and one on the D drive. The dates on the files in those folders showed that sometimes the import put the file on one drive, and other times on the other drive.
The MsDtsSrvr.ini.xml file shows this: "<StorePath>..Packages</StorePath> ".
We intend to stored the packages in the msdb database instead in order to work around this problem.
Has anyone else noticed this happening?
Thanks,
Ron
View 3 Replies
View Related
Nov 5, 2007
Hello SQL Server Experts, Data Analysts, and Report Writers et al:
re: Reporting Options with SQL Server
I wanted to propose an offshoot to the pryor thread:
Would anyone take a stab at comparing Access Reports, Crystal Reports,
Cognos or other options to all the Reporting Services and its components offered as part ofSQL Server, especially as to extracting data from SQL Server into a report format?
I guess this is a far as capabilites, ease of use, limitations, and especially formatting
or presentation of the end report product?
Thank you to all, and I hope this is a beneficial discussion to others.
Hal1490
Hal9000
View 4 Replies
View Related
Apr 6, 2015
I am running my package in sql server 2012, in which i am giving network path for flat file destination. And its working fine. But if i give m local path, its giving me  error " cannot open data file" ...
Nothing is wrong with package.
View 10 Replies
View Related
Sep 24, 2015
Using the "move" command it's possible to relocate e.g. datasources without negatively impacting reports that use them. We've got some folders where users have uploaded images for reuse in several reports as external images (2008 R2).
Now I'm trying to consolidate these folders. Yet it seems, moving images will not update the reports that use them as it happens with datasource. Instead, the link to the image breaks.
Is there a way to move the images and telling the reports that reference them that I did so?
View 4 Replies
View Related
Dec 15, 2009
I am using a Report Viewer on a web page and I'm not sure how to set up the ReportPath.
The URL.... I don't have any permission on the ReportServer database itself. How can I determine the correct path?
View 7 Replies
View Related
Oct 15, 2007
I've created some reports in Reporting Services 2003 and would like to call them up from a .Net 2003 windows based application. I'm passing one parameter to the report and would like to be able to automatically call the Save As... command and provide a path and name to save the file. I wnat to render the report as a PDf file and save it in that format as well. I can generate the name of the file on the fly using the input parameter value.
What I need is a sample of how to instantiate a reporting services object in .Net 2003 and the commands to execute the Save As function after the report has been rendered.
Thanks
The Mad Jammer
View 6 Replies
View Related
Sep 15, 2015
I have a drill through report. I am passing multiple parameter values to my sub report using 'url'.
But I am getting error 'The full path must be less than 260 characters long'.
View 4 Replies
View Related
Sep 28, 2015
I have an XML file that some of the nodes are represented with a namespace prefix. I have found the "msdn" page where shows the possibility to filter using the Namespace Prefix, but I have not been successful at it.
View 4 Replies
View Related
Oct 28, 2015
We are running 2014 enterprise. Our users love to see related report sections saved in separate tabs of the same spreadsheet. Is there a way to control how ssrs will save a report to excel when it comes  to tabs? Â
Are subreports a/the way (and only way) to do this?Â
By sections I mean they might have a grid, then another grid, then a graph then another graph and so on.Â
What property controls the tab name? What if they want to combine 2 or more sections into one tab?
View 7 Replies
View Related
Apr 24, 2015
I am trying to configure the reporting for TFS using SQL Server. But I get following error when viewing any report:
So I try to manually process the cube to check if it works. I am following this article:Â [URL] ....
When I click on GetProcessingStatus and invoke it (with last field set as TRUE) I get following error:
How to resolve this issue and be able to see the reports.
View 5 Replies
View Related
Jun 9, 2015
I have an issue as to where only some reports are visible within Report Viewer url. But if I navigate to the path of the rdl's there are many more present that should be visible through Report Viewer. The client was recently upgraded, and I seem to have all out of box roles and permission levels yet can only see some of those reports, as if some were manually copied to this folder location at a later point and perhaps some user job needs to be run to reflect import those additional rdl's?
View 2 Replies
View Related
Jul 20, 2005
Hi All,I use Bulk insert to put data to myTable.When the SQL server is in local machin, it works well. But when I putthe data in a sql server situated not locally, then I get a errormessage like this:Could not bulk insert because file 'C:Data2003txtfilesabif_20031130.txt' could not be opened. Operating systemerror code 3(The system cannot find the path specified.).BULK INSERT myTableFROM 'C:Data2003 txtfilesabif_20031130.txt'with (-- codepage = ' + char(39) + 'ACP' + char(39) + ',fieldterminator = ';',rowterminator = '',keepnulls,maxerrors=0)Someone can explan me what the error shows upThanks in advance- Loi -
View 3 Replies
View Related
Apr 28, 2015
Basically i have 3 images capacity per record in my asp.net application. In there i am saving the image path with record id in database and image in my application folder.
Now i am creating SSRS Report in Report builder . In there i have taken one image control to show the images. In the Image properties in report builder i have chosen database under the select the image source field. then inside use this field i have chosen image url and in use this MIME Type i have selected image/jpeg. Now i have saved this report in report server folder.
Now while calling in .net web from through report viewer control. It is opening the report but wont showing the image.
View 2 Replies
View Related
Jan 9, 2008
Hi,
We've had this issue for awhile, but I have not been able to find a resolution for this. We have 2 identical servers (hardware & software) set-up: IBM x3850-(88633RU) running Windows Server 2003 Enterprise x64 (5.2.3790 SP1 Build 3790 R2). Server A was running SP1 (Build 9.0.2047) on a 3-node active-passive-passive failover cluster. Server B was running SP2 + Hotfix KB933097 (Build 9.0.3152) on a 2-node active-passive failover cluster. Both these servers are heavily utilized, and when originally installed with these versions of SQL Server Enterprise 2005 experienced no issues during or post-install. The original install of SQL 2005 Enterprise on both these servers had the Cluster Group Selection "Data files" setting changed to "M:MICROSOFT SQL SERVER" so that all system dbs and binaries would be installed to that location. On Server B where SP2 had already been applied, the update was downloaded on May 1, 2007, well after the GDR issues which had been plaguing SP2 releases was supposed to have been resolved.
Our problems began the night when we tried to apply hotfix KB933097 on Server A, and cumulative update packages 1,2,3 on Servers A & B, as well as catch-up the same SP2 and hotfix packages on other non-clustered servers where default system db locations remained the same. On all servers other than Servers A and B, the installs proceeded smoothly and without issue.
On Server B, which was already running SP2 + Hotfix KB933097, we tried installing Cumulative Update Package 1 (Build 9.0.3161) on the primary node. The install proceeded until the following errors were encountered:
"[Microsoft][SQL Native Client][SQL Server]Cannot find the object 'dm_exec_query_resource_semaphores', because it does not exist or you do not have permission."
"[Microsoft][SQL Native Client][SQL Server]Cannot find the object 'dm_exec_query_memory_grants', because it does not exist or you do not have permission."
After this, the setup finished with the message "Product: Microsoft SQL Server 2005 (64-bit) - Update 'Hotfix 3161 for SQL Server Database Services 2005 (64-bit) ENU (KB935356)' could not be installed. Error code 1603. Additional information is available in the log file C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB935356_sqlrun_sql.msp.log."
We rebooted the server and tried several times to reinstall the hotfix as sa and as the database service account with full windows and sa credentials, each time with no success, and rebooting between retries. All other components were installed successfully. We were hesitant to try uninstalling the hotfix, since it was unable to be removed successfully in previous attempts. However, when I connected to Database Engine using Management Studio, I found that the version of SQL being reported was in fact Build 9.0.3161! Everything also appeared to be running properly.
I tried to to Google the answer, but could only find information as applied to SP1 when system dbs master and mssqlsystemresource were located on different locations (http://support.microsoft.com/default.aspx?scid=kb;EN-US;918695 ). I had already verified that all system dbs, including the ones mentioned, were originally and currently located together on M:Microsoft SQL ServerMSSQL.1MSSQLData.
Finally I contacted a fellow DBA who advised me to continue installing all the other hotfixes, and then compare all SQL files against other servers where the install succeeded to ensure all system files were updated correctly (matching file sizes and datetime stamps). Since we were depending on a fix in Cumulative Update Package 3 to fix a critical log-shipping security issue, I went ahead with the rest of the installs, getting the same error with each package, rebooting between each install.
We found after the final install that the mssqlsystemresource files in the M:Microsoft SQL ServerMSSQL.1MSSQLData
directory were in fact updated after each install with a new timestamp, and that all files in C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn matched on both file size and timestamp. The log-shipping security issue we were looking to fix also appeared to be resolved by the last update.
On Server A, which was running SP1 (Build 9.0.2047), we installed the same SP2 package as we had on Server B with no issue. We rebooted and then tried to apply the same Hotfix KB933097 (Build 9.0.3152) that had been applied successfully on Server B and found to our surprise that the same errors were being generated here as well. We continued to apply the rest of the hotfixes so that both servers matched software updates. Comparing the above directories showed that files seemed to have been updated, even though database services appeared to be not installed.
It looks like we now need Cumulative Update 5 hotfix package in order to deal with WMI alerting issues, but I'm afraid to apply it until we have a known resolution for the past errors.
Since this issue was supposed to be resolved by SP2, I'm not sure why these post-SP2 hotfixes should fail. It seems to me to be an issue of the directory location of the system files which is in question.
I can post the hotfix log files as well, if anyone is interested in seeing them.
Any help would be greatly appreciated. As well, does anyone have any idea when SP3 is scheduled to come out? I really don't like applying hotfixes which are usually untested for general purposes, and it seems Microsoft is delaying this in favour of releasing SQL 2008.
View 6 Replies
View Related
Nov 1, 2006
well, i'm using vs2005 and sql server 2000.i've got this snippet for uploading an image and creating itz thumbnail and also saving the thumbnail and the original image in a folder.but i also want the path of the image and the thumbnail to be stored in sql 2000 database, instead of saving the images in sql.can some one plz provide me the way.thanx in advance.-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click' Initialize variablesDim sSavePath As StringDim sThumbExtension As StringDim intThumbWidth As IntegerDim intThumbHeight As Integer' Set constant valuessSavePath = ".Image"sThumbExtension = "_thumb"intThumbWidth = 160intThumbHeight = 120' If file field isn’t emptyIf Not fileUpEx.PostedFile Is Nothing Then' Check file size (mustn’t be 0)Dim myFile As HttpPostedFile = fileUpEx.PostedFileDim nFileLen As Integer = myFile.ContentLengthIf nFileLen = 0 ThenlblStatus.Text = "No file was uploaded."ReturnEnd If' Check file extension (It must be .JPG)If System.IO.Path.GetExtension(myFile.FileName).ToLower() = ".jpg" Then' Read file into a data streamDim myData() As Byte = New Byte(nFileLen) {}myFile.InputStream.Read(myData, 0, nFileLen)' Make sure a duplicate file doesn’t exist. If it does, keep on appending an ' incremental numeric until it is uniqueDim sFilename As String = System.IO.Path.GetFileName(myFile.FileName)Dim file_append As Integer = 0While System.IO.File.Exists(Server.MapPath(sSavePath + sFilename))file_append = file_append + 1sFilename = System.IO.Path.GetFileNameWithoutExtension(myFile.FileName) + file_append.ToString() + ".jpg"End While' Save the stream to diskDim NewFile As System.IO.FileStream = New System.IO.FileStream(Server.MapPath(sSavePath + sFilename), System.IO.FileMode.Create)NewFile.Write(myData, 0, myData.Length)NewFile.Close()' Check whether the file is really a JPEG by opening itDim myCallBack As System.Drawing.Image.GetThumbnailImageAbort = New System.Drawing.Image.GetThumbnailImageAbort(AddressOf ThumbnailCallback)Dim myBitmap As Drawing.BitmapTrymyBitmap = New Drawing.Bitmap(Server.MapPath(sSavePath + sFilename))' If jpg file is a jpeg, create a thumbnail filename that is unique.file_append = 0Dim sThumbFile As String = System.IO.Path.GetFileNameWithoutExtension(myFile.FileName) + sThumbExtension + ".jpg"While System.IO.File.Exists(Server.MapPath(sSavePath + sThumbFile))file_append = file_append + 1sThumbFile = System.IO.Path.GetFileNameWithoutExtension(myFile.FileName) + file_append.ToString + sThumbExtension + ".jpg"End While' Save thumbnail and output it onto the webpageDim myThumbnail As System.Drawing.Image = myBitmap.GetThumbnailImage(intThumbWidth, intThumbHeight, myCallBack, IntPtr.Zero)myThumbnail.Save(Server.MapPath(sSavePath + sThumbFile))imgPicture.ImageUrl = sSavePath + sThumbFile' Displaying success informationlblStatus.Text = "Image uploaded successfully!"' Destroy objectsmyThumbnail.Dispose()myBitmap.Dispose()Catch errArgument As ArgumentExceptionSystem.IO.File.Delete(Server.MapPath(sSavePath + sFilename))End TryElselblStatus.Text = "The file must have an extension of JPG or GIF"ReturnEnd If End SubPublic Function ThumbnailCallback() As BooleanReturn FalseEnd Function
View 2 Replies
View Related
Jan 12, 2005
I have installed and am using Reporting Services.
I can create RDL files using Visual Studio.NET, but like to offload this task to a minion who does not have VS.NET.
Is there a stripped down something-or-other to make reports that and end user or business analyst could use and then upload the reports?
Thanks.
View 1 Replies
View Related
Aug 23, 2004
Can anyone tell me if MS Reporting Services can import/read a Crystal Reports file as it can an Access report file?
I've seen a few posts out on other boards that there are some companies charging fees for converting Crystal Reports over to MS Reporting Services.
Is there any easier way? Are there any tools out there that can do it?
Perhaps Microsoft should produce such a tool for converting...
Thanks in advance for any help!!
View 3 Replies
View Related
Jul 12, 2007
Crystal reports to Reporting Services
Is there any way I can convert Crystal reports to Reporting Services instead of creating reports again in Reporting Services?
View 3 Replies
View Related
Apr 29, 2015
I have a Manager dashbord  report.In this report 1st page contains some tables and 2nd,3rd page contains line charts with tables.Actually what we need is in 1st page tables are pretty simple,we need to add kpi's and shown these tables in kpi. kpi in ssrs and how to add kpi in ssrs reports?
View 2 Replies
View Related
Nov 30, 2007
We are planning to migrate our crystal reports to reporting services, just wondering if we have cross tab reports in sql server reporting services. Thx.
View 7 Replies
View Related
Apr 30, 2008
I currently have a report on our Reporting Services 2005 server that takes a CompanyID as its only parameter. Also, in our database, we have a list of contacts that are linked to the CompanyID that is used in the report. What I would like to do is email a pdf or each company's report to every contact for that company in our database daily. However I am stuck on how to do that. My project is using C#, but if there is a way to do this directly through Reporting Services I could do that as well. If anyone could help me out I would greatly appreciate it. Thanks!
View 5 Replies
View Related
Jan 25, 2007
I am using Visual Studio 2005 and trying to deploy reports to SQL Server 2000.
I get the following message:
A connection could not be made to the report server http://aklink1/reportserver.
------------------------------
ADDITIONAL INFORMATION:
The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version. (Microsoft.ReportingServices.Designer)
Please help!
View 4 Replies
View Related
May 14, 2015
I'm copying files to a folder with the naming convention as follows in the source folder:
CM_ABC_MY_TEST.txt
In the destination folder, this filename needs to appear as:
CM_XYZ_MY_TEST.txt
In my File System Task, I'm pretty sure I'm going to need an expression with a replace, substring, etc. But am having a hard time nailing down the exact syntax.
View 10 Replies
View Related
Mar 26, 2008
Thought I'd help some folks with rs and dates..
here is some scalar valued functions:
CREATE FUNCTION [dbo].[get_date_only] (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(day, DateDiff(day, 0, GetDate()), 0)
END
CREATE FUNCTION [dbo].[get_month_end] (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(ms, -3, dateadd (m,datediff(m,0,
dateadd(m,1,@date)),0))
END
CREATE FUNCTION [dbo].[get_month_start] (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(m,datediff(m,0, @date),0)
END
CREATE FUNCTION [dbo].[get_today_end] (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(ms, -3, datediff(d,0,dateadd(d,1,@today)))
END
CREATE FUNCTION [dbo].[get_today_noon](@date datetime)
RETURNS datetime
BEGIN
RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,0, @date),0))
END
CREATE FUNCTION [dbo].[get_today_start] (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(day, 0, datediff(d,0,@today))
END
CREATE FUNCTION [dbo].[get_tomorrow_noon](@date datetime)
RETURNS datetime
BEGIN
RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,-1, @date),0))
END
CREATE FUNCTION [dbo].[get_week_end] (@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,7-datepart(weekday, @date),@date))-1900, 0)
+ dateadd(ms, -3,
dateadd(dy, datepart(dy,
dateadd(weekday,7-datepart(weekday, @date),@date)),0) )
END
CREATE FUNCTION [dbo].[get_week_start] (@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,1-datepart(weekday, @date),@date))-1900, 0)
+ dateadd(dy, datepart(dy,
dateadd(weekday,1-datepart(weekday, @date),@date))-1,0)
END
CREATE FUNCTION [dbo].[get_weekday_end] (@weekday tinyint,
@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,@weekday-
datepart(weekday, @date),@date))-1900, 0)
+ dateadd(ms, -3,
dateadd(dy, datepart(dy,
dateadd(weekday,@weekday-datepart(weekday, @date),
@date)),0) )
END
CREATE FUNCTION [dbo].[get_weekday_start] (@weekday tinyint,
@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,@weekday-
datepart(weekday, @date),@date))-1900, 0)
+ dateadd(dy, datepart(dy,
dateadd(weekday,@weekday-datepart(weekday, @date),
@date))-1,0)
END
CREATE FUNCTION [dbo].[get_year_start] (@date datetime)
RETURNS datetime AS
BEGIN
RETURN DATEADD(year,DATEDIFF(year,0, @date),0)
END
CREATE FUNCTION [dbo].[get_yesterday_end] (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(ms, -3, datediff(d,0,@today))
END
CREATE FUNCTION [dbo].[get_yesterday_start] (@today datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(day, -1, datediff(d,0,@today))
END
Then create a Table-Valued Function like so:
CREATE FUNCTION [dbo].[udfCommonDates] (@date datetime)
RETURNS @t table (week_start datetime,
week_end datetime,
lastweek_start datetime,
lastweek_end datetime,
month_start datetime,
month_end datetime,
lastmonth_start datetime,
lastmonth_end datetime,
yesterday_start datetime,
yesterday_end datetime,
today_start datetime,
today_end datetime,
thisweek_monday_start datetime,
thisweek_monday_end datetime,
year_start datetime,
year_end datetime,
tomorrow_noon datetime,
today_noon datetime,
date_only datetime)
BEGIN
INSERT @t
SELECT
dbo.get_week_start ( @date ) AS week_start,
dbo.get_week_end ( @date ) AS week_end,
dbo.get_week_start ( DATEADD(d, -7, @date ) ) AS lastweek_start,
dbo.get_week_end ( DATEADD(d, -7, @date ) ) AS lastweek_end,
dbo.get_month_start( @date ) AS month_start,
dbo.get_month_end ( @date ) AS month_end,
dbo.get_month_start ( DATEADD(m,-1, @date) ) AS lastmonth_start,
dbo.get_month_end ( DATEADD(m,-1,@date) ) AS lastmonth_end,
dbo.get_yesterday_start ( @date ) AS yesterday_start,
dbo.get_yesterday_end ( @date ) AS yesterday_end,
dbo.get_today_start (@date) AS today_start,
dbo.get_today_end ( @date ) AS today_end,
dbo.get_weekday_start(1,@date) AS thisweek_monday_start,
dbo.get_weekday_end(1,@date) AS thisweek_monday_end,
dbo.get_year_start(@date) AS year_start,
dbo.get_year_end(@date) AS year_end,
dbo.get_tomorrow_noon(@date) AS TomorrowNoon,
dbo.get_today_noon(@date) AS TodayNoon,
dbo.get_date_only(@date) AS DateOnly
RETURN
END
Now the RS folks might be thinking but how does this help me as I need a dataset and a dataset can only be based on a Stored Procedure or a direct table. No problem create the following stored procedure:
CREATE PROCEDURE [dbo].[uspCommonDates] AS
begin
set datefirst 1
declare @date datetime
set @date = getdate()
select * from dbo.udfCommonDates(@date)
end
Now you've got a stored procedure to use as a dataset...Now in reporting services add a new dataset:
Now go to the report parameters section of the report:
Now pick that dataset dsFunctions (or whatever you called it) and then pick any of the value fields from the scalar functions such as:
Now when you run the report it uses the scalars:
If you have questions feel free to ask :).
Hope this helps someone
View 2 Replies
View Related
Jul 25, 2008
I am trying to set default print settings for a report on Reporting services.
I need to make to make a page print out on landscape by default.
Â
I cant seem to find this option anywhere.
View 7 Replies
View Related
