I am creating SSRS reports on top of SSAS cubes. I want the default value of parameter to change dynamically based on the current year or it should select the last of the parameter values.
Can this be done?
Hi all,
Does anybody know how to set the parameter's default value dynamically?
I'm working on a report with some parameters against datacube, and I hope the default value of one of the parameters could be set dynamically based on the user's login.
Thanks,
Jone
Hi all,
Does sombody have experience on dynamically set or change the default value of a report parameter?
Assuming: report parameters p1, p2, p3, p4 have been set up(and have their default value 'all') with the creation of the report1; report browseing is through reportviewer that embedded in the web application; datasource is datacube
What I want to do: based on the login user of the my web application, set default value of p1 as the user's username.
What I did is:
Microsoft.Reporting.WebForms.ReportParameter reportParam = new Microsoft.Reporting.WebForms.ReportParameter("P1","Mary");
ReportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WebForms.ReportParameter []{ reportParam });
what I got when reveiw the report:
P1 has no default value, and p2, p3, p4 greyed and could not choose value from them.
Any idea and suggestion will be pre-appreciated!
Jone
Is it possible to fill a parameter list with values based on another parameter value?
Here's what I have so far (which hasn't worked)...
I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.
I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).
I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.
Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...
The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'.
The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid.
...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?
Any help you can give me would be great!! Thank you
My requirement is to have my report display on one 11 by 8.5 page. Is there a way to programmatically change the PageHeight report property based on the number of rows returned in a table? i.e., if the rowcount for table x >= 10, change PageHeight to 9.5.
Any ideas?
Thanks.
Brad
@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)
i am transfering the table from one database to csv file format..i did it.. again i want to shift that csv files to another databse as tables. how to do this task.. pls help me.. its very urgent..out TL had given me the dead line.. send reply soon....
View 5 Replies View RelatedWe have a query in which we have data in fields called TS1Min, TS1Max, TS1Avg, TS2Min, TS2Max, TS2Avg etc.
We have a different table in which we define that vale of TS1, TS2, as an example TS1 might equal RED, TS2 might equal BLUE.
We have written a query that puts TS1Min, TS1Max, TS1Avg, TS2Min, TS2Max, TS2Avg in a temp table #TEMPA
and we also put the values of RED and BLUE in another temp table #TEMPB
now we want to select * from #TEMPA but rename the headings TS1Min to display RED-TS1Min, TS1Max as RED-TS1Max, TS1Avg as RED-TS1Avg etc...
any ideas on how to do this
Hi all,
Is there a way to change the dataset being used by a table dynamically ?
Regards,
Neil
Hi,
In my report, I've to print the address of the customer in a form which is in a pre-defined format. So while printing, the line number and position should be specified by the user. Is there any ways in SSRS to change the position of a textbox/any control dynamically by inputing the x and y coordinates? If there is a way please let me know that.
Thanks in Advance,
Leks
I want to transfer data from one server to another by using SSIS. i want the connection string to be dynamic and also according to the some other variable, the transforming data is changing.
Could you provide me the solution thet how i am able to change my connetction string dynamically and the other variable too.
i am using VS 2003 as front end and SQL server 2005 as a backhand.
Due to VS.NET 2003 i am able to create DTS packages but i have to migrate it and then anly i am able to use it in JOB in SQL server agent of SQL server 2005.
is that any code or any stored procedure from which i am able to migrate DTS packages to SSIS packages.
Thank you
Hi Everyone,I have a question about dynamically changing the length of a varchar(n)field, in case the value I'm trying to insert is too big and will givea "truncated" error, but before the error is given! i.e. Is there somekind of a way to "test" the length of the field while Inserting thevalue into it, and to have it automatically increase its length to thelength of the value being inserted, in case the value is too big?I've been able to do this in a "primitive" way, simply by identifyingthe specific error number in case the value is being truncated, andthen increasing the length of the varchar(n) field by using the ALTERcommand, and then duplicating the insert statement, but is there astandard (shorter) way of doing this?Here is my code (I'm working in an ASP environment):<%var_txt = "abcdefghijklmnopqrstuvwxyz12345678789"sql = "Insert Into Table1 (text) Values ('" & var_txt & "')"On Error Resume Nextconn.Execute sqlIf err = -2147217833 ThenResponse.Write "Error Recognized Successfully!<br /><br />"sql = "ALTER TABLE Table1 ALTER COLUMN text VARCHAR(" &Len(var_txt) &") NOT NULL"On Error Resume Nextconn.Execute sqlIf err<>0 ThenResponse.Write "Error while trying to alter Column:<br/>" & err & "= " & err.description & "<br />"ElseResponse.Write "Column altered successfully to: " &Len(var_txt) &"<br />"sql = "Insert Into Table1 (text) Values ('" & var_txt &"')"On Error Resume Nextconn.Execute sqlIf err<>0 ThenResponse.Write "<br />Error number 2:<br />" &err.description &"<br />"ElseResponse.Write "Now it was added successfully!HaHa!<br />"End IfEnd IfElseResponse.Write "Success."End If%>Thanks in advance!
View 2 Replies View Related
In my SSIS package I have a loop container that I am running the same code against 4 servers.
I have the package export the SQL data to an Excel spreadsheet that has multiple tabs.
Is there a way I can change the tab on the fly or do I need to create a Connection for the same spreadsheet 4 times
Each Connection pointing to a different tab?
I tried to set up a expression for the Excel Connection Manager to use the InitialCatalog for the tab and change it
based on the script in the loop however this causes the following error:
An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Thanks in advance
Before I launch into a long description of the issue, I was wondering whether its possible to dynamically amend the file path held in a configuration string? (I'm looking at the Package Configurations Organizer)
Basically recreating the /CONF switch in the dtexec utility
So a parent package could somehow tell a child package to pull its configuration from a selection of different xml config files at run time... I couldn't see a variable in the child package to set from the former
My only idea at the moment is to replace the Execute Package Task with an Execute Process Task, and directly invoke the child using dtexec & an expression to dynamically set the /CONF switch..
thanks
James
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
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
Hello. What I'm trying to do should be fairly simple, but after an hour of searching and trials, I can't seem to get it working.When I run my website on my test machine, I would like it to use a certain value for my connectionString. But when it's run on the live server, it should use a different connection string. This way, when it's on the test server it'll use the test database.In my Web.Config file, I have this:<connectionStrings> <add name="myConnectionString" connectionString="Data Source=TEST_DB;Initial Catalog=tester;Integrated Security=True" providerName="System.Data.SqlClient" /></connectionStrings>I would like to be able to change the connectionString in something like the Global.asax file, when the application is loaded. I'd check for a certain environment variable, or some other condition, and change the value of the connectionString.I tried this in my Global.asax, but it told me the value was read_only: ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString = "Data Source=LIVE_DB;Initial Catalog=live;Integrated Security=True" Does anyone have a good way of doing this, so I don't need to juggle Web.Config files when I publish my site? Thanks.
Okay, this one might stump you guys.
View 3 Replies View RelatedDoes anyone know how to change the task name displayed within a ForEach Loop Container (or of the ForEach Loop Container task itself) based on a variable. I am pretty familiar with setting variable values during task execution and using expressions to alter task properties based on variables. I have tried using an expression to alter the value of the Name property of the ForEach Loop Container but the name of the ForEach Loop Container does not change during execution. Since the color of the various tasks change during execution, I would think that the task names could be changed as well.
I have a scenario where we have to handle dynamically changing source columns.
For example , some times in the source files the number of columns will be increased or decreased, new columns can be added in the middle or in the end of the source file.
How to handle this kind of scenario in the SSIS ?
I 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
I created a tabular model, and used it as a source for the power view. Is there any possibility to set a dynamic default value for the filter. My model has a field named 'CalendarMonth', by default - filter should take current month. I would like to know is there any possibility with out changing the data in the source.
View 4 Replies View RelatedHello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa
View 2 Replies View RelatedHi, can anyone help me please - I'm still struggling with my database.
My set up is: Microsoft Visual Web Developer 2005, SQL Express 2005, GoDaddy windows shared hosting.
On my PC I have a site which uses membership and the default database which was created after first compilation.
This works fine locally.
However, I need to export this to the GoDaddy server.
To do this I must rename the database (or recreate the project with a newly named database) and figure out how to alter any other settings.
I tried running aspnet_regsql.exe command but I really didn't have any luck.
I would have thought this would be a straight forward operation.
Can anyone give me guidance please.
Garrett
Hi ,
Can someone please help , we are trying to change the default language
from us_english to british englsish as the date formats have to be in dd/mm/yyy for our european clients.
We tried sp_configure 'default language' 23
Reconfigure . It worked 3 months back.
We are trying this on the reverse , now and it doesnt work.
Can some one please Help !! Its Urgent !
Thanks in advance
Nat
Does anyone thing it is a wise thing to change the default port for SQL? Will this adversely affect the server? Thanks.
View 2 Replies View RelatedHi.
I thought I had this solved but after running aspnet_regiis again
I am having the same problem.
I can't log onto my sql server because the default DB is master.
The following command using sqlcmd gives me the master db:
Code:
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO
So, to try to fix this i ran:
Code:
EXEC sp_defaultdb 'sa', 'my_default_db'
GO
and no luck. I went over to the DB server and checked
the permissions under SERVER/SQLEXPRESS > SECURITY > LOGINS. All except NTAuthority and Administrators have
my intended DB as the default (including ASPNET).
This is frustrating because I know I have figured it out
before but I can't remember what I did. Help appreciated.
Thanks.
Does anybody know how I can change the default value for a column?I was trying to remove the default value in order to add the new oneafterwards. This is what I tried:alter table /table-name/ drop default for /column-name/alter table /table-name/ alter column /column-name/(/new-decl-without-default/)It did not work. I cannot find a solution in the documentation. Maybeyou can help me out?Thank you,Johan
View 4 Replies View RelatedA few weeks ago a client asked me to add a column to a table so Icreated this script:ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULLCONSTRAINT fldRenewalStatus_Default DEFAULT 0Now they want to change it from a BIT to an INT, to store an enum.Fair enough. However, no matter how much I wrangle with a script, Ican't find a reliable way to alter the column. I've mixed and matchedthe following and nothing seems to work:EXEC sp_unbindefault 'tblIndividual.fldRenewalStatus'DROP DEFAULT DF_tblIndividual_fldRenewalStatusALTER TABLE tblIndividualDROP CONSTRAINT fldRenewalStatus_DefaultALTER TABLE tblIndividualDROP COLUMN fldRenewalStatusGOALTER TABLE tblIndividualADD fldRenewalStatus int NOT NULLCONSTRAINT fldRenewalStatus_Default DEFAULT 0Thoughts?ThanksEdward
View 4 Replies View RelatedHow can i change the default value of a column? I already have a columnnamed DateOfRental but I want to alter it so that it has default valuegetdate()ThanksDavid--http://www.nintendo-europe.com/NOE/...=l&a=Prodigious
View 4 Replies View Related
Hi,
I've created a new Stored Procedure template and saved it to .Microsoft SQL Server90ToolsShellTemplatesSqlStored Procedure. I can view (and use) the template via the Template Explorer, however, I want this new template to become my default template.
What do I mean by this? - I want to be able to right-click on stored procedures in my object explorer, click New Stored Procedure, and be forced to use my new template, rather than Create Procedure (New Menu).SQL.
Thanks
Jon Derbyshire
Hello,
Our security center just scanned my database server and has asked that I change the database server from the default instance name and change the default port used. When installed MS SQL Server 2000 I used the default installation settings. I have installed SP4 and this is all running on a fully patched WIN 2003 Server machine. Is this possible without un-installing and then re-installing?
Thanks for any and all help.
Ben
Best practice talks about the use of name conventions in the SSIS packages.
Is there a way to rename the default names of the tasks in the DataFlow and ControlFlow once and for all, so that each time I drag a new DataFlowTask in, the default name will be DFT and so on.