Hide matrix rows?

Hi,

can somebody help me figuring out if the following is possible?
I have a matrix creating weeks out on "x-axis" and projects at "y-axis". For each project have i specified three rows, sum(Fields!hours.Value), sum(Fields!used.Value) and a total saying (sum)Fields!used.Value - sum(Fields!hours.Value).

12 13 14 15 16
A 3 4 5 3 4
2 4 3 4 1
-1 0 -2 1 -3

B ... and so forth...

What i would like is to have only the total row displayed initially, and the through drill-down on clicking A being able to se the two columns used to calculate the total.

I can't seem to figure it out.

Hope somebody out there has an idea for this - or can say that it cant be done for sure.. :-)

Best RegardsYou need to set the inner group (in the group properties) to be initially hidden and toggle the group visibility on the textbox. Make sure you have subtotals turned on (click on the outer group and select 'display subtotals').|||Thanks for the answer, but I can't seem to get this right.

I think maybe the problem is that i don't have anything to group for. Theres nothing that i can group so that the three rows becomes one. They are all just different sums of the one row in the dataset.

Am i getting it wrong? If i select one of the three fields i cant seem to select the "toggle visibility".

Can you follow me?|||

You always have a group in the matrix. If you aren't using any grouping, then you probably should be using a table dataregion. Can you bring up the matrix properties? Also, you could give more of an idea what you are trying to do.

|||

Hi Brian,

I have a similar problem and I wandered if you answer is what I might possibly need, but I haven't been able to implement it.

This is the part of my report. I have first group row being same company branch, second group row are clients and the third one is destinations where those clients were travelling. The measurement is the amount they spent on those destinations. This is the expanded version which look as it should. But look below for not expanded version and that is the one I have problem with.

Leisure

2005

2006

2007

Al

X company

AU

751458.06

298819.34

NZ

302619.79

121497.81

0

US/CA

270860.44

218673.12

SPAC

172093.03

96573.88

ASIA

164682.67

81023.22

UK/EUR

932738.37

481519.72

OTH

54430.17

62402.62

Total

2648882.53

1360509.71

0

Living Options Charitable Trust

AU

Total

Total

2657071.43

1360509.71

0

What I would like to see in not expanded version is just totals for the group rows. But what I am getting is suppressed description of the group rows but expended amounts as in the previous version. There is a little + next to Al in my version to show it is not expended version but for some reson it didn't come in here.

Leisure

2005

2006

2007

Al

751458.06

298819.34

302619.79

121497.81

0

270860.44

218673.12

172093.03

96573.88

164682.67

81023.22

932738.37

481519.72

54430.17

62402.62

Total

2648882.53

1360509.71

0

Total

Total

2657071.43

1360509.71

0

Interesting enough is that when exported into excel it works as it should be but in to Reporting Services as well.

Thanks a lot

Cheers

Marina

|||

I think I found out what my problem is. I was setting up the visability on the field instead of on the group.

Thanks

Marina

|||

Hi!!, what happend with you repor, t could you find an answer? I have the same question.

sql

Hide matrix rows?

Hi,

can somebody help me figuring out if the following is possible?
I have a matrix creating weeks out on "x-axis" and projects at "y-axis". For each project have i specified three rows, sum(Fields!hours.Value), sum(Fields!used.Value) and a total saying (sum)Fields!used.Value - sum(Fields!hours.Value).

12 13 14 15 16
A 3 4 5 3 4
2 4 3 4 1
-1 0 -2 1 -3

B ... and so forth...

What i would like is to have only the total row displayed initially, and the through drill-down on clicking A being able to se the two columns used to calculate the total.

I can't seem to figure it out.

Hope somebody out there has an idea for this - or can say that it cant be done for sure.. :-)

Best RegardsYou need to set the inner group (in the group properties) to be initially hidden and toggle the group visibility on the textbox. Make sure you have subtotals turned on (click on the outer group and select 'display subtotals').|||Thanks for the answer, but I can't seem to get this right.

I think maybe the problem is that i don't have anything to group for. Theres nothing that i can group so that the three rows becomes one. They are all just different sums of the one row in the dataset.

Am i getting it wrong? If i select one of the three fields i cant seem to select the "toggle visibility".

Can you follow me?|||

You always have a group in the matrix. If you aren't using any grouping, then you probably should be using a table dataregion. Can you bring up the matrix properties? Also, you could give more of an idea what you are trying to do.

|||

Hi Brian,

I have a similar problem and I wandered if you answer is what I might possibly need, but I haven't been able to implement it.

This is the part of my report. I have first group row being same company branch, second group row are clients and the third one is destinations where those clients were travelling. The measurement is the amount they spent on those destinations. This is the expanded version which look as it should. But look below for not expanded version and that is the one I have problem with.

Leisure 2005 2006 2007 Al X company AU 751458.06 298819.34 NZ 302619.79 121497.81 0 US/CA 270860.44 218673.12 SPAC 172093.03 96573.88 ASIA 164682.67 81023.22 UK/EUR 932738.37 481519.72 OTH 54430.17 62402.62 Total 2648882.53 1360509.71 0 Living Options Charitable Trust AU Total Total 2657071.43 1360509.71 0

What I would like to see in not expanded version is just totals for the group rows. But what I am getting is suppressed description of the group rows but expended amounts as in the previous version. There is a little + next to Al in my version to show it is not expended version but for some reson it didn't come in here.

Leisure 2005 2006 2007 Al 751458.06 298819.34 302619.79 121497.81 0 270860.44 218673.12 172093.03 96573.88 164682.67 81023.22 932738.37 481519.72 54430.17 62402.62 Total 2648882.53 1360509.71 0 Total Total 2657071.43 1360509.71 0

Interesting enough is that when exported into excel it works as it should be but in to Reporting Services as well.

Thanks a lot

Cheers

Marina

|||

I think I found out what my problem is. I was setting up the visability on the field instead of on the group.

Thanks

Marina

|||

Hi!!, what happend with you repor, t could you find an answer? I have the same question.

Hide matrix rows?

Hi,

can somebody help me figuring out if the following is possible?
I have a matrix creating weeks out on "x-axis" and projects at "y-axis". For each project have i specified three rows, sum(Fields!hours.Value), sum(Fields!used.Value) and a total saying (sum)Fields!used.Value - sum(Fields!hours.Value).

12 13 14 15 16
A 3 4 5 3 4
2 4 3 4 1
-1 0 -2 1 -3

B ... and so forth...

What i would like is to have only the total row displayed initially, and the through drill-down on clicking A being able to se the two columns used to calculate the total.

I can't seem to figure it out.

Hope somebody out there has an idea for this - or can say that it cant be done for sure.. :-)

Best RegardsYou need to set the inner group (in the group properties) to be initially hidden and toggle the group visibility on the textbox. Make sure you have subtotals turned on (click on the outer group and select 'display subtotals').|||Thanks for the answer, but I can't seem to get this right.

I think maybe the problem is that i don't have anything to group for. Theres nothing that i can group so that the three rows becomes one. They are all just different sums of the one row in the dataset.

Am i getting it wrong? If i select one of the three fields i cant seem to select the "toggle visibility".

Can you follow me?|||

You always have a group in the matrix. If you aren't using any grouping, then you probably should be using a table dataregion. Can you bring up the matrix properties? Also, you could give more of an idea what you are trying to do.

|||

Hi Brian,

I have a similar problem and I wandered if you answer is what I might possibly need, but I haven't been able to implement it.

This is the part of my report. I have first group row being same company branch, second group row are clients and the third one is destinations where those clients were travelling. The measurement is the amount they spent on those destinations. This is the expanded version which look as it should. But look below for not expanded version and that is the one I have problem with.

