Howto Do Totaling In Column Wide In Matrix Table
Jun 14, 2005Hi
View 10 Replies
ADVERTISEMENT
Totaling Different Table Columns Within SQL
Feb 18, 2007Hi, I have 3 tables which are part of my query and I need to total one column from each of them, but cannot get it to work. I had this working within Access using the nz function (below), but when trying to use this within VS to produce my ASP.net code did not work, saying NZ was not a valid function. nz(HW.HGF)+nz(HD.HGF)+nz(HL.HGF) AS FWhere HW,HD,HL are the table names and HGF is the same column name in each which I want to total.Any help would be grately appreciated.Thanks
View 3 Replies View RelatedTotaling More Than One Line Item In Table
Jan 16, 2008I need to figure out hours based on StartTime and EndTime (year does not matter) from the first table and totaled for the entire week based on the effective date in the second table.
I need it to return the follwing:
WorkHourGroup TotalWorkHours
TJOHNSO 24 Hours
Table 1 -'workhour'
WorkHourGroup DayIDStatrtTimeEndTime
TJOHNSO0NULLNULL
TJOHNSO11899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO2NULLNULL
TJOHNSO31899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO4NULLNULL
TJOHNSO51899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO6NULLNULL
Table 2 -'workhourgroup'
WorkHourGroupWorkHourDescEffective Date
SMBSMB Work Week2007-09-11 00:00:00.000
SMITHBSTANDARD2008-01-12 00:00:00.000
TJOHNSOJohnson12008-01-11 00:00:00.000
Any ideas on how to accomplish this?
Thanks,
DZ
Reporting Services :: SSRS Matrix - Add A Column In A Matrix With A Variance
Aug 6, 2015I 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.
Making A Wide Table -- SQL Query
Sep 24, 2004Hi Guys,
I have a requirement as follows:->
I have a table like below
EMPLOYEEID------- OWNS
********** ****
1-----------------car
1-----------------house
1-----------------dog
2-----------------house
3-----------------car
3-----------------bus
3-----------------shop
3-----------------hotel
3-----------------theater
3-----------------casino
Requirement:
I wanted to create another table based on the column values. For eg: I have to take the employee id and check for what value he has under owns column in the table. I take only 3 values and then these values should go to the newly created columns (owns1, owns2,owns3).
if there is no value for any of these columns it should have null values loaded in them.
The result of the modification should look like this:->
EMPLOYEEID-------OWNS1------OWNS2------OWNS3
*********** ***** ***** *
1-----------------car--------house------dog
2-----------------house------Null-------Null
3-----------------car--------Bus--------shop
Note: eventhough employeeid 3 owns more than 3 things we only take 3 of what he owns and populate to above coloumns.
In addition to it, the column OWNS will have more than 500 different values in them.
Its kind of urgent and if anyone knows how to , Can you please help me on this.
Thanks a lot.
-- Ragulan
;)
Howto Add More Column Into Select Record?
Aug 16, 2006hi, good day,
i have try following to get the records of the existing folder using :
insert into my_table
exec xp_dirtree 'c: emp'
for example , it give the result
subdirectory depth
--------------------
another 1
TEST 1
txtfiles 1
anotherSub 2
my question is , can i have add another column maindirectory into this existing result ?
and my expected result would like to be as follow
main directory subdirectory depth
--------------------------------------
another another 1
TEST TEST 1
txtfiles txtfiles 1
TEST anotherSub 2
is it possible ? thanks for guidance
Howto Use The Query Results As View Column?
Sep 17, 2006Hi All,I have two tables, one is about member infomations, the other is thecatergoriesmember_info(id,name,email,phone)member_categories(id,category)how can create a view like this (id, name, category1, category2,category3) with high performance?Thanks in advance.Joshua
View 2 Replies View RelatedSplit Wide, Denormalized Table Into Normalized Structure
Aug 27, 2002Thanks for reading.
This is pretty long, hopefully it isn't rambling.
I'm building a system that imports data from several source, Excel files, text files, Access databases, etc. using DTS. The entire process revolved around MS SQL Server, by the way.
I figured I would create denormalized tables that mirror the Excel and flat files, for example, in structure, import data to those, clean up and remove duplicates there, then break those out into my normalized table structure later.
Now I've finished the importing part (though this is going to happen once a week) and I'm onto breaking up the denormalized tables.
I'm hesitating because I'm not sure I've made the best decisions in terms of process, etc.
I've decided to use cursors to loop over the denormalized tables and use batch insert statements to push data out to the appropriate tables.
Any comments? Suggestions? All is welcome.
I'm specifically interested in hearing back on the way I've set up the intermediate, denormalized tables and how I'm breaking them up using cursors (step 2 of the process below). Still, all comments are welcome. As are suggestions for further reading.
Thanks again...
simplified example
(my denormalized tables are 20 - 30 colums wide)
denormalized table:
===================
name, address, city, state, cellphone, homephone
normalized tables:
==================
tblPerson [PK_person, name, age, height, weight]
tblAddress [PK_address, FK_person, street, city, state, zip, addressType]
tblContact [PK_contact, FK_person, data, contactType]
I'm breaking up the denormalized tables like this (*UNTESTED*):
=================================================
DECLARE @vars.... (one for each column in my normalized table structure, matching size and type)
DECLARE myCursor CURSOR
FAST_FORWARD FOR
SELECT name, address, city, state, cellphone, homephone
FROM _DNT_myWideTable
INTO
WHILE @@Fetch_Status = 0
BEGIN
-- grab the next row from the wide table
FETCH NEXT FROM myCursor
INTO @name, @address, @city, @state, @cellphone, @homephone
-- create the person first and get the ID with @@IDENTITY
INSERT INTO tblPerson (name) VALUES (@name)
SET @personID = @@IDENTITY
-- use that ID to coordinate inserts across other tables
INSERT INTO tblAddress (FK_person, address, city, state, addressType)
VALUES(@person, @address, @city, @state, 'HOME')
INSERT INTO tblContact (FK_person, data, contactType)
VALUES(@person, @cellphone, 'CELLPHONE')
INSERT INTO tblContact (FK_person, data, contactType)
VALUES(@person, @homephone, 'HOMEPHONE')
END
Removing Blank Space Caused By Wide Table
Apr 5, 2007Hi all,
Report:
-For instance 2 small tables (eg. width 10cm = 3 inch?)
-And one wide table (eg. width 30cm = 10 inch?)
All separated by "insert pagebreak after table".
Problem:
When rendered, the pages with the small tables on have a lot of white blank space at the right of the table. This is probably caused by the big table on page 3.
This report is distributed by email in Excell format. So on sheet 1 and 2 there are a lot of white cells on the right of the tables. When trying to print, they just want to use the "landscape" option and the "fit to page" option. Because of the empty white cells, the fit to page option reduces the first 2 tables to a very small table which covers only 50 % of the page width. The other 50 % is reserved for the empty cells.
Off course, I know that deleting the empty cells offers a solutions, but it would be a lot more handier if there were no empty cells in the first place.
Anybody with a solution?
Integration Services :: How To Insert Rows In A Wide Table With Over 13K Columns
Aug 21, 2015I have a flat file with 13K columns which I need to load in a wide table.
The flat file does not even have column names and no datatypes defined.
How to load data in the wide table?
Also if i choose to load the data in 13 different work tables.
How do I define datatypes in the flatfile connection manager in SSIS for 13000 columns ?
Transact SQL :: How To Bulk Insert Rows From Text File Into A Wide Table Which Has 1400 Columns
Feb 3, 2010we can easily load a file into db tables. However, my main concern here is the number of columns in the file. A text file TEXT_1400.txt has 1400 columns. I am unable to load data to my db table using BCP or BULK INSERT commands, as maximum of 1024 columns are allowed per table in SQL Server 2008.
We can still go ahead and create ‘Wide Table’ (a special table that holds up to 30,000 columns. The maximum size of a wide table row is 8,019 bytes.). But when operating on wide table, BCP/BULK INSERT commands still fail. After few hours of scratching my head over BCP and BULK INSERT, I observed that while inserting BCP/BULK INSERT commands are unable to identify SPARSE columns and skip these columns, which disturbs column mapping and results in data conversion and trancation errors.
Is there any proper way to load this kind of files into the db table?
Howto Use A Temporaly Table Created By An Sp?
Oct 12, 2005Hi all!
I would like to gain data from a temporaly table created by an EXEC command.
e.g. EXEC('SELECT col1, col2, col3 FROM Table WHERE ...') - that's right.
But I would like to use it:
SELECT * FROM _ThisTempTableTheExecCommandHasCreatedRigthNow
WHERE...
I know that a function can return a table but an sp cannot.
How can I do it?
Thx: Gurmy
HOWTO Drop Temporary Table ?
Jul 19, 2006hi, i have one temporary table
#tmp_tbl
before i create a temporary table , i would like to drop it first, but i try
"drop table if exists #tmp_tbl "
it doesn't work , any help ? thanks in advance
Totaling Records
Apr 30, 2008I have a query that I have written that gives me a record for each "customers" total invoice. I would like to only output one line for each customer but the total for all of that customers invoices.
Like this:
Customer Total
--------------------
Customer1 1000
Customer2 1123
Customer3 1134
etc...
So I need to find a way to total up each customers' individual lines.
Here is the query:
Code:
SELECT DISTINCT
dbo.ARHEADER.[INVOICE NO] AS [INV No], dbo.ARHEADER.[INVOICE DATE] AS [INV Date], dbo.ARGLTRAN.[BATCH NO] AS [Bat. No],
dbo.ARGLTRAN.[BATCH YEAR] AS Year, dbo.ARGLTRAN.[BATCH PERIOD] AS Month, dbo.DEBTOR.NAMES AS [Customer Name],
dbo.SALESREP.NAME AS [Sales Rep], dbo.DEBTOR.CSR, dbo.ARHEADER.TOTNET
FROM dbo.ARHEADER INNER JOIN
dbo.ARGLTRAN ON dbo.ARHEADER.[INVOICE NO] = dbo.ARGLTRAN.[INVOICE NO] INNER JOIN
dbo.DEBTOR ON dbo.ARHEADER.[DEBTOR ACCT NO] = dbo.DEBTOR.[AC NO] INNER JOIN
dbo.SALESREP ON dbo.ARHEADER.[SALESREP RECNUM] = dbo.SALESREP.[DATAFLEX RECNUM ONE]
WHERE (dbo.ARGLTRAN.[BATCH TYPE] = 'DI') AND (dbo.ARGLTRAN.[BATCH YEAR] = 2008) AND (dbo.ARGLTRAN.[BATCH PERIOD] = 01)
Help is appreciated!
Totaling Group Bys?
Jul 20, 2005Table1:SessionID intHours int....SessionID Hours111 3222 2333 3444 2Table2:SessionID intRegistrationID int....RegistrationID SessionID888 111888 444777 111666 222666 333I want to sum the hours for each person spent in sessions.Results I'd like to see:RegistrationID hours888 5777 3666 4What is not working:Select hours, registrationIDfromtable1, table2wheretable1.SessionID = table2.sessionIDGroup by registrationID, hoursThis adds up ALL the hours and then groups them by person.Any help?John Mosey "Humping your theoretical mom since 1993"Complaints can be sent to Join Bytes!The sun rises in the east, dumbass
View 2 Replies View RelatedHOWTO Insert Into ACCESS Linked Table Via OLE DB Destination Task
Jun 22, 2007I have used an access linked table to import data from a sharePoint list hosted on SharePoint 2007 server.
I hope someone else has done this. (1)I Does anyone know of any other way to pull data from a SharePoint 2007 list.
(2)Has anyone pushed data to a SharePoint List from a SQL Server table?
I tried doing that via the linked table but the destination task of the data flow task does not allow me to push data into the table.
(3) Has anyone designed an SSIS package to write data to an AccESS table from a SQL Server table?
Any help would be appreciated
Howto: Save Prediction Query Results To Relational Table
May 29, 2006I believe saving prediction query results to relational tables is possible (the BI studio does it!). I am not clear on how to do this w/o the BI studio, which means if I write a DMX query and want to store its output to a relational table, how do I do it?
Tips, anyone?
Thanks!
Reporting Services :: Totaling HHMMSS In SSRS
Sep 9, 2015From the image above, I am totaling the column HHMMSS is SSRS with the below expression:
=CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(0)))+sum(CInt(split(Fields!HHMMSS.Value,":")(1)))60)
&":"& CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(1))) mod 60+sum(CInt(split(Fields!HHMMSS.Value,":")(2)))60)
&":"& CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(2))) mod 60)
I equally tried:
=iif(Fields!HHMMSS.Value = "", "", CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(0)))
+sum(CInt(split(Fields!HHMMSS.Value,":")(1)))60)
&":"& CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(1)))
mod 60+sum(CInt(split(Fields!HHMMSS.Value,":")(2)))60)
&":"& CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(2))) mod 60))
and nothing works. What could be wrong with my expression?
Add Matrix Column
Oct 17, 2007hello there,
how can i add column datas together or merge two columns together.
cheers
zolf
How To Add A Column In The End Of Each Matrix Row?
Feb 25, 2008Hi,
I've a report containing a matrix. I want a column on end of each row in matrix, which shows me sum of that row. Is it possible in matrix? if yes, how can i achieve it?
Sum Of A Column In Matrix
Jun 24, 2006hi reporters!
i m using matrix in my project and i want to calculate the
(cell_value/sum_of_column). for data cell values we have to use sum or
another aggregate function for subtotaling, so for the cell value i m
wirting sum(column) / A. how to write A that points to sum of all cells
on the column.
x_column
y_column
z_column
sum(y_column)/A
A?
Subtracting One Matrix Column From The Other
May 19, 2008
I have the following fields in my report
Year
2008 2007 Net Change
Val1 10 6
Val2 7 5
Val3 15 7
The Matrix Column is grouped by Year.
I need to calculate Net Change as Year 1 - Year 2 for each row.
How do I access the contents of the first and second column of the matrix ?
Hide Column In Matrix
Oct 24, 2006I have a matrix that has the following columns:Date Shift Equipment1 Equipment2 Equipment3 etcBased on a parameter, i want to show/hide the Shift column. If the Shift column is hidden, i want to move the equipment columns over so there is no gap in the columns.I know you can do this in a table by hidding the whole column, but how do you do this in a matrix? when i try to hide the column the visibility option is not there, and when i hide the field, it leaves a gap in the report.Thanks for your help.
View 2 Replies View RelatedHow To Add Column Header In A Matrix
Jan 25, 2007Hi,
is there a way to add column header in a Matrix?
Thanks,
Igor
Matrix Column Aggregations
Jan 17, 2007Quick question for you pro's...
Within a report matrix, I want to be able to have different aggregations for totals. One column would be a Sum of the data in that column, the next column would be an Average of the data. Is this possible, or is this not supported in SSR2005?
Thanks in advance for your help,
Clint
Percentage Column In Matrix
Jan 26, 2007Hi,
I would like to generate following table with reporting service matrix.
But if I use the expression : percentage coulmn=sum(field!qty.value) / sum(field!qty.value, "region_group")
It will become a percentage of a row total, instead of a group total (store group). Please see the second table.
Any ideas?
Thank you!
(Correct!)
store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC 10 10% 20 20% 70 70% 100 100% 25 50% 25 50% 50 100% EURP C2 CHINA
(Wrong) store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC 10 6% 20 13% 70 46% 100 25 16% 25 16% 50 EURP C2 CHINA
Column Headers For Matrix
Sep 26, 2007
I am developing a matrix report in SRS. In columns group there are several values. When report runs they apper in any order based on the first record in row group. I want colums to apeear in specific order all the time. For example the column sequence in one out put is Follwup 1, Initial , Followup 2. I want to column header to be in order of Initial, Folloup 1, Followup 2.
Can someone help?
Column Names On Matrix
Apr 30, 2007Hi,
Is there any way of having Column names in a matrix report. Actually it is a RowGroup. I want to display the name of the row group at the top, so that the user understands what is the data beneath.
regards
Josh
Matrix Column Visibility
Mar 29, 2006Hello,
I have one column group and three static columns. I'd like to control static column visibility property depending on a field value. I.e. Hidden: =Fields!GroupId<>5.
But I'm not able to find visibility property in column object. Neither in properties explorer nor RDL documentation. I'm only able to set hidden property in textbox objects.
I have RS 2005.
Thank you for any idea.
Jirka Nouza
Matrix Column Grouping
Sep 15, 2006Hi,
I have a requirement from my users to be able to drill down for a single column in a matrix. I've been able to implement drilldown for all of the measures (all columns grouped at the same time) in my matrix but they now want to be able to group different columns independently of each other. In other words they want to be able to group the data in different columns by different things.
An example might make my question clearer, the report would need to look like this:
Client Name
Sales -
Costs +
Revenue -
Client A +
Europe +
Middle East +
Asia Pacific +
$12,000,000
Products +
Investments +
$12,000,000
$8,000,000
$4,000,000
$10,000,000
$22,000,000
Client B +
$77,000,000
$16,000,000
$9,000,000
$22,000,000
$8,000,000
$32,000,000
As you an see, the Sales measure has been expanded so it's grouped by region so the sales figures can be seen for all clients and the 3 regions. The revenue column has also been expanded/grouped so that you can see revenue figures for products and investments for all clients. Costs is not expanded but it could be by clicking on the '+' which would group the data in that column by something else. They have asked for more than 1 level of drilldown, so clicking on "Europe" above would allow another level for the sales measure which might be country. There will also be regular measure columns that are not drilldown/groupable columns.
Is it possible to implement this with SSRS 2005?
Thanks,
Lachlan
Extra Column In Matrix
Sep 20, 2006Hello,
I'm trying to make a report with the following layout:
Car Sales 2004 2005 Var %
Total 10 20 50%
Green 7 14 50%
Red 3 6 50%
I'm using a matrix and the data is coming from a cube. One dimension called 'Years' is used to fill the matrix columns and the data corresponding to the car sales is filled by a measure called 'Sales'.
I've built part of the example but I can't add the final column ( the VAR% column).
If I try to add a static column in the end it apears only one year.
The behavior that I would like to have is simillar to the situation when we add the Subtotal column. Although, instead the sum() made by subtotal I would like to calculate the variation percentage between years.
Is there any way to add a final column into a matrix avoiding the problem that I'm having or maybe change the behavior of Subtotal column?
Thanks and best regards.
vjn
Cannot Remove Matrix Column
Jan 12, 2007Added a subtotal to matrix column. But really wanted subtotal below the columns. Now that subtotal column is permanent. Cannot find a way to remove it.
View 1 Replies View RelatedMatrix : Compare Column Value
Mar 16, 2008Hi,
I am a newbie with Reporting services and I have a question about matrix using...
So I am using SSRS on an cube with MDX language. I have a matrix like that :
C1 C2 C3
---------------------------
R1 | 10 12 09
R2 | 08 10 23
So I would like to color my best values on a same row. I don't find a function in the expression editor.....
Do you have an idea ?????