MS SQL Server :: Matrix And Column And Row Based Averages Without Using Avg()

Averages In Matrix

Aug 18, 2006

I am new to Reporting Services on SQL Server 2005 and I need help. I have a report that I have create below using matrices.

Qtr 1

1
2
3
4
5
6
7
8
9
10
11
12
13
Total

2004
80
60
70
95
70
85
60
90
110
55
90
90
50
1005

2005
5

5
10
20
5

5
5
5
5

10
75

The columns represent each week in a quarter and the row represents the year the week is in and the details is the number of new accounts created during that particular week of the year. I have couple of questions.

How do I get and average for the number of account created per week per quarter?

How do I create a percentage of the change of accounts per week per year?

Qtr 1

1
2
3
4
5
6
7
8
9
10
11
12
13
Total
AVG

2004
80
60
70
95
70
85
60
90
110
55
90
90
50
1005
77.31

2005
5
0
5
10
20
5
0
5
5
5
5
0
10
75
5.77

-93.8%
-100.0%
-92.9%
-89.5%

-71.4%
-94.1%
-100.0%
-94.4%
-95.5%
-90.9%
-94.4%
-100.0%
-80.0%

Thanks in Advance.

View 10 Replies View Related

Setting Parameter Value Based On Matrix Column

Apr 10, 2007

Hello all.

I am on the verge of being able to do exactly what I want, but just can't seem to find the right combination of things to do it. I'm sure all of you wonderful folks will be able to point it out to me immediately, but I've been looking at it too long or something....

I have a record of individual sales with the state, and quarter of the sale.

sale_id state quarter
001 NY 2005Q1
003 WI 2006Q2
etc.

I create a report with a matrix to show count(sale_id) with Quarter as the column group and State as the row group. This works fine.

Now what I want to do is to get percentages based on quarterly sales. In other words, what percent of sales for 2005Q1 in NY vs. all sales in 2005Q1. So I create a second dataset (called total) with an SQL query like so:

SELECT count(sale_id)
FROM data_table
WHERE quarter = @QueryQuarter

Now, back in the matrix I want to use the column that we're in (2005Q1, 2005Q2, etc.) as the value that is passed to this query.

This is a simple concept, but I can't seem to figure out the correct call to pass the column group to the query as the parameter.

Thank you for any pointers you might be able to give. As I said, I'm right on the verge and just can't quite get it.

cmk

View 3 Replies View Related

Matrix Report:Adding Average Column/Sorting Based On Last Month/Conditional Formating

Aug 2, 2007

I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like

Sale Profit
Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007
A 100 200 300 20 25 15
B 200 250 50 30 45 19

how can i do following 3 things

1)Add Total column for Sale and Average column for Profit
2)Sort report by lastMonth of Sale (here 7/1/2007) High to low
3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red

thanks

View 1 Replies View Related

Reporting Services :: SSRS Matrix - Add A Column In A Matrix With A Variance

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

Column Based On Other Column (short Name Based On Name), When To Do The Transformation?

Oct 18, 2007

Hi!

I am designing a dimension table which will include a short name column based on the (full) name column. For example say Product dimension where I will have ProductName and ProductShortName. ProductShortName will be the first 6 characters of ProductName. I could populate ProductShortName using:

Substring in the select when I select from the original system, e.g. SUBSTR(PRODUCT_NAME, 1, 6) AS ProductShortName

Create a derived column in the SSIS flow which does the same thing

Create the ProductShortName column as a computed column which uses substring on ProductName

Create a trigger that populates ProductShortName based on ProductName when a row is inserted or updated

Create a named calculation in the table in the Analysis Services project's data source view

Create a named query in the Analysis Services project's data source view

I usually use 1, and 5 or 6 would only be used if I only will create reports against the cubes. 3 seems easiest to maintain, so I am thinking about using that one, but maybe it is slow for the data flow as I imagine it must be something like using 4, or when is the column "created" at runtime, i.e. when the table is queried?
Which approach(es) do or would you use? Pros and cons?

Thanks!

View 3 Replies View Related

Creating A Chart Based On Data In A Matrix

Jun 27, 2007

I am using a matrix to display current and historical financial data. To do this, I am using 5 different datasets. Each dataset contains 1 row of data for a specific year for a specific account.

Now that problem I am having is displaying this data in a chart. When I create a new chart, it asks which dataset I would like to use. I do not want to use any specific dataset, I want to get chart's data from the matrix. Is this possible?

View 8 Replies View Related

SLA Based Production Support DBA Needs Project Matrix Samples

Mar 20, 2008

Hi all

I am working as Production DBA (SLA based),I want Project matrix samples

Could you some body help me by sending Project matrix.

Any advice or Sample appreciated

View 3 Replies View Related

Integration Services :: Add New Column Based On Existing Column Using Derived Column Task?

Jul 28, 2015

I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".

Code:

A
B
C
D
E
F
G
H

If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".

View 4 Replies View Related

Setting Page Break In Matrix Report Based On RowNumer

Mar 17, 2008

Hi,

Do some one has idea how we can create pagebreak in matrix based on RowNumber.Say I have 40 rows in matrix(I don't have group on which I can create a group by setting PAGE BREAK AT THE END)

=ROWNUMBER(NOTHING) MOD 10 = 0 ,TRUE,FALSE

I don't have any distinct value on which I can create Group like

= IIF(Parameters!PageBreak.Value,Fields!CategoryID.Value,1) and setting Page Break At The End

-Thanks,
Digs

View 1 Replies View Related

SQL Averages

Jun 18, 2008

Is there anyway to find an average date value in SQL? it may sound like a strange question. Im on the bench as to whether it is possible

View 15 Replies View Related

Calculating Weekly Averages...

Nov 9, 2007

Hi. I want to be able to calculate weekly averages of our report data. Here is a sample from our EmplyeeTrends table:

EmployeeID ReportDate TotalCases
2 1/1/2007 77
2 1/2/2007 63
3 1/1/2007 56All the report data is produced daily. However, my boss wants to be able to see weekly/monthly averages for each employee. Is this possible? Thanks so much for your help!

View 9 Replies View Related

Averages Of Time By Month

Dec 21, 2007

Hello all, I have the query below that is returning the difference in time between two scans, I now need to take that time and break it out by month and than break that out by shift and get an average for the 2 shifts for the month. Below is the query with some sample data. Your help is greatly appreciated. THANKS!!!

SELECT LoadID, DATEDIFF(MINUTE, mi, ma) AS DiffMinutes
FROM SELECT LoadID, MIN(ProcessDate) AS mi, MAX(ProcessDate) AS ma
FROM [Scan Detail]
WHERE (ScanSequence = 2 AND LoadSequence = 2) OR
(ScanSequence = 3 AND LoadSequence = 13)
GROUP BY LoadID) d

LoadIDDiffMinutes
80485
308755
226816
1609613
1477910
319006
364355
399487
218020
115616
339479
2838715
1960913
395079
288289
81946
3965310
84897
60017

View 7 Replies View Related

Running Sums Of Averages

Aug 3, 2007

Hi,

I have a table with a userid and duration field. I need to contrast and individual against a group of users from the table. I can get the users cumulative hours by date in the select clause.

I am having a problem producing the group's sum of averages in the main query.I have tried it many ways and just cant get it to work. I need to be able to display something like this below:

Date GV IV
1/1/07 .45 .37
1/2/07 .56 .45
1/3/07 .68 .59
--
So Far I have this:

SELECT

D1.Date,

GV=ROUND(CAST(SUM(D2.Duration) AS FLOAT) / 3600,2),
IV=(SELECT ROUND(CAST(SUM(DurationAll)AS FLOAT) / 3600,2)

FROM IR_UserDaily WHERE UserID=@UserID AND Date<=D1.Date)

FROM

( SELECT Date,Duration=AVG(DurationAll)

FROM IR_UserDaily

WHERE

UserID IN(SELECT FilterID FROM IR_Filter WHERE ReportID=@RID)AND(Date BETWEEN @LowDate and @HighDate)

GROUP BY Date
) AS D1
INNER JOIN

( SELECT Date,Duration=AVG(DurationAll)

FROM IR_UserDaily

WHERE

UserID IN(SELECT FilterID FROM IR_Filter WHERE ReportID=@RID)AND(Date BETWEEN @LowDate and @HighDate)

GROUP BY Date

) AS D2
ON D2.Date < = D1.Date
WHERE (D1.Date BETWEEN @LowDate and @HighDate) AND (D2.Date BETWEEN @LowDate and @HighDate)
GROUP BY D1.Date,D1.Duration
ORDER BY D1.Date

The problem is that the avg function is returning the avg for all values up to the inner join condition, I think.
Can someone help me here I know it should be simple.

View 1 Replies View Related

Calculating Averages On Integer Fields

Nov 22, 2000

Yesterday we ran into a very surprising problem: a colleague had developed a view which calculated a numbero of domain functions on an integer field (sum, count, standard deviation, average).

We were amazed to find that the view always returned an integer value for the average (not for the std dev, though). This was obviously wrong. We even tried changing the function to Sum(fld)/Count(fld) where "fld" was the fieldname, but we still got this strange curious result.

I got around the problem using the CONVERT function to change the integer value into a floating (e.g. Avg(CONVERT(FLOAT(5),fld)), but this is so strange that I'm sure we'll have the same problem again and again.

Now, can anyone tell me why the SQL engine is so incredibly stupid to presume that the average of a series of integer values is necessarily an integer???

View 3 Replies View Related

Master Data Services :: Filter Column Based On Other Column In Same Entity

May 12, 2015

Using MDS 2012: I have an entity "XYZ_Entity". In "XYZ_Entity" entity I have 2 domain based Columns "DealerGroup" and "Dealer".

While inserting information into "XYZ_Entity" entity user can select the required dealer group from domain base Dealer Group values. Now for selecting Dealer he wants the dealers to be filter based on selected dealer group and he can select from the filtered list. reason to do that is he don't want to go through thousands of dealers and select an incorrect one.

Is it possible, if yes then how?

View 2 Replies View Related

Query To Find A Value In Column B Based On An Aggregate Function On Column A?

Jul 20, 2005

Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy

View 5 Replies View Related

Add Matrix Column

Oct 17, 2007

hello there,

how can i add column datas together or merge two columns together.

cheers
zolf

View 3 Replies View Related

How To Add A Column In The End Of Each Matrix Row?

Feb 25, 2008

Hi,

I've a report containing a matrix. I want a column on end of each row in matrix, which shows me sum of that row. Is it possible in matrix? if yes, how can i achieve it?

View 4 Replies View Related

Sum Of A Column In Matrix

Jun 24, 2006

hi reporters!

i m using matrix in my project and i want to calculate the
(cell_value/sum_of_column). for data cell values we have to use sum or
another aggregate function for subtotaling, so for the cell value i m
wirting sum(column) / A. how to write A that points to sum of all cells
on the column.

x_column
y_column
z_column

sum(y_column)/A

A?

View 13 Replies View Related

SQL Server 2012 :: Group Column Based On Another Column

Jul 11, 2014

I have Table Like this

t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail

[Code] ....

View 1 Replies View Related

Derive A Column Based On Other Derived Column In A Table .. Is It Possible ?

Mar 25, 2008

Table structure as follows

Employee

Empno empname salary
commission

I want to have an other employee table named employee_modified

Empno empname salary
commission derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx) and so on derive other
columns based on the earlier derived columns)

Is that possible to do it.. or am I doing something wrong.

something like

Select empno , empname , salary , commission,

(salary + commission) as derived_colum1 ,

(derived_colum1 + xxxxx) as derived_colum2 ,

(derived_colum2 + xxxxx) as derived_colum3

into employee_modified from employee

View 3 Replies View Related

Transact SQL :: Returning A Column Value Based Upon The Precedence Value Of Another Column?

Nov 4, 2015

#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):

View 6 Replies View Related

Power Pivot :: 28 Day Rolling Averages With Discrete Time Attributes

Jul 14, 2015

I've been able to find plenty of info online in relation to calculating moving averages for a given value. I am having a specific problem however.

So because I need to see the average on a daily basis, my forumla (below) works fine:

However, my Calendar dimension (TIME) also has a discreet, non-continuous attribute called DateMonth (which looks like 1-Jan, 2-Jan etc.) which has no reference to the year.

I was hoping this would allow me then to take my year attribute and compare the 28 Day Average for the 1st of Jan across multiple years.

So you could have a line chart where the X axis is 1-Jan to 31-Dec (with no reference to the year) and the series category is by year, so a line for each year.

But when I replace DATE with DateMonth in my pivot, the calc gives some strange numbers.

Rolling28DayAvg :=
CALCULATE (
    [Revenue],
    DATESINPERIOD (
        TIME[DATE],
        LASTDATE ( TIME[DATE] ),
        -28,
        DAY
    )
)
    / 28

View 15 Replies View Related

