Cascading Prompts
Jun 30, 2005
This question is for Bill directly. Hi Bill, I have lots of experience with RS, implementing the beta in a production environment, I was so impressed. Currently my company uses Cognos for financial reporting, and I am hoping to replace this with RS and MSAS. I have little knowledge of MDX, so I have been using your various series of articles to get up to speed. However, I don't get the article Mastering OLAP Reporting: Cascading Prompts. I fully understand how to build the prompts, but there seems to be a step missing in the article. You never replace the hardcoded where clause in the base dataset, and I can't seem to work out how to pass the parameter from the cascading prompts to the report. Maybe I'm being stupid, but the article doesn't seem to tell us how to do that.
Many Thanks
Sam
View 3 Replies
ADVERTISEMENT
Aug 17, 2005
I need to implement my cascading deletes on a SQL database. Is it better (performance/reliablility-wise) to use the Foreign Key Cascading Deletes or to just write my own triggers to do the deletes?I was hoping someone had experimented and found which works best.
View 2 Replies
View Related
Nov 13, 2006
Hi guys
I have been asked to develop a query which when run will prompt the user to enter the number of months before runnin the query.
I know we can do this in MS Acess .
can we do the similar sort of thing in SQL as well??
My query is
SELECT DHBMappingTable.[DHB Name], Agency.DHB_service, PurchaseUnitMappingTable.PU, PurchaseUnitMappingTable.PU_name,
SUM([NMDS Data for IDF Report].[Number of caseweighted discharges]) AS Expr1, AVG(wies2.[0607_cwd_WIES11]) AS Expr2,
AdmissionMappingTable.Admission, wies2.Admission_type
FROM DomicileCodes
In the above query, I need to multiply "0607_cwd_WIES11" with the no. of months
Lets say if the user is being prompted to enter the no of months and he enters 3, then "0607_cwd_WIES11" should be multiplied by 3 and divided by 12.
Do i have to use stored procedures(which I have got no knowledge of)
What do I have to do??
Pls help
Thanks
Mita
View 5 Replies
View Related
Oct 19, 2006
I would like to set up a prompt / filter as part of the model that carries through to the report builder. I have a query where I would like to force a prompt on the user as part of any report they create. Ideally, I would like to have it default as well.
I noticed that there is a prompt attribute in model designer. When I go to add the filter attribute in model designer, I get the same dialog box I get in the report builder - except it does not have the prompt as an option. Is there a way to accomplish this?
Thanks
View 3 Replies
View Related
Jan 24, 2008
Hello everyone,
I have a request to hide certain parameters in a report based on the current user logged into our web based report viewer using the ReportViewer control fo asp.net.
I am doing the standard stuff required to display the report as shown below.
Code Snippet
rptViewer.ServerReport.ReportServerUrl = new Uri("http://172.17.144.26/ReportServer"); // new Uri(MINETSecurity.WebSettings.WebServerPath, UriKind.Absolute);
rptViewer.ServerReport.ReportPath = ReportName;
rptViewer.ServerReport.DisplayName = "Report1";
rptViewer.ServerReport.ReportServerCredentials = new CustomReportCredentials("myUsername", "myPassword", "myDomain");
Now that i have loaded the report into the viewer i illiterate through the report parameters looking for the ones which the specified user should not see as show below.
Code Snippet
ReportParameterInfoCollection MyReportParams = rptViewer.ServerReport.GetParameters();
foreach (ReportParameterInfo MyParameter in MyReportParams)
{
// returns a value from a custom class which is a predefined value for this user based on the current user logged into the website
string Value = CustomParameterValues.ToString(GlobalUserSettings.CurrentUser.CustomValueList);
if(Value != "") MyParameter.PromptUser = false;
}
Here is where the promblem lies, when i try to set the value of PromptUser i get the following compiler error.
Property or indexer 'Microsoft.Reporting.WebForms.ReportParameterInfo.PromptUser' cannot be assigned to -- its is read only
Is there anything i can do to set certain parameters invisible to the user and set there value myself. I know i could create 2 seperate reports and set the PromptUser value in the designer but i dont really want to be duplicating reports just to hide or show parameters.
I have tried using the SetParameters method of the ReportViewer component to pass the value of the parameters that should be hidden and even set the visible property to false, but it dosn't seem to work.
Any help here would be appreciated.
Cheers
Scott Blood
View 8 Replies
View Related
May 22, 2008
Hello,
We want to have one prompt (parameter) to set a point in time, so we can use this parameter as filter in other elements.
e.g. We have a point in time prompt, that we can set and use to filter by examples illness records and availability record (by the same (one) prompt)
View 9 Replies
View Related
Oct 15, 2007
Hi,
Suppose I have two prompts, say A and B.
These two prompts are populated by using database queries.
B is dependent on A.
These prompts are multiselect.
I have noticed that it is not possible to deselect them once a value(s) are selected.
If I deselect all in A. They automatically get selected again.
They work fine as long as some value selected.
It is possible however to deselect all in B.
Thanks in advance.
View 6 Replies
View Related
Jan 15, 2005
Hi
Is there any why to Create SQL Server Objects from Command Prompts like (Databases , Tables, Stored Procedures, …) ??
If you will Install some Applications Like this forums you will see the SQL Server object Created from Command Prompts
How Can I do that .. ??
And thanks with my regarding
Fraas
View 1 Replies
View Related
Jan 5, 2008
I am running a report which takes a long time to render. It has 500K rows in it that need to be summed and grouped. After 5 minutes IE prompts for a password. I set the report time out to 15 min just to see if it would make a difference. I even set the IIS timeout to an to 15 min as well.
I know that its bad to run a report this big, but I'm just curious why it prompts for a password. In case a user runs the report. Report uses dates so its possible they could go way back and not realize its 500K rows. I am assuming it is an IE issue with the browser and found a registry edit, but I don't really want to edit registries on my users' computers.
View 1 Replies
View Related
Sep 27, 2007
hye everyone,
i have Problem for the prompts will display at the top of the report in the browser :
1)go to the report manager on computer and set the
set the Prompt User check boxes to checked.
--> auto check
2)select the report viewer control, if you are using it, and set the Parameters property to false.
--> the eror mesage will be display
" The ' field " parameter is missing value. "
what should i do...any idea
thanks in advance
thank you very much
View 1 Replies
View Related
Sep 16, 2004
Hello -
I dont know something weired happened on our MSSQL server today. We are able to connect to any Databases from Enterprise Manager from a Remote server.
Once we logged in and connected to a database on my database server, it keep prompting for the user id and password as we browsing through different tables in that database?
(Right click on the table then select Open Table -> Return all rows -> and it prompts for password )
Now when we enter password the contents of Table are displayed and now when we try to see contents of another table in the database it again prompts for password.
Any Idea whats wrong? How this can be resolved as it was not happening before .. :(
View 1 Replies
View Related
Nov 20, 2015
I run these stored procedure to build the report and I am able successfully to build the report but I need some prompts to in the report to get the specific data .
ALTER
PROCEDURE [dbo].[Sharepoint_Ticket_Report]
@StartDate
DATETIME,
@EndDate
[Code] ....
And for prompts how to create created_by and message_type dropdowns as shown in picture.
View 7 Replies
View Related
Nov 8, 2006
I use SQL Server 2005I have tables tblUserData, tblUsersAndGuestbook, tblGuestbooktblUserdata contains:UserCode intUsername nvarchar(50)tblUsersAndGuestbook contains:Usercode int (FK to tblUserData)GBEntryCode inttblGuestbookGBEntryCode int (FK to tblUsersAndGuestbook)GBText textNow...if I delete a user in tblUserData I want to also delete the entries in tblUsersAndGuestbook AND in tblGuestbook.I've heard something about cascading delete, but how can i configure that in my database?Or do I manually need to delete all entries from code?
View 2 Replies
View Related
Dec 15, 2005
Hi
I have three params p1 , p2 and p3.
All 3 are non queried with values Yes and NO .
if p1 is yes only i have to enable the remaining twp params otherwise disable them.
Can some one suggest as to how this can be achieved.
Thanks
View 1 Replies
View Related
Jun 4, 2001
Hi,
I read all the existing material in SWYNK but still am not clear on the following question.
What is the best way to perform Cascading actions (Delete & Update) with foreign Key Constraints declared? We are using SQL Server 7.0
thanks
Rozina
View 1 Replies
View Related
Nov 3, 2000
Can someone pls help me with a syntax example of a cascading delete trigger.
thanks
View 2 Replies
View Related
Sep 14, 2004
Procedure spDeleteRows
/*
Recursive row delete procedure.
It deletes all rows in the table specified that conform to the criteria selected,
while also deleting any child/grandchild records and so on. This is designed to do the
same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys
table to find any child tables, then deletes the soon-to-be orphan records from them using
recursive calls to this procedure. Once all child records are gone, the rows are deleted
from the selected table. It is designed at this time to be run at the command line. It could
also be used in code, but the printed output will not be available.
*/
(
@cTableName varchar(50), /* name of the table where rows are to be deleted */
@cCriteria nvarchar(1000), /* criteria used to delete the rows required */
@iRowsAffected int OUTPUT /* number of records affected by the delete */
)
As
set nocount on
declare @cTab varchar(255), /* name of the child table */
@cCol varchar(255), /* name of the linking field on the child table */
@cRefTab varchar(255), /* name of the parent table */
@cRefCol varchar(255), /* name of the linking field in the parent table */
@cFKName varchar(255), /* name of the foreign key */
@cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */
@cChildCriteria nvarchar(1000), /* criteria to be used to delete
records from the child table */
@iChildRows int /* number of rows deleted from the child table */
/* declare the cursor containing the foreign key constraint information */
DECLARE cFKey CURSOR LOCAL FOR
SELECT SO1.name AS Tab,
SC1.name AS Col,
SO2.name AS RefTab,
SC2.name AS RefCol,
FO.name AS FKName
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id
AND FK.fkey = SC1.colid
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id
AND FK.rkey = SC2.colid
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName
OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
WHILE @@FETCH_STATUS = 0
BEGIN
/* build the criteria to delete rows from the child table. As it uses the
criteria passed to this procedure, it gets progressively larger with
recursive calls */
SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' +
@cRefTab +'] WHERE ' + @cCriteria + ')'
print 'Deleting records from table ' + @cTab
/* call this procedure to delete the child rows */
EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
END
Close cFKey
DeAllocate cFKey
/* finally delete the rows from this table and display the rows affected */
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
print @cSQL
EXEC sp_ExecuteSQL @cSQL
print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName
--------
The above code is good .. but has limitation...throws an error:
Server: Msg 217, Level 16, State 1, Procedure spDeleteRows, Line 58
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can anyone out there suggest a better way of implementing on a database without a limitation of levels.. we are talking about a HUGE DB with lots of table and FK referentials..
Please advice.. or solve the problem..
Thank you
View 2 Replies
View Related
Mar 28, 2006
Hi All
I am using RS 2000. I used 2 input parameters for one of the report. The 2nd parameter is based on the 1st parameter. i.e. First Parameter is Country and 2nd Parameter is State. Based on the Country selection the list of states for the selected country will get displayed. This is working fine in Reporting services as well as report server. When deploying into the application it doesn't work.
If you have solution for this I would like to share.
Regards
Venkataraman M
ramanmahalingam@hotmail.com
View 6 Replies
View Related
Dec 21, 1998
I need some suggestions concerning the issue cascading deletes in a self-referencing table, i.e. a table with a foreign key pointing at the primary key in itself. Although SQL Server still does not support cascading deletes the declarative way there are some other ways to handle this situation. One common way is to use trigger coding with the simple structure
CREATE TRIGGER DelCascadeTrig
ON self_ref_tab
FOR DELETE
AS
DECLARE @C_FK xxxxxx
SELECT @C_FK = C_FK FROM DELETED
BEGIN
DELETE self_ref_tab
FROM self_ref_tab, deleted
WHERE self-_ref_tabC_FK = deleted.C_PK
END
where C_PK is the primary key column and C_FK the foreign key column. The problem is that this simple pattern does not work with self-referencing tables, because the removal of dependent rows deeper layer (n-2, n-3 etc. if the originating delete request is level n and the first level of dependant deletes handled by the trigger code is n-1) would require the delete trigger to fire more than once for the same delete operation. As far as I know, in ver. 6.x triggers are executed only once per SQL statement and in this case the n-2, n-3 etc. level rows would have been left as "orphans".
In ver. 7.0 I suppose this should work fine because of the new recursive trigger execution possibility (trigger will fire up to 32 recursive times per SQL statement), but in the meanwhile (i.e. my case util we have upgraded all our servers) the delete logic for a cascading, self-referencing relationship must be handled completely within one execution of the trigger.
My question is now: does anyone know anything about any common algorithm or trigger code example solving this problem.
Thanks in advance
Bjorn Ehnberg
View 1 Replies
View Related
Aug 5, 2004
Here's my table setup:
tblSteps:
StepID int IDENTITY
ParentStepID int ALLOWSNULLS
OtherID int
Amount money
tblOther:
OtherID int IDENTITY
Amount money
Now, I have a trigger defined on tblSteps:
Code:
CREATE TRIGGER tgrUpdateAmount
ON dbo.tblSteps
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @Amount money
SET @Amount = ISNULL((SELECT SUM([Amount]) FROM inserted), 0) - ISNULL((SELECT SUM([Amount]) FROM deleted), 0)
IF (SELECT [ParentStepID] FROM inserted) IS NULL
BEGIN
UPDATE tblOther SET [Amount] = [Amount] + @Amount WHERE [OtherID] = (SELECT [OtherID] FROM inserted)
END
ELSE
BEGIN
UPDATE tblSteps SET [Amount] = [Amount] + @Amount WHERE [StepID] = (SELECT [ParentStepID] FROM inserted)
END
END
What this code basically does is this: if you update the amount of a Step that has a ParentStepID, it will take what was addded (or deleted etc...) and update it's parent with the added amount. If the Step does not have a ParentStepID, it will take the amount add add it to the Other row it corresponds to.
Here's an example of some test data:
tblOther:
OtherID: 1
Amount: 0
tblSteps:
StepID: 1
ParentStepID: NULL
Amount: 0
------
StepID: 2
ParentSTepID: 1
Amount: 0
If I update the Amount to 100 for StepID=2, it also updates the amount for StepID=1. If I update the amount for StepID=1, it also updates the amount for OtherID=1. However, when I update the amount for StepID=2, it does not cascade up to the tblOther level (e.g. Updating StepID=2 should update StepID=1 which should update OtherID=1, right?)
Basically, the trigger isn't cascading. Can anyone point out what I need to do?
Thanks
View 1 Replies
View Related
May 24, 2004
When I setup a relationship in Access I can specify that Primary Key deletes cascade down to the Forgien Key. So when I delete an Order Header it cleans up all the items in the Order Details table for me automatically.
Can I get this same functionality in SQL Server 7 without having to write triggers or are triggers the only way?
thanks
dog
View 13 Replies
View Related
Apr 15, 2008
Hi
I am doing report development against cube (OLAP) and I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do we achieve this in a cube environment? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1. Hope this helps. If you have questions, please let me know.
View 1 Replies
View Related
Mar 21, 2007
hi all,
In my report I want to make certain parameters to depend on the previous parameters. I think we can use those as cascading parameters. How to make a parameter a cascading parameter? and how to use those cascading parameter ? It would be nice of you if you can help me.
Thanking you,
Lekshmi
View 4 Replies
View Related
Feb 7, 2008
I haven't used cascading deletes in the past but we're starting a new database and it seems like a good way to go to keep data clean. Or at least it did seem like a good way until I ran some tests. I have 3 tables.
People PeopleEmails Email
(pk)peopleid (pk)peopleid (pk)emailid
fname (pk)emailid address
lname emailtype
password
In this structure, the peopleemails table is simply an association table between the email and people table. I have setup up relationships in a diagram so that when a person is deleted, it cascades to peopleemails and removes the entry there. I also had a cascade set up hoping that when an entry was deleted from peopleemails, it would remove it from the email table but this is not happening. The relationship between email and peopleemail is primary key table email.emailid and foreign key table peopleemail.emailid. Is there a way to get this to work to remove the email address if a peopleemail entry is removed? Thanks.
View 5 Replies
View Related
Jul 20, 2005
We have a situation that occurs every so often with blocking ofvarious databases on one server (Win200 SQL7). It appears to happen atrandom, so I'm assuming it originates from something a user does andnot a regularily run process.We've examined the data available to us and used the very helpfulblocking code on http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html(Thanks Erland).Were getting closer to finding the problem, but need some advice onwhat to look for.This is a bit of guesswork, but we suspect that we get into asituation where blocking takes places, and this then cascades to otherprocesses which then block others in turn. The original culprit thenfinishes, but the blocks continue as the newer processes are holdingsomething else up. A bit like dominoes. It seems to take a while tofree this up.The problem we have is determining the start of this process. Once weare made aware of blocking issues, we can find out who is doing what,but almost always get a different answer/user and think we're gettingto it a little late.Ideally, I want to log the blocking somewhere so I can examine thefiles when this occurs and can therefore establish a pattern etc...Any ideas or suggestions would be welcome.
View 1 Replies
View Related
Jul 20, 2005
I use cascading delete on my SQL Server Database. I am experiencing along query time on my highest level delete, 10 minutes. If I deletefrom each table manually and then delete the parent, I will usually bedone in less than a minute. Any suggestions?
View 1 Replies
View Related
Jun 1, 2007
I have a report that is based on 2 listboxes, the second one's values dependent on the value selected in the first box. How would I display all values in both list boxes on the report if I so decided?
Thanks,
The Rook
View 3 Replies
View Related
Sep 24, 2007
I have 2 parameters that are of type string. The user can enter anything they want in them. The third parameter is query based and uses the first 2 parameters to get a list of people. Is there a way I can prevent the third parameter from propigating until the first 2 are both filled in? The first 2 cannot be drop downs however, they are used in a wild card fashion.
Thanks!
View 6 Replies
View Related
Apr 25, 2007
Hi,
I have a listbox which selects distinct brands from the products table.
Then another list box which lists all the orders with order description. Each order has a unique system generated ORDERID and the user provides the orderdescription which could be duplicate.
Depending on the Brand selected by the user in the earlier list box, only those orders containing the the brands in order details files should be available for selection in the list box.
e.g. BRands Lisbox shows: Cream A, Cream B and Cream C
If the user selects 'Cream A', then the next list box shows orderdescription as 'Cream A order for regular sale','Cream A order for exhinition sale', 'Cream A order for exhibition sale'
The problem here is that if the user selects a description which is duplicate (could be the case), then the system brings back the wrong order details.
How is it possible to allow a user to select a order description but search on the OrderID?
Thanks for the help
regards
josh
View 1 Replies
View Related
Mar 20, 2007
Hi,
I am using SQL Server 2005 reporting services. I am having a problem with Cascading Parameters. In my report there are 4 parameter . (Product Class, Product Type, Product SubType and Activites). when user open the report page he/she will have to select a value for product class depending on the selection Product Type and Sub type will be populated. So far its good, but Activities also depends on the Product Class. My question is can we have 2 parameters depend on same parameter. Here in this case Product Type and Activities both depend on the Product Class. I want only those activies in the dropdown which belongs to the Product Class that is selected.
Can anyone please give an ideas?
Thanks
Ashwini
View 10 Replies
View Related
Aug 18, 2007
If we want to maintain the data in relationships.
There are two ways to do it.
1. Auto (Like Cascading Update And Delete)
2. Manually (Like In Stored Procedures)
I read an intresting article
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419
In this article Imar has choosen the second way (Manually).
And when I talk to Imar.
He said, "Cascading deletes would have worked equally well in this situation. However, I personally don't like them too much. I am much rather in control, enabling me to delete what I want and when I want it. I could, for example, keep certain data for "time travelling scenarios" (e.g. the state things were in some time ago) or I might want to keep it for other purposes."
Can any one help me to choose the better one.
Waiting for helpful replies.
View 2 Replies
View Related
May 21, 2008
I need to "cascade clone" or "cascade copy" one row only, in a given database table, but I also need to copy all the child records in related tables, yet preserving the Primary-Foreign key relationships.
Say I have 3 DB Tables.Companies with a key named CompanyID.Employees with a key named EmployeeID and a FK to CompanyID.Projects with a Foreign Key to EmployeeID
One company may contain several employees, and each employee may have several projects.(See this image for reference www.AgustinGarzon.com/3RelatedTables.jpg )
I need you to point me to a procedure that enables me to copy, for example, one Company record, and have all the associated Employees copied over, and for each Employee, have all the Projects associated copied over.
This is what I call a "Cascade copy", because it's a process that walks down all the database structure, starting from a given field, and looking down through all the database relational hierarchy.
There might be a straight way I can accomplish this task, although I'm unable to find it.I could write a .net script where I specify the Table Names, the Prikmary Keyname and the Foreign Keyname, but this doesn't sound like a clean solution.
The most important thing is keep a correct Foreign to Primary relationship once the fields have been copied. I mean, if we clone a company:
1- All the employees should be also copied.2- All the new employees should relate to the new company primary key.3- All the projects should be also copied.4- All the projects should related to the new employee primary key.
I'm attaching an image so you can easily see the pretty simple database structure I'm talking about.www.AgustinGarzon.com/3RelatedTables.jpg
I'm also attaching the VS2005 project with the SQL Express database.www.AgustinGarzon.com/TestSolution.zip
Look forward to receiving some tips and links to resources so I can achieve this task, preferably through a SQL stored procedure or an ASP.NET script.
Best Regards.
Agustin Garzon
View 2 Replies
View Related
Jan 27, 2004
I may have misinterpreted the error I just got...but.. is it impossible in SQL Server to have a cascade delete FK constraint that leads to a table which has another cascade delete FK?? What's a workaround? Triggers?
View 5 Replies
View Related
