Advanced Find query

Hi all,

We would like to get a list of all accounts that haven't had any activities 
for the last three months. This is similar to system view "Accounts: No 
Orders in Last 6 Months" which is a system, API based view which to my 
knowledge can't be produced using the Advanced Find view. 

I'm just trying to get my head around how we are going to produce these 
types of reports/queries in the future. These are seldomly run and I believe 
they need to be modified all the time so building reports feels like 
overdoing things. What would you do? SQL queries in Excel against filtered 
views? The "consumers" of these reports are sales guys without good SQL 
knowledge so I need to figure out the best way to get the data out where they 
can add/remove some parameters.

Applies to CRM 3.0.

Thanks,
- Manso
0
Utf
11/13/2007 12:34:00 PM
crm 35858 articles. 1 followers. Follow

5 Replies
596 Views

Similar Articles

[PageSpeed] 29

I would suggest you to export your report as dynamic excel file and modify 
the query on data from within the Excel to get the desired results.

Most of the users are very familiar with using Excel as compared to other 
reporting solutions. There are some other reasons for this suggestion as well 
such as:

1 - Report is very simple and easy to maintain or modify.
2 - Highly portable and user friendly.
3 - Very cost effective.

"Manso" wrote:

> Hi all,
> 
> We would like to get a list of all accounts that haven't had any activities 
> for the last three months. This is similar to system view "Accounts: No 
> Orders in Last 6 Months" which is a system, API based view which to my 
> knowledge can't be produced using the Advanced Find view. 
> 
> I'm just trying to get my head around how we are going to produce these 
> types of reports/queries in the future. These are seldomly run and I believe 
> they need to be modified all the time so building reports feels like 
> overdoing things. What would you do? SQL queries in Excel against filtered 
> views? The "consumers" of these reports are sales guys without good SQL 
> knowledge so I need to figure out the best way to get the data out where they 
> can add/remove some parameters.
> 
> Applies to CRM 3.0.
> 
> Thanks,
> - Manso
0
Utf
11/17/2007 1:40:01 PM
I would suggest you to export the system view as dynamic Excel file and 
manually modify the query in Excel application.

Besides the fact that most of the Microsoft CRM users are already very 
familiar with Excel, there are some other reasons that may incline you to use 
excel for your solution, which are:

1 - Report is simple and it would easy to change or modify.
2 - Report is portability.
2 - report is cost effective.

Ayyaz Ahmed
http://mscrm3dev.blogspot.com/

"Manso" wrote:

> Hi all,
> 
> We would like to get a list of all accounts that haven't had any activities 
> for the last three months. This is similar to system view "Accounts: No 
> Orders in Last 6 Months" which is a system, API based view which to my 
> knowledge can't be produced using the Advanced Find view. 
> 
> I'm just trying to get my head around how we are going to produce these 
> types of reports/queries in the future. These are seldomly run and I believe 
> they need to be modified all the time so building reports feels like 
> overdoing things. What would you do? SQL queries in Excel against filtered 
> views? The "consumers" of these reports are sales guys without good SQL 
> knowledge so I need to figure out the best way to get the data out where they 
> can add/remove some parameters.
> 
> Applies to CRM 3.0.
> 
> Thanks,
> - Manso
0
Utf
11/17/2007 1:50:01 PM
I would suggest you to export the system view as dynamic Excel file and 
manually modify the query in Excel application.

Besides the fact that most of the Microsoft CRM users are already very 
familiar with Excel, there are some other reasons that may incline you to use 
excel for your solution, which are:

1 - Simple and it would easy to change or modify.
2 - Portable.
2 -Very cost effective.

Ayyaz Ahmed
http://mscrm3dev.blogspot.com/

"Manso" wrote:

