Sending Parameters From Reporting Svcs To Web Methods
Jan 18, 2007
I have this:
[WebMethod]
public DataSet EeoReport(DateTime start, DateTime end)
{}
i want to know how to send the date time parameters to this from RS using XML data source.
basically i need the <Query></Query> XML used to make this happen. So far i'v eonly been able to statically create parameters, but that does me no good, i want the users to be able to pick a date range from RS dynamically to get the report.
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know. What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in. This is the relationship of the data. I have at the highest group, an industry code, then a customer, then the part and then the fiscal year. What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!
industry Customer Year OCT NOV DEC 001 - Signposts M12345 Part 1 2006 5 6 2 2007 0 3 1
In the v1.2 CTP version, the DatabaseMetadata methods for getting information about objects in a database (i.e. getTables(), getColumns(), ...) errors if your current database connection is in a different database than the object you are quering.
Is this the intended behavior going forward?
In my case I have access to both database A and database B. My current connection is in database A , but I am looking up object in database B.
[junit] The database name component of the object qualifier must be the name of the current database. [junit] com.microsoft.sqlserver.jdbc.SQLServerException: The database name component of the object qualifier must be the name of the current database. [junit] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSet(Unknown Source) [junit] at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(Unknown Source)
i have a report that is specifc to a user (has a user parameter)
is there a way to send out a dynamic subscription where the report is sent to the user "BOB" using "BOB" as a parameter, and a report that is sent to the user "Sue", uses the parameter "sue"
is this possible - i hope im making some sort of sense
I have a problem with calling my stored procedure in vb and passing in its parameters. the system is supposed to then use the store the procedures recordset and use this to pass into a crystal report.
I am getting the following message: Procedure 'TestReport' expects parameters '@StartDate', which was not supplied. This happens on the objCom.Execute line.
This is my code: Dim CRXApplication As New CRAXDRT.Application Dim CRXReport As CRAXDRT.Report Dim CRXDatabase As CRAXDRT.Database
Set CRXReport = CRXApplication.OpenReport(APPConst.DatabasePath & stReport & ".rpt")
Dim objCom As adodb.Command Set objCom = New adodb.Command Dim prm_one As adodb.Parameter Dim prm_two As adodb.Parameter Dim prm_three As adodb.Parameter Dim fdate, tdate As Date
Call CRXReport.Database.Tables(1).SetLogOnInfo("dell330", "Rota", "RotaAdmin", "dadcahadfu") Set CRXDatabase = CRXReport.Database
I am new to reporting services. I am currently on the maintenance of few reports that are developed.
Any subscribed report I attempt to deliver via email is failing with a strange error.
"Failure sending mail: The report server has encountered a configuration error. See the report server log files for more information."
I am not able to understand where this log file is stored..where can I find this? Please let me know.. and if someone has faced this error before then what could be the reason? I am using SQL Server 2005 on Windows Server 2003 R2 standard edition.
I am Getting this error in SSRS Report Subscription. I went to Subscription table Checked the Laststatus. The LastStatus is :Failure sending mail: The specified string is not in the form required for an e-mail address.Mail will not be resent.
I have  data driven subscriptions which are working fine for some subscription and one i have newly developed which is used to send the missing punch report to employeesNow , when the subscription runs it shows that all have gone email in report server but some of they are not getting any email i have tested it on my id to send each ones report to me but i also get sometimes 2 of 15 , 4 of 15 or else but not all i also checked mailroot folder under my server's inetpub there are nothing stucks. How to check why is it not sending email to all employees ?
I am new to resporting servies. I am using VS2003 with SQL Server 2005. I have a report parameter called TestStep which I populate from a stored procedure. The values are something like Step1, Step2, and so on.
Now I use this parameter to filter the results of another stored procedure to only show results based on the test step. What I would like to do is give the user the ability where if he/she selects 'ALL' in TestStep then I retrieve results for all the test steps.
So far, I have been able to display Step1, Step2 etc. in my TestStep parameters and then use the value selected by the user to filter the results. I would like to display results from all TestSteps in case user selects 'ALL' in the TestStep parameters. How would I add 'ALL' as an option into my TestStep parameter and then use filter only if TestStep is not equal to 'ALL' ? I can't change the stored procedures.
When i use this model to create a report, all goes well.
But now i want to add date parameters (from ... to) to this report.
When i add my parameters in my reporting parameters (i use visual studio to create my reports)
i get the calender to select a from and to date.
but when i generate my report, i get the whole date range available in my database (so the parameters
are not used).
I tried to create a filter in my dataset, but there i cannot specify the parameters i created (error : from is not a date. i also tried @From but that doesn't work either)
anybody have an idea how i can add parameters ? but it must be based on reporting model.
Any body plese tell me How to set the Parameters at the time or report design and how to use that parameter to call report from web application.Suppose i want to display report depend on Prodid(field in table). And Suppose i want to display report between dates.
I have ony query in my dataset at design time with parameters. I am getting the correct data when i pass parameters. Supose,i dont pass any parameters,I need Total data. Now how can i write other query in my Data set. Regards
We are implementing one SQL Server 2005 Reporting services for my client. I want to know, is there any possibilty to keep track the parameters at SQL Reporting sever side, which i am sending it from my UI.
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; } } } }
I'm just setting up Reporting Services for our company and it's a great product, no doubt about it. However, I've come across a problem I can't solve and I wonder if any of you can help.
I've produced a sales report and I want my users to be able to enter a list of sales periods to include on the report. For example, the user might want to view the report for periods 10,11,13,14 and 17 and exclude periods 12,15 and 16. Obviously, the underlying query will probably involve an IN() clause but I'm struggling to think of a way that the user might be able to enter these parameters.
The most obvious way might be by typing in a comma separated string, but I don't know how to then put those values into the query.
Not sure if I will be able to get the answer I need on this forum but hopefully I will!
We have a previous report that uses the code:
AND T1."Decision_Date" BETWEEN '2005-07-01 00:00:00.000' AND '2006-06-30 00:00:00.000' AND T1."Decision_Type_Description"
With this the report was run with the "static" dates, the new report (in MS reporting services) requires that the user be able to enter in the start date range and the end date range.
I have come up with something like: (T1."Decision_Date" = @Start_Year) AND (T1."Decision_Date" = @End_Year)
I am creating a drill through that will go out and find the correctreport out of 27 that equal my 2 parameters NAME_ID and DETAIL_ID.Does anyone out there have any ideas for me? I can make it conect to 1report. I do not know how to make it search for the correct report
Hi all. I can pass parameters from my front end(C#.net) to my reports(in Business Intelligence Project). I want the reverse of it. I want to pass parameter from my report to my windows application project.
i am trying to generate a report based on 3 parameters age, location, ethnciity
every thing works fine in data and layout tab, when i run the preview tab, it give me the option to input paramaters and then when i hit veiw report, it shows processing report.... (indefinite) time.
i tried executing the query in data tab, it takes less than a sec.
When creating a Reporting Services report and declaring local variables as part of your query in a dataset there is sometimes a problem. When you hit run in the Data section and the €œDefine Query Parameters€? box pops up, all the variables are not there. Sometimes when you go to properties (€¦) of that dataset the parameters are gone. Is this a bug? This is happening both in RS2000 and 2005. Thanks
I'm trying to create report with datasource as a cube. I'm able to connect to datasource to pull data with a single parameter. But, I'm unable to succeed in the following scenario: I have a dimension "Period" as Paremeter. The Period dimension has a hierarchy as year- quarter- month. So, I would like to create a drilldown parameter for period as year - quarter - month.
I have an asp.net app that is calling a reporting service url. on that url I pass two parameters as query strings. In the Report I defined the query string parameters in the Report Parameters. I then want these two parameters to be passed to the stored procedure in the report. How can I make this happen? The parameters seem to get to the Report but I can't seem to get them mapped to the stored proc. In the data set I defined the stored procs parameters and mapped them to the corresponding Report Parameters but still no go.
I'm assuming there's something fundamental I'm missing here about passing parameters to reporting services.
1 report but different departments run this report with different parameters. Atm I have copied this report for the 4 departments and adjusted the default parameters accordingly (highly inefficient). Isn't there a way to just have one report and that I can adjust the default parameters in code based on user or group ?
Can we have more than 2 parameters displayed in a single row on report (when browsed using web browser). The parameters BY DEFAULT - seems to be lined up in an array of 2 columns, and N rows - which gets frustrating because so much of space is left blank (gray space just left of <View Report> button and to the right of parameters line-up).
How can we have more than 2 parameters displayed in one single row ? As this will help the actual report content to take up more space on the screen than the parametes taking up almost 1/2 of the screen.
I am new to reporting services and wanted to see if anyone had any experience trying to do this...that "this" is passing a parameter to a report, but having the user fill in some others. I can pass all of my parameters to the report or let the user fill all of them in, but it doesn't seem to let me fill in one and have the users fill in the others.
I have a Start Date and End Date parameter in my SSRS report. Â The results are off by 1 day. Â For example if I enter 4/2/2015 and 4/20/2015 it will return a few results from 4/1/2015 to 4/19/2015.
Member [Measures].[Measure3] As [Measures].[Value Rent Period Receipts] Member [Measures].[Measure4] As (([Measures].[Value Rent Period Receipts]*-1)/[Measures].[Value Rent Period Debit])
There are two parameters; the value of lag passed to the LastPeriods function, and tenancy types passed in the where clause
I have a third parameter I would like to use called FirstDate and this I would like to pass to the LastPeriods function as the second argument
FirstDate is a value from my Time dimension, something like [Time].[Fiscal Time].[Sep. 15]
When editing the query to: -
Member [Measures].[Measure3] As [Measures].[Value Rent Period Receipts] Member [Measures].[Measure4] As (([Measures].[Value Rent Period Receipts]*-1)/[Measures].[Value Rent Period Debit]) Select {[Measures].[Measure1] ,[Measures].[Measure2]
[Code] ....
The code errors. I have tried all sorts of formats such as  LastPeriods(@LagMonths,[@FirstDate]) and LastPeriods(@LagMonths,[Time].[Fiscal Time].[@FirstDate]), but nothing works
We've developed several reports where you can type in / select variables and run them, affecting their outcomes.
We'd like to change this so if the user clicks on a link from our Intranet, this takes them straight to the report with the appropriate parameter(s) - rather like the "jump to report" function within a report - i.e. the url for that function...
