Is RS2005's Matrix Data Region Feature Broken, Or Subject To Certain Limitations?
Dec 25, 2007
I'm consistently getting >10 minute render times in a matrix based report whose underlying query only takes 1-2 seconds to run in both RS's data tab and mgt studio. The bad render times occur in preview tab, Report Viewer and when requested from Report Manager. I believe most of the time being taken is RS's attempt to pivot and present the data.
I can run a homegrown query that pivots (with unknown number of cols) the data itself in under 4 seconds but would be concerned about mapping an unknown number of columns to a table region in RS.
The matrix I'm testing with is 67 columns across (just over 2 months of daily columns) and is supposed to be 207 rows long. There is no aggregation required by RS when it is pivoting the data because each intersection occurs at most one time in the query's result set. The number of data points actually being "bucketed" is around 13,800 values. Row headers consist of 3 columns.
RS is also cutting off the number of rows it is supposed to be showing. The number of rows cutoff seems to be 100% repeatable based on the date range chosen. RS doesnt tell me when it cuts off rows that should have been displayed. From what I can tell so far, it never cuts off columns.
The number of rows cut off seems consistent across designer's preview, report viewer and when requested thru report manager. The "run" command (exclamation icon in data tab) seems to always return the correct number of unpivoted rows, just as in mgt studio.
Are there known bugs and or limitations in RS's matrix data region feature? Are there some settings or workarounds that might make it work more to my liking?
View 13 Replies
ADVERTISEMENT
Jun 6, 2007
Hello All,
In my report, I have a Matrix control placed next to a table. It renders properly and displays data aligned in two controls in Visual Studio Preview. However, when I deploy to production, it wraps the matrix control below the table, in fact puts the entire matrix control underneath the table. Why such strange behavior and not in the Preview of the report but only in Production? Any ideas how to fix this?
In VS Env. Preview --> [Table][Matrix]
In Report Svr Prod --> [Table]
[Matrix]
Thanks,
View 6 Replies
View Related
Dec 25, 2007
i ran a preview of a matrix based report whose column headers are dates. The dates seem to be displaying in a somewhat (not completely) random order from left to right. How can I ensure that they display chronologically from left to right?
View 1 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
Sep 5, 2007
Hi,I am studying data mining features of SSAS and for a workshop I'vecreated 2 views derived from vTargetMail view of AdventureWorksDW.Train data consists every record except those in Pacific, and testview consists only records from Pacific area.1. I've created a mining structure based on Decision Tree and selectedBikeBuyer as predictable column.2. According to input column suggestions, I've selected Age,Eng.Education, NumberCarsOwned, YearlyIncome, CommuteDistance,NumberChildsatHome and TotalChildren as input columns,3. I've modified no other setting, and deployed project.I can get training results in decision tree browser and dependencynetwork (and both seem to give rather logical results) however, when Itry to browse lift chart or classification matrix I get an emptyclass.matr. and a lift chart of a single 45 degree line.Am I missing a step, or must I do some fine-tuning on (what)parameters?Thanks...
View 2 Replies
View Related
Sep 5, 2007
Hi,
I am studying data mining features of SSAS and for a workshop I've
created 2 views derived from vTargetMail view of AdventureWorksDW.
Train data consists every record except those in Pacific, and test
view consists only records from Pacific area.
1. I've created a mining structure based on Decision Tree and selected
BikeBuyer as predictable column.
2. According to input column suggestions, I've selected Age,
Eng.Education, NumberCarsOwned, YearlyIncome, CommuteDistance,
NumberChildsatHome and TotalChildren as input columns,
3. I've modified no other setting, and deployed project.
I can get training results in decision tree browser and dependency
network (and both seem to give rather logical results) however, when I
try to browse lift chart or classification matrix I get an empty
class.matr. and a lift chart of a single 45 degree line.
Am I missing a step, or must I do some fine-tuning on (what)
parameters?
View 3 Replies
View Related
Feb 14, 2007
can anyone help me about reporting services on how to sum the coloumn and put it on a textbox... but the case is like this,
Stud Name Fee Name Fee Amt. Amt Paid
John Doe Registration 1,000 1,000
Tuition Fee 5,000 3,500
1,500
Jane Bake Tuition Fee 5,000 5,000
SubTotal 16,000 11,000
when i aggregate the Fee Amt. using Sum function i get the result of 16,000 because john doe break his payment in to two(it should be 11,000 only). any help would be appreciated.
Thanks...
View 1 Replies
View Related
Mar 26, 2008
I've been having a problem getting the font-color right on a matrix subtotal. I searched around but could not find anything else describing the issue I'm having. Basically, the problem is stated as such:
IF a matrix subtotal row has conditional coloring based on the data, THEN changes to the subtotal format via "the green arrow" like bordercolor, backgroundcolor, fontstyle, etc, override the conditional formatting.
This only happens when the report is deployed and viewed in a browser. In Visual Studio, the formatting appears as one would expect.
It also only happens when the color is determined conditionally. If the color is explicitly stated, the subtotal format behaves.
Heres a better example:
I was making a matrix with a subtotal that has the classic "red color when the revenue amount is negative" situation:
COLOR:
=iif(Sum(Fields!rev.value) < 0, "red", "black")
All data cells, whether in subtotal or datasection, would be red if the revenue amount was less then zero. At this point, the matrix was almost newly created and this was the *only* formatting applied so far.
Next, I changed the subtotal formating with the green arrow, giving the total and subtotal cells a grey background.
At this point, the report looks fine in the Visual Studio Preview. A negative subtotal is still red. But, when deployed and accessed via IE, the subtotals are black (default) no matter what.
Is this a known issue?? It seems like a bug to me, not a feauture, for at least a couple reasons:
It only happens in a deployed browser, not the designer.
It only happens w/ conditional formatingThe version I'm using is 9.00.3215.00. I also checked on another box w/ version 9.00.3054.00 and it looks like the same problem.
For now, I'm working around it by keeping the subtotal formatting absolutely clean. All formating done in the subtotal is done through =IIF( INSCOPE("...") , , ) checks. This is annoying because the matrix I'm working on isn't anywhere near as simple as the example above, it has crazy row and column groupings with numerous subtotals and pre-existing conditional border/background/color formats. Any ideas?
View 1 Replies
View Related
Jan 30, 2008
Hi,
I have a table1 which points to a dataset1
I was wondering how I use the 'data region' dropdown selection which is present when i click the properties of a cell in table1, it always appear to be blank.
I would like to know how this works as i feel it will be useful in specifying various data regions within a table, I cant seem to find any relevant information on it, can someone tell me how this works ?
View 3 Replies
View Related
Jul 20, 2005
On the subject of Data Warehouses, Data Cubes & OLAP….I would like to speak frankly about Data Warehouses, Data Cubes andOLAP (on-line analytical processing). Has it dawned on anyone elsethat these buzz words were created by some geek who decided to take astab at marketing? Knowing that to the backwoods manager who knowslittle of technology that new innovative names for old concepts wouldhelp to sale their products.I mean seriously, what is the story here? In a nut shell, and pleasestop me if you disagree, but isn’t a data warehouse simply adatabase? Can’t you do everything on a conventional databaselike SQL Server, Oracle or DB2 that you can do on these newproprietary Data Warehouse constructs? I mean who are they trying tofool?Take a look, for instance, at Data Cubes. Who hasn’t noticedthe striking similarity between data cubes and views used in all themore robust databases? Also, what about OLAP? OLAP is nothing morethan a report generator. There’s nothing you can do with thesemillion dollar price tagged Data Warehouse total solution packagesthat I can’t do with SQL Server, Oracle or DB2…for thatmatter Microsoft Access.As an example some sales people for Metadata Corporation has the VicePresident of I.T. in Nashville, for Healthspring, sold on their totalsolution data respository which is such a scam. All they had to dowas throw a couple of buzzwords at him and they have him hypnotized.Personally, I feel that these kinds of marketing practices undermineour industry. It helps to unravel what little standards orconsistency we have. What do you guys think?Stuart
View 7 Replies
View Related
May 5, 2008
Hi Everyone. How could I configure data region ( table, matrix ) to be repeated on each report's page?
View 8 Replies
View Related
Jul 9, 2007
I am trying to display hirearchical data using data region. For eg. the first level will be a list of customer names. The second level will be a list of Invoices under each customer. The third level will be a list of product names that were sold under each invoice.
I used a list for the data region and in each hireachy, there is a textbox in each list to display the data. I am trying to associate each data region with a datatable (I will provide the logic to populate the datatable with appropiate data). I do not want the ReportViewer to interact directly with a database because this is a 3 tier design and I am using the ReportViewer in local mode. I couldn't find any example of these. Can anybody help? Also I would like to see an example of the .rdlc file in these situation. Thanks very much.
Kam
View 4 Replies
View Related
Jul 28, 2007
Hi All, Would appreciate some assistance with the attached, I'm using some fairly complicated recordsets (ASP VBScript) for a property search website, I have a page where a user selects search by County this in turn takes them to a page which lists all the towns in the county and number of live properties in each, I'm using 4 recordsets for this - 3 of which list the towns (townsA2I, townsJ2R & townsS2Z) the 4th is a Property Count, examples of the recordsets are below (using townsAtoI and LiveProperties -
TownsA2I
<%
Dim TownA2I
Dim TownA2I_numRows
Set TownA2I = Server.CreateObject("ADODB.Recordset")
TownA2I.ActiveConnection = MM_recruta2_STRING
TownA2I.Source = "SELECT towncountyID, Town, County FROM dbo.easytolettowncounty WHERE Town LIKE 'A%' AND County = '" + Replace(TownA2I__MMColParam, "'", "''") + "' OR Town LIKE 'B%' AND County = '" + Replace(TownA2I__MMColParam1, "'", "''") + "' OR Town LIKE 'C%' AND County = '" + Replace(TownA2I__MMColParam2, "'", "''") + "' OR Town LIKE 'D%' AND County = '" + Replace(TownA2I__MMColParam3, "'", "''") + "' OR Town LIKE 'E%' AND County = '" + Replace(TownA2I__MMColParam4, "'", "''") + "' OR Town LIKE 'F%' AND County = '" + Replace(TownA2I__MMColParam5, "'", "''") + "' OR Town LIKE 'G%' AND County = '" + Replace(TownA2I__MMColParam6, "'", "''") + "' OR Town LIKE 'H%' AND County = '" + Replace(TownA2I__MMColParam7, "'", "''") + "' OR Town LIKE 'I%' AND County = '" + Replace(TownA2I__MMColParam8, "'", "''") + "' ORDER BY Town ASC"
TownA2I.CursorType = 0
TownA2I.CursorLocation = 2
TownA2I.LockType = 1
TownA2I.Open()
TownA2I_numRows = 0
%>
LiveProperties
<%
Dim LiveProperties
Dim LiveProperties_numRows
Sub sLivePropertyCount(vLocation)
vCount=0
Set LiveProperties = Server.CreateObject("ADODB.Recordset")
LiveProperties.ActiveConnection = MM_recruta2_STRING
LiveProperties.Source = "SELECT COUNT(PropertyID) As NumberofProperties, propertylive, propertylocation FROM dbo.easytoletproperty WHERE propertylive = 'y' AND propertylocation = " & vLocation & " GROUP BY propertylocation, propertylive"
LiveProperties.CursorType = 0
LiveProperties.CursorLocation = 2
LiveProperties.LockType = 1
LiveProperties.Open()
vCount=(LiveProperties("NumberofProperties"))
Response.write(vCount)
LiveProperties.Close()
Set LiveProperties = Nothing
end sub
LiveProperties_numRows = 0
%>
I've tried following the tutorial here http://www.webthang.co.uk/tuts/tuts_dmx/rob9/rob9.asp but when i review the page i get the following error -
"Microsoft OLE DB Provider for SQL Server error '80040e14'
Invalid column name 'Barnet'.
/PropertiesbyTown1.asp, line 338 "
Where line 338"
Where line 338 is the "LiveProperties.Open()" of the above recordset, when i try to view bindings on this recordset Dreamweaver gives me the following errors -
"Column 'dbo.easytoletproperty.propertylive' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Column 'dbo.easytoletproperty.propertylocation' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause."
I would appreciate any assistance -
View 1 Replies
View Related
Jan 9, 2008
I have a report which produces invoices.
These invoices are more complex than your average ones.
The invoice is for one customer but many orders. Within the invoice, orders are grouped together by their delivery address which can be different for each order within the invoice. I throw a page for each seperate delivery account and sub-total by delivery account too. All this works fine and page breaks are all OK. However I also need a summary page at the end of the invoice showing 1 line per delivery account andf the sub-total for it, then I show a grand total.
To achieve this, I used a list control, grouping on Invoice number. within it I placed 2 tables. 1 is the main Invoice table, the 2nd is the summary table. I have a page break on a group in table 1 that controls the split on delivery account. I have a page breaks on table 2. Again this all works fine and paging is perfect.
Here's the crunch though! If the invoice only contains one delivery account, I need to suppress the summary table. This I can do BUT I still get the page throw for it, effectively giving blank pages. The page break is triggered regardless of whether the containing control is visible or not.
I'm pretty accomplished with RS and I've tried all sorts of tricks and hacks to get round it but it seems a page break is added regardless of whether the control triggering it is visible or not!
I've tried rectangles both with the table in it and outside. And with the page break on the rectangle and when that didn't work , on the table inside it.
Any one else have any ideas on this.
Cheers
Chris
View 6 Replies
View Related
Apr 11, 2008
I've never attempted to use the "Repeat Report Item with Data Region on Every Page " feature before. I'm trying to use it on text boxes with the data region being a matrix. I have a report that is two pages, and the text boxes are only appearing on the first page. Also, the matrix is appearing with zero space between it and the header. Any ideas?
Lindsay
View 1 Replies
View Related
Aug 18, 2015
I pull data from Sql Server through the query, I want to pass the region parameter to the power pivot connection query. So that I can automatically pull the required region data. The parameter should pick the value from the excel range. And also how to control this through VBA
View 4 Replies
View Related
Sep 21, 2007
Hi!
Is it possible to generate a script, that extracts every bit of data it can, from tables which suffers from latch errors;
DBCC CHECKDB ->
Msg 7985, Level 16, State 2, Line 1
System table pre-checks: Object ID 4. Could not read and latch page (1:403) with latch type SH. Check statement terminated due to unrepairable error.
So bad news on that one. There are no backup available that I know off, so all bets are off on that part of the recovery process.
The "good news" part of the problem, is that it is possible to extract some data out of the table, before SQL server 2005 returns the error. So what I want to do, is get every bit out I can, doing it somehow automatically, so I don't have to run bcp on 180 tables :).
Presumeably, this is quite possible, but how?
NB. If I do a complete basic SELECT * FROM tbl1, it returns data, then errors out, but what about the data after the last row SELECT returns? Is it possible, that there might be data in the table that are recoverable after the 'broken row(s)'?
View 20 Replies
View Related
Apr 7, 2007
I have software that uses SQL Server Express as it's database. I am only able to import so many records until it stops and fails to allow me to import anymore.
I'm very new at this but, is there some type of limitation on Table size that's preventing me from importing anymore data?
I'd really appreciate it if someone could help me,
Thanks... Bill
View 10 Replies
View Related
Feb 2, 2015
I'm having an issue in encrypting large documents. I know that previous editions ENCRYPTBYKEY had a maximum size of 8,000 byte limitation. Does SQL Server 2014 have any new features that overcomes this limitation?
Using
SQL Server 2014 Std
Symmetric key with AES_256
View 2 Replies
View Related
May 28, 2008
Dear all,
we have tables with many image columns. We fill these image columns via ODBC and SQLPutData as described in MSDN etc (using SQL_LEN_DATA_AT_EXEC(...), calling SQLParamData and sending the data in chunks of 4096 bytes when receiving SQL_NEED_DATA).
The SQLPutData call fails under the following conditions with sqlstate 08S01
- The database resides on SQL Server 2000
- The driver is SQL Native Client
- The table consists e.g. of one Identity column (key column) and nine image columns
- The data to be inserted are nine blocks of data with the following byte size:
1: 6781262
2: 119454
3: 269
4: 7611
5: 120054
6: 269
7: 8172
8: 120054
9: 269
The content of the data does not matter, (it happens also if only zero bytes are written), nor does the data origin (file or memory).
All data blocks including no 7 are inserted. If the first chunk of data block 8 should be written with SQLPutData the function fails and the connection is broken. There are errors such as "broken pipe" or "I/O error" depending on the used network protocol.
If data no 7 consists of 8173 bytes instead of 8172 all works again.
(Changing the 4096 chunk size length does not help)
Has anybody encountered this or a similar phenomenon?
Thank you
Eartha
View 7 Replies
View Related
Mar 13, 2008
Ok so facebook groups have 100,000's of members. Members can be part of an unlimited number of groups, and a group can have an unlimited number of members.
Comma Deliniated String seems absurd. Many-2-Many Database relationship seems like it won't scale well t the 10's of thousands and 100's of thousands of members (especially if you have 1000-5000 groups). A table for each group would work but thats a bit over the top in my opinion. XML file doesn't seem to be any better than the above options.
I am no database guru, but I can't figure out a scalable method of doing this, be it with or without a database. I need something that can support 10 groups that have 20 members each OR 1000 groups with 100,000 members each.
Any help, suggestions, or kicked in the right direction would be most appreciated.
View 3 Replies
View Related
Feb 21, 2008
When trying to process my minding model (using Association Rules) i receive the following errors
Errors and Warnings from Response
Error (Data mining): The 'Items Recommendations' mining model has 64675 attributes. This number of attributes exceeds the attribute limit of 5000 allowed by the current version of the algorithm associated with the mining model.
Errors related to feature availability and configuration: The 'Unlimited data mining attributes.' feature is not included in the 'Standard Edition' SKU.
I searched through the internet and found that a hotfix can be installed to make it work (http://support.microsoft.com/kb/932609). I emailed microsoft and they provided me back with the hotfix and a password.
I tried to install the fix but i couldnt. It needed a prerequisit fix. So i installed SQL Server 2005 Service Pack 2 from (http://support.microsoft.com/?kbid=921896)
Then tried to install the fix for my unlimited attributes problem.
I tried to process my module after installing the fix and restarting my computer, but am still gettin the same error.I created another new model but the problem still exists.
Am i missing something here? Any idea to make it work?
Is there any alternative? I need to use all the elements in my datasource view.
View 3 Replies
View Related
May 30, 2007
Hi,
Is it possible to create Expand/Collapse functionality for the grouped data in Table and Matrix data regions? Essentially, the idea is for the user to be able to see the group/subgroup data if she wishes to by clicking on (+/-) symbols, as is usually the case in Tree View style data grid control in web apps. Any ideas how to accomplish the same in reporting services?
Thanks.
View 1 Replies
View Related
Jul 23, 2007
I'm trying to use the SSIS Execute SQL Task to pull XML from a SQL 2005 database table. The SQL is of the following form:
SELECT
(
SELECT
MT.MessageId 'MessageId',
MT.MessageType 'MessageType',
FROM MessageTable MT
ORDER BY MT.messageid desc
FOR XML PATH('MessageStatus'), TYPE
)
FOR XML PATH('Report'), TYPE
For some reason I can only get this query to work if I use an ADO.NET connection type. If I try to use something like the OLEDB connection I get the following error:
<ROOT><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?></ROOT>
Can anyone tell me why the SELECT ... FOR XML PATH... seems only to work with ADO.NET connections?
Thanks
Walter
View 1 Replies
View Related
Oct 8, 2004
Question: Why is Graz a moderator on this forum? And on nothing else?
Brett
8-)
View 4 Replies
View Related
Feb 15, 2008
Microsoft products that are doing well tend to have very strong communities around them, and I am starting to get a bit disappointed with SQL-CLR. This forum seems fairly light, the links int he FAQ are broken, and the official SQL-CLR blog has no update since 2006..
Taking all of that into account I am forced to wonder, Is this a dead subject?
I am not trying to say that the people in this forum are doing something wrong, nor am i claiming that no one uses SQL-CLR (i am a user, thats why im here). But I did want to get the opinion of the group here of where they think this concept stands.
I think SQL-CLR can really be a good tool in the chest for various scenarios. I am for one using it to have a generic SQL-CLR Stored Procedure that can analyze small sets of data and move it into various buckets depending on validation rules built into the code. So far its working very well, but I admit it has not yet hit production so only time will tell. (Performance is always a concern of mine)
Regards,
Dmitry.
http://blog.lyalin.com
View 5 Replies
View Related
Dec 12, 2007
Hi all experts,
I have a result set like:
Project
Milestone
Info
Project1
M1
Info1
Project1
M1
Info2
Project1
M2
Info3
Project2
M1
Info4
Project2
M2
Info5
Project3
M1
Info6
I need to create a report like:
M1
M2
Project1
Info1
Info2
Info3
Project2
Info4
Info5
Project3
Info6
But while I use matrix to build this report, I got the result like:
M1
M2
Project1
Info1
Info3
Project2
Info4
Info5
Project3
Info6
The report will not show the multiple records on the row group "Project" like item "Info2".
After I referred to the similar problem mentioned on this forums, I tried to use "RowNumber("Matrix1_Project")" to resolve it. But still I got the result like:
M1
M2
Project1
RowNumer
Info1
RowNumer
Info1
RowNumer
Info3
Project2
RowNumer
Info4
RowNumer
Info5
Project3
RowNumer
Info6
It would be much appreciated if anyone could give me some hints to complete the report I need.
Thanks in advance,
Steve Wang 2007/12/12
View 5 Replies
View Related
Oct 19, 2015
create the column "Region" like this Picture?.
View 26 Replies
View Related
Oct 14, 2015
How to create column "Region" like this Picture.
View 16 Replies
View Related
Jul 31, 2007
I have a report that has several headings for users to click on to toggle visibility of the data regions that the headings represent. Several of the columns in the data tables have interactive sort enabled. However, every time the user clicks the sort indicator, the region collapses again and is hidden. So, ultimately for users the interactive sort becomes a two-click action, after which they have to visually re-orient.
I tried putting the table into a rectangle and toggling visibility of the rectangle instead of the table, but it exhibited the same behavior.
How can I get it so the regions do not get hidden just because the user sorts?
Thanks,
Joel
View 1 Replies
View Related
Mar 13, 2008
I would like to include some information in the subject line of a report subscription email. Right now the default subject line is something like "@ReportName was executed at @ExecutionTime". is there a way to use one of my report parameters in the subject? I tried something like "Thank you for your order @OrderNum", but that did not work.
Thanks,
Stuart Fox
View 4 Replies
View Related
Sep 24, 2007
Good Morning,
I have created a varible in a report which displays as a text box i.e contains data like "LWD Apps 450" where the value will change each day. The report is emiled to a list of users, is there a way to automatically enter this value so that it is in the subject bar when the email is automatically sent??
Thanks in advance.
Steve
View 1 Replies
View Related
May 3, 2007
Hi, all experts here,
Thanks a lot for your kind attention.
I have been trying to adjust the report regions fit into the whole report page. I could see the width and height property of the whole report page from the report property dialogue, but where can I find the width and height for the particular report region like the table? Then I could accordingly adjust the layout of both the report page and report region like table?
Hope my question is clear for your help.
With best regards,
Yours sincerely,
View 5 Replies
View Related
