Solved

MS SQL REPORTING 2000 - Changing Commandtext during runtime

Posted on 2006-11-25
9
419 Views
Last Modified: 2008-03-04
I need to change the commandtext of a report during runtime because my parameters are too complex. The query to be sent to the report is the result of a front end (ASP.neT) query builder which has atleast 10 selection criteria.

sql="select <fieldlist> from view where 1=1"

sqla=" and f1 in (fx1,fx2,fx3....)"
sqlb=" and f2 in(fy1,fy2,fy3...)"

sql=sql & sqla & sqlb

and so on.  

One way could be to getreportdefnition for the report, save it as an XML file, modify the command text on the file and then createreport back on the report server.

IS there any other way I could achieve this?

kalrani
0
Comment
Question by:kalrani
[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
  • 4
9 Comments
 
LVL 18

Expert Comment

by:chrismc
ID: 18118678
10 parameters is not a big deal for RS, looks horrible on the selection but it could work.
And parameters selection options can be tied to previous parameter selections, simply by including the parameter name in the where clause of the dataset for the second parameter. The second parameters list won't get populated until the first is parameter is selected.

Secondly, building SQL queries on the fly as you have in your example, is not efficient as the command has to be compiled at run-time. The more complex the query, the more of an issue this becomes.
Try and put as much of the SQL in a stored procedure to which you can pass parameters (NB: multi-valued parameters can't be passed to stored procs though!). Stored procedures are pre-compiled and run much faster. If you still feel it's too complex to fit into one routine, put it into several procs, then in RS, using standard SQL call the appropriate proc, e.g.;

If @Param1 = 'Red'
    Exec procRed @Param2

If @Param2 = 'Green'
    Exec procGreen @Param3, @Param4

For multi-valued parameters, consider using views. Again these are pre-compiled queries but you can use them as a table in a query. E.g.;
   Select * From vwABC Where Field1 In (@Param7)

Where vwABC would be

Create View As vwABC
As
  Select Field1, Sum(Field2) From Table1 Group By Field1
Go

This shows the view doing some totalling work so RS doesn't have to.

I have done some very complex reports in RS over nearly 3 years now and have been able to achieve everything within standard RS. The trick is to make SQL do the hard work. Use views and stored procedures to do as much of the work as possible.

Cheers
Chris
0
 

Author Comment

by:kalrani
ID: 18120714
The requirement was such that the end user wanted to be provided with 10 multiselect options to generate reports.

We used a stored procedure with a text parameter. And withing the SP we called the view that joins all the necessary tables for the Report.

We made a front end ASP.net query builder that allows the user to select any combination of paramters from the multiselect list boxes etc, and this query builder provides the where condition in a text string. This text string when passed to the stored  procedure as the text parameter, helps to return matching records from the view.

Works!
0
 
LVL 18

Expert Comment

by:chrismc
ID: 18123209
Hi Kalrani,
As we say,  there is more than one way to skin a cat!
If it works, that's fine.
But I was pointing out that passing snippets of SQL as strings to build up a full query string is highly inefficient. If you are dealing with many tables and/or lots of data, then you will see the problem.
I still think you can use RS and SQL to do all this as standard, without resorting to 'Dynamic SQL'. I understand why you might want another web page as a front end though. But again, multi-select parameters is standard in RS2005 and works well enough.

Let me know if you need me to elaborate.

Cheers
Chris
0
Industry Leaders: 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!

 
LVL 18

Expert Comment

by:chrismc
ID: 18311291
I did answer the question fully, kalrani just chose not to try it in my view.

Cheers
Chris
0
 
LVL 18

Expert Comment

by:chrismc
ID: 18655316
I've just re-read the thread, it looks like the poster resolved it before I answered. I have no objections to what ever you see fit.

Cheers
Chris
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 18731982
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 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