In the data tab of my report (Reporting Services for SQL Server 2000), I'd like to run a query that requires parameters be passed to it. Is there a way to pass the parameters of the report to that query?
Thank you.
Hi 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
I have three databases which have the identical data structure. When the user runs a report, he has to select a database.
For example, my report query would look like:
Select * from <theDatabase>.dbo.MyTable
How could I assign the value of <theDatabase> at run time depending on what the user selects from the list?
In my datasource, I have only specified the server name and no value for initial catalog.
I am attempting to pass report parameters to my query. My report's data source is a WCF web service. I can run normal queries fine, but I have found that without the know-how to pass parameters to my query I am limited with my capabilities.
Assume a string parameter named "Name" with a specified default value of "Jmachol90", how would I pass that into the following query at the designated place:
 Â
<Query>
      <Method Namespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09" Name="SecurityPrincipalsGetRequest">
      <Parameters>
      <Parameter Name="Criteria" Type="XML">
<DefaultValue>
[code]....
It's well known issue, that one can't use any dataset fields in a
report header/footer directly. One of the approach is to create
query-based parameter that basically equals
=First(Fields!@FieldName@.Value, "@DataSetName@") and use that
parameter value instead. But it doesn't work in my case!
My report displays some entity description and is parametrized with
EntityID param. Its header contains entity name that, according to the
approach, is queried from the data source through the EntityName
report parameter. There's important issue: the report is displayed in
ReportViewer control, that is embedded into my application and entity
ID parameter isn't ser by user in ReportViewer parameters area. Its
default value is changed by the application with SetReportParameters()
web method every time a user wants to view the report according to the
entity the user is exploring in the application. But after the report
has been rendered, its header always contains not actual (outdated)
entity name. Nevertheless, the report body contains actual data
(including entity name). If I alter entity ID parameter in ReportViewer
or in web-based Report Manager and refresh report, header displays
correct entity name.
What's wrong in the workflow described?
Hi,
I have created a report with the report server project template.
the report is created from stoked procedure having defaut input parameters.
With visual studio, i publish my report on reportserver. whenever i access to my report on this url :
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render. the created report is with the default parameters.
I would like to know if i can transmet parameters for the stocked procedure to build the report with the request i want.
I tried to put parameter directly in the url in this way
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render&@region='toto'
but without success.
my error message is that one
An attempt was made to set a report parameter '@region' that is not defined in this report. (rsUnknownReportParameter)
My stocked procedure is :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[akli] @region varchar ='m'
as
select * from dbo.Report2 where region=@region
The request used to buid the report ?
DECLARE @region varchar
EXECUTE dbo.akli @region
What is wrong in that ?
Thanks for your help.
Arioule
Hi,
I am having hard time in sending parameters to the report server through reportviewer control from my application. Could anybody help me on this issue?. Is there any way to send the parameters and also what kind of configuration do i need on my report project so that it can accept parameters from my application.
Thanks,
Good morning, all
I have a really cool Budget report which has two matrices on it. One is used/visible if the user selects four levels of grouping, and one is used if the user selects only three levels of grouping. The four choices for grouping are four fields, Par, FERC, Point, and Resource.
Since grouping is optional, I need a way to pass the parameter for a field that is not grouped on from the main report to the drillthrough report. For example if the user chooses to group on all fields but Point, I need to pass the report parameter that was used by the main report for Point to the drillthrough report as a report parameter.
So, I tried testing the three grouping levels values and if none were equal to "Point" then pass in the parameter by putting the following expression in the Parameter Value box for the Point parameter on the Navigation tab of the datacell of the main report that is the drillthrough link:
=iif(
Parameters!GroupLevel1.Value <> "Point"
and
Parameters!GroupLevel2.Value<>"Point"
and
Parameters!GroupLevel3.Value<>"Point"
,
Join(Parameters!PNTPoint.Value,",")
,
Fields!Point.UniqueName
)
But this is not working; the drillthrough report shows a blank dropdown for the Point parameter and I have to manually set it. Does anyone see an error in the syntax of the iif statement?
Any references, links, resources or good clean jokes would be greatly appreciated.
Thanks,
Kathryn
In a matrix report with column and row groupings, how to pass the parameters to the drill-through report? The column grouping could open up into multiple columns and the same to the row groupings. I have a navigation link to a drill-through report on a total field in the matrix report and the drill-through report is required three parameters, one from the parameter from the matrix report (this one I can map to easily), second parameter is one of the value from the column grouping field and the third parameter is one of the value from the row grouping field. How to select those values from the column/row grouping.
Thanks
Hello all,
I have a project where I need to automatically pass 2 sets of parameters to a SSRS report and generate PDF files. I have set the defaults for the parameters based on one set of parameters and created a subscription to automatically generate PDF copy. However, I am not sure how to generate the report automatically using the other set of parameters. Also, I don't want to create two report files to accomplish this.
Can you please help?
Thanks in advance,
Saurav
We are going insane trying to start Report Manager on a SQL Reporting Services 2000 installation. The programmer/admin who originally set this up for us is gone.
We recently upgraded a database/application server to a new server, causing the data source being used by reports in reporting services to no longer be valid. Unfortunately, we do not have access to the original report project to 're-deploy' with the corrected data source.
We desperately need to get the reports that are installed to retrieve their data from the new database location/machine. We understand this can be done by specifying a new data source in Report Manager. (To clarify, we have NOT moved the report servier installation or database, these remain in place - it's just the deployed reports that no longer point to the correct data source.)
For some reason, our Report Manager will no longer run - when we try to launch it, we get a windows login dialog - nothing will work here. We've tried both local and domain admins and constantly get ACCESS IS DENIED 401.3 error messages that we do not have permission/problems with ACL's.
We've gone so far as to allow EVERYONE read/write access to the ReportManager and ReportServer folders and the RS virtual directories, but nothing seems to help.
Can anyone help with this? Ideas on how to change our data source, or how to get back into Report Manager?
Since we are somewhat technical, but not experts, and don't have much more time to waste, we are willing to pay $500 for this project to someone willing to access the server and fix the problem so that the reports point to the correct database and restore access to Report Manager.
Thanks in advance for any help.
hi ,
i am trying for a drill through report (rdlc)
ihave written the following code in drill through event of reportviewer, whenever i click on the first report iam getting the error like
An error has occurred during report processing.
A data source instance has no
t been supplied for the data source "DetailDS_get_orderdetail".
the code is
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
//using Microsoft.ApplicationBlocks.Data;
using Microsoft.Reporting.WebForms;
using DAC;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
ReportViewer1.Visible = false;
}
protected void Button1_Click(object sender, EventArgs e)
{
DAC.clsReportsWoman obj = new clsReportsWoman();
DataSet ds = new DataSet();
ds = obj.get_order();
ReportViewer1.LocalReport.DataSources.Clear();
ReportDataSource reds = new ReportDataSource("DataSet1_get_order", ds.Tables[0]);
ReportViewer1.LocalReport.DataSources.Add(reds);
ReportViewer1.LocalReport.ReportPath = "C:/Documents and Settings/km63096/My Documents/Visual Studio 2005/WebSites/drillthrurep/Report.rdlc";
ReportViewer1.LocalReport.Refresh();
ReportViewer1.Visible = true;
}
protected void ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
{
DAC.clsReportsWoman obj = new clsReportsWoman();
ReportParameterInfoCollection DrillThroughValues =
e.Report.GetParameters();
foreach (ReportParameterInfo d in DrillThroughValues)
{
Label1.Text = d.Values[0].ToString().Trim();
}
LocalReport localreport = (LocalReport)e.Report;
string order_id = Label1.Text;
DataSet ds = new DataSet();
ds = obj.get_orderdetail(order_id);
ReportViewer1.LocalReport.DataSources.Clear();
ReportDataSource reds = new ReportDataSource("DetailDS_get_orderdetail", ds.Tables[0]);
ReportViewer1.LocalReport.DataSources.Add(reds);
ReportViewer1.LocalReport.ReportPath = Server.MapPath(@"Reportlevel1.rdlc");
ReportViewer1.LocalReport.Refresh();
}
}
the code in method get_orderdetail(order_id) is
public DataSet get_orderdetail(string order_id)
{
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
cmd.Parameters.Add("@order_id", SqlDbType.VarChar, 50);
cmd.Parameters["@order_id"].Value = order_id;
ds = SQLHelper.ExecuteAdapter(cmd, CommandType.StoredProcedure, "dbo.get_orderdetail");
return (ds);
}pls help me.
I have a DTS package of SQL server 2000 which has two "Execute SQL Task", which gets maximum ID (call it maxID1) from table1 in mysql and maximum ID (call it maxID2) from table 2 in sql server. Those 2 as global variables passes into a data flow which grabs all data from mysql's table1 with its ID > maxID2 and ID <= maxID1 and insert into SQL server table3. This package is working so far.
My question is how to achieve the same thing in SSIS? I created two "Execute SQL Task", which will get maxID1 and maxID2, and one "Data Flow Task". But how to pass those 2 parameters into that "Data Flow Task" without using "Script Task"?
Anyone could help? Thanks!
Hi.I bet this is a 101 question, but i'd appreciate any help!I am in the 'where...' section of the configure data source wizzard .Column: I grab 'UserName' Operator: I select '='BUT how do I get the UserName (The user is signed into the app) Is it from the Form? Profile? Session?Ive tried profile.name..... THANKS In advance.... Dan
View 4 Replies View RelatedUsing RS2005, how should multivalue parameters be stored in a database field so that a data driven subscription can properly read and use them? I have so far had no luck using syntax of:
1) parm1, parm2
2) parm1,parm2
Do single qutoes need to explicitly wrap the values? Can you please provide an example and a SQL INSERT statement using parm1 and parm2 to demonstrate what to store in the database field?
Thanks!!
In SSIS in Sql task we have option to pass parameter or variable..But in Data Flow Task when we use Data Reader Source using ADO.NET connection..There is no option to pass parameter or variable Or no option to receive a parameter or variable .
I am having a query were it need to pass a parameter.in sql task ...And Data Reader Source have to receive this parameter from sql task .
Sql Task finds a value of parameter and pass to DataReader Source in DataFlow Task .. ...
Please can any one help me to solve this problem of Receiving parameter or variable in DataReader Source using DAO.Net connection in DataFlow Task..thank you dilsa
How do i use the foreach loop container and pass each file found according to a specified pattern to a Flat File Source in a Data Flow Task Object so i can operate on each file found in the foreach loop object instead of having to specify a static file name
Thanks
I pull data from Sql Server through the  query, I want to pass the region parameter to the power pivot connection query. So that I can automatically pull the required region data.  The parameter should pick the value from the excel range. And also how to control this through VBAÂ
View 4 Replies View RelatedI writed That CodeDim SqlCom As New SqlDataSource SqlCom.ConnectionString = "data source=localhost;initial catalog=Mansouria1.1;integrated security=True" SqlCom.DataSourceMode = SqlDataSourceMode.DataReader SqlCom.InsertCommandType = SqlDataSourceCommandType.StoredProcedure SqlCom.InsertCommand = "Add_Machine_Component" SqlCom.InsertParameters.Add("@Name", TextBox1.Text) SqlCom.Insert() and the store Procedure@Name Nvarchar(150)ASInsert into Machine_Component(Name) values (@Name)Go That error Message displayprocedure or Function 'Add_Machine_Component' expects parameter '@Name', which was not supplied.
View 3 Replies View RelatedDoes anyone have any experience setting up paramters when using a Teradata data source? I am specifically looking for how to set up dates and multi-select drop down boxes as parameters. Using a fixed query against Teradata using SSRS is straight forward and is working well. Parameters, not so much.
R
I am building a report that takes a web service as data source. This web service does not take primitive types as parameters. The signature is something like this:
<soap:Body>
<GetXml xmlns="myreports.ws">
<root >
<TP Name="string">
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
</TP>
<TP Name="string">
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
</TP>
</root>
</GetXml>
</soap:Body>
My question is: how do I pass the xml to the web service from reporting services.
I have seen examples with simple types. They do it with this query:
<Query>
<SoapAction>myreports.ws/GetXml</SoapAction>
<Method Namespace="myreports.ws" Name="GetXml"></Method>
<Parameters>
<Parameter Name="Parameter1">
<DefaultValue>some value</DefaultValue>
</Parameter>
</Parameters>
<ElementPath IgnoreNamespaces="True">
GetXmlResponse{}/GetXmlResult{}/diffgram{}/NewDataSet/Element{@Attribute1,@Attribute2}
</ElementPath>
</Query>
But there are no example on using xml directly instead of parameters. Can anyone help me? Where do I put my xml?
I have few reports under different folders in Report Manager. Is there a way to set the data source for the whole folder rather than each report individually? There is also a folder where reports generate dynamically. Since the report project doesn't exist anymore, I can't set the data source through code. How can I set the data source for the whole folder?
View 7 Replies View RelatedThis seems to be much more difficult than it was in DTS (or perhaps I just need to adjust to the new way of doing things).
Eventually I found that I needed to use "SQL command from variable" and using two other variables as input parameters. The expresion for the command is
"usp_ValveStatusForDay '" + @[User:ate] + "','" + @[User::Report] + "'"
which typically evaluates as
usp_ValveStatusForDay '18 Oct 07','Report_Name'
This previews correctly and the resulting columns are available for mapping to a destination. So far so good.
By the way, is this the best way to call a stored procedure with parameters?
I have pasted the stored procedure at the end of this posting because I have come accross a puzzling problem. The query as shown below works correctlly but if I un-comment the delete statement, the preview still works and the columns are still avilable for mapping but I get the following errors when the package is executed.
Error: 0xC02092B4 at Data Flow Task, OLE DB Source [1]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC02092B4.
I realise that I could execute the delete query in a separate SSIS package step but I am curious as to why there is a problem with the way I tried to do it.
At one stage the stored procedure used a temp table and later I also experimented with a table variable. In both cases I got similar errors at execution time. In the case of the temp table there was another problem in that, while the preview worked, there were no columns available for mapping. Using a table variable seemed to overcome this problem but I still got the run time error. eventually I found a way to avoid using either a temp table or a table variable and the package then worked correctly, copying the data into the desitnation table.
It seems to me that if there is any complexity at all to the stored procedure, these errors seem to occur. Can anyone enlighten me as to what the "rules of engagement" are in this regard? Is one solution to use a wrapper stored procedure that simply calls the more complex one?
ALTER procedure [dbo].[usp_ValveStatusForDay]
(
@dateTime DateTime,
@reportName VarChar(100)
)
AS
BEGIN
DECLARE @day VarChar(10)
DECLARE @month VarChar(10)
DECLARE @year VarChar(10)
DECLARE @start VarChar(25)
DECLARE @end VarChar(25)
SET @day = Convert(Varchar(10),DatePart(day, @dateTime))
SET @month = Convert(VarChar(10), DatePart(month, @dateTime))
SET @year = Convert(VarChar(10), DatePart(year, @dateTime))
IF @month = '1' SET @month = 'Jan'
IF @month = '2' SET @month = 'Feb'
IF @month = '3' SET @month = 'Mar'
IF @month = '4' SET @month = 'Apr'
IF @month = '5' SET @month = 'May'
IF @month = '6' SET @month = 'Jun'
IF @month = '7' SET @month = 'Jul'
IF @month = '8' SET @month = 'Aug'
IF @month = '9' SET @month = 'Sep'
IF @month = '10' SET @month = 'Oct'
IF @month = '11' SET @month = 'Nov'
IF @month = '12' SET @month = 'Dec'
SET @start = @day + ' ' + @month + ' ' + @year + ' 00:00:00'
SET @end = @day + ' ' + @month + ' ' + @year + ' 23:59:59'
--delete from ValveStatus where SampleDateTime between dbo.ToBigInt(@start) and dbo.ToBigInt(@end)
exec dbo.usp_ValveStats_ReportName @reportName, @start, @end, '1h'
END
In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.
Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.
I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.
Am I missing an obvious workaround?
I am building a webapp that calls an SSRS instance to display a report based on another servers web service.I can make a call from the ssrs server using this in the RDL:
<Query>
<Method Namespace="http://machine/webservices" Name="HelloUser">
               <Parameters>
                               <Parameter Name="user">
                               <DefaultValue>Craig</DefaultValue>
                               </Parameter>
               </Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>
