?
Solved

Can not get fields using stored procedure on reporting service.

Posted on 2005-03-16
14
Medium Priority
?
1,067 Views
Last Modified: 2008-02-01
Hi,

I am using Reporting service and stored procedure. My stored procedure works on the "Data" tab,and result can succeed in displaying on this page. But after I press to "Layout" tab, the "fields" is empty. It means that I can not drop the field into the table on the Layout page.

I tried to  set some options on the Dataset dialogue box: in the "Fields" tab, I set the "Field name", "Type" as "Database Field" and "Value", "Parameters" and "Filters". However, what I get on the "preview" page is just the field name s that I set on "Field name" option and no any data on the page.

Any ideas?

Moreover, except the resource on the microsoft website, is there any other resource for Reporting Service, such as some books? Thanks.

I set 500 points on this problem.

0
Comment
Question by:HobbyTown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 14

Expert Comment

by:simon_kirk
ID: 13563975
Try this:

1. Create your stored procedure and test it out from query analyzer.
2. Then copy it to another stored procedure. Have it have all the parameters, they should be there, just don't do anything with them in the second SP. Delete everthing except what is needed to return the fields. If you have a temp table then do a select * from #temptablename. If doing an exec then instead do a normal select.
3. Use the generic query in RS to call the second stored procedure with the dataset type as text (this is important) to get your field list back.
4. Change what you are calling to the name of the real one.

As for resources, MS Hands Online Labs have some good courses (requires registration) on https://partner.microsoft.com/global/40015797

Couple of decent books:

Microsoft SQL Server 2000 Reporting Services (Database)
by Brian Larson
ISBN: 0072232161

Professional SQL Server Reporting Services
by Paul Turley, Todd Bryant, James Counihan, George McKee, Dave DuVarney
ISBN: 0764568787
0
 

Author Comment

by:HobbyTown
ID: 13565505
Hi simon_kirk,

Thanks for your suggestion. I completed step 1, step 2, and half of step 3 which you listed. In the step 3, I completed " Use the generic query in RS to call the second stored procedure ", and my question is :

What is  "with the dataset type as text (this is important)", and how to setup it?
How to do the step 4?

0
 
LVL 14

Expert Comment

by:simon_kirk
ID: 13565716
When you create a dataset it will generally default to a Command Type of Text.  So create a new dataset, ensure that the Command Type is Text and then enter the call to your Stored Procedure e.g. EXEC sp_MyStoredProc
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:HobbyTown
ID: 13565962
Hi simon_kirk,

EXEC Topsales '100', 'high'  
is what I want to use Stored Procedure(SP), and it works. However, I am wanting to use a parameter to replace '100' and use a sencod parameter to replace 'high'. In fact, in the Stored Procedure of "TopSales", there are two parameters.

I am doing a project that needs a runtime page and I can choose parameter for changing that. In details, I want to choose the two parameters on two drop down lists, and transfer them to the SP "Topsales" then display the new data on the page.

How can I do that?

Thanks again.
0
 
LVL 14

Expert Comment

by:simon_kirk
ID: 13566355
Try something like:

EXEC Topsales @myParameter1 = Parameters!myReportParameter.Value ,@myParameter2 = Parameters!myReportParameter2.Value
0
 

Author Comment

by:HobbyTown
ID: 13566903
Hi Simon_kirk,

Do you mean that I need to type:

EXEC Topsales @myParameter1 = Parameters!myReportParameter.Value ,@myParameter2 = Parameters!myReportParameter2.Value

into the text area under Data tab. I tried that and get the error: Incrrect syntax near !.

I also tried: EXEC Topsales @Hundred, @HorL, and after running, the result is : the execution data can be returned, but under the "Layout" tab, the field is empty.

I tried EXEC Topsales '100', 'high', and the result is different: the execution data can be returned, and under the "Layout" tab, the field is NOT empty.

How can I use the parameter and get the result: under the "Layout" tab, the field is NOT empty?

Thanks.

0
 
LVL 14

Expert Comment

by:simon_kirk
ID: 13567082
Apologies, the context I was using was generic, in that you need to input your own names for the parameters!  Don't forget you'll need to create 2 parameters within the report properties that link to the Stored Procedure parameters.

I.e. sp parameter = @myParameter1
Report Parameter  = myReportParameter

0
 

Author Comment

by:HobbyTown
ID: 13567204
I am sorry, and could you please describe your idea about "I.e. sp parameter = @myParameter1
Report Parameter  = myReportParameter" in details?
0
 
LVL 14

Expert Comment

by:simon_kirk
ID: 13602452
You need to link your parameter that your stored procedure needs, to the parameter that you create within the report. E.g

EXEC Topsales @Hundred = Parameters!myReportParameter.Value, @HorL = Parameters!myReportParameter2.Value

Parameters!myReportParameter.Value is the report parameter, which can be created by going to the 'Layout' view, Right Click the 'square' in the top left hand corner and select 'Report Parameters...'

If you haven't yet used parameters in RS, I would recommend you read a bit about them in BOL so that you can get used to how they work.
0
 

Expert Comment

by:ledwards
ID: 13726996
Is your stored procedure dynamic SQL.  
   eg   SET @sql = 'SELECT * FROM Table WHERE integerfield = ' + @pi_integer
          EXEC @sql

If it is you will not be able to view the fields for either dropping them onto your report or to link to your report paramters.

The solution is to temporarily rem out the dynamic sql bit of your Stored Procedure and just write the select statement with out care of report parameters

eg SELECT *
    FROM table

OR if your SQL is more complex just do
   
   SELECT   0 as intfield1
                ,1 as intfield2
                ,'text' as txtfield3
                .... for each field you expect back

Update the stored procedure, go back to Reporting Services in the data tab and click the refresh icon (2 icons left of the run icon).  To ensure it has worked, click the elipses (...) for the dataset and clikc the fields tab.  All the fields you expect to return should be on this tab.  You can now go back your Stored Procedure and rem out the SELECt you have just written (you may need it later) and unrem your dynamic SQL.

In repotring services you will now be able to drop your fields onto the report and map your report parameters to your Stored Procedure parameters.

Hope this helps.
0
 

Author Comment

by:HobbyTown
ID: 13782355
Hi ledwards,

Thanks for your solution. I tried it. After I did "Update the stored procedure, go back to Reporting Services in the data tab and click the refresh icon (2 icons left of the run icon).  To ensure it has worked, click the elipses (...) for the dataset and clikc the fields tab.", all the fields I expect to return are indeed on this tab. But when I go back my Stored Procedure and rem out the SELECt I have just written and unrem your dynamic SQL, the fields dispear again after I press "Layout" tab. The following is my Stored Procedure. Could you please provide a solution to that? Moreover, I am wanting to change the tables to display. Thanks a lot.



SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER  proc Topsales @Hundred varchar(100), @HorL varchar(100)
as
declare @strCmd varchar(1000)
set @strCmd = 'select * from TopSales' + @Hundred + @HorL
exec (@strCmd)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
0
 

Author Comment

by:HobbyTown
ID: 13782869
Hi,

I find the solution, and the followings work. Thank everyone.


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER  proc Topsales @Hundred varchar(100), @HorL varchar(100)

as

declare @strCmd varchar(1000)

if @Hundred = '100' and @HorL = 'high'
      set @strCmd = 'select * from TopSales100high'
if @Hundred = '200' and @HorL = 'high'
      set @strCmd = 'select * from TopSales200high' ---+ @Hundred + @HorL

if @Hundred = '100' and @HorL = 'low'
      set @strCmd = 'select * from TopSales100low'
if @Hundred = '200' and @HorL = 'low'
      set @strCmd = 'select * from TopSales200low' ---+ @Hundred + @HorL

exec (@strCmd)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
0
 

Accepted Solution

by:
PAQ_Man earned 0 total points
ID: 13984823
PAQed with points refunded (500)

PAQ_Man
Community Support Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
How to increase the row limit in Jasper Server.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month10 days, 10 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question