Datasets in report header/footer - A Summary of the issue

I know there are already a few threads on this topic but I wanted to create another one - and directly ask - how could this limitation possibly exists - and even more important - why is it still a problem in 2005?

I want to summarize a few of the solutions that have been listed here and elseware online. We've tried them all and they all have some kind of problem:

1) Create hidden field(s) and then reference that field from the header: Doesn't work on any pages where the field isn't printed. Header value will be blank in this case.

2) Create hidden column(s) in the data table and reference that from the header: Works, but again, if the table is showing on every page, the value will be blank. i.e. - if you have two or more tables stacked, you would have to add the columns to every one. A very sloppy and not practical solution.

3) Do it with custom code: I've seen this approach recomended but have never actually seen someone provide a working example. From what I understand, custom code can't access the datasets (I'm using 2000 - maybe 2005 is different here?)

4) Use a queried parameter to hold the value- then reference that in the header: This seems to be the best contender so far. It seems to work sometimes, but on several machines here, the report designer goes into some kind of infinite loop and crashes once the queried parameter is added.

If anyone has any other approaches that I'm not aware of, or has another idea, please post it here. Microsoft - this is a huge problem for many people. Is there any chance of a fix for this any time soon?

Thank you.

~H

did you find a solution ?

|||I am very interested in a solution, too.

Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).

5) Use "Available values: From query"

All

report parameters are hidden and passed via the URL. The parameter

value is an ID, but in the page header my customer would like to see

the corresponding description.

I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.

Parameters!ProcessId.Label

There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):

The process ID parameter value is missing.

This problem can be solved, but I consider it being a quirk. Just use the following SQL for your data set:

select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes

I'm also interested in sharing other (more elegant) approaches.

Thanx for listening and kind regards,
Gerald

|||I am very interested in a solution, too.

Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).

5) Use "Available values: From query"

All

report parameters are hidden and passed via the URL. The parameter

value is an ID, but in the page header my customer would like to see

the corresponding description.

I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.

Parameters!ProcessId.Label

There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):

The process ID parameter value is missing.

This problem can be solved, but I consider it being a quirk. Just use the following SQL for your data set:

select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes

I'm also interested in sharing other (more elegant) approaches.

Thanx for listening and kind regards,
Gerald

sql

Datasets in report header/footer - A Summary of the issue

I know there are already a few threads on this topic but I wanted to create another one - and directly ask - how could this limitation possibly exists - and even more important - why is it still a problem in 2005?

I want to summarize a few of the solutions that have been listed here and elseware online. We've tried them all and they all have some kind of problem:

1) Create hidden field(s) and then reference that field from the header: Doesn't work on any pages where the field isn't printed. Header value will be blank in this case.

2) Create hidden column(s) in the data table and reference that from the header: Works, but again, if the table is showing on every page, the value will be blank. i.e. - if you have two or more tables stacked, you would have to add the columns to every one. A very sloppy and not practical solution.

3) Do it with custom code: I've seen this approach recomended but have never actually seen someone provide a working example. From what I understand, custom code can't access the datasets (I'm using 2000 - maybe 2005 is different here?)

4) Use a queried parameter to hold the value- then reference that in the header: This seems to be the best contender so far. It seems to work sometimes, but on several machines here, the report designer goes into some kind of infinite loop and crashes once the queried parameter is added.

If anyone has any other approaches that I'm not aware of, or has another idea, please post it here. Microsoft - this is a huge problem for many people. Is there any chance of a fix for this any time soon?

Thank you.

~H

did you find a solution ?

|||I am very interested in a solution, too.

Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).

5) Use "Available values: From query"

All

report parameters are hidden and passed via the URL. The parameter

value is an ID, but in the page header my customer would like to see

the corresponding description.

I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.

Parameters!ProcessId.Label

There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):

The process ID parameter value is missing.

This problem can be solved, but I consider it being a quirk. Just use the following SQL for your data set:

select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes

I'm also interested in sharing other (more elegant) approaches.

Thanx for listening and kind regards,
Gerald

|||I am very interested in a solution, too.

Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).

5) Use "Available values: From query"

All

report parameters are hidden and passed via the URL. The parameter

value is an ID, but in the page header my customer would like to see

the corresponding description.

I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.

Parameters!ProcessId.Label

There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):

The process ID parameter value is missing.

This problem can be solved, but I consider it being a quirk. Just use the following SQL for your data set:

select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes

I'm also interested in sharing other (more elegant) approaches.

Thanx for listening and kind regards,
Gerald

Dataset Query Size limit (300 lines)

Hi,
I have a formatted Dataset query with size exceeding 300 lines (vertically). The dataset editor is not allowing me write it. Is this a limitation that the query size should be less than 300 lines or do we need have any way to increase this.
Formatting helps understand the query it in a legible format.
ThanksBy "dataset editor" do you mean the Dataset dialog, generic query designer
or graphical query designer? How long is the query in characters? What do
you mean by "not allowing me to write it"? Do you get an error message?
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"push" <push@.discussions.microsoft.com> wrote in message
news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> Hi,
> I have a formatted Dataset query with size exceeding 300 lines
(vertically). The dataset editor is not allowing me write it. Is this a
limitation that the query size should be less than 300 lines or do we need
have any way to increase this.
> Formatting helps understand the query it in a legible format.
> Thanks|||Apologise for this confusion.....I talked about the generic Query Designer [Report Design Window --> Data Tab -- > Place where the query is written]
I have a query as, say,
"select " & Parameters!col_name>Value & " as myValue from dual UNION ALL ...(almost 18k characters)"...
I wrote the above query as follows (for legibility)
" select " & Parameters!col_name>Value & " as myValue " & (press enter)
" from dual " & (press enter)
" UNION ALL " & (press enter)
... (exceeds 300 lines vertically now)
Here, i notice that only 300 lines come in the window and after that it does not paste or allow me manually write the query. Nothing happens when i write.
Kinldy let me know if it cannot work beyond this or a solution.
"Albert Yen [MSFT]" wrote:
> By "dataset editor" do you mean the Dataset dialog, generic query designer
> or graphical query designer? How long is the query in characters? What do
> you mean by "not allowing me to write it"? Do you get an error message?
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "push" <push@.discussions.microsoft.com> wrote in message
> news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > Hi,
> >
> > I have a formatted Dataset query with size exceeding 300 lines
> (vertically). The dataset editor is not allowing me write it. Is this a
> limitation that the query size should be less than 300 lines or do we need
> have any way to increase this.
> >
> > Formatting helps understand the query it in a legible format.
> >
> > Thanks
>
>|||I am unable to reproduce your problem with a query with up to 600 lines and
30K characters. Could you post the text of your query? Although, it would
seem strange if the specific text made any difference.
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"push" <push@.discussions.microsoft.com> wrote in message
news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> Apologise for this confusion.....I talked about the generic Query
Designer [Report Design Window --> Data Tab -- > Place where the query is
written]
> I have a query as, say,
> "select " & Parameters!col_name>Value & " as myValue from dual UNION ALL
...(almost 18k characters)"...
> I wrote the above query as follows (for legibility)
> " select " & Parameters!col_name>Value & " as myValue " & (press enter)
> " from dual " & (press enter)
> " UNION ALL " & (press enter)
> ... (exceeds 300 lines vertically now)
> Here, i notice that only 300 lines come in the window and after that it
does not paste or allow me manually write the query. Nothing happens when i
write.
> Kinldy let me know if it cannot work beyond this or a solution.
> "Albert Yen [MSFT]" wrote:
> > By "dataset editor" do you mean the Dataset dialog, generic query
designer
> > or graphical query designer? How long is the query in characters? What
do
> > you mean by "not allowing me to write it"? Do you get an error message?
> >
> > --
> > Albert Yen
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "push" <push@.discussions.microsoft.com> wrote in message
> > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > Hi,
> > >
> > > I have a formatted Dataset query with size exceeding 300 lines
> > (vertically). The dataset editor is not allowing me write it. Is this a
> > limitation that the query size should be less than 300 lines or do we
need
> > have any way to increase this.
> > >
> > > Formatting helps understand the query it in a legible format.
> > >
> > > Thanks
> >
> >
> >|||Well, this is really strange.
Please follow the process as,
format the query in an editor (textpad, in my case) with an enter character after every line. Copy this query which is beyond, say 400 lines, and paste it directly in datatset querystrign editor. Are you sure you get the complete query in the window. Try copying it back to the textpad editor and check for the number of lines.
My focus is primarily on having the copy and paste operation performed, as i did. Otherwise, if you can suggest any probable reason for this behaviour..
Thanks.
"Albert Yen [MSFT]" wrote:
> I am unable to reproduce your problem with a query with up to 600 lines and
> 30K characters. Could you post the text of your query? Although, it would
> seem strange if the specific text made any difference.
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "push" <push@.discussions.microsoft.com> wrote in message
> news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> > Apologise for this confusion.....I talked about the generic Query
> Designer [Report Design Window --> Data Tab -- > Place where the query is
> written]
> > I have a query as, say,
> > "select " & Parameters!col_name>Value & " as myValue from dual UNION ALL
> ...(almost 18k characters)"...
> > I wrote the above query as follows (for legibility)
> > " select " & Parameters!col_name>Value & " as myValue " & (press enter)
> > " from dual " & (press enter)
> > " UNION ALL " & (press enter)
> > ... (exceeds 300 lines vertically now)
> >
> > Here, i notice that only 300 lines come in the window and after that it
> does not paste or allow me manually write the query. Nothing happens when i
> write.
> > Kinldy let me know if it cannot work beyond this or a solution.
> >
> > "Albert Yen [MSFT]" wrote:
> >
> > > By "dataset editor" do you mean the Dataset dialog, generic query
> designer
> > > or graphical query designer? How long is the query in characters? What
> do
> > > you mean by "not allowing me to write it"? Do you get an error message?
> > >
> > > --
> > > Albert Yen
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "push" <push@.discussions.microsoft.com> wrote in message
> > > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > > Hi,
> > > >
> > > > I have a formatted Dataset query with size exceeding 300 lines
> > > (vertically). The dataset editor is not allowing me write it. Is this a
> > > limitation that the query size should be less than 300 lines or do we
> need
> > > have any way to increase this.
> > > >
> > > > Formatting helps understand the query it in a legible format.
> > > >
> > > > Thanks
> > >
> > >
> > >
>
>|||I do see a limit of 32K characters in the textbox, after which I can't enter
any more text. I think the 32K character limit is imposed by the textbox
control, not us. That sounds like what you are seeing, except it's a limit
on the number of characters, not the number of lines. I'm not sure why you
have a problem after only 18K characters. At any rate, I don't have any
workarounds for this problem.
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"push" <push@.discussions.microsoft.com> wrote in message
news:5637124F-6032-4D36-A8F3-E73F4F68E44D@.microsoft.com...
> Well, this is really strange.
> Please follow the process as,
> format the query in an editor (textpad, in my case) with an enter
character after every line. Copy this query which is beyond, say 400 lines,
and paste it directly in datatset querystrign editor. Are you sure you get
the complete query in the window. Try copying it back to the textpad editor
and check for the number of lines.
> My focus is primarily on having the copy and paste operation performed, as
i did. Otherwise, if you can suggest any probable reason for this
behaviour..
> Thanks.
> "Albert Yen [MSFT]" wrote:
> > I am unable to reproduce your problem with a query with up to 600 lines
and
> > 30K characters. Could you post the text of your query? Although, it
would
> > seem strange if the specific text made any difference.
> >
> > --
> > Albert Yen
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "push" <push@.discussions.microsoft.com> wrote in message
> > news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> > > Apologise for this confusion.....I talked about the generic Query
> > Designer [Report Design Window --> Data Tab -- > Place where the query
is
> > written]
> > > I have a query as, say,
> > > "select " & Parameters!col_name>Value & " as myValue from dual UNION
ALL
> > ...(almost 18k characters)"...
> > > I wrote the above query as follows (for legibility)
> > > " select " & Parameters!col_name>Value & " as myValue " & (press
enter)
> > > " from dual " & (press enter)
> > > " UNION ALL " & (press enter)
> > > ... (exceeds 300 lines vertically now)
> > >
> > > Here, i notice that only 300 lines come in the window and after that
it
> > does not paste or allow me manually write the query. Nothing happens
when i
> > write.
> > > Kinldy let me know if it cannot work beyond this or a solution.
> > >
> > > "Albert Yen [MSFT]" wrote:
> > >
> > > > By "dataset editor" do you mean the Dataset dialog, generic query
> > designer
> > > > or graphical query designer? How long is the query in characters?
What
> > do
> > > > you mean by "not allowing me to write it"? Do you get an error
message?
> > > >
> > > > --
> > > > Albert Yen
> > > > SQL Server Reporting Services
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > > "push" <push@.discussions.microsoft.com> wrote in message
> > > > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I have a formatted Dataset query with size exceeding 300 lines
> > > > (vertically). The dataset editor is not allowing me write it. Is
this a
> > > > limitation that the query size should be less than 300 lines or do
we
> > need
> > > > have any way to increase this.
> > > > >
> > > > > Formatting helps understand the query it in a legible format.
> > > > >
> > > > > Thanks
> > > >
> > > >
> > > >
> >
> >
> >|||Is it practical for you to write the query as a stored procedure and call it
from the report?
"Albert Yen [MSFT]" wrote:
> I do see a limit of 32K characters in the textbox, after which I can't enter
> any more text. I think the 32K character limit is imposed by the textbox
> control, not us. That sounds like what you are seeing, except it's a limit
> on the number of characters, not the number of lines. I'm not sure why you
> have a problem after only 18K characters. At any rate, I don't have any
> workarounds for this problem.
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "push" <push@.discussions.microsoft.com> wrote in message
> news:5637124F-6032-4D36-A8F3-E73F4F68E44D@.microsoft.com...
> > Well, this is really strange.
> > Please follow the process as,
> > format the query in an editor (textpad, in my case) with an enter
> character after every line. Copy this query which is beyond, say 400 lines,
> and paste it directly in datatset querystrign editor. Are you sure you get
> the complete query in the window. Try copying it back to the textpad editor
> and check for the number of lines.
> >
> > My focus is primarily on having the copy and paste operation performed, as
> i did. Otherwise, if you can suggest any probable reason for this
> behaviour..
> >
> > Thanks.
> >
> > "Albert Yen [MSFT]" wrote:
> >
> > > I am unable to reproduce your problem with a query with up to 600 lines
> and
> > > 30K characters. Could you post the text of your query? Although, it
> would
> > > seem strange if the specific text made any difference.
> > >
> > > --
> > > Albert Yen
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "push" <push@.discussions.microsoft.com> wrote in message
> > > news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> > > > Apologise for this confusion.....I talked about the generic Query
> > > Designer [Report Design Window --> Data Tab -- > Place where the query
> is
> > > written]
> > > > I have a query as, say,
> > > > "select " & Parameters!col_name>Value & " as myValue from dual UNION
> ALL
> > > ...(almost 18k characters)"...
> > > > I wrote the above query as follows (for legibility)
> > > > " select " & Parameters!col_name>Value & " as myValue " & (press
> enter)
> > > > " from dual " & (press enter)
> > > > " UNION ALL " & (press enter)
> > > > ... (exceeds 300 lines vertically now)
> > > >
> > > > Here, i notice that only 300 lines come in the window and after that
> it
> > > does not paste or allow me manually write the query. Nothing happens
> when i
> > > write.
> > > > Kinldy let me know if it cannot work beyond this or a solution.
> > > >
> > > > "Albert Yen [MSFT]" wrote:
> > > >
> > > > > By "dataset editor" do you mean the Dataset dialog, generic query
> > > designer
> > > > > or graphical query designer? How long is the query in characters?
> What
> > > do
> > > > > you mean by "not allowing me to write it"? Do you get an error
> message?
> > > > >
> > > > > --
> > > > > Albert Yen
> > > > > SQL Server Reporting Services
> > > > >
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > > >
> > > > > "push" <push@.discussions.microsoft.com> wrote in message
> > > > > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > > > > Hi,
> > > > > >
> > > > > > I have a formatted Dataset query with size exceeding 300 lines
> > > > > (vertically). The dataset editor is not allowing me write it. Is
> this a
> > > > > limitation that the query size should be less than 300 lines or do
> we
> > > need
> > > > > have any way to increase this.
> > > > > >
> > > > > > Formatting helps understand the query it in a legible format.
> > > > > >
> > > > > > Thanks
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>sql

Dataset limitation

I have encountered a really strange error regarding using Unions in my
datasets.
If i exceed the limit of 4 unions in my dataset, it ignores the data for
those 4 unions and only gives me the data from the 5th union and onwards
until it reaches the limit of again 4 unions, i.e. 8 for the second time
around.
Has anyone else encountered this kind of error/ limitation' Any suggestions
are welcome.
Thanks,
NatWhat type is the data source? SQL Server? What happens if you run the same
query in Query Analyzer?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nat" <Nat@.discussions.microsoft.com> wrote in message
news:288F50E7-DF2F-408E-B416-1E2C1558487E@.microsoft.com...
>I have encountered a really strange error regarding using Unions in my
> datasets.
> If i exceed the limit of 4 unions in my dataset, it ignores the data for
> those 4 unions and only gives me the data from the 5th union and onwards
> until it reaches the limit of again 4 unions, i.e. 8 for the second time
> around.
> Has anyone else encountered this kind of error/ limitation' Any
> suggestions
> are welcome.
> Thanks,
> Natsql

database user

What are the maximum limitation of database user ?
Didnt really tried it, but must be the "magic" number of objects 32768 like
database per instance.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
news:enoIGp8WFHA.2700@.TK2MSFTNGP12.phx.gbl...
> What are the maximum limitation of database user ?
>
|||Hi,
It is 32767 per instance.
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:enoIGp8WFHA.2700@.TK2MSFTNGP12.phx.gbl...
> What are the maximum limitation of database user ?
>
|||Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uaQzk29WFHA.2060@.tk2msftngp13.phx.gbl...
> Hi,
> It is 32767 per instance.
> Thanks
> Hari
> SQL Server MVP
> "Joh" <joh@.mailcity.com> wrote in message
> news:enoIGp8WFHA.2700@.TK2MSFTNGP12.phx.gbl...
>