Passing Parameter Query From SQL Function To Access Project Report
I can pass a parameter from an Access Query to an Access Report (MDB) by entering [Select Date] in the Query criteria and by placing an unbound control with a control source =[Select Date] on the report. I can't get this to work from a SQL Function Criteria to an unbound control on the Access Data Project Report. In the Function Criteria, I enter @SelectDate. In the Report control, I enter @SelectDate and it gives me an 'Invalide Column Name' error. Any idea how I can pass a parameter from a SQL Function to an ADP report?
THANKS!
p.s. I tried searching for other postings on this without any luck.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Passing Parameters To Action Stored Procedures Using ADO, In Access Project
There is a form in an Access Project (.adp, Access front end with SQLServer) for entering data into a table for temporary storing. Then, byclicking a botton, several action stored procedures (update, append) shouldbe activated in order to transfer data to other tables.I tried to avoid any coding in VB, as I am not a professional, but I havefound a statement in an article, that, unlike select queries, form's InputProperty can't be used for action queries. Therefore, parameters can bepassed to action stored procedure only by using ADO through VB.As I'm not very familiar with VB, I had to search in literature.So, this is a solution based on creating Parameter object in ADO and thenappending values to Parameter collection.Please, consider the following procedure I created for passing parametersfrom form's control objects (Text boxes) to a stored procedureDTKB_MB_UPDATE:Private Sub Command73_Click()Dim cmd As ADODB.CommandSet cmd = New ADODB.Commandcmd.ActiveConnection = CurrentProject.Connectioncmd.CommandText = "DTKB_MB_UPDATE"cmd.CommandType = adCmdStoredProcDim par As ADODB.ParameterSet par = cmd.CreateParameter("@DATE", adDBTimeStamp, adParamInput)cmd.Parameters.Append parSet par = cmd.CreateParameter("@BATCH_NUMBER", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@STATUS", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@DEPARTMENT", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@PRODUCTION", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@SAMPLING_TYPE", adVarWChar, adParamInput,50)cmd.Parameters.Append parcmd.Parameters("@DATE") = Me.DATEcmd.Parameters("@BATCH_NUMBER") = Me.BATCH_NUMBERcmd.Parameters("@STATUS") = Me.STATUScmd.Parameters("@DEPARTMENT") = Me.DEPARTMENTcmd.Parameters("@PRODUCTION") = Me.PRODUCTIONcmd.Parameters("@SAMPLING_TYPE") = Me.SAMPLING_TYPEcmd.ExecuteSet cmd = NothingEnd SubUnfortunately, when clicking on the botton, the following error apears:"Run-time error'-2147217913 (80040e07)':Syntax error converting datetimefrom character string."Obviously, there is some problem regarding parameter @DATE. In SQL Server itis datetime, on the form's onbound text box it is short date (dd.mm.yyyy)data type. I have found in literature that in ADO it should beadDBTimeStamp.So, what is the problem ?Greetings,Zlatko
View Replies !
View Related
Passing Parameter In Function
I have to pass 3parameters in function, @begindate,@enddate and @group_type.. but in @group_type should be - state,zipcode and country from salestable inview :vwstzipcont create view vwstzipcont as select distinct s2.stype,s3.itemnmbr,s2.docdate,s3.state,s3.zipcode,s3.country from Salestable s3 left outer join (select distinct stype,docdate from salesdisttable) s2 on s2.stype = s3.stype where s2.soptype = 2 go create function mystzipcont ( @begindate datetime, @enddate datetime, @group_type char(70)) RETURNS TABLE AS RETURN (Select distinct t.docdate,t.itemnmbr,t.index,t.group_type from ( select distinct vs.docdate,vs.itemnmbr, p.index From Pubs P inner join vwstzipcont vs on vs.index = p.index Where (vs.docdate between @begindate and @enddate) and @group_type ) as t order by t.itemnmbr,t.docdate end how can i assign @group_type variable or t.group_type? in s3.state,s3.zipcode,s3.country can anyone tell me? what condition should be in where clause for this variable? thanks
View Replies !
View Related
Passing Table Name As Parameter To Function
Hi all. I'm writing reports in Rep. Services that reads data from Dynamics NAV (Navision). In NAV data are stored by company and this is implemented by using the company name as prefix to the table name. This means that in a NAV database with three companies (lets call these companies A, B and C) we will have three tables with customers. The table names will be A$Customer, B$Customer and C$Customer. Now to my problem: I wan't to write one report where I can choose company. I do not want to use a stored procedure. I want to use a function so I can use the function in select statements and join several functions to build up a report that needs data from several tables. Is there some way to pass the table name or a part of the table name to a function that returns the content of the actual table? I know I can pass parameters that I can use in the where clause, but is it possible to do it with the table name. Or is there any other way to solve this. All ideas are welcome. Thanks.
View Replies !
View Related
Passing A Suquery To A Function As A Parameter
How would one pass a subquery to a function as a parameter? I have a function, f_Split that returns a table and has two parameters @List varchar(max) and @Delim char(1). I'd like to use it to normalize rows in a table and return the results for use in a report. I tried the following: SELECT * FROM dbo.f_Split((SELECT Code FROM Codes WHERE ID = 10), '|') I'm getting a syntax error for the subquery. Could anyone show me the proper way to pass a subquery to a function, even if the function is different from what I have defined here. Thanks, DD
View Replies !
View Related
Multi-value Parameter In Master Report Passing To Single Param Sub-report In A List.
Here's tricky one. I have a fairly complex report that was given to me that was hard coded for single parameters. There is a dropdown for each market (created from a query in SSRS). The users have to run for each market each week. Is there a way to use this report as a Sub-report inside a list of a master report and then use a mult-value parameter? I want this multi-value parameter to build the values for the list and then run the "sub-report" for each value. Essentially, I want to create a for each loop. Any ideas?
View Replies !
View Related
Passing Report Parameter Through URL
I am experiencing a problem that has been posted a number of times but cannot find a solution. Passed linked report parameter value through URL: Jump to URL: Code Block="javascript:void(window.open('http://myservert/Reports/Pages/Report.aspx?ItemPath=/Sales/Enquiries/Order+Enquiry&rs:Command=Render&Cust="+Fields!Cust.Value+"'))" When I click the link I am taken to the report but it is waiting for me to enter a parameter. I can see the parameter in the url address and it looks correct. I also tried Parameter!Cust.value (which has the same value as Fields!Cust.Value) but get the same result. The target report has the correct name parameter: Cust. In any case, when I use "Jump to report" instead, it works correctly - although I don't see the parameter value in the url. Any advice appreciated. Thanks, SQL Servant
View Replies !
View Related
Default Non-queried Report Parameter Not Updated When Project Is Deployed
Adding a value to a non-queried default report parameter value does not update on the target server after deployment. To recreate 1. Create a report in Visual Studio and add a report parameter with the following properties: Multi-value is checked Available values = "From Query" Dataset = [create a dataset that returns a table w/ a Id and Description column] Value field = [the Id field from the table] Label field=[the Description column from the table] Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report) 2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter. 3. Go back to Visual Studio and add a value to the Non-queried Default values. 4. Build and deploy the report again. View the report. The newly added item is not selected. Notes I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter. Work-arounds 1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this: SELECT '4' AS SelectedId UNION SELECT '5' AS SelectedId UNION SELECT '6' AS SelectedId 2. Delete the report in Management Studio, then redeploy. I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.
View Replies !
View Related
Passing Parameter FROM REPORT TO WINDOWS APP
Hi all. Is it possible to pass parameter FROM Report to my windows application(C#) ? Report is made in Business Intelligence Project. I just want to retrieve the total row number to my windows appication. Is it possible? If yes, Can you provide codes for this? Thanks. Your help would be highly appreciated. -Ron-
View Replies !
View Related
Passing Parameter In SP And Designing The Report Using SP
using SSRS 2000 i am creating web report with the use of parametric stored procedure.I want the solution for the following scenario.. 1. when i ran report it shows textboxes for parameter input and then it shows report.how can i pass parameters to SP so that it can show the report without prompting inputs. 2. How can i design the report with stored procedure using wizard.Because while adding report there is no option of adding SP in wizard instead of query only. Please give your vauable suggestion... Thanks
View Replies !
View Related
Parameter Not Passing Correctly From One Report To Another
I am trying to create a drill-down report by passing the Customer ID number to the drill report. The 'parent' report is a tabular report, as is the 'child.' As long as I used the numeric Customer ID, the child report returned no rows from the passed parameter. However, when I use the Customer Name instead, the child report functions correctly. Here is the parameter as passed using the Customer ID: [Customer Hierarchy].[Customer ID].&[70011231] Can anyone shed some light on why this wouldn't work? Thanks, nanc
View Replies !
View Related
Passing Parameter From Report To Package
Hi all, I'm trying to create a report that will pass its parameter (not multi-valued) to an SSIS package which will use the parameter in one of its conditions to return data and pass it through the DataReader which in turn brings back results for the report itself. Does anyone know how to set this up?? Any help would be greatly appreciated!!
View Replies !
View Related
Passing A Parameter Into A Stored Procedure In A Report ..........
Hi, I have found this question asked many times online, but the answers always consisted of getting a parameter from the user and to the report. That is NOT what everyone was asking. I would like to know how to pass a parameter I already have into my stored procedure. Using Visual Studio 2005, in the "Data" tab of a report calling something likeUnique_Login_IPsreturns correctly when that stored proc takes no params, but when I have one that takes the year, for example, I do not know how to pass this info in. Note: the following does not work in this context : EXEC Unique_Login_IPs ParameterValue nor does EXEC Some_proc_without_paramsVisual studio seems to want only the procedure name because it returns errors that say a procedure with the name "the entire line above like EXEC Unique_Login_IPs ParameterValue" does not exist.... Thanks in advance,Dustin L
View Replies !
View Related
Passing A Parameter To The Filename In A Report Subscription
I have defined a monthly substription for a series of reports. I would like to be able to pass a paremter used in generating the report to the file name define in the subscription. I can do it if I used system names such as @datetime, but I want to be able to use a parameter unique to the report. For example if the parameter for the report was CARS and I was quesrying Toyotas how can I get toyoats to show up in the file name? Any suggestions?
View Replies !
View Related
Passing A Report Group In Drill Through Parameter
Hi all; Could any body tell me as to how I would be able to pass report grouping in drill through report. Please review the diagram below to better understand my problem Report 1 (Summary) Columns1 Column 2 Row-1:Grouped by attribute X Calculation based of dates Calculation based of dates Row-2: Grouped by attribute Y Calculation based of dates Calculation based of dates The Drill thru option should be present in all of the columns; so that, when any of the numbers in the column are clicked it should drill through to another report which should show all the records pertaining to all the values in X1 Report 2 (Details) Report 3 ((Details) X1 Y1 X2 Y2 X3 - X4 - X5 - - Yn - - - Xn What I did so far was to pass the report parmeter in report 1 as "Fields! attribute X ,Value" and same for Y The problem with is that only the first value of the group that satisfies condition gets passed on to the detail reports. Kindly let me know were I am going wrong and what should I be doing. Awaiting your opinions Thanks and Regards GM
View Replies !
View Related
Passing A Report Parameter To Reporting Services From VB
I have a Report that I want to access that has a parameter input for the date, Production_Date. I want to allow the user to use the DateTimePicker to select the date to use for Production_Date. I can't seem to find the correct way to format what I'm tring to do. My production date field is in a format without punctuation so that it becomes: Dim ProductionDate = ((DateTimePicker1.Value.Month * 1000000) + (DateTimePicker1.Value.Day * 10000) + DateTimePicker1.Value.Year) which gives me a value that I want to send as a parameter for the Reporting Services report that I have located on a tab in my project. The report is: Me.ReportViewer1 I want to send the ProductionDate to the report where the report looks for Production_Date. Obviously, I 'm very new to this. I'd appreciate any suggestions. As it runs now, the default date is loaded (today's date) from the expression I calculated in Reporting services: =(Now.Month*1000000)+(Now.Day*10000)+Now.Year This is a big roadblock right now and I can't seem to put together a legal means to pass this value. Thanks for any help.
View Replies !
View Related
Query Criteria / Parameter For A Whole Project
I have a large collection of 'projects', i.e., RS reports for each of our customers. Each project is effectivley a library of maybe up to 15 reports. They are usually modified slightly according to the customer's requirements. Most of the reports use a variery of SQL tables / views and to ensure the customer only sees theor data. I am currently changing the query criteria for each report in the project each time I deply a new library / set. This is time consuming and open to the risk that I mis-key the criteria for one or more report. The criteria is always the customer's account number and is common across most of the database tables. E.g: WHERE (LIVE_INVOICESUMMARY_CRREPONLY.IS_SUCODE = 'DH166'... Is there any easy way to set this criteria at the project level, rather than at individual report level? Can it be done by getting the SQL query to refer to something in the custom code? I know I can use parameters, but again, these have to be set per individual report. I'm on SQL2000
View Replies !
View Related
Use Report Server Project To Connect To Report Model Project Data Source View?
Hello, I've created a Report Model Project that can be used by Report Builder to generate ad-hoc reports. I'm trying to create a connection string in my Report Server Project that points to the Report Model Project data source view. All I can do is create a regular datasource, which bypasses the metadata contained in the Data Source View. Basically I want my Report Server Project and my Report Builder reports to leverage the same metadata. Is this possible? If so how do I get the connection string? Thanks!! -Matt
View Replies !
View Related
RsReportParameterTypeMismatch - When Passing DateTime Parameter To Drill Through Report
All, I have two reports in my project. One is summary and the other is drill through. In Summary report I have StartDate and EndDate Parameters which are DateTime type. Language settings on my workstation are set to Canadian English, and so are in the IE. When I deploy the reports to the Sharepoint WebParts based reporting portal, the reports run fine individually. However, when I try to drill through from summary report to the detail report, the following error is displayed, where the StartDate is 13/07/2007 (Canadian format dd/MM/yyyy): The value provided for the report parameter 'StartDate' is not valid for its type. (rsReportParameterTypeMismatch) Why do the reports run fine individually? Obviously something is wrong with Report Server processing the parameters, that it converts the date format to US English only when passing parameters to drill through report. This problem is eliminated when I change the language settings to US-English on my workstation. Any ideas how can this be fixed, if at all? It would certainly be not feasible to have all the users change their settings from Canadian to US-English. Thanks.
View Replies !
View Related
Passing Parameter From Asp.net Application To Reporting Services Report
Hi, There is a .net application which has a screen with four options(text boxes) Each of these should take to reports generated by SQL REp Services This is the requirment The School selected in the App should be passed on to the report How should I pass the parameter in my report so that when user selects a link or text box report for that particular school number is displayed Thanks sowree
View Replies !
View Related
Passing Parameter To MDX Query
Hi, I am passing Pool Name,From Date and Two date to this quey.I want to change the requirement a bit. I want to select From Date in following query as Last date of Privious Month.Any help on this?? Following query works fine ="Select crossjoin(union(except([From OTS STATUS].[OTS_STATUS].members,[From OTS STATUS].[OTS_STATUS].[All]),[From OTS STATUS].[OTS_STATUS].[All]), [measures].[Loan count] ) on 1,union(except([To OTS STATUS].[OTS_STATUS].members,[to OTS STATUS].[OTS_STATUS].[All]),{[To OTS STATUS].[OTS_STATUS].[All],[To OTS STATUS].[OTS_STATUS].[flat]}) on 0 from [Roll Rate Matrix] where (" &Parameters!FromDate.Value & "," &Parameters!ToDate.Value & "," &Parameters!Pool.Value & ")" I want to get my FromDate as DATEADD(dd,-(DAY(Parameters!FromDate.Value)),Parameters!FromDate.Value) But as soon as I replace From Date parameter with above line it says End Of statement Expected. Any help on this is appriciated. -Thanks, Digs
View Replies !
View Related
In A Query, Passing Data To A .net Function
Hello, I have just migrated some databases from sql2000 to sql2005 and I just read about the possibility to use CLR-based functions within a query. This may be really nice for me, since I often need complex functions and I often cannot write them in TSQL. But my question is: what kind of data can I pass in a sql query to a .net function? Obviously, simple data, like numbers and strings from within a record can be passed. But would it be possible to go further, like passing a complete record, or even passing a set of records? How far could I go in sql when using .net function? Thanks to inform me about the available power of this new feature.
View Replies !
View Related
Passing Multiple Values To Single Report Parameter Using Cube Surce
Hello Freinds, I'm facing a small problem while passing Multiple Values to a Single Report Parameter, this report is using a Cube as its source. I'm able to assign and send a single value to the report parameter but i'm not finding a way to send more than one value.. The following is the code which i am using to populate my Parameter Dim Param(0) As Microsoft.Reporting.WebForms.ReportParameter Param(0) = New Microsoft.Reporting.WebForms.ReportParameter("BUDASADepartment", "[BUDASA].[Department].&[Accommodation]") In the same 'BUDASADepartment' i want to add one more value as "[BUDASA].[Department].&[Others]" but i'm not able to figure out a way of do'ing it. Any Suggestions , Thanz !
View Replies !
View Related
Passing Parameter To Another Report Using Jump To URL Option In Reporting Services 2005
Hi, I tried to pass parameter from one report to another report. I can send the parameter using following option: I used jump to url option and write the following expression: ="javascript:void(window.open('http://hpsi-dev/Reports/Pages/Report.aspx?ItemPath=%2fNextGen+Reports%2fMAUA%2fSales+Order+Detail&rs:Command=Render&SalesOrderNumber="+Fields!SalesOrderNumber.Value+"'))" and it shows me the following in browser url http://hpsi-dev/Reports/Pages/Report.aspx?ItemPath=/NextGen+Reports/MAUA/Sales+Order+Detail&rs:Command=Render&SalesOrderNumber=SO43667 now the problem is how to get this ordernumber in my report any option ???pls urgent...any javascript function to take this no into my another report
View Replies !
View Related
Passing DataSet To MS Access Report
I have many MS Access reports that process recordsets obtained from a MySQL database, based on a user-selected date range. This uses VBA and input boxes. I'm now creating .aspx pages to get the user input via the web, and am successful in creating a DataSet. My .aspx.vb code includes using Automation to open the Access report in Snapshot Viewer (DoCmd.OutputTo). How do I pass the DataSet to MS Access to replace using recordsets? My VBA code in Access used to be this: Code Snippet Dim ws As Workspace Dim strConnection As String Dim dbs As Database Dim rst_chg As Recordset Set ws = DBEngine.Workspaces(0) strConnection = "ODBC;DSN=xxx;DATABASE=xxx;" _ & "SERVER=10.1.144.xxx;" _ & "UID=xxx;PWD=xxx;PORT=xxx;OPTION=0;" _ & "STMT=set wait_timeout=100000;;" Set dbs = ws.OpenDatabase("", True, True, strConnection) Set rst_chg = dbs.OpenRecordset("SELECT ...") 'process the recordset ... I'm thinking I should be able to eliminate most of this code and Set rst_chg = DataSet. I've been successful with using WriteXml in the .aspx.vb page and Application.ImportXML in my VBA to pass the data using XML, but this writes to the hard drive, and also creates a database in Access (overhead I would rather not have to deal with). Again, is there a way to open the DataSet directly in my VBA code? Thanks, Guy Rivers
View Replies !
View Related
Passing Column Name As A Query Parameter?
Hi, Is there a way to pass the column name as a query parameter?? If I use '@Question', like below, I get an error. If I change it to the actual name of the column 'Question1', it works fine. However, I need it to be dynamic so I can pass the column name [Question1, Question2, Question3, etc...] in and not have to define this for each question. Doesn't Work!! Code: SELECT 1.0 * SUM(CASE WHEN @ColumnName > 1 THEN 1 ELSE 0 END) / COUNT(*) AS 'Good', 1.0 * SUM(CASE WHEN @ColumnName = 0 THEN 1 ELSE 0 END)/ COUNT(*) AS 'OK', 1.0 * SUM(CASE WHEN @ColumnName < 0 THEN 1 ELSE 0 END) / COUNT(*) AS 'Poor' FROM tableA AS A INNER JOIN tableB AS B ON A.SessionID = B.SessionID WHERE (A.SurveyID = @SurveyID) AND (A.SubmitDate BETWEEN @BeginDate AND @EndDate) Works, but I need to pass in the column name dynamically. Code: SELECT 1.0 * SUM(CASE WHEN Question1 > 1 THEN 1 ELSE 0 END) / COUNT(*) AS 'Good', 1.0 * SUM(CASE WHEN Question1 = 0 THEN 1 ELSE 0 END)/ COUNT(*) AS 'OK', 1.0 * SUM(CASE WHEN Question1 < 0 THEN 1 ELSE 0 END) / COUNT(*) AS 'Poor' FROM tableA AS A INNER JOIN tableB AS B ON A.SessionID = B.SessionID WHERE (A.SurveyID = @SurveyID) AND (A.SubmitDate BETWEEN @BeginDate AND @EndDate)
View Replies !
View Related
Passing Date Parameter To Oracle Query
ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD') When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results. But when I run the report from Preview, I get no results at all no matter what format I use in the function. Any chance any of you have seen this and know how to work with it?
View Replies !
View Related
Passing MS SQL2005 Query Result Into Javascript Function
I'm selecting the last latitude & longitude input from my database to put into the Google maps javascript function. This is how I retrieve the longitude: <asp:SqlDataSource ID="lon" runat="server" ConnectionString="<%$ ConnectionStrings:LocateThis %>" SelectCommand= "SELECT @lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)"> </asp:SqlDataSource> I wish to input the latitude & longitude into the JAVASCRIPT function (contained in the HTML head before the ASP) something like this: var map = new GMap2(document.getElementById("map"));var lat = <%=lat%>;var lon = <%=lon%>;var center = new GLatLng(lat,lon);map.setCenter(center, 13); However, lat & long do not contain the retrieved result but rather a useless System.something string. How do I assign the retrieved results to these variables and port them over to Javascript as required? Many thanks!
View Replies !
View Related
MS Access Front End To SQL Server Problem Passing Form Value To Report
I just changed my Access 2002 database to a SQL Server ADP project. Ihad a form where the user entered a value into a text box and when acommand button on the form was clicked a Report was opened. The reportsrecord source is a query. The query uses the value from the form textbox to restrict the query.Table name = EggsTableone of the columns in the table is named: EggColorForm name = EggColorFormForm text box name = ColorTextBoxThis sql worked for the query before I converted to SQL:SELECT EggsTable.EggColorFROM EggsTableWHERE (((EggsTable.EggColor)=[Forms]![EggColorForm]![ColorTextBox]));This no longer works. Can I change the syntax somehow to get this towork? I tried dropping the brackets around the word "Forms", I trieddropping all the square brackets, etc., nothing worked.I also tried just opening the report with the report's Server Filterproperty set to:EggColor=N'Forms.EggColorForm.ColorTextBox'I tried using the Report's open event to pass the form value directlyto the report. I tried setting a variable from the text box value onthe form. So far, nothing works. Any ideas?
View Replies !
View Related
Passing Object Variable As Input Parameter To An Execute SQL Task Query
I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are: 1) In the First Execute SQL Task, I want to store a single row result of @@identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 ) 2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work). Please give me solutions for the above two..
View Replies !
View Related
Passing Value From A Access Form To A Query
Frustration has gotten the best of me on this one. Can anyone help? I need to pass the current value in an Access Data Project (back-end is MS SQL) text field to the where condition in an SQL using VBA. This is what I have, but does not work. Private Sub Command44_Click() Me.SS.SetFocus Dim strSQL As String Dim strSSecurity As String strSSecurity = Me.SS strSQL = "Update Employees Set employees.PositionID = '',employees.jobcode = '' Where employees.ss = strSSecurity" DoCmd.RunSQL strSQL End Sub It is supposed to take the current Social Security number from the form and match it against the employees table. Once it finds the matching record it should update the PositionID and JobCode fields to '' (empty string) But it doesn't Anyone with any ideas? Thanks Dan
View Replies !
View Related
Input Parameter To Function In SQL Query
I am trying to use a Execute SQL task in which I call a query and get back a scalar value. I THINK I have it set up correctly, yet I am getting a very unhelpful error message of: Error: 0xC002F210 at Determine Previous Trade Date, Execute SQL Task: Executing the query "SELECT[Supporting].[dbo].[fGetOffsetTradeDate](?, -1) AS [PreviousTradeDate]" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. The Parameter Mapping has a single INPUT entry of data type DATE mapped to parameter 0. The Result Set property (in General) is set to Single Row and there is a single entry in the Result Set config which maps [PreviousTradeDate] to a variable. Odd thing is, if I replace the ? in the query with a date (say '03/24/2006') everything works fine. This would indicate that my query syntax is fine.
View Replies !
View Related
Is It Possible To Set The Query Parameter By Giving A Function Name
I am trying to do a query similar to this: SELECT * FROM TRANSACCTION_HISTORY WHERE TXN_DATE=@RepDate For the query parameter @RepDate, I would like to pass a function to it. The function is a calculation of date based on today's date (e.g. Today() function to be simple). So that users don't have to type in the date every time. Now the question is: is it really possible to do that in RS? Because I am getting a type conversion error, when I put Today() in the Define Query Parameters disalog box. Thanks for helping!
View Replies !
View Related
Is There A Way To Loop Over Report Parameters, And Format Them Before Passing Them Onto The Query
Good morning all, I have a report which is getting its parameters from an ASP.net page. My ASP developer wants to send in simple values, such as the list 1,2,3,4 for a parameter. However my report needs that list to look like [CD RSRC].[RSRC].&[1], [CD RSRC].[RSRC].&[2], [CD RSRC].[RSRC].&[3], [CD RSRC].[RSRC].&[4]. Is there any way, on the report services side, to capture an incoming report parameter, parse it, loop over the parsed values and format them? I don't think there is, but I wanted to check before I go back to the developer and tell him he has to send in tuple lists. Thanks, Kathryn
View Replies !
View Related
Report Server Parameter Datasource Access
Hi All, Im guessing this is something simple as Im very new to reporting services. The issue is that I create a report, that I can preview and I can also execute myself via a web browser. But when I have someone else attempt to access the report via a browser, they are getting some sort of access issue to the datasources for the parameters. The exact error message is: An error has occurred during report processing. (rsProcessingAborted) Query execution failed for data set 'RegionList'. (rsErrorExecutingCommand) For more information about this error navigate to the report server on the local server machine, or enable remote errors As far as I can tell, Ive given that userid all of the privileges they need against the SQL server database itself (from which the parameter data is pulled), so Im not sure what the issue may be. I saw an earlier post that looked like the same problem, but when I checked to see if that solution would help (making sure all of the parameters were present as both report and query parameters in the code), I found that was not the issue as all of the parameters were present in both places. In case it helps, Ive pasted the logic related portions of the code code from the .rdl file below. Thanks so much for any help! <?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <DataSources> <DataSource Name="IncoconxSQLServer"> <ConnectionProperties> <IntegratedSecurity>true</IntegratedSecurity> <ConnectString>Data Source=B0015C5212654;Initial Catalog=INFOCONX</ConnectString> <DataProvider>SQL</DataProvider> </ConnectionProperties> <rdataSourceID>221eecfd-5570-4927-b0d0-f1f08c8ca7b6</rdataSourceID> </DataSource> </DataSources> <BottomMargin>1in</BottomMargin> <RightMargin>1in</RightMargin> <ReportParameters> <ReportParameter Name="RegionList"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>RegionList</DataSetName> <ValueField>region</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> <Prompt>Region</Prompt> <ValidValues> <DataSetReference> <DataSetName>RegionList</DataSetName> <ValueField>region</ValueField> <LabelField>region</LabelField> </DataSetReference> </ValidValues> <MultiValue>true</MultiValue> </ReportParameter> <ReportParameter Name="ProductList"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>ProductList</DataSetName> <ValueField>prd_grp</ValueField> </DataSetReference> </DefaultValue> <AllowBlank>true</AllowBlank> <Prompt>Product</Prompt> <ValidValues> <DataSetReference> <DataSetName>ProductList</DataSetName> <ValueField>prd_grp</ValueField> </DataSetReference> </ValidValues> <MultiValue>true</MultiValue> </ReportParameter> <ReportParameter Name="SourceList"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>SourceList</DataSetName> <ValueField>source</ValueField> </DataSetReference> </DefaultValue> <Prompt>Source</Prompt> <ValidValues> <DataSetReference> <DataSetName>SourceList</DataSetName> <ValueField>source</ValueField> <LabelField>source</LabelField> </DataSetReference> </ValidValues> <MultiValue>true</MultiValue> </ReportParameter> <ReportParameter Name="START_DATE"> <DataType>DateTime</DataType> <DefaultValue> <Values> <Value>=Today()</Value> </Values> </DefaultValue> <Prompt>START_DATE</Prompt> </ReportParameter> <ReportParameter Name="END_DATE"> <DataType>DateTime</DataType> <DefaultValue> <Values> <Value>=Today()</Value> </Values> </DefaultValue> <Prompt>END_DATE</Prompt> </ReportParameter> </ReportParameters> <rd:ReportID>a66903d1-5595-4002-93df-febeb098c7e2</rd:ReportID> <LeftMargin>1in</LeftMargin> <DataSets> <DataSet Name="InfoConnxTest"> <Query> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> <CommandText>select region, prd_grp, prod_type, sum(case when app_submt_dt between (@START_DATE) and (@END_DATE) then 1 else 0 end) as products_recieved, sum(case when dsptn_ts between (@START_DATE) and (@END_DATE) and cur_stat_cd = 'BA-APPR' then 1 else 0 end) as products_approved, 0 as products_worked, sum(case when dsptn_ts between (@START_DATE) and (@END_DATE) and substring(cur_stat_cd,1,4) = 'DECL' then 1 else 0 end) as products_declined, sum(case when dsptn_ts between (@START_DATE) and (@END_DATE) and substring(cur_stat_cd,6,4) = 'WTHD' then 1 else 0 end) as products_withdrawn, sum(aprv_am) as approved_amount, sum(case when dsptn_ts between (@START_DATE) and (@END_DATE) and cur_stat_cd = 'BA-APPR' then aprv_am else 0 end) as approve_amount_agg from INFOCONX.dbo.APPSEC002 where (app_submt_dt between (@START_DATE) and (@END_DATE) or dsptn_ts between (@START_DATE) and (@END_DATE)) and source IN (@SourceList) and region IN (@RegionList) and prd_grp IN (@ProductList) group by region, prd_grp, Prod_type order by region, prd_grp, prod_type</CommandText> <QueryParameters> <QueryParameter Name="@START_DATE"> <Value>=Parameters!START_DATE.Value</Value> </QueryParameter> <QueryParameter Name="@END_DATE"> <Value>=Parameters!END_DATE.Value</Value> </QueryParameter> <QueryParameter Name="@SourceList"> <Value>=Parameters!SourceList.Value</Value> </QueryParameter> <QueryParameter Name="@RegionList"> <Value>=Parameters!RegionList.Value</Value> </QueryParameter> <QueryParameter Name="@ProductList"> <Value>=Parameters!ProductList.Value</Value> </QueryParameter> </QueryParameters> <DataSourceName>IncoconxSQLServer</DataSourceName> </Query> <Fields> <Field Name="Region"> <rd:TypeName>System.String</rd:TypeName> <DataField>region</DataField> </Field> <Field Name="Prd_Grp"> <rd:TypeName>System.String</rd:TypeName> <DataField>prd_grp</DataField> </Field> <Field Name="Prod_Type"> <rd:TypeName>System.String</rd:TypeName> <DataField>prod_type</DataField> </Field> <Field Name="products_recieved"> <rd:TypeName>System.Int32</rd:TypeName> <DataField>products_recieved</DataField> </Field> <Field Name="products_approved"> <rd:TypeName>System.Int32</rd:TypeName> <DataField>products_approved</DataField> </Field> <Field Name="products_worked_1"> <rd:TypeName>System.Int32</rd:TypeName> <DataField>products_worked</DataField> </Field> <Field Name="products_declined"> <rd:TypeName>System.Int32</rd:TypeName> <DataField>products_declined</DataField> </Field> <Field Name="products_withdrawn"> <rd:TypeName>System.Int32</rd:TypeName> <DataField>products_withdrawn</DataField> </Field> <Field Name="approved_amount"> <rd:TypeName>System.Decimal</rd:TypeName> <DataField>approved_amount</DataField> </Field> <Field Name="approve_amount_agg"> <rd:TypeName>System.Decimal</rd:TypeName> <DataField>approve_amount_agg</DataField> </Field> <Field Name="products_worked"> <rd:TypeName>System.Int32</rd:TypeName> <Value>=Fields!products_approved.Value+Fields!products_declined.Value</Value> </Field> <Field Name="Prod_Pct_Approved"> <Value>=Fields!products_approved.Value/Fields!products_worked.Value</Value> </Field> </Fields> </DataSet> <DataSet Name="RegionList"> <Query> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> <CommandText>select distinct region from infoconx.dbo.appsec002 order by region</CommandText> <DataSourceName>IncoconxSQLServer</DataSourceName> </Query> <Fields> <Field Name="region"> <rd:TypeName>System.String</rd:TypeName> <DataField>region</DataField> </Field> </Fields> </DataSet> <DataSet Name="ProductList"> <Query> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> <CommandText>select distinct prd_grp from infoconx.dbo.appsec002 order by prd_grp</CommandText> <DataSourceName>IncoconxSQLServer</DataSourceName> </Query> <Fields> <Field Name="prd_grp"> <rd:TypeName>System.String</rd:TypeName> <DataField>prd_grp</DataField> </Field> </Fields> </DataSet> <DataSet Name="SourceList"> <Query> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> <CommandText>select distinct source from infoconx.dbo.appsec002 order by source</CommandText> <DataSourceName>IncoconxSQLServer</DataSourceName> </Query> <Fields> <Field Name="source"> <rd:TypeName>System.String</rd:TypeName> <DataField>source</DataField> </Field> </Fields> </DataSet> </DataSets> </Report>
View Replies !
View Related
Passing Parms From .Net And Using Report/query Parms In Report Viewer Control
I have a dozen Web reports that work when i pass parms via the parm collection from an app to the report and mapped as report parameters. Nice. Another dozen reports that work with reprot viewer and query parameters ("@") mapped and the automagic dropdown boxes to allow selection and regeneration of reports within the RV control. Nice. However, when i try to create a program that passes a parm via the collection AND use query/report parms inherent in the report viewer control, it works in the DEV environment but not PRODUCTION. It works on REPORT SERVER when i unhide the program passed parm but not when i try it from the app when hidden. I have read several of the reporting books on integrating RS with .Net apps, and they all seem one or the other, programatically with SETPARAMETER or with the query/report parms mapped in the RV control. I have not seen any examples where you can use both at same time. Maybe, i missed something, but can you mix the two - .Net app parms AND report viewer parms ?
View Replies !
View Related
How To Get Parameter Passed To SQL Query From Access
I am new to sql and very familiar with access. I am using a verylarge database(130M records) in ms sql2000 and think I need to frontend it with access for reports and forms, etc..I have some questions:1) Is there a way in SQL to prompt a user for input at the running ofa query like the [what is your name] construct in an access query?2) I can't seem to create calculated fields in a view in sql. Inaccess I put "total:=hours*rate" and a new column would be createdwhich would contain the total.3) I normally would create a form in access from which the accessqueries would pull their variables and then insert those variables inthe queries or reports, etc. How would this be accomplished usingsql.4) Lastly and probably most basic. How does a "real" sql developercreate reports and forms to interact with the enduser? I am usingaccess as a front end, but am open to suggestions.Thanks,Brad
View Replies !
View Related
From Query - Report Parameter
What needs to be done to allow a user to input data in a "From query report parameter"? The "From query" option was used to show all possible selections the user can do but what if the user would now like to select for partial key...i.e.: like '%tss%' I would like to avoid the Multi-Value parameter option. NOTE: The report is done through Microsoft Visual Studio / RDL. Thanks,
View Replies !
View Related
|
TRACKER
