Matrix Headings On 2005, Pain In The Neck
Jan 9, 2007
Hello,
I know this is a known issue since SSRS 2000, but it seems that it hasn't been solved: How can I have column headings on a Matrix Report ? (not in the data region, just in the columns that identifies the rows).
All the workarounds and tricks that I have found are quite nasty and have drawbacks when you export to Excel, for instance:
- Using a table inside upper left blank area in the matrix report: Problem, when you export to excel you loose the Header.
- Using an rectangle and placing textboxes (tweaking with pixels... :-(): When you export to excel you get excel columns merged, quite bad if somebody wants to make some macros or calculations).
- Using a list report... well I need a matrix report :-).
Is there other way to implement this ? This is a known issue since 2000 came up, no really a solution available ? I have seen on SP-2 and there is no solution for this issue... please heeelp.
Greetings
Braulio
View 1 Replies
ADVERTISEMENT
Dec 28, 2007
what is considered the best practice for placing headings above dynamic row headers if you want them to float with the row headers when scrolling horizontally?
View 8 Replies
View Related
Sep 15, 2006
I built a test job that loads data into the database. I get greatperformance with Oracle and I'm trying to tune Sql Server to get thesame type of performance.Based on system monitoring it looks like an I/O issue. So I startedinvestigating based on sql server perfromance tuning technicalreference.Here are my system monitor findings:The system monitor shows during my job:disk writes per second 3670disk reads per second 0avg disk queue is .45avg disk second read 0avg disk second write 0My total i/os per second is 7200. (disk writes per second * 2) + diskreads. It's times two because I am mirroring.I have a 6 disk raid array configured Raid 1/0 with 200M of cacheconfigured read/write. The average disk second read and write of 0means no i/o latency but my i/os per disk is high compared toMicrosoft's recommendation of 125 i/o per disk. I don't see an i/oissue based on the numbers, except for the amount of disk writes persecond. The same job ran in Oracle had 187 disk writes per second ascompared to 3600 writes per second in Sql Server. What's up with that?Here's my calculation:i/o per disk = [reads + (2 * writes)/number of disksi/o per disk = [0+(2*3600)/6] = 1200Microsoft is saying I need way more disks based on their calculationrecommendation.Total i/o / 125 i/os per disk = disk needed7200/125= 57.6 - 58 disksIf the disks are showing no latency in the system monitor how can diskbe a bottleneck?Based on Microsoft's recommendation - I need 58 disks to relieve thedisk bottle.But I have no i/o latency and the disk queue length is .45.Ahy recommendations ?
View 19 Replies
View Related
Dec 28, 2005
hi
i have a database named Friends
I have a table there with the name Phone
I following is the query
Select * from Phone where statuscode = 'Alive'
Now i want to use XP_Sendmail
EXEC xp_sendmail @recipients = 'myself@myworld.com',
@query = 'SELECT * FROM Friends.dbo.Phone where statuscode='Alive' ',
@subject = 'Friends that are alive',
@message = 'Alive Friends are as follows:',
@attach_results = 'TRUE', @width = 250
This is giving an erorr (Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'Alive'.
, plz help
& when I try it without the quotes it still doesnt work .
Please help
View 7 Replies
View Related
Dec 31, 2006
Hi All... I'm using a SQL Server 2005 database. I've noticed that columns that are declared as "char" and that have a fixed size tend to put trailing spaces at the end of the data when I pull it out. I guess I can understand why... But it's a pain dealing with it. As I'm bringing my application up, I can see spaces all over the place - I just havent gotten around to doing anything about it yet. What's the easiest/best way to get rid of those spaces. Geez, it'd be real cool if I could put something in the SELECT statement. Any thoughts? Thanks much!! -- Curt
View 1 Replies
View Related
Apr 8, 2004
I have a table that stores pass information for coils in a steel mill. For each coil stored, I have a field that has the number of passes made, and then fields for the width and gauge of steel for each pass. (ie, it has pass1gauge, pass1feet, pass2gauge, pass2feet, etc. up to a max of 9 passes ... table was in place and in heavy usage long before I got here, so don't blame me for the problems with the schema ... :)
I need to, in SQL, calculate the weight of the coil (width*last pass gauge*(last pass feet*12)*.283). I know from the pass field how many passes were made, and in VB, I could bring down the recordset, loop through the recordset and calculate this by checking the pass field, but there has to be a way to do this in SQL.
The gist of my problem is I need to, if its a 5 pass coil, do calculations with pass5gauge and pass5feet. Maybe I could do a join back on the same table? But ... well I'm not too experienced with SQL. :confused:
Anyone see how to do this? I can give a crap-ton of more details if needed. I am an admin (the only admin, as a matter of fact) of this db server, so I can do anything needed (ie, stored procedures for testing, views, whatever). I'd like to be able to avoid stored procedures, though, if possible.
View 14 Replies
View Related
Jun 13, 2007
Does anyone know how to output the contents of a table via BCP utility to a file WITH the column headings?
Thanks in advance,
--Alla
View 5 Replies
View Related
Mar 17, 2004
I use the following to get the column names from a table but it is giving the error "Incorrect syntax near the keyword 'Where'"
Here is the Stored Proc:
CREATE PROCEDURE usp_GetTemplatePrompts
@cCode varchar(5)
AS
declare @cTableName varchar(100), @iError int, @cError varchar(255)
SELECT @cTableName = s.TableName
FROM tbShapes AS s INNER JOIN tbProductCodes AS pc
ON s.Shape = pc.fkShape
WHERE pc.Code = @cCode
SELECT @iError = @@Error
if(@iError = 0)
BEGIN
EXEC('SELECT * FROM ' + @cTableName + ' WHERE Template = 0')
SELECT @iError = @@Error
END
if(@iError <> 0)
BEGIN
set @cError = 'Error while attempting to get input fields for product ' + @cCode + '.'
RAISERROR(@cError, 16, 1)
END
GO
The table structure is as follows:
tbProductAccounts
Account (PK) | Description
tbProductCodes
Code (PK) | fkAccount | fkShape | Description
tbShapes
Shape (PK) | TableName | ShapeName
tbDoubleT
Template (PK) | fkCode | SectionName | Width | Height | Flange
tbRectangular
Template (PK) | fkCode | SectionName | Width | Height
tbCircular
Template (PK) | fkCode | SectionName | Radius
tbTSection
Template (PK) | fkCode | SectionName | X1 | Y1 | X2 | Y2
Any ideas?
Mike B
View 1 Replies
View Related
Jan 15, 2007
Hi,
is it possible to export a report to a csv file without the headings?
at best i have been able to hide the headings in the report, but when exported to csv file it includes a row at the top of commas, then the data follows. I need the file to be just the data.
thank.
View 10 Replies
View Related
Mar 3, 2004
If the SOURCE doesn't have column headings in a txt file (the format will be pipe delimited), how can I make it work (Through DTS) to load the source into my designed SQL Table? Thanks in advance!
Jqiu
View 4 Replies
View Related
Sep 22, 2006
This may be a stupid question, but I have a table of orders that consists of various data. I want to take by date all orders that have been completed but have not had a previous order in 180 days. I have this part done. What gets returned is Date, Count, Status. What I want to get is the various status's returned as headings. So I get Data, Total Order Count, Completed Count, Abandoned Count, etc. I'm returning this data to Excel for display. My original thought was seperate queries, but some of the data will not have a count so the ordering would be off. Any ideas?
View 3 Replies
View Related
Feb 2, 2006
Hi,
I've got an IS package which reads a lot of records from a text file and loads that into the database. The text file has column such as Firstname, Lastname, phone number etc and same as the database table.
The problem:
IS works fine if I have the text file columns in the same order as the database columns but for example if have phone number in the place of firstname (in the text file) IS puts the phone numbers as firstname in the database and moves all the columns dow the order.
Is there anyway I could force IS to use the heading names in the text file and put it in the appropriate database columns?
Thanks guys...
View 4 Replies
View Related
Nov 30, 2007
Greetings,
I have generated a report that requires the user to scroll down the page since it is fairly long. I want to be able to show the column headings as the report is being scrolled. Much like excel locking the column headings as you would scroll down the spreadsheet so you know what values correspond to what heading. How do I configure this report to do this? Any suggestion is greatly appreciated. Thanks!
View 6 Replies
View Related
Feb 5, 2008
We have a query in which we have data in fields called TS1Min, TS1Max, TS1Avg, TS2Min, TS2Max, TS2Avg etc.
We have a different table in which we define that vale of TS1, TS2, as an example TS1 might equal RED, TS2 might equal BLUE.
We have written a query that puts TS1Min, TS1Max, TS1Avg, TS2Min, TS2Max, TS2Avg in a temp table #TEMPA
and we also put the values of RED and BLUE in another temp table #TEMPB
now we want to select * from #TEMPA but rename the headings TS1Min to display RED-TS1Min, TS1Max as RED-TS1Max, TS1Avg as RED-TS1Avg etc...
any ideas on how to do this
View 4 Replies
View Related
Oct 30, 2007
Hi,
Is it possible to display Report headings text vertically bottom-up?
It goes from top to bottom by default now, which seems unusual.
Thanks,
Alex
View 1 Replies
View Related
Apr 2, 2008
I am relatively new to SQL and have experience with relatively simple queries and data manipulation. I have just finished creating a fully normalized database, which, of course, is presenting me with a few problems retrieving data. I'm not sure if I'm even taking the right approach. Anyway, I am trying to create a web search with a filter. I want my filter to be based upon different columns in a view, rather than rows in one column. I have the following columns:
Emp_Title, Department_Name Company_Name Office_Location, Practice_Area_Name, Practice_Group_Name, School_Name, Emp_Zip_Code
I want to populate a drop list with these column headings. So I thought I would need a way to make a dummy column named Category or something, and then make each of these columns a row in the the Category colum. Can that be done?
Any help would be appreciated.
View 3 Replies
View Related
Nov 16, 2007
we'd like our users to have the option of choosing the language (eg French) for column headings (maybe report heading too). I'm sure I'd have to provide the translations somehow but I'd like to know what feature, if any in RS2005 comes the closest to doing/supporting this, even if it is something like making column headings themselves dependent on a variable?
View 5 Replies
View Related
Jun 5, 2001
I'm using isqlw to generate delimited text files from scripts. There are several SET options available so that you can restrict the output to just the data in the script, for example SET NOCOUNT ON.
I can't find a similar SET option to suppress the column headings, although there is a tickbox that allows you to do this in Query Analyzer (Query -> Current Connection Options -> Advanced -> Print Headers)
DTS packages appear to support this, yet I can't find the setting from a dts file.
Thanks,
Paul
View 1 Replies
View Related
Apr 21, 2008
Hi,
I have a SSRS 2005 report with a table and several groupings. These groupings are made visible with toggle items.
I am trying to determine how to make the associated group headings (entered within table header) visible or not visible depending on the visibility state of the groupings.
Example: (initial state with group1 only visible)
Group1 Heading Count Sum
+Group1 999 999
(click + sign to make visible group 2)
Group1 Heading Group2 Heading Count Sum
-Group1 999 999
+Group2 888 888
etc.. for remaining groups
I've tried toggling the table header column the same way I did the group, but the group scope isn't available so I don't know what to use. (Manually putting it in causes report error.)
I don't know what visibility expression to use because I can't figure out what report item I can look at to determine the group's visibility "status" in order to make an expression.
Any ideas?
Note: I did see a similar post from sadsac, but the "answer" didn't address this.
Thanks
View 1 Replies
View Related
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
Jan 8, 2007
When I am using a SQL Query I have an ability to control whether or not I am able to Include the Column Headers when copying or saving results.
The control exists in the Options > Query Results > Results to Grid > Include column headings etc.
My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query.
The idea is that when I setup a view it€™s a drag/drop type of query building (query building for dummies if you will). Once I have a view and click the Execute icon it will return all the records selected by the View. However, when I click the upper left/top box to select all rows and column and then try to copy/paste the records into Excel all the data copies just fine but the field name/column headers are not there.
How can I get the header fieldname date to copy/paste from View result set that I'm able to copy from a Query result set?
Thank you,
Mike
View 1 Replies
View Related
Jan 31, 2007
I wonder if anyone can help with this. I have searched around and not found much on the topic which can resolve my problem.
History on the reason for using VBA and not C# or VB or VB.net etc. Whn I arrived at this company, there were no development tools so had to resort to Access and VBA. Of course, the routines I wrote are now set in production and I haven't had much time to convert them to c# and ASP.NET (which I have learnt over the past couple of years).
Anyway the problem is that I found on the SQLDTS.com site that I could in fact create and execute DTS packages within VB and followed the examples and got this working in VBA. Of course I created the DTS first and then saved it as VB, then used the resulting code with the VBA project etc etc. This worked well and we were happy.
The DTS packages I create and execute are to export data from SQL2000 tables to tabs in Excel spreadsheets.
Now I find that on my machine, when the objPkg.execute command runs, the Excel file is created but only the query column headings are populated into the excel file. No data is returned.
If I run this on a different machine it works (although having said that, the other machine is now starting to display similar behaviour on some routines).
we created a version of the Access 2000 MDB in Access 2003 some time ago and the Access 2000 still behaves correctly. The strange thing is that my machine has only started doing this incorrectly a little while ago (not sure how long now but could be as little as a couple of months). The routines are monthly processes so it's only going to show up as a problem once a month so the fact that it used to work on my machine and has only failed the last couple of times means that something must have changed on my setup. I am runningXP-Pro SP2 - fully patched, Office Pro 2003 and talking to SQL 2000 SP3a
is it possible that some windows updates or office updates are clobbering something which used to work?
If I allow the VBA to create the DTS but not execute it, I can execute the created DTS manually from SQLEM and it outputs data correctly.
No security access to the SQL databases/table has changed, nor have there been any changes to security on the file system where the files are written.
It's weird.
Any help would be gratefully accepted.
Annabel
I'm really lost and not sure whether I should look at driver versions, ODBC versions blah blah
View 1 Replies
View Related
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
View Related
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
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
Jan 10, 2008
I have a Matrix table that expands to the right when choosing an amount of months to be shown. Under this matrix I have to Charts. The two charts are situated together, that is no space between them, and to the left of the report.
Now, if I choose a lot of months, say three years the matrix diagram will be huge to the right. The problem I have is that the second diagram, the one on the right, moves to the right depending on how big the report gets, and this is not good at all. The two charts are supposed to be all the way to the left.
How? Why does the right chart move?
Thanks in advanced
Kind Regards
View 1 Replies
View Related
Sep 8, 2006
Hi,
I have a simple enough task to complete that I can€™t seem to find the answer to.
The task is this €“
Select table x from the database and write it to a flat file complete with that tables column headings.
Now I€™ve managed to set up an ole db datasource and selected the table and I€™ve also linked it to the flat file output. So now I can generate a flat file from the database. However no column headings appear in the flat file.
I can€™t seem to find anywhere (like a checkbox) that will also output the column headings to the flat file.
Now I can add in Headings manually in the properties of the Flat File Destination object but the columns that appear in the flat file don€™t appear to be in the order that I requested them in the SQL.
So the question is how do I automatically have the column headings appear for flat file output (ideally without me having to manually add them in).
If it can€™t be done and I have to use a vb.net script instead then would anyone have an example script of how to do it?
Thanks in advance for anyone who manages to answer this.
Matt.
View 6 Replies
View Related
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> </td><td> </td><td>4</td></tr>
<tr><td> </td><td>Rm 34A Total</td><td>10</td></tr>
<tr><td> </td><td>Rm 50</td><td>7</td></tr>
<tr><td> </td><td>Rm 34A Total</td><td>7</td></tr>
<tr><td>Unit 1 Total</td><td> </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
May 31, 2007
Hello Everyone,
I have been tasked with creating a report using Reporting Services from an excel report that was wasting a lot of manual effort and time. I have searched boards and I am having the hardest time trying to find a way to do the following in Reporting Services within a Matrix:
Spring Summer Fall
2005 2006 2007 %Change 2005 2006 2007 % Change 2005 2006 2007 %Change
Reponses 219 310 410 100 100 200 300 100 2000 3000 200 -2800
FRC% 102% 103% 200% 97% 23% 25% 10% -15% 20% 32% 10% -22%
===========================================================================================
I can not find out how to do a percent change (the column in red) subtracting just the final or most right two columns of the resulting matrix columns, not an average or some other function over the entire row....
Please help, I know others have had the same issue...
Thank You,
View 4 Replies
View Related
Jun 29, 2007
Hi,
Could someone help with the following problem, I what to create a cross-tab report of the following data, grouped by Colour:
RAW DATA
Name
Colour
Sarah
Black
Kim
Red
Jane
Black
Jane
Pink
Robert
Yellow
Tom
Green
Tom
Black
Billy
Black
Sarah
Black
Sarah
Black
Tom
Pink
Kim
Pink
Robert
Black
Group Colours by Names Report
Number
Proportions %
Name
Black
Green
Pink
Red
Yellow
Total by Name
Black
Green
Pink
Red
Yellow
Billy
1
1
100.00%
0.00%
0.00%
0.00%
0.00%
Jane
1
1
2
50.00%
0.00%
50.00%
0.00%
0.00%
Kim
1
1
2
0.00%
0.00%
50.00%
50.00%
0.00%
Robert
1
1
2
50.00%
0.00%
0.00%
0.00%
50.00%
Sarah
3
3
100.00%
0.00%
0.00%
0.00%
0.00%
Tom
1
1
1
3
33.33%
33.33%
33.33%
0.00%
0.00%
I want to produce a Matrix cross-tab report, like the above, within Reporting Services. Any suggestions welcome
Many Thanks,
Radha
View 5 Replies
View Related
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
Jun 22, 2015
I have got this matrix and I am trying to calculate the average amount of working days in a month. At the moment, I have divided the total number of jobs by 21 for every month which is a hard coded value. However, I am not sure how to retrieve this value dynamically. Is there any formula that can find out the working days?
View 7 Replies
View Related
Nov 26, 2007
Hello.
I hope to explain myself well - I want to make a matrix with two rows.
Lats say my data is this:
I hava a list of months and in every month I have a number of pepole and there age.
How can I show this in a matrix?
It need to be in a matrix since I need the columns to expand acording to the month but I don't know how to create two diffrent rows in my matrix.
The data should look like this:
10/06 11/06 12/06 01/07 02/07 03/7 04/07 .....
num 5 1 2 5 4 5 7 .....
age 16.1 25 18.5 14.8 25.5 20.5 18.5 .....
Thanks for any help.
View 3 Replies
View Related