> Hi all,
> 
> We would like to get a list of all accounts that haven't had any activities 
> for the last three months. This is similar to system view "Accounts: No 
> Orders in Last 6 Months" which is a system, API based view which to my 
> knowledge can't be produced using the Advanced Find view. 
> 
> I'm just trying to get my head around how we are going to produce these 
> types of reports/queries in the future. These are seldomly run and I believe 
> they need to be modified all the time so building reports feels like 
> overdoing things. What would you do? SQL queries in Excel against filtered 
> views? The "consumers" of these reports are sales guys without good SQL 
> knowledge so I need to figure out the best way to get the data out where they 
> can add/remove some parameters.
> 
> Applies to CRM 3.0.
> 
> Thanks,
> - Manso
0
Utf
11/17/2007 1:51:00 PM
Thanks for your reply. I agree with you totally. The problem is that you are 
quite limitied to what you can export using the Advanced Find. The query I 
mention below "All accounts without any activities for the last N months" is 
impossible to create as far as I can tell.

Thanks,
Manso

"DotNetMonster" wrote:

> I would suggest you to export your report as dynamic excel file and modify 
> the query on data from within the Excel to get the desired results.
> 
> Most of the users are very familiar with using Excel as compared to other 
> reporting solutions. There are some other reasons for this suggestion as well 
> such as:
> 
> 1 - Report is very simple and easy to maintain or modify.
> 2 - Highly portable and user friendly.
> 3 - Very cost effective.
> 
> "Manso" wrote:
> 
> > Hi all,
> > 
> > We would like to get a list of all accounts that haven't had any activities 
> > for the last three months. This is similar to system view "Accounts: No 
> > Orders in Last 6 Months" which is a system, API based view which to my 
> > knowledge can't be produced using the Advanced Find view. 
> > 
> > I'm just trying to get my head around how we are going to produce these 
> > types of reports/queries in the future. These are seldomly run and I believe 
> > they need to be modified all the time so building reports feels like 
> > overdoing things. What would you do? SQL queries in Excel against filtered 
> > views? The "consumers" of these reports are sales guys without good SQL 
> > knowledge so I need to figure out the best way to get the data out where they 
> > can add/remove some parameters.
> > 
> > Applies to CRM 3.0.
> > 
> > Thanks,
> > - Manso
0
Utf
11/18/2007 4:10:00 PM
You would have to modify the Date filter on the query in Excel file once you 
have exported this report as excel file.

As a hint you would have to add a sub query to find all those accounts which 
do not have any activity within the given date span and add it as a condition 
to the filter of parent select query.

If you are not comfortable with creating or modifying reports then I suggest 
you can always take a little bit of help from some freelancers. They are 
reliable, turn out quality work and are cost effective. If you would like, I 
can surly pass you on with some well reputed names. you can mail me at ayyaz 
[at] windowslive [Dot] com




"Manso" wrote:

> Thanks for your reply. I agree with you totally. The problem is that you are 
> quite limitied to what you can export using the Advanced Find. The query I 
> mention below "All accounts without any activities for the last N months" is 
> impossible to create as far as I can tell.
> 
> Thanks,
> Manso
> 
> "DotNetMonster" wrote:
> 
> > I would suggest you to export your report as dynamic excel file and modify 
> > the query on data from within the Excel to get the desired results.
> > 
> > Most of the users are very familiar with using Excel as compared to other 
> > reporting solutions. There are some other reasons for this suggestion as well 
> > such as:
> > 
> > 1 - Report is very simple and easy to maintain or modify.
> > 2 - Highly portable and user friendly.
> > 3 - Very cost effective.
> > 
> > "Manso" wrote:
> > 
> > > Hi all,
> > > 
> > > We would like to get a list of all accounts that haven't had any activities 
> > > for the last three months. This is similar to system view "Accounts: No 
> > > Orders in Last 6 Months" which is a system, API based view which to my 
> > > knowledge can't be produced using the Advanced Find view. 
> > > 
> > > I'm just trying to get my head around how we are going to produce these 
> > > types of reports/queries in the future. These are seldomly run and I believe 
> > > they need to be modified all the time so building reports feels like 
> > > overdoing things. What would you do? SQL queries in Excel against filtered 
> > > views? The "consumers" of these reports are sales guys without good SQL 
> > > knowledge so I need to figure out the best way to get the data out where they 
> > > can add/remove some parameters.
> > > 
> > > Applies to CRM 3.0.
> > > 
> > > Thanks,
> > > - Manso
0
Utf
11/19/2007 4:38:00 AM
Reply:

Similar Artilces:

When viewing an MFC dll's dependancies can't seem to find MFC80D.d
While compiling a project that has recentlly been upgraded to VS2005 from VS6.0 i keep getting a registration error. at the end of the process the project tries to register the newly compiled DLL. when i looked at the DLL through Dependancy Walker i discovered that it could not seem to find three DLLs: MFC80D.dll, msvcp80d.dll and msvcr80d.dll. any ideas why this would happen? On Oct 20, 7:28 am, Shawn <Sh...@discussions.microsoft.com> wrote: > While compiling a project that has recentlly been upgraded to VS2005 from > VS6.0 i keep getting a registration error. at the end of ...

Advanced Search Folder Criteria
Outlook 2007 I want to create a search folder that contains compound criteria (search for x AND y). In the Search Folder Criteria dialog box, on the Advanced tab, there is a "More Advanced..." button, but it is grayed out. How do I enable that button, and is there a way to create a search folder with compound criteria? --Tom Hello Tom, You need to enable the query builder, see this microsoft Knowledgebase article http://support.microsoft.com/?kbid=3D307922 On Dec 17 2008, 3:52=A0pm, "Thomas M." <NoEmailRepl...@Please.com> wrote: > Outlook 2007 > &g...

Combo box to find records
Hi, need help with what I am sure is a simple problem. I have a form and wish to find records using a combo box. Which I know how to do. The problem is it takes me to "one" record. I would like to select, for example, employee name: John Smith and it return all records for John Smith while filtering out all other records. I would be thankful for any help. Thanks, Michael On Wed, 30 Jan 2008 20:24:04 -0800, Michaelchessking <Michaelchessking@discussions.microsoft.com> wrote: >Hi, need help with what I am sure is a simple problem. > >I have a form and wis...

Excel Query #2
I got a stored procedure with 1 parameter and I want to call this stored procedure inside an Excel Query , I tried ..dbo.MySP ? It return with error message "Parameters are not allowed in queries that can't be displayed graphically" The purpose for this approach - This would allow me to anchor my parameter to certain cells on the spreadsheet. My report users could then change the values in those cells and click a button to refresh the query data. Thanks Mike ...

Search Function with queries
Hi guys, new user to access 2000 and i am trying to create a search query that will allow me to enter in a value and return that value. The part i am having trouble with is i need to do this for many fields and if i use the method i came up with, when i leave a entry empty using the "AND" function i get nothing and when i use the "OR" function i get all the values for both. maybe you guys can help me get in the right direction. this is the command i am using in SQL: SELECT [Chain Machine prod Machine list].Description, [Chain Machine prod Machine list]...

Nz function in query
Hi All, the query below shows an error if any of the date in / date out entries in the table are empty. How can i impiment the Nz function to stop this. SQL from query... SELECT Employees.[First Name], Employees.[Last Name], Format([Normal start Time],"h:nn AMPM") AS ['Normal Start Time], Format([Normal End Time],"h:nn AMPM") AS ['Normal End Time], Format([scan time in],"h:nn AMPM") AS ['Scan Time In], Format([scan time Out],"h:mm AMPM") AS ['Scan Time Out], Format( [Scan Date In],"dddd dd,mm,yyyy") AS ['Scan Date ...

find location max value in column
I need to find the cell location of the max value in a column. If paste the following [from =(cell onwards] into a cell I get the righ result. Having a hgard time getting the right VBA code to make thi work in an unattended macro. Probably some bonehead mistake on my part but I cannot find it. Suggestions? GOAL is to make this formula work. Range("z2").Formula "=(CELL(e1,OFFSET(e1,MATCH(MAX(e1:e100),e1:100,0)-1,0)))" TIA, Ro -- rroac ----------------------------------------------------------------------- rroach's Profile: http://www.excelforum.com/member.php?act...

Finding last occurence of Interior.ColorIndex 36
I have been searching for a formula to help me find the last time (most recent) color 36 appears in a column of colored cells. Most of the time there is no typed information and when there is, it is not the same for every color 36 cell. The cells are not conditionally formatted. C. Pearson's site is great, but I can't find what I need. I don't want to change color, I don't want to know how many times it shows up, I just want to find the last time it is in the column. I could count down to find it, but there are over 15,000 columns spread over several worksheet...

where can i find a list of excell formula's? #2
I'm learning to use excel more and more and i would like to know how to create a formula that subtracts one cell from another. Is there a list of formula's for excel? Look in HELP contents for function index -- Don Guillett SalesAid Software donaldb@281.com "Art" <Art@discussions.microsoft.com> wrote in message news:DFF58A3A-86CE-415C-A471-0F995F95C108@microsoft.com... > I'm learning to use excel more and more and i would like to know how to > create a formula that subtracts one cell from another. Is there a list of > formula's for excel? Art, y...

upgrade to 2007 can't find exsiting website
I just upgrade from microsoft office 2003 to 2007 office enterprise, my operating system is visita. I'm trying to get my website from 2003, it comes in but none of my pictures,background,pages. doesn't show. I've uninstall the software and installe it again, hopeing this would fix the problem. I'm at a lost here. someone please help. I think Microsoft should let office 2003 run with visita, instead running out to buy some software that suppose to be compatiable. this is a waste of money. Office 2003 SP2 & SP3 will run on Vista. -- ~~~~~~~~~~~~~~~~~~ Rob Giorda...

Export Query to Excel in VB
Below is a code that works and what I like to do is every time I run the command the data will be created in a new worksheet? If yes, what will the code look like? This code is executed from a form. This query will run every month and will only collect data from the current month. Private Sub Command88_Click() On Error GoTo Err_Command88_Click Dim SavePath As String Dim strExport As String SavePath = "C:\MyFolder\MyFilename.xls" strExport = "MasterQry" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strExport, SavePa...

macro query disable download failure
Hello, I have a macro which retrieve different queries from the web. It happens that a resource at that moment isn''t available. If this happens the querie gives a message which said unable to open http://webpage This message i don't want to display (other routines herefor). I use "Application.DisplayAlerts = False" but this won't help. Can anybody help me with this problem. Thanks ...

Find 3rd Record
Hi there, I need to find the third record in a query. This is my basic select query: SELECT tblGuests.GID, tblGuests.Date FROM tblGuests ORDER BY tblGuests.GID, tblGuests.Date; There are several thousand records in tblGuests, some Guests (GID) have just one record, others have 20 or more. I've thought of DateDiff function but that doesn't seem to work. I have also thought that I need to find only records where there are more than 2 records for a certain GID so I can filter out the Guests who have only visited 1 or 2 times using the count function but have no idea how to do t...

Import Access Query to Excel
I am trying to import MS Access Query to Excel. I tried with "Data --> Import External Data --> Import Data" but I found two issues. First, "Select Table" lists only 5 of 15 query tables I made. Second, data range only displays column headings, no record. Both Access and Excel files are on local drive. My computer is on domain network and I heard there is a security issue with Import External Data within domain environment. However, I used to do this on my previous employment within domain environment. I appreciate any help! Thank you! Jonathan Kim Jonathan, wh...

Rank Query With Groups (Array?)
Hello, I have a table of data with grades which are grouped by study. I need to rank the grades relative to all the other grades in the same study. Below is an example the study and grade fields with the desired outcome for the rank. Study Grade Rank 11111 89 1 11111 75 2 11111 65 3 22222 99 1 22222 87 2 Hopefully this can be done with simple SQL in a query and not with code but I am open to anything that works. Many thanks in advance for your time and expertise! Try this -- SELECT Study, Grade, (SELECT Count(*) FROM YourTable AS [XX] WHERE You...

Web Query File Reference
Is there a Web Query File (.IQY) Format Reference anywhere? Thanks, - Vlad ...

Routine to run/export queries
Looking for some links to examples that do the following (or something close): 1. user would create a whole bunch of queries themselves (using Access) 2. form that lets the user runs these queries to display results and/or export 3. Would be good if user could select multiple queries and get multiple resultsets to display or be exported to an excel file with multiple worksheets. Example: user selects 5 queries and wants an excel file made with 5 tabs with the 5 different outputs of the queries, or the outputs of the 5 queries show in a form with multiple tabs in Access (hide...

How to find the datatype of a particular variable...
Dear pals, I am developing an win32 console application, I need to find the data type of particular variable dynamically. Is it possible? Say for eg, declaring 'a' as integer, how can I find the data type of variable 'a' dynamically. Awaiting for your ideas. note: please ignore this, if it is not a relevant group. @Shahul. I'm trying to imagine why your would need this. It sounds like this will be hard coded so you'll know. If you need something higher-level to do this, I would probably create a class that is similar to a variant in that it could store any t...

Able to find text properties?
Is there a function in excel to test the text properties of a cell. ie. if it's bold or red color text? thanks naveed010@hotmail.com G0 to the Help files, and lookup Cell worksheet function That will give you a list of the types of formats that can be returned. Mostly date, time and currency formats. The only color return would be the "negative red" in currency. What you're looking for needs code. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ========================================...

tool to find GDI resource leak
Hallo, i search a tool or DLL or source to find GDI resource leaks. I've Boundschecker, but it don't find GDI resource leaks. Jimmy > i search a tool or DLL or source to find GDI resource leaks. > I've Boundschecker, but it don't find GDI resource leaks. Hmm, as far as I can remember BC did find GDI leaks without problems several years ago, when I was using it. You may also check this: http://msdn.microsoft.com/msdnmag/issues/03/01/GDILeaks/default.aspx -- Igor Green, Grig Software. www.grigsoft.com Compare It! + Synchronize It! : files and folders comparison neve...

how do i create subset and union in XPath Query
Hi Friends, I need a XPath query, whcih ll return me subset of data.... check below xml stream <label id="MyExpenseDetails_lbl" xlink:role="terseLabel">Short Expense Details</label> <label id="MyExpenseDetails_lbl" xlink:role="displayLabel">Expense Details</label> <label id="InternalExpense_lbl" xlink:role="displayLabel">Internal Expense</label> <label id="ExternalExpense_lbl" xlink:role="terseLabel">Short External Expense</label> See above xml data having 4 label ...

Still unable to run successful query
I've used the IFF suggestions given but it returns only Not Active results. I'm working on a case problem that reads: Create a query to display all matching records from the tblProgram and tblMember tables, selecting the ProgramType and MonthlyFee fields from the tblProgram table, and the FirstName and LastName fields from the tblMember table. Add a calculated field named MonthlyFeeStatus as the last column that equals Active if the MembershipStatus field is equal to Active and equals Not Active otherwise. -- jj Assuming MembershipStatus is a string (varchar) data field,...

Find Last instance of carriage return In a Memo Field
Hi, I have a form where the user usually pastes a glob of text into it a memo field called "Comment". After he pastes the text, the cursor is obviously at the end of the text. I would like the user to be able to click a button and for the comment to be highlighted from the very end of the text to the first instance of a carriage return going backwards. In other words, I want to automatically select the very last line of the comment. I was thinking that I could use seldown, but I would need to first find the Last instance of Chr$(13). Or, can the instr function can be ...

where can I find an excel file I "saved as" in OLKE9?
I saved over an EXCELL file that someone sent to me in OUTLOOK, after adding hours of data. Unfortunately I clicked "save as" but did not change the directory. It has saved my EXCEL FILE in a folder called OLKE9. I have searchjed the path, but cant fnd the file (I am displaying hidden files). Please help, I need this file in only 8 hours time or my life will be a misery. Easy way: Open ANY file (EXCEPT THAT ONE!!) through Outlook, and hit File-->Save as. That'll take you to the OLK folder. If you don't see it, likely, it's gone. Never, ever open files attached ...

Query Help Please
I have a relational db with a Client Table and a Project Table. The Client table holds general info that usually doesn't change. The Project table has a record for each project we've done for the client, and contains a Manager field. The manager could change from year to year. I want to show by current manager all the other projects for that client (regardless of who previous manager was). Current manager is just the manager on the latest project. I'm guessing I need a query of a query, but I'm stumped. Any advice would be appreciated. Thanks. Do you hav...