Hi,
I have started using SQL Server 2005 Reporting Services. I am using SQl Server authentication where user has to supply username and password .I want to know can I Pass the SQL authentication username and password in the URL as a parameter to report.
Nikhil Jahagirdar
Hi, I have created an insert stored procedure which inserts a userid into an sql server 2005 field of datatype uniqueidentifier.
What datatype would the parameter be in my c# code?
do i pass it in as an object datatype?
the code is below....
1 conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
2
3 cmd = new SqlCommand("spInsKeyswap_history", conn);
4
5 cmd.CommandType = CommandType.StoredProcedure;
6
7 MembershipUser mu = Membership.GetUser(User.Identity.Name);
8
9 object gdUserID = mu.ProviderUserKey;
10
11
12
13
14
15 cmd.Parameters.Add("@user_title", SqlDbType.VarChar, 50);
16
17 cmd.Parameters.Add("@first_name", SqlDbType.VarChar, 20);
18
19 cmd.Parameters.Add("@last_name", SqlDbType.VarChar, 50);
20
21 cmd.Parameters.Add("@email", SqlDbType.VarChar, 50);
22
23 cmd.Parameters.Add("@birthday", SqlDbType.VarChar, 15);
24
25 cmd.Parameters.Add("@alternate_number", SqlDbType.VarChar, 50);
26
27 cmd.Parameters.Add("@msisdn", SqlDbType.VarChar, 50);
28
29 cmd.Parameters.Add("@call1", SqlDbType.VarChar, 50);
30
31 cmd.Parameters.Add("@call2", SqlDbType.VarChar, 50);
32
33 cmd.Parameters.Add("@call3", SqlDbType.VarChar, 50);
34
35 cmd.Parameters.Add("@new_sim_msidn", SqlDbType.VarChar, 50);
36
37 cmd.Parameters.Add("@dealer_id", SqlDbType.UniqueIdentifier);
38
39 cmd.Parameters.Add("@status_code", SqlDbType.Int);
40
41 cmd.Parameters.Add("@support_id", SqlDbType.Int);
42
43 cmd.Parameters.Add("@return_value", SqlDbType.Int);
44
45 cmd.Parameters["@user_title"].Value = txtTitle.Text.ToString();
46
47 cmd.Parameters["@first_name"].Value = txtFirstName.Text.ToString();
48
49 cmd.Parameters["@last_name"].Value = txtLastName.Text.ToString();
50
51 cmd.Parameters["@email"].Value = txtEmailAddress.Text.ToString();
52
53 cmd.Parameters["@birthday"].Value = txtBirthday.Text;
54
55 cmd.Parameters["@alternate_number"].Value = txtAlternate.Text.ToString();
56
57 cmd.Parameters["@msisdn"].Value = txtNew.Text.ToString();
58
59 cmd.Parameters["@call1"].Value = txtCall1.Text.ToString();
60
61 cmd.Parameters["@call2"].Value = txtCall2.Text.ToString();
62
63 cmd.Parameters["@call3"].Value = txtCall3.Text.ToString();
64
65 cmd.Parameters["@new_sim_msidn"].Value = txtOld.Text.ToString();
66
67 //get logged in users user_id from membership
68
69 cmd.Parameters["@dealer_id"].Value = gdUserID;
70
71 cmd.Parameters["@status_code"].Value = 1;
72
73 cmd.Parameters["@support_id"].Value = 0;
74
75
76
77 cmd.Parameters["@return_value"].Direction = ParameterDirection.ReturnValue;
78
79 cmd.ExecuteNonQuery();
80
81 int returnValue = (int)cmd.Parameters["@return_value"].Value;
82
83 conn.Open();
84
85
86
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.
Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks
I have multiple databases, one for each client. I created a master report. Then I created links to this report for each client database in separate client-specific folders. I parameterized the connection string so that the server name and the database name are parameters of the linked report. The connection string is: ="data source=" & Parameters!SrvrName.Value & ";initial catalog=" & Parameters!DbName.Value.
The problem is that the security for now is Windows Authentication. (We are in the testing phase). However, my users will be both internal (company) and external (client) users. We do not want to set up database user IDs for each client user. I could create a single SQL account that has read access to all databases, relying on the Reporting Services security. But I would prefer to create separate SQL accounts for each database as the information is sensitive. Then I would probably pass that information in as a parameter to the report as well. Either way, I would have to include the password somehow. But, if I do that, I'm not sure how to include the password parameter. I would like it to be secure and passing it in as a parameter is not very secure.
I would really appreciate some suggestions on how to proceed.
I have a value I need to show on every report so I want to create a report parameter. It appears the only way for a report to have a parameter is for it to be a variable in the SQL WHERE clause. This value is not in the database so can't be retrieved from SQL.
How can I pass this value to the report and display it in a textbox in the footer?
Thanks.
Hello
I have an ASP.NET application where I can filter data from an SQL DB by certain criterias. Users are now also able to click on a link which redirects them directly to the SQL Server Reporting Services application.
What I'd like to have is a possibility to pass an object (as string) from my ASP.NET app to the textbox of the search criteria from the Reporting Services app.
Example:
- ASP.NET appl.: The user selected a FileId=84 -> then he clicks on the hyperlink to the SQL RS
- The SQL RS appl. opens and the textbox FileId is filled with "84"
it of course isn't a problem to pass the FileId to the hyperlink the user clicks on, but is it possible that the SQL RS appl. can somehow read it from the URL and pass it to the textbox?
Or does someone have another solution?
Thanks for your help!
I have a report in SSRS 2005. I wish to pass a parameter called nIndex from the calling WinApp.
http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fMy-Reports%2fPrimarySuppliers&rs:Command=Render&rc: parameters=false&nIndex=82896
even tried the following url:
http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fMy-Reports%2fPrimarySuppliers&nIndex=82896
I tried to use the above url. The report doesn't pick the correct value of nIndex that I am passing as query string.
In Report Parameters page: nIndex is defined as a string parameter, marked as hidden, that allows nulls, and the default value is null.
I catch the command execution from SQL Profiler, and I always see the original value of 1, even though I am now passing 82896. I had initially given 1 as default value, when testing, now I need to pass this new value to get the latest data. I have built and deployed the report several times since then.
Any help is highly appreciated. Thanks.
Hello,
I have a main report that drills-through to a secondary report. The main report has parameters which can be selected (Dairy, Frozen, Fruits, etc.). If I make a selection (let's say Fruits) the second report displays just Fruits information (as would be expected).
However, when I select 'All' from the parameter, on the Main report, and then click to drill-through, the passed parameter is the first selection below 'All.' I cannot seem to pass 'All' to the second report.
What am I doing wrong?
Thank you for the help.
-Gumbatman
p.s. The datasets are based on an Analysis Services cube.
I have recently had a few reports that are built around the userid parameter start failing to pass the userid parameter. If I run the report and hard code the userid everything runs just fine, but I am not able to collect the userid when the user is logging into the report server. Any thoughts or suggestions?
R/ AA
In my form i have a list box and i need to display paticular records of all employees I selected.So please tell me how to pass multiple values from a single parameter.
currently i am able to pass single value through a parameter using following code
osqlreport.reportViewer1.ServerReport.ReportServerUrl = new Uri("http://localhost/reportserver");
osqlreport.reportViewer1.ServerReport.ReportPath = "/employee/5_0emplyeedata";
List<Microsoft.Reporting.WinForms.ReportParameter> paramList = new List<Microsoft.Reporting.WinForms.ReportParameter>();
string stritem;
stritem = Convert.ToString(this.lstSelectedData.Items[0]);
paramList.Add(new Microsoft.Reporting.WinForms.ReportParameter("paraempno", stritem, false));
osqlreport.reportViewer1.ServerReport.SetParameters(paramList);
osqlreport.reportViewer1.RefreshReport();
osqlreport.Show();
what changes do i need to do to make it work.(lstSelectedData is my list box)
I am creating a new application and just created a new database
Application: VB.net 2005/ ASP.net
Database: Sql Server 2005 with four tables
I need to set the userid and password on the database. How do I do that?
I want to be able to create a SQL connection object in my code and I have something like the following:
<CODE>
Dim objcon As New SqlConnection("server=serverName;uid=;pwd=;database=SomeDatabase")
</CODE>
but the "uid" and the "pwd" are not set on my database. what is an easy way to do this? thanks
I use DTSRun /Uuserid /Ppassword /Sservername .... in a batch process.
I always get the error message to run with a trusted connection. I can run the same batch process from a command line under my own domain id and it runs as if it had a trusted connection.
Will DTSRun run only with a trusted connection? Do I need to set something in the package to get SQL Server to run with a userid and password? Why does it always default to using a trusted connection. I need to know what I have to set to tell it that I am using a userid and password. I have included an example of how it's coded in the BAT file.
Example:
DTSRun /S"myserver" /Umyuser /Pmypass /N"Load_Data" /L "c:mylogsLoad_Data.log" /W"0"
When I tried to browse the reportserver url. The IE 7.0 asks me for Username and password. I feel, its some settings related to security. But other sites created are working fine. The virtual directories created using Repoting services configuration manager only ask me username and password.
Any suggestions.
Murali.
i want to create bar chart dynamically. how to pass the x axis values and y axis values in crystal report in runtime.
View 1 Replies View RelatedHow do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\PackageName.dtsx /Set Package.Variables[StartDate];"&Parameters!StartDate.Value
How do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\PackageName.dtsx /Set Package.Variables[StartDate];"&Parameters!StartDate.Value
Hi All,
While deploying reports on the reports server, it is asking to supply user name and password. When I entered the same, it didn't accept them. What do I need to do? Is there any specific setting which I need to configure?
An immediate reply will be highly appreciated.
Thanks
Sekhar
Hi,
I am using the sql server for creating the database. I want to create the database which is having userid and password. This I require since I wanted to restrict acces to database and also only authorise user to database can use the userid and password created at time of creation of database.
So how can I create the database which is having userid and password?
I want to set userID and Password for perticular database without affecting sql server userid and password in sql server 2000 :)
View 3 Replies View RelatedMy PC is Window XP Pro and I'm using Microsoft SQL Server 2005 Express and Microsoft SQL Server Management Studio Express.
My question is how to create a login userid and password under "SQL Server Authentication"? (as shown in http://www.findingsteve.net/print_screen.jpg)
Any tutorial about this I can read?
Hi
We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?
Query 1: (source View Query)
SELECT
V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID
FROM
V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE
WHERE
REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )
Query 2: (look up )
SELECT
GDR_PRODUCT_ID,
TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') ,
TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
V_RDP_GOLD_PRICE
where
GDR_PRODUCT_ID = :1 and
report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') AND
PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')
GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
please anyone give the sample control flow and how to pass the parameter?
Thanks & regards
Jeyakumar.M
My Required tables are
I am Passing one parameter Is UserID
Table Name : SC_Attendence
ColumnName Datatype
AttendanceID int CompanyID int AttendanceDate datetime SectionID int
Table Name : SC_AttendenceDetails
Columnname Datatype
AttendanceDetailID int AttendanceID int
UserID int
IsAttended bit
IsLate bit
Table : SC_UserSections
columnname Datatype
UsersSectionId int
UserId int
SectionId int
CreatedDate datetime
Month NoofAttendenceDays NoofPresentdays
January 25 21
Febrauary 22 19
March 25 22
Thanx in advance It's very urgent
When integrating SQL Reporting Services 2005 with Sharepoint Services 3.0, and you have a SRS report that runs over a view with the UserID as a parameter (to filter the records the user has access to), will the SharePoint ReportViewer passover the UserID to RS and RS to SQL?
Sharepoint security with Reporting Services stops at Item level or does it impersontate down to SQL? Should authenticating be Integrated or SQL ? Should the userid in SQL be exact match with Sharepoint? Or are they mapped somewhere?
Thank You!!
I need to create a basic report that displays the userid, username, email, status and need to update the status column to enabled when we select any particular username. And the username parameter comes from the available users from the database. How to do this?
Do I need to mention the where clause where username=@username in the query or we don't need any
Also I need to get the available distinct usernames from the database while entering the parameter username
/*DECLARE variables to use IN queries*/
DECLARE @intErrorCode INT
DECLARE @ErrorMessage VARCHAR(MAX)
DECLARE @UserID INT
SET @ErrorMessage = 'Completed.'
SELECT [userid] ,
[code].....
Basically first I need to find the userid, username, accountstatus, email for a given particular user. Then I need to update the accountstatus to enabled for that selected user.
Also I need to create auditing that from before update and after update the accountstatus.
Is it possible to include a text/string as suffix(append to the end of textbox) to an report parameter prompt string ? Something like this
Enter Date: [ Textbox ] format:mm/dd/yyyy
Enter Age:[Textbox ] e.g 50
I need to include text like the one that I have highlighted in blue. Could someone help me out?
Hi,
I have created a report with the report server project template.
the report is created from stoked procedure having defaut input parameters.
With visual studio, i publish my report on reportserver. whenever i access to my report on this url :
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render. the created report is with the default parameters.
I would like to know if i can transmet parameters for the stocked procedure to build the report with the request i want.
I tried to put parameter directly in the url in this way
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render&@region='toto'
but without success.
my error message is that one
An attempt was made to set a report parameter '@region' that is not defined in this report. (rsUnknownReportParameter)
My stocked procedure is :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[akli] @region varchar ='m'
as
select * from dbo.Report2 where region=@region
The request used to buid the report ?
DECLARE @region varchar
EXECUTE dbo.akli @region
What is wrong in that ?
Thanks for your help.
Arioule
Request is to have a Requirement number from the requirement form generate a report in Reporting Services with the requirement number as a filter.
I can set up the parameter - how does the value get there? Should I be asking this question in the Visual C# group?
Thanks!
Terry B
Hi,
I am having hard time in sending parameters to the report server through reportviewer control from my application. Could anybody help me on this issue?. Is there any way to send the parameters and also what kind of configuration do i need on my report project so that it can accept parameters from my application.
Thanks,
Good morning, all
I have a really cool Budget report which has two matrices on it. One is used/visible if the user selects four levels of grouping, and one is used if the user selects only three levels of grouping. The four choices for grouping are four fields, Par, FERC, Point, and Resource.
Since grouping is optional, I need a way to pass the parameter for a field that is not grouped on from the main report to the drillthrough report. For example if the user chooses to group on all fields but Point, I need to pass the report parameter that was used by the main report for Point to the drillthrough report as a report parameter.
So, I tried testing the three grouping levels values and if none were equal to "Point" then pass in the parameter by putting the following expression in the Parameter Value box for the Point parameter on the Navigation tab of the datacell of the main report that is the drillthrough link:
=iif(
Parameters!GroupLevel1.Value <> "Point"
and
Parameters!GroupLevel2.Value<>"Point"
and
Parameters!GroupLevel3.Value<>"Point"
,
Join(Parameters!PNTPoint.Value,",")
,
Fields!Point.UniqueName
)
But this is not working; the drillthrough report shows a blank dropdown for the Point parameter and I have to manually set it. Does anyone see an error in the syntax of the iif statement?
Any references, links, resources or good clean jokes would be greatly appreciated.
Thanks,
Kathryn
I'm trying to pass a comma delimited list of numbers to a parameter to use in an IN of my Where clause.
Dim MySqlParamSelected As New SqlParameter("@Selected", SqlDbType.Int) Cmd.Parameters.Add(MySqlParamSelected) MySqlParamSelected.Value = Session("intSelected")
WHERE tblSelected.Selected_ID IN (@Selected)
It will work if I only pass it one number (e.g. 78), but when I pass it more than one (e.g. 78,79) it fails.
Here is the error:
Msg 119, Level 15, State 1, Line 4Must pass parameter number 7 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
Does anyone know how I can correctly pass multiple numbers to use in my IN?
How do i pass a parameter to a stored proc using this command:
object SqlHelper.ExecuteScalar (SqlConnection connection, string spName, params object[] parameterValues)
let's say that my parameter name is @ID and the value is IDval ..
thanx
HiWe still use SqlServer 2000 and DTSs.My question is: Can I pass a parameter to a DTS and if yes then how ?We would like to plan our screen like this.The user will input a parameter on the screen/form and then the DTS willbe activated using the value inputed.We do have how to work around it if parameters can't be passed but itwould make things a lot easier and straightforward if we could.Thanks !David Greenberg
View 2 Replies View RelatedI see a lot of posts asking for help with this type of problem but cannot find an answer that works. After 24 hours of trying, using examples from the Microsoft documentation, it's been utterly impossible to pass a parameter to a report via URL.
To make sure I'm not fighting more than one problem at the same time, I've stepped away from the production report I was trying to produce and created a simple report with a parameter named "p1". The only field on this test report is a textbox that displays the value of the parameter "p1". If I set the report up so that the user is queried for a value for "p1", the report works as expected -- the parameter is echoed back on the report.
The same thing can't be done via the URL. If I follow the docs and tack something like &rs:Command=Render&p1=hello onto the URL, I'm prompted for a value and/or told 'p1 is missing a value', depending on the visibility and default options I set for the parameter in the report definition.
I've tried every possible combination of settings I can think of for a parameter in the report definition, and every single one fails. Is this a task that's beyond RS abilities?