Creating an All option for parameter value in GUID format

I am working with SRS 2005 SP1 which no longer has the "ALL" option available on parameters. I am trying to create an "ALL" entry in a picklist so it can be used in a where clause for a dataset. I have a dataset with a union statement that creates a list of CRM usersids and names and an entry with a dummy guid with the name "All". Parameter is defined as a string type, with a dataset providing a list of users (label field) and their corresponding GUID value (value field), along with the an "All" entry.

select systemuserid, fullname
from FilteredSystemUser
Union
Select '00000000-0000-0000-0000-000000000000' as systemuserid, ' All' as fullname
order by fullname

The issue I am running into is implementing logic in another dataset referencing my parameter.

All is fine in the where clause if it is structured "where ownerid in (@.Users)" but if I try to add logic to check for the "All" option "where (ownerid in (@.Users) or @.Users = '00000000-0000-0000-0000-000000000000') it errors out.

How do you impement "All" when you're dealing with a GUID type field? Thanks.

Have you looked at SSRS SP2? It puts the Select All option back. You can get it HERE

R

|||Thanks. We will be installing SP2.

Creating A3 size reports for PDF export

Hello,

I would like to create "big" reports designed for printing. I have to get a pdf version of the reports.

When I try to use the pdf format, the report appears to be truncated to match the A4 paper size, instead of the bigger A3 design I made.

Do you have experience on this problem? What can I do?

Thank you

David

to display the report in A3 it to so easy just make the Pages size 42cm*59.4cm it will give the required paperssql

Create variables with same format in one single step?

I would need to create multiple variables with the same format for
future update, what I did is listing them separately, is there an easy
way to combine them in one step? please see query below. Thanks a lot!

--Current Query--
SELECT cast(0.0 as money) as balance_1
,cast(0.0 as money) as balance_2
,cast(0.0 as money) as balance_3
,cast(0.0 as money) as balance_4
,cast(0.0 as money) as balance_5
,cast(0.0 as money) as balance_6
,cast(0.0 as money) as balance_7
,cast(0.0 as money) as balance_8
,account_no
,XXX
INTO table1
FROM account

Can I do something like this? This one didn't work.

SELECT balance_1 to balance_8 (cast 0.0 as money)
,account_no
,XXX
INTO table1
FROM accountIt's not a single step but this might work for you:

This might work for you:

declare @.balance_1 money,
@.balance_2 money,
@.balance_3 money,
@.balance_4 money,
@.balance_5 money,
@.balance_6 money,
@.balance_7 money,
@.balance_8 money

set @.balance_1 = 0
set @.balance_2 = 0
set @.balance_3 = 0
set @.balance_4 = 0
set @.balance_5 = 0
set @.balance_6 = 0
set @.balance_7 = 0
set @.balance_8 = 0

SELECT @.balance_1,
@.balance_2,
@.balance_3,
@.balance_4,
@.balance_5,
@.balance_6,
@.balance_7,
@.balance_8,
account_no,
XXX
INTO table1
FROM account|||I'm confused. There are no variables in the code you posted - you are
creating a table with eight columns. There is only one step in a query
- the whole query operates as one logical unit.

What you seem to be looking for is a syntax shortcut that saves you
typing out the column names. The best shortcut is probably to click and
drag a list of names from the Object Browser in Query Analyzer then
search and replace the rest. I expect you could do that in much less
time than it took to write out this question. :-)

Your query itself looks a little strange. Are you sure you can't
calculate the balances with a single query rather than SELECT followed
by an UPDATE / INSERT?

--
David Portas
SQL Server MVP
--|||(rong.guo@.gmail.com) writes:
> I would need to create multiple variables with the same format for
> future update, what I did is listing them separately, is there an easy
> way to combine them in one step? please see query below. Thanks a lot!
> --Current Query--
> SELECT cast(0.0 as money) as balance_1
> ,cast(0.0 as money) as balance_2
> ,cast(0.0 as money) as balance_3
> ,cast(0.0 as money) as balance_4
> ,cast(0.0 as money) as balance_5
> ,cast(0.0 as money) as balance_6
> ,cast(0.0 as money) as balance_7
> ,cast(0.0 as money) as balance_8
> ,account_no
> ,XXX
> INTO table1
> FROM account
> Can I do something like this? This one didn't work.
> SELECT balance_1 to balance_8 (cast 0.0 as money)
> ,account_no
> ,XXX
> INTO table1
> FROM account

You could build an string with the SQL statement by iterating from
1 to 8, and use EXEC() to execute that statement. However, this is
definnitely not recommendable.

I have no idea what you are up to, but columns are usually distinct
entities. If you find that you need balance_1 to balance_8, maybe
you should make them rows instead. That is how you work with array
data in SQL, rows with pairs (index, value).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To answer the question I think you are trying to ask: No, there are not
arrays in SQL.

You create a table with a CREATE TABLE statement and you have to know
what it looks like before you type in the command.

You need to put the column names in the SELECT list. Using the SELECT
* option is a really bad idea for production code.

You need to give every column a meaningful name from your data model.
But fromthe look of this, you do not have a data model and are writing
code on the fly.|||Thank you ALL so much!!

I do have a bunch of other variables in the table (about 20), I only
listed account_no as an example of the variables.

Doing the calculation in a single step instead of using update is a
great idea, but for some reason, it created duplicates. I haven't found
out if it is due to the database duplicates or because of my query. I
will do further research... Thanks again...

--CELKO-- wrote:
> To answer the question I think you are trying to ask: No, there are
not
> arrays in SQL.
> You create a table with a CREATE TABLE statement and you have to know
> what it looks like before you type in the command.
> You need to put the column names in the SELECT list. Using the
SELECT
> * option is a really bad idea for production code.
> You need to give every column a meaningful name from your data model.
> But fromthe look of this, you do not have a data model and are
writing
> code on the fly.