Passing A Dataset Programmatically To Report
Oct 5, 2007HI everyone;
i have a problem , is there a possible to pass a dataset to a ssrs report programmatically ?
or at runtime without specifying it at design time ...
ADVERTISEMENT
Passing DataSet To MS Access Report
May 28, 2007I 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
Passing Parameters To DataSet Useb As Report Data Source
May 21, 2007Hi guys,
How can I pass a parameter used for the DataSet query? I'm using this DataSet file as data source of my .rdlc report for Windows Forms.
I've already done a .rdlc report to Web Forms where I passed the query parameter by ObjectDataSource.SelectParameters, but in Windows Forms this object was not created. I just hava the following created objects:
- despesaTableAdapter (from myDataSetTableAdapter)
- despesaBindingSource (Windows.Forms.BindingSource)
- RelatorioDataSet (from myDataSet)
- relatorioDataSet1 (from myDataSet)
Any help will be very appreciated.
Tks and rgds,
Luis Antonio
Adding Lookup Programmatically:How Can I Add Column From Reference Dataset To The Transformation?
Jun 19, 2007
Hello,
I have created SSIS package programmatically, I want to add Lookup transformation,
How can I add column from reference dataset to the transformation?
I have try to add new output column but it gives me an validation error, I write following coed to add new output column to lookup.
IDTSOutputColumn90 outputColumn = this.lookup.OutputCollection[0].OutputColumnCollection.New();
outputColumn.Name = col.Name;
outputColumn.Description = "Staging table output";
outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
outputColumn.ErrorOrTruncationOperation = "Copy Column";
outputColumn.SetDataTypeProperties(col.DataType, col.Length, col.Precision, col.Scale, col.CodePage);
Please suggest other way to add column from reference dataset to transformation output.
Passing A Table Programmatically To Datareader Source Component In Ssis
Feb 26, 2007Hi There,
I am loading/executing packages from c# and I need to populate a temp table from user input and pass this table as a variable to the datareader source components sql command. I am using expression to build this query, but I am getting design time error when I have this command..
"select id, (SysDate + 28) as ExpiresDate from Table1 where id in (Select Id from" +@[User::Table2]+")"..
I have declared Table2 as a variable of type Object and I am creating Table2 in C# and I am assigning that Table to the user Table. But in the design mode, I am getting an error...expression cannot be evaluated.
Can anybody please tell me when I cannot do this?
Thanks,
Listing Datasets In Report (dataset Name, Dataset's Commands)
Oct 12, 2007
Is there any way to display this information in the report?
Thanks
Passing Parameter Into Dataset
Jun 15, 2007Hello
Im trying to pass a integer parameter to my dataset query depending on the selection made from the user. It is suppose to get the Top number off records based on what number the user chooses
I get the error Incorrect syntax near '@Param_TopNo, If i take the top @param_topno out the query works
The cutdown version off the query is...
Select Top @Param_TopNo SaleDate, Consultant, Productname
From tblSales
where product = 2000
Im not sure if the method above is even achievable !!!! does anyone know how i can pass a variable/parameter into my dataset and maybe provide an example off the code.
thanks
Passing A Dataset To Reporting Services
Feb 2, 2007I've seen questions similar to this posted, but I'm unable to find a clear answer to what I need to do.
We are currently using Crystal Reports, generating a dataset in our application and passing it to Crystal. Due to the many problems we have with Crystal we are now looking to move to Reporting Services 2005, but I can't find out how to pass it a dataset.
Although we do use SQL Server for data storage, we have many reports with calculations that are way too complicated to use calculated fields, and which change too often to want to store them in the database. One solution might be to store the data temporarily and regenerate it each time it's required, but this doesn't seem very elegant. So please could someone tell me if there is a way to do what I'm after and point me at a page that gives a nice clear explanation.
Thank you
Passing NULL To DataSet Parmameter
Apr 13, 2007Hey All,
I have a number Stored Procs that have been around for a while that pull the entire list, or if I pass an ID, will just the record for that ID like below.
I want to be able to use these querries to poplate Multi-Select parameter dropdowns. going to the Data tab and creating a new dataset, I put in the SP name, and close the window. I then go to the Red ! point to preview the data it prompts me for my ID parmaeter on my SP. In the dropdown list it has '<Null>' (no ' though). When I run it, it works fine and returns all of my records.
When I run the report, it errors saying I didn't pass a parm for ID. I go back to the data tab, and edit my DataSet hitting the elipse. I go to the 3 tab called parameters, and type the following I get the following errors:
@ID = <Null> ---- ERROR - [BC30201] Expression expected
@ID= ---- ERROR - Return statement in function must return a value
@ID = Null -----ERROR - Null constant not supported use System.DBNull instead
@ID = System.DBNull ----ERROR - [BCS30691] DBNull is a type in System and cannot be used in an expression
@ID=System.DBNull.Value --- NO ERROR, but it does not return anything either. I also did a SQL Trace, and I can see that it doesn't even send the querry to the database.
Does anyone know another magic value I can pass to get this to work?
I am being a little stuborn, I know that I could just create new procs, and wrap up the null, but the more stuff you create the more you have to maintain, so I would prefer to reuse these.
Thanks in advance.
Eric Wild
PS: My company is moving from crystal reports to Reporting service, and Reporting services is Rocks! It is very intuitve, simple and straign forward. The formatting is easy with the table and the matrix control blows away the crosstab control in crystal. Also, I'm finding that because crystal was so un extendable, that I would spend hours shaping sql to get over it's blemishes, and hours shaping it in the report, only to sometimes reliaze that the proposed onetime hack wouldn't work, and have to start all over! So far with RSS any tips and tricks I have learned can very easily be applied to any report I work on! Aslo, I do mostly interanet web apps, and it is nice to dump my reports on the Report Server, and not worry about haing to create a web page, create a datasource and all the ansilary stuff to go along with it. The only thing I don't like is the name 'Roporting Services': It does not stick out too far in Google Searches like 'AJAX.NET' or 'ASP.NET'. Anyway kudoes to the Reporting Services team!
ALTER PROC [dbo].[spGetLaborRole]
@ID INT = NULL
AS
BEGIN
SELECT ID, Descr
FROM dbo.LaborRole
WHERE ( (ID = @ID) OR (@ID IS NULL) )
Reporting Services :: Passing Parameters To MDX Dataset
Oct 1, 2015I have an MDX data-set query as follows: -
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]
,[Measures].[Measure3]
[Code] ....
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
Reporting Services :: Passing All Parameter Selection To Dataset Query
Nov 20, 2015I have a report that prompts the user to select a parameter, for simplicity, let's say the parameter is for color choice, options are Red, Yellow, Blue or *. The * is for include all colors. I am passing that parameter back to the dataset query which, again for simplicity is
select
Hue from AvailableColors
whereColor
= @ColorParam.
For a specific color this works fine, for the "*" selection it returns a null. It would seem that I need to convert the * to % but I am not sure how.....
Adding DataSet As DataSource To Report Published On Report Server From ClientSide
Jan 15, 2007Hi
I am Creating Click Once Windows Application with Reporting Services 2005.I have created Report and Published on Report Server.In my windows application I am successfully able to view my published report through report viewer control.
Now in my application I am getting a dataset from my custom webservice. I want this dataset data to be added to my report as datasource at runtime on Client Side ,as my report is on Report Server.
waiting for help!!
Thanks in Advance
Pankaj
Publishing A Report Programmatically.
Jun 5, 2007Hi!
I have a rdl file and would like to publish it programmatically? Is there any API or Web Service available for this.
Multi-value Parameter In Master Report Passing To Single Param Sub-report In A List.
Aug 20, 2007Here'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?
Passing Report Parameters To An SSRS 2005 Report Hosted In Sharepoint 2007
Nov 18, 2007Here is a situation in my company:
We have an Office SharePoint 2007 site, we developed a couple of web parts and added them to our site. We used SQL Server Reporting services 2005 as our reporting solution. The reports are hosted on the report center and when we need a report, we open it by sending a URL requesting the report, passing the report parameters in the URL query string. So the report is filtered based on the parameters passed from the web parts.
But since best practices say that you should host reports directly under SharePoint, by configuring the reporting services to run in the SharePoint integrated mode. We followed the steps and installed SharePointRS and we finally succeeded to publish the reports to a SharePoint folder, but we had a limitation: we are unable to pass the report parameters internally to the report hosted in SharePoint. If we passed them in the query string as the report center case, SharePoint neglects them totally.
So the question is: how can I pass parameters internally between a SharePoint web part and a SQL server 2005 reporting services report hosted in SharePoint?
Reporting Services :: Passing Cascaded Parameters From Main Report To Drill Through Report In SSRS?
Sep 16, 2015I am trying to develop a report from cube,and it has one drill through report.when i click on sales amount field on main report,then it open the drill through report with details.Here i passed the **(category,subcat,product)parameters(Cascaded)** to drill through report in action part of main report.In my main report i have two columns .
columns are **Level** and **Salesamount**.
Values are like
**[-]category** **100**
**[-]subcat** **50**
**product** **30**
when i click on 100, parameters are passed & it open the detail report correctly, but when i click on 50,the values for subcat parameter is not getting values in detail report and same as the product also.
Here i used the expressions in parameter values
**pCategory**----iif(inscope(category),!fields.category.value,split(join(Parameters!category.value,","),","))
**psubcat**----iif(inscope(subcat),!fields.subcat.value,split(join(Parameters!subcat.value,","),","))
**pproduct**----iif(inscope(product),!fields.product.value,split(join(Parameters!product.value,","),","))
Programmatically Create A Report Model
Jul 14, 2007Hello,
What we'd like to do is programmatically generate and maintain a report model based on how the individual install is configured. I've found some documentation that makes me believe this is possible, but I'm hoping someone can nudge me in the right direction. The ReportingServices2005 class has several model related methods (CreateModel) and I found the .xsd for the semantic model XML file
(http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling/SemanticModeling.xsd),
but no real documentation on how to put together a Report Model document from scratch. Looking at the files generated from BIDS is somewhat overwhelming and I have no idea where I'd pull most of the information.
Finally, I want to create Report Model(.smdl) file without Report Model Designer.
I want to Implement CreateModel Method to create new model, any source code sample..??
Any help that can be provided would be greatly appreciated. Thanks!
Sandip.
Adding Content To The Report Programmatically
Oct 23, 2007HI guys,
I want to add some customized text to the report programmatically before the report is rendered. As it has to be done for all the reports, I don't want to do it report by report.
For example, the report needs to display user selected filters.
Any thoughts?
Thansk.
How To Create Report Parameter Programmatically?
Dec 30, 2006Hi,
I'm working on custom report manager. It manages "report entities" and
"report templates" (actually, RDLs uploaded on the server) and stores
one-to-many relation between them. The task is to store
"MasterEntityID" report parameter in every RDL and keep it up in actual
state whether RDL is being assigned to another entity or new RDL is
being uploaded and assigned. I've covered the first issue with
SetReportParameters() web method, but how should I deal with the second
one? Uploaded RDL may be short of the param, so I have to add it
programmatically while uploading.
Thanks,
Anatoly
Battling To Programmatically Load A Report
Jun 11, 2007Hi friends,
I am struggling to programmatically render my report to a pdf without displaying it in a viewer and would appreciate a pointer in the right direction.
My code seems to work and the report is found and everything is honkey-dorey until I try to actually load the report.. lemme show: (please excuse the bit of a mess since this is just a test form)
rs = new rs2005.ReportingService2005();
rsExec = new rsExecService.ReportExecutionService();
// Prepare Render arguments
string historyID = null;
string deviceInfo = null;
string format = "PDF";
Byte[] results;
string encoding = String.Empty;
string mimeType = String.Empty;
string extension = String.Empty;
rsExecService.Warning[] warnings = null;
string[] streamIDs = null;
// Define variables needed for GetParameters() method
string _historyID = null;
bool _forRendering = false;
rs2005.ParameterValue[] _values = null;
// Authenticate to the Web service using Windows credentials
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs2005.DataSourceCredentials[] _credentials = null;
rs2005.ReportParameter[] _parameters = null;
FindReport();
//Create a variable containing the selected item
selItem = MyCatalogItem.Item;
try
{
// Get if any parameters needed.
_parameters = rs.GetReportParameters(selItem.Path , _historyID, _forRendering, _values, _credentials );
// Load the selected report.
rsExecService.ExecutionInfo ei = rsExec.LoadReport(selItem.Path, historyID);
// Prepare report parameter.
// Set the parameters for the report needed.
rsExecService.ParameterValue[] parameters = new rsExecService.ParameterValue[5];
// Place to include the parameter..
if (_parameters.Length > 0)
{
parameters[0] = new rsExecService.ParameterValue();
parameters[0].Value = pQuoteID.ToString();
parameters[0].Label = "Quote ID";
parameters[0].Name = "cor_Quote_id";
parameters[1] = new rsExecService.ParameterValue();
parameters[1].Value = DateTime.Now.ToString();
parameters[1].Label = "Quote Start Period";
parameters[1].Name = "QuoteStartPeriod";
parameters[2] = new rsExecService.ParameterValue();
parameters[2].Value = DateTime.Now.ToString();
parameters[2].Label = "Quote End Period";
parameters[2].Name = "QuoteEndPeriod";
parameters[3] = new rsExecService.ParameterValue();
parameters[3].Value = "Client";
parameters[3].Label = "Client";
parameters[3].Name = "ClientName";
parameters[4] = new rsExecService.ParameterValue();
parameters[4].Value = "Ralph Contact";
parameters[4].Label = "Ralph Contact";
parameters[4].Name = "RaljacContact";
}
rsExec.SetExecutionParameters(parameters, "en-us");
results = rsExec.Render(format, deviceInfo, out mimeType, out encoding, out warnings, out streamIDs);
// Create a file stream and write the report to it
using (FileStream stream = File.OpenWrite(fileName))
{
stream.Write(results, 0, results.Length);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Right, It bombs out at the section I marked in blue and gives me this HUGE message :
"System.Web.Services.Protocols.SoapException: Server did not recognize the value of HTTP Header SOAPAction: http://schemas.microsoft.com/sqlserver/2005/01/12/reporting/reportingservices/LoadReport. at System.Web.Services.Protocols.Soap11ServerProtocolHelper.RouteRequest() at System.Web.Services.Protocols.SoapServerProtocol.RouteRequest(SoapServerMessage message) at System.Web.Services.Protocols.SoapServerProtocol.Initialize() at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing)"
Could this be related to credentials or something? Any advice welcome.
Thanks Mates
Programmatically Determine That A Report Has No Data?
Apr 2, 2008
I'm creating ssrs reports via the web service render method & would like to be able to determine when a report has no data.
Currently what I'm doing is rendering the report twice - once as a pdf (the format that the report needs to be in) and once as a csv. I then check the csv for a specific string placed in the norows property of the report table.
Is there a better way of doing this? It seems to me that this would have been a good candidate to include in the warnings array.
Between this issue & the hacks required to get data into the header I'm thinking that I should maybe reconsider some other reporting options...
Thanks.
Passing Parameter To The Sql Server Report Using Report Viewer Control
Dec 29, 2006Hi,
I want to give filtering criteria in my SSRS report.
I have drop down list control having list of Email's of clients.
So, how can i pass the value of the particular Email id in my SSRS report using Report Viewer control?
How can i pass user input as parameter in my report using visula studio 2005?
Thanx,
Ruja
Report SnapShot Creation Programmatically Through Java
Jan 5, 2007Hi,
I am facing some issues while creating report snapshots programmatically using axis (Java platform for creating and deploying web services applications).
To create a report snapshot what should be the methods called ? I tried using UpdateReportExecutionSnapshot() but didn't find any parameters to set the schedule information which would say when the execute the report snapshot. Can you please guide me for this ?
Also I had one more doubt regarding report snapshots. If a parameterized report is configured as snapshot then we would require to set default parameters for the report.
Is there a way to create snapshots of the same report with different parameters ?
eg : The employee information report displays the information based on the employee id taken as a parameter. So is it possible to create snapshots with different employee id's ?
Thanks,
Amit Shah.
Programmatically Create A Report History Snapshot
Dec 20, 2006Hi,
To programmatically invoke a subscription we can use -
exec ReportServer..AddEvent 'TimedSubscription', '575F96C6-A1BD-49FD-9C2F-934FC9658780'
How can we programmatically cause a Manual Report History Snapshot to be created.
I know it can be done using the Webservice. How would we do it using the ReportServer stored procedures. Which SP/SP's can we call. With what parameters.
TIA,
Sam.
Pass Dataset From Main Report To Sub-report
Apr 17, 2008Hi
Don't know if this is possible or not - Need to produce a report and the main report has a grid in it.
For every row in the main report, in one of the grid fields there are n images. So to do this, I am using a sub-report. But there is a problem with going to the database for every row to fetch images that if the user does a report spanning more than about 6 months, there will be thousands of records each with the potential to have many images and the constant round trips to the db server would probably kill it.
So what I want to achieve is to fetch the dataset in the main report with just one trip to the database and then pass the dataset filtered by a uniqueidentifier to each row of the subreport.
Could someone please let me know if this or something like it is possible and how to go about it?
Thanks
Steve
Setting Report Data Source Credentials Programmatically?
Feb 1, 2006Hello,
I've been working on an application that uploads an RDL to Reporting Services (through the SOAP webservice method CreateReport) programmatically. I'm having difficulty setting up the data source properties for my uploaded report. In particular the Data Source Credentials property.
The datasource for my report doesn't require credentials. By default after I upload the report to Reporting Services, the Data Source Credentials property is set to "Credentials supplied by the user running the report". How do I go about setting the Data Source Credentials property to "Credentials are not required" programmatically through the webservice?
Thanks in advance
Programmatically Create A Report Model SMDL File.
Jul 16, 2007Hello,
I'm looking for Programmatically genration of Report Model .smdl file.
Please guide me or suggest some links so that ui can refer same.
Is there any smdl genrator or any classes for the same...?
Thanks in advance.
Sandip
How To Programmatically Deploying A Sql Reporting Services 2005 Report Through Aps.net Using Language VB
Oct 18, 2007
Hello friend,
my problem is , i want to deploy my rdl and rds file which i make in the sql 2005 reporting services. Now i want to deploy this rdl through asp.net coding using language VB
i hope someone is help me
Thanks
Rahul Sinha
Programmatically Assigning The Datasource In The Server Report Ssrs 2005
Jan 2, 2008Dear All:
I am new to SSRS 2005.
I have created many shared datsources and reprots at http://localhost/resportserver.
I am trying to access these reports from ReportViewer in the web application. Actually I want to use these reports for different customers by just changing the datsource programitically.
Is this is a possible?
Thank you
Theju
How To Run Rdl Report From .net Using Dataset.
Oct 8, 2007
Hi
Is it possible create Reporting Svcs (RS) report with coding in .net assigning dataset to it....
how to run RS report with windows form through report viewer
I have tried to find a solution but so far, no luck.
Anyone have any ideas?
2 Dataset In 1 Report
Mar 10, 2007Hi all. Is it possible to create 2 dataset in 1 report in Business Intelligence Project? I tried to make 2 datasets but if I ran the report it occurs an error. It only ran for 1 dataset.
any thoughts please.
thanks.
-ron-
Link Two DataSet In The Same Report
Apr 11, 2007Hi !
I use ms sql server 2005, and I have a problem
I have a dataBase where I have a lot of data, I have a webServices which calculate indicator for a time period and an id indicator. The time period must be specific and choose by the user, so I can't dto automatically by integrating the data from my webService in a dataBase (with IntegrationServices).
So I create a report with 2 dataset, one which recover the indicator value for each equipment (the webService send a list of {<idEquipment> and <value>}) , and I want to link the idEquipment with the IdEquipement in my dataBase, it would allow me to know other informations about the equipment :
equipement ID | Indicator value (from webServices) | equipment name | eq number | cumul of event duration for this eq....
All in the same grid...
Does it possible ?
If it is possible with the report designer, does it possible with report builder, as report builder need a model, how can i do to make a model from 2 dataSource...
thank you for your answer...
Designing A Report Without Dataset
Jun 12, 2007
Hello
We are trying to create an app where we pass dynamically a dataset (from our form in C#) to our {report}.rdl file.
We are having doubts about something.
How can we design the actual report if we don't have the datasource until runtime? The reason to do this is due to our complex calculations of data which becomes almost impossible to achieve in a T-SQL environment.
I know with a lot of patience and time (we don't have both) we can achieve it, but even though, we would need to process some data in the client side.
So, the question is... is it possible to design a report without a Dataset? I know we can drag the controls on the layout window, but we won't be able to test it. IS there any workaroung about this?
Thank you