Hi All
We're having a technical issue integrating the print reports into the portal. I'm hoping maybe some of your guys can help. We're using the RSClientPrint component to print the reports. The problem appears to be a conflict between the & in the URL and the & in the parameter value's cube reference. Below is an HTML code snippet. The 1st button works fine for us, it prints everything and has no & in the parameter's cube references. The second button references specific User and Employee values and therefore has & in the cube reference and generates an error. We tried encoding the & as & but it did not help. Thanks.
I'm trying to get client side printing to work in asp.net with reporting services 2005. I've got an aspx page to do printing as described here but when I try to render a report I get the following:
1. Print dialog box appears as expected
2. Press print
3. Dialog box appears saying "Print in progress"
4. After a few seconds another dialog box appears saying there was an error printing the report. Error code is 80004005.
I get the same error if I try to preview instad of print.
Does anybody know what this error is and how I can get the control to work? Its not clear to me what the version on the object statement should be. This example uses the version string from the control but I've also tried 1.0 with the same result. Its also not clear if I should be using RSClientPrint.Authenticate or not. I've tried both true and false with the same result.
We are using VB.NET 2005 to develop a WinForms application that uses SQL Server 2005 Reporting Services to produce reports. We want to print a large report programmatically without preview. RSClientPrint looked like a good solution but it produces an error when we try to use it.
Details... I added Interop.RSClientPrintLib.dll Version 1.0.0.0 to my application and used the code sample I found at: http://msdn2.microsoft.com/en-us/library/ms159195.aspx
The call to print looks like: RSClientPrint.Print("http://ourServer/reportserver", strReportPathParameters, "Report_name")
This call generates an error message: "An error occured during printing. (0x80004002)"
and throws an exception: "Specified cast is not valid"
I have an asp.net application which is launched from our B2B site. The primary purpose of the application is to allow our external clients access to reports, along with the ability to export and print. The RS2005 report viewer component was used within the application to render the reports. The problem is when the our client users click on the print button on the report viewer's tool bar, many of them are receiving a window asking them if they want to install "SQL Server". In reality, the ActiveX viewer, RSClientPrint, is what is trying to install. Our technical support staff is being flooded with calls due to this pop up. The users are very confused and concerned about installing "SQL Server." In addition, some users do not have the ability to install the ActiveX viewer, so they are not going to be able to print. We've experienced this in the past with ActiveX for Crystal. Finally, some users receive no message or feedback when they click on the print button for several minutes. We've determined the ActiveX is being installed during this time. We've had users wait as long as 7 minutes for the Print Dialog to come up.
1. We would like to place a message along with the link on the page where the report is rendered. Is there a way for us to provide a link to a self extracting file containing RSClientPrint?
2. Also is there any way to get around the issue where the client user does not have the ability to install the ActiveX? I've seen references on the forum to using SMS, but this is not possible since the users are external?
3. Is there any setting we can turn on so the users don't think the application has gone "out to lunch" while the ActiveX viewer is installed?
We are using the RSClientPrint component to print reports from our web application. When we use it from IE all works fine. But on using the FireFox the print method fails. I am using following code to print
Code Snippet
var oRSClientPrint = window.document.getElementById("RSClientPrint"); alert(oRSClientPrint); if (typeof oRSClientPrint.Print == "undefined") { alert("Unable to load client print control."); return; }
In FireFox above code fails by raising error message "Unable to load client print control.". As per me FireFox is not able to use the Print method of object, Can you help me on how we can make it to work with FireFox.
but I can't figure ou how to "remove" the RSClientPrint Class add-on in IE 6.
P.S. Why does something as simple as printing need to be an ActiveX control? There are all kinds of issues revolving around pushing this out using SMS for users without local admin permission.
I am using SQL 2005 with reporting services, I have very basic reports, most users can print fine with the print icon in the toolbar, some users get the Blue screen and have to reboot. They have the rsclientprint.dll loaded in the browser, we have deleted it and reloaded, what do I do ?
I have a Development Machine running Windows XP Professional SP2, with SQL Server 2005 including Reporting Services. I am developing an ASP.NET application using VS 2005, and am including Server Side Reports from SQL Server. Everything is working well, except when viewing the Reports.
The Reports when viewed from Reporting Services€™ Report Manager or from within the ASP.NET application only display the first page of the report, displaying the total number of pages as 0. As well, most of the functions on the report toolbar appear to be disabled. This occurs for any report viewed, even sample reports such as the Adventure Works Samples.
The problem only occurs when viewed from within Internet Explorer on the Development Machine. To overcome the problem, I have upgraded SQLServer to SP2, and removed and reinstalled Internet Explorer, but it made no difference. It appears to be related to the rsClientPrint ActiveX addon which should have been installed when the print icon was first selected. It appears not to have installed properly, but up to that point everything worked OK. Selecting the Print icon (or any other icon) since nothing happens. I€™m guessing here, but I suspect that the rsClientPrint addon needs to be reinstalled, but I don€™t know how to go about it. It is not listed within IE addon (where it is on other machines connecting to the Development Machine), nor is it listed within the €śC:WindowsDownloaded Program Files€?. But it is not being downloaded when the Print Icon is selected. I have checked all the Security settings for the environment to ensure that ActiveX programs are downloaded, and everything appears to be in order.
Would anyone be familiar with this problem or have any suggestions on how to go about resolving it? Any help would be appreciated as it is causing significant problems. Strange though, the reports appear fine when viewed from within the Server Manager.
Hello, since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE PROCEDURE [schema].[spCreateReportTest] @Name nvarchar(20)= ''
AS BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON set @slqSelectQuery = N'SELECT field1,field2,field3 from table' if (@Name <> '') begin set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + '''' end EXEC sp_executesql @slqSelectQuery end
Inside my business Intelligence Project I created: -the shared data source with the connection String - a data set : CommandType = Stored Procedure Query String = schema.spCreateReportTest When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter... So inside"Layout" tab, I added the parameter: Name allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chartÂ
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
I need "conditional" cascading parameters: In Report Manager when one changes parameter 1, parameter 2 get changed based on parameter 1. Optionally, one can also enter values to parameter 2 directly.
I was able to achieve this in SSRS 2000 (SP2) with the following setups. SSRS 2005 and SP1 no longer works - Parameter 2 always shows its default value regardless whether one select a value in Parameter 1 or not.
Parameter 1 available values: from query default values: non query (specify a value "<None>") Parameter 2 available values: Non query (no value specified) default values: from query (based on Parameter 1)
It seems to me that the default value in SSRS 2000 is considered as cascading parameter. But it is no longer the case in SSRS 2005.
Is this a SSRS 2005 bug? is there any other work arounds or suggestions?
I have parameters in my report. The user can choose the year, month and date (3 parameters). Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.
My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension . Does somebody know how to make it possible to set default values for this parameters?
Other question :
Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007". But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All". Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.
my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you  can not select null in multivalue parameter.How can i do it. i am using share point list.
Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?
I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1. Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence. Can you do this thru SSRS? Any help would be great. Thanks for your time in advance. Kent
Is it possible to fill a parameter list with values based on another parameter value? Here's what I have so far (which hasn't worked)... I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school. I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table). I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school. Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up... The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'. The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid. ...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter? Any help you can give me would be great!! Thank you
I am using reporting services 2012, Can we make visibility of report parameter dynamic, ie can we make parameter visible or hide on certain condition or its visibility depends on other parameters Is this feature  available in any other updated version of ssrs?Â
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 would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful.
I have a report parameter called para1 which is a drop-down list and what I want to do is display another report parameter based on the para1 selection.
So for example, para1 contains a, b, c choices. if a user selects b, I would like para2 to display but if the user selects a or c, I don't want the para2 to display.
Using SQL Server 2008R2 and Report Builder 3.0..I have an action set in a text box of a table. My intent is to pass the value of that text box (which is variable) to a sub-report in a popup window. Here's my code: URL....The parameter of the report I'm trying to open is @SONum.I'm guessing my error is involved in the formatting of how the value of the parameter is being passed. I've also seen examples where the report server and report values were parameterized, but I don't know where to define
Parameters!ServerAddress.Value anywhere.Do I need to have something set up a certain way within the report I'm opening? Here's the report Parameter settings on the report I'm trying to open.
I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter. If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank. However, sometimes this will automatically add.Â
Is this a bug in Visual Studio? How do I get around this?Â
I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. how to make this work?
Create Function HR.Equipment ( @startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())), @enddate Date = (Convert(Date,@StartDate-90) ) RETURNS TABLE AS RETURN ( SELECT EquipID, EmpName, IssueDate FROM HR.Equipment WHERE IssueDate <=@StartDate and IssueDate >=@EndDate ) GO
After running my ssis pkg for some time with no problems I saw the following error come up, probably during execution of a stored procedure ...
An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The formal parameter "@ReportingId" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.".
I see some references to this on the web (even this one) but so far they seem like deadends. Doe anybody know what this means? Below is a summary of how the variable is used in the stored proc.
The sp is declared with 21 input params only, none of them is @ReportingId. It declares @ReportingId as a bigint variable and uses it in the beginning of the proc as follows...
Code Snippet select @ReportingId = max(ReportingId) from [dbo].[GuidToReportingid] where Guid = @UniqueIdentifier and EffectiveEndDate is null
if @ReportingId is null BEGIN insert into [dbo].[GuidToReportingId] select @UniqueIdentifier,getdate(),null,getdate() set @ReportingId = scope_identity() END ELSE BEGIN select @rowcount = count(*) from [dbo].[someTable] where ReportingId = @ReportingId and...lots of other conditions
...later as part of an else it does the following...
Code Snippet if @rowcount > 0 and @joinsMatch = 1 begin set @insertFlag = 0 end else begin update [dbo].[GuidToReportingId] set EffectiveEndDate = getdate() where ReportingId = @ReportingId insert into [dbo].[GuidToReportingId] select @UniqueIdentifier,getdate(),null,getdate() set @ReportingId = scope_identity() end
...and before the return it's value is inserted to different tables.
I have created a menu report listing available reports and am setting the Jump to settings of each text box in the list to take the user to the relevent report. But I am having a problem setting up drill down to reports with multivalued parameters.
What I want to happen when the user drills down is for the report to open up with no parameter values selected. This works for single value parameters where I have set Null as the default value. But I have not been able to figure out how to do this with MVPs. Does anybody know if this is possible?
As a workaround I have been using Jump to URL (rather than Jump to report), which has no problem opening the report without having to pass any values for the MPV. But I do not like using this as I use different DEV/UAT and PROD environments so would have to amend the URL for each environment.
Seems like this should be possible... Any idea?
Cheers, Mike
PS: I should add I can Jump to reports with MVP without specifying a parameter in development studio but not when the report is published.
I have a parameter (hidden) that gets its value using an expression base on another parameter. When in the designer, the first time when the designer loads I can select the Parameter that controls the child parameter (expression lies in the default value section). The value changes.
When I change the parent parameter again, the value of the child parameter does not seem to change.
How can I make this parameter change automatically when the parent is changed ?
I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){ if (e.Exception == null) { string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString(); ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] ( @original_FormNum nvarchar(20), @NewRetVal INT OUTPUT ) AS SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int
Set @stoptrans = 'NO'
/* ---------------------- Search PART #1 ----------------------------------------------------- */ SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum ) SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )
IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/ -----This means the delete can't happen........... BEGIN
IF @AssignedFound > 0 AND @AvailFound = 0 BEGIN SET @NewRetVal = 1 END
IF @AssignedFound > 0 AND @AvailFound > 0 BEGIN SET @NewRetVal = 2 END
IF @AssignedFound = 0 AND @AvailFound > 0 BEGIN SET @NewRetVal = 3 END END
ELSE BEGIN DELETE FROM dbo.Form WHERE dbo.Form.FormNum=@original_FormNum
SET @NewRetVal = 0 ---Successful deletion END GO -------------------------------------------------------- When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.
I know i can accomplish this by setting a session variable on page load on the server side (Session["UserName"]= User.Identity.Name) and then use a session parameter, but I was wondering how to do it without using session variables, i was hoping the following code would work, but it doesn't. Thanks for any ideas.
I have a dataset listing distinct values for items (like 1, D10, M4, etc.) The WHERE statement in my query refers to unit IN(@Unit). I then have 2 report parameters to select 1) a date (datetime); and 2) a multivalue parameter to select one or all of the "units". I would like the second parameter to default to "Select All". Can someone tell me how to do this? I'm sure this is a fairly simple thing but I am really struggling. The report parameter is set as multivalue; My "available values" is set to "from query" and refers to my "unit" dataset and the value and label fields are set to "unit" (only field I bring into this particular dataset). The "Default Values" section is set to "from query" , the dataset is set to "unit" and the value is set to "unit". I can preview the report and select a date but the list of units comes up with all boxes unchecked, including "Select All". Any help will be much appreciated. Thanks.