Leisure 2005 2006 2007 Al X company AU 751458.06 298819.34 NZ 302619.79 121497.81 0 US/CA 270860.44 218673.12 SPAC 172093.03 96573.88 ASIA 164682.67 81023.22 UK/EUR 932738.37 481519.72 OTH 54430.17 62402.62 Total 2648882.53 1360509.71 0 Living Options Charitable Trust AU Total Total 2657071.43 1360509.71 0

What I would like to see in not expanded version is just totals for the group rows. But what I am getting is suppressed description of the group rows but expended amounts as in the previous version. There is a little + next to Al in my version to show it is not expended version but for some reson it didn't come in here.

Leisure 2005 2006 2007 Al 751458.06 298819.34 302619.79 121497.81 0 270860.44 218673.12 172093.03 96573.88 164682.67 81023.22 932738.37 481519.72 54430.17 62402.62 Total 2648882.53 1360509.71 0 Total Total 2657071.43 1360509.71 0

Interesting enough is that when exported into excel it works as it should be but in to Reporting Services as well.

Thanks a lot

Cheers

Marina

|||

I think I found out what my problem is. I was setting up the visability on the field instead of on the group.

Thanks

Marina

|||

Hi!!, what happend with you repor, t could you find an answer? I have the same question.

Hide Duplicates Attribute Not working

We are creating a matrix report with multiple row headers. There are
duplicate values in the first column. The duplicate values are hidden but we
would like them to be displayed. The â'HideDuplicatesâ' attribute is
unchecked. Ultimately, this report will be exported to Excel and we will be
using these column values to filter the data so I need values in every column
for every row. Is there another attribute that we are missing?
Thanks,
TylerHi Tyler
Did you find a solution to this problem? We have the exact same requirement
- need to output a matrix report to Excel with all cells showing.
Thanks
"Tyler Allbritton" wrote:
> We are creating a matrix report with multiple row headers. There are
> duplicate values in the first column. The duplicate values are hidden but we
> would like them to be displayed. The â'HideDuplicatesâ' attribute is
> unchecked. Ultimately, this report will be exported to Excel and we will be
> using these column values to filter the data so I need values in every column
> for every row. Is there another attribute that we are missing?
> Thanks,
> Tyler|||No - I'm an MSDN subscriber, so I expected a response from MS, but nothing.
"Phil Jeffrey" wrote:
> Hi Tyler
> Did you find a solution to this problem? We have the exact same requirement
> - need to output a matrix report to Excel with all cells showing.
> Thanks
>
> "Tyler Allbritton" wrote:
> > We are creating a matrix report with multiple row headers. There are
> > duplicate values in the first column. The duplicate values are hidden but we
> > would like them to be displayed. The â'HideDuplicatesâ' attribute is
> > unchecked. Ultimately, this report will be exported to Excel and we will be
> > using these column values to filter the data so I need values in every column
> > for every row. Is there another attribute that we are missing?
> >
> > Thanks,
> > Tyler

Hewlp needed creating a stored proceedure

Can someone please help me...I have created a DNN module that works onthe test site but when I upload the module zip to a new site I get anerror on creating my stored proceedure as follows:

StartJobBegin Sql executionInfoExecuting 01.00.00.SqlDataProviderStartJobStart Sql execution: 01.00.00.SqlDataProvider fileFailureSQL Execution resulted infollowing Exceptions: System.Data.SqlClient.SqlException: Line 25: Incorrect syntax near '@.Str_Title'. Line 51: Incorrect syntax near '@.Str_Title'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE PROCEDURE dbo. ListTAS_Journal @.PortalID int, @.SortOrder tinyint = NULL, @.Str_Title varchar(100) = '', @.Str_Text varchar(100) = '' AS IF ISNULL(@.Str_Title, '') = '' or ISNULL(@.Str_Text, '') = '' SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') AND Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') OR Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESCEndJobEnd Sql execution: 01.00.00.SqlDataProvider file

The SP looks like this:

/* --------------------------
/ ListTAS_Journal
/ -------------------------- */
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier} ListTAS_Journal
@.PortalID int,
@.SortOrder tinyint = NULL,
@.Str_Title varchar(100) = '',
@.Str_Text varchar(100) = ''
AS

IF ISNULL(@.Str_Title, '') = '' or ISNULL(@.Str_Text, '') = ''

SELECT
[EntryID],
[PortalID],
[ModuleID],
[Title],
[Text],
[DateAdded],
[DateMod],
[Owner],
[Access]
FROM
TAS_Journal
WHERE
PortalID = @.PortalID AND
(Title like COALESCE('%' + @.Str_Title + '%' ,Title , '') AND
Text like COALESCE('%' + @.Str_Text + '%' ,Text, ''))

ORDER BY
(CASE
WHEN @.SortOrder = 1 THEN DateAdded
WHEN @.SortOrder = 0 THEN DateMod
END) DESC, EntryID DESC
else
/***Select from either field
***/
SELECT
[EntryID],
[PortalID],
[ModuleID],
[Title],
[Text],
[DateAdded],
[DateMod],
[Owner],
[Access]
FROM
TAS_Journal
WHERE
PortalID = @.PortalID AND
(Title like COALESCE('%' + @.Str_Title + '%' ,Title , '') OR
Text like COALESCE('%' + @.Str_Text + '%' ,Text, ''))

ORDER BY
(CASE
WHEN @.SortOrder = 1 THEN DateAdded
WHEN @.SortOrder = 0 THEN DateMod
END) DESC, EntryID DESC
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

This SP works on the test site.
Any help would be creatly apreciated

Mark

CREATE PROCEDURE dbo. ListTAS_Journal @.PortalID int, @.SortOrder tinyint = NULL, @.Str_Title varchar(100) = '', @.Str_Text varchar(100) = '' AS IF ISNULL(@.Str_Title, '') = '' or ISNULL(@.Str_Text, '') = '' SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') AND Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @.PortalID AND (Title like COALESCE('%' @.Str_Title '%' ,Title , '') OR Text like COALESCE('%' @.Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @.SortOrder = 1 THEN DateAdded WHEN @.SortOrder = 0 THEN DateMod END) DESC, EntryID DESC

Notice, your pluses are missing.

|||Hi Thanks for the reply,

Yes the Pluses are in the sql file if you look at my first post but theerror is reporting they are not there so DNN must be stripping them outfor some reason!

Mark

Help: Multiple sub-reports creating extra blank pages on print

I have a project with multiple pages of reports. I am using one main page with all the other reports added through the sub-report control. The only problem is, after the first page and a couple of the other pages, it inserts a blank page in the print preview. If I view and print the reports seperately, and not all on the main report page they print fine, they don't roll over, don't print any extra blank pages. This only occurs when I add all them all as sub-reports. The last 5 pages don't suffer from this problem either.

Can anyone think of what the problem could be, or a way to solve it? All the pages are 8.5x11 with no margins. As I said, the pages alone print fine without any extra blank pages, so I'm confused why they would create extra white pages when added as sub-reports. The other thing is, I thought that perhaps pages having text that go all the way to the bottom were causing the issue, but some of the reports don't have text that extend to the bottom, so that rules that out.

I'd really appreciate any help. Thanks in advance.

We have seen a similar problem before. Can you try making the subreport element in the main report wider (enough to contain the entire subreport) and see that helps?|||The sub elements are all 8.5 inch,.25inch. The main report is 8.5,11 as are all the other reports. I initially had just placed them on there and they were seperated by a grid space or so, and their heights were all different (widths all stayed constant at 8.5). When I did this, though, the formatting was even worse and some of the reports would run into eachother. I would try making the main report length the size of all the subreports * 11 but I have 21 pages of reports and the maximum length is 160 inches.

Right now my main report is setup with each sub report element set at 8.5,.25 and starting from the very top of the main report, they are placed one below the other right where the previous one ends.
|||Any MSFT people who can provide any help? :||||I'm sorry to bump this again, but I could really use some assistance. I've tried everything and I can't get it to work.|||Can you attach your rdls? That'd help us repro and investigate the problem.