Hidden Parameters In SQL Server 2000 Reporting Services.
Jun 27, 2007
Hi all,
I am having a problem. I know of hidden parameters in SQL 2005 RS, but I need to have this functionality as well for SQL 2000 RS. I read on the Net that MS SQL 2000 RS Service Pack 1 adds this functionality. I have installed SP4 for SQL 2000, then I installed RS SP1 on my laptop, but I don't see that functionality in VS 2003 Enterprise Architect. I even installed RS SP2 on my other machine, but still no joy. Am I missing something here or is Microsoft lying about this functionality? My client will be upgrading to SQL 2005 sometime in the near future (if they don't change their minds again), but I need to implement this kind of functionality now. I can't wait for them to upgrade to SQL 2005. This parameter I need to hide in the reports is necessary to ensure the reports show data by specific department. I'll implement it in such a way that when they call the report from the web application, I'll pass the departmentID to the report via the query string.
P.S. It is not possible for me to achieve this using JOINs in the queries. The parameter lists themselves depend on this value.
I need to know how, and if, possible to create a multiple value parameter in SQL Server 2000 Reporting Services. I need this for a client of mine. Any help/tips/etc will be greatly appreciated.
I'm trying to create reports in RS2005 using AS2000 as my data source. I understand that if I use RS2005 on AS2000, I wont be able to enjoy the OLAP based parameters as in using AS2005. Does anyone know an easy way to easily use Parameters in RS2005 while still using AS2000?
I am trying to figure how to programmatically pass parameters to the report in SQL Reporting Services using web service API and then send this report to the printer. I found code in C# on how to print and it does work very well. I am hoping to add report parameters to this module. Any ideas, samples or help would really be appreciated.
here is the code for printing
using System; using System.Drawing; using System.Drawing.Imaging; using System.Drawing.Printing; using System.IO; using System.Web.Services.Protocols; using PrintReport.reportserver; using System.Runtime.InteropServices; // For Marshal.Copy
namespace PrintReport { /// <summary> /// A simple console application that demonstrates one way to /// print Reporting Services reports to a printer. /// </summary> class app { /// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main(string[] args) { PrintExample pe = new PrintExample(); // The name of the printer should be added here; // this could be a local or network printer. pe.PrintReport(@"\jacc-fs120S2500 PCL IT"); } }
class PrintExample { ReportingService rs; private byte[][] m_renderedReport; private Graphics.EnumerateMetafileProc m_delegate = null; private MemoryStream m_currentPageStream; private Metafile m_metafile = null; int m_numberOfPages; private int m_currentPrintingPage; private int m_lastPrintingPage;
public PrintExample() { // Create proxy object and authenticate Console.WriteLine("Authenticating to the Web service..."); rs = new ReportingService(); rs.Credentials = System.Net.CredentialCache.DefaultCredentials; }
// Build device info based on the start page deviceInfo = String.Format(@"<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>", "emf");
//Exectute the report and get page count. try { // Renders the first page of the report and returns streamIDs for // subsequent pages firstPage = rs.Render( reportPath, format, null, deviceInfo, null, null, null, out encoding, out mimeType, out reportHistoryParameters, out warnings, out streamIDs);
// The total number of pages of the report is 1 + the streamIDs m_numberOfPages = streamIDs.Length + 1; pages = new Byte[m_numberOfPages][];
// The first page was already rendered pages[0] = firstPage;
for (int pageIndex = 1; pageIndex < m_numberOfPages; pageIndex++) { // Build device info based on start page deviceInfo = String.Format(@"<DeviceInfo><OutputFormat>{0}</OutputFormat><StartPage>{1}</StartPage></DeviceInfo>", "emf", pageIndex+1); pages[pageIndex] = rs.Render( reportPath, format, null, deviceInfo, null, null, null, out encoding, out mimeType, out reportHistoryParameters, out warnings, out streamIDs); } }
private void pd_PrintPage(object sender, PrintPageEventArgs ev) { ev.HasMorePages = false; if (m_currentPrintingPage <= m_lastPrintingPage && MoveToPage(m_currentPrintingPage)) { // Draw the page ReportDrawPage(ev.Graphics); // If the next page is less than or equal to the last page, // print another page. if (++m_currentPrintingPage <= m_lastPrintingPage) ev.HasMorePages = true; } }
// Method to draw the current emf memory stream private void ReportDrawPage(Graphics g) { if(null == m_currentPageStream || 0 == m_currentPageStream.Length || null ==m_metafile) return; lock(this) { // Set the metafile delegate. int width = m_metafile.Width; int height= m_metafile.Height; m_delegate = new Graphics.EnumerateMetafileProc(MetafileCallback); // Draw in the rectangle Point destPoint = new Point(0, 0); g.EnumerateMetafile(m_metafile,destPoint , m_delegate); // Clean up m_delegate = null; } }
private bool MoveToPage(Int32 page) { // Check to make sure that the current page exists in // the array list if(null == this.RenderedReport[m_currentPrintingPage-1]) return false; // Set current page stream equal to the rendered page m_currentPageStream = new MemoryStream(this.RenderedReport[m_currentPrintingPage-1]); // Set its postion to start. m_currentPageStream.Position = 0; // Initialize the metafile if(null != m_metafile) { m_metafile.Dispose(); m_metafile = null; } // Load the metafile image for this page m_metafile = new Metafile((Stream)m_currentPageStream); return true; }
private bool MetafileCallback( EmfPlusRecordType recordType, int flags, int dataSize, IntPtr data, PlayRecordCallback callbackData) { byte[] dataArray = null; // Dance around unmanaged code. if (data != IntPtr.Zero) { // Copy the unmanaged record to a managed byte buffer // that can be used by PlayRecord. dataArray = new byte[dataSize]; Marshal.Copy(data, dataArray, 0, dataSize); } // play the record. m_metafile.PlayRecord(recordType, flags, dataSize, dataArray);
return true; }
public byte[][] RenderedReport { get { return m_renderedReport; } set { m_renderedReport = value; } } } }
Is there any way to pass a dataset parameter in RS 2000? Basicly, I have some data in my web page and want to print it. But I don't want to create another page to pass parameters to RS. I want to print the page as it is with the data already in the page, so if there is a way I can send the data through a dataset to the report directly, would be great. Is there?
People, help me with this? Already broke my brain.
I have some data on Sybase 9.0.2.3198, i'm tryin to render report with MSSQL ReportingServices 2000 Eval SP2.
the report designer is fyiReportDesigner 3.0 - and I can do preview with it.
after deployment I'm tryin to render it, RS gives me an error:
An error has occurred during report processing. (rsProcessingAborted) Get Online Help Query execution failed for data set 'Data'. (rsErrorExecutingCommand) Get Online Help Column '@start_date' not found
I tried connect through ODBC and OLEDB both - same result.
ps I'm gonna kill somebody real soon.
the sql:
WHERE PERIOD.BEGINDATE between CAST(@start_date as timestamp) and CAST(@end_date as timestamp)
I have a working set of parameters, when they are all visible. However, when I change the second parameter to hidden it stops working.
First Parameter - Contains static values 'MTD' & Daily Second Parameter - has available values and a default. Set up to be invalidated when the First parameter changes... like i say it works beautifully when not hidden. Third Parameter - has an expression takes the value of the 2nd parameter value, removes the first character off and then converts the remainder to a date and uses it as its default value.
It based on this webpage: URL....However, when i set the second parameter to hidden it no longer works.
We are using SSRS 2005.We designed one report.This report contains Table and chart controls .When Table is hidden,chart only is visible, this chart is going to show only on last page.There is a white space in top of the chart while hiding the table control.
Consider for an Example Report one tables and one chart . We want to show only chart,So we made Table's visible property is false.Total Number of pages is five and table has only four pages and chart has only one page that is in last page. Now the report showing Four pages are empty with header and footer and last page have a chart with header and footer.
Any suggestions how to avoid the empty pages?
I tried to Filter property to filter the table.Same empty page with header and footer is only visible
I have an RDL report that has tables which are hidden based on expression. When I chose Run, they are appropriately hidden or visible. However, when I chose print, all tables are printed, even if they are invisible in the run version.
We have 1000s of SSRS reports hosted on the SQL 2008 R2. All reports are supposed to have all parameters "hidden". Random reports have been reported to expose the parameter when developers accidently deploy to test servers. Is there a way to identify the reports and the parameter property (Hidden, Visible, Internal) from the Report server Database?
I checked executionlog2 but can not find the parameter property.
There is a multi value parameter called "include" in the report where "Allow Multiple Values" is checked and it has 4 Available values as shown in the attached screen shots and preview of the report is also shown .There is no data set for this parameter and the values will get displayed on the report based on the visibility condition set in the report.Example : If first value is selected then 1 is passed and based on the visibility condition set in the report - the report output is displayed.None is default value and has value 4 and when the report is run with this option i.e. "None" then rest three parameter values are not applicable .
Requirement : -When the end user selects (Select All) Check box then (None) -check box must be disabled or must not appear for selection for the end user -When the end user selects check boxes either of the first three except None then also None check box must be disabled or must not appear for -selection for the end user -when the end user selects a combination of first three then also None check box must be disabled or must not appear for selection for the end user -The None is set as default with a value as 4 and is applicable only when the user does not select either of the first three values and the report will run.
I have a Reporting Services 2005 report which uses cascading parameter multi-select drop-down boxes. The report runs perfect after making initial selections. However, each subsequent parameter selection causes the page to refresh to a blank page until the View Report button is clicked. What I would like to happen (which should be the norm) is for the user to be able to make all necessary selections without the page auto-refreshing after each selection. The user would then click the View Report button to regenerate the report. We are currently using reports which contain up to 5 cascading parameters. This becomes very time-consuming when the page refreshes after each selection.
Has anyone come across a solution or code alternative to this problem??
This is the code which I have written in code window.
Public Shared Function CalcLocalFactor(ByVal CalcLifeCode As Integer, ByVal CalcFiscalAge As Integer, ByVal CalcLifeYearsUsed As Double, ByVal CalcLocConvention As String, ByVal CalcSRate As Integer) As Double Dim locCalcFiscalAge As Integer = 0 Dim locFactor As Double= 1.0 Dim locFactor1 As Double = 1.0 Dim REM1 As Integer = 1 Dim DEP As Double = 0 Dim YR As Integer Dim HALF_YEAR As Double Dim LINEAR As Double Dim MACR As Double If CalcFiscalAge > CalcLifeCode + 1 Then locCalcFiscalAge = 0 locFactor = 1.0 End If If (CalcLocConvention <> "HALF-YEAR" And CalcLifeYearsUsed < CalcLifeCode) Then locFactor = Math.Round((CalcLifeYearsUsed / CalcLifeCode), 4) End If If (CalcLocConvention = "HALF-YEAR") Then for YR = 1 to CalcFiscalAge step 1 If YR = CalcLifeCode + 1 Then locFactor1 = 1 Exit For End If If (YR = 1 Or YR = CalcFiscalAge) Then HALF_YEAR = 2 Else HALF_YEAR = 1 End If LINEAR = Math.Round(REM1 / (CalcLifeCode - YR + 1.5) / HALF_YEAR, 4) MACR = Math.Round(REM1 / CalcLifeCode * CalcSRate / HALF_YEAR, 4) If MACR >= LINEAR Then DEP = MACR Else DEP = LINEAR End If locFactor1 = locFactor1 + DEP REM1 = 1 - locFactor1 locFactor = locFactor1 Next End If Return locFactor End Function
I'm calling this code in a Report Parameter like below:
It is working fine for the first record where as for other records, the value is not getting changed. i.e. the first records value is coming repeatedly for all other records also.
How can I dynamically change the parameter values of the function?
Parameter is not accepting directly the field names, hence I used other parameter to initialize the field and used that parameter for this.
Ex. Parameter Name ; FieldPurchDate (internal) FieldName : PURCHDATE Other parameter: FieldInDate (internal) FieldName : InDate
While initializing the new parameter CalcPurchDate,, I used an expression for this: Parameters!CalcFiscalAge.Value
=iif(Parameters!FieldPurchDate.Value is nothing, Parameters!FieldInDate.Value,Parameters!FieldPurchDate.Value)
and using this CalcPurchDate for processing of the parameter:
I have a report which takes around 5 seconds when run in BIDS but takes around 20 seconds when deployed on report server.Execution log says TimeDataRetrieval is around 3-4 seconds and rendering time is around 15-17 secs.From this report I am passing 8 parameters to a drill through report and there are 36 text boxes where I have defined these parameters for drill through action.All these parameters are populated in main dataset.When I deployed the same report without any drill through action and parameter, it takes 5 seconds.So I am suspecting that because of drill through parameters report is taking more rendering time on server.I am using 2008R2 and IE11.
Is it expected behavior that due to so many parameters for drill through action, report will take more rendering time?If yes, then why is it not taking same time in BIDS?
I am trying to migrate my reports from SQL server 2000 reporting services 32bit to 2005 64bit. I am following the migration steps that MS specified. Restored my Reportserver and ReportserverTempDB databases Then I was using the configure Report services to upgrade these databases but I always end up getting the follwoing exception when I run the upgrade on the "Database Setup" configuration for 'ReportServerTempDB' database System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases for database 'ReportServerTempDBTempDB'. No entry found with that name. Make sure that the name is entered correctly. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script) at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
It's driving me crazy, why is it looking for 'ReportServerTempDBTempDB' in the catalog instead of 'ReportServerTempDB'? Is it possible to migrate from 32bit to 64bit?
My requirement for the parameter is multivalue parameter with a text box. for example when user enters aa15 it need to include product aa15. when the user enters aa15, aa16, zz15 than it needs to include all the three products. the last case is when the user enters AA** than i need to inclued all the products start with AA. when i use default multivalue parameter with data source analytical services than i am getting a drop down box. I dont want that. I need a text box where user can enter the value. 1. In sql we have a like key word to query . for example select * from product where product like "AA%". what is equavalent mdx query to get such results ? 2.How to impliment the multivalue parameters without using dropdown box?
I have installed SQL Server 2000 Reporting Services with the default names for the virtual directories , (ReportServer and Reports). Is there any way i can change the name of these directories by running a script or some thing.
Also is there a way to use the Cascaded Style Sheet used in my ASP.NET application for the reports.
We had someone leave work and he was responsible for the reports for our company. He used SQL 2000 w/ Reporting Services. The connection to the report server was tied to his userid and password. Since his account was disabled the website will not connect to the reporting database because it was tied to his account. Is there a way to reset the connection creditions? I looked on the server and I can't find the Reporting Services Admin Tool.
i would like to install Report Services on visual studio 1.1 version
using sql server 2000. i currently have sql server 2005 and visual
studio 2.0 and there is no problems and so i remember how that
works.
With sql server 2000 i saw a link to a trial version of reporting services that i can download and install but after 120 days it's no good. I think that provides the gui interface to install reporting
services like the 2005 version except less robust. But i'm wondering with the proper service packs of sql server 2000 installed if i can
bypass download.
Can somebody help me how to install reporting services with sql server 2000, pre-req and software i need. I currently have the
is there a reporting services configuration tool for 2000 that comes with the non-trial version as the trial version did not install this tool - maybe it did not do so because i had the 2005 version already installed. ???
I am new to SQL server Reporting services and its configurations. I have installed and set up the SQL Server 2000 Reporting services in Windows 2003 Server. I have two websites up and running in the IIS excluding the default website. I have made the neccessary settings to enable one of the non- defualt website to run the reporting services. However when i run http://Servername/reports, The properties tab is not available. Is there any rights issues that i miss out? Please enlighten me. Thnx
A customer of mine, running Reporting Services 2000, has a linked report situation. The report is landscaped and prints ok from the base report. If the same User tries to print the Linked version of same report it prints as Portrait. The user has Admin privileges. I am told another customer is having the same trouble.
Is there any way to have a parameter hidden for a report without it being Read-Only? I want the user to be able to select a specific parameter within a report and have another parameter passed via URL.
I am using Windows XP PRO and trying to Install SQL SERVER 2000 Reporting Services.
I have already installed SQL SERVER 2000 developer Edition, sql server service pack 3, VS.NET 2003 and .NET frame work 1.1.
I downloaded the SQL Server 2000 Reporting Services Evaluation Edition. I expected that there are both Server Component and Client Components which are available to install because evaluation edition support all features of Enterprise Edition. However I only saw the Client Components available during setup. Where can I find the Server Components to install? I appreciate very much if I can get advice from any of you.
To Whom It May Concern:There is a piece of software that was needed to be installed to authorreports in Reporting Services for SQL 2000. That piece of software isVisual Studio .NET 2003. I do not have that piece but I am able toget the beta version of Visual Studio .NET 2005 from Microsoft. Willthat work for the installation and consequently the authoring part ofReporting Services? If not, could someone help me?Thank you in advance!Josh
Hi, When i try to send an email from SQL 2000 reporting services, i get error "The underlying connection was closed: Unable to connect to the remote server" . In a different environment the same settings is working. The only difference is that in first case, SMTP server and SQL 2000 reporting server are in different domain. But i have tested SMTP server connectivity from the reportserver using DOS command. It works good. Please help me
Hi, This may be a trivial question but neither SQL Server 2000 Reporting Services SP1 or SP2 wil install against our SQL Server 2005 Developer edition installation.
Our Version: Microsoft SQL Server Reporting Services Version 9.00.3027.00
Does anyone know whether the Services Packs are for SQL Server 2000 only? if so, whether the new functionality in SP2 will be made available to SQL Server 2005?
I've been trying to install SQL Reporting Services 2005 on Server 2000.
Everything installed fine until I looked at the Web Service Identity which is blank. I've tried changed the rsconfig file, and giving everyone access, that did not change anything. I look on the database server and noticed that there is no ASPNET account. Not sure if this means anything, but I'm lost as to how to get the Web Service Identity to show anything other than blank.
Hi I want to install SQL Server 2000 Enterprise Edition on Windows XP but it isn't installing. I have installed SQL Server 2000 Personal Edition but Reporting Services are not installing on the server. PLZ some one help me out.
Dear All, I am making web application using Asp.net C#(Visual Studio2005). And Sql server 2000 as a back End upgraded with service pack 3 and analysis service. Now I am trying to install sql server 2000 report services. But I run SQL2KRSSP1-ENG.EXE setup Then following error occurs The upgrade patch can not be installed by window installer because the program to be upgraded may be missing , or the upgrade patch may update a different version of the program Verify that the program to be upgraded exists on your computer and that you have the correct upgrade patch. I redownload the same exe to other location. And again run but getting same error Please Guide me or atleast give some help full link. thanks
