Dynamics CRM 4.0 Reports Attributes Problem

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?
LVL 1
sbdt8631Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

processguruCommented:
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
sbdt8631Author Commented:
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
processguruCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

processguruCommented:
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
processguruCommented:
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
sbdt8631Author Commented:
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
processguruCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sbdt8631Author Commented:
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
sbdt8631Author Commented:
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
processguruCommented:
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
processguruCommented:
ahh. so it was a permissions problem. that makes sense.
0
sbdt8631Author Commented:
Yes, I change to integrated security and it works fine now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.