Dynamic SQL Query and CRM Report parameter

Hello Everyone,

Right now I am working on a  CRM reporting. I am using SQL server 2005 
Reporting services.

In my Report I want to pass Field name as a parameter from Report interface.
My query is as Following:
------------------------------------------------------
DECLARE @SQL AS nvarchar(1000)
DECLARE @Param1 AS nvarchar(50)
SET   @SQL = 'Select Field1, Field2,'+@Param1+' AS Field3 FROM Table1'
------------------------------------------------------
@Param was difined in 'Report Parameters' and I initialed it. I use 
Field1,Field2 and Field3 in report layout.
I use @Param1 as one of my Field name in sql query of report, but it give me 
error that:
"Report item expressions can only refer to fields within the current data 
set scope or, if inside an aggregate, the specified data set scope."
Also my Datset have not any field in Dataset Menu.

If anybody know answer pls reply me fast my deadline for this report is near!

-- 
Mohsen Ahmadi,
msnahm@hotmail.com
IR-0912 *** ****
0
Mohsen (12)
6/22/2008 8:05:00 AM
crm 35858 articles. 1 followers. Follow

0 Replies
526 Views

Similar Articles

[PageSpeed] 18

Reply:

Similar Artilces:

Detail Tax Collected and Tax Analysis report totals should match
The 'Detail Tax Collected' and 'Tax Analysis' report totals do not match. We know the reasoning is that the 'Tax Analysis' report rounds the totals, which causes the two reports to be off by just a few cents to sometimes a few dollars. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click &...

SQL Server Issues Error# -2147467259
I have 1 machine on our network is running Win 98 to accomadate our printer/copier. The rest are XP Pro. We had a problem where the network locked up and had to reboot to get up and running again. But now I cannot connect the Win 98 machine to the database through administrator. I have tried uninstalling and re- installing RMs to no avail. The error I receive in the admin is: Error# -2147467259 [DBNETLIB][CONNECTIONOPEN (CONNECT())] SQL Server Does Not Exist or Access Denied. Help. I don't know if it will fix your problem, but have you tried typing the IP address of the SQL server w...

CRM Email account for Exchange has thousands of emails in it.
Is it normal for Exchange to store all emails sent to the CRM email account used for the Exchange router? Do I need to do something in CRM or more likely Exchange to purge these? We have a client that has 7000 emails in it after a couple months. -- Thanks, Brian The emails in the CRM email account should be auto-purged. No user intervention is required. On the Exhange box, - Check that the CRM-Exchange Email Router Service is started - Check the Event log. Thanks for this. So now I have found the the email address set up in when installing the CRM Exchange router is wrong...

Budgets - Bills - Reports
Summary: I have a recurring bill that appears in my budget in the correct category – with the monthly expense of $9.95 and M07 shows that it has budgeted $9.95 to pay for that bill. When I look at that category in several reports, the reports show that I have $0 set aside for that category, and that I am overspent in that category by $9.95. Can anyone help explain how/why budgets and bills seem to have a relationship, but this relationship does not carry over into the actual reports? The Details: I have typed in all of my bills into the bills section. For simplicity, I will only tal...

Multi Table Inser Query
Hello ... im new to Access so i need some expert help. im creating an application for my organization where i have this one form in which i collect all the required data and need to save it in table but the problem it this form is connected to to different tables. how can i save the data via query on one click to two different tables ? If you have several tables, each with essentially the same fields, and you need to connect up your form to different tables at different times, you have not built a database. That kind of thing might make sense in a spreadsheet, where you create a sheet ...

Report by Sales Rep
RMS should have a report that lets you see the items a sales rep has sold for a given time period. Or does that already exist and I can't find it? Thanks, Nick https://mbs.microsoft.com/downloads/customer/Custom_Detailed_Sales_by_Sales_Rep.zip Rob <nick@realkiteboarding.com> wrote in message news:FNf4e.4650$Kx5.1158@fe03.lga... > RMS should have a report that lets you see the items a sales rep has sold > for a given time period. > > Or does that already exist and I can't find it? > > Thanks, > Nick > Thanks Rob "Rob" <rob@retail...

Sorting in select query. manually
Hello, I have a select query and I would like to sort on the field month. but I want to be able to specify the order myself (not descending nor ascending). how can I do this? thank you! You create a table with the data you which you would see in the Month field and the sort order that you want. You then join these two tables in the query and sort on it. Something like this: Month MonthSort Jan 12 Feb 11 Mar 3 and so on. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycl...

Outlook client for CRM V3
We have a client who has users who want to make changes to the 'my contacts' local data group, so they sync their Outlook contacts with CRM. They all use the desktop client. But the save button is greyed out. They have a CRM role assigned with the Outlook sync permission and they have local admin rights. I've also checked they have write permissions to the C:\Documents and Settings\User.Name\Application Data\Microsoft\MSCRM folder and it's subfolders. What else could be disabling the save option? Becuase My Contacts is a default group, I don't think you can make c...

Active Directory Problem
Hi Everyone, Hopefully you can help me out a bit here. Long story short, we had a failure last week that we were unable to recover Active Directory from. As such, we've had to rebuild AD from scratch - now the domain name and all user accounts are recreated and identical to the previous setup. However, I can no longer access CRM. I assume CRM 4.0 is authenticating on, not only AD membership, but based on an AD GUID of some sort - which I can't replicate. How can I get back in to re-load my users? Thanks, Craig Hi, Did u check if the users were part of CRM groups created ...

Dynamic SQL 04-07-10
I have a procedure that does the same set of 3 Update Queries 4 times. The only difference in the 3 queries is whether the status is 1, 2 or 3. So I can combine each of the 3 into 1 query (Where Status Between 1 and 3) and end up with 4 queries. These 4 are essentially the same except one of the JOINS is a different table or view. I was thinking of combining them into one dynamic expression and having only one select statement where the INNER JOIN changes depending which of the 4 loops I am doing. In each case, I am still doing 4 statements. I assume the 4 statements ...

External data query using MAX/SUM
I'm trying to create an external data link using Microsoft Query. Ho do I write SQL in Microsoft query that will return a max date and su an amount field for the max date only? Current SQL is as follows: SELECT DISTINCT CB_PORTFOLIO_DMN.PORTFOLIO_NAME Max(CB_POOL_PORTFOLIO.POST_DATE), Sum(CB_POOL_PORTFOLIO.EXPOSURE_AMT) Count(CB_POOL_PORTFOLIO.NBR_AVG_MATY_YRS) FROM CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO, CB06U.CB_PORTFOLIO_DM CB_PORTFOLIO_DMN WHERE CB_PORTFOLIO_DMN.PORTFOLIO_ID = CB_POOL_PORTFOLIO.PORTFOLIO_ID GROUP BY CB_PORTFOLIO_DMN.PORTFOLIO_NAME This returns the sum of the exp...

Clause order in SQL select query
Are there any rules that govern clause order? Does the parser care? Obviously SELECT must be first but does WHERE have to precede ORDER BY? This question arises because I have a situation where the VBA code would be easier if the WHERE could be the final clause. That way I could have an unchanging SELECT, FROM, ORDER BY string and just tag the WHERE; on the tail. My thinking then turned to whether there is a mandated order for other clauses too. Is there an article somewhere to explain it? -- Len ______________________________________________________ remove nothing for v...

