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 an "ALL" Parameter Value

I am trying to create an "All" parameter. I created a stored procedure that says:

Code Snippet

CREATE PROCEDURE dbo.Testing123

AS

SELECT distinct ID AS ID, ID AS Label

FROM TPFDD

UNION

SELECT NULL AS ID, 'ALL' AS Label

FROM TPFDD
Order by ID
GO

Then I createded a report parameter and set the default to All

I also created a filter that sets the textbox vaule to the report parameter.

In theory I think that when I select ALL it should bring back everything but it is not. It brings back nothing. What am i doing wrong?

In the query that returns the data for the report, I suspect you're doing something like:

WHERE id = @.ID

What you would need to do is:

WHERE id = @.ID OR @.ID IS NULL

I think that you should be setting your default value to NULL instead of "ALL", that would remove the need for your filter.

|||

Is this what you mean?

Code Snippet

CREATE PROCEDURE dbo.Testing123

@.id char

AS

SELECT distinct ID AS ID, ID AS Label

FROM TPFDD

UNION

SELECT NULL AS ID, 'ALL' AS Label

FROM TPFDD

Where ID = @.id or @.id is NULL

Order by ID

Should this make it work?

I am also getting this error: "The report parameter 'pid' has a DefaultValue or ValidValue that depends on the report parameter "pid" Forward dependencies are not valid."

|||

No, I meant for you to put it in the query that is returning the data for your report, not for the parameter.

I am assuming that what you have currently is a parameter as a drop down box where they select the id from the list generated by the query that you have posted above. Then, the user presses "view Report" and a report for that id is executed with data filled in by some other query, that currently has

Where ID = @.id

to return just the data for the id you have selected.

If you add

or @.id is NULL

to the where clause, it will detect if the user has selected ALL and not filter the results.

Maybe I have misunderstood what you're trying to do?

|||You were right! Thank you so much for your help with this

creating a view

I'm trying to create a view to check another view for a record that matches to the value in a textbox of a form. It is giving a syntax error.
This is how my SQL looks like

SELECT aql_level, aql_level_info
FROM v_tblPart_cost_info
WHERE cust_part_num = forms!edit_shiping_sched!cust_part_num

Thanks!!What was the error?
Check books online for correct syntax=.

Creating a primary key in a trigger

I'd like to create a primary key value (incremental) within a trigger and set it in a primary key column.

Any idea anyone? Do I define my trigger as a On INSERT, Instead of INSERT? I tried both but it doesn't seem I'm doing things right.

You can use the identity column. Is there any different logic you are using for incremnetal primary key?

|||

Not quite sure what you're trying to do here- are you not able to use IDENTITY as your auto-incrementing primary key field?

I think if you *really* want to do this you may do something like this:

Code Snippet

create trigger bbbb on bb
instead of insert
as

declare @.int int

select @.int = MAX(col1) from bb

insert into bb (col1, col2, col3)
select @.int+1, col2, col3

from inserted

HTH!

Creating a Cursor out of results of a SP

Is it possible to create a cursor out of the results of a stored procedure.
OR
Is it possible to get just one value.
EXEC sp_columns @.table_name = 'tablename', @.column_name='columnname'
--I want the type_name valueYou can use the INFORMATION_SCHEMA.COLUMN view...

What data do you want?|||I will look at that.

I was able to create a temp table, when i figured out the right syntax, and then selected the appropriate colomn out of the temp table.|||Good for you...

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTable' AND COLUMN_NAME = 'yourColumn'

Creating a cached instance of a report for all variable values

I have a report that takes quite some time to render.
So I would like to create cached-instances of the report for each possible
variable value.
I suppose this is a rather common problem. Is there a solution (script,
program) available somwhere to do this?
(I've tried some things with the scripts but I can't get it to work, I keep
geeting "timed out' errors (although de report execution is set not to time
out) or security exeptions (althoug my user is a RS system user with all the
authoroty))
Thank youDid you ever figure out how to do this?
We thought that creating a data-driven subscription that dumped the report
to a file share when the report was setup to be cached would cache all
possible versions of the report, but we're finding that it's not caching
those executions and the report is being rendered on the first request.
Any thoughts?
Thx, Joel
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:CFFBE8A3-1C94-4FC7-8280-3F68E4ECD19F@.microsoft.com...
>I have a report that takes quite some time to render.
> So I would like to create cached-instances of the report for each possible
> variable value.
> I suppose this is a rather common problem. Is there a solution (script,
> program) available somwhere to do this?
> (I've tried some things with the scripts but I can't get it to work, I
> keep
> geeting "timed out' errors (although de report execution is set not to
> time
> out) or security exeptions (althoug my user is a RS system user with all
> the
> authoroty))
> Thank you|||I did, but it's a workaround. I've written a small programme in VB.net
that will take the name of the report and the parameters and that will
render the report in a web-window for each possible combination of the
parameters.
This does the trick, but it's not what you would call "elegant", I hope MS
will solve this in the next version.
"Joel Rumerman" wrote:
> Did you ever figure out how to do this?
> We thought that creating a data-driven subscription that dumped the report
> to a file share when the report was setup to be cached would cache all
> possible versions of the report, but we're finding that it's not caching
> those executions and the report is being rendered on the first request.
> Any thoughts?
> Thx, Joel
> "Antoon" <Antoon@.discussions.microsoft.com> wrote in message
> news:CFFBE8A3-1C94-4FC7-8280-3F68E4ECD19F@.microsoft.com...
> >I have a report that takes quite some time to render.
> > So I would like to create cached-instances of the report for each possible
> > variable value.
> > I suppose this is a rather common problem. Is there a solution (script,
> > program) available somwhere to do this?
> >
> > (I've tried some things with the scripts but I can't get it to work, I
> > keep
> > geeting "timed out' errors (although de report execution is set not to
> > time
> > out) or security exeptions (althoug my user is a RS system user with all
> > the
> > authoroty))
> >
> > Thank you
>
>

Creating A Boolean Computed Column

Consider the following columns:

EmpID, Status, TermDate

I want a computed column called OkToDisable as a bit value: True if the TermDate is any day before today.

The logic is if DATEDIFF(day, TermDate, GETDATE()) is greater than 0 then the column should be true, otherwise false.

I cant figure out how to do this... SQL keeps complaining that it can't validate the formula.

I was able to create a computed colum from 'DATEDIFF(day, TermDate, GETDATE())' that shows the number of days past the term date, but if I change it to: 'DATEDIFF(day, TermDate, GETDATE()) > 0' it says it can't validate the formula.

Can't you create boolean computed fields?

Thanks.

J

There is no boolean type in T-SQL. The [bit] type is an number type. You can do what you want with CASE:

CASE WHEN DATEDIFF(day, TermDate, GETDATE()) > 0 THEN 1 ELSE 0 END

Steve Kass
Drew University
http://www.stevekass.com
|||

Bit is a boolean type, no? )

I had tried the CASE but I couldn't get it to work properly... It helps when you use the proper syntax. (I had left off the END statement)

Then I had to use a cast, otherwise it was an integer.

Thanks for the reply.

J

Createing tables

I am trying to make a customer management database and need some help. I have two tables, customer and equipment. I want to have a unique value that is in both tables such as custid. I feel I need this to be able to search for all equipment belonging to a specific customer. Help please.Not sure what exactly you are asking, your approach of having a key that identified a customer and of using that the identify the equipment sounds like a fine one.|||

If an equipment can only belong to one customer this would be something like this:

Customers
=======

CustomeriD
(OtherColumns)

Equipment
=========
EquipmentD
(OtherColumns)
CustomerID

If one Equipment can belong to more than one customer you have a N-M relation:

Customers
=======
CustomeriD
(OtherColumns)

Equipment
=========
EquipmentD
(OtherColumns)

CustomerEquipment
================
EquipmentD
CustomeriD

HTH, jens Suessmeyer.

http://www.sqlserver2005.de