?
Solved

Dynamics CRM 4.0 Reports Attributes Problem

Posted on 2011-05-03
12
Medium Priority
?
681 Views
Last Modified: 2012-06-22
Dynamics CRM rollup 14
I am creating a report in SSRS that I want to import into CRM.  I need to report on a custom attribute in the opportunity.  The view opportunity provides the numeric value of the attribute, but not the string value associated with it.  There is a view for attribute_campaign and attribute_email, but not opportunity.  Where do I find the string value of an opportunity custom attribute?
0
Comment
Question by:sbdt8631
  • 7
  • 5
12 Comments
 
LVL 6

Expert Comment

by:processguru
ID: 35513012
Please give more information about your custom attribute (type, name, etc.) and please post your current SQL query and resulting output so I can understand what you're asking.
0
 
LVL 1

Author Comment

by:sbdt8631
ID: 35513338
SELECT     accountidname, campaignidname, customeridname, new_funnelpositionname
FROM         FilteredOpportunity
No output upon running.
Now I am confused. Using SQL Server MGMT Studio I found my custom attribute, new_funnel_positionname, located in the FilteredOpportunity view.  When I open the view in the mgmt studio I have 35 records displayed.  When I add the view to my report with the above code I get no records.  When I change it to select * I get no records.  I tried a simple crystal report on the same view and get no records.  What's up?  What am I doing wrong?
0
 
LVL 6

Expert Comment

by:processguru
ID: 35513512
is the name of the attribute

new_funnelpositionname

or

new_funnel_positionname

You reference it both ways above? Maybe it's erroring on an unknown column name?
0
Independent Software Vendors: 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 6

Expert Comment

by:processguru
ID: 35513565
You will want to use the CRMAF_ prefix in your query to enable auto-filtering for "run on current record" capabilities in CRM. Try the following query:

SELECT CRMAF_Opp.accountidname,
    CRMAF_Opp.campaignidname,
    CRMAF_Opp.customeridname,
    CRMAF_Opp.new_funnel_positionname
FROM Filtered Opportunity AS CRMAF_Opp

Open in new window

0
 
LVL 6

Expert Comment

by:processguru
ID: 35513584
sorry...there's a typo in that code...

SELECT CRMAF_Opp.accountidname,
    CRMAF_Opp.campaignidname,
    CRMAF_Opp.customeridname,
    CRMAF_Opp.new_funnel_positionname
FROM FilteredOpportunity AS CRMAF_Opp

Open in new window

0
 
LVL 1

Author Comment

by:sbdt8631
ID: 35513618
new_funnelpositionname
Just a typo in my above comment

When I open the view in Microsoft SQL Server Management Studio and edit the SQL to the following:
SELECT     accountidname, campaignidname, customeridname, new_funnelpositionname
FROM         FilteredOpportunity
I get 35 records

When I use the same SQL in SSRS:
SELECT     accountidname, campaignidname, description, new_funnelpositionname
FROM         FilteredOpportunity
I get 0 records

I also get 0 records from Crystal Reports.
0
 
LVL 6

Accepted Solution

by:
processguru earned 2000 total points
ID: 35513642
You may need to fully qualify the column selects.

SELECT CRMAF_Opp.accountidname,
    CRMAF_Opp.campaignidname,
    CRMAF_Opp.customeridname,
    CRMAF_Opp.new_funnelpositionname
FROM FilteredOpportunity AS CRMAF_Opp 

Open in new window

0
 
LVL 1

Author Comment

by:sbdt8631
ID: 35513734
I copied/pasted your code into SSRS and it automatically changed it to the below and returned 0 records.
SELECT     accountidname, campaignidname, customeridname, new_funnelpositionname
FROM         FilteredOpportunity AS CRMAF_Opp
0
 
LVL 1

Author Closing Comment

by:sbdt8631
ID: 35513767
I found this through Google
"The view in question filteredopportunity is a filtered view , this is known as a filtered view because it filters the data as per the logged in user's permissions to the records. That is the basic reason why we use the same in reports. Now as far as the availability of record in the opportunitybase table is concerned there is no such filteration is there."
0
 
LVL 6

Expert Comment

by:processguru
ID: 35513782
that's very bizarre. I should not have removed the full qualifications on the select statement. This is the exact syntax I use on all my reports with no trouble. I personally use the BI tools in Visual Studio to build my SSRS reports for CRM so I'm not sure if it's something specific to the tool you're working in.

What really confuses me is that a SELECT * returning 0 points to a different problem. Are you not receiving any errors? Are you sure you have a valid connection with valid authentication to the database?

0
 
LVL 6

Expert Comment

by:processguru
ID: 35513787
ahh. so it was a permissions problem. that makes sense.
0
 
LVL 1

Author Comment

by:sbdt8631
ID: 35690718
Yes, I change to integrated security and it works fine now.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Today as you open your Outlook, you witness an error message: “Outlook is using an old copy of your Outlook Data File…”. Probably, Outlook is accessing an old OST file.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
Suggested Courses

850 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