SSRS 2005 - How To Have Multiple Subtotals In Matrix

Jan 15, 2008

Is there any way to get multiple subtotals in a matrix? For example, one that does a count and the 2nd that does an averages as per the desired result below ...





Code Block

A B C
A 1 2 3
B 2 3 4
C 3 4 5
Total 6 9 12
Avg 2 3 4

View 8 Replies


ADVERTISEMENT

Reporting Services :: Add Subtotals To SSRS Matrix Report?

Oct 7, 2015

Seems simple.  People working in departments with different types of hours (regular, overtime, vacation... thus the grid which pivots hours in rows to the appropriate columns).  I want to subtotal by department, but whenever I try to add totals after to that group, I just get a grand total.  No subtotals.  Why?

View 8 Replies View Related

Multiple Subtotals In A Matrix

May 8, 2007

I would like to set up a report which would look simular to



Mon Tues Wed Thurs Fri Total Month to date total

salesguy1 10 5 11 10 9 45 120

SalesGuy2 9 1 15 0 0 25 89



I have created a matrix which shows the data upto total.

Is there a way to include the Month to date total after total?



Thanks

View 2 Replies View Related

Matrix Subtotals

Aug 31, 2006

I have this:

2003 2004 2005 Total
cars apples cars apples cars apples
Tom 3 1 4 2 5 1 16
Sally 0 2 4 1 7 6 20



And I need the last column (the group subtotals) to be like this:



Totals
cars apples
12 4
11 9

So basically, the question is, how do I get the second column-group (cars/apples) subtotals to appear in the last column? As seen, the the subtotal for the first column-group (year) is meaningless.

View 4 Replies View Related

Need Some Help With Subtotals In A Matrix

May 16, 2007

Hello all,

This is all being done under SQL2000 and VS2003

I have a matrix report which is showing user information. The Rows are displaying numbers for each user, and the columns show the user info in weekly increments. I have 7 fields of info for each user. My stored procedure already is set up to give me the correct numbers. I dont need to SUM them or anything. Although in the report designer it forced me to SUM them since it was part of an aggregate. This still worked for me anyhow because it was Summing a single value.

However, at the end of the report i want to display totals for all the users combined, per week. So right now the report is showing 21 weeks, so at the end of the report i should have 21 sets of totals.

I right clicked on the users name column and selected subtotal. This gave me some of what i want. But some of the numbers are not correct. Some of the numbers should not just be a simple SUM of the column. Some of the values should be averages etc. I know how to calculate those values myself (its very simple math) but i dont know how to do it using this setup in the report designer. So in the matrix, for each week, how can i calculate the totals for all the users combined and specify the formula used to get the totals for each field?

thanks

View 1 Replies View Related

Custom Subtotals In A Matrix

Apr 23, 2007


FY 2006
FY 2007
Total

1ST QTR
2ND QTR
3RD QTR
4TH QTR
Total
1ST QTR
Total

Customer1
Count1
290
271
233
207
1001
200
200
1201

Count2
111
110
123
118
462
113
113
575

Customer2
Count1





12
12
12

Count2





9
9
9

Customer3
Count1
616
540
513
526
2195
531
531
2726

Count2
362
299
324
368
1353
347
347
1700

Customer4
Count1
7
12
15
22
56
26
26
82

Count2
3
1
7
9
20
9
9
29

Customer5
Count1

4
9
2
15
3
3
18

Count2

1
1
1
3
3
3
6

Customer6
Count1
1
0


1


1

Count2
0
0


0


0



I have the matrix above that displays the subtotals per customer(group_row), per quarter(group_column). I added a subtotal to the top group_column (year) so I can get grand total. I need to modify grand total formula to display variance instead.



I don't need to modify the subtotal formula per Customer/quarter , only per cutomer/Year. Is it possible?

View 12 Replies View Related

0's Om Subtotals But Not In Matrix Columns, How?

Mar 5, 2008

Hi
I have a matrix report. It expands to the right when the user chooses the amounts of months he/she wishes to see. Then it is populated with revenues for each month and deportment. On the matrix report I have put in a couple of Subtotals. Some of the totals are empty due to a month in a department with no revenue.

I wish to see a 0 on the subtotal column but I don€™t want to see a 0 on the month column. If I use
=IIF( Fields!Revenue.Value is Nothing, "0", Fields!Revenue.Value)
on the month columns I will see 0 all over the place. But I only want a 0 on the subtotal column. Can I do this?

Thanks in advanced
Kind regards
//Javier

View 5 Replies View Related

Using Matrix Subtotals In Expression

May 8, 2008

Hi all,

I'm wondering if its possible to use the subtotal for one group in a matrix in an expression for another group in the matrix.

Im getting the total number of units someone holds in one column, and need to show the percentage of the total units in another column.

An example of what im doing is below... argh images dont seem to be working when you post then! sorry





the first group is Date - This will show all there quantities anytime a trade occurs.

the second and third groups are Quantity (of units) and % of fund.

Quantity is a running value of all the units the account holder has. The percentage needs to be that number / subtotal of all units on that date. So the expression needs to be something like:


=runningvalue(Fields!Quantity.Value, sum, "matrix1_Account_Reference") / --subtotal of date group-- * 100

any ideas?

Thanks

View 1 Replies View Related

Conditional Matrix Subtotals

Apr 9, 2007

Let's say I have raw data that looks something like this

Fruit_name status count
apples Fresh 5
apples rotten 3
pears Fresh 3
pears rotten 2

and I was to matrix it and group on fruit_name and add a subtotal to count. In that subtotal, without changing what was displayed in the details number, could I conditionaly only show a sum of fresh fruit? Example below


Apples 5
Apples 3
-------------------
Total 5

Pears 3
Pears 2
----------------------
Total 3

in a nonmatrix situation I'd use something like

sum(iif(status="Fresh",count,0))

Thanks for the help.

View 3 Replies View Related

Horizontal Totals Of Subtotals In A Matrix

Sep 19, 2007

I'll admit I am fairly new to the reporting services, but I managed to figure most things out so far except this one. I have a matrix report where I have column groupings of:

Sales person
Region

Basically, the report shows sales by sales person and region, and I added subtotals to each so each salesman/region combination gets subtotals, as does each salesman entry. Now, the one piece missing is the 'total of the subtotals' so to speak. So if I have something like:

Salesman Region Sales Jan Sales Feb Sales Mar
001 A $100 $175 $50

B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200
002 A $100 $175 $50

B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200
Grand Total $800 $950 $400

What I want to do is sum up the totals and add another column like so:

Salesman Region Sales Jan Sales Feb Sales Mar
001 A $100 $175 $50

B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200 $1075
002 A $100 $175 $50

B $100 $200 $100
C $200 $100 $50
Total $400 $475 $200 $1075
Grand Total $800 $950 $400 $2150

And that is where I am stuck. In Report Designer, with the existing matrix report as above, how do I get these horizontal totals of the subtotals?

View 5 Replies View Related

Matrix Report And Customized Subtotals - Can It Be Done?

May 31, 2007



I need a sales report that would display weekly amounts either sold or forecasted in matrix (pivot) form.

The data used for the report is like following (INV is sold and FC is forecasted):












rtype
region

week

wgt

INV
EU
1
150

INV
US
2
200

FC
US
2
400

FC
US
3
1000

FC
EU
2
100



I want the report to show data like this:












WEEK 1

WEEK 2

WEEK 3

TOTAL


INV

INV

FC

FC

INV

FC

EU
150

100

150
100

US

200
400
1000
200
1400



So I put region as rows, week and rtype as columns, and wgt as data field. Everything works fine except that there will be no grand totals for INV/FC. What I get is:








WEEK 1
WEEK 2
WEEK 3
TOTAL

INV
INV
FC
FC


EU
150

100

250

US

200
400
1000
1600



How can I get my totals? I know I could tailor my data to get INV/FC values into different columns to show as data fields in matrix report. But in this case, every week would always display 2 columns, which is certainly not what I want.

I also know I could add another matrix report to create a simulated total columns, but I wasn't able to "join" these 2 reports properly, there is always some space between them and the report looks unprofessionally. So I am looking for a way to do that with a single matrix.



Second question: how can I paint entire columns into different colors, so that INV is always green and FC is always blue? I tried customizing background color, but it only paints cells with values inside, leaving empty cells white. Is it possible to have the INV columns green, entirely?



Thank you in advance,

Wapper



View 3 Replies View Related

Problem With Subtotals Of A Matrix (Blank, Pdf, Empty Column)

Apr 16, 2008

Because i get no answers i will try it again:

Hi,

i have the following problem:
I have a matrix with a right subtotal column and this matrix was in a list (because in the end i will have more than one matrix). The list fits perfectly the matrix in design mode. But if i render the report in the viewer or to pdf, an additional blank area (like a blank copy of the subtotal column) was inserted after the right subtotal column of the matrix and increases the list too. You can see this easy by set the backgroundcolor of a list to a color. Without the subtotal column the list fits perfect after rendering. The problem is that this additional blank "column" creates empty pages in .pdf rendering, if the width of the matrix is near the page width. The same behaviour happens if i put the matrix with subtotal in a rectangle.
I must use a list in the end because the the final report contains some matrixes and a subreport.
So is this a bug?
Someone must have this problem too?

Thanks for any help.

View 5 Replies View Related

Subtotal In Matrix SSRS 2005

Jun 2, 2008

hi guys,

I have added a subtotal on a column, but the report shows the total at the end of the report, anyone knows why this is happening?

The structure i am getting is as follows:
C1 C2 C3 C4
A1 B1 Y 0
N 5
B2 Y 3
N 2
A2 B3 Y 0
N 5
B4 Y 2
N 3
Total 20

What I want is as follows:
C1 C2 C3 C4
A1 B1 Y 0
N 5
B2 Y 3
N 2
Total Y 3
N 7
A2 B3 Y 0
N 5
B4 Y 2
N 3
Total Y 2
N 8

The formatting is lost , i wonder if you can understand the structure.

Any help will be appreciated.

View 3 Replies View Related

SSRS 2005 Matrix Subtotal Issue

Jul 31, 2007

Now, I know this has to be REALLY easy but I can't seem to figure this out...I'm new to SSRS and my book order proably just came in today at home. I have a matrix with one header level and one row level. Basically the report is an Aging of dollars across time (columns) and financial class (rows). Nothing fancy here.

I've clicked on the header for the row and selected 'subtotal' which adds a 'Total' row and done the same for the column header which adds a Total for the column. But when I run the report the subtotals only include the first entry in each row (row subtotal) or column (column subtotal). Why won't it add all of the cells together?

Thanks in advance...






View 6 Replies View Related

Matrix Subtotal Label Issue (SSRS 2005)

Aug 2, 2007



Hello,



We have a matrix that includes two row groups with subtotals for each group, like the following:



<table width="80%">

<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>

<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>

<tr><td>&nbsp;</td><td>&nbsp;</td><td>4</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td>&nbsp;</td><td>Rm 50</td><td>7</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td>&nbsp;</td><td>17</td></tr>

</table>



The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,



RLG

View 2 Replies View Related

SSRS: Sum Of A Column With Subtotals Based On A An ID Group

Jul 18, 2007


Hello,
I'm just starting to analyze with SQL 2005 reporting services and trying to build a test report using the report wizard.

This is how i've got it in SQL reports now:

Opportunity ID Product Name Cost
--------------------- --------------------- ----------------
1 Orange $1
1 Apple $2
1 Grapes $4
2 Orange $1
2 Apple $2
3 Orange $1


Now, what if I wanted to group them to provide subtotals like:


Order Product Name Cost
--------------------- --------------------- ----------------
1 Orange $1
Apple $2
Grapes $4
-------------------------------
SubTotal $7

2 Orange $1
Apple $2
-----------------------------
SubTotal $3

3 Orange $1
------------------------------
SubTotal $1

The problem that i'm having right now is on the layout designer somehow programming the subtotals based on the IDs.
Am i thinking in the right direction that I should be using the layout designer to do this? Or should this be done programmatically?

Thanks.
Mike

View 6 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

Mutiple Column Group Subtotals, Custom Subtotals Expressions/Calculations

Jul 9, 2007




































Some DB Field ID
X
2007
2008

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
A
X-A
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
B
X- A -B

1
500
10
20
0
0
0
0
0
0
0
20
50
0
100
400
25
10
10
0
0
5
0
25
15
10
10
20
130
270

2
750
10
10
10
20
20
10
10
20
10
10
10
10
150
600
20
20
20
20
30
30
10
10
10
30
30
30
260
340

3
600

































All,



I am trying to achieve something as above. Basically, the Months subtotals are represented by A and B. Then (X-A) and (X-A-B) are also the subtotals at the same group level as A and B but don't simply display the total for respective years 2007 and 2008, instead those are remaning totals from X. In order to calculate the remaining totals however, one need to consider the subtotal in previous group. For example, for 2007 its X-A, but for 2008 its X-A-B. I would like to know if this can be achieved using Matrix control. If so, what would be the steps?



Thanks.



View 5 Replies View Related

SSRS 2005 Download To Excel : Matrix Column Moved To The Right (moving, Shifting, Indent)

Jan 1, 2008

Hi,

I encounter a bug while exporting to excel a matrix.
one cell is shifted to right and so i have wrong numbers and empty cell in the middle of the matrix.

instead of :

























ABS
Recent College Graduate (C)

Male (M)
Total
Female (F)
Male (M)
Total


Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %
Headcount Col %

232
34
54
56
56
5
24
56
56
56
34
23
43
54
56

I get:
























ABS
Recent College Graduate (C)

Male (M)
Total
Female (F)
Male (M)
Total


Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %
Headcount Col %

Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %
Headcount Col %
Headcount
Headcount Row %

232
34
54
56
56
5
24
56
56
56
34
23
43
54
56




does anyone familiar with a solution to this issue?
Thanks. Yuval.

View 1 Replies View Related

Reporting Services :: Calculating Average Amount Of Working Days In A Month - SSRS 2005 Matrix

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

Subtotals In Table (group Footer) Using Report Items 2005

Aug 31, 2007

How can I calculate a subtotal for a Report Item? I have a textbox(lets call it "PlusMinus") in the detail section of my table, which is a calculated textbox of two others (lets call them "Budget" and "Spent"). So, PlusMinus = (Budget - Spent). What I would like to do is get a subtotal for PlusMinus. I have tried several ways, using Sum() or RunningValue, even tried to write code, but I can't seem to get it right. Any ideas??

Thanks in advance!

View 3 Replies View Related

Can SSRS 2005 Handle Stored Procedures Or SQL Subqueries That Rreturn Rowsets Based On Multiple SQL Updates?

Nov 8, 2006

Hello,

I have a stored procedure that creates a temporary table, and then populates it using an INSERT and then a series of UPDATE statements. The procedure then returns the temporary table which will contain data in all of its columns.

When I call this procedure from SSRS 2005, the rowset returned contains data in only those columns that are populated by the INSERT statement. The columns that are set via the UPDATE statements are all empty. I read (in the Hitchhikers Guide to Reporting Services) that SSRS will only process the first rowset in a stored procedure that generates multiple rowsets. Is this true? Is this why SSRS does not retrieve data for the columns that are populated by the UPDATE statements?

Here is the stored procedure:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- File: sp_GetProgramsWatchedByDateRange.sql
-- Desc: Returns EDP program and related channel (i.e., provider) information from the IPTV Data warehouse.
-- Note that some of that data used by this procedure are obtained from the RMS_EPG database
-- which is created by an application (loadEPG) that loads the EPG data from a GLF format XML file.
-- Auth: H Hunsaker
-- Date: 11/07/2006

-- Example invocation
-- EXEC dbo.sp_GetProgramsWatchedByDateRange ...

-- Arguments/Parameters:

-- Parameter Name Type Description
-- 3. StartDate datetime First date of reporting period
-- 4. EndDate datetime Last date of reporting period
-- TerseMode bit Return all columns? (1 = no, 0 = yes)
-- 5. AsXML bit Resultset format (0 = standard, 1 = XML)
-- 6. Debug bit Debug mode (0 = off, 1 = on). Currently disabled

IF OBJECT_ID (N'dbo.sp_GetProgramsWatchedByDateRange') IS NOT NULL
DROP PROCEDURE dbo.sp_GetProgramsWatchedByDateRange
GO

CREATE PROCEDURE dbo.sp_GetProgramsWatchedByDateRange
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@TerseMode bit = 0,
@AsXML bit = 0,
@Debug bit = 0
AS
-- Notes: Much of the program content (roles, flags, etc.) that we want is not stored in the IPTV data warehouse.
-- So I am going to the RMS_EPG database to obtain that information.
-- We will have to ensure that the 2 databases are generated at the same or a matching time
-- in order to to ensure that all programID values in the data warehouse can be located in the RMS_EPG database.

-- Debug code for testing
-- DECLARE @StartDate datetime
-- DECLARE @EndDate datetime
-- DECLARE @TerseMode bit

--SET @StartDate = NULL
--SET @EndDate = NULL
--SET @TerseMode = 1

SET NOCOUNT ON

CREATE TABLE #programWatched
(
--IPTV device ID
tdeviceId uniqueidentifier NULL,
taccountId uniqueidentifier NULL,

-- Basic program information
tprogram int NULL, -- programID from EPG XML, needed to access program data in the RMS_EPG db.
tprogramId uniqueidentifier NULL, -- programID generated by IPTV
tprogramTitle varchar(150) NULL,
tprogramEpisodeTitle varchar(100) NULL,
tprogramDescription varchar(500) NULL,

toriginDateTime datetime NULL,
tduration bigint NULL,
tprogramType nvarchar(100) NULL,
tchannelCallName nvarchar(20) NULL,

--Rating
programMPAARating varchar(50) NULL,
programVCHIPRating varchar(50) NULL,
programMPAARatingVal smallint NULL,
programVChipRatingVal smallint NULL,

-- Categories
programGenre varchar(50) NULL,
programCategory1 varchar(50) NULL,
programCategory2 varchar(50) NULL,
programCategory3 varchar(50) NULL,
programCategory4 varchar(50) NULL,

-- Roles
programActor1FirstName varchar(50) NULL,
programActor1LastName varchar(50) NULL,
programActor1 varchar(100) NULL,

programActor2FirstName varchar(50) NULL,
programActor2LastName varchar(50) NULL,
programActor2 varchar(100) NULL,

programActor3FirstName varchar(50) NULL,
programActor3LastName varchar(50) NULL,
programActor3 varchar(100) NULL,

programActor4FirstName varchar(50) NULL,
programActor4LastName varchar(50) NULL,
programActor4 varchar(100) NULL,

programActor5FirstName varchar(50) NULL,
programActor5LastName varchar(50) NULL,
programActor5 varchar(100) NULL,

programActor6FirstName varchar(50) NULL,
programActor6LastName varchar(50) NULL,
programActor6 varchar(100) NULL,

programActor7FirstName varchar(50) NULL,
programActor7LastName varchar(50) NULL,
programActor7 varchar(100) NULL,

programActor8FirstName varchar(50) NULL,
programActor8LastName varchar(50) NULL,
programActor8 varchar(100) NULL,

programDirectorFirstName varchar(50) NULL,
programDirectorLastName varchar(50) NULL,
programDirector varchar(100) NULL,

programWriterFirstName varchar(50) NULL,
programWriterLastName varchar(50) NULL,
programWriter varchar(100) NULL,

programProducerFirstName varchar(50) NULL,
programProducerLastName varchar(50) NULL,
programProducer varchar(100) NULL,

-- Flags
ClosedCaption bit NULL,
InStereo bit NULL,
Repeats bit NULL,
New bit NULL,
Live bit NULL,
Taped bit NULL,
Subtitled bit NULL,
SAP bit NULL,
ThreeD bit NULL,
Letterbox bit NULL,
HDTV bit NULL,
Dolby bit NULL,
DVS bit NULL,

FlagOrdinalValue smallint NULL,

-- Channel
tchannelId int NULL,

callLetters varchar(20) NULL,
displayName varchar(50) NULL,
type varchar(50) NULL,
networkAffiliation varchar(50) NULL
)

-- I store the program watching data in a temp table because
-- data from the VIL and the Sandbox that were used to test this procedure were either incomplete or invalid.
-- Use of a temp table with a series of updates allow me more control over the result set.

IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL
INSERT INTO #programWatched (
tdeviceId,
tprogramId,
--tprogramTitle,
--tprogramEpisodeTitle,
toriginDateTime,
tduration,
--tprogramType,
--tchannelCallName,

ClosedCaption,
InStereo,
Repeats,
New,
Live,
Taped,
Subtitled,
SAP,
ThreeD,
Letterbox,
HDTV,
Dolby,
DVS
)
SELECT pw.DeviceID,
pw.programID,
--epg.program,
--epg.programTitle,
--epg.programEpisodeTitle,
pw.originTime AS 'When Watched',
pw.Duration AS 'Duration Seconds',
--epg.programType,
--epg.channelCallName,

0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.

FROM DW_EventClientProgramWatched pw
WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system
AND originTime BETWEEN @StartDate AND @EndDate
ELSE
INSERT INTO #programWatched (
tdeviceId,
tprogramId,
--tprogramTitle,
--tprogramEpisodeTitle,
toriginDateTime,
tduration,
--tprogramType,
--tchannelCallName,

ClosedCaption,
InStereo,
Repeats,
New,
Live,
Taped,
Subtitled,
SAP,
ThreeD,
Letterbox,
HDTV,
Dolby,
DVS
)
SELECT pw.DeviceID,
pw.programID,
--epg.program,
--epg.programTitle,
--epg.programEpisodeTitle,
pw.originTime AS 'When Watched',
pw.Duration AS 'Duration Seconds',
--epg.programType,
--epg.channelCallName,

0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.

FROM DW_EventClientProgramWatched pw
WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system

-- AccountId/SubscriberId
UPDATE #programWatched
SET taccountId = (SELECT accountId
FROM DW_BRDB_bm_device d
WHERE d.deviceId = tdeviceId)

-- program (this is the integer program ID stored in the EPG XML, not to be confused with the IPTV programId)
-- a program can occur on multiple channels, so we filter channels where scheduleTime <= originTime <= scheculeTime + durationSecs
UPDATE #programWatched
SET tchannelCallName = (SELECT TOP 1 channelCallName
FROM DW_EPG EPG
WHERE tprogramId = EPG.programId AND toriginDateTime BETWEEN scheduleTime AND DATEADD(s, epg.durationSecs, epg.scheduleTime))

UPDATE #programWatched
SET tprogram = (SELECT TOP 1 program FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramTitle = (SELECT TOP 1 programTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramEpisodeTitle = (SELECT TOP 1 programEpisodeTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),
tprogramType = (SELECT TOP 1 programType FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName)

-- Rating (otained from programValues, can also be obtained from programFlags)
UPDATE #programWatched
SET programMPAARating = (SELECT TOP 1 programValue
FROM RMS_EPG..programValue pv
WHERE tprogram = pv.programID AND pv.programValueTypeId = 9)

UPDATE #programWatched
SET programMPAARatingVal = CASE programMPAARating
WHEN 'G' THEN 10
WHEN 'PG' THEN 25
WHEN 'PG-13' THEN 30
WHEN 'R' THEN 35
WHEN 'NC-17' THEN 50
WHEN 'NRAO' THEN 60
WHEN 'NR' THEN 0
ELSE 0
END

UPDATE #programWatched
SET programVChipRating = (SELECT TOP 1 programValue
FROM RMS_EPG..programValue pv
WHERE tprogram = pv.programID AND pv.programValueTypeId = 8)

UPDATE #programWatched
SET programVChipRatingVal = CASE programVChipRating
WHEN 'TV-Y' THEN 10
WHEN 'TV-Y7' THEN 20
WHEN 'TV-G' THEN 35
WHEN 'TV-PG' THEN 40
WHEN 'TV-14' THEN 45
WHEN 'TV-MA' THEN 60
ELSE 0
END

-- Genre
UPDATE #programWatched
SET programGenre = (SELECT TOP 1 programCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType psct ON psct.programSubCategoryTypeId = pc.programCategoryId
INNER JOIN RMS_EPG..programCategoryType pct ON pct.programCategoryTypeId = psct.programCategoryTypeId
WHERE tprogram = pc.programID)

-- Categories
UPDATE #programWatched
SET programCategory1 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID)

UPDATE #programWatched
SET programCategory2 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1))

UPDATE #programWatched
SET programCategory3 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2))

UPDATE #programWatched
SET programCategory4 = (SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory pc
INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId
WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2, programCategory3))

-- Roles
UPDATE #programWatched
SET programDirectorFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)

UPDATE #programWatched
SET programDirectorLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)

UPDATE #programWatched
SET programDirector = programDirectorLastName + ' , ' + programDirectorFirstName

UPDATE #programWatched
SET programWriterFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)

UPDATE #programWatched
SET programWriterLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)

UPDATE #programWatched
SET programWriter = programWriterLastName + ' , ' + programWriterFirstName

UPDATE #programWatched
SET programProducerFirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)

UPDATE #programWatched
SET programProducerLastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)

UPDATE #programWatched
SET programProducer = programProducerLastName + ' , ' + programProducerFirstName

UPDATE #programWatched
SET programActor1FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)
UPDATE #programWatched
SET programActor1LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)

UPDATE #programWatched
SET programActor1 = programActor1LastName + ' , ' + programActor1FirstName

UPDATE #programWatched
SET programActor2FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName))

UPDATE #programWatched
SET programActor2LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName))

UPDATE #programWatched
SET programActor2 = programActor2LastName + ' , ' + programActor2FirstName

UPDATE #programWatched
SET programActor3FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName))

UPDATE #programWatched
SET programActor3LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleLastName NOT IN (programActor2LastName) AND programRoleLastName NOT IN (programActor2LastName))

UPDATE #programWatched
SET programActor3 = programActor3LastName + ' , ' + programActor3FirstName

UPDATE #programWatched
SET programActor4FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))

UPDATE #programWatched
SET programActor4LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))

UPDATE #programWatched
SET programActor4 = programActor4LastName + ' , ' + programActor4FirstName

UPDATE #programWatched
SET programActor5FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))

UPDATE #programWatched
SET programActor5LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))


UPDATE #programWatched
SET programActor5 = programActor5LastName + ' , ' + programActor5FirstName

UPDATE #programWatched
SET programActor6FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))

UPDATE #programWatched
SET programActor6LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))

UPDATE #programWatched
SET programActor6 = programActor6LastName + ' , ' + programActor6FirstName

UPDATE #programWatched
SET programActor7FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))

UPDATE #programWatched
SET programActor7LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))

UPDATE #programWatched
SET programActor7 = programActor7LastName + ' , ' + programActor7FirstName

UPDATE #programWatched
SET programActor8FirstName = (SELECT TOP 1 programRoleFirstName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName)
AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))


UPDATE #programWatched
SET programActor8LastName = (SELECT TOP 1 programRoleLastName
FROM RMS_EPG..programRoleName prn
INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId
WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1
AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)
AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)
AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)
AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)
AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)
AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName)
AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))

UPDATE #programWatched
SET programActor8 = programActor8LastName + ' , ' + programActor8FirstName

-- Channel (provider) Call Letters, Display Name and Type
-- Is this correct? Should we get the channelId from the schedule table?
-- Is this efficient? View execution plan

UPDATE #programWatched
SET tchannelId = (SELECT TOP 1 c.channelId
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram)

UPDATE #programWatched
SET callLetters = (SELECT TOP 1 c.channelCallLetters
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)

UPDATE #programWatched
SET displayName = (SELECT TOP 1 c.channelDisplayName
FROM RMS_EPG..channel c
JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)

UPDATE #programWatched
SET type = (SELECT TOP 1 c.channelType
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)

UPDATE #programWatched
SET networkAffiliation = (SELECT TOP 1 c.channelNetworkAffiliation
FROM RMS_EPG..channel c
INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID
WHERE s.programId = tprogram and s.channelId = tchannelId)

IF @TerseMode = 0
SELECT *
FROM #programWatched
ORDER BY toriginDateTime
ELSE
-- Get only Genre, title, show date/time, rating, call letters
SELECT tDeviceId, tprogramTitle, tprogramEpisodeTitle, programGenre, toriginDateTime, programMPAARating, programVCHIPRating, tchannelCallName
FROM #programWatched
ORDER BY toriginDateTime

DROP TABLE #programWatched

SET NOCOUNT OFF

GO

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I also tried a query that populates some of its columns via subqueries. The query works fine when executed by the SQL Sevrer Query Analyzer,
meaning that all columns contain values, but when executed from SSRS, the columns that are poulated by the subqueries are empty, and only the columns that are not set by subqueries contain values:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT PW.DeviceID,
PW.originTime AS 'When Watched',
PW.programID,
PW.Duration AS 'Duration Seconds',

(SELECT TOP 1 programTitle FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime
AND DATEADD(second, durationSecs, DW_EPG.scheduleTime)) AS Title,

(SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND
DATEADD(second, durationSecs, DW_EPG.scheduleTime)) As program,

(SELECT TOP 1 programCategoryTypeValue
FROM RMS_EPG..programCategory PC
INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId
INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId
WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN
DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Genre,

(SELECT TOP 1 programSubCategoryTypeValue
FROM RMS_EPG..programCategory PC
INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId
INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId
WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime
BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Category

FROM DW_EventClientProgramWatched PW
ORDER BY DeviceId, programId, originTime

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Any help is appreciated

View 3 Replies View Related

SSRS - Using Inscope With A Matrix

Jun 15, 2015

I have a simple matrix. The row group is schoolname. The column group is tweek. and the data field is thours (sum(thours)). on the right side of my matrix, I want to display the Average hours for all the week columns. SSRS can do a sum very simply, but when I use the avg function, I get erroneous results. Do I somehow use the inscope function in the data field ?? I just don't know. and I don't understand how to use the inscope function.

View 2 Replies View Related

SSRS - Matrix Row Group

Feb 19, 2008

I have a matrix with six rows and the column is dynamic depending on if any data exists for any row item in a month/year. All rows are summations of data for the particular month. I have a row group that when toggled to visible expands all the rows. Is there a way to restrict the row group to only expand the data in a specific row?

View 1 Replies View Related

Matrix Control In SSRS

May 16, 2007

Just wondering when is it preferred to use Matrix control as opposed to a table? Also, on a related note, can a table control be used for displaying information from one data set or could information coming from multiple data sets be bound to a single table in such a way that there could be logically different sections within the same table?



Thanks.

View 10 Replies View Related

Matrix Type Report In Ssrs

Dec 20, 2007



Hi,

I have a matrix report in which i embedded an image in the page header, i want that image to be alighned at the right most top corner of the page when we view the report, Pls help me What to do to achieve this.

View 1 Replies View Related

Browsing A Cube In Ssrs Matrix

Jul 21, 2007

When we browse the cube in management studio we are able to see the correct aggregates where as in the report from reporting services we are just getting the sums instead of running aggregates especially in the intersection cells of columns and rows. ( where ever the aggregrate function is difference/division. )



Is there a setting in reporting services which would make the report return the running aggregates like in the management studio.



thanks in advance

View 3 Replies View Related

SSRS - 2 Parameters GroupHeader And 2 Matrix

Jul 17, 2007

Dear Friends,

I have one doubt creating a report...



I have 2 datasets... and 2 matrix...

Each matrix have own dataset... ok?


In the header page I have 2 parameters (param1 and param2) The parameters is the same for the both datasets and both matrix...

How can I group the report for 2 parameters in the 2 matrix?!



Understood?

regards!

View 3 Replies View Related

SQL 2012 :: SSRS Matrix Report Preview

Jul 16, 2015

In ssrs preview tab and When i export to excel i am getting the rows wrapped up (Please see current output below) but i want LOB and description in each row (Please see Expected output below).

LOBdescription statusJan-15Feb-15Mar-15Apr-15May-15Jun-15Jul-15
CFC RISK DO NOT USE - HIPAA 278 - INPATIENT APPROVED 1
CLOSED 147831007311326048
INPROCESS 16
DO NOT USE - HIPAA 278 - OUTPATIENT CLOSED 1
Behavioral Health APPROVED 262516222015
CLOSED 853422
DENIED 51232

[code]....

View 0 Replies View Related

SSRS - Remove Lines From Matrix Report!

Jul 19, 2007

Dear friends,

I need to remove some empty lines from my matrix.. how can i do it?

I tried to use the visibility expression, but hide me the contents of the line, but not the space of the rows.... I need to remove them from the report... check the image on:

http://pedrocgd.blogspot.com/2007/07/ssrs-msdn-temp.html



In the visibilty expression I wrote:

http://pedrocgd.blogspot.com/2007/07/ssrs-msdn-temp.html



but I need to remove!! :-(

regards!!!

View 1 Replies View Related

Reporting Services :: SSRS Matrix Only Showing First Row In Group

May 28, 2015

I am new to matrixes and I have created a dataset which I would like to populate into an expanding / collapsing matrix.

The purpose of the report is to show a list of pallets and their respective stock adjustments. I have laid the matrix out as follows...

I have spent the entire afternoon playing around with this and searching Google, but as yet, I have been unable to get the matrix to display more than 1 record per pallet. Each of the pallets below have multiple adjustment records, yet the report will only display the first one...
 
If I assign the dataset to a table, it displays as expected.

View 8 Replies View Related

Reporting Services :: Alternate Row Colour In A Matrix In SSRS

Jul 7, 2015

I am creating a matrix in which row color changes alternately(1st row white second row blue and so on .) now i am calling this matrix as a sub report in other report and i had applied grouping on that row in which this MATRIX is called the problem i am facing is in first sub report 1st row is coming in white color while in second sub report 1st row is coming as blue color background  And so on...

Why so is happening as i want 1st row as white and second row as blue i all sub reports .

View 4 Replies View Related

Reporting Services :: SSRS Matrix Report Export To CSV

Aug 18, 2015

I have one Matrix Report developed in BIDS(2008R2). If I export this report into Excel sheet exact structure what i developed in BIDS its coming. But If I exported into CSV I am loosing Structure and Columns showing as Rows.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved