Matrix: Calculating Variance In Rows
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
ADVERTISEMENT
Aug 6, 2015
I got the following code to add a column in a matrix with a variance:
IIF(IsNothing(Previous(Sum(Fields!Amount.Value))) or Fields!year.Value=First(Fields!year.Value,"Category") or Previous(Sum(Fields!Amount.Value))=0,nothing,
(
(Fields!Amount.Value)
/Previous(sum(Fields!Amount.Value))
)
)
This code works fine, except that the first row of the matrix shows an #error
This happens with each matrix where I use this expression. A warning emerges:
rsruntimeerrorinexpression the value expression for the textrun Textbox43.Paragraphs[0].TextRuns[0]' contains an error.
Attempted to divide by zero.
The strange thing is that the part
Fields!year.Value=First(Fields!year.Value,"Category")
should prevent an error and I expect it to show 'nothing'
An screenshot of the table. (each color is a different category. Each row stands for 2013, 2014, 2015)
As you can see, all other 2013 rows show a blank cell, except the first row.
View 3 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
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 9, 2015
I am having trouble with calculating AVG in a matrix. I have done the subtotal but I would like to know how to calculate the Average. I would also like AVA below the Total.
Link : [URL] ....
I have also added an image oh how the matrix looks like.
View 5 Replies
View Related
May 15, 2008
Hi,
I am sure there is a simple answer to this, but I cannot find it at the moment???
I have a simple data table in SQL which gives me Division, PL Measure and Value...
SQL Table
Division
PL_Desc
Result
A
Total Labour Costs
10
A
Total Sales (inc Machine Income)
100
B
Total Labour Costs
5
B
Total Sales (inc Machine Income)
100
C
Total Labour Costs
9
C
Total Sales (inc Machine Income)
100
I need to report on this and calculate a ratio, so I have pushed this into a Matrix Report but cannot work out how to get the ratio column to work???...
Matrix Report
???????????
Division
Total Labour Costs
Total Sales (inc Machine Income)
New Column = Labour / Sales
A
10
100
10%
B
5
100
5%
C
9
100
9%
Grand Total
24
300
8%
If anyone can help save me from my own stupidity!
Cheers, Matt
View 6 Replies
View Related
Jun 22, 2015
I have got this matrix and I am trying to calculate the average amount of working days in a month. At the moment, I have divided the total number of jobs by 21 for every month which is a hard coded value. However, I am not sure how to retrieve this value dynamically. Is there any formula that can find out the working days?
View 7 Replies
View Related
Nov 26, 2007
Hello.
I hope to explain myself well - I want to make a matrix with two rows.
Lats say my data is this:
I hava a list of months and in every month I have a number of pepole and there age.
How can I show this in a matrix?
It need to be in a matrix since I need the columns to expand acording to the month but I don't know how to create two diffrent rows in my matrix.
The data should look like this:
10/06 11/06 12/06 01/07 02/07 03/7 04/07 .....
num 5 1 2 5 4 5 7 .....
age 16.1 25 18.5 14.8 25.5 20.5 18.5 .....
Thanks for any help.
View 3 Replies
View Related
Mar 19, 2008
Dear all,
I havea table name HISTORY_MEASURE which is a collection of different measure value issue from different mesuring device.
Values inthis table is represented as follow :
Id Name Value
==============================
1 Diameter1 0.45
2 Diameter2 1.23
3 Temperature2 123
4 Temperature2 200
5 Diameter1 0.65
Out of this table what I need to do is calculate the average value for each same [Name]. As you can see from the sample set above, the Diameter1 has 2 entries value hich gets store at different time of course.
So I I take the example of Diameter1 I need to calculate and display in a field the average results.
The result would be
Name Average
=====================
Diameter1 .....
Diameter2 ....
Temperature2 ....
Temperature1 ....
How can I perform this ?
Or could it be better to get a view of the table above which gets display as follow :
Diameter1 Temperature2 Diameter2
0.45 123 1.23
0.65 200 0
Thanks fro your help
regards
serge
View 7 Replies
View Related
Aug 23, 2007
Hello forum.
I have three columns in my table with the following datatypes:
Date - DateTime
Height - Decimal
Change_From_last_Height - Decimal
I am using "SQL Server 2005 Express Edition". I'm fairly new to SQL and would greatly appreciate any help or advice I can get.
The "date" column increases by an extra day in every new row and I then enter the new height of the plant. What I want to know is how I can get SQL server express to automatically enter the difference in height between the current row's height and that of the previous row.
Is it possible to automate the entry in the Change_From_Last_Height column in SQL?
Put another way, I know how to find the difference between two values in the same row but different columns, but how do I calculate the difference between values in adjacent Rows (ie. Rows next to each other)?
Thank you.
View 3 Replies
View Related
Oct 2, 2014
I have two tables that look like this (below). One tells me the name of my product, the Amazon Category it is in & the amount that I want to sell it for. The other tells me the Category & the fee for that category. So far so good. Though it gets tricky in the sense that some categories have two tiers. So in Electronics, the fee for $0.00 - $100.00 is 15%. But from $100 and up it is 8%.
Since it has two columns & both of the new columns pertain to the fee of my product, I can't figure out how to use both at once. For my $599.99 example it would be ($100 * 0.15) + ($499.99 * 0.08) = $55.00. Would I pivot the data? If not, how would I group it to be considered together?
Category Example
IDAmazonCategoryIDAmazonCategoryNameFeePercentageStartPriceEndPrice
1apsAllDepartments0.150.000.00
2instant-videoAmazonInstantVideo0.000.000.00
3appliancesAppliances0.150.000.00
Product Example
1Product1Electronics9.99
2Product3Electronics99.99
3Product2Electronics599.99
Raw SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #Amzn_Category_FeeStructure(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] ....
I use Microsoft SQL 2008
View 2 Replies
View Related
Mar 29, 2008
I am trying to create a SSRS report that needs to show average for both parent and child rows. In the example below i need to show an average of ErrorRecAge column for Company, then for cic_Group and one grand total/avg. How can this be done from single query (as below) and using SSRS built in functions/codes? I am also curious to know if this can be done from TSQL directly (ex. something similar to running sum/total). I appreciate the help.
Select
CustomerCorrection.Id As ParentId,
cc_company As cc_company2,
ccError.Id As ErrorId,
COALESCE(cic_Group,'Other Errors') As cic_Group,
COALESCE(cic_Code,'Unknown') As ErrorCode,COALESCE(cic_Description,'Unknown') As ErrorDescription,
DateDiff(hh,cc_entrydt,getdate())/24.00 AS ErrorRecAge
From
CustomerCorrection Inner Join CCError On
CustomerCorrection.Id = CCError.CustomerCorrectionId
Left Outer Join CustomerImportControl On
cic_code = ce_errno
Where cc_company = 'IWA' And CustomerCorrection.Id In (129,1004,3228)
Order By CustomerCorrection.Id
That Returns
129 IWA 992 Other Errors Unknown Unknown 399.500000
129 IWA 1089 Other Errors Unknown Unknown 399.500000
129 IWA 1760 Other Errors Unknown Unknown 399.500000
1004 IWA 952 Other Errors Unknown Unknown 365.333333
1004 IWA 1853 Other Errors Unknown Unknown 365.333333
3228 IWA 10 Other Errors Unknown Unknown 329.375000
Here is my temp workaround:
Select a.*,b.*
From
(
Select
cc_Company, Count(Distinct CustomerCorrection.Id) as RecCount, Avg(DateDiff(hh,cc_entrydt,getdate()))/24.00 As RecAge
From
CustomerCorrection
Group By cc_Company
) As a
Inner Join
(
Select
cc_company As cc_company2,
COALESCE(cic_Group,'Other Errors') As cic_Group,
Count(Distinct ccError.Id) As ErrorRecCount,
AVG(DateDiff(hh,cc_entrydt,getdate()))/24.00 As ErrorRecAge
From
CustomerCorrection Inner Join CCError On
CustomerCorrection.Id = CCError.CustomerCorrectionId
Left Outer Join CustomerImportControl On
cic_code = ce_errno
Group By cc_company, COALESCE(cic_Group,'Other Errors')
) as b
On a.cc_company = b.cc_company2
Order By cc_Company,cic_group
View 2 Replies
View Related
Dec 28, 2007
my matrix is cutting off at 42 rows. Is this the max? Any way to override this max?
View 5 Replies
View Related
Nov 28, 2007
Hi All
I have a matrix with single row. The no. of column varies and sometimes goes to 10-15. So it goes to next page and while exporting it inserts blank pages when exported to PDF. I need the column width at least 2.5cm. I need to break the matrix to next row instead of it going to next page say after 6th or 8th column. I tried to work with the example given in the site http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx by Chris Hays. But it is showing matrix for each Row Group, which doesn't meet my requirement.
I had a work around which worked by putting two matrix one below the other and filtering the columns to be shown in each matrix.
If anybody faced this issue or anybody solved the issue kindy reply which will be very helpful for me.
One more doubt, Can I get the Column number of the matrix?
Thanks in advance
Dileep
View 3 Replies
View Related
Jul 4, 2005
Is there any way to add a header to a Matrix row in RS SP2? Much like a header for fields in a table.
View 4 Replies
View Related
Jun 19, 2007
My requirement is to generate multiple columns dynamically.So I have used Matrix.It working fine.I can able to generate columns dynamically based on dealer.
In Data region region of matrix I have given the results of that column.But it is displying only first record of each column.In need to display all records.Please help me.
View 1 Replies
View Related
Jul 19, 2007
I have a table and matrix placed next to each other in my report:
[Table displaying ProjectInfo, Revenue, etc.. ][Matrix displaying Monthly Distribution]
When I run the report, the result set returns say 100 rows for table and a row for each project in Matrix. The header and detail rows of table and matrix are perfectly aligned with each other, however, on the first page the table displays 47 rows and then breaks while the matrix displays 50 and breaks. On the next page the Matrix is three rows shorter. As a result the bottom of each control does not align on any page.
Any ideas what could be going wrong or how to fix this?
Thanks.
View 3 Replies
View Related
Jul 26, 2007
I just spent about 30mins searching through the forums for this and saw several posts, but I didn't find a straight answer that seems like it would work for my report. How can I add only 2 data rows to a group (to hide them via the group's visible properties) and keep the rest of the rows outside of the group, but still in the same column (vertical area), as shown:
This is the view of all rows:
Cat1
Cat2
Product A
Data 1
#
#
Data 2
#
#
Data 3
#
#
Data 4
#
#
Data 5
#
#
Data 6
#
#
Product B
Data 1
#
#
Data 2
#
#
Data 3
#
#
Data 4
#
#
Data 5
#
#
Data 6
#
#
And this is the view I'm seeking in some cases:
Cat1
Cat2
Product A
Data 1
#
#
Data 4
#
#
Data 5
#
#
Data 6
#
#
Product B
Data 1
#
#
Data 4
#
#
Data 5
#
#
Data 6
#
#
View 4 Replies
View Related
Oct 23, 2007
Hi,
I am working on a matrix report shown as below: the first one is what i got now, the second one is what I desire to have
Quarter1
January
February
March
Total
CCC
2006
9
9
19
37
2007
2
17
15
34
CHTDS
2006
5
15
10
30
2007
6
8
9
23
FTA
2006
4
9
3
16
2007
4
4
6
14
GDA
2006
9
8
12
29
2007
15
7
16
38
Quarter1
January
February
March
Total
CCC
2006
9
9
19
37
2007
2
17
15
34
Diff
-7
8
-4
-3
CHTDS
2006
5
15
10
30
2007
6
8
9
23
Diff
1
-7
-1
-7
FTA
2006
4
9
3
16
2007
4
4
6
14
Diff
0
-5
3
-2
GDA
2006
9
8
12
29
2007
15
7
16
38
Diff
6
-1
4
9
How can I get the difference between 2006 and 2007 for each category as highlighted in yellow?
Thanks,
xhh
View 3 Replies
View Related
Dec 10, 2007
Hello anyone!!! Can anyone helpme with this pls?
I have this data model: there is 4 tables A, B, C and D. The constraints says that a identity row in the table A is the forain key in the table B, and the same row is a forain key in the table C. The table D have two forain keys with the table C and B.
C A
++ <-- ++
++ ++
| |
V V
++ <-- ++
++ ++
D B
I need make in a part of a report this: for each row of the table D the report have to print the row of the table C that has found by the constraint and then in the next row the report have to print the row of the table B found by the other constraint. It could looks like this:
C.field1
B.field1
The Problem that I have is that If in the table D are more rows, ie. 3, the report have to crossed sort the next rows such this
C.field1
B.field1
C.field2
B.field2
C.field3
B.field3
In the report with a matrix I have done more or less this, but not exactly that I need to implement. cuz in this way the report returns to me this:
C.field1
C.field2
C.field3
B.field1
B.field2
B.field3
anoter solution that I tried to implement is do this, in a single field write this "Fields!C1.Value & vbCrLf & Fields!B1.Value" but the problem with this is that the fields of the table B are not the same or the same number that in the table C. So this isn't the solution too.
So wonderfull developers, anyone have any idea or know how to resolve this? THX!!!
View 1 Replies
View Related
Jan 18, 2008
I've got what I thought was a fairly simple matrix request, but just can't seem to do it in reporting services.
In the rows, things are grouped by campaign. In the columns, things are grouped by district. Something like this:
Area1 Area2 Area3 Total
CampaignType1 1 2 3 6
CampaignType2 2 4 1 7
CampaignType3 3 3 1 7
Total 6 9 5
Campaign and Area are all selectable by parameters, so the actual number of rows is dynamic.
What I would like to do is an additional aggregates other then sum for the total - things like average, percent to goal, etc.
Area1 Area2 Area3 Average % attained Total
CampaignType1 1 2 3 2 60% 6
CampaignType2 2 4 1 2.33 70% 7
CampaignType3 3 3 1 2.33 70% 7
Total 6 9 5
And this is where, either I'm completely missing something, or SSRS and I have a huge communication breakdown . I absolutely cannot seem to do this. I tried adding additional columns, but they are grouped under area, not after it - in the above example, it would result in three new columns, one for each area type, not 1. Confusing to describe, but looks something like:
Area1 Area2 Area3 Total
Sum Avg Sum Avg Sum Avg CampaignType1 1 xx
CampaignType2 2 yy
CampaignType3 3 zz
Total
I hope that conveys the idea w/o having to fill it all in.
I'm lost as to how to get this accomplished. All I can think of is adding a union dummy row into the actual stored proc to make a different area type (say, AreaAverage) just to add in an additional column and that make sure it sorts at the end. That screams hack to me. Any help????
View 2 Replies
View Related
Nov 3, 2015
My stored procedure give me top 3 activities. My SSRS report design, I am able to display first activity when I select the Activity field from the Stored procedure, how to display 3 activities for each category.. Also I need a solution to sort the categories based on the requirement below.
View 2 Replies
View Related
Nov 22, 2007
Hello,
I have a report in wich I show a list of countries. The length of this list differs each month and comes from a SSAS datasource.
I want to show this list on my report in a matrix like this:
country1 country6 country11
country2 country7 country12
country3 country8
country4 country9
country5 country10
How can I do this? I need some sort of check that counts the number of countries added and then adds a new column after 5 countries.
I'm sure there is somebody that did this or knows how to do this.
Thanks in advance!
Jorg.
View 3 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
May 31, 2007
I have one column in a matrix component and it has about 7 items, but the only the items which have values on the page appear at the top of that page.
This is for a labratory so the columns are the different Patient Types and the rows are the different Test Mnemonics. If one of the Patient Types is not used in any of the tests on that page, it doesnt show up. How to I make sure all Patient Types show up on every page?
Thank you all.
View 2 Replies
View Related
Jul 19, 2007
Is there a way to control how many Detail Rows are displayed on one page in Table and Matrix controls?
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
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
View Related
Jul 13, 2007
Hi
I have a dataset with 2 columns, a rownumber and a servername - eg
rownumber servername
1 server1
2 server2
....
15 server15
I want to display the servernames in a report so that you get 3 columns - eg
server1 | server2 | server3
server4 | server5 | server6
...
server13 | server14 | server15
I have tried using multiple tables and lists and filtering the data on each one but this then makes formating very hard - i either end up with a huge gap between columns or the columns overlap
I have also tried using a matrix control but cant find a way to do this.
Does anybody know an easy way to do this? The data comes from sql 2005 so i can use a pivot clause on the dataset if somebody knows a way to do it this way. The reporting service is also RS2005
Thanks
Anthony
View 1 Replies
View Related
Jan 19, 2008
Code Block
Hi,
I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem:
Table Name: metrics_ladder
id security_id metric_id value
1 3 80 125.45
2 3 81 548.45
3 3 82 145.14
4 3 83 123.32
6 4 80 453.75
7 4 81 234.23
8 4 82 675.42
.
.
.
Table Name: metric_details
id metric_id metric_type_id metric_name
1 80 2 Fiscal Enterprise Value Historic Year 1
2 81 2 Fiscal Enterprise Value Current Fiscal Year
3 82 2 Fiscal Enterprise value Forward Fiscal year 1
4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2
5 101 3 Calendar Enterprise value Historic Year 1
6 102 3 Calendar Enterprise Value Current Fiscal Year
5 103 3 Calendar Enterprise value Forward Year 1
6 104 3 Calendar Enterprise Value Forward Year 2
Table Name: metric_type_details
id metric_type_id metric_type_name
1 1 Raw
2 2 Fiscal
3 3 Calendar
4 4 Calculated
The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder:
id security_id metric_id value
1 3 101 <calculated value>
2 3 102 <calculated value>
3 3 103 <calculated value>
4 3 104 <calculated value>
Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81.
Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82,
metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally
metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use).
As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes:
security_id 80 81 82 83 101 102
----------- -- -- -- -- -- --
3 125.45 548.45 145.14 123.32 <calculated value> <calculated value>
4 ...
.
.
.
which is then unpivoted.
The SQL that achieves this is more or less as follows:
*********
START SQL
*********
declare @calendar_averages table (security_id int, [101] decimal(38,19), [102] decimal(38,19), [103] decimal(38,19), [104] decimal(38,19),etc...)
-- Dummy year variable to make it easier to use MONTH() function
-- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc...
DECLARE @DUMMY_YEAR VARCHAR(4)
SET @DUMMY_YEAR = 1900;
with temp(security_id, metric_id, value)
as
(
select ml.security_id, ml.metric_id, ml.value
from metrics_ladder ml
where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...)
-- only consider securities with fiscal year end not equal to december
and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec')
)
insert into @calendar_averages
select temppivot.security_id
-- Net Income
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103]
,NULL as [104]
-- Share Holders Equity
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107]
,NULL as [108]
-- Capex
-- Sales
-- Accounts payable
etc...
..
..
from temp
pivot
(
sum(value)
for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...)
) as temppivot
inner join company_details cd on temppivot.security_id = cd.security_id
*********
END SQL
*********
The result then needs to be unpivoted and stored in metrics_ladder.
And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET.
Many thanks (if you've read this far!)
M.
View 6 Replies
View Related
May 13, 2015
I am creating a report in SSRS which has the following criteria:
- Row 1 (parent) is 'Product'
- Row 2 (child) is 'Feed'
- Columns are date. I have 5 dates showing at any one time across the top. The date field is set up as a parameter so depending on the date the user selects, the report will show that date on the end column and then the 4 days prior to that in the other columns.
- Data is the number of records.
I have a sub total on the Product and the report is collapsed on Product as default.
What i'm stuck on is trying to insert a column at the very end that will show the variance between the last two dates. So the difference between the date the user selected (@date parameter) and the day before that.
View 6 Replies
View Related