Subtracting One Matrix Column From The Other

May 19, 2008

I have the following fields in my report

Year
2008 2007 Net Change
Val1 10 6
Val2 7 5
Val3 15 7

The Matrix Column is grouped by Year.

I need to calculate Net Change as Year 1 - Year 2 for each row.
How do I access the contents of the first and second column of the matrix ?

View 1 Replies View Related

Hide Column In Matrix

Oct 24, 2006

I have a matrix that has the following columns:Date Shift Equipment1 Equipment2 Equipment3 etcBased on a parameter, i want to show/hide the Shift column. If the Shift column is hidden, i want to move the equipment columns over so there is no gap in the columns.I know you can do this in a table by hidding the whole column, but how do you do this in a matrix? when i try to hide the column the visibility option is not there, and when i hide the field, it leaves a gap in the report.Thanks for your help.

View 2 Replies View Related

How To Add Column Header In A Matrix

Jan 25, 2007

Hi,

is there a way to add column header in a Matrix?

Thanks,

Igor

View 6 Replies View Related

Matrix Column Aggregations

Jan 17, 2007

Quick question for you pro's...

Within a report matrix, I want to be able to have different aggregations for totals. One column would be a Sum of the data in that column, the next column would be an Average of the data. Is this possible, or is this not supported in SSR2005?

Thanks in advance for your help,

Clint

View 4 Replies View Related

Percentage Column In Matrix

Jan 26, 2007

Hi,
I would like to generate following table with reporting service matrix.
But if I use the expression : percentage coulmn=sum(field!qty.value) / sum(field!qty.value, "region_group")
It will become a percentage of a row total, instead of a group total (store group). Please see the second table.
Any ideas?

Thank you!

(Correct!)
store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC 10 10% 20 20% 70 70% 100 100% 25 50% 25 50% 50 100% EURP C2 CHINA

(Wrong) store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC 10 6% 20 13% 70 46% 100 25 16% 25 16% 50 EURP C2 CHINA

View 1 Replies View Related

Column Headers For Matrix

Sep 26, 2007

I am developing a matrix report in SRS. In columns group there are several values. When report runs they apper in any order based on the first record in row group. I want colums to apeear in specific order all the time. For example the column sequence in one out put is Follwup 1, Initial , Followup 2. I want to column header to be in order of Initial, Folloup 1, Followup 2.
Can someone help?

View 3 Replies View Related

Column Names On Matrix

Apr 30, 2007

Hi,

Is there any way of having Column names in a matrix report. Actually it is a RowGroup. I want to display the name of the row group at the top, so that the user understands what is the data beneath.

regards

Josh

View 3 Replies View Related

Matrix Column Visibility

Mar 29, 2006

Hello,

I have one column group and three static columns. I'd like to control static column visibility property depending on a field value. I.e. Hidden: =Fields!GroupId<>5.

But I'm not able to find visibility property in column object. Neither in properties explorer nor RDL documentation. I'm only able to set hidden property in textbox objects.

I have RS 2005.

Thank you for any idea.

Jirka Nouza

View 4 Replies View Related

Matrix Column Grouping

Sep 15, 2006

Hi,

I have a requirement from my users to be able to drill down for a single column in a matrix. I've been able to implement drilldown for all of the measures (all columns grouped at the same time) in my matrix but they now want to be able to group different columns independently of each other. In other words they want to be able to group the data in different columns by different things.

An example might make my question clearer, the report would need to look like this:

Client Name
Sales -
Costs +
Revenue -

Client A +
Europe +
Middle East +
Asia Pacific +
$12,000,000
Products +
Investments +

$12,000,000
$8,000,000
$4,000,000
$10,000,000
$22,000,000

Client B +
$77,000,000
$16,000,000
$9,000,000
$22,000,000
$8,000,000
$32,000,000

As you an see, the Sales measure has been expanded so it's grouped by region so the sales figures can be seen for all clients and the 3 regions. The revenue column has also been expanded/grouped so that you can see revenue figures for products and investments for all clients. Costs is not expanded but it could be by clicking on the '+' which would group the data in that column by something else. They have asked for more than 1 level of drilldown, so clicking on "Europe" above would allow another level for the sales measure which might be country. There will also be regular measure columns that are not drilldown/groupable columns.

Is it possible to implement this with SSRS 2005?

Thanks,

Lachlan

View 4 Replies View Related