Hi all - struggling a bit with this one... I have made an accounts application and need to make a report called "Aged Debtors". It lists all of the clients with outstanding invoices and breaks them down into periods:
current (this month)
period 1 (last month)
period 2 (2 months ago)
period 4 (invoices raised 3 months ago or more)
My tables are as follows (simplified)
Clients
clientid int
clientname varchar(100)
invoices
invoiceid int
clientid int
invoicedate datetime
cinvoiceamount decimal(19,2)
paid int (1 = paid, 0 = unpaid)
paidamount decimal(19,2) (how much of this invoice has been paid)
I need the following columns in the report
clientname invoiceid current period1 period2 period3+
test client 1 £100 - - -
2 £50 - - -
3 - £100 - -
test client2
I can format all the report using a matrix in SSRS so just need the stored proc to get me going - any help greatly appreciated!
Thanks guys,
Stephen.
hai, iam new to ssrs pease help me below calculations.iam taking the parameters as begin date,Enddate,region,year,site,
My report having outstanding bills information, i need to calculate, here resters are nothing but students, you take any relavent input data.
1)Rosters Outstanding 6-10 Days (Rosters expected, but not received for up to 10 days in the rears), 2)Rosters Outstanding > 30 Days 3)Rosters Current (Rosters expected as of the report end date) 4)Total # Rosters Outstanding (Total rosters expected, but not received) 5)Rosters Queued For Entry Into 3rd Party Systems (Total rosters entered into the SIS System and expected to be entered into, but not already in 3rd party systems) 6)Rosters Entered Into 3rd Party Systems (Total rosters entered into 3rd party systems) 7)Display Total Billable Dollar Amounts Associated With All Outstanding Rosters For All Aging Columns At Bottom Of Report By Daily, Weekly, Monthly, SYTD
Can anyone advise if it's viable to send order invoice reports to customers with SQL Reports.
I have a well formatted report that accepts an order number and generates an email to the customers address but i'm struggling on how to batch send this. Each order record has the email address to send to.
In our orders table I have a 'sent' flag so can easily write a query to bring back all orders that need sending. I just can't work out how to automate running the report for each order. Then after the report has been run for that order it needs to update the 'sent' flag.
Possibly this can work with a SSIS package that does the initial selection and updating of the flag but i can see how to run a report server report from an SSIS package.
If I have the total amount of 673000 that is passed as a parameter in my stored proc then, I need to do this:
Declare @TotalAmount money @TotalAmount = 673000 Col1 Col2 Col3 Test1 45 672955 --(I want to subtract 673000 from Col2 data) 673000-45 Test2 30 672925 --(I want to subtract 30 from the remaining amount of col3) Test3 100 672825 --(I want to subtract 100 from the remaining amount of col3)
Hi every one, I am facing problem in printing the reports from browser and also when i export it to pdf,the problem i am facing is blank pages are coming when report column getting the large amount of text around 2500 characters into column value. can any one help me in this issue?. if the report is getting acceptable amout of data it is printing in proper way i.e no balnk pages at all.i maintained all properties like margins+body size < page size.
Generally, on any screen, we design filter screen by allowing user to identify range or one value to search. But sometime in some screen, It will be more convenient for user if user can identify No matter how value to search.
For example On screen which have information of people in any province. So user would like to search it by identify no matter how value to search. There are check box of any province on filter part which enable users choose it.
Hence, if sometime user choose (by clicking on checkbox) 3 provinces : LA, Michigan, WachingtonDC to see description only 3 chosen province. and sometime user choose (by clicking on checkbox) 2 provinces : LA, Michigan to see description only 2 chosen provinces.
Please give me any idea for create Stored Procedure or any tecnique to complete my idea....
I want to write a query that will give me a dynamic amount of columns back.What I want to do, I want to create a calendar application, in which foreach employee, I want to show if he is in the office or not.this should look like:ID, Name, 1,2,3,4,5,6,7,888,Leo,0,0,1,1,1,0,0,1The amount of columns is dynamic, and is a period of time, with a column foreach day.Any suggestions what the best approach to this could be?ThanksLeo
Before today when I run the package, everything is fine. Today when we use similiar database with small schema change, my package can not be run successfully. Always got '
Bulk Insert with another outstanding result set should be run with XACT_ABORT on
I have an SSIS package which calls several other SSIS packages. The "mother" package has TrasactionOption set to "Supported". There is a sequence container and an imbedded Execute Package Task for each of my embedded packages. Each of the sequence containers has TransactionOption = Required. DTC is running.
When I run my mother package I get the following error within the first package executed "Bulk Insert with another outstanding result set should be run with XACT_ABORT on." I've looked high and low for others with this problem, but haven't found any resolution. Can anyone tell me how to resolve this error? I have tried using BEGIN TRANSACTION instead of using Required and I turn on XACT_ABORT, but it doesn't seem to help either. Any help on this would be greatly appreciated.
Not nice but a work around: is there a way to get the amount of fields or rows from a table or query within MS SQL, to pick out the lastfield-4 for example ?
I have a query that returns a result that looks like this:
amount count steve 122000 12 jim 145213 13 paul 62325 7
I need to add 2 columns to this query that rank the employees by count and amount with a number, so for example:
amount amount_rank count count_rank steve 122000 2 12 2 jim 145213 1 13 1 paul 62325 3 7 3
I was going to paste the entire query in but I thought I'd try this first to see if I could get by with a hint. So essentially I need to evaluate with an order by or something and then insert a number as amount_rank and number as count_rank.
I'm creating a temporary table in a Sql 2005 stored procedure that contains the transaction amount entered in a period <= the period the user enters. I can return that amount in my result set. But I also need to separate out by account the amounts just in the period = the period the user enters. There can be many entries or no entries in any period. I populate the temporary table this way:
SELECT t.gl7accountsid, a.accountnumber, a.description, a.category, t.POSTDATE, t.poststatus, t.TRANSACTIONTYPE, t.AMOUNT, case when t.transactiontype=2 then amount * (-1) else amount end as transamount, t.ENCUMBRANCESTATUS, t.gl7fiscalperiodsid
FROM UrsinusCollege.dbo.gl7accounts a
join ursinuscollege.dbo.gl7transactions t on a.gl7accountsid=t.gl7accountsid
where (t.gl7fiscalperiodsid >= 97 And t.gl7fiscalperiodsid<=@FiscalPeriod_identifier) And poststatus in (2,3) and left(a.accountnumber,5) between '2-110' and '2-999' And right(a.accountnumber,4) > 7149 And not(right(a.accountnumber,4)) in ('7171','7897')
order by a.accountnumber
Later I create a temporary table that contains budget information. I join these 2 temporary tables to produce my result set. But I don't know how to get the information for just one period. For example, if the user enters 99 as the FiscalPeriod_identifier, I need a separate field that contains only those amounts(if any) that were entered for each account in Period 99.
Can anyone help? It may be that I am not seeing the forest for the trees, but I can't figure it out.
I have a data table which has list of bills with date and outstanding total.
If the bills dates between 0 and 30 I like to insert the Outstanding total into Colum K , If the bills dates between 31 and 60 I like to insert the Outstanding total into Colum L , If the bills dates between 61 and 90 I like to insert the Outstanding total into Colum M and so on ..
I tried below formula but it is not working
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 0 and 30 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('K') CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 31 and 60 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('L') CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 61 and 90 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('M')
I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .
I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.
hi! i actually have more than question related to the SQL Server 2000 1) On my system I charge users for using an online service (SMS ) and I have a monthly fees charge and monthly allowance usage so if they exceed the limit I can also charge them for over usage this is an example for a rate that I have in my Rate Table
Rate Id = 1 MonthlyFees £10.0 MothlyAllowence = 100 sms/ month Extra usage = 0.12 p for each extra SMS
The system should calculate the monthly invoice from the registration date so if you register in 14 April the invoice will be generated every month at the same day (15) each month.
Now! I need to generate and calculate the invoice …..Where shall I do the calculation? In the Business object Layer or in somewhere else? And for invoicing the client every month I might have 20 clients and each one might have different day so the billing should be auto generated also what properties or methods that can calculate or check monthly related to the day so it will run in the way like the phone bill!! any advice is appreciate it!
Please i have created some tables Delivary with this columns (DelivaryId,DelivaryNo,QtyRecieved,DelivaryDate,ProductId) and Product with this columns (ProductId,ProductCode,ProductName,ProductPrice) as you can see the product table keeps record of products whlie the delivary table keeps record of stock supplied. I will like to create another table that will keep record of stock sold out (Invoice Table) based on the qty recieved from the delivaries table
Just seeing if people have any suggestions for me on this problem, i am trying to produce some form of invoice for a company and finding that cursors if the best method due to having to format the invoice using sql server. However i have come across a problem in a section where i need to have the invoice address and a shipping address i am finding it hard to sort out the spacing for example
Now the problem comes that for space reasons I have the datatype for the variables as varchar mainly but when running the report obviously its a problem that it appears disjointed
eg.
Alex JohnsonAlex Johnson 16 Stobson Street 16 Stobson Street Leeds Leeds LS12 6GHLS12 6GH
Now is there a way that this can be sorted like should I us char() datatype to specify the lengths,
Also is there a command the insert spaces instead of just using the quotation markers ' '
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?
I need a bit of generic advice about a project I am currently working on which is slightly out of my comfort zone. I€™m using SQL 2005 and VB.Net to develop an application that produces invoices for a number of different countries. The issue is that the data is arriving from an AS400 system so the tables I am working from are a little strange! This results in a fair degree of work tweaking the data into a format that is useful for my application.
To simplify the explanation each invoice in its basic form consists of the invoice data and the line items (so there is nothing new here). What is unusual is that the invoice data has an invoice number but then an additional invoice number specific to the country it is destined for. The initial invoice number arrives with the data from the AS400 but the secondary number is generated according to a number of business rules.
I have written an update query in a stored procedure which updates the invoice data with a number of values from a secondary table and a number of calculated values. There are several functions, one in particular which looks up the new invoice number from a secondary table based on specific criteria (country of destination being one of them) and I had intended to write an update query, called from within this function which then increments the invoice number but I have since found out that you cant do an update/delete from a function?
I can€™t add an update query to increment the value after the initial update query as this results in the stored procedure running through and adding all the invoice numbers (which are the same) and then the number being incremented at the end.
I assume I can€™t call an update query from within the initial update query so that the increment takes place after each loop of the initial update (if I can what would the syntax be?).
I have tried writing a trigger which completes the increment when the invoice data is updated. I haven€™t used triggers before but once again it appears as if the first update query is running through and completing all the updates and then the trigger fires incrementing the number. Or have I just got this wrong?
I guess what I need is a trigger which fires after each row of the table is updated which was how I assumed it should work!
Ironically I could write this in VB.Net with ease but for speed we need it within the database.
What is the best way to increment my invoice number?
I€™m sorry of this is general in nature but im really looking for some advice as to the best approach to deal with this issue.
I have a requirement to generate Invoice system for 1000 customers. how to create a report or SSIS Package to auto generate invoices everyday as per the start date of the customer.
I am using an invoice image that is preset and a fixed size. I am trying to lay text boxes on top of this image, but the boxes move around differently when you preview and/or print preview. If this is deployed, then the form prints and all of the text box information prints after the invoice image. Has any one experienced this before?
And one more question, is there a way to make a report a fixed length in size, ie, another invoice that might have 1 line or 23 lines and the total always needs to print at the bottom of the report and not move up the page. Is there a way to fix your report to accomodate for that? I have tried list box, matrix, etc and I am unable to find a solution to this.
Any help with either of these questions would be greatly appreciated!!!
I have a report that prints 20+ invoices and I need a page break after each invoice.
STEPS TO REPRO: 1) Create a report to show an invoice (one row of data) using a query that pulls 20+ rows of invoice data. 2) Table Properties | "Insert a page break after this table" is checked.
If you will I am trying to join a master invoice table to its detail records. The problem is I can't quite get the records to match correctly. There is a master record that has the net total of the invoice that corresponds to however many detail records for that invoice. I am attempting to get the records to line up in a query. I am having trouble because the key fields match the total up with each detail record. So for example in this record set below the 3825.75 value appears for each detail record so when I total the invoice column the figure is way too high. The detail has a 4462.54 and a -636.79 for a net of 3825.75. I tried to line the example up for better illustration. I copied it off a pdf and I am trying to replicate it programmatically.
All I have a table full of invoices and in that table there is a field named HCC. An invoice can contain multiple HCC's. I also have a table named hierarchical codes (below is an example)
(COL1)HCC...........(COL2)If any of the HCCs in this column exist on an invoice along with the HCC in column 1 then use HCC listed in column one 1 2 3.......................4 4 6 8.......................9 ,10 ,11 ,12 ,13
[code]...
I want to return invoice 1 with HCC set to 34 since the hierarchy for when any of these HCCs (35 ,36 ,37 ,38) exist on an invoice along with 34 is 34.
i need to generate documents for customers to sign automatically as sales staff enter their data into SQL. These are invoice style documents. I currently have word templates of the invoice documentation, i just need to be able to add the clients names, address etc into the relevant spaces for them to print off and sign.
I am good with TSQL and writing Stored Procs etc and can easily get the data ready - i just need to find a way to populate the templates in the right places and then save a copy for emailing.
Can you help me with SQL issue I€™m stuck on? I wish to take source data that looks like this:
Invoic_num
Line_num
6658
0
6658
2
6658
8
7721
2
7721
3
And rebuild the line numbers like this:
Invoic_num
Line_num
6658
1
6658
2
6658
3
7721
1
7721
2
This seems completely impossible to me. So I was thinking that maybe a second procedure using update could be run against the table after the initial build.
