| Author |
Topic |
|
gispro
Starting Member
21 Posts |
 Posted - 2006-01-19 : 08:55:50
|
| I hope this is a suitable forum for questions regarding Reporting Services. Otherwise please advise.-----In our application the result of a (long) complex query is stored as a temporary table with base table records ID's. The full result set may not be stored for security reasons.The result are easily re-generated by issuing this query: Select * From BaseTable where ID In (Select ID from TempIdTable)in the application, and it works fine.In RS, I would like to use the same setup, but I haven't been able to find a way to pass the TempIdTable name as a parameter of the report.Is it possible at all in MS/SQL 2000 ? And if so, how ?--GisPro |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-19 : 09:09:22
|
| You need to use Dynamic SQL Exec('Select * From BaseTable where ID In (Select ID from '+@tableParam+')')MadhivananFailing to plan is Planning to fail |
 |
|
|
gispro
Starting Member
21 Posts |
Posted - 2006-01-20 : 04:43:02
|
| Yes, I know about Exec, it is how I use it currently.But is it possible to use it in Reporting Services ?--GisPro |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-20 : 04:49:45
|
| I am not sure about that. Cant you use Stored Procedure that has the Dynamic SQL and design report based on that sp?MadhivananFailing to plan is Planning to fail |
|
|
|
gispro
Starting Member
21 Posts |
Posted - 2006-01-23 : 09:32:26
|
| Ok, I just (after years of using MS/SQL) discovered the existence of functions that return a table/resultset ;-)How do I get such a function to return the result of this query : Select * From BaseTable where ID In (Select ID from TempIdTable)when given the table name for "TempIdTable" as an argument ?I've tried this sofar Create Function fnGetData ( @tabnam varchar(100) ) return table as return ( Select * From BaseTable where ID In (Select ID from TempIdTable) )But I can't get it to work with an "exec('select ...')" in place of the hard-coded stament above.It would surely solve my problem with dynamic reports, so is this possible ?--GisPro |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-23 : 23:59:55
|
| Dynamic SQL is not allowed inside a FunctionMadhivananFailing to plan is Planning to fail |
|
|
|
gispro
Starting Member
21 Posts |
Posted - 2006-01-24 : 14:29:12
|
| Thanks for putting that mis-notion to rest madhivanan, although it does send me back into the wilderness :..-(--GisPro |
|
|
|
decentviv
Starting Member
12 Posts |
Posted - 2006-01-27 : 09:02:29
|
| Hey i am also new to reporting services. I am using Stored Procedures in Sql Reporting Services. This SP has some parameters and based on these parameter's value Final query is being created at run time. But when i use this SP in VS.Net Reporting Services as dataset then it doesnt show its fields. My procedure is as below : e.g.CREATE PROCEDURE usp_RegistrationBWDates @Name Char(100)ASDECLARE @Query VARCHAR(1000), @Add char(100)SET @Add = "where u_user_id = " + @NameSET @Query = "SELECT * from tbluser " + @AddEXECUTE (@Query)GoThis works fine when i run in in Sql Query analyser & returns records but when we use This Stored Procedure in VS.Net to generate any new Report it doesnt return its in fields. In Data View i open DataSet Properties -> fields tab then no fields are coming with respect to this SP.Please help me out, i am really stuck up. |
|
|
|
gispro
Starting Member
21 Posts |
Posted - 2006-01-27 : 09:48:35
|
| Hi *viv, and welcome to the threadmill :-)Isn't the problem due to missing quotes around your @name parameter ? I.e. 'thisismyname' instead of thisismynameHowever, I think you can transfer such scalar parameters directly to the report via http. Just name them in the report parameters (I think), and the report'll prompt for the value unless it's provided.--GisPro |
|
|
|
decentviv
Starting Member
12 Posts |
Posted - 2006-01-30 : 00:01:33
|
| HI GisPro, Thanks for your reply. But this is not the case, SP is running fine in when i run it separately and Parameter is of numeric type so i dont think single quote is required in that case. But when i attach this SP with an dataset (report) it doesnt show me the list of fields in reporting services. as these fields are not visiable in the report so i am not able to create report from this SP.-Viv |
|
|
|
gispro
Starting Member
21 Posts |
Posted - 2006-01-30 : 03:30:38
|
| Hi Viv,Oh, I assumed "name" was a text since it's given as a char parameter. If it's a number, quotes are not needed.Does it work in RS with a fixed value for the @Name parameter ?I'm currently having trouble connecting the report parameter to the sp parameter, but a fixed value works. I.e. (in your notation) this would work : EXEC usp_RegistrationBWDates @Name = 5--GisPro |
|
|
|
decentviv
Starting Member
12 Posts |
Posted - 2006-01-30 : 06:31:28
|
| HI GisPro, The example i have posted in not the real one in my report i have 6 parameters and based on these parameters i am creating my query dynamically at run time from different table (i.e. Start date, end date, user ... ) if any parameter is not being passed in SP then query changes.SP is running fine in when i run it separately that means when i run it i.e EXEC usp_RegistrationBWDates @Name = 5 it runs fine But when i attach this SP with an dataset (report) it doesnt show me the list of fields in reporting services. as these fields are not visiable in the report so i am not able to create report from this SP. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-01-30 : 07:55:14
|
| Try creating a # temp table in your stored procedure, insert into the temp table with the dynamic SQL, and then select from the temp table to return the result set to Reporting Services.CODO ERGO SUM |
|
|
|
gispro
Starting Member
21 Posts |
Posted - 2006-01-30 : 08:30:39
|
| Hi Viv,I did get my report to work - finally - but a few problems along the way. I finally found the dialog box where I could switch from normal query to stored procedure (the "..." button beside the dataset dropdown).One problem was the missing fields, as in your case, as it seems the fields may get lost when switching from query to sp. I entered them manually under the Fields tab.Then there was the report parameter setting, but I finally got that worked out.Then the data didn't output, and that seems to be a catch22 problem: not a query => no fields, no fields => no query. It's apparently a known problem.The work-around - according to the found faq online - seems to be to fake a query to get VS in line, which I think I did first time around, but I'll try it off course.--GisPro |
|
|
|
decentviv
Starting Member
12 Posts |
Posted - 2006-01-30 : 08:34:45
|
| Even i tried this Temporary table method i.e. Select Field1, Field2 INTO #Tmp From TableNameand after that : Select Field1, Field2 From #Tmp and then associated this SP to Report's Dataset but still it is not showing any fields in my report. |
|
|
|
decentviv
Starting Member
12 Posts |
Posted - 2006-01-30 : 08:47:10
|
| We have one more way to add fields manually in report by 'Adding New Field' option from field tool bar, where we have to specify Field's Name & its Associated Database Field's Name, but this method also not worked. As we refresh our dataset in VS.net all added fields get deleted. So this also not working for me :-( |
|
|
|
gispro
Starting Member
21 Posts |
Posted - 2006-01-30 : 17:16:48
|
| Hi Viv,I think your question was ahead of me initially, as it seems I'm now stuck at the same point you are. :-(With no idea on how to remedy it, except maybe by looking into the raw RDF XML code.I do have a connection to an expert in US, and if he can solve it for me, I'll post it here for you as well.--GisPro |
|
|
|
decentviv
Starting Member
12 Posts |
Posted - 2006-01-31 : 01:07:30
|
| Thnx GisPro. Please do post me if you get the solution for this. |
|
|
|
gispro
Starting Member
21 Posts |
Posted - 2006-02-01 : 07:24:23
|
| Hi Viv,I've found the problem to be an entirely VS.net GUI problem, not a Reporting Services problem.When switching data source from "text" (a view or an "exec ...") to "stored procedure", VS.net looses the entire Fields collection. When opening the report as XML, the Fields collection under Datasets/Dataset is entirely missing.If you create a new report with the same original data source (view or exec), and copy the entire Fields collection from its XML into the first report's XML, it works ! Preview as well as deployment.Entering fields manually in the Dataset dialog Fields tab do produce a Fields collection, but the format is all wrong, and thus doesn't work when previewed/deployed.The US expert hasn't returned to me yet, but I've informed him of the above conclusions. He may suggest a better solution than the XML copy'n'paste method.--GisPro |
|
|
|
decentviv
Starting Member
12 Posts |
Posted - 2006-02-01 : 22:54:46
|
| Thanks for your cooperation & the valuable time you spent for us. I was continuously tracking the problem since last two days and finally we got the solution. I am concluding the solution in following steps: 1. Create Dynamic Query based on parameter’s value 2. Insert result set cursor into a Temporary table ( Create Temporary table in the beginning of SP and use insert into #T Select field1, field1 from table ) 3. Select data from Temporary table in the end of proc (Select * from #T) 4. Bind this SP with Report’s Dataset.5. Now all the fields are coming in report. So this way things are going in right direction for us. So once again thanks for your help.Cheers-Vivs |
|
|
|
chumsat
Starting Member
1 Post |
Posted - 2007-06-14 : 10:44:01
|
| I have a question like, if i dont know how many columns will generate from dynamic query, then how can i create a temporary table?Let me know is there any option for creating temporary table without knowing how many columns? |
|
|
|
Next Page
|
Reporting Services parameter question