Using Multiple Stored Procedure In Master/detail Report
Aug 19, 2007
Almost new to ssrs, accessing Oracle's stored procedures to feed the reports (no other choice), working on a master/detail report and badly stuck. This report needs to access 3 stored procedures one for master and two for small tables under it. The master's order ID is fed to the two stored procedures that may or may not pull any thing for certain order ID's
I am approaching it by creating 2 datasets and a page-long list, added desired text boxes and a table in the list, grouped the list on order ID and passing it to tables using default values etc. but keep getting the following message.
"The Group expression for the table €˜table1€™ refers to the field €˜ProductName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope".
The list seems to be associated to one dataset only and any item in it cannot refer to anyother dataset, even if I try to access the table's dataset in its properties (The 'current data set scope' implies that).
(Not sure what will happen when I will try to add the second table and dataset with the same parameter name).
How should I approach this issue. I will appreciate help, if explained in steps but won't mind a good tip.
Thanks.
I am attempting to setup a Master-Details with GridView/DetailsView but I can't seem to find any information on using a stored procedure that requires parameters with the SqlDataSource control. SelectCommandType specifies that you are using a stored proc. SelectCommand specifies the name of the proc, but I haven't found any information on how to pass a parameter to the stored procedure.Is it even possible or do I have to forget about using the DetailsView control altogether?
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.* FROM MASTER WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
I have multiple drop down lists that I would like to have send parameters to a stored procedure that does a select on the data. (Kind of like a search feature) But if a ddl isnt populated, I want that to act like a * and get all. For instance, I have a persontype, state, and country drop down lists. If I drop the state one down to WA, I want the gridview to populate with all the people in WA regardless or their type. If I populate type and state, have it give me both regardless of country. What does the stored procedure or select syntax look like for this? (What would the default values of my dropdown boxes?) my current sql select statement (The bottom 3 are bit fields which are throwing me off a bit too). do i need to have some IsNull statments around each variable in the select?SELECT
tblPerson.PersonTypeID = @PersonTypeID and tblPerson.StateID = @StateID and tblPerson.CountryID = @CountryID and tblPerson.SchoolID = @SchoolID and tblPerson.InterestChapterLeaders = @InterestChapterLeaders and tblPerson.InterestMembers = @InterestMembers and tblPerson.InterestUniLeader = @InterestUniLeader
now i want to learn how to make a stored procedure to insert a record to `purchase` table, and many records to `purchase_detail` table with transaction where the some value are passed from vb6 through the parameters. i've made a SP to insert 1 record to `purchase` table n 1 record to `purchase_detail` just for testing, so i set the disc value to 10. it works fine... --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10), xitem_id VARCHAR(10), xqty TINYINT(3), xprice DOUBLE(15,2)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, 10); COMMIT; END --------------------------------------------------------------------------------- what i need is something like that but i only pass 3 variables (purch_id, purch_date, and supp_id) to SP, and then the SP will insert 1 record of purchase to `purchase` table, and add the purchase items to `purchase_detail` automatically from `purch_temp` table, and use the disc rate based on `supplier_id` and `item_id` from supplier_disc table, which will be looked something like this: --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); /*start looping here get the disc rate for each items where supp_id = xsupplier_id and item_id = the item_id from purch_temp table, and save it in a local variable (let's say local_disc) INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, local_disc); */ COMMIT; END --------------------------------------------------------------------------------- can anyone help me please? thank you in advance...
Hello community,I think my problem is easy to solve even though I did not find a solution through different tutorials and help pages. Here it is (select statements are hier simplified):In the gridview "GridView1" I have a master record with person_id, which is the data-key-value. There is also another id-field named task_id (This record comes from a database view which joins the persons- and the tasks- table) SelectCommand="SELECT [id], [person_id], [task_id] FROM [ViewPersonTasks] WHERE ([id] = @id)"For both fields I want to display details in two different detail-views. One for the person (depending on person_id) and one for the tasks (depending on the task_id).The first one is easy. I declare a details-view for the person data based on a SqlDataSource with a control-parameter like this: SelectCommand="SELECT [person_id], [first_name], [last_name], [birth_date] FROM [TabPersons] WHERE ([person_id] = @person_id)" .... <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="person_id" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters>But now the problem: how should I declare a parameter @task_id for the task_id, so that the second select statement for the tasks-details-view retrievs the data for the tasks: SelectCommand="SELECT [task_id], [task_name], [task_date], [task_description] FROM [TabTasks] WHERE ([task_id] = @task_id)"@task_id should have the value from the task_id-field of the master record, displayd in the master grid-view.Thank you in advance for your help
I'd like to put together a summary report for some of my management staff. The report should show the # of jobs that match about 20 different statuses. Each status has different criteria. For example, one might look for a date in a datetime field and the type of job. Another might look at whether a date has passed and the quantity we're shipping.
Sort of like this: Status Items Jobs Status #1: 50000 15 Status #2: 25251 3
I want the user to see this summary information, but also have drill down capability. I'd like the ability to print the summary or just a drill-down.
I'm also considering using Dundas Charts for RS on the report.
Can I accomplish this with Reporting Services? If so, any tips on how to do it? If this a bunch of sub-reports? Can I sum the # of jobs on a sub-report? Should I be looking at BI for this?
Hi, can anyone help me? I want to know whether is it possible to insert records simultaneously in both master & detail tables? For elaboration, say there is a master table contains (orderid,orderdate,amount) and details table contains (orderid, productid,qty,price). 1 record of master table associated with n records of details table. Can it be possible to insert both the 1 record at master table with n records in details table in a single sql statement?
I need to know the best approach to save data in master table and then in detail table. I know this method but i know it's not a good approach why i will explain
Insertion in Master Table..................................... A
Insertion in Detail Table........................................B
Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach.
So please can any one tell me a good approach for this.
As part of a project, I'm not allowed to use foreign keys, well can't. But I have a problem with this master detail relationship (and a very simple one too). Tables to begin with: Invoice: invoiceid : bigint indentity autoincrement primarykey Detail: invoiceid: bigint - detailnumber:bigint - desc : char20 Okay - I want to set these two tables up such than when I create a new invoice, and subsequently get the new invoiceid, and I start to add detail records to the detail table, I want the detailnumber to autoincrement when I do an SQL Insert. Right now I have detailnumber as the primary key, so when you view the detail records, it looks like: (invoiceid, detailnumber, desc) 1,1,text - 1,2,text - 1,3,text - 2,4, text on another invoice - 3,5, text I want it to work like: (invoiceid, detailnumber, desc) 1,1,text - 1,2,text - 1,3,text - 2,1, text on another invoice - 3,1, text where the invoiceid and the detail number make up the primary key. I seem to recall you could do this is PHP before it had foreign keys & such, and I'm completely drawing a blank on this. BTW - I'm using enterprise manager to set this up, but I can use QA with a sample script to acheive the same. I realize this may be (and probably is) off-topic here, but I can't figure out where to ask. Thanks in advance.
Help! Surely this has happened to others before me. A new customer wants to send updates in a fixed-width txt file in which master and detail rows alternate.
How do you do this?
Do you: 1) Painfully muck around with 100,000 rows in the .txt file first--split it into 2 files, one for master records and one for detail records and then process? If so, what do you use? I'd probably hack at it with a VB Script module in a DTS package.
or
2) Is there a way to feed it into 2 tables where rows starting with x go to one table and rows starting with y go to another?
The plan was to use a fast and dirty DTS package to shove this stuff into a table (probably 2 but we might just toss the stuff we don't need and put in in one) but I'd like some advice on how to proceed.
In second Table store those records who are selected and stored in 2nd table. NameAppidFunctionCodeFunNameSubFunCode Data810Summary0 Data820View0 Data830&View0 Ad630Mbl20
Our requirements we use one query, In query fetch total 5 rows. and output show like this
I'd like to create a master-detail output from SQL2005 to display in a page using classic ASP. Ideally, I want the output to contain a <div> to show/hide the order details beneathe the order header, as shown below.
I currently just have a stored procedure returning the results and it is very slow as I'm simply querying the details section for each order header. Does anyone have any ideas on how I can create an efficient and fast method for returning these results in the format above?
I need to write a sql query which is a master-detail query. Here's the example structure of tables:
Master table: ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint
Child table -- Wheel: ColID as longint, ColA as int, ColB as int Child table -- Door: ColID as longint, ColA as int, ColB as int Child table -- Window: ColID as longint, ColA as int, ColB as int ..... etc
From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName).
My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please.
The plant number field is a location based field that the application uses to filter/select data on for the end users. What they want to be able to do is to select a record, select another location from a dropdown list and then click a button that duplicates the master record and the detail records to the new location.
I am thinking that a stored procedure passing the JSAID and new Location number to do it, I am just not sure how to get the new ID when I go to copy the detail records.
I'm currently strugling with the setup of our packages for deployment to a new environment.
We are working with a master/detail package setup. One master package is created that will call all child packages. In the master package we don't have any connection towards our source and/or target databases/sourcesystems.
Everything works fine, however, starting to deploy the whole set of packages, it seems that we don't have the option to set specific properties of our detailed packages, e.g. connection properties. But this is just what we need.
When we are adding a job in the Job Agent for our master package to be scheduled, we want to be able to set all different connection manager properties, not only the one from the master package and definitely the ones from the detailed packages as there we switch the connections from the development environment towards the acceptance environment.
I tried to fix this with parent package variables, but I can't set the password property, only the ServerName and UserName can be set, not the Password.
Anyone an idea what the easiest and best approach is to solve this burden?
I have a master detail reports in one page. Master report displays all the products and details report shows their orders... What I want is, when user clicks on products graph its detail graph show the orders of that particular record on the same page. Both reports would remain visible on the same page side by side.
The CUSTOMERS table holds the record date span into which the FLAGS table records have to "fit". In no case will the date spans from the FLAGS table fall outside the STARTDATE - ENDDATE span in the CUSTOMERS table for any CUSTOMERNBR. Here is an example of the final expected output in the DHS table from combining records in the CUSTOMERS and FLAGS tables:
During a unit test, I was surprised to find a data access error that traces back to a particular master stored procedures missing execute permissions for the dbo. I wonder if there's somewhere a sample script that globally grants execute permision to stored procedures. Better still can the permission granting be automated each time a new stored procedure is created?.Thanks much.
I need some clarification regarding the security inside of the master db.
I have a user stored procedure in master. I would like to be able to execute that stored procedure from an internal web app. Would I give execute permission on the stored procedure to the "public" or "guest" role? The web app would be using a userid/pw for an application database.
Also, is it a good idea to have user stored procedures inside of master? Could someone point me to where I can find a good article on master db best practices?
I have a SQL server instance being used as our data warehousing environment. The instance consists of several databases that I am snapshotting as part of our high availability strategy for data. I've created a stored procedure that takes the source database as an argument and that will create a new snapshot when a new one needs to be created and will also automatically remove the old snapshot. It also updates some synonym tables that point to the new snapshot but that might not be an important detail.
I would like to have the stored procedure stored some place global to all of the databases that I am routinely snapshotting, but that would mean putting it in the master database. Although having it there makes things significantly better in terms of usability, it seems like there's something wrong with putting any stored procedures in the master database. Am I wrong? Is it OK to put stored procedures there in situations like this?
Hello I am building a survey application. I have 8 questions. Textbox - Call reference Dropdownmenu - choose Support method Radio button lists - Customer satisfaction questions 1-5 Multiline textbox - other comments. I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID. I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score. Please help me! Thanks Andrew
Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...
my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...
if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...
this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...
but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??
I would appreciate any help on this project. I have created an Access database that contains one vehicle. I have also included all options on that vehicle, which are in one column. Therefore the main criteria for the vehicle is listed each time for each different option. On my report I am grouping by the vin and placing the main criteria in the group header area of the report. The options are going into the detail section. How do I get the options to print in two columns within the detail section? I am unable to find any help on this subject, so I am asking you for help.
I am developing a report analog of a machine readable form that has to display a static number of detail rows regardless of the number returned from the database - i.e. if a record set has only three detail records, I need to display three blank rows, while if the record set has ten detail records, I need to display six detail records, print the footer, start a new page, repeat the header information, print the remaining 4 detail records and 2 blank rows, print the footer again and move on to the remaining recordset. I am new to report development and I'm having to pick it up on the fly. I can't seem to locate any documentation about how to handle this scenario.
I don't have the time or inclination to re-invent the wheel here, is there anyone who has solved this problem who can point me in the direction of some help?
I am listing detail transaction lines in a table sorted by account and order number. the problem is that I only want to see the detail if the sum of a value field is zero for all the transactions in an order otherwise ignore the detail for that order.
I was trying Group by and Having but this doesn't seem to do what I need.
Being relatively new to Reporting services, any nudge in the right direction would be useful.