select query of parent and child in one row
I have a parent table and a child table one parent has between 1 and 6 children in the child table I wont a query to have the parent and the child in one "row" how do I create that? thanks for helping me Your table structure is not correct for a relational database but this will get you there. This has three but you get the idea and expand on it. Place the table in the design grid as many times as you have field to combine. SELECT MultipleFieldData.x, MultipleFieldData.a & IIf(MultipleFieldData_1.b Is Null,Null," " & MultipleFieldData_1.b) & IIf(MultipleF...

Money Reports #3
I was wondering if anyone could tell me why MS Money counts credit card payments from my checking account as income?? Does anyone know how I can fix the problem? Thanks In microsoft.public.money, jagpit wrote: >I was wondering if anyone could tell me why MS Money counts credit card >payments from my checking account as income?? Does anyone know how I >can fix the problem? Use a single Transfer transaction from bottom of the list under SPECIAL. This assumes you are using "Advanced" register setting. "jagpit" <fbbc83@yahoo.com> wrote in message news:11...

CRM Mobile and Orange SPV Phones
Does anyone know if CRM Mobile 1.2 will work on Orange SPV mobiles? Thanks, Adam Pedder CRM Mobile will run on Pockect PC 2003 devices, not spartphones at this time. Jason Hunt Technical Product Manager Microsoft Business Solutions CRM This posting is provided "AS IS" with no warranties, and confers no rights. "Adam Pedder" <adam@nothereatall.com> wrote in message news:uH4N9SDkEHA.3612@TK2MSFTNGP12.phx.gbl... > Does anyone know if CRM Mobile 1.2 will work on Orange SPV mobiles? > > Thanks, > > Adam Pedder > > Currently smartphones such...

Too Few Parameters, 1 expected
I'm getting the error too few paremeters, 1 expected with the code below. I'm getting the error at " Set rst3 = dbs.OpenRecordset(strSQL3)". Any help would be much appreciated On Error GoTo Err_cmdCreateRecords_Click Dim intResp As Integer Dim intCount As Integer If ReportDate = "" Or IsNull(ReportDate) Then MsgBox "Please enter a valid report date." ReportDate.SetFocus GoTo Exit_cmdCreateRecords_Click End If intResp = MsgBox("This function will add records for the reporting date specified. Do you wish to continue?", vbO...

User Access Reporting
Hello Everybody, Does anyone know of a better way to audit security for individual users that the User Security report? Even with the options set to include Accessible Items only, we're finding the report for a single user runs well over 100 pages. With that kind of bulk, both the generation and review of the reports becomes a major endeavor. We're currently using GP9, with plans to go to 10 this year. Thanks, Jeremy Hi Jeremy Have you looked at the Rockton Software add-on called Security Auditor? I think it will do everything you are looking for and more. -- Fliehigh &q...

Print button on Report form
Hi all :-) =20 Access 2007 Pro Plus, Vista Ultimate SP2 I am preparing a small app for a friend who is handicapped, and am = trying to make things as simple as possible for them to use. I am trying = to limit the number of functions and clicks necessary to accomplish = tasks as much as possible, and would like to try an idea. I want to add a small button on the report form that will print the = report when clicked. I have done this on display forms to print as a = report, but, not on a report form. In trying to add such a button to the = report form in 2007 I am not being offered...

how to insert chart for imported data in a report?
plz reply soon if you know Please use the large white space under the subject to enter a detailed question. It also helps to read some past threads that might pertain to your question (whatever it is). -- Duane Hookom Microsoft Access MVP "varsha from pilibhit" wrote: > plz reply soon if you know ...

Public Folder Query
I don't want to actually use OWA or the Outlook application I want to know if there are is some way of having a web page display Public Folder information. For example, a calendar in our Public Folders contains meeting room bookings. I would like to display booking information on a web page, pulled in from the Public Folder, and even possibly create calendar bookings without using Outlook or OWA to go into the Public Folder Is that ok? Hope that makes sense Cheers Tommy Yes, it is possible to build custom web pages that display data from public folders, but such applications can b...

CRM 1.2 SBS 2003 Can not install exchange email router
Logged on as domain admin throughout. Installed CRM successfully, followed install instructions, did not install to local host installed web to a hostheader of "companyweb" port 80. during Exchange email router setup I get an error when specifying CRM server and web - "one or more os the MS CRM servers you listed is invalid or you do not have administrator priveleges on that server...." server name: sbssrb01 web http://companyweb If I go into IIS can not open CRM web get - http Error 403 - Forbidden: Access is denied. All services are on 1 server "sbssrv01&qu...

SQL timeout
Is anyone familiar with a timeout setting for SQL. As long as we continue to use CRM the speed changing from one window to the next is fine, but if we go to another program(outlook, excel, word, etc.) and then return to CRM there is anywhere from 20 to 30 sec. delay when attempting to change pages in CRM. AB ...

Inbound email statistics/reports/monitoring
I'm looking for a way to monitor my exchange server's inbound email so that I can get a list of the biggest spammers. At the moment I am getting spammed by 10K+ undeliverable messages a day so I want to figure out who the more persistant servers are and block future messages. With this much activity on my server I am worried that it is degrading the performance. I know most of you would tell me to RTFM but I think this is the quickest way for me to get help while I work on other issues. Thanks for your help, Ben What version of Exchange? If you're not running Exchange ...

viewing crm emails in Outlook inbox
In crm 1.2 when a workflow fired off an email message it was sent to the recipients CRM and Outlook inbox, it appears as 3.0 doesn't do the same. Is there a way to get the email messages generated by a workflow in the CRM to appear in the recipients inbox. My issue is that people are not keen at having to track email messages in two places and they don't want to use CRM as there main interface for emails. Thanks, John ...

Reference field in Distribution Query
Hi there, I am using GP10.0. While using Distribution Query, i have selected "Refrence" (8) as one of column. I expecting "Distribution Refrence" from General Ledger Entry should be populated. But i am not getting it. (It is showing Blank values in Excel. I am sure we are having Distribution Refrence in GL entries) Can you pls. let me know, Is my understanding correct? And also in Distribution Query how we can bring Distribution Refrence from G/L entries? Thanks, Loganathan S Logu, Distributioin reference field is available in GL10001 (GL_TRX_LINE...