Report Page Width with Dynamic columns through parameters

I found the following paragraph while searching on here:

************************************************************************
You could easily set up a parameter for each column and then display that column conditionally based on the parameter.

For instance, if you have a column that displays First Name, you could have a parameter called DisplayFirstName. Then in design view you'd select the whole FirstName column and in the Visibility-Hidden property set it to :

=iif( Parameters!DisplayFirstName.Value = false, true, false )

This could easily become a big, unwieldy report, though, if you have a great number of dynamic columns. Also, The width of the report is set at design time, and it includes the width of all your columns, not just the visible ones. This could cause you some pagination problems.
************************************************************************

That is exactly my problem. I have lots of dynamic columns which causes the width of the report to be wide. Thus even though at run time the report only shows columns within a page, the report itself consists of a lot of white spaces after the selected columns. Does anyone know a solution to this? If not, I guess creating the rdl with code manually is the only way? Please advise. Thanks in advance.

- Will

I summarize your requirement that I have understood and based on that I given you my comment if my understanding is wrong then let me know.

Your requirement is to design a report in which columns are visible on some condition or dynamically. You have design the report and set the column property visible = ‘TRUE’ OR FALSE based on the condition. Let assume the problem is that when col1 is visible that time col2 is invisible due to this a white space is left in place of col2 and you want to remove that white space.

My comment:

Take the above scenario if col1 is visible and col2 is invisible and vice a versa then place the col2 over col1 so then col2 is visible then col1 will be invisible and if the col1 is visible then col2 is invisible. And you will not see any white pace in the report.

|||Hi mverma,

First let me thank you for replying to me. Let me clarify my requirements just to make sure what you are proposing would solve my problem.

I have a report, which at design time, would span 2 physical pages (17 inches), with each column being the same width.
Now, the columns' visibility is toggled by parameters passed to the report page during run time.

Now the problem.
Lets say for one of the report we pass in parameters to toggle on columns 1,3,5,7, so the total width would be 8.5 inches, which would fit exactly on one page. So when choosing to print, it would print one page with the 4 columns (with no spaces between the columns), and one blank page. The blank page is printed because, during design time, the report is set to be 17 inches wide, thus even only 8.5 inches of the report contains columns, the whole 17 inches are still printed.

You solution seems to solve the problem of white spaces in between columns that are visible and invisible, but my problem is that though all the columns are neatly next to each other, spaces are still accounted for the invisible columns are at the end of the table.

Now, if I misunderstood your solution and it actually does solve my problem, could you please provide more details or a link as to how to "stack" one column on top of another during runtime. Nevertheless, thanks again for your reply.

- Will

|||

I have another option suppose according to the passing parameter report generate value for 1,3,5,7 etc and for next parameter value it will generate report based on 2,4,6,8. Then you can take two table one is design for 1,3,5,7 and next one is designed used by 2,4,6,8 column when parameter passed for 1,3,5,7 then set visibile='true' for this table and set visibile= 'false' for another table that we don't want. Place table one over other so that you need not to worry about the white space.

Does this help you to achive your requirment ?

Regards

Manoj Verma.

|||Hi Manoj Verma,

Thanks for replying. I do not think your suggestion would work because my example was a watered downed one. Our actual need is a 40 columns dynamic report, where users can choose any of the 40 columns. Thanks again for your input.

- Will

Report Page Width with Dynamic columns through parameters

I found the following paragraph while searching on here:

************************************************************************
You could easily set up a parameter for each column and then display that column conditionally based on the parameter.

For instance, if you have a column that displays First Name, you could have a parameter called DisplayFirstName. Then in design view you'd select the whole FirstName column and in the Visibility-Hidden property set it to :

=iif( Parameters!DisplayFirstName.Value = false, true, false )

This could easily become a big, unwieldy report, though, if you have a great number of dynamic columns. Also, The width of the report is set at design time, and it includes the width of all your columns, not just the visible ones. This could cause you some pagination problems.
************************************************************************

That is exactly my problem. I have lots of dynamic columns which causes the width of the report to be wide. Thus even though at run time the report only shows columns within a page, the report itself consists of a lot of white spaces after the selected columns. Does anyone know a solution to this? If not, I guess creating the rdl with code manually is the only way? Please advise. Thanks in advance.

- Will

I summarize your requirement that I have understood and based on that I given you my comment if my understanding is wrong then let me know.

Your requirement is to design a report in which columns are visible on some condition or dynamically. You have design the report and set the column property visible = ‘TRUE’ OR FALSE based on the condition. Let assume the problem is that when col1 is visible that time col2 is invisible due to this a white space is left in place of col2 and you want to remove that white space.

My comment:

Take the above scenario if col1 is visible and col2 is invisible and vice a versa then place the col2 over col1 so then col2 is visible then col1 will be invisible and if the col1 is visible then col2 is invisible. And you will not see any white pace in the report.

|||Hi mverma,

First let me thank you for replying to me. Let me clarify my requirements just to make sure what you are proposing would solve my problem.

I have a report, which at design time, would span 2 physical pages (17 inches), with each column being the same width.
Now, the columns' visibility is toggled by parameters passed to the report page during run time.

Now the problem.
Lets say for one of the report we pass in parameters to toggle on columns 1,3,5,7, so the total width would be 8.5 inches, which would fit exactly on one page. So when choosing to print, it would print one page with the 4 columns (with no spaces between the columns), and one blank page. The blank page is printed because, during design time, the report is set to be 17 inches wide, thus even only 8.5 inches of the report contains columns, the whole 17 inches are still printed.

You solution seems to solve the problem of white spaces in between columns that are visible and invisible, but my problem is that though all the columns are neatly next to each other, spaces are still accounted for the invisible columns are at the end of the table.

Now, if I misunderstood your solution and it actually does solve my problem, could you please provide more details or a link as to how to "stack" one column on top of another during runtime. Nevertheless, thanks again for your reply.

- Will

|||

I have another option suppose according to the passing parameter report generate value for 1,3,5,7 etc and for next parameter value it will generate report based on 2,4,6,8. Then you can take two table one is design for 1,3,5,7 and next one is designed used by 2,4,6,8 column when parameter passed for 1,3,5,7 then set visibile='true' for this table and set visibile= 'false' for another table that we don't want. Place table one over other so that you need not to worry about the white space.

Does this help you to achive your requirment ?

Regards

Manoj Verma.

|||Hi Manoj Verma,

Thanks for replying. I do not think your suggestion would work because my example was a watered downed one. Our actual need is a 40 columns dynamic report, where users can choose any of the 40 columns. Thanks again for your input.

- Will

sql

Report Name in Subscription

Hi!
Is there a way to make the name of the report dynamic? instead of just
appending a number if a certain already exists, the name could append
the name of the current month or a selected parameter.
Thanks! :)Hi Sigh,
I think you need a data driven subscription which will require the
enterprise edition of reporting services..
Cheers
Matt
"Sigh" wrote:
> Hi!
> Is there a way to make the name of the report dynamic? instead of just
> appending a number if a certain already exists, the name could append
> the name of the current month or a selected parameter.
> Thanks! :)
>|||Hi Matt.
Unfortunately, we're running the standard edition. Thanks for the
idea. :)
Sigh.

Report layout matrix/dynamic columns...

Hello

Could someone please help me with how I can produce a report. I'm new to Reporting Services and I'm thinking this should be so easy, but it doesn't seem to be. A simplified version of my data is:

Table 1
=======
JobNumber Date Staff
123 01/01/06 5
444 01/03/06 6

Table 2
=======
JobNumber FieldName FieldValue
123 Apples $13.23
123 Deleted False
444 Deleted True
444 Oranges 23

I need to create a report with the following output:

Report
======
JobNumber Date Staff Apples Deleted Oranges
123 01/01/06 5 $13.23 False
444 01/03/06 6 True 23

The FieldNames in Table 2 are variable, any number of new field names could be added and I don't know what they are. I have tried using a matrix, a subreport and I cannot get either to produce the desired result.

Also I have control over the layout of table 2 if that helps. It was originally set up as:

JobNUmber Apples Deleted Oranges
123 $13.23 False Null
444 Null True 23

Thanks in advance.

Maybe you can join the tables together to get something like:

JobNumber FieldName FieldValue

123 Date 01/01/06
123 Staff 5
123 Apples $13.23
123 Deleted False
444 Date 01/03/06
444 Staff 6
444 Deleted True
444 Oranges 23

Then you can use a matrix, grouping by JobNumber on the rows and FieldName on the columns.

-Albert

|||

Cheers Albert. I think I can achieve that, but how do I get my Staff and Date columns into the matrix? If I add them to it then I can't get column headers for these fields? I end up with something like this

Report
======
Apples Deleted Oranges
123 01/01/06 5 $13.23 False
444 01/03/06 6 True 23

Cheers

|||

You need to do something like this:

SELECT JobNumber, FieldName, FieldValue FROM [Table 2]
UNION SELECT JobNumber, 'Date', Date FROM [Table 1]
UNION SELECT JobNumber, 'Staff', Staff FROM [Table 1]

Does that work for you?

-Albert