Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
asuds
Starting Member
4 Posts |
 Posted - 2004-12-16 : 10:01:36
|
| Greetings.I am hoping somebody here can help me as I am a novice with vb syntax and what have you.I have a matrix with row and column groups. It looks like this in layout view: column group 1 data header1 dataheader2 row group1 rowgroup2 count() sum()Now the idea is that you get a drilldown-able matrix with totals based on row group1. You can toggle the visibilty of row group2 based on row group1.When row group2 is open, there is a list of items and a total at the bottom, like this when the report is previewed: column group 1 data header1 dataheader2 row group1 rowgroup2 (1)count() sum() rowgroup2 count() sum() rowgroup2 count() sum() group2total (2)count() sum()rowgroup1 rowgroup2 count() sum() grand totalNow I want to be able to navigate from the count cells to other reports that provide detail on the items that have been aggregrated. The problem comes when I try and pass parameters when coming from cell (1) - where I want a rowgroup1 AND rowgroup2 to be specified vs. coming from cell (2) - where I want rowgroup1 specified and rowgrop2 to be ALL or NULL something. Originally I had parameter2=rowgroup2field.value. If I try this, it will pass things correctly when there is a specific gowgourp2, but for the total cell (i.e. from cell (1)), but for cell (2) it sets the parameter to the first value of rowgroup2field contained withing the total -- the same parameters as passed when coming from cell (1).My current solution attempt is to put into the link parameters something like parameter2 = IIF(ReportItems!rowgroup2textbox.Visibility/Hidden/etc.=TRUE, do this, do that)Being brand new to vb net I am not sure of the right syntax for the ReportItems!name.property line, although I have tried most reasonable permutations. (where is a good on-line refernce?)Problems:1. I can't find a way to test the property of the text bow.2. I get an "The value expression for the textbox ‘textbox5’ references a field outside an aggregate function. Value expressions in matrix cells should be aggregates, to allow for subtotaling." error, although maybe this will go away with the right IIF statement3. I am not sure what the textbox is going to end up being named. I am currently trying the rowgroup2 text box in the layout view, but after the dynamic parameter goes to work there is going to be may rows, so what are they all named? My current plan if I get this far is to put an expression in the document map label like =cellROWNUMBER()COLUMNNUMER() -- I don't know the right syntx there yet, but that should be possible (getrownumber maybe)If anybody can help or provide some guidance on getting the same effect that would be fantastic. I have condsidered jsut making a table and putting lots of columns but that has some problems of its own and is less elegant, but I could probably effect a solution that way.Regards,-Andrewp.s. To add insult to injury I also want to be able to detect the total for the column group -- same problem though. |
|
|
asuds
Starting Member
4 Posts |
Posted - 2004-12-16 : 12:48:21
|
| I have an update. I figured out how to use ReportItems!specitic_textbox.Vale in an IIF and pass that on as a parameter, except that this still doesn' twork for the total cell (i.e. #2 in diagram above). This is a cell that is in that "grayed out" area of the matrix object area. Anybody have some insight into that?-Andrew |
 |
|
|
jhermiz
3564 Posts |
Posted - 2004-12-16 : 13:09:28
|
quote:
Originally posted by asuds
I have an update. I figured out how to use ReportItems!specitic_textbox.Vale in an IIF and pass that on as a parameter, except that this still doesn' twork for the total cell (i.e. #2 in diagram above). This is a cell that is in that "grayed out" area of the matrix object area. Anybody have some insight into that?-Andrew
In short (since I hate reading long posts including my own) what are you looking for? If all you need is a sum based on a condition you can in the details section place a text box with IIF(condition, 1, 0) and make this field invisible. IN the footer you can SUM the result set of these IIF's using=SUM(yourControl)Jon |
|
|
|
asuds
Starting Member
4 Posts |
Posted - 2004-12-16 : 13:16:22
|
| Short and sweet question:I have a cell in a matrix. The cell has a link to another report. I want to be able to vary the parameters passed based on whether the cell is the subtotal (or visible) for a row or column group in the matrix. I would be ok if I could address a cell by row and column number except I am a little worried about this whole... "Value expressions in matrix cells should be aggregates, to allow for subtotaling. " ... class of errors.-Andrew |
|
|
|
|
|
|
|
|
parameters based on location in matrix