I have a bar chart that displays the detail of a table with locations along the x-axis and number of employees on the y-axis. Is there any way to have the sum of all of the locations show as the final bar in the chart? (which would be the footer in the table)
View 3 RepliesI 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.
HiI've two tablesTableAidnameTableBidtableA_iddescoperAs you can see tableA is a master and tableB is a detail table wherewe can have many records for each related tableA record.I need to get all records for tableA with a count on some oper oftableB.I suppose I can got it with a join or a subselect but I don't use SQLoften so I'm getting crazy with this stupid query...Could somebody help ?Thanks in advanceC
View 2 Replies View RelatedHi,-- ddl/*create table #tmp (col1 int);insert into #tmp values(1);insert into #tmp values(2);insert into #tmp values(3);create table #tmpChild (col1 int, fkCol int, Num int);insert into #tmpChild values(1,1,3);insert into #tmpChild values(2,1,2);insert into #tmpChild values(3,2,1);*/-- get parent and child data (outer join)select *from #tmp t Left Outer JOIN #tmpChild tCON t.col1 = tC.fkCol-- resultset/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 31 2 1 22 3 2 13 NULL NULL NULL(4 row(s) affected)*/-- desired resultset/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 3-- eleminate next row, want only Max(Num) row from the child tbl withsame FK (parentID)-- 1 2 1 22 3 2 13 NULL NULL NULL*/In other words, desired results is as follows:/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 32 3 2 13 NULL NULL NULL*/How to accomplish this task? ENV: MS SQL Server 2000TIA
View 1 Replies View RelatedHi,
I have a master and a detail table as defined :
Master : tmpOrder
Column_name Type
------------------------------ ------------------------------
OrderID int
Date DateTime
Detail : tmpOrderItem
Column_name Type
------------------------------ ------------------------------
OrderID int
Seq int
Qty float
Amount float
How can I get the master fields and sum of qty and amount in one query. I need the following result set
OrderID Date TotalQty TotalAmount
-------------- ---------- ---------- --------------
Thank you in advance
Allex
Hi Experts,
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.
Hi all,
I have a 2 main tables in my system. One is main table has ticket information location and so on. And detail table which hold all actions on that record with multiple money fields (its around 5 fields). it goes trough some crazy calculations on the detail table and get 5 results for each ticket. And this calculation is done everytime the ticket is looked at.
There is around 10 detail records per 1 main record. and the main table is around 7 mil. records
Now i got 2 options. I can create a trigger that does the calculation and update the main table show the results from there. This is going to affect all updates deletes and inserts.
OR
I can leave as it is so only when they view the ticket it does the calculation. Dont ask me how often they view it i dont have a clue.( if there is any suggestion how to get the count i could try to do it. )
:confused:
Suggestions
(My attitude is going towards the trigger way but 100 percent sure)
I have a detail table of comments. I need to grab the fist 50 characters from the comment field from this table. The kick is that I need to get the comments from all of the records in the detail table, as the comments might be less than 50 characters, so i need to pull the comments from the other comment records that apply to the master table record.
right now i have the comments in a join to the master table, but this is causing a few records to be returned....
I guess what i am looking for is a way to grab the comment value from the first record of the detail table.
Dear Sir
Thank for your reply but our requirements are not this
I am fully explain my requirements
In Master table have 5 columns; In Master Table stored all records.
NameAppidFunctionCodeFunNameSubFunCode
Ad630Manual0
Ad630Log10
Data810Summary0
Data820View0
Data830&View0
Data840Row10
Ad630Mbl20
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
NameAppidFunctionCodeFunNameSubFunCode
Ad630Manual0N
Ad630Log10N
Data810Summary0Y
Data820View0Y
Data830&View0Y
Data840Row0N
Ad630Mbl20Y
Please immediate reply me,
I am waiting your reply.
Thanks Asit Sinha
Two questions:
1) I am unable to get a detail row of my table to suppress. I am using:
=Iif(IsNothing(Fields!Address2.Value), True, False)
2) I am not able to get an If/Then statement to work. I am able to use the followingl; however, ideally I would want to have multiple if/else statements:
=Iif(Fields!Taxschid.Value = "CUST", 0, Fields!Selling_Price.Value*.08)
Tx
Les
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...
View 1 Replies View RelatedI have a flat file with header and detail information, it is actually employee punch card data. I need to parse the header line which contains the Employee ID and don't save it to a table just save the value. Then with the detail line, parse the different data elements and save them along with the employee ID to one table. Then continue until the next header line is read.
The file looks something like this:
FINNEY,RONNIE 0001005420
Mon 09/03 700a HOL 8.00
Tue 09/04 630a*E 326p 8.50 8.50
Wed 09/05 645a 330p 8.00 16.50
Thu 09/06 639a 2.40 18.90
HALL,MARK 0001005601
Mon 09/03 700a HOL 8.00
Tue 09/04 608a*E 257p 8.40 8.40
Wed 09/05 601a*E 259p 8.50 16.90
Thu 09/06 606a*E 3.30 20.20
JONES,WILLA JEAN 0001005702
Mon 09/03 700a HOL 8.00
Tue 09/04 556a*E 326p 9.10 9.10
Wed 09/05 600a*E 328p 9.00 18.10
Thu 09/06 554a*E 3.50 21.60
So I think I need a data flow transformation object that let's me save the Employee ID into a variable available when the next record is read. What type of transformation would be best?
I want to produce a combination Column / Line chart for sales and budgets by amount/month
Sales figures should be displayed as lines with a separate line for each year.
Budgets should be displayed as columns. I will only be displaying budgets for the current year.
Is it possible to do this within Reporting Services - if so what chart type do I use
CREATE TABLE DHS(CUSTOMERNBR VARCHAR(20), CONTRACT VARCHAR(20), SUBCONTRACT VARCHAR(20) , STARTDATE DATETIME, ENDDATE DATETIME, EFLAG VARCHAR(20), HFLAG VARCHAR(20))
The data which will be going into this table is from two table which have a 1 to many relationship:
Here is the 1 side and data:
CREATE TABLE CUSTOMERS(
CUSTOMERNBR VARCHAR(20),
CONTRACT VARCHAR(20),
SUBCONTRACT VARCHAR(20),
STARTDATE DATETIME,
ENDDATE DATETIME DEFAULT '12/31/2099')
[Code] ....
Here is the Many side and data:
CREATE TABLE FLAGS(CUSTOMERNBR VARCHAR(20), FLAGCODE VARCHAR(20), STARTDATE DATETIME, ENDDATE DATETIME DEFAULT '12/31/2099')
INSERT INTO FLAGS(CUSTOMERNBR, FLAGCODE, STARTDATE, ENDDATE) VALUES('10001000101', 'H', '02/01/2014', '03/31/2014')
[Code] ....
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:
CUSTOMERNBRCONTRACTSUBCONTRACTSTARTDATEENDDATEEFLAGHFLAG
10001000101A910400801/01/201401/31/2014
10001000101A910400802/01/201403/21/2014H
10001000101A910400804/01/201404/30/2014
10001000101A910400805/01/201405/31/2014H
10001000101A910400806/01/201412/31/2099E
10001000102A555500101/01/201403/31/2014E
10001000102A555500104/01/201404/30/2014EH
10001000102A555500105/01/201406/30/2014E
10001000102A555500107/01/201412/31/2099
10001000103A666600401/01/201410/01/2014
I have two tables. Order table and order detail table.
What I want to do is send notification to a service when an order is created. And I want to include both header and detail of the order.
I can't get working with on insert trigger . How to go around with trigger ?
Hello Experts,
Problem here. Trying to display database column information in a chart. Basically I have a dbase query that pulls different percentiles on transaction. So when I run the query, a number appears in the data for columns called 95th percentile, 97th percentile and 99th percentile.
Now I know how to filter the data in reports by a parameter, however I do not know how to show columns by a parameter. Specicially in a chart situation. So if I choose 95th Percentile in a parameter dropdown box, I want to see the chart for the 95th percentile column in the database, likewise with the other columns.
I am not sure how to show/hide these via a parameter when charting. Anyone have any ideas?
Has anyone heard as to whether Microsoft plans on updating the charting engine for Reporting Services? Right now I find the charting capability rather limited and would like to see richer charting capability. Is this a case where it is better to not wait for this feature as it will be way off into the future before this happens, or better charting features just are not in the scope of Microsoft's plans for Reporting Services. If either case is true then the best solution would be to purchase the full capabilities of Dundas Chart for Reporting Services.
View 1 Replies View RelatedI would like to create a bar chart and at the same time display some text after each bar.
I guess I could just create some textboxes on the chart, but I have a parameter that the user can change to specify the numbers of bars (therefore the number of textboxes, and the location of the textboxes are variables)
Is there anyway that I could do this in reporting services?
Thanks,
Steven
I have detail table like
date item_id grade in out
------------- --------------- ------- -------- ----------
01-01-08 001 A 10 0
02-01-08 001 O 8 0
01-02-08 002 O 1 0
03-01-08 001 T 0 10
02-01-08 003 O 20 0
02-01-08 003 T 0 10
02-01-08 003 B 0 8
Result View
=======
Item_id A B O T Total
001 10 0 8 -10 8
002 0 0 1 0 1
003 0 8 20 -10 18
where grade could be any Alphabet, the column of result query could varies
each grade contain sum of (in-out) of all item_id of detail table.
Smartsys
Hello,
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.
Hope this makes sense.
How can I do this?
GrkEngineer
Can SSRS be configured to use any third party .NET charting components or only ones that have been specifically designed for SSRS? I have seen a couple of third party charting software (Dundas, etc.) and while they are nice, they sell at a real premium and there are not many vendors offering SSRS charting. I was hoping for a bit better value and more of a selection to choose from.
R
I need to insert data into Header & Detail table. As shown in the below xml,
RecordID is identity-column and incremented by 1, after new record is saved into Header table. Need to assign the same recordID for the detail also.
Expecting output should be like as shown below:
How can we accomplish this requirement.
I have a table which contains data regarding calls to the Help Desk, I want to chart this using a simple line chart in SSRS 2005 with the chart displaying the number of cases opened by the help desk each hour for the last 24 hours. Although our Help Desk provides 24/7 support, there are periods of an hour in which no calls are received. The issue I'm having is I want the chart to still display these hour periods of time even though there are no records created in the time span. I want the x-axis to display every hour for the last 24 hours.
Anyone have any suggestion on how I can accomplish this? The only idea I've come up with is creating a new table containing a list of every hour in a day and referencing this to build the x-axis...but it seems as though it should be easier than that?
Thanks in advance for any input or assistance.
Does anyone know if you can do polar charting with Reporting Services? I have a need to report the polar coordinates of an element and show the polar chart of the data elements. If I can't do polar charting with Reporting Services, does anyone know of a third party product that I can utilize?
Thanks
KM
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
View 1 Replies View RelatedI am trying to create a function that will run when a job step fails. I want it to dynamically capture the name of the job and insert it into a table that I will use to send email alerts for. The reason I need to do this as opposed to sending an email from the job is that if the alert is not responded to after so many minutes it can be escalated to the next operator. Does anyone know how to dynamically capture the name of the current job being run.
thanks
Tom
Hello there :-)
Just want know where can we find the last T-SQL being executed on a process. This is the one that pops-up when we double-clik a process on the Process Info List. Thank You in Advance :-)
Hi
I have a invoice that displays the company name in a rectangle on top then it has a list in the list it has details tables my problem is that the invoice detail goes on the next page I need to have the company name as well on the second page I cant put it in the same table as the detail because I have a few tables in the detail so it is actually no header is it possible to have Repeated the Company name information on the next page?
Thanks
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 am creating a DTS package to export a text file. My question is: does anyone have any ideas on how for one read of the tables I can produce 2 lines of output. Here is how the file layout needs to be...
HEADER
DETAIL
HEADER
DETAIL
HEADER
DETAIL
I am a little confused about how I can stagger header and detail using the same data.
I appreciate any help you can give. Hopefully my explanation of the problem is understandable.
Thanks, Val
Hello,
I am trying to create two subreports in the main report. One sub report should give detail data and other sub report shuold give sumamry data. Is detail and summary reports are possible in sub reports? Iappreciate your help on this.
Thanks,
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?
Or should I be working on a Forms-based?
Thanks!
Brian
I have two tables(Order and OrderDetail) of master-detail relationship. I have a nchar field in the detail table called itemno. I want to query like:
Select Order.OrderNo, Order.Date, SUM(OrderDetail.ItemNo) as ItemNos,....
From Order Inner Join OrderDetail on Order.OrderID=OrderDetail.OrderID
Where Order.OrderID=10
so that the resulting field ItemNos will become a string in format "ItemNo01, ItemNo02, ItemNo03,...."
How can I write this query in T-SQL?
Thanks