Dynamic reports

Yooo... I'm trying to build a dynamic report with Reporting Services. The problem is that I have a stored procedure that returns a different number of columns with different name for the columns almost each time. So... how can I get dynamic the number and the name of the columns at runtime.

He is an example of the SP:

CREATE PROCEDURE [dbo].[Test]
@.nrCol INT
, @.CarCol CHAR(5)
AS
CREATE TABLE #Part(DenPart CHAR(10))
DECLARE @.i INT
SET @.i = 0
WHILE @.i < @.nrCol
BEGIN
EXEC('ALTER TABLE #Part ADD [' + @.CarCol + @.i + '] NUMERIC(18,2) NOT NULL DEFAULT(0)')
SET @.i = @.i + 1
END
INSERT INTO #Part (DenPart) VALUES('A')
INSERT INTO #Part (DenPart) VALUES('B')
SELECT * FROM #Part

Any ideeas?

Thanks

I wont think we can use the above stored procedure. To develop a report we need to have a result set at design time. Report need to know what are the data fields at design time.sql

Dynamic Report Columns..?

I have a stored procedure that outputs a dynamic resultset with an unknown number of columns...

So, the resultset might look like:

RowId 03/16/2006 03/17/2006 03/18/2006 03/19/2006
Run of House 02 10 20 35
Suites 05 30 05 15

Or it could look like:

RowId 03/16/2006 03/17/2006
Run of House 02 10
Suites 05 30

Or perhaps:

RowId 03/16/2006 03/17/2006 03/18/2006 03/19/2006 03/20/2006 03/21/2006 03/22/2006
Run of House 02 10 20 35 10 15 45
Suites 05 30 05 15 05 10 80

I need a way that I can run a report that displays these results the from the stored procedure without having to know the number of columns. Is this possible..? If so, can you explain how this can be done in SSRS...

Thanks

the matrix component should fulfill your needs

its like a table but expanding horizontally
but its also a bit more tricky, as you will see

greets
gerhard

Dynamic Parameter List

Is it possible to have a dynamic number of parameters? Basically I have a
vertical table that will contain a number of records. As records are added
to this table, they will need to be passed to a stored procedure. So say I
have this structure:
SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
@.dynamic_param2, etc. etc.
Tbl_Dynamic_Params
ID
1 Nationality
2 Net Worth
3 Hair Color
4 Shoe Size
So I want to pass:
SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
@.Net_Worth, @.HairColor, @.ShoeSize.
Is this possible without using dynamic SQL?"James" <neg@.tory.com> wrote in message
news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Is it possible to have a dynamic number of parameters? Basically I have a
> vertical table that will contain a number of records. As records are
> added to this table, they will need to be passed to a stored procedure.
> So say I have this structure:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
> @.dynamic_param2, etc. etc.
> Tbl_Dynamic_Params
> ID
> 1 Nationality
> 2 Net Worth
> 3 Hair Color
> 4 Shoe Size
> So I want to pass:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
> @.Net_Worth, @.HairColor, @.ShoeSize.
> Is this possible without using dynamic SQL?
>
You can have *optional* parameters in a proc, yes. Just specify a default
value for those parameters. For example:
CREATE PROC usp_proc
@.p1 INT = NULL,
@.p2 INT = NULL
AS ...
Both @.p1 and @.p2 are optional and will default to null if they are not
specified. Obviously your parameter *names* must be known at runtime
otherwise you couldn't write any code that used those parameters could you?
Important: Do not use "sp_" as a prefix for your procs. "sp_" is the name
used for system procs and has a special meaning that will cause unwanted
side-effects to your code.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I don't actually use sp as a prefix, it was merely a visual cue. I
understand optional parameters, but I want the list of parameters to be
dynamic, not just the arguments...that's the crux of the problem. I don't
think there's a really good solution to this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uX5l3nC5HHA.5724@.TK2MSFTNGP05.phx.gbl...
> "James" <neg@.tory.com> wrote in message
> news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
>> Is it possible to have a dynamic number of parameters? Basically I have
>> a vertical table that will contain a number of records. As records are
>> added to this table, they will need to be passed to a stored procedure.
>> So say I have this structure:
>> SP_Update_Contact @.first_name, @.last_name, @.city, @.state,
>> @.dynamic_param1, @.dynamic_param2, etc. etc.
>> Tbl_Dynamic_Params
>> ID
>> 1 Nationality
>> 2 Net Worth
>> 3 Hair Color
>> 4 Shoe Size
>> So I want to pass:
>> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
>> @.Net_Worth, @.HairColor, @.ShoeSize.
>> Is this possible without using dynamic SQL?
> You can have *optional* parameters in a proc, yes. Just specify a default
> value for those parameters. For example:
>
> CREATE PROC usp_proc
> @.p1 INT = NULL,
> @.p2 INT = NULL
> AS ...
> Both @.p1 and @.p2 are optional and will default to null if they are not
> specified. Obviously your parameter *names* must be known at runtime
> otherwise you couldn't write any code that used those parameters could
> you?
> Important: Do not use "sp_" as a prefix for your procs. "sp_" is the name
> used for system procs and has a special meaning that will cause unwanted
> side-effects to your code.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||"James" <neg@.tory.com> wrote in message
news:eK3xtqC5HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I don't actually use sp as a prefix, it was merely a visual cue. I
>understand optional parameters, but I want the list of parameters to be
>dynamic, not just the arguments...that's the crux of the problem. I don't
>think there's a really good solution to this.
>
I don't understand what you mean when you say you want the "list of
parameters to be dynamic". Parameters have names and types. If you don't
know the names and types in advance then how could you possibly write a proc
that made use of them - even assuming it was possible to pass them as
parameters?
Maybe what you want is an array. SQL Server has a data structure that is
much more powerful than an array: a table! :-)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> ... but I want the list of parameters to be dynamic, not just the
>> arguments...that's the crux of the problem.
The crux of the problem has to do with the poor design scheme you use to
represent various attributes as values. If you have a static set of
properties that you have to manage, treat them as columns rather than values
in a column. The right answer is that you should reconsider the logical
design. Mixing up data with metadata is always a recipe for messy and
complex solutions.
--
Anith|||You're preaching to the choir. This isn't my design. Let me rewind. A
colleague approach me with this problem.
Contacts Table:
ID First_Name Last_Name ... ...
Pretty basic. Now there is additional data that needs to be stored. I.E.
Shoe Size, Hat Size, and various other data. However, not every record will
have all of these fields. Also, fields need to be added on the fly with the
lowest amount of maintenance possible.
Performance is a huge consideration. He has approximately 2 million records
in the Contacts table and say, 10 fields +/-. He believes that performance
will be better if he has a flat table that is a 1-to-1 with this table that
stores the extra columns...as opposed to a more normalized alternative that
they have in place now which CRAWLS.
Should I talk him out of this redesign, encourage it, or suggest an
alternative? If so, that begs the question...what alternative?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23rOPW9C5HHA.4964@.TK2MSFTNGP06.phx.gbl...
>> ... but I want the list of parameters to be dynamic, not just the
>> arguments...that's the crux of the problem.
> The crux of the problem has to do with the poor design scheme you use to
> represent various attributes as values. If you have a static set of
> properties that you have to manage, treat them as columns rather than
> values in a column. The right answer is that you should reconsider the
> logical design. Mixing up data with metadata is always a recipe for messy
> and complex solutions.
> --
> Anith
>|||"James" <neg@.tory.com> wrote in message
news:eLGgUPD5HHA.5316@.TK2MSFTNGP04.phx.gbl...
> You're preaching to the choir. This isn't my design. Let me rewind. A
> colleague approach me with this problem.
> Contacts Table:
> ID First_Name Last_Name ... ...
> Pretty basic. Now there is additional data that needs to be stored. I.E.
> Shoe Size, Hat Size, and various other data. However, not every record
> will have all of these fields. Also, fields need to be added on the fly
> with the lowest amount of maintenance possible.
> Performance is a huge consideration. He has approximately 2 million
> records in the Contacts table and say, 10 fields +/-. He believes that
> performance will be better if he has a flat table that is a 1-to-1 with
> this table that stores the extra columns...as opposed to a more normalized
> alternative that they have in place now which CRAWLS.
> Should I talk him out of this redesign, encourage it, or suggest an
> alternative? If so, that begs the question...what alternative?
>
Two million rows is not large and should be well within the capabilities of
even an entry level server. If performance is inadequate then start looking
at indexing, query plans, statistics and procedure design.
As for the logical model, a normalized design is the right place to start.
Nothing you have said suggests any need for a change from that.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:elfSovC5HHA.3400@.TK2MSFTNGP03.phx.gbl...
>.
> Maybe what you want is an array. SQL Server has a data structure that is
> much more powerful than an array: a table! :-)
>.
This is a disturbing statement from someone who purports to
understand a strong typed system. I assume its rationale
is not intellectual. Shall I pull out the sql couch and
start the examination? :-)
www.beyondsql.blogspot.com|||You mean like printf. I seriously doubt it considering the hoops C has to go
through to make it work.
How about name/value pairs passed into one large varchar?
set @.paramerters = 'first_name='+@.first_name+';last_name='+@.last_name
create procedure sp_update_contact @.parameters
as
declare @.parameters varchar(max)
loop through the string looking for the ; delimiter, then split the
name/value pair and put it in a table variable:
declare @.MyArray TABLE (
name varchar(20)
value varchar(20)
)
Then do as you please.
Since the name portion had to corospond to a column in the table, you better
have some decent error checking an support for an error return.
"James" <neg@.tory.com> wrote in message
news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Is it possible to have a dynamic number of parameters? Basically I have a
> vertical table that will contain a number of records. As records are
> added to this table, they will need to be passed to a stored procedure.
> So say I have this structure:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
> @.dynamic_param2, etc. etc.
> Tbl_Dynamic_Params
> ID
> 1 Nationality
> 2 Net Worth
> 3 Hair Color
> 4 Shoe Size
> So I want to pass:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
> @.Net_Worth, @.HairColor, @.ShoeSize.
> Is this possible without using dynamic SQL?
>sql

Dynamic Page Breaks

How can I create dynamic page breaks based on a number of records I only want
to be displayed per page?To create a page break after a specified number of rows:
1. Select the table properties
2. Add a grouping
3. In the Expression field add the following:
=Ceiling(RowNumber(Nothing)/30)
4. Check the â'Page break at endâ' check box.
reeves
"Terry" wrote:
> How can I create dynamic page breaks based on a number of records I only want
> to be displayed per page?|||Thank you so kindly.
By the way, should the new page break group be added to the already existing
groups?
"Reeves Smith" wrote:
> To create a page break after a specified number of rows:
> 1. Select the table properties
> 2. Add a grouping
> 3. In the Expression field add the following:
> =Ceiling(RowNumber(Nothing)/30)
> 4. Check the â'Page break at endâ' check box.
> reeves
> "Terry" wrote:
> > How can I create dynamic page breaks based on a number of records I only want
> > to be displayed per page?|||Terry,
Yes you need to add a group even if groups already exist. Where it is placed
might get a little tricky and need to be played with.
I got this info from a technical article: Report Design: Best Practices and
Guidelines
but it looks like this article got updated to: Report Design Tips and Tricks
(http://msdn2.microsoft.com/en-us/library/bb395166(sql.90).aspx) and there
looks to be another way in the new article to do what your after.
Here is the site for some great articles:
http://msdn2.microsoft.com/en-us/library/aa496080.aspx
The Chart one is VERY good.
Reeves
"Terry" wrote:
> Thank you so kindly.
> By the way, should the new page break group be added to the already existing
> groups?
>
> "Reeves Smith" wrote:
> > To create a page break after a specified number of rows:
> >
> > 1. Select the table properties
> > 2. Add a grouping
> > 3. In the Expression field add the following:
> >
> > =Ceiling(RowNumber(Nothing)/30)
> >
> > 4. Check the â'Page break at endâ' check box.
> >
> > reeves
> >
> > "Terry" wrote:
> >
> > > How can I create dynamic page breaks based on a number of records I only want
> > > to be displayed per page?|||I have tried this on multiple occassions and I always get this error message...
[rsInvalidGroupExpressionScope] A group expression for the table â'table1â'
uses the RowNumber function with a scope parameter that is not valid. When
used in a group expression, the value of the scope parameter of RowNumber
must equal the name of the group directly containing the current group.
Build complete -- 1 errors, 0 warnings
I'm sure there is something else that i am missing...
Any suggestions will be greatly appreciated.
--
CipherTeKST
MCSE: Security 2003, CCNA, Security+
"Reeves Smith" wrote:
> To create a page break after a specified number of rows:
> 1. Select the table properties
> 2. Add a grouping
> 3. In the Expression field add the following:
> =Ceiling(RowNumber(Nothing)/30)
> 4. Check the â'Page break at endâ' check box.
> reeves
> "Terry" wrote:
> > How can I create dynamic page breaks based on a number of records I only want
> > to be displayed per page?

Dynamic number of columns.

Hi all.
I do have a "simple" problem on my hand.
I have one table containing PriceGroups. It could be 1 or many PriceGroups
pr. produkt. When I list the produkts data in one record I want the
PriceGroup description to be columns as well with the corresponding price. A
kind of dynamic Pivot JOIN with the productrecord.
E.g if there are 3 pricegroups to this produkt I would get 3 lines using
standard join.
ProductNr PricegroupDesc Price
ab Price1 200
ab Price2 300
ab Price3 500
What I want is 1 line with the 3 descriptions as columnheader and the
corresponding prices under.
ProductNr Price1 Price2 Price3 ..........PriceN
ab 200 300 500 nnnnnn
Thanx all.
geirhttp://www.aspfaq.com/2462
David Portas
SQL Server MVP
--|||Thank you David.
This got me startet on something I can use.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108636820.313261.178950@.c13g2000cwb.googlegroups.com...
> http://www.aspfaq.com/2462
> --
> David Portas
> SQL Server MVP
> --
>

Dynamic graphs in SSRS

Hello,

Im building a horizontal bar graph, but according to the parameters it could have a different number of bars, how can i make the SSRS resize the graph?

Or how can i make ssrs to give the same size as a table right next to it?

Thank you

You may need to create a couple of separate graphs with different sizes and show-hide based on # of rows in the dataset.

There is an article here which may also help, though it is more for working inside the chart rather than sizing the object itself. http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx#maxminvalues

A more flexible approach may be available in Dundas Charts for Reporting Services.

More info here:
http://msdn2.microsoft.com/en-us/library/aa964128.aspx

cheers,
Andrew

Dynamic Filtering

I want to dynamically filter my published data to many subscribers based
on a number contained within the server name of the subscriber. ie one
subscriber server is called 8364-Server and I want to use the 8364 as
the "club code" to extract the subset from the publisher. I preferably
want to use push subscriptions.
Using host_name just seems to return the name of the publisher which is
no good. Is my only option to use a look-up table on the publisher to
map usernames of all the subscribers to club codes?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
use a pull subscription to get the hostname parameter to resolve to the name
of the subscriber.
If you are using merge replication you can use the HostName parameter on you
merge agent commands for this.
You would have to use left(host_name(), 4) for this to work in your filter
in the pull subscription.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Martin Bancroft" <martin.bancroft@.galagroup.co.uk> wrote in message
news:egYD3AIeEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I want to dynamically filter my published data to many subscribers based
> on a number contained within the server name of the subscriber. ie one
> subscriber server is called 8364-Server and I want to use the 8364 as
> the "club code" to extract the subset from the publisher. I preferably
> want to use push subscriptions.
> Using host_name just seems to return the name of the publisher which is
> no good. Is my only option to use a look-up table on the publisher to
> map usernames of all the subscribers to club codes?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Dynamic field list

Hi,
The underlying query in the dataset of my report has a set number of static
fields (which I bind to report elements) but also can return additional
variable number of fields, depending on passed parameters. Is there a way to
access those fields at report runtime?
Thanks.How about creating a SQL View, which has your current Static Column &
Computed Column & then returning that as Query to your Report?
On May 2, 1:10=A0pm, "Yuriy Galanter" <y...@.galanter.net> wrote:
> Hi,
> The underlying query in the dataset =A0of my report has a set number of st=atic
> fields (which I bind to report elements) but also can return additional
> variable number of fields, depending on passed parameters. Is there a way =to
> access those fields at report runtime?
> Thanks.|||That's the thing - I don't know in advance *how many* dynamic columns I am
going to return. Let's say I pass no parameters - the query will return
columns:
A B C
If I pass parameter "1" the query will return columns
A B C D
If I pass parameter "2" the the query will return columns
A B C D E
field list in dataset in report definition can contain only static number of
fields and if I bind report to A B C then D and E become unaccessable even
if query returns them.
The only way I can think of is, since I am launching the report from a .NET
application anyway is download report definition and modify it on the fly by
adding new columns to dataset field list. But I'd like to avoid it if
possible.
<prabhupr@.gmail.com> wrote:
How about creating a SQL View, which has your current Static Column &
Computed Column & then returning that as Query to your Report?
On May 2, 1:10 pm, "Yuriy Galanter" <y...@.galanter.net> wrote:
> Hi,
> The underlying query in the dataset of my report has a set number of
> static
> fields (which I bind to report elements) but also can return additional
> variable number of fields, depending on passed parameters. Is there a way
> to
> access those fields at report runtime?
> Thanks.|||Not tested , Just an idea - Does the use of
=IIF(Fields!Column_1.IsMissing, true, false)in the hidden property of the
coloumn solve your problem ?
P.I.
"Yuriy Galanter" <yuri@.galanter.net> a écrit dans le message de news:
eXm6bCJrIHA.4848@.TK2MSFTNGP05.phx.gbl...
> Hi,
> The underlying query in the dataset of my report has a set number of
> static fields (which I bind to report elements) but also can return
> additional variable number of fields, depending on passed parameters. Is
> there a way to access those fields at report runtime?
> Thanks.
>

Dynamic Decimal Format

I have a number: 3320.8000000. My expected result is 3320.80 based on a
dynamic decimal parameter for example:
declare idecimal int
select @.idecimal=3
convert(decimal(20,@.idecimal),number) does not work - error message.
How can I accomplish this?Format the value client side. Why would you want to do this in the database?
David Portas
SQL Server MVP
--|||I have a stored that procedure generates data for a report. The data is
stored in the database as decimal (20,7). Are you saying there is no easy wa
y
to do this using T-SQL?
"David Portas" wrote:

> Format the value client side. Why would you want to do this in the databas
e?
> --
> David Portas
> SQL Server MVP
> --
>
>|||Boy, you were one of the kids asleep in the back of my 20+ years of
database classes!! Where do we do display in a tiered architecture
(this is faaaar more fundamental question than SQL)? The front end !!
Never,never in the database!!
SQL is a static data type language. THIS IS A FUNDAMENTAL PROGRAMMING
CONCEPT!! Why did you think you could change data types in a schema?
You so ignorant or stupid that you are dangerous to people.|||Ultimately the display format is controlled by the client application, not
by SQL Server. A query always has fixed metadata and that includes the
precision and scale of each column. I think your options therefore are to
use dynamic SQL or to cast the value as a string and return it that way. You
can use the STR function to output a string with a varying numberof
decimals:
SELECT STR(x,20,@.d)
FROM T1
However, this might cause you some problems if your reporting application
needs to do arithmetic on the results.
David Portas
SQL Server MVP
--

Dynamic dataset column names and number of columns

I have a dataset that is based on a dynamic pivot stored procedure. There is
at least one column name that I know will be in the dataset, the key column,
but the rest of the columns are dependent on the data in the underlying table.
Once I have the dataset, how can I put it in the layout given that the
column names are dynamic and the number of columns are dynamic?
StephanieOn Aug 20, 4:36 pm, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> I have a dataset that is based on a dynamic pivot stored procedure. There is
> at least one column name that I know will be in the dataset, the key column,
> but the rest of the columns are dependent on the data in the underlying table.
> Once I have the dataset, how can I put it in the layout given that the
> column names are dynamic and the number of columns are dynamic?
> Stephanie
You will want to look into Matrix Reports, where the pivoted column's
unique values are actually the number of columns in the report. This
provides the dynamic functionality you need.
Regards,
Enrique Martinez
Sr. Software Consultant

dynamic columns/aliases - Continue

Hi
Following my previous question. Here is the implemetation.
I have to design a report which display monthes as columns.
The number of columns in the report depends on the range paramters.
For example : if the Range is from 01/02/05 to 30/06/05 there ara 6 columns
shown.
01/05,02/05....06/05. (column alias) .
if the range of dates are 8 monthes range there are 8 dynamic columns, etc'
1) My first major problem is to write a SP or a function that return a
dynamic number of columns.
2) Second,(This is not a must) to change column alias (Heading) according to
the parameters.
thanksWhat reporting tool do you use? This is called a cross tab report and
any decent reporting tool will render it for you client-side with no
programming required. Otherwise you'll have to use dynamic SQL, which
is not much fun.
David Portas
SQL Server MVP
--|||>> 1) My first major problem is to write a SP or a function that return a dy
namic number of columns. <<
NO, your **first** problem is that you missed the basic concepts of
RDBMS. A query returns a table; a table has a fixed number of columns.
A query is not a report; a query sends information to a front end
which can have a reporting tool in it.
This is definitions, not anything fancy.
\If you want to be a bad SQL programmer, this can be done with dynamic
SQL and a ton of procedural code that will be a XXXXX to maintain.|||Yes. My Big Problem is indeed the Reporting tool which is Custom Reporting
tool of an ERP software that simply translate
the resulted query to a GUI look alike Report.
Unfortunately , There's Nothing I can do about it.
What are my options with dynamic sql ?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1126806242.941597.208120@.g44g2000cwa.googlegroups.com...
> What reporting tool do you use? This is called a cross tab report and
> any decent reporting tool will render it for you client-side with no
> programming required. Otherwise you'll have to use dynamic SQL, which
> is not much fun.
> --
> David Portas
> SQL Server MVP
> --
>

Dynamic columns in RS reports

I have to design a report that will have a dynamic number of columns, in a
dynamic order.
For example: items and a selection from their attributes
ITEM Attr 1 Attr 2
Attr3 Attr 4
I1 5 Realy large text
........................................ Short text
12305
etc...
I know the matrix is used to obtain dynamic number of columns, but the
problem is that the columns in matrix have the same width
and i need different column widths (for text attributes and numeric
attributes for example), and the column width can have only numeric
(and fixed) values - i mean you can put there a function call..
Is there any way other than generating the rdl at run time to obtain such
dynamic table ?
Thanks in advance for any advice.This is not really feasible as far as I can tell, in the way you are
asking.
You can't set column width dynamically, which sounds like a big problem
for you.
But you may be able to get some way towards it if you compromise a
little.
In terms of no. of columns, you'd need to set a maximum number, make
sure you always return that number in the dataset even if they contain
nulls you can then allow for the maximum in your report and just hide
or show them as needed. You'd have to do it this way because you will
be referring to the columns with a field name and the report validates
that those fields will be returned when it renders.
You can then hide table columns or text boxes in a list control based
on the number of columns you're expecting that scenario. You could have
several table columns geared to one dataset column, all set differently
according to data type, then hide the columns that you don't need based
on whats returned in the query.
Chris
Razvan Popov wrote:
> I have to design a report that will have a dynamic number of columns,
> in a dynamic order.
> For example: items and a selection from their attributes
> ITEM Attr 1 Attr 2
> Attr3 Attr 4
> I1 5 Realy large text
> ........................................ Short text
> 12305
> etc...
> I know the matrix is used to obtain dynamic number of columns, but
> the problem is that the columns in matrix have the same width
> and i need different column widths (for text attributes and numeric
> attributes for example), and the column width can have only numeric
> (and fixed) values - i mean you can put there a function call..
> Is there any way other than generating the rdl at run time to obtain
> such dynamic table ?
> Thanks in advance for any advice.|||Thank you for your suggestion. But my problem is a little worse than that:
I don't have a maximum number of columns (the user can add attributes and
select them into the report at runtime).
And if i set a maximum number of X columns as constraint. I don't know which
will be the X attributes the user selected to
be displayed into report and for that I have no way of presetting the width
of the columns (which should be dependent on attribute type)..
I have as the last option to generate dinamically the RDL (or parts of it)
but this is prety complex solution and i would like to be sure that there is
no
simpler solution before i start coding such scenario...
Thx
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> This is not really feasible as far as I can tell, in the way you are
> asking.
> You can't set column width dynamically, which sounds like a big problem
> for you.
> But you may be able to get some way towards it if you compromise a
> little.
> In terms of no. of columns, you'd need to set a maximum number, make
> sure you always return that number in the dataset even if they contain
> nulls you can then allow for the maximum in your report and just hide
> or show them as needed. You'd have to do it this way because you will
> be referring to the columns with a field name and the report validates
> that those fields will be returned when it renders.
> You can then hide table columns or text boxes in a list control based
> on the number of columns you're expecting that scenario. You could have
> several table columns geared to one dataset column, all set differently
> according to data type, then hide the columns that you don't need based
> on whats returned in the query.
> Chris|||Razvan,
You've got a pretty complex scenario, I think you're going to need a
complex solution!
It sounds like you're developing a form of end-user report builder?
Have you taken a look at Cizer (www.cizer.com) or Report Builder in
SQL2005?
Chris
Razvan Popov wrote:
> Thank you for your suggestion. But my problem is a little worse than
> that: I don't have a maximum number of columns (the user can add
> attributes and select them into the report at runtime).
> And if i set a maximum number of X columns as constraint. I don't
> know which will be the X attributes the user selected to
> be displayed into report and for that I have no way of presetting the
> width of the columns (which should be dependent on attribute type)..
> I have as the last option to generate dinamically the RDL (or parts
> of it) but this is prety complex solution and i would like to be sure
> that there is no
> simpler solution before i start coding such scenario...
> Thx
>
> "Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
> news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> > This is not really feasible as far as I can tell, in the way you are
> > asking.
> > You can't set column width dynamically, which sounds like a big
> > problem for you.
> >
> > But you may be able to get some way towards it if you compromise a
> > little.
> > In terms of no. of columns, you'd need to set a maximum number, make
> > sure you always return that number in the dataset even if they
> > contain nulls you can then allow for the maximum in your report and
> > just hide or show them as needed. You'd have to do it this way
> > because you will be referring to the columns with a field name and
> > the report validates that those fields will be returned when it
> > renders.
> >
> > You can then hide table columns or text boxes in a list control
> > based on the number of columns you're expecting that scenario. You
> > could have several table columns geared to one dataset column, all
> > set differently according to data type, then hide the columns that
> > you don't need based on whats returned in the query.
> >
> > Chris|||Thank you for your help..
If i find any solution simpler than generating rdls dynamically i'll post it
here..
Razvan

Dynamic columns in RS reports

I have to design a report that will have a dynamic number of columns, in a
dynamic order.
For example: items and a selection from their attributes
ITEM Attr 1 Attr 2
Attr3 Attr 4
I1 5 Realy large text
........................................ Short text
12305
etc...
I know the matrix is used to obtain dynamic number of columns, but the
problem is that the columns in matrix have the same width
and i need different column widths (for text attributes and numeric
attributes for example), and the column width can have only numeric
(and fixed) values - i mean you can put there a function call..
Is there any way other than generating the rdl at run time to obtain such
dynamic table ?
Thanks in advance for any advice.This is not really feasible as far as I can tell, in the way you are
asking.
You can't set column width dynamically, which sounds like a big problem
for you.
But you may be able to get some way towards it if you compromise a
little.
In terms of no. of columns, you'd need to set a maximum number, make
sure you always return that number in the dataset even if they contain
nulls you can then allow for the maximum in your report and just hide
or show them as needed. You'd have to do it this way because you will
be referring to the columns with a field name and the report validates
that those fields will be returned when it renders.
You can then hide table columns or text boxes in a list control based
on the number of columns you're expecting that scenario. You could have
several table columns geared to one dataset column, all set differently
according to data type, then hide the columns that you don't need based
on whats returned in the query.
Chris
Razvan Popov wrote:
> I have to design a report that will have a dynamic number of columns,
> in a dynamic order.
> For example: items and a selection from their attributes
> ITEM Attr 1 Attr 2
> Attr3 Attr 4
> I1 5 Realy large text
> ........................................ Short text
> 12305
> etc...
> I know the matrix is used to obtain dynamic number of columns, but
> the problem is that the columns in matrix have the same width
> and i need different column widths (for text attributes and numeric
> attributes for example), and the column width can have only numeric
> (and fixed) values - i mean you can put there a function call..
> Is there any way other than generating the rdl at run time to obtain
> such dynamic table ?
> Thanks in advance for any advice.|||Thank you for your suggestion. But my problem is a little worse than that:
I don't have a maximum number of columns (the user can add attributes and
select them into the report at runtime).
And if i set a maximum number of X columns as constraint. I don't know which
will be the X attributes the user selected to
be displayed into report and for that I have no way of presetting the width
of the columns (which should be dependent on attribute type)..
I have as the last option to generate dinamically the RDL (or parts of it)
but this is prety complex solution and i would like to be sure that there is
no
simpler solution before i start coding such scenario...
Thx
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> This is not really feasible as far as I can tell, in the way you are
> asking.
> You can't set column width dynamically, which sounds like a big problem
> for you.
> But you may be able to get some way towards it if you compromise a
> little.
> In terms of no. of columns, you'd need to set a maximum number, make
> sure you always return that number in the dataset even if they contain
> nulls you can then allow for the maximum in your report and just hide
> or show them as needed. You'd have to do it this way because you will
> be referring to the columns with a field name and the report validates
> that those fields will be returned when it renders.
> You can then hide table columns or text boxes in a list control based
> on the number of columns you're expecting that scenario. You could have
> several table columns geared to one dataset column, all set differently
> according to data type, then hide the columns that you don't need based
> on whats returned in the query.
> Chris|||Razvan,
You've got a pretty complex scenario, I think you're going to need a
complex solution!
It sounds like you're developing a form of end-user report builder?
Have you taken a look at Cizer (www.cizer.com) or Report Builder in
SQL2005?
Chris
Razvan Popov wrote:
> Thank you for your suggestion. But my problem is a little worse than
> that: I don't have a maximum number of columns (the user can add
> attributes and select them into the report at runtime).
> And if i set a maximum number of X columns as constraint. I don't
> know which will be the X attributes the user selected to
> be displayed into report and for that I have no way of presetting the
> width of the columns (which should be dependent on attribute type)..
> I have as the last option to generate dinamically the RDL (or parts
> of it) but this is prety complex solution and i would like to be sure
> that there is no
> simpler solution before i start coding such scenario...
> Thx
>
> "Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
> news:u%23XoXUncFHA.2436@.TK2MSFTNGP10.phx.gbl...
> > This is not really feasible as far as I can tell, in the way you are
> > asking.
> > You can't set column width dynamically, which sounds like a big
> > problem for you.
> >
> > But you may be able to get some way towards it if you compromise a
> > little.
> > In terms of no. of columns, you'd need to set a maximum number, make
> > sure you always return that number in the dataset even if they
> > contain nulls you can then allow for the maximum in your report and
> > just hide or show them as needed. You'd have to do it this way
> > because you will be referring to the columns with a field name and
> > the report validates that those fields will be returned when it
> > renders.
> >
> > You can then hide table columns or text boxes in a list control
> > based on the number of columns you're expecting that scenario. You
> > could have several table columns geared to one dataset column, all
> > set differently according to data type, then hide the columns that
> > you don't need based on whats returned in the query.
> >
> > Chris|||Thank you for your help..
If i find any solution simpler than generating rdls dynamically i'll post it
here..
Razvan

dynamic columns in matrix

hi all,

i m using ssrs 2005. i want to generate a report which displays data according to the dataset returned.Now this dataset can return any number of columns.

does matrix use helps out in this case..

if anyone can really explain me on it or can even point to certain articles in this regard,that wudd be wonderful

thanks a ton...

hi all
m quite new to reporting services so may b it sounds easy for u champs but still ur replies wudd b appreciated..

thanks a ton ...

|||Hi,hilander:
Try to do this.
1. Convert your RDL files in RLDC files (see this topic at VS2005 help).
2. Configure your ReportViewer to a LocalReport mode. This will allow you to
link yourdataset to a report at runtime by using ReportViewer.LocalReport methods or at disign level too.

Hopefully I help you.

|||

Hi,hilander:
We are marking this issue as "Answered". If you have any new findings or concerns, please feel free to unmark the issue.
Thank you for your understanding!

Dynamic columns

My report has a large number of rows in the details section, This wastes a lot of space. I'd like to spread these rows across multiple columns in the details section of my table.

What i'm currently getting...

ROW1-

ROW2-

ROW3-

ROW4-

ROW5-

ROW6-

ROW7-

ROW*-

ROW9-

What I'd like to see...

ROW1- ROW2- ROW3-

ROW4- ROW5- ROW6-

ROW7- ROW8- ROW9-

Is this possible, if so, how?

SSRS supports multi-column reports.

|||Thanks for your reply, but the example in that link doesnt really suit me. My report has various groups and footer totals that are independent of the "details" columns. I need to, somehow, break the 'details' into 3 columns, while still maintaining my header,subreport, groups, and footer|||

Perhaps a multi-column subreport for the details only then.

|||

Using a Matrix:

Save this as an RDL file and figure out whats going on.

Code Snippet

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="AdventureWorks">

<DataSourceReference>AdventureWorks</DataSourceReference>

<rd:DataSourceID>05fe442d-a63b-47f0-9339-790445d10db9</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>1in</BottomMargin>

<RightMargin>1in</RightMargin>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ReportItems>

<Matrix Name="matrix1">

<MatrixColumns>

<MatrixColumn>

<Width>1in</Width>

</MatrixColumn>

</MatrixColumns>

<RowGroupings>

<RowGrouping>

<Width>1in</Width>

<DynamicRows>

<ReportItems>

<Textbox Name="textbox3">

<rd:DefaultName>textbox3</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value />

</Textbox>

</ReportItems>

<Grouping Name="matrix1_ROWGROUPS">

<GroupExpressions>

<GroupExpression>=Fields!ROWGROUPS.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicRows>

</RowGrouping>

</RowGroupings>

<ColumnGroupings>

<ColumnGrouping>

<DynamicColumns>

<ReportItems>

<Textbox Name="COLUMNGROUPS">

<rd:DefaultName>COLUMNGROUPS</rd:DefaultName>

<ZIndex>2</ZIndex>

<Style>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!COLUMNGROUPS.Value</Value>

</Textbox>

</ReportItems>

<Grouping Name="matrix1_COLUMNGROUPS">

<GroupExpressions>

<GroupExpression>=Fields!COLUMNGROUPS.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicColumns>

<Height>0.25in</Height>

</ColumnGrouping>

</ColumnGroupings>

<DataSetName>DataSet1</DataSetName>

<Corner>

<ReportItems>

<Textbox Name="textbox1">

<rd:DefaultName>textbox1</rd:DefaultName>

<ZIndex>3</ZIndex>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value />

</Textbox>

</ReportItems>

</Corner>

<MatrixRows>

<MatrixRow>

<Height>0.25in</Height>

<MatrixCells>

<MatrixCell>

<ReportItems>

<Textbox Name="ROWDATA">

<rd:DefaultName>ROWDATA</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=First(Fields!ROWDATA.Value)</Value>

</Textbox>

</ReportItems>

</MatrixCell>

</MatrixCells>

</MatrixRow>

</MatrixRows>

</Matrix>

</ReportItems>

<Height>0.5in</Height>

</Body>

<rd:ReportID>327962be-8654-4648-a50f-65e063317681</rd:ReportID>

<LeftMargin>1in</LeftMargin>

<DataSets>

<DataSet Name="DataSet1">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>SELECT 'ROW1' AS ROWDATA, 1 AS ROWGROUPS, 1 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW2' AS ROWDATA, 1 AS ROWGROUPS, 2 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW3' AS ROWDATA, 1 AS ROWGROUPS, 3 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW4' AS ROWDATA, 2 AS ROWGROUPS, 1 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW5' AS ROWDATA, 2 AS ROWGROUPS, 2 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW6' AS ROWDATA, 2 AS ROWGROUPS, 3 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW7' AS ROWDATA, 3 AS ROWGROUPS, 1 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW8' AS ROWDATA, 3 AS ROWGROUPS, 2 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW9' AS ROWDATA, 3 AS ROWGROUPS, 3 AS COLUMNGROUPS</CommandText>

<DataSourceName>AdventureWorks</DataSourceName>

</Query>

<Fields>

<Field Name="ROWDATA">

<rd:TypeName>System.String</rd:TypeName>

<DataField>ROWDATA</DataField>

</Field>

<Field Name="ROWGROUPS">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>ROWGROUPS</DataField>

</Field>

<Field Name="COLUMNGROUPS">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>COLUMNGROUPS</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>2in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>1in</TopMargin>

</Report>

Dynamic column width? Any way to do it?

Hi,

Is there a way to dynamically change the column width depends on the length of the data retrieved? Meaning, if I retrieve a number 11.0031243 or 11.3, it should fit into the column just nice with no redundant spaces. The width of the header should follows.

Is that possible?

Thanks!Ya its possible but u need to calculate the length of the retrieved field and accordingly u can adjust width of the column in Cystal Rpt...
Before that at the time of testing u need to confirm how many characters u can place in ur column field...
if u have any query abt this soln pls tell me....

dynamic column name?

I want to create a table where the column names and number of columns is not
known. It can be a temp table or table variable.
Something like:
DECLARE @.output table (@.columnname int)
or
ALTER TABLE @.output ADD @.columnname varchar(50)
-Max*shudder*
http://www.sommarskog.se/dynamic_sql.html
http://www.aspfaq.com/
(Reverse address to reply.)
"Max" <nospam@.notvalid.com> wrote in message
news:u8i9m9qHFHA.588@.TK2MSFTNGP15.phx.gbl...
> I want to create a table where the column names and number of columns is
not
> known. It can be a temp table or table variable.
> Something like:
> DECLARE @.output table (@.columnname int)
> or
> ALTER TABLE @.output ADD @.columnname varchar(50)
> -Max
>|||>> I want to create a table where the column names and number of
columns is not known.<<
Why, in the name of God!!' The whole idea of a database is that you
have a reality, you model it and then you work with it.
What you are saying is tha tyou have no idea what you are doing.
Tell me the name of your employer. I think that I can get $2000.00 per
day consulting contract fixing your screw ups. My estimate is based on
a mere 35 years in the IT industry, and 10 years on the ANSI X3H2
Database Standards Committee.
Max, if this is no joke and you are writing databases, then stop and
walk away from your job. Your question is that kind of fundamentally
stupid.
And before you start whining about myn abusing you, consider that last
year I did an email consult with an organization that sent medical
supplies to Africa. Their problem was a programmer who did not know
what 1NF was. The shipments were shorted and children died.
It take SIX YEARS to become a Journeyman Union Carpenter in New York
State. how many years did you think that you need to say are database
designer?|||I want to output a table that is dynamically created in a stored proc.
Queries are dynamically generated all the time. Why is that stupid?
-Max
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109719661.313856.309060@.l41g2000cwc.googlegroups.com...
> columns is not known.<<
> Why, in the name of God!!' The whole idea of a database is that you
> have a reality, you model it and then you work with it.|||>> I want to output a table that is dynamically created in a stored
proc. Queries are dynamically generated all the time. Why is that
stupid? <<
All of 40 years of software engineering, you idiot :) Do you want to
the references ?
--CELKO--|||From someone who gets strange ideas all the time:
Doctrines of Demons and Democrats!! HOW Dare you raise the stench of Hell in
this Sacred and Holy place with your blasphemies ideas. You will cause the
Holy Judgment and wrath of the great and powerful gods of SQL (and there a
lot of’em around here) to rain destruction upon us all!!! Your doom will
be
complete. YOU SHOULD BE CAST OUT!! BEGONE YE SPAWN OF THE EVIL ONE!!
I mean really! These religious fanatics around here really get carried away
sometimes. So someone has an idea. It may be good one, it may be a bad one.
No need to get all puritanical. Come on! Lighten up already. Believe it or
not our souls do not hang by a thread.
and as always i wish to say that i get a LOT of good advice here all the tim
e!
thank all
kes
"Max" wrote:

> I want to create a table where the column names and number of columns is n
ot
> known. It can be a temp table or table variable.
> Something like:
> DECLARE @.output table (@.columnname int)
> or
> ALTER TABLE @.output ADD @.columnname varchar(50)
> -Max
>
>|||What is so fundamentally wrong with discovering and creating database schema
or metadata during runtime? I can give you 50 enterprise level examples of
this.
-Max
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109722606.650060.311970@.f14g2000cwb.googlegroups.com...
> proc. Queries are dynamically generated all the time. Why is that
> stupid? <<
> All of 40 years of software engineering, you idiot :) Do you want to
> the references ?
> --CELKO--
>|||I've always imagined how I'd be at age 60, a grumpy old man, except for that
I would have embraced high technology instead of condemned it as would the
typical grumpy old man of today. Well I think I just found an example of
this hi-tech old man. ;)
-Max
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:C101A921-6189-4BD7-9B3D-F889A02179D7@.microsoft.com...
> From someone who gets strange ideas all the time:
> Doctrines of Demons and Democrats!! HOW Dare you raise the stench of Hell
> in
> this Sacred and Holy place with your blasphemies ideas. You will cause the
> Holy Judgment and wrath of the great and powerful gods of SQL (and there a
> lot of'em around here) to rain destruction upon us all!!! Your doom will
> be
> complete. YOU SHOULD BE CAST OUT!! BEGONE YE SPAWN OF THE EVIL ONE!!|||Why are the return columns of the query changing? Send the columns back and
let the presentation tier determine which columns to show/hide. Or have
different stored procedures based on the type of report.
On 3/1/05 6:40 PM, in article OACwYgrHFHA.720@.TK2MSFTNGP14.phx.gbl, "Max"
<nospam@.notvalid.com> wrote:

> I want to output a table that is dynamically created in a stored proc.
> Queries are dynamically generated all the time. Why is that stupid?
> -Max
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1109719661.313856.309060@.l41g2000cwc.googlegroups.com...
>|||Ye hath seen the LIGHT!! Go now!! and sin no more!!!!
(ya gota love this ______. have a good one!!!) ;)
"Max" wrote:

> I've always imagined how I'd be at age 60, a grumpy old man, except for th
at
> I would have embraced high technology instead of condemned it as would the
> typical grumpy old man of today. Well I think I just found an example of
> this hi-tech old man. ;)
> -Max
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:C101A921-6189-4BD7-9B3D-F889A02179D7@.microsoft.com...
>
>

Dynamic change of number of series on the chart

Depending on the result of my stored procedure logic I need to display
different number of lines on the chart in my report. I can not drop the data
fileds into the chart design as suggested in the Help, because at design time
I do not know their names and how many of them I will have when report is
called. Is it possible? What is involved?
Thanks.Not sure I understand what you are trying to do. It sounds like you want a
dynamic series grouping expression which is basically just based on the
field that comes from the stored procedure.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> Depending on the result of my stored procedure logic I need to display
> different number of lines on the chart in my report. I can not drop the
> data
> fileds into the chart design as suggested in the Help, because at design
> time
> I do not know their names and how many of them I will have when report is
> called. Is it possible? What is involved?
> Thanks.|||Thank you Robert,
You indirectly answered my question. I just could not make sense of grouping
capabilities of the chart. The Help on this topic is not very "helpful".
"Robert Bruckner [MSFT]" wrote:
> Not sure I understand what you are trying to do. It sounds like you want a
> dynamic series grouping expression which is basically just based on the
> field that comes from the stored procedure.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > Depending on the result of my stored procedure logic I need to display
> > different number of lines on the chart in my report. I can not drop the
> > data
> > fileds into the chart design as suggested in the Help, because at design
> > time
> > I do not know their names and how many of them I will have when report is
> > called. Is it possible? What is involved?
> > Thanks.
>
>|||I am having to do the same thing like dynamically having series elements
depending on the query results... is this possible to do?
"Simon Gold" wrote:
> Thank you Robert,
> You indirectly answered my question. I just could not make sense of grouping
> capabilities of the chart. The Help on this topic is not very "helpful".
> "Robert Bruckner [MSFT]" wrote:
> > Not sure I understand what you are trying to do. It sounds like you want a
> > dynamic series grouping expression which is basically just based on the
> > field that comes from the stored procedure.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> > news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > > Depending on the result of my stored procedure logic I need to display
> > > different number of lines on the chart in my report. I can not drop the
> > > data
> > > fileds into the chart design as suggested in the Help, because at design
> > > time
> > > I do not know their names and how many of them I will have when report is
> > > called. Is it possible? What is involved?
> > > Thanks.
> >
> >
> >|||Yes, It is possible to do that. The "Help" does not have a good wordage on
it, but here how you do it.
Imagine that you want to display stock prices for several different
companies for a say 10 days period. Create a table 'Prices' that would have
at least three columns: 'DayNumber', 'StockPrice' and 'CompanyName'. Populate
that table with your data. Create a dataset for your chart where run a
"SELECT DayNumber,StockPrice, CompanyName FROM Prices".
Om the chart drug and drop 'DayNumber field under the horizontal axis
(Category fields), drop StockPrice field above the chart into 'data fields'
and drop 'Companyname' filed to the right of the chart into 'Series fileds'.
This will create a chart that will have as many series as many company names
you have in your table.
Good luck...
Simon.
StockPrice
"Mathi" wrote:
> I am having to do the same thing like dynamically having series elements
> depending on the query results... is this possible to do?
> "Simon Gold" wrote:
> > Thank you Robert,
> > You indirectly answered my question. I just could not make sense of grouping
> > capabilities of the chart. The Help on this topic is not very "helpful".
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Not sure I understand what you are trying to do. It sounds like you want a
> > > dynamic series grouping expression which is basically just based on the
> > > field that comes from the stored procedure.
> > >
> > > -- Robert
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > > "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> > > news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > > > Depending on the result of my stored procedure logic I need to display
> > > > different number of lines on the chart in my report. I can not drop the
> > > > data
> > > > fileds into the chart design as suggested in the Help, because at design
> > > > time
> > > > I do not know their names and how many of them I will have when report is
> > > > called. Is it possible? What is involved?
> > > > Thanks.
> > >
> > >
> > >