Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







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


 
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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Mutiple Column Group Subtotals, Custom Subtotals Expressions/Calculations
 


































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 Replies !   View Related
Matrix Sub Total Column Width And Multiple Subtotals At Same Column Group Level
All,
 
I have two questions. First how can I make the width of the subtotal column in a matrix bigger than the column group cells. Secondly, how can I display two subtotals at the same group level, say one that adds all the columns and one that provides the product of subtotal and some value X?
 
Thanks.

View Replies !   View Related
Hiding Column Group Based On Row Group In Matrix...
I have a matrix that has a row group with page break after each and a column group.  I want to show only certain columns when row group is equal to (something).  Is this possible?

 

View Replies !   View Related
How Can I Hide A Table Column Based On A Group ?
I am using Reporting Services 2000. If you find out that Reporting Services 2005 would resolve this issue, please lemme know also. But I want to mention that I would prefer a way to fix this without changing Reporting Services versions.

I have a table that has a group on ProductTypes. This group is set to PageBreak at end.
What I need to do is to conditionally hide an entire column based on the current group's ProductType.

Examples
Page 1 = Product 1 = Column 1 is visible
Page 1 = Product 1 = Column 2 is visible
Page 2 = Product 2 = Column 1 is hidden
Page 2 = Product 2 = Column 1 is visible

Can you help me figure this one out ? I've tried everything I found on the net, especially everything on this page : http://blogs.msdn.com/chrishays/rss.xml

Thank you in advance.

View Replies !   View Related
Expressions Based On Subtotals
How do I create calculated fields based on subtotals?

View Replies !   View Related
Help With Group Subtotals
I have contracts grouped by project then grouped by division. On the  division group header I want to show the subtotal of all active projects only.  CountDistinct(Fields!ProjectId.Value) gives me count of all projects. 

I can't figure out what I need to get only a subset of projects counted. I need something like:

CountDistinct(Fields!ProjectId.Value & Fields!ProjectStatusId.Value = 1)

but that isn't allowed.

Any help appreciated.

View Replies !   View Related
SSRS 2005 - How To Have Multiple Subtotals In Matrix
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 Replies !   View Related
Subtotals In Table (group Footer) Using Report Items 2005
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 Replies !   View Related
Need Script For Column Visibility Based On Group's Visibility Toggle
 

Hello,
 
I'm using SSRS 2005
 
I'm trying to write an expression for the "Hidden"  property of a column in a table. The column is only populated with data if the group on my table is open. If the group is collapsed, then the column is empty. I'd like to make the column hidden if the group is collapsed. So I'm thinking it would be something like this:
 
=Iif(Table_1_Group.Hidden = "True", "True", "False")
 
But I can't find anyone who has written this anywhere.
 
I'm basically trying to make this report do the same thing a matrix does, but the matrix doesn't let me label the columns. I put textboxes above the matrix in line with the columns, but when I deploy the report to ReportManager, the textboxes get thrown all over the place and don't line up with the columns anymore.
 
So I guess if you can't answer the first question, an alternate question I have is how are you supposed to label the columns (row groups) in your matrix? They don't have headers, if I try to line textboxes up with them they get all messed up during deployment. They have that textbox up there in the top left corner that runs across the top of all of the row group columns, but if I put labels separated spaces in that textbox, the spaces get removed in rendering so the text is pushed all to the left and doesn't line up anymore.
 
Thanks,
Andy

View Replies !   View Related
How To Have 2 Subtotals Under Column Side For Reports?
Hi to all,

 

I am a newbie to SQL Report 2005 and currently i am required to a report with a format something like this.

 

                                                Month         Total              Total (%)

Employee A

Employee B

Employee C

 

Currently, i am able to generate a table with [Month], subtotal[Total], Employee rows...

 

I do not know how to display another subtotal of [Total(%)] which calculates the percentage of the Total (i.e, Total/GrandTotal x 100%)

 

Any advice is appreciated.

Thanks

 

Rgds

Eric

View Replies !   View Related
How To Use Group By (group Tasks Based On Projects)
Hi folks,

               I have a Projects , each project have many tasks now i want to display tasks replated to each project:

for example:

            

 

 

    Project1-------------------->task1

                                              task2

                                               task3

                                              task4

 

Project2----------------------->task4

                                              task5

                                              task6

 

 

.............................................projectN.....................

 

 

how to write query for this

 

i have 2 tables:

Project .......>columns are projectid

Task------------->columns are projectid, taskid

|


 

View Replies !   View Related
Problem With Subtotals Of A Matrix (Blank, Pdf, Empty Column)
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 Replies !   View Related
Column Based On Other Column (short Name Based On Name), When To Do The Transformation?
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 Replies !   View Related
Subtotals In Report Give &&"Infinity&&" Value In Case Of Empty Cells In Column.
The title, I think, accurately illustrates the situation in my report.
Naturally, this result is unwanted.
 
How do I get the avg-function to ignore empty cells in the column?
 
Thanks in advance,
 
Pluggie

View Replies !   View Related
Sum In A Group Based On Other Group Value
 

Hi all,
I have a Report and I have used Table in the report to define 5 levels of Sub Groupings: Group1, Group2, Group3, Group4, grpResourceBehavior.
 
The Sample data which comes is:
Group1    Group2    Group3    Group4    ResourceBehavior    BgtHrs   BgtAmt
   A1             B1         C1           D1         NML                        12         12
   A1             B1         C1           D1         ODC                         0          12
 
Now, to calculate a field "Cost", I have to use following condition. If the ResourceBehavior is NML, then Cost = BgtAmt/BgtHrs
But, if the ResourceBehavior is ODC, then COst = BgtAmt/(SUm of BgtHrs for the resourcebehavior NML)
 
I am not able to calculate the Sum of the BgtHrs for different ResourceBehavior. Also, I need this field calculation for each and every group as well, and this makes it all the more complicated.
 
Any help is appreciated.
 
Thanks in advance.
Swati

View Replies !   View Related
Query Based On Group By Clause
Hello!
suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like
DEPT ABSENT
-----------------------------
Accounts 10
EDP Section 0 **
Admin 2
Stationary 0**

if no employee is absent in the department it has to display Zero

View Replies !   View Related
SSRS Group Authentication
Added an AD group to SSRS both in the properties of the server with system user access and gave it role "browser" but users are denied access. Why?

View Replies !   View Related
SSRS Conditional Group?
Hi,
 
I was wondering if there was a way to do a conditional group in Reporting Services or do I need to create another report?  One user wants it grouped by a certain field and another user does not want it grouped at all.  I know I can hide the group header with a parameter but I don't want it sorted by the group either.
 
Thanks for any help.
 
Fred

View Replies !   View Related
SSRS - Matrix Row Group
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 Replies !   View Related
Query To Find A Value In Column B Based On An Aggregate Function On Column A?
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 Replies !   View Related
Assigning Values Based On InitialToggleState Of A Control In SSRS
Hi,

I need to assign the value for a field in a report based on Expand/Collapse state of another field.

Eg. If Collapsed, the value should be "AA" else if Expanded "BB".

Is there any way to get the value of InitialToggleState for any field in SSRS.

Thanks in advance.

Sathya

 

 

View Replies !   View Related
Using Prompt Parameter Into SSRS Report Based On Sql Query
Hi All,

I have a question regarding how to use report prompts in SSRS reports that are based on SQL queries.
When I added the prompt into the query for use as a filter value, it says that there is an error.
It does not recognise the '!' inside the parameter prompt string. Example is parameter!month_prompt.Value, which the ! is not recognised.

Your help is much appreciated.

Thanks & Regards,
Mohd Fadzli

View Replies !   View Related
Derive A Column Based On Other Derived Column In A Table .. Is It Possible ?
 
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 Replies !   View Related
SSRS: How To Get A Page Break After A Value In A Group
This question is related to SSRS: There are 3 groups in my report– grp1, grp2 and grp3, with grp1 at the top, grp2 in the middle and grp3 at the bottom. I need to insert a page break when the value of grp1 record becomes “abc”. Is this possible? If so, I will really appreciate if someone can let me know how.

Thanks in advance,
Saurav

View Replies !   View Related
SSRS Adding Group Rows
 

Hello Friends,
                     I have created a report using SSRS and in that report I am using group rows in one of the matrix. When I tried to display the subtotal of that group row by using the SSRS in-built feature the subtotal column is coming at the last of the matrix columns but I want it at the front .
 
so it will be like this,
 
First the total should be displayed then the group members value.
 
Can anyone help me on this issue.
 
Thanks & Regards
Shivanandan Gupta

View Replies !   View Related
SSRS GROUP PAGE BREAK
I am using three groups in my report on a table. The option "page break at end" is checked in all three groups. It is working fine as for report output is concerned but in print preview it generates extra pages. In print preview page break gets occured at every group regardless of the fact that group break occurs or not. As a result, it is generating extra pages. please help me out
 
thanks.

View Replies !   View Related
Sorting Parameter Values In SSRS Model Based Report
Hi,
 
I have created a report using Report designer (Visual Studio,  using Data Model as a data source), in the report I had created few datasets (with single filed) to populate the report parameters, lets  say I have created a multi valued Parameter CustomerName and assigned field from a dataset,
 
result are coming correctly and combo box is getting populated but the customers are not in alphabetical order!
 
I want to sort it and need to specify it in report (please note that I am using Report Model as a data source and I can€™t sort the source table in the data base to get the result sorted)
 
 
Please let me know if anybody has done that or forward me if know some link which talks about it.
 
 
Thanks in advance.
 
Regards,
 
Jayant Jape
 

View Replies !   View Related
Change Property Based On Actual State Of Collapsed/expanded Group
Hi, please I would like to use something like this>

=IIF(table1_group2 is Collapsed,True,False)

I want to generated some event based on actual state of e.g. report group. if user expand group make this event otherwise (group is collapsed) make another event.

Thanks for your advice.

View Replies !   View Related
Display Data In Reports Based On Active Directory Group Membership...
Hello,
 
I am fairly new to SQL 2005 and Reporting Services.
 
We are trying to create a report that will display sales data based on group membership from Active Directory.
 
For example, if USER1 logs in and looks at a Year to Date Sales report, it will only show data that pertains to his group.  If USER2 logs in and accesses the same report, it will display different Year to Date information because he is in a different group.
 
Background Information:  We are running SQL 2005 Enterprise Edition Service Pack 2 with Analysis and Reporting Services.  We are delivering the reports through a Sharepoint site.
 
Please let me know if anyone has a good way to tackle this.
 
Thanks,
 
Justin

View Replies !   View Related
Can SSRS 2005 Handle Stored Procedures Or SQL Subqueries That Rreturn Rowsets Based On Multiple SQL Updates?
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 Replies !   View Related
Update Column Value Based On Value In Another Column?
Hi.

I'm writing a web application with VS2005 andSQL Server 2005 express edition.

I have an SQL table:

Table name:
statistics

Columns:
stat_id
firm_name
stat_month
stat_year
view_count
follow_count
percentage

When a user clicks a button, an sql query is fired which increments the view_count value by one and calculates a new percentage value from this. The query to update the percentage value doesn't work, here's the query:

UPDATE [statistics]
SET percentage = follow_count / view_count * 100
WHERE (stat_id = 15)

This code worked fine with MySQL, but since migrating to MSSql it doesn't seem to work. The data type of the percentage column is: decimal(5, 2)

Any help would be appreciated.

View Replies !   View Related
Return Value Based On Column
Hello all, I am a newbie to MSSQL Functions so forgive me if this is an elementary question.

I have two tables, ContactRelations, and RelationshipName,

ContactRelations
FK - RelationshipID
FK - Contact1ID
FK - Contact2ID

RelationshipName
PK - RelationshipID
- Relationship
- InverseRelationship


When the user will selects a contact and their relationship, an entry will be added with the contact id's and relationshipID. If the selected relationship is in the relationship column, the user's ID will be entered into Contact1, and if the relationship is in the InverseRelationship column, then the user's ID will be entered into Contact2.

On the display page, I'll search the Contact1 and Contact2 columns for the user's Id. if the user's id is in contactI then i'll display the value from the Relationship column; if the user's ID is on contact2 i'll display the value from the InverseRelationship column.

This is the part where I need some help. I need to write a function that returns a value dependent on which column he value is from. Any help?

P.S.
I know there are other ways to implement this functionality, but I'd appreciate any help because writing this type of function can be useful in other ways. Thanks.

View Replies !   View Related
Identity PK Column Based On Other PK...please Help
I have this table where there is tow primary keys ,one for Year and the other for DocNumber.

In this table the DocNumber takes a number based on each year,for example in year 2008 the DocNumber should start counting from 1 and increment by one for every record,when the year changes to 2009 the DocNumber should start again from 1 until and seed by one again.

Those columns are PKs because there must be one doc Number for each year and they can not be null .

Is there any simple way to approach this in Sql Server 2005 ,or do I have to work on trigger to do so...???

Any ideas will be really appreciated.

Eng.Osman Hamad
http://arabic.aspose.com

View Replies !   View Related
Column Value Based On A Formula
I am trying to do the following.
While inserting a record into a table I need one field to be created based on a stored procedure output.The stored procedure will create something like this, "XX-mm-yyyy-incremental number which resets to zero at the end of every month". All other field except the Identity field and the above mentioned storeproc generated field will be inputted manually. How can I do this?
Thanks
Hope my question is clear enough.

View Replies !   View Related
Avg Based On Column Values
Greetings,

I need to avg column data based on grid textboxes. I cannot use AVG because the column values come from a comma delimited string. I have tried using !Parameters to store a count and total for each column but they are always read only. What is the best approach.

The columns come from a field which is in the format of (99,75,60,100,-1,20,-1,80,75) for each record

-1 means the values are not counted in the average. I have a function for each textbox that parses the value from the list based on the column index. There can be 1 to many columns.

Everytime I to set the value of a field or parameter at runtime I get a read-only error.

 

Maybee the only way is to use calculated fields.

 

Any ideas. 

View Replies !   View Related
Summing Up Based On Value Of Another Column
I have a report with several columns which include Status(0,1,2) and Time in hours.   I want to be able to total up the time for each status.  I Sum up the all of the fields fine.   I'm new to reporting services so any help would be appreciated.   Thanks

View Replies !   View Related
SSRS Toggle Parameter Between Multi-Value And Single Value Based On Another Parameter
 

I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful.
 
Thanks

View Replies !   View Related
Check Constrain Based On Another Column
Hi All,

I want to create a check constrain on a column based on the values in another column.

Following is the sample data

Col_A Col_B
AA A
AA B
BB C
BB D

I want a check constraint on col_B such that it's value is A or B if Col_A has AA and likewise col_B has C or D if Col_A has BB.

Can anybody tell me how to accomplish this.

Thanks in Advance
Rajesh

View Replies !   View Related
Query Based (partly) On CSV Column
I'll try to simplify the problem as much as I can.

There's survey. It has different types of question answer pairs, but the problem is in Multiple Choices, Multiple Answers questions, where user can choose, for example 3 checkboxes from 5 at the web form, for the question.

I am emphasizing three tables:

tbl_questions
IDquestion (primary)
question (textual question)

tbl_answers
IDanswer (primary)
IDquestion (foreign,to table tbl_questions)
answer (textual answer)

tbl_results
IDresult (primary)
IDquestion (foreign, to tbl_question)
result (string based on CSV, may contain few IDanswers, because visitor may choose few checkboxes, for example it can be "4,6,7")

Inside those tables are data (I'll put here just two questions):

Example:
tbl_questions
IDquestion_question
1_________Which OS do you use?
2_________Which databases do you use?
tbl_answers
IDanswer_IDquestion_answer
1________1_________Windows
2________1_________Linux
3________1_________Mac OS
4________2_________SQL Server
5________2_________MS Access
6________2_________MySQL
7________2_________Oracle
8________2_________Other
9________2_________No, I do not use databases
tbl_results
IDresult_IDquestion_results
........
23_______1________1,2
24_______2________4,6,7
........
Is there a possibility to make query, using columns, but also the data inside the CSV format in results (varchar or char) column?

Result of the query would be:
IDquestion_IDanswer_answer
1_________1________Windows
1_________1________Linux
2_________4________SQL Server
2_________6________MySQL
2_________7________Oracle

Further queries based on this query should be able to make statistics (GROUP BY).

View Replies !   View Related
Displaying Tablenames Based On The Column Name
hi,

i have the following scenario,

100 tables in the database,i have to display the table names if the column name given by the user is repeated in the different tables...

e.g.: Let us say,the column name ID IS REPEATED in the 70 tables out of 1000 tables.Then i have to display all the 70 table names..

how to write sql query for it?

i am greateful if any one help me in solving this scenario..

View Replies !   View Related
How To Get Additional Rows Based On Column Val
Hello,

I am unable to figure out how to proceed after trying for more than a day. Should I add a parameter to the stored proc? How do I proceed?

I need to be able to show data for EdgeID 2,3,5,6,20,21 and so on...Right now I am showing data for 1, 4, 19 and so on based on the ReltTotID based on the result set below. This is because the table that the query below is selecting from adds up all common EdgeIDs to give one row for example

EdgeID Desc TermType ReltTotID

1Global Edge Model w/ Fwd Earn II T 1
2Short Term Global Edge Model w Fwd Earn IIS 1
3Long Term Global Edge Model w Fwd Earn IIL 1
4Emerging Market Edge Model w Fwd Earn T 4
5Short Term EM Edge Model w Fwd Earn S 4
6Long Term EM Edge Model w Fwd Earn L 4
19SmallCap Edge Model w/ Fwd Earn T 19
20SmallCap Short Term Edge Model w/ Fwd EarnS 19
21SmallCap Long Term Edge Model w/ Fwd EarnL 19
35Global+EM Edge Model w Fwd Earn T 35


The final query result is :



EdgeID Description Short Desc PerID UnivID DefID

1Global Edge Global Developed 500622355938
4Emerging Market Emerging Markets 500632356039
19SmallCap Edge Small Cap Edge 500642364244


I would like it to be :

1Global Edge Global Developed 500622355938
2Short Term Global Developed NULL2355938
3Long TermGlobal Developed NULL2355938
4Emerging Market Emerging Markets 500632356039
5Short Term Emerging Markets NULL2356039
6Long Term Emerging Markets NULL2356039
19SmallCap Edge Small Cap Edge 500642364244
19Short Term Small Cap Edge NULL2364244
19Long Term Small Cap Edge NULL2364244


The stored proc query is as below:


SELECT
EdgeModelID = em.EdgeModelID
--, EdgeModelID = em.EdgeModelID
, Description = m.Description
, ShortDescription = ISNULL(emdn.ParameterValue, m.ShortDescription)
, ViewPermissionID = emdp_perm.ParameterValue
, EdgeUniverseID = univ.UniverseID
, EdgeDefinitionID = univ.MemberID

FROM OptMod..GO_EdgeModels em

JOIN OptMod..GO_Models m
ON em.EdgeModelID = m.ModelID
AND m.ModelType = 'E'
AND Status = 1

JOIN OptMod..GO_EdgeModelDisplayParameters emdp
ON emdp.EdgeModelID = em.EdgeModelID
AND emdp.ParameterName = 'NewEdge32 Screening'


LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdn
ON emdn.EdgeModelID = em.EdgeModelID
AND emdn.ParameterName = 'NewEdge32 Display Name'


LEFT JOIN OptMod..GO_ModelUniverses mu
ON em.EdgeModelID = mu.ModelID

LEFT JOIN OptMod..vUniverses univ
ON mu.UniverseID = univ.UniverseID

LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdp_perm
ON emdp_perm.EdgeModelID = em.EdgeModelID
AND emdp_perm.ParameterName = 'NewEdge32 Permissions'

WHERE em.EdgeModelID = em.RelatedTotalEdgeModelID



Thanks in advance!!!
sqlnovice123

View Replies !   View Related
Add A Column Based On A Select Query
Supose I have the following select:

Select Name, Age, (select TelNums from Telephone)
From Person

The problem is that (select TelNums from Telephone) can return more than 1 record:

tel1
tel2
.
.
.

I was wondering how I can make a select to return the tel numbers like: 'tel1,tel2,tel2'

»»» Ken.A

View Replies !   View Related
Adding A Y/N Column Based On Criteria
Currently I'm trying to add a column based on certain criteria based on the following data:

CallID GroupName CustomerPending
------ ----------- ------------
00500588FollowupN
00500588FollowupN
00500588Server N
00500588Service DeskN
00500588Service DeskN

Basically I'm trying to add an extra column, so that whenever the GroupName is "Followup", then a 'Y' will appear in the CustomerPending column for all instances of that CallID. I tried with the following, but it only provides a 'Y' in the rows (not the CallId's) where "Followup" is found.

--------------------------------------
UPDATE dbo.Asgnmnt
SET CustomerPending = 'Y'
FROM dbo.Asgnmnt
WHERE dbo.Asgnmnt.GroupName IN ('SD Followup')

ALTER Table Asgnmnt
ALTER column CustomerPending varchar(1)

UPDATE dbo.Asgnmnt
SET CustomerPending = 'N'
FROM dbo.Asgnmnt
WHERE dbo.Asgnmnt.GroupName NOT IN ('SD Followup')

ALTER Table Asgnmnt
ALTER column CustomerPending varchar(1)
---------------------------------------

Any assistance appreciated.

View Replies !   View Related
Unique Constraint Based On Value In A Column
I don't immediately find if this is possible but hope someone can give me an answer:
is it possible to make a unique constraint over 2 columns but only when 1 column has a specific value ?

Example: table (tableid, instancetype, instancename, ..)
instancetype can be A or B
if it is A then instancename must be unique
but for B instancename is not unique as these are copies from A

only solution I can think of is to make a trigger on an insert to check what the instancetype is and do a select to see if the name already exists in the table or not..

are there other solutions to make a constraint like this ?

Aeneas.

View Replies !   View Related
Matrix And Column And Row Based Averages Without Using Avg()
 




 

A

A

A

A

B

B

Avg.


Name

1

2

3

4

1

2

 


xxxxxxxxxxx

12/16

90

100

85

10/8

100

--


xxxxxxxxxxx

20/16

 

 

93

5/8

100

--


xxxxxxxxxxx

16/16

 

89

90

16/8

95

--


Avg.

--

--

--

--

--

--

--
 
 

Greetings,

I have a matrix that looks similar to the table above with two row groups and one column group. Does anyone know a way to manually do column and row based averaging without using the avg() function -as the data is not numerical?
Thanks

View Replies !   View Related
Select A Column Based On A Parameter
I need to select a column based on a parameter, something like this:

 

SELECT column

FROM table

WHERE (column=@parameter)

 

or just:

 

SELECT @column

FROM table

 

The user should be able to decide which column of data to present by choosing this in a scroll down list.

Is this possible and how do I do this?

 

Sincerely

Niklas

View Replies !   View Related
How To Sort Data On Based Of Column's Value?
Hi everybody!
 
My users need to sort data on base of columns that they select ,the same as sorting in grid but i need it  with reporting services.
 
Thankas in advance.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved