Hi,
I am not sure this is the correct place to post this
question, but here it is. I am trying to pass some parameters to SSIS
from a report using the report parameter, then SSIS will create the
datareaderdest and return to the report to use.
Anyone have any idea, guidance or leads please share it out. Thanks in advance.
Daren
I am working on existing ssrs report. When I see the preview I could see the extra parameter than the actual parameter.
How do I know , when this parameter is coming in the preview ?
Hi,
I need "conditional" cascading parameters: In Report Manager when one changes parameter 1, parameter 2 get changed based on parameter 1. Optionally, one can also enter values to parameter 2 directly.
I was able to achieve this in SSRS 2000 (SP2) with the following setups. SSRS 2005 and SP1 no longer works - Parameter 2 always shows its default value regardless whether one select a value in Parameter 1 or not.
Parameter 1
available values: from query
default values: non query (specify a value "<None>")
Parameter 2
available values: Non query (no value specified)
default values: from query (based on Parameter 1)
It seems to me that the default value in SSRS 2000 is considered as cascading parameter. But it is no longer the case in SSRS 2005.
Is this a SSRS 2005 bug? is there any other work arounds or suggestions?
Thanks.
Kong
I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful.
Thanks
I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter. If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank. However, sometimes this will automatically add.
Is this a bug in Visual Studio? How do I get around this?
public Sub Main()
Dim url, destination As String
destination = Dts.Variables("report_destination").Value.ToString + "" + "Report_" + Format(Now, "yyyyMMdd") + ".xls"
url = "http://localhost/ReportServer?/ssis_resport_execution/ssis_ssrs_report&rs:Command=Render&ProductID=" + Dts.Variables("ProductID").Value.ToString + "&user_id" + Dts.Variables("user_id").Value.ToString
+ "&rs:Format=EXCEL"
SaveFile(url, destination)
Dts.TaskResult = ScriptResults.Success
End Sub
How to pass more than one variable values in ssis as parameter values to ssrs. With the above code its showing as empty.If i am taking single variable i am able to render the data into excel sheet.
How do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\PackageName.dtsx /Set Package.Variables[StartDate];"&Parameters!StartDate.Value
How do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\PackageName.dtsx /Set Package.Variables[StartDate];"&Parameters!StartDate.Value
Hi,
Does anyone know of a link or list that has all the parameters for the "rs:" section of the URL access parameter, except for the ones in the Microsoft books?
Thanks,
Please can someone provide an overview of how to select all records in the parameter list. I am trying to get an understanding of how this works.
Having a union join with all value is fine, to retrieve the value of 'all'. However, given the data-set there is no 'all' value. From reading the ms course notes, the query syntax is: select from where = (field = @x) or @x = 'all'. How does this syntax work? Does x = all, so all= all = true then returns all records?
Regards,
Nijojo
All,
I have a report that needs to return a number of columns from a table based on 2 parameters [StartDate & EndDate [datetime]].
Here is my dataset
Select * from table1 where start_date between @StartDate and @Enddate.
In table1 [start_date datetime ,example value=[3/6/2008 6:41:47 PM]]
My problem is how do I convert the start_date to [3/6/2008] within my Dataset.
Something like the below.[I get an error when I do the below]
Select * from table1 where convert(varchar(10),start_date,111) between @StartDate and @Enddate.
The reason for doing this is because when I pick, StartDate as 01/01/2008 and EndDate as 1/31/2008.My report only return's values of 01/01/2008 to 01/30/2008.The items that falls on 1/31/2008 do not get displayed as the datasets does not consider time when comparing,but only the date part.
Do give me a suggestion,solution to this.Thank You in advance!
I don't want to use SSRS's GUI for parameter entry. It's quite minimal and not very easy to use for users and doesn't cope very well with large selection lists (E.g if you wanted to select 3 companies from a list of 60 000 to run a report for).
So I want to make my own parameter entry screen and then pass only the user parameter value selection on to the report parameters.
Can I do that? Are the parameters exposed?
I'd wanna do something like:
My_SSRS_Rep.param1=@MyValFromMyApp1
My_SSRS_Rep.param2=@MyValFromMyApp2
My_SSRS_Rep.Run/Refresh/View
Currently I just have a Windows form with a web browser control in it.
And I've set the url to http://myserver/reportserver?%2fDB+Report+Server%myRep&rs:Command=Render
So it is possible?
Or do I need to go about this some other way like using the SSRS Report Viewer or some other mechanism?
Answer:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88484
Report Viewer it is then!
UPDATE1:
Or maybe not...
When I do the following, the report does not refresh:
Dim param(0) As Microsoft.Reporting.WinForms.ReportParameter
param(0) = New Microsoft.Reporting.WinForms.ReportParameter("Test", a)
Me.ReportViewer1.ServerReport.SetParameters(param)
Me.ReportViewer1.ServerReport.Refresh()
Anyone know why?
There is a select all option for each multivalue parameter. If this option is selected, I would like to display in the report that the parameter selected is "ALL" instead of using the Join(parameter!parametername.value,",") to display all the values. Any idea how I check to see if the select all at the beginning is checked?
View 13 Replies View RelatedHi All,
I want to toggle visiblity of a parameter in sql server reporting services 2005. Please anyone tell me how to do it?
Please help me.
I want to toggle visiblity of a parameter at runtime.
Please tell me how to achieve this.
I am having trouble adding a smalldatetime parameter @ScrubbedDate to my procedure and getting it to work with my report.
I get the cannot convert string to smalldatetime error.
I want to create a parameter in SSRS and use it in the MDX query.But when i am trying to access the parameter , the resultset is returned blank.I want to get only the logo matching the customer number.parameter field is a double type. Below is the query listed.
With
Member [Measures].[Logo1] AS
Case
When IsEmpty([Client].[Logo].MemberValue ) Then 'logo-chemwatch.jpg'
When len([Client].[Logo].MemberValue)=0 Then 'logo-chemwatch.jpg'
Else
([Client].[Logo].MemberValue)
End
SELECT {[Measures].[Logo1] } ON COLUMNS, { ( [Client].[Logo].[Logo].ALLMEMBERS)} ON ROWS FROM [Chem Watch]
Where [Client].[Customer No].[Parameters!Location.Value]
I am facing some issues with parameter in SSRS reports that when my parameter contains the space then i get the syntax error can be seen below.If i choose any other parameter value which do not contains the space then it works well. I am using SSAS cube as a data source.
View 4 Replies View Related
Hello,
I tie up columns with parameters.what ever column are selected only those column should be displayed. Now my parameter are string data type.
I write this expression in column-->propertyvisibility--> hide-->Expression
=iif(len(Parameters!VALUEDATE.Value(1)) > "0",false,true)
I am gettin error saying "Index was outside the bounds of the array"
kindly help me !!!
I've a SSRS monthly sales report with the sales details for current year and last year. I've 4 paramaters in the report. StartDate1, EndDate1, StartDate2 (hidden), EndDate2 (hidden).
For
StartDate1 - Beginning of Last month -
DateAdd(DateInterval.Month, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1)) (Will give me Feb 1 2015)
EndDate1 - End of Last month -
DateAdd(DateInterval.Minute, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1)) (Will give me Feb 28 2015)
StartDate2 - Need to get startdate as Feb 1 2014
EndDate2 - Need to get enddate as Feb 28 2014
I know this is the code to add a single valued report parameter to a report via asp.net
Dim paramList As New Generic.List(Of ReportParameter)
paramList.Add(New ReportParameter("MyParameter", "MyValue", False))
Me.ReportViewer1.ServerReport.SetParameters(paramList)
But how do I go about adding a multi valued parameter?
Hi,
I wanted to know more about validation of SSRS parameters. I have a simple report which has a parameter called startdate of DateTime datatype. The datetime parameter in SSRS takes manual input as well. So, the user can enter any junk value. I want to ensure that the input parameter is in correct format and I want to display an error msg when the format is incorrect. My report has the following VB code for validation:
Public Function Validate( ByVal startdate As String) As Boolean
If IsDate(startdate) = True Then
Return True
Else
Return False
End If
End Function
And my report has a textbox which has the expression property set to;
=Code.Validate(Parameters!startdate.Value)
the textbox on the report has to display if the entered date is valid or not.
But, when i enter an erroneous date, SSRS doesn't render the report and throws a generic error. This happens even before the code written for validating the parameter executes.
Also couldn't find a way to disable the manual input for the datetime parameter. Even that would solve the problem.
Another alternative was to make the startdate parameter as string, but i want the calendar control button to be provided for the user.
Currently i am setting up a report. The data source comes from pre-defined custom datasets where i've pre-entered the parameters for both Month and Year.
I already have one table up and working, but now i need a new table below it with a % column for each month so it gives visibility for the trend in recent months. As the interactive parameters are already in place; when i try to create the table it comes through with the specific month that the parameters are set too, and change when the parameters are altered.Is there a way to ignore the parameters for this table; or get around it?
I am trying to calculate a measure based on parameter passed I have a liquidation rate of amount/facevalue when the dimension attribute is 'all' and i will like to the 'amount' to change based on the parameter passed and the 'facevalue' should not changed , i tried with the code below.
What I noticed is when i add a calculated member to return the currentmember of the cell member
measures.strat
as
[Strategy].[ICS].CURRENTMEMBER.uniquename
it comes back as
[Strategy].[ICS].[All]
even when I changed the default value for the parameter to another member like red
WITH
MEMBER
MEASURES.TEST
AS
((
CASE
WHEN [Strategy].[ICS].CURRENTMEMBER.uniquename = '[Strategy].[ICS].[All]'
THEN
[Measures].[Measures].[AMOUNT]
[code]....
hi all
m newbie for SSRS 2005 . when i ran the folowing query it gives an error like "An error occurred while executing the query.Parser: The syntax for '.' is incorrect".
WITH
SET [FilteredAccountSet] AS 'order(Parameter!Account.Value,[Measures].[_MeasureBillingCode],ASC)'
SELECT {
[FilteredAccountSet]
} ON COLUMNS
FROM [Profitability]
may i know the reson?????
Hi all,
I'm sure what I am trying to do is very simple - but I just can't seem to figure it out. I have a report based on a SSAS cube (SQL 2005). The report shows sales based on the dates the user selects from the parameter field (the date parameter field comes from a Y-Q-M-D hierarchy). This all works fine.
What I would like to happen is for the members within the last 3 months to be automatically selected so that the report automatically executes for the last 3 months.
Can anyone help or offer any advice. If possible I would like to achieve this using the GUI features so that power users can use the "plug and pray interface".
Many thanks
Matt
In an existing ssrs 2012 report, I want to basically add the following logic to a new dataset that will be used by a new parameter called 'Inventory'.
SELECT DISTINCT Inv_number as value, Inv_name as label
FROM s.dbo.fnUser( @endYear, 0, 0, 0)
WHERE (@report is not null)
order by label,value
In this ssrs report, there are 15 reports that can automatically be generated. I basically want this new parameter called 'Inventory' to only be active when the 4th report is selected to be generated. Thus in the query I listed above, how can I change the query to only be active when the 4th report is selected?
I tried to change the where clause to (@report(4) is not null), but that did not work.
Hi All,
I have a question regarding how to use report prompts in SSRS reports that are based on SQL queries.
When I added the prompt into the query for use as a filter value, it says that there is an error.
It does not recognise the '!' inside the parameter prompt string. Example is parameter!month_prompt.Value, which the ! is not recognised.
Your help is much appreciated.
Thanks & Regards,
Mohd Fadzli
I have created ssrs report which has a parameter for start date. I want another parameter which shows time-frame of 6months,12 months,18 months,24 months which calculate dynamically from my start date
select distinct [dbo].[MSP_EpmResource_UserView].[ResourceName], [dbo].[MSP_EpmProject_UserView].[ProjectUID],[dbo].[MSP_EpmProject_UserView].[ProjectManagerName] ,
[dbo].[MSP_EpmTask_UserView].[TaskStartDate],[dbo].[MSP_EpmTask_UserView].[TaskFinishDate],
[dbo].[MSP_EpmTask_UserView].[TaskPercentCompleted],[dbo].[MSP_EpmProject_UserView].[Project Status] AS StatusForExecutiveReporting,
[Code] ....
In my report I have two parameters PID and patientName, but user want to enter either PID or patientName. When I preview the report user can only enter one parameter not both. Is there any way to create two parameter available but users can enter only one parameter value?Here is my report layout and query I used for that report. These two parameters comes from one column 'String_NVC' as from query and column name 'Description' as from the report layout.
SELECT Username_NVC AS Username,
String_NVC,
Time_DT AS UserActionDateandTime,
FROM test
[code]...
I created a parameter with available values from a dataset.
By default i want it to display the maximum value. and then the user can select the dates if he does not like the default value.
How to i do this. because when i tried to set the paramter default value to max(dataset!dt.value) it says expression canot be used.
How to do this. Should I create a dataset with maximum value and then assign it to this one.
Hi,
i am working on SSRS 2005. i have one problem while accessing report throgh ASP.net pages.
i have used multivalue parameters in the report. on the report server it works fine but on the .asp page when that report is accessed, drop down is not working,
can anybody guide me regarding this , what to do?
thanks and Regards,
Vishruti.
I am working on SSRS 2008 R2 in BIDS. The report's data source is from Stored Procedure which is running good in SQL Server 2008 R2.
The stored procedure has an output parameter, which I don't know how to set in BIDS, so when I try to add the data source to the report, a mistake is shown up.
So my problem is:
1. How to set the output parameter correctly, so the data source(stored procedure) can be run as good as it were in SQL Server 2008 R2.
2. How to get the output parameter value in BIDS which will be used to set the title of table dynamically.