SQL Reporting :: Using Parameters To Search Dates In Services
Sep 19, 2010
I have recently started using SQL Server Reporting services. I currently have a problem. I am trying to achieve a search by dates in a report. I have a table with the fields RequestDate in e.g. 22-Jan-2010 or 23-Sep-2010. I have created a report in Query Designer with two parameters in it RequestDate & RequestDate2.
The Operators are for RequestDate is: Is more than or equal to The value is: 1-Jan-2010 And the Operator for RequestDate2 is: Is less than or equal to. The value is 30-Dec-2010 When i go to the SQL Statement this is produced:
SELECT
MHRA.ImportRequestID
,MHRA.UniqueID
,MHRA.CreatedBy
,MHRA.RequestDate
FROM
MHRA
WHERE
MHRA.RequestDate >= @RequestDate
AND MHRA.RequestDate <= @RequestDate2
When i go to view report i want to be able to search for dates between the dates 1 Jan 2010 and 30-Aug-2010 My query seems to pick up all or most of the results between these dates. How can i build this query so i can get specific results. For example...in the Request Date Parameter box i would put 2-feb-2010 and in Request Date 2 i would put in 5-Jun-2010 and this would return all the results between these dates.
I developed an ASP.net 1.1 web application that is written in VS2203 and I am using Reporting Services 2005 to render some reports in PDF Format. My problem was it is working fine in local (XP) and Production environment (Windows 2003 Server).fromo long back the solution was not working for my integration Server which has windows 2003 Server (1.1) and we installed the 2.0,3.5 frameworks. It is throwing an Object refernce error while render the report. Is there any permisions or something missing.
i'm using sql server 2008 with advance services the below error is of reporting services.."The permissions granted to user 'sysnameASPNET' are insufficient for performing this operation. (rsAccessDenied)"when i'm trying to host appliction.. the above error is occured..it works fine when i try to execute locally.. but when i try to execute through the IIS i.e host applicationthe above error is displayed.
I have created a drill down report in SSRS which works fine in Visual studio but when deployed and viewed using a report viewer on an asp.net web page it acts funny. It load fine the first time but as soon as i expand any of the drill downs the width of the report suddenly becomes very! lengthy and the details data disappears.
serverClass.GetInstances() {System.Management.ManagementObjectCollection} System.Management.ManagementObjectCollection + Count 'serverClass.GetInstances().Count' threw an exception of type 'System.Runtime.InteropServices.COMException' int {System.Runtime.InteropServices.COMException} IsSynchronized false bool
Code:-
const string WmiNamespace = //@"\localhost ootMicrosoftSqlServerReportingServicesv8"; // ORIGINAL @"\localhost ootMicrosoftSqlServerReportServerv9Admin"; // NEW ONE //@"\localhost ootMicrosoftSqlServerReportingServicesv9Admin"; // NOT WORKING COPY const string WmiRSClass = //@"MSReportServerReportManager_ConfigurationSetting"; // ORIGINAL // @"MSReportManager_ConfigurationSetting"; @"MSReportServer_ConfigurationSetting"; // NEW ONE string fullWmiClassName = WmiNamespace + ":" + WmiRSClass; ManagementClass serverClass; ManagementScope scope; //scope = new ManagementScope(WmiNamespace, connOptions); scope = new ManagementScope(WmiNamespace); // Connect to the Reporting Services namespace. scope.Connect(); // Create the server class. serverClass = new ManagementClass(fullWmiClassName); // Connect to the management object. try { serverClass.Get(); } catch (Exception ex) { throw new Exception("Could not get WMI information. " + ex.Message); }
It seems the Print Icon in Reporting Services is now throwing up a Windows Authentication box. Even when entering my domainusername and password, nothing happens. It just keeps popping up?
Our reporting service URL is a http://IP Address/........
Not sure if that has anything to do with it. We did add the I.P. in the Local Intranet Zones but still nothing works.
We are running sql server 2000 with reporting services version 8.00.743.00. Yes, I know it's old, but it works. Anyway it worked up until a week ago. Now when you try to render a report on IE 7 or 8 or Firefox, the windows authentication appears multiple times. The report continues to render fine in IE 6.
I have a SQL Server 2005 database with Windows Credentials. I am trying to pass my credentials from Visual Web Developer to my report but it returns me: "Cannot create a connection to data source". My web application is based on Windows login as well.
I am using HTML controls while using Ajax in my .net project. Now I want to make my text box to be autocomplete. I am fetching a data using a query for the respective typed text but i am not sure how to bind that data to text box and show it the way it is being displayed in google and other famous sites. I would also love to know the way using web services. Which way is more efficient?
I have a problem in deploying the reports. when i try to deploy the reports it shows that Report Sever not working but when i try to start my SQL Server Reporting Services will get the below mentioned error so please can one could suggest me on this.
"Could not Start the SQL Server Reporting Services (MSSQLSERVER) service on local computer
ERROR1053: Service didnt respond to start or control request in a timely fashion"
I am truying to work in my PC with reporting services 2008, but I got this message:
The report server can not decrypt the symmetric key used to access sensitive or encrypted data in a database server reports. You must restore a key from a backup or delete the encrypted content.(RsReportServerDisabled) .
Please suggest me some good reference for learning RDLC reports. Does it provides all the features as provided by BOE3.1(Business Objects Enterprise 3.1).
Upgraded to IE 8 and received the "Unable to load client print control" msg researched this a bit and everything pointed to the Killbit hotfix. However we did not install this hotfix so this can't be the problem. Uninstalled IE 8 and went back to IE 6 still get the error msg.
Running windows 2003 server, SQL server 2005sp2 on same box
I have one application in that there are two server 1. Web/Application Server 2. Database Server. i installed only reporting services component on web server and connect report server database from second server i.e DB Server. but When i clicked on report services URL it gives error: The report server installation is not initialized. (rsReportServerNotActivated) what extra needs to configure report server?? what are the steps to configure report server ?? i checked in log files it shows as :
appdomainmanager!DefaultDomain!fcc!03/29/2010-14:15:15:: i INFO: Appdomain:11 ReportManager_MSSQLSERVER_0-3-129143259005241268 started. appdomainmanager!ReportManager_0-3!fcc!03/29/2010-14:15:16:: i INFO: RS authentication mode is 4; effective ASP.NET authentication mode is Windows. vdir=/Reports. appdomainmanager!DefaultDomain!fcc!03/29/2010-14:15:16:: i INFO: Appdomain:11 ReportManager_MSSQLSERVER_0-3-129143259005241268 initialized (#2). appdomainmanager!DefaultDomain!1e0!03/29/2010-14:15:23:: i INFO: Appdomain:12 ReportServer_MSSQLSERVER_0-4-129143259230591718 started. appdomainmanager!ReportServer_0-4!1e0!03/29/2010-14:15:24:: i INFO: RS authentication mode is 4; effective ASP.NET authentication mode is Windows. vdir=/ReportServer. httpruntime!ReportServer_0-4!1e0!03/29/2010-14:15:24:: i INFO: Report Builder manifest file C:Program FilesMicrosoft SQL ServerMSRS10.MSSQLSERVERReporting ServicesReportServerReportBuilderReportBuilder_2_0_0_0MSReportBuilder.exe.manifest not found. appdomainmanager!DefaultDomain!1e0!03/29/2010-14:15:24:: i INFO: Appdomain:12 ReportServer_MSSQLSERVER_0-4-129143259230591718 initialized (#2). webserver!ReportServer_0-4!1e0!03/29/2010-14:15:54:: i INFO: Reporting Web Server started resourceutilities!ReportServer_0-4!1e0!03/29/2010-14:15:54:: i INFO: Reporting Services starting SKU: Standard resourceutilities!ReportServer_0-4!1e0!03/29/2010-14:15:54:: i INFO: Evaluation copy: 0 days left resourceutilities!ReportServer_0-4!1e0!03/29/2010-14:15:54:: i INFO: Running on 2 physical processors, 2 logical processors runningjobs!ReportServer_0-4!1e0!03/29/2010-14:15:54:: i INFO: Running Requests Scavenger timer enabled: Next Event: 60 seconds. Cycle: 60 seconds runningjobs!ReportServer_0-4!1e0!03/29/2010-14:15:54:: i INFO: Running Requests DB timer enabled: Next Event: 60 seconds. Cycle: 60 seconds runningjobs!ReportServer_0-4!1e0!03/29/2010-14:15:54:: i INFO: Memory stats update timer enabled: Next Event: 60 seconds. Cycle: 60 seconds library!ReportServer_0-4!1e0!03/29/2010-14:15:56:: Call to GetPermissionsAction(/). library!ReportServer_0-4!1e0!03/29/2010-14:15:56:: i INFO: Catalog SQL Server Edition = Standard crypto!ReportServer_0-4!1e0!03/29/2010-14:15:56:: i INFO: Initializing crypto as user: NT AUTHORITYNETWORK SERVICE crypto!ReportServer_0-4!1e0!03/29/2010-14:15:56:: i INFO: Exporting public key crypto!ReportServer_0-4!1e0!03/29/2010-14:15:56:: i INFO: NT Service not activated library!ReportServer_0-4!1e0!03/29/2010-14:15:56:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerNotActivatedException: The report server installation is not initialized., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerNotActivatedException: The report server installation is not initialized. crypto!ReportServer_0-4!1e0!03/29/2010-14:15:56:: i INFO: Initializing crypto as user: NT AUTHORITYNETWORK SERVICE crypto!ReportServer_0-4!1e0!03/29/2010-14:15:56:: i INFO: Exporting public key crypto!ReportServer_0-4!1e0!03/29/2010-14:15:56:: i INFO: NT Service not activated library!ReportServer_0-4!1e0!03/29/2010-14:15:56:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerNotActivatedException: The report server installation is not initialized., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerNotActivatedException: The report server installation is not initialized. ui!ReportManager_0-3!fcc!03/29/2010-14:15:57:: e ERROR: System.Web.Services.Protocols.SoapException: The report server installation is not initialized. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerNotActivatedException: The report server installation is not initialized. at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.GetPermissions(String Item, String[]& Permissions) at Microsoft.ReportingServices.WebServer.ReportingService2005.GetPermissions(String Item, String[]& Permissions) ui!ReportManager_0-3!fcc!03/29/2010-14:15:57:: e ERROR: HTTP status code --> 200 -------Details-------- System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: The report server installation is not initialized. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerNotActivatedException: The report server installation is not initialized. at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.GetPermissions(String Item, String[]& Permissions) at Microsoft.ReportingServices.WebServer.ReportingService2005.GetPermissions(String Item, String[]& Permissions)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.SqlServer.ReportingServices2005.ReportingService2005.GetPermissions(String Item)
at Microsoft.SqlServer.ReportingServices2005.RSConnection.GetPermissions(String Item)
at Microsoft.ReportingServices.UI.Permissions.GetPermissions()
at Microsoft.ReportingServices.UI.Permissions.CurrentUser(String itemPath)
at Microsoft.ReportingServices.UI.ReportingPage.get_RSUser()
at Microsoft.ReportingServices.UI.FolderPage.Page_Init(Object sender, EventArgs e)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Page.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) library!ReportServer_0-4!1e0!03/29/2010-14:15:57:: Call to GetSystemPropertiesAction(). crypto!ReportServer_0-4!1e0!03/29/2010-14:15:57:: i INFO: Initializing crypto as user: NT AUTHORITYNETWORK SERVICE crypto!ReportServer_0-4!1e0!03/29/2010-14:15:57:: i INFO: Exporting public key crypto!ReportServer_0-4!1e0!03/29/2010-14:15:57:: i INFO: NT Service not activated library!ReportServer_0-4!1e0!03/29/2010-14:15:57:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerNotActivatedException: The report server installation is not initialized., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerNotActivatedException: The report server installation is not initialized. crypto!ReportServer_0-4!1e0!03/29/2010-14:15:57:: i INFO: Initializing crypto as user: NT AUTHORITYNETWORK SERVICE crypto!ReportServer_0-4!1e0!03/29/2010-14:15:57:: i INFO: Exporting public key crypto!ReportServer_0-4!1e0!03/29/2010-14:15:57:: i INFO: NT Service not activated library!ReportServer_0-4!1e0!03/29/2010-14:15:57:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerNotActivatedException: The report server installation is not initialized., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerNotActivatedException: The report server installation is not initialized. ui!ReportManager_0-3!fcc!03/29/2010-14:15:57:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted. at System.Threading.Thread.AbortInternal() at System.Threading.Thread.Abort(Object stateInfo) at System.Web.HttpResponse.End() at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm) at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal() at System.Threading.Thread.Abort(Object stateInfo) at System.Web.HttpResponse.End() at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm) at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
I have question is it possible to install Report Manager (IIS Service) on different server than Reporting Services is installed? I would like have IIS service (Report Manager) on one server and Reporting Services mechanism on the second one. Do you think this solution is possible?
I have started using rdlc file in my asp.net website.I am wondering of a way to load the reoprt on aspx page.I have one.aspx which collects user define parameters. When user click next, I collect all user input and store in session then it will go to two.aspx.On two.aspx, I have reportviewer which looking a local report (report.rdlc) with datasource (ds1) which has session parameters.I feed ds1 parameter with session fields from one.aspx.It all working fine.But...., it is taking long time to go from one.aspx to two.aspx.
I assume it is because report is being generated on page_load (two.aspx).Is there any clever way... to generate/view this report?I tried to use UpdatePanel and UpdateProgress with no luck.If this title is already existed in this forum, please give me the link. I could not find the right topic.I m using asp.net 3.5, Visual studio 2010, SQL 2008 server.
This is the error I m getting. Can anyone tell me what exactly the problem isAn error occurred during local report processing.An error has occurred during report processing.Query execution failed for dataset "DataSet1".ExecuteReader : CommandText property has not been initialized.
I have a page like this In my SQL, I want calculate some value between these days And this is my code;
strQuery = @"SELECT B.HESAP_NO, A.TEKLIF_NO1 + '/' + A.TEKLIF_NO2 AS 'TEKLIF', B.MUS_K_ISIM, CONVERT(VARCHAR(10),A.ISL_TAR,103) AS 'TARIH', SUM(ISNULL(CAST(A.ODENEN_ANAPARA AS FLOAT),0)+ISNULL(CAST(A.FAIZ AS FLOAT),0)+ ISNULL(CAST(A.BSMV AS FLOAT),0)+ISNULL(CAST(A.GECIKME_FAIZ AS FLOAT),0)+ ISNULL(CAST(A.GECIKME_BSMV AS FLOAT),0)) AS 'YATAN', (CASE WHEN B.DOVIZ_KOD = 21 THEN 'EUR' WHEN B.DOVIZ_KOD = 2 THEN 'USD' WHEN B.DOVIZ_KOD = 1 THEN 'TL' END) AS 'KUR', D.AVUKAT, (CASE WHEN D.HESAP IN (SELECT T_HESAP_NO FROM TAKIP) THEN (SELECT ICRA_TAR FROM TAKIP WHERE T_HESAP_NO = D.HESAP) ELSE ' ' END) AS 'ICRA TARİHİ', (CASE WHEN D.HESAP IN (SELECT T_HESAP_NO FROM TAKIP) THEN (SELECT HACIZ_TAR FROM TAKIP WHERE T_HESAP_NO = D.HESAP) ELSE '' END) AS 'HACİZ TARİHİ' FROM YAZ..MARDATA.BIR_TAHSIL A, YAZ..MARDATA.S_TEKLIF B, AVUKAT D WHERE A.TEKLIF_NO1 = B.TEKLIF_NO1 AND A.TEKLIF_NO2 = B.TEKLIF_NO2 AND B.HESAP_NO = D.HESAP AND A.HESAP_NO = D.HESAP "; if (txtBoxText1 != "") { strQuery = strQuery + " AND A.ISL_TAR >= @S_TARIH_B"; dt_stb = DateTime.Parse(txtBoxText1); myCommand.Parameters.AddWithValue("@S_TARIH_B", dt_stb); } if (txtBoxText2 != "") { strQuery = strQuery + " AND A.ISL_TAR <= @S_TARIH_S"; dt_sts = DateTime.Parse(txtBoxText2); myCommand.Parameters.AddWithValue("@S_TARIH_S", dt_sts); } strQuery = strQuery + " GROUP BY B.HESAP_NO, A.TEKLIF_NO1 + '/' + A.TEKLIF_NO2,A.ISL_TAR,B.DOVIZ_KOD ,B.MUS_K_ISIM, D.AVUKAT, D.HESAP"; And this is my Function; ALTER FUNCTION [dbo].[fngcodeme] ( @HESAP INT,@BAS DATE, @BIT DATE,@DOV INT ) RETURNS FLOAT AS BEGIN RETURN( SELECT SUM(TUTAR) FROM YAZ..MARDATA.M_GHAREKET WHERE TEMEL_HESAP = @HESAP AND DOVIZ_KOD = @DOV AND REF_KOD = 'GC' AND BACAK_GRUP = 'PERT' AND ISL_KOD = 1 AND ISL_TAR >= @BAS AND ISL_TAR <= @BIT ) END
What I want getting a value in this code with this function. @BAS is Start Date, @BIT is End Date How can I associate @BAS with Textbox1 and @BIT with Textbox2 ?
OleDbCommand com = _ new OleDbCommand("select * from techs where actd0v between '" + _ TextBox1.Text + "' and '" + TextBox2.Text + "'" , con);
where textbox 1 and 2 are the specified dates in which I want to retrieve some date related to those dates. But when I'm trying to find the data between the given dates it gives me some dates which are not included in between them. Is there any pattern I need to specify to get all the dates which occur in between the specified dates.
I'm using the date picker and I need to know how (or if it's even possible), to select a date, and based on the date, have the actual query use the week the date was in and return all the records from that week (Sunday through Saturday)
Like - if I pick 8/24/2010 - it returns a date range of 8/22 through 8/28 I'd also like to display the date range in the report, somehow
Can i set dates [From date and To date] as parameter in my RDLC report. Report generating using sql datasource .. But i want to pass dates as a parameters.