This works no problem. but.. I want to have the user parameter "Craig" be a parameter passed in from the web app. If it were a regular SQL data source you would put @user in the query. How do you do it with XML text queries?
We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.
The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.
I have two quick questions:
Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes?
Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.
Code Snippet
SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.
Thanks very much in advance for your help.
Regards,
Steve
Hi,
I'm trying to use query parameters with an Oracle OLEDB Source in a data
flow task and I'm having problems.
I've tried formatting the query each of the following ways...
--
select
frq_code,
frq_name,
update_frq,
uptime_frq
from frequency_bcs
where update_frq > ?
and update_frq <= ?
--
Parameters cannot be extracted from the SQL command. The provider might not
help to parse parameter information from the command. In that case, use the
"SQL command from variable" access mode, in which the entire SQL command is
stored in a variable.
Additional information
---> Provider cannot derive parameter information and SetParameterInfo has
not been called. (Microsoft OLE DB Provider for Oracle).
I've got a report that is using a cube as a data source and I can't get the report to show all the data. Only data at the lowest level of the cube is displayed. The problem is that most of the data I'm concerned with is at higher levels. There's no problem with the MDX. I get the correct results when I run the query.
I'm using a table to show the results. I've also tried a matrix, but I get the same results. I'm using SSRS 2005 and SSAS 2000.
Anyone have experience with this? Am I missing something simple?
Hello,
I have a rs script to modify the data source of reports that were built based on report models.
Whenever I update the report model I need to reassign the new report model as a data source.
Since I have more than 1 report model I would like my script to check which report model the report uses so I can update just the reports that use the updated report model.
What is the property or which method I call to get the data source the report use?
According to this I'll know if to relink it to the new report model.
Thank you,
Itzhak
I'm pulling data from Sharepoint via SOAP (GetListItem).
The data pulls correctly. In the VS tab, when I execute the dataset, I can see multiple values and I see what I expect to see.
The problem arises when I actually use the report. I have a single Report Parameter (note that I have no parameters defined in the dataset):
Name: Num
Data Type: String
Prompt: #
Allow Blank Value
From query
Dataset: SPList
Value field: ows_Num
Label field: ows_Num
Default Values: Null
So the report prompts the end user to drop down a box containing a list of items (displaying "Num"). This is exactly what I'd like to see. However, if they select any report, it only displays the first one in the Sharepoint List. No field in the report is set to =First(blahblah). They're all =Field!blahblah.
In addition to this, I have a field in the same Sharepoint list that returns a numeric value (nnnn.nnnnnnnnnn). I want this to be a currency format. I've right clicked the text box -> Properties -> Format -> Format Data and entered C. This does not change the output on the report. It remains a value without a dollar sign and with >2 decimal places.
I'm having the same issue with a datetime field. Sharepoint enters a datetime as mm/dd/yyyy hh:mms. I need to drop the "hh:mms", but again, when formatting the box as d in the Report designer, it still renders with the time.
Ideas?
Hello,
I've created a Report Model Project that can be used by Report Builder to generate ad-hoc reports. I'm trying to create a connection string in my Report Server Project that points to the Report Model Project data source view.
All I can do is create a regular datasource, which bypasses the metadata contained in the Data Source View.
Basically I want my Report Server Project and my Report Builder reports to leverage the same metadata. Is this possible? If so how do I get the connection string?
Thanks!!
-Matt
I prepared an OLAP cube for the report data source in the SSAS 2005. The OLAP cube consists of more than 20 dimensions and several measure groups. I then created the subset/view of the OLAP cube using the "Prepective" function and limit to not more than 7 dimensions on each of the subset. How do I reference the OLAP cube subset as the data source when developing the report in the report designer. Furthermore what is the advantage of creating multiple smaller OLAP cubes with less dimensions comparing to one big OLAP cube with several subset/view attached to it. Thanks.
View 3 Replies View Related
This is a question regarding the use of an XML data source within an SSRS sub-report. This is a two server configuration consisting of SSRS 2K5 and a separate file server exposing xml files over http, IIS 5.
Is it possible, and if yes how, to configure the xml data source to use the application pool identity instead of the current caller identity? I can not turn off impersonation within SSRS. I need to revert to use the W3WC, app pool, process identity within the xml data source. I do not want to configure Kerberos delegation and the file server€™s IIS virtual directory must be set for windows security.