How To Make A Subscription Use The Default Value For A Parameter
Sep 18, 2007Hi everyone,
I need to use the CreateSubscription method and have this new subscription use a default value for certain parameter. How can I do this?
ADVERTISEMENT
How To Make Hiding The Parameter Row As Default?
Apr 17, 2007When I ran the report from the browser (not from the preview when designing the report), the parameters and the View Report button still displayed. I noticed clicking a double arrow button can hide or show the parameter section . How can I make hiding the parameter section as default? Currently showing the section is the default.
Thanks.
DanYeung
Subscription Issue With Null Default Parameter - Key Cannot Be Null
May 3, 2007I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.
The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.
Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Key cannot be null. Parameter name: key
SQL Server 2012 :: Use Of Default Keyword As Parameter Default - What Value Is It
Aug 11, 2015@pvColumnName VARCHAR(100) = Default,
However, I am unable to determine what is the value for Default. Is it '' ?
Default is not permitted as a constant - below fails to parse:
WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)
Default Parameter Value Is No Longer Part Of Cascading Parameter In SSRS 2005?
Jan 30, 2007Hi,
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
How To Use Default Parameter Values With A Date Parameter From A Cube/Reducing Parameters
Oct 15, 2007
Hi,
I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.
My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?
Other question :
Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.
Thanks !
JF
How To Make Or Add A Table With A Field With Default Value
Nov 7, 2007I am doing a shopping basket type demo.I have difficulties going from table with fields like (Name,ProductCode) to table(Name, ProductCode, NumberOfItems). One way could be adding just Name and ProductCode fields and let NumberOfItems come automatically. It could have some common value like 1. How should I do this with VB.
View 1 Replies View RelatedSQL Server 2012 :: Set Default Parameter For Function Parameter?
Jan 13, 2014I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. how to make this work?
Create Function HR.Equipment
(
@startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())),
@enddate Date = (Convert(Date,@StartDate-90)
)
RETURNS TABLE AS RETURN
(
SELECT
EquipID,
EmpName,
IssueDate
FROM HR.Equipment
WHERE IssueDate <=@StartDate and IssueDate >=@EndDate
)
GO
How To Make Enforce Password Policy Unchecked By Default
Jul 12, 2007I am trying to create a login using my application.
The application creates the user and password itself .
when working with sql server 2000 it works fine.
when i try to install my application with sql server 2005 it is displaying the error .
'Password does not meet windows policy requirements
because it is not complex enough'
I want to keep the same password.
Is there any way to disable/uncheck this option by default?
Prashant
How To Set Default Parameter To Select All For A Multivalue Parameter
Jul 24, 2007I have a dataset listing distinct values for items (like 1, D10, M4, etc.) The WHERE statement in my query refers to unit IN(@Unit). I then have 2 report parameters to select 1) a date (datetime); and 2) a multivalue parameter to select one or all of the "units". I would like the second parameter to default to "Select All". Can someone tell me how to do this? I'm sure this is a fairly simple thing but I am really struggling. The report parameter is set as multivalue; My "available values" is set to "from query" and refers to my "unit" dataset and the value and label fields are set to "unit" (only field I bring into this particular dataset). The "Default Values" section is set to "from query" , the dataset is set to "unit" and the value is set to "unit". I can preview the report and select a date but the list of units comes up with all boxes unchecked, including "Select All". Any help will be much appreciated. Thanks.
View 11 Replies View RelatedSubscription Parameter Not A Valid Value
Oct 4, 2007Hi,
I'm trying to set up a data-driven subscription and having problems with parameters. On the Report Parameter Values screen of the Create Data-Driven Subscription function I have set a parameter TimesheetPeriodEnd to "Specify a static value" and checked the NULL box. In the report the parameter is type string and allows nulls. When I try to run the subscription the reports are not sent and the error log contains the following message:
ReportingServicesService!library!f!10/05/2007-09:31:08:: Status: Failure sending mail: Default value or value provided for the report parameter 'TimesheetPeriodEnd' is not a valid value.
ReportingServicesService!notification!f!10/05/2007-09:31:08:: Notification 0ae544df-4b1b-4a46-b7ab-cdefdca201be completed. Success: False, Status: Failure sending mail: Default value or value provided for the report parameter 'TimesheetPeriodEnd' is not a valid value., DeliveryExtension: Report Server Email, Report: Timesheet, Attempt 0
I am also setting other parameters for this subscription in the same way and not getting errors.
Any ideas? Previously I have had problems passing null values to parameters from the subscription query "Get value from database" from SELECT NULL AS ParameterName (this function just doesn't seem to work in SSRS). But I'm surprised I can't even seem to get this working with the default settings.
Regards,
Greg
Pass Parameter For Subscription
Oct 17, 2007Hi
I want to create subscription for the report.
I have 1 date parameter. and i want the value of the date parameter as today s date.
How can i set value of the parameter as today's date.
I have tried using
getdate
today
now
getutcdate
didnt work.
any other option.
I need to create subscription either from report manager or from management studio
Sync_type Parameter In Subscription
Jun 12, 2006Hi,
I tried to change the subscription property "sync_type" whith a system procedure but I was not able to do it, because sp_changesubscription does not support this property.
Is there any chance to change this property by procedure?
So I decided to drop the subscription and define it new with sp_addsubscription. I chose to set this to "replication support only" as the value "none" is depricated based on the online help. Unfortunately using this value results in an error message "only none or automatic is allowed".
How can I set this value now to "replication support only"?
I need to set the value to "replication support only" as this value leads into the generation of the stored procedures used for insert, update and deletes.
The purpose behind this, to restart a transactional push replication, doing all the initialization stuff by myself and not with reinitialize subscirption. I would like to decide, if I do the initialisation.
Regards
Nobsay
The Subscription Contains Parameter Values That Are Not Valid.
Feb 28, 2008
Hi,
We have a report server which has lots of reports. this particular report has 375 subscriptions and 80% of them are failing with the error "The subscription contains parameter values that are not valid." since last week nothing new was done on the server. when I open the properties of one of the subscriptions, one of the parameter value shows as a number where as it should be the name of a state.
Not sure what to do to get the subscriptions back.
any Ideas/help greatly appreaciated.
Thanks,
Phani
Parameter Driven Subscription - Error
Apr 22, 2008I have set up a parameter driven subscription and am having strange results. This subscription is being set up as Web-archive, including the report and link.
At first when setting up the report, I didn't have a default value set in the report, but would choose it in the subscription and would see an error saying the parameter value provided is not valid. After seeing the error I would edit the subscription and see the parameter value was blank. This would happen if the subscription was set to start after at least a few hours. This didn't matter if I chose the subscription schedule or using a shared schedule.
When I set up a subscription the same way, but then had it delivered within the next few minutes, the report would work fine, using the given parameter.
Since one of reports is being sent to a manager, I put in a default parameter value and set up a few subscriptions. I was hoping at least if the subscription "didn't remember" the parameter value, the default would be used and delivered. I was right, the report was delivered using only the default... but will only be delivered using the default.
There have been quite a few people posting similar questions, but none of them are answered.
Any help is greatly appreciated.
Thanks in advance,
Eric
Ps - here is some of the log file relating to one of the failed subscriptions:
From: ReportServerService*.log
ReportingServicesService!library!10!4/19/2008-02:00:05:: i INFO: Schedule 93453b16-89d3-48d1-a470-d7a09fc163b6 executed at 4/19/2008 2:00:05 AM.
ReportingServicesService!library!10!4/19/2008-02:00:05:: i INFO: Schedule 93453b16-89d3-48d1-a470-d7a09fc163b6 execution completed at 4/19/2008 2:00:05 AM.
ReportingServicesService!dbpolling!10!4/19/2008-02:00:05:: i INFO: EventPolling finished processing item e40d5803-bb69-4d20-bd55-797074969348
ReportingServicesService!dbpolling!12!4/19/2008-02:00:05:: i INFO: EventPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!f!4/19/2008-02:00:05:: i INFO: EventPolling processing item fe04012c-6376-4599-b980-6aa1c77036fd
ReportingServicesService!library!f!4/19/2008-02:00:05:: i INFO: Schedule 93453b16-89d3-48d1-a470-d7a09fc163b6 executed at 4/19/2008 2:00:05 AM.
ReportingServicesService!schedule!f!4/19/2008-02:00:05:: Creating Time based subscription notification for subscription: f2551ca1-f6b8-4394-8036-7fa8a9be1070
ReportingServicesService!library!f!4/19/2008-02:00:05:: i INFO: Schedule 93453b16-89d3-48d1-a470-d7a09fc163b6 execution completed at 4/19/2008 2:00:05 AM.
ReportingServicesService!dbpolling!f!4/19/2008-02:00:05:: i INFO: EventPolling finished processing item fe04012c-6376-4599-b980-6aa1c77036fd
ReportingServicesService!dbpolling!12!4/19/2008-02:00:05:: i INFO: NotificationPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!f!4/19/2008-02:00:05:: i INFO: NotificationPolling processing item d1e8f484-659e-48a1-a5da-747108ce63af
ReportingServicesService!library!f!04/19/2008-02:00:06:: i INFO: Initializing EnableIntegratedSecurity to 'True' as specified in Server system properties.
ReportingServicesService!library!f!04/19/2008-02:00:06:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter 'day' is not a valid value., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter 'day' is not a valid value.
ReportingServicesService!notification!f!04/19/2008-02:00:06:: Notification d1e8f484-659e-48a1-a5da-747108ce63af completed. Success: False, Status: , DeliveryExtension: Report Server Email, Report: Daily RTS time, Attempt 0
ReportingServicesService!dbpolling!f!04/19/2008-02:00:06:: i INFO: NotificationPolling finished processing item d1e8f484-659e-48a1-a5da-747108ce63af
Using Parameter Values In Subscription Fields?
Sep 6, 2007Anyone know if it is possible to reference the parameter values stored for a given subscription, in the subscription fields (subject/comment)?
So instead of just @ReportName at @ExecutionTime it could be @ReportName at @ExecutionTime (@ReportParameter1)
I know I can do this in data driven subscriptions, but was wondering if it was possible without.
Thanks,
The Subscription Contains Parameter Values That Are Not Valid
Mar 10, 2008Hello,
I have something like 100 regular subscriptions.
It sometimes happen that one of the subscriptions suddenly stop to work with error "The subscription contains parameter values that are not valid". Example, one report has a dozen subscriptions, one of them stop to work. I look into the subscription and see that either one of the parameters is no longer set to a value or see nothing special besides the warning at the top of the subscription page. Parameters are all set to either default or some value(s). I reset one of the parameters, validate, or simply validate with no change and it works again.
Next time, in one or three weeks another subscription will fail, I will reset it.
Sometimes there are no errors, sometimes just 1 error and sometimes multiple errors accross reports or sometimes one report will suddenly have a few errors.
Right now, I use a weekly report to warn me after the fact of what were last week errors if any (no data, parameters unvalid, etc).
It looks like it happen with strings parameters based on datasets like "Sales offices" by example, where value and label are the same. Note that sales offices are by nature very stable, we do not change the spelling nor add remove entries. The list being a dataset gets rebuild, just to make sure we do not miss any eventual new sales office but if there is a new sales office, no one had a chance to select it as parameter value before it is in the dataset.
I am really not sure what is going on, were parameters values changed in the metadata? I do not think so.
Anyways, Is there a way to get an early warning instead of an after the fact error warning?
Is there anything else I coud try to "Refresh" or "remap" parameters values to avoid these errors?
When the subscription was created by a business analyst, If the parameters becomes blank, I have a hard time figuring out what it was when it was still valid. Is there any way to have a list of parameters values and have these values automatically "re-selected" by report server when the value gets lost?
This is not a big problem however it is annoying and people will have a bad perception of the system quality. "I created a subscription for Sacramento and never got my report", some people will also introduce garbage because they will create a new subscription instead of fixing the broken one, then they may eventually get twice the same report.
Any thought, does anyone had the same problem?
Dynamic Parameter Values For Subscription
Jan 26, 2008I have been trying to add in date functions for parameters in a report subscription and they are not accepted. I have a report with 2 parameters, @start and @end. I have default parameters set wtihin the report of Now - month for @start and Now for @end. Can my users create a subscriotion using something like "=DateAdd("D", -1, Now)" for the parameters rather than the default?
How To Make New Parameter In Sqldatasource?
Jan 31, 2008Hi there,I'm new to db stuffs and I'm using sqldatasource to pull my data from the server. Here's the codes.<asp:SqlDataSource ID="testSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:p01impConnectionString %>" SelectCommand="SELECT [a], [b], [c], [d], [e] FROM [MYDB] WHERE (([a] = @a) AND ([b= @b))" CancelSelectOnNullParameter="False"> <SelectParameters> <asp:ControlParameter ControlID="TextBox1" Name="a" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBox2" Name="b" PropertyName="Text" Type="String" /> </SelectParameters></asp:SqlDataSource> notice that I haven't used [c], [d], [e] and I want to declare a parameter, something like: Total = c + (d*e)can anyone show me the syntax to do this UNDER sqldatasource? I then will have to put that Total in a gridview (i can solve this part)Thanks
View 1 Replies View RelatedMake Use Of An Passed XML Parameter
Nov 19, 2007I have tested the following code, and it works for me:
DECLARE @XML XML
SET @XML = '
<DocumentElement>
<data>
<item code="ABCDEFG" quantity="1" sort="0" />
<item code="XCFVGBF" quantity="1" sort="0" />
<item code="ABCDEFG" quantity="10" sort="0" />
</data>
</DocumentElement>'
SELECT SUM(x.qty * ISNULL(p.price_mn,0))
FROM (SELECT [Code] = A.A.value('@code','varchar(20)'),
[Qty] = A.A.value('@quantity','INT')
FROM @xml.nodes('/DocumentElement/data/item') AS A(A)) X
LEFT JOIN productprice_t P ON p.code_id = x.code
The question is, how can I do this if the XML is in a different format & doesn't use any attributes?
So it looks like this (which I'm getting from an ADO.Net datatable using .WriteXML):
<DocumentElement>
<data>
<code>ABCDEFG</mercurycode>
<quantity>1</quantity>
<sort>0</sort>
</data>
<data>
<mercurycode>XCDFEG</mercurycode>
<quantity>2</quantity>
<sort>0</sort>
</data>
<data>
<code>ABCDEFG</mercurycode>
<quantity>10</quantity>
<sort>0</sort>
</data>
</DocumentElement>
Passing A Parameter To The Filename In A Report Subscription
Jan 18, 2007I have defined a monthly substription for a series of reports. I would like to be able to pass a paremter used in generating the report to the file name define in the subscription.
I can do it if I used system names such as @datetime, but I want to be able to use a parameter unique to the report.
For example if the parameter for the report was CARS and I was quesrying Toyotas how can I get toyoats to show up in the file name?
Any suggestions?
Delivering Multi Parameter Reprots By DD Subscription
Nov 15, 2007
HI All,
If I use more than one parameters in my reports, still would I be able to deliver those reports by Data Driven subscriptions?
Thanks
Data-driven Subscription - Invalid Parameter?
Aug 23, 2007Hi,
I've set up a data-driven subscription on a report but it's not sending and the error log says it's because:
Default value or value provided for the report parameter 'CSRFaxID' is not a valid value.
For the particular dataset record causing the error I'm passing the value Null to the report parameter. The report parameter CSRFaxID is set up with "Allow Null" and with a default value of Null. I can run the report in my browser with CSRFaxID set to null, but the scheduled subscription is not able to produce the report with the same parameter value. Does anyone have any idea what the problem is and how I can fix it?
Regards,
Greg McNamara
SQL Parameter And Wildcards, How To Make It Work?
Oct 22, 2007
Hello, I have what should be a very simple problem, but I cant solve it.
I want to have a stored procedure return a table query (no problems here) but I also need to supply several parameters to the stored procedure (again, no problem!)
Here is the problem, I need to be able to supply a wildcard into the stored procedure as an argument somehow. I can do this already, but the results are incorrect!!! It seems like when local variables are used, the wildcard argument gets ignored. for example, I have included the following example:
DECLARE @Dv_id nchar(15)
SET @Drv_id = '%'
SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE @Dv_id
SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE '%'
OK, this is an example of my problem, the results I get from this are that the fist SELECT return 0 rows.
The second SELECT returns the correct number of rows (everything in the table). Why is there a difference between:
WHERE Drv_id LIKE @Drv_id
and
WHERE Drv_id LIKE '%'
?
The wildcard statement '%' is supposed match everything, correct??
It seems like the local variable SET command syntax eats up my value of '%' and turns it into a NULL.
Is there any way around this?
How Do You Make A Report Parameter Invisible???
Jun 4, 2007Hi, I have 3 report parameters.
If a user selects 'value1' from the first parameter list then parameter list 2 & 3 should be open for the user to make their selection. BUT if the user selects 'value2' from parameter 1 then only parameter list 2 should be open and parameter list 3 invisible. Is this possible??
Data-driven Subscription: Put Parameter In The Filename Text
Feb 1, 2007Hi all,
I've developed a data driven subscription report. I have a paramete (named "Data") that is a result of my query to the current date. It is working fine.
Now I would like to make one change: In the step4 of the creation of the data-driven report we have the option to give a name to the filename.The name that I gave was TestFile. In this option i'm also able to select the parameter instead of giving the name to the filename. Can I make something like TestFile_ & @Data? Wich would result in TestFile_01-02-2007? Or the only way is to make, in the query of the paramenter another field with this result?
Thanks in advance.
Marco.
Passing Null Value Parameter To A Data-driven Subscription
Jun 13, 2007I have a data-driven subscription (DDS) stored procedure that gets called when a subscription fires and returns parameters to a report. One of the parameter values I want to return is NULL, which also happens to be the default value of the corresponding report parameter.
When I set the report parameter to use the default value (NULL) when setting up the subscription in Report Manager, the subscription works fine. When I try to pass the NULL value from the DDS proc to the report, the subscription fails.
I do not know what the error logs say. I've rarely looked at them and what I have seen seems a bit cryptic. I've narrowed down through trial-and-error that passing the NULL value is the issue.
Has anyone else experienced this issue and do you have a resolution?
Thanks in advance for your assistance.
How To Make Subreport Visible Based On Parameter Condition?
Aug 29, 2007
Hi,
I have a subreport added to the main report and I want to make this report visible only when the parameter value is met.
Ex, I have a parameter CustName in the main report and want to show the subreport when the custName = xxxxx. There reports are parameter driven not data driven reports.
Any help is greatly appreciated.
Thanks,
Sirisha
Make Items Bold In Parameter Selection List
Mar 19, 2007Is it possible to make certain items in a parameter selection list appear bold?
View 3 Replies View RelatedIntegration Services :: Make Sure That The Required Parameter Is Set - Error
Sep 4, 2015I'm trying to create an SSIS package job in SQL server 2014 but I get the following error when trying to change the Package source and confirm. I have alsredy checked the connection mangers and they were all successful, I'm not sure what I should be checking.
"Make sure that the required parameter "name of parameter" is set".
SSRS -- Data Driven Subscription And Pivoting For Dynamic Parameter Mapping
Feb 12, 2007Hi,
For the Data Driven Subscription in SSRS we are using the following stored procedure
In Step 3 - Create a data-driven subscription
create procedure spRSGetReportSettings
(
@ReportID as integer
) as
begin
set nocount on
declare @t as table(y int not null primary key)
declare
@cols as nvarchar(max),
@y as int,
@sql as nvarchar(max)
set @cols=stuff(
(select N',' + quotename(y) as [text()]
from (select ParameterName as y from Reportsettings where reportid=1) as Y
order by y
For XML Path('')),1,1,N'');
set @sql=N'select * from
(select reportid,parametername, parametervalue from ReportSettings where reportid= ' + Cast(@ReportID as varchar(5)) +' ) as D
pivot(min(parametervalue) for parametername in(' + @cols +N')) as p'
exec sp_executesql @sql
end
Basically the idea is to maintain a single report parameter setting table for multiple reports.
Structure of the table is as given below
ReportID, ParameterName, ParameterValue.
Using Pivot we can generate the ParameterName/ParameterValue combinations for each report. This stored procedure is working fine in query editors(Management Studio)
But, in SSRS it is giving any results.
In Step 4 - Create a data-driven subscription,
Get the value from the database drop down, I am not getting any database columns.
Please help.
Kumar
Reporting Services :: SSRS Custom Start Date Subscription Parameter
Jun 3, 2015Is it possible to have a custom start date parameter on a report? I would like to have a cumulative daily report for the week, Friday to Thursday. The final daily to run on the Friday, then the next week begins. I have a enddate, which is today()-1 (for previous day); I need a start date to be variable.
In other words,
On a Monday, report pulls data from last Friday to Sunday;
On a Tuseday, report pulls data from last Friday to Monday,
On a Wednesday, report pulls data from Friday to Tuesday, etc,
until on Friday, the report pulls data from last Friday to Thursday.
Transact SQL :: How To Make Procedure Work For Multiple Values In Parameter
Jun 7, 2015Below is the stored procedure i have it works fine if i have 1 value passed to @invited_by but i want to modify but i want this code to be working for multiple inputs .Lets say if i do
exec [dbo].[sp_GetInvitationStatusTest] 'Test1 . I get the desired output but i want this procedure to work for
exec [dbo].[sp_GetInvitationStatusTest] 'Test1,Test2'.
USE [merck_acronyms]
GO
[code]....