dont want that... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

dont want that...

Author

Topic

jhermiz

3564 Posts

Posted - 2004-11-05 : 08:51:14

I had originally used the generic query designer to write my SQL.
Had it working fine and had a parameter where the end user had a drop down from a list...fine and dandy...

Decided to rewrite this into a sproc so I Had this:


CREATE PROCEDURE rsp_user_profile	 @ClientID integer
AS
BEGIN
SET NOCOUNT ON
SELECT     dbo.Login.Login, dbo.Login.FirstName, dbo.Login.LastName, dbo.Login.Title, dbo.Login.Telephone, dbo.Login.EmailAddress, 
                      dbo.FunctionalGroup.FunctionalGroup, dbo.Login.Manager, dbo.Client.Client, dbo.Client.ClientID
FROM         dbo.Login LEFT OUTER JOIN
                      dbo.FunctionalGroup ON dbo.Login.FunctionalGroupID = dbo.FunctionalGroup.FunctionalGroupID
	      LEFT OUTER JOIN dbo.Client ON dbo.Login.ClientID = dbo.Client.ClientID
WHERE
dbo.Login.ClientID=@ClientID
Set NOCOUNT OFF
END
GO

The problem is when the parameter comes up, its a simple text box forcing the user to enter it in. What I wanted was a drop down...

Now what I tried to do was go to the parameters screen and change the "Available Values" from non queried to from query. Then I selected "ClietID" for value field and "Client" for label field so that the user can see the Clients and not have to enter a number.
I clicked ok and tried to view the report. I get some error:

"The report parameter 'ClientID' has a defaultvalue or a valid value that depends on the report parameter "ClientID". Forward dependencies are not valid"

So I'm like huh :-)

Jon

jhermiz

3564 Posts

Posted - 2004-11-05 : 11:25:44

Maybe I should modify my sproc and not even pass in a parameter ????

And just define the parameter in the report rather than the stored procedure ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-05 : 12:27:09

If you want a drop down, then you'll need to add another data set to that report. The data set would be a query that gets the values to be displayed in the drop down list. You'll then link that data set to the parameter. Do you know how to add another data set?

Tara

jhermiz

3564 Posts

Posted - 2004-11-05 : 13:07:08

Yes I can add another data set...but...

When I do that..it gives me two parameters, the ClientID (looking for a number) and the ClientID / Client (drop down box)

Thats the problem I'm running into. My stored procedure looks fine to me, I could probably get it to work by getting rid of the parameter in the sproc, but I dont want to do that :(.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-05 : 13:08:44

You need to remove one of the parameters. Go to Report/Report Parameters. What's your list show for parameters? You should only have one ClientID. Both data sets can refer to the same parameter.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-05 : 13:09:50

You do have two data sets, right? Two stored procedures, right? One for the report rendering, the other for the drop down list...

Tara

jhermiz

3564 Posts

Posted - 2004-11-05 : 13:16:18

yes two data sets one is a sproc, the other is really just a query based one (for the drop down list...)
I know exactly what you mean and I have done it this way...but I am not getting the expected results.

Let me tell you exactly what I have:
I created one dataset (the sproc) that I posted in the beginning of my thread.
This sproc by default created a Parameter ClientID...
I then created another dataset as such:


SELECT     ClientID, Client
FROM         dbo.Client
WHERE     (ClientID = @ClientID)

After that I went into the report parameters and deleted the first parameter (the default one)
I then added another one:

Gave it the following properties:
Name: Client
Prompt: Client:
Data Type: String
Allow blank value

In available values I selected: From query
I then selected dsClients as my dataset
Value Field:ClientID
Label Field: Client
Default Values: None

Clicked ok...
I get like three warnings:
The value expression for the query parameter @ClientID refers to a non existing report parameter 'ClientID' (I get this twice)
I also go the report parameter Client has a default value or valid value that depends on the report parameter "ClientID". Forward dependencies are not valid.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-05 : 13:22:00

You shouldn't have deleted the default one.

Go to the properties of your sproc data set. Go to parameters tab. You should have @ClientID for name and =Parameters!ClientID.Value for value. Same goes for the second data set. Then in Report Parameters, you should have one parameter named ClientID. Prompt would be Client:. Then Available values, you select from query and select the query data set not the sproc. Then select which column.

Tara

jhermiz

3564 Posts

Posted - 2004-11-05 : 13:27:03

Tara I have done this...

I get that same:

the report parameter Clientid has a default value or valid value that depends on the report parameter "ClientID". Forward dependencies are not valid.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-05 : 13:27:59

To clarify parameters, if you added up all of your parameters in all of your data sets for a particular report, that list should equal the list found in Report Parameters. The names will be the same minus the @ sign. So don't delete them from Report Parameters when Reporting Services Designer automatically added it for you based on the data sets.

Tara

jhermiz

3564 Posts

Posted - 2004-11-05 : 13:33:05

Initial sproc dataset:

called dsRptUsers its type is StoredProcedure..the stored procedure is:


CREATE PROCEDURE rsp_user_profile	 @ClientID integer
AS
BEGIN
SET NOCOUNT ON
SELECT     dbo.Login.Login, dbo.Login.FirstName, dbo.Login.LastName, dbo.Login.Title, dbo.Login.Telephone, dbo.Login.EmailAddress, 
                      dbo.FunctionalGroup.FunctionalGroup, dbo.Login.Manager, dbo.Client.Client, dbo.Client.ClientID
FROM         dbo.Login LEFT OUTER JOIN
                      dbo.FunctionalGroup ON dbo.Login.FunctionalGroupID = dbo.FunctionalGroup.FunctionalGroupID
	      LEFT OUTER JOIN dbo.Client ON dbo.Login.ClientID = dbo.Client.ClientID
WHERE
dbo.Login.ClientID=@ClientID
Set NOCOUNT OFF
END
GO

So doing this as you know...creates the default parameter...so I clicked on parameters for this dataset.
I see in Name: @Client (which was originally ClientID, but you told me to change it. In value it has "=Parameters!ClientID.Value.

Fine ... now I create another dataset called dsClients: it has the following query:
SELECT ClientID, Client
FROM dbo.Client
WHERE (ClientID = @ClientID)

In the gui or grid it shows both columns clientID and client..in the criteria for clientID i put in =@ClientID.
Anyhow I go to the parameters for this DS I have:
Name: @Client
Value: =Parameters!ClientID.Value

Fine...
Now I go to my report parameters, it shows me one called ClientID.
Name: ClientID
Prompt: Client
Data Type: Integer
From Query: dsClients
Value Field: ClientID
Label Field: Client

jhermiz

3564 Posts

Posted - 2004-11-05 : 13:35:53

When I get rid of the @ClientID parameter in dsClients and run the report at first it looks good...it asks for a client drop down. I select the client and click view report i get the error
An error has occurred during report processing. Query execution failed for data set 'dsRptUsers'. @Client is not a parameter for procedure rsp_user_profile....tara you do know that this stored procedure @ClientID and @Client...

jhermiz

3564 Posts

Posted - 2004-11-05 : 13:40:05

Ok recreated these...did exactly what you said...both datasets have ClientID as a parameter with the value Parameters!ClientID.Value.

I then went to the report parameters and have one parameter named ClientID with prompt Client. Change the from query...
again tried to run it and got that error:

The report parameter ClientID has a defaultvalue or a validvalue that depends on the report parameter "ClientID. Forward dependencies not valid.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-05 : 13:47:41

I am now realizing what is going on. You are going to need two parameters. One for @ClientID, so the name would be ClientID. Then another one called Client. Client is going to be the one pointing to your second data set. Once a user types in ClientID, the Client drop down list should change and fill with the data according to ClientID.

Tara

jhermiz

3564 Posts

Posted - 2004-11-05 : 13:54:37

no but i dont want that :(...i dont want anyone to type anything in...

All I need is one drop down parameter....of a client the actual name of the client not a digit or anything. :(
I cant believe I'm not getting something so simple, if I dont use stored procedures it works fine...right when I use a sproc I have problems with this...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-05 : 13:59:14

How are the queries going to know which ClientID to use then?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-05 : 14:00:15

Is Client the natural key for your Client table? Is it unique?

Tara

jhermiz

3564 Posts

Posted - 2004-11-05 : 14:03:03

ClientID is the primary key of my client table.
Client is the actual name for instance, valid rows include:


ClientID  |   Client
1              Detroit
2              Cleveland
3              Miami
.........

My users table: Login has ClientID as a foreign key.

Like I said If I don't use stored procedure this works out fine...I just dont know how to set this up
using stored procedures. I know my sproc is right cause I can execute it and pass parameters to it. Plus
the report generates as I said before however its a text box forcing the user to remember client id numbers which I dont want.

Thanks / Jon

jhermiz

3564 Posts

Posted - 2004-11-05 : 14:06:51

If I do...

exec rsp_user_profile 1

It knows to display all rows where the clientid is = 1 fine...
But I don't want my end users to know what ClientID numbers are...they are not gonna remember ClientID=20 is LA etc etc...
Thats why I want to have a drop down...all Im looking for is one parameter in the output of the report (a drop down of the available clients).

jhermiz

3564 Posts

Posted - 2004-11-05 : 14:10:57

I got it!

jhermiz

3564 Posts

Posted - 2004-11-05 : 14:12:08

Tara,

Looks like I just need one parameter for the sproc dataset. The other one has no parameters :).
Then in the report parameter, I put in ClientID with name Client type is string. I then used the query from my 2nd dataset and now it works :)

Easy!

Jon

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources