Display The Variance Between Two Years In Row With Months In Columns?
Mar 17, 2008
I already read a lot about the inscope-function and how it is used to display variances over time periods. But I don't know where to start, as there is no tutorial how to setup this functionality. What I want to display within the report is the following:
Code Snippet
Months
ProductGroup Article Year 1 2 3 4 5 ....
Bicycles 1020 2007 1500 2000 etc.
2008 3000 3000
Var. abs. 1500 1000
Var. % 100% 50%
1025 2007 0 1000
2008 500 1200
Var. abs. 500 200
Var. % 500% 20%
Motorcycles etc.
View 14 Replies
ADVERTISEMENT
Aug 29, 2007
Hi All,
I have the following table "Project"
-------------------------------------------------------------------------------------------
ID Name Start Date End Date
--------------------------------------------------------------------------------------------
001 Project 1 2-2-2003 2-3-2007
002 Project 2 1-24-2003 2-6-2007
003 Project 3 4-10-2005 2-10-2008
004 Project 4 5-20-2006 6-6-2008
...
015 Project 15 2-20-2006 3-3-2009
----------------------------------------------------------------------------------------------
What I want is the the following output.
Output 1:
------------------------------------------------------------------------------------------------------------------------------------------------
Year Projects No. Of Projects Starting Months
------------------------------------------------------------------------------------------------------------------------------------------------
2003 Project 1 , Project 2 2 February, January
2005 Project 3 1 April
2006 Project 4, Project 15 2 May , Feb
------------------------------------------------------------------------------------------------------------------------------------------------
(the order displayed in the months shoudl be in accordance with the order of the projecs in the projects column...)
and also the following
Output 2: (this is optional view...)
-----------------------------------------------------------------------------------------
Year Start Date Project Name
-----------------------------------------------------------------------------------------
2003
February 2 Project 1
January 24 Project 2
2005
April 4 Project 3
2006
May 5 Project 4
February 20 Project 15
-------------------------------------------------------------------------------------------
I am very much in need of Output 1. Could someone help me,
View 5 Replies
View Related
Dec 15, 2007
Hi there,
for displaying purposes I'm trying to convert number of months into years and months. ie. 35 months = 2 years and 11 months.
how can I do this within a T-SQL script?
Thanks for your help.
Manny
View 1 Replies
View Related
Mar 3, 2015
formula to return patients ages 6 months to 5 years?
create table dbo.TEST
(
MRN varchar(10),
[Code]....
View 6 Replies
View Related
May 28, 2015
I have simple query that works fine if I write it every time. I want to get this one time group by each year and week ending. here us the query:
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/09'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/16'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/23'
How can I get the count for each week year and each week ending? Weekending 05/16 will include ID's from weekending 05/09 as well and week ending 05/23 will include all the ID's from 05/16 and 05/09 and so on...
View 13 Replies
View Related
Nov 5, 2015
Using 'TAIL([Time].[Time].[Year].MEMBERS,1)' I can get the current year dynamically. My question is how do I get the previous two years dynamically as well. I've tried a few different ways with no luck. As you can see below I'd like to replace 2014 and 2013 with current year -1 and current year -2.
SELECT
{
[Time].[Time].[Year].&[2013],
[Time].[Time].[Year].&[2014],
TAIL([Time].[Time].[Year].MEMBERS,1)
}ON COLUMNS,
NON EMPTY
{[Branches].[Branches].[Region]*[Measures].[Ship Resale S&D Run Rate]} ON ROWS
FROM SALES
View 9 Replies
View Related
May 13, 2015
I am stuck on a query where I need to display all the month year values even if the corresponding count_booking values are NULL. The requirement is to display the 13 month year period from current date.
For e.g. if the date exists in the current month then starting from May 15 go all the way back to Apr 14.
My current query works in terms of displaying all the 13 months if the count_booking values exist for all the months. However, if some months are missing the values then those months don't show up.how to display all the months even if the values are NULL ? Query below:
SELECT COUNT(m.BOOKING_ID) AS count_booking, LEFT(DATENAME(MONTH, m.CREATE_DT), 3) + ' ' + RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2)
AS month_name
FROM MG_BOOKING AS m
WHERE (m.CREATE_DT >= DATEADD(m, - 13, GETDATE()))
[code]...
View 8 Replies
View Related
Apr 24, 2015
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
View 6 Replies
View Related
Apr 30, 2015
I would like to display a portion of report where there is data or no data
There is data subreport display
Product Name Latex Gloves
Product ID
xxxx5678
There NO data in the subReport
Product Name
Product ID
View 3 Replies
View Related
Dec 14, 2007
Hi i need a sql stored procedure below, this returns 2 columns of data, what i now need is to display the months as columns
ie.
TotalQty - OL_St_Code - Jan - Feb - March
then each month column shows a TotalQty for that month, do i need a query of query? if so how would i go about this
select count(OL_Qty)as TotalQty, OL_St_Code
from dbo.X_TBL_ORDER_LINEWEB LW
LEFT JOIN dbo.X_TBL_ORDERWEB OW ON OW.O_ID = LW.OL_O_ID
WHERE MONTH(O_Date) Between '11' AND '12'
group by Month(O_Date), OL_St_Code
View 19 Replies
View Related
Jul 18, 2007
I have created a tabular report. Some of the columns I have are titled "AdjustedHistory1", "AdjustedHistory2" and so on. I also have columns titled "AdjustedForecast1", "AdjustedForecast2", and so on. I want a column to be able to retrieve the current date and thus display the information "July" (i.e. the current month). Next, I want "AdjustedHistory1" to be June, "Adjusted History2" to be May, and so on. Then I want "AdjustedForecast1" to be August, "AdjustedForecast2" to be September, and so on. The trick is, I need these fields to auto-update; meaning so that when the current month is August, "AdjustedHistory1" is July, "AdjustedHistory2" is June, "AdjustedForecast1" is September, and "AdjustedForecast2" is October.
How do I go about doing this?
View 6 Replies
View Related
Jan 26, 2007
Hi, i'm reasonably new to reporting services and am looking for a way to split my reports' Years to compare the months in year 2005 to 2006 but i can't get my data nest to one another in a single line, it splits the years into different rows
as an example this is what i want if you can decipher that
2005
2006
Growth
2005 Year to Date
2006 Year to Date
Year to Date Growth
turnover
gross profit
turnover
gross profit
turnover
gross profit
turnover
gross profit
Jan
250500
75300
280200
84100
11.85629
11.686587
250500
75300
Feb
205000
67950
190350
59900
-7.14634
-11.84695
455500
143250
take the month above and add the
current months values
Mar
217670
70540
234200
78000
7.594064
10.57556
673170
213790
Apr
270780
84000
290400
93000
7.245735
10.714286
943950
297790
May
265000
79260
289050
90200
9.075472
13.802675
1208950
377050
Jun
277300
81050
277900
82000
0.216372
1.172116
1486250
458100
Jul
Aug
Sep
Oct
Nov
Dec
Here is my Query:
SELECT /*DT.[YEAR],*DT.[MONTH],*DT.MONTH_NAME,*/ DC.CLIENT_KEY, (select SUM(FT.Cost)where dt.[year] = 2005) AS COST , (select SUM(FT.Price)where dt.[year] = 2005)AS SALES,(select SUM(FT.Cost) where dt.[year] = 2006),(select SUM(FT.Price) where dt.[year] = 2006)--, SUM(FT.QTY) AS QUANTITY, SUM(FT.PRICE) - SUM(FT.COST) AS GP,(SUM(FT.PRICE) - SUM(FT.COST)) / SUM(FT.PRICE) * 100 AS GP_PERCENTAGEFROM FACT_TRANSACTION FT, DIM_TIME DT, DIM_CLIENT DC, DIM_INVOICE_TYPE DIT, DIM_PRODUCT DPWHERE FT.TIME_KEY = DT.TIME_KEYAND FT.PRODUCT_KEY = DP.PRODUCT_KEYAND FT.CLIENT_KEY = DC.CLIENT_KEYAND FT.TYPE_KEY = DIT.TYPE_KEY AND DIT.TYPE_KEY NOT IN (5,6,13,14,15,16,17)AND DC.CLIENT_SERIALNO = '86634'--AND DT.[YEAR] IN(2005,2006)AND DT.[MONTH] IN(1,2,3,4,5,6,7,8,9,10,11,12)AND DP.PRODUCT_KEY <> 1668684GROUP BY DT.[YEAR],DC.CLIENT_KEY--, DT.[MONTH]ORDER BY /*DT.[YEAR],*/DT.[MONTH]
but it returns everything under one another
2005 1 January 2005 3 296092.3431 405263.62 12811 109171.2769 26.93
2005 2 February 2005 3 318597.658 432098.17 13220 113500.512 26.26
2005 3 March 2005 3 371327.721 506481.46 15283 135153.739 26.68
2005 4 April 2005 3 371647.994 504713.99 15491 133065.996 26.36
2005 5 May 2005 3 400870.6138 542759.57 16296 141888.9562 26.14
2005 6 June 2005 3 399673.0086 546110.59 16607 146437.5814 26.81
2005 7 July 2005 3 390477.7521 535531.40 16153 145053.6479 27.08
2005 8 August 2005 3 380628.57 520281.87 15800 139653.30 26.84
2005 9 September 2005 3 340949.8849 471861.17 14820 130911.2851 27.74
2005 10 October 2005 3 340240.804 470007.78 14444 129766.976 27.60
2005 11 November 2005 3 349156.1871 481193.61 14523 132037.4229 27.43
2005 12 December 2005 3 346038.5059 477011.72 14865 130973.2141 27.45
2006 1 January 2006 3 340062.1369 470010.08 14037 129947.9431 27.64
2006 2 February 2006 3 328463.9689 452404.79 13996 123940.8211 27.39
2006 3 March 2006 3 375264.977 517800.27 16065 142535.293 27.52
2006 4 April 2006 3 412708.965 567014.52 17550 154305.555 27.21
2006 5 May 2006 3 446973.4231 606476.26 18920 159502.8369 26.29
2006 6 June 2006 3 406072.4943 544634.77 17053 138562.2757 25.44
2006 7 July 2006 3 389104.6316 526091.14 16228 136986.5084 26.03
2006 8 August 2006 3 317810.4531 431530.58 13641 113720.1269 26.35
2006 10 October 2006 3 405230.7083 549310.72 17151 144080.0117 26.22
2006 11 November 2006 3 379788.6645 514554.14 15917 134765.4755 26.19
2006 12 December 2006 3 393235.0906 531582.69 16924 138347.5994 26.02
If i do get them split then it put every year's value on a different line
2005 1234123.34 32432432.43 NULL NULL
2006 NULL NULL 12312.212 15235453.21
Please Help,
View 1 Replies
View Related
Oct 16, 2014
I am trying to create a calendar style report that will have 12 months (as columns) and store opening listing in rows. I have created a matrix, but the problem that I have is that the store opening listing displays in the right period, but they are not in any order. I would like to have the openings always on top, right under the header in the matrix. Now I have them scattered randomly all over the matrix. I tried numerous way of sorting and that does not work.
I am attaching a sample of what I would like to accomplish (months are columns).
View 0 Replies
View Related
Mar 27, 2008
I have the following table
FeedBack Type Date
test2 positive 03/15/08
tes3 negative 03/01/08
.. ....
in my page i need to select the number of negative/positive comments within the last
1 month, 6 months, 12 months
How can I accomplish that?
thanks
View 5 Replies
View Related
Jun 5, 2015
One Table have 2 Columns.
1.Order_number
2.PSON NUmber
In Table Contains 6 rows with 2 order_numbers and different PSON Number.I want Order Number,PSON1,PSON2,PSON3,PSON4 and its Values
Ex:--Order_number PSON NUmber
1 ONT10
1 ONT11
1 ONT12
2 ONT13
2 ONT14
I want data :- Order_number PSON1 PSON2 PSON3
1 ONT10 ONT11 ONT12
2 ONT13 ONT14 Null
View 3 Replies
View Related
Feb 5, 2008
My first ASP.NET/SQL project. I'm creating an Asset Management DB. I wish to view the Asset number, the full name of the user it's assigned to and the Make and Model of each record in the DB. However assets are split into different categories e.g. monitors, PCs, Preinters etc. and they are all in different tables. The SQL below displays the asset number, Name of person assigned and the model of the asset.
SELECT Hardware.AssetNo, [User].FullName, MonitorModel.Model, PCModel.Model AS Expr1, PrinterModel.Model AS Expr2
FROM Hardware INNER JOIN
[User] ON Hardware.UserID = [User].UserID INNER JOIN
Model ON Hardware.ModelID = Model.ModelID LEFT OUTER JOIN
MonitorModel ON Model.MonitorModelID = MonitorModel.MonitorModelID LEFT OUTER JOIN
PCModel ON Model.PCModelID = PCModel.PCModelID LEFT OUTER JOIN
PrinterModel ON Model.PrinterModelID = PrinterModel.PrinterModelID
This outputs:-
Asset number FullName Model Expr1 Expr2
00000 User Name Model NULL NULL
00001 User Name NULL Model NULL
00002 User Name NULL NULL Model
However what i hope to acheive is output Model, Expr1, Expr2 into one column like so:-
Asset number FullName Model
00000 User Name Model
Can i do this in the SQL or do i have to do it in my ASP.NET (VB) Page?
Using VSWD 2005 Ex Edition and SQL Server 2005 Ex Edition
Thank you for your replies
View 4 Replies
View Related
Jan 3, 2008
Does somebody know how do you get a column in Reporting Services to display the values from two different columns? I don't mean a concat, but the result of the two columns.
This has been a headache for 2 days now.
Thanks!!
View 3 Replies
View Related
Oct 21, 2014
I have a table. I have some sensitive data that I don't want to display that sensitive data. The columns I want to abstract are
Firstname varchar(50) not null
Latname varchar(50) not null
dob datetime not null
addressline1 varchar(50) null
adressline2 varchar(50) null
city varchar(50) null
postalcode varchar(20) null
I want to display null values as nulls empty as empty not null as numbers.
View 4 Replies
View Related
Apr 17, 2014
I have the below working query
select distinct OrderNum from invoiceHistory where orderNum in (56387,57930,57933,57935)
I need to get all columns of the distinct ordernum as resultset. I am working to get the resultset of below.
select invoiceID,distinct orderNum,invoiceDate from invoiceHistory where orderNum in (56387,57930,57933,57935)
View 1 Replies
View Related
Jul 31, 2007
Hi all,
i have a table which has date and count as column
Can you please...help me write a query for it.
date | type | count
10/07 | A | 70
11/07 | A | 50
12/07 | A | 50
11/07 | B | 60
12/07 | B | 50
13/07 | B | 40
11/07 | C | 50
12/07 | C | 60
13/07 | C |60
14/07 | A | 50
i need to write a sp such that..if user sends date and @ndays(no. of days) as parameter say 11/07 and 3
then
out put
type 11/07 12/07 13/07
A 50 50
B 60 50 40
C 50 60 60
Thanks..
Regards,
sradez
View 3 Replies
View Related
Sep 17, 2007
Hello, I have the following table with 4 columns....firstname, lastname1, lastname2, EMAILTable has user names and email, I would like to generate a 5th columncalled DisplayName.The email Id is sometimes firstname.lastname1.lastname2@ and othersjust firstname.lastname1@I would like to generate the display name exactly like the email egfirstname.lastname1.lastname2@ displayName = firstname lastname1lastname2......so for james.smith display name = James Smith and forjames.earl.smith displayName = James Earl Smith etc etcIs there a way that I can check/compare email Id (before the @ part)with firstname, lastname1 and lastname2 and generate a display namebased on what was used for the email address?I hope I've explained this well :-)Many thanks in advance for any help/adviseYas
View 5 Replies
View Related
Nov 15, 2006
Hi,
i have a problem where in i have to display the greatest marks scored by each student.
How can i do this?? Is there any built in TSQL function.
Rgds..,
Aazad
View 4 Replies
View Related
Aug 16, 2007
How to display 2 columns of Data in 1 column?
If I use like this
Select Names, 'myData' from emp
The output is:
Name1 myData
Name2 myData
Name3 myData
But, I need to display like this
Name1
myData
Name2
myData
Name3
myData
please advise
View 5 Replies
View Related
Dec 27, 2007
Hi there,
I have the following matrix which I already built in SRS report.
XX
YY
XXParts
XXParts
Qty
Average
LastPO
Qty
Average
LastPO
VarianceQty
VarianceAverage
VarianceLastPO
0-100CBM
10
20
30
5
4
3
5 ((10-5)
16 (20-4)
27 (30-3)
0-100INWC
11
22
33
3
4
5
8 (11-3)
18 (22-4)
28 (33-5)
How do I find the VarianceQty, VarianceAverage and VarianceLastPO values?
Thank you for your help,
dhv919401
View 1 Replies
View Related
Dec 12, 2013
Trying to get the PSI Outcome, Expected, and PSIIndex every month whether it has data or not. Created a CTE and left outer joined with PSI table, but it's still not pulling every month for every PSIKey.
Table schematics
di.DivisionRegion,int
P.PSIKey,int
P.PSIOutcome,int
P.PSIExpected,int
[Code] .....
View 2 Replies
View Related
Feb 19, 2014
This is my table structure
--=====
create table calculate(
ID int identity (1,1),
PreviousYear_Profit float not null,
)
insert into calculate values (12500)
insert into calculate values (22700)
insert into calculate values (18500)
insert into calculate values (25800)
--======
I want to calculate variance and expected output is
ID PreviousYear PriorVersion Variance
1 12500 sum(PreviousYear) PreviousYear-PriorVersion
2 22700 sum(PreviousYear) PreviousYear-PriorVersion
3 18500 sum(PreviousYear) PreviousYear-PriorVersion
4 25800 sum(PreviousYear) PreviousYear-PriorVersion
I don't have PriorVersion and Variance columns.
PriorVersion will be like sum(PreviousYear)
Variance will be (PreviousYear - PriorVersion)
I can query if I have column, since I don't have these two column how could I possibly achieve the output using my existing table schema.
View 1 Replies
View Related
Apr 9, 2008
1. Is it possible to use the subtotal as a field for calculating values?
2. Can I add another row to the subtotal area, having two subtotal rows?
I need to achieve the following output:
Months
1 2 3
101200 Cust1 2008 50 40
2007 45 45
Subtotal +5 -5
102300 Cust2 2008 70 80
Subtotal 0 0
What I want is to use the subtotal as a variance-field for the two selected years by the user. It could happen that a customer doesn't generate turnover in one year, then the subtotal has to be 0 or 100 or whatever.
If my suggestions aren't applicable then I'd like to know If there is another possibility to display the desired results.
Thanks!
View 8 Replies
View Related
Apr 23, 2008
I have built an Advanced Search page which allows users to select which columns to return (via checkbox) and to enter search criteria for any of the selected columns (into textboxes). I build the SQL statement from the properties of the controls. Works great.
My problem is getting my gridview control to play nicely. At first I used a SqlDataReader and bound the gridview to it, thus giving me the ability to run new SQL statements through it (with different columns each time). Worked nicely. But, per Microsoft, sorting can only be done if the gridview is bound to a datasource control like the SqlDataSource. So I wrote the code to handle sorting. No big deal; worked nicely. But I could not adjust the column widths programmatically unless bound to a datasource control like the SqlDataSource. And could not figure out a work around.
So, I decided to use the SqlDataSource. Works great. Except, I cannot figure out how to run a new SELECT statement through the SQLDataSource and have the gridview respond accordingly. If I try to return anything other than the exact same columns defined declaratively in the html, it pukes. But I need to be able to return a new selection of columns each time. For example, first time through the user selects columns 1,2,3,4 – the gridview should show those 4 columns. The second time the user selects columns 2,5,7 – the gridview should those 3 columns (and ONLY those 3 columns). Plus support selection and sorting.
I am desperate on this. I've burned 2.5 days researching and testing. Does anyone have any suggestions?
Thanks,
Brad
View 9 Replies
View Related
Jan 19, 2012
I am using Query Writer (should be SQL 2005) and have included the following code.
The end result: -should retrieve columns and display the count for two different data sources that were added by personnel in a specific department.
Problem: results are returned but not accurate. The code below works just fine and returns the results for all spot buy orders added by personnel in the sales department. However, I want to add a column in the same report that also counts blanket orders from the exact same table added by personnel in the sales department. The database uses views (v) in lieu of dbo.
Parameters:
@Add_Date_From SMALLDATETIME='',
@Add_Date_To SMALLDATETIME='',
@Last_name VARCHAR(50)='',
@First_Name VARCHAR(50)=''
Query:
SELECT
T1.Last_name,
T1.First_name,
T2.Position,
T3.Add_date,
COUNT(T4.PO_Type) AS 'Spot Buy Added'
[Code] ....
If I substitute COUNT(T4.PO_Type) AS 'Spot Buy Added' with COUNT(T4.PO_Type) AS 'Blanket Added' I also get accurate results for the blanket order. IE separately they work just fine. If I try to combine the two that is where the trouble begins.
What am I doing incorrectly when I try to add the criteria/code for the additional column to count the blanket orders?
View 9 Replies
View Related
Nov 11, 2007
Hi,
I'm using SSRS to generate reports. i have many columns data to be displayed.while converting the data into Excel and PDF the data, header, and footer are not displaying proper format. what are all the properties to be set for that.
Could any one help in this regard.
Thanks for your help..
View 1 Replies
View Related
May 13, 2015
Below is a picture of my report format. It's the yellow column in trying to calculate which is basically a subtraction between the 7th Column (which is based on the date selected by the user in the parameter) and column 6 which is the day before.
View 1 Replies
View Related
Jun 19, 2007
I have used SSRS 2005 to create a matrix and need to add a % of variance column. Is this possible? I hope I'm missing something simple. Basically the report should look like this:
Item CurVol PriorVol % of Var CurSales PriorSales % of Var
abc 100 90 11% 1250 990 26%
cde 96 128 -25% 192 243 -21%
Dataset looks this this:
Select item, vol, sales, 'Current' AS Per
FROM Table
WHERE (invoice_date >= @curStartDate) AND (invoice_date < @curEndDate + 1)
UNION
Select item, vol, sales, 'Prior' AS Per
FROM Table
WHERE (invoice_date >= @priorStartDate) AND (invoice_date < @priorEndDate + 1)
The column group on the matrix is using the Per column because the accounting periods cross over calendar months.
gusina
View 4 Replies
View Related
Jun 18, 2015
We are planning to develop weekly report in SSRS.For this we wants each day as column & some expenses[Numeric figure in row]we have dataset like
day
exp1
exp2
exp3
17/05/2015
120
150
650
[code]....
There are some other filters are there that i have applied in my report tablix property.
View 2 Replies
View Related
