custom report - truncate in sql

I have a report that groups sales by zip codes.  However, many of my zip 
codes have the four digit extension.  Does someone know the command I can put 
in the zip field to truncate the zip code for the main 5 digits, allowing the 
group total for the main zip code?
0
3/13/2009 9:06:01 PM
pos 14173 articles. 0 followers. Follow

2 Replies
381 Views

Similar Articles

[PageSpeed] 45

you will have to edit the report in notepad to change the formula:


Begin Column
   FieldName = "ZipCode"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Zip"
   VBDataType = vbString
   Formula = "left(Customer.zip,5)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 630
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftTop
End Column


"Gene @ Treads" wrote:

> I have a report that groups sales by zip codes.  However, many of my zip 
> codes have the four digit extension.  Does someone know the command I can put 
> in the zip field to truncate the zip code for the main 5 digits, allowing the 
> group total for the main zip code?
0
MattHurst (182)
3/13/2009 10:44:01 PM
Thanks worked great!

"Matt Hurst" wrote:

> you will have to edit the report in notepad to change the formula:
> 
> 
> Begin Column
>    FieldName = "ZipCode"
>    DrillDownFieldName = ""
>    DrillDownReportName = ""
>    StoreIDFieldName = ""
>    Title = "Zip"
>    VBDataType = vbString
>    Formula = "left(Customer.zip,5)"
>    ColHidden = False
>    ColNotDisplayable = False
>    FilterDisabled = False
>    ColWidth = 630
>    GroupMethod = groupmethodNone
>    ColFormat = ""
>    ColAlignment = flexAlignLeftTop
> End Column
> 
> 
> "Gene @ Treads" wrote:
> 
> > I have a report that groups sales by zip codes.  However, many of my zip 
> > codes have the four digit extension.  Does someone know the command I can put 
> > in the zip field to truncate the zip code for the main 5 digits, allowing the 
> > group total for the main zip code?
0
3/13/2009 11:04:01 PM
Reply:

Similar Artilces:

Customizing the search in the "members of a marketing list" view
Hi! We have a strange problem with the search in the "members of a marketing list" view. If we do a search for a last name, there are no hits, even though there is such a contact in the marketing list. If we do the search for the same contact with the first name or a "*" before the lastname (for example "*gates") we get the contact we were looking for. I guess our CRM is only searching in the field with the full name... Everywhere else, the search works as expected and we can find the contact even with searching only for his last name. I checked the defau...

Variables show in Print Preview but not when report prints
I have a form that requests a startdate and enddate for the transactions to be reported. In Print Preview, these two variables appear on the report header, but disappear when the report actually prints. Any help would be greatly appreciated. On Fri, 19 Oct 2007 13:30:01 -0700, Sam V wrote: > I have a form that requests a startdate and enddate for the transactions to > be reported. In Print Preview, these two variables appear on the report > header, but disappear when the report actually prints. Any help would be > greatly appreciated. What is the actual control source of...

SQL statment to convert integer to string
Dear all, I have a select statement "select year from table1" , where year is an integer type. How can I convert the year to string in sql. I have try "select cstr(year) as year-string from table 1". But it is failed. Please help. On 21 feb, 09:48, hon123456 <peterhon...@yahoo.com.hk> wrote: > Dear all, > > =A0 =A0 =A0 =A0I have a select statement "select year from table1" , wher= e > year is an integer type. How can I > convert the year to string in sql. I have try "select cstr(year) as > year-string from table ...

Equity in Options not appearing in reports???
Sorry, this may be a simple issue but I'm new to Money. I can't figure out why my vested stock option amounts aren't being included in any of Money's reports. The accounts are there in my portfolio, but when I print portfolio value reports they are not included. I've selected "all" accounts but the options acct doesn't even seem to be available.. Any help is appreciated. ...

reports
still waiting for some pro to teach me how to do a report in Excel 2002 with a brief like that, what can we be thinking? -- HTH RP (remove nothere from the email address if mailing direct) "herbzee" <her.ky@verizon.net> wrote in message news:i%NUd.66504$8a6.43117@trndny09... > still waiting for some pro to teach me how to do a report in Excel 2002 > http://office.microsoft.com/en-ca/assistance/HA010346581033.aspx Gord Dibben Excel MVP On Mon, 28 Feb 2005 23:42:06 GMT, herbzee <her.ky@verizon.net> wrote: >still waiting for some pro to teach me how t...

Importing Customizations and Workflows from 3.0
Hi, I've exported some customizations and workflows from CRM 3.0 and want to import to CRM 4.0. I'm trying at the CRM 4.0 interface but it's saying "Either the file could not be uploaded, or this is not a valid Customization file." How should I do that? It isn't possible? tks, Alex Unfortunately you can't export from CRM 3.0 and import into CRM 4.0 Your only option is to have your customisations on a CRM 3.0 server, upgrade the server to CRM 4.0, then export and import the customisations and workflows -- David Jennaway - Microsoft Dynamics CRM MVP Web: h...

Multiple reports in same Document
I am looking for a way to show on the same document, two different type of data as follows: I have a data table with any number of columns that are set in width. I am wanting to insert a pivot table report imediately below the dat table. The pivot table picks up the column width from the data tabl already in the sheet. This causes the pivot table fields to be eithe too large or too small. If I correct the pivot table, then the dat table attributes are changed. Thanks -- JGallik0 ----------------------------------------------------------------------- JGallik01's Profile: http://www...

Formulae for customized field
Dear sir , I want to use the formulae iif (and ( expression1,expression2),truepart,falsepart). But its not working it is sho wing syntex error with And. how to solve the problem? -- Amey B. Vidvans, Planning Engineer Vidvans_amey@rediffmail.com It's almost like EXCEL but not quite. iif (expression1 AND expression2,truepart,falsepart) Watch out for quoted literal text, eg "truepart" for text and flag fields. -- Trevor Rabey 0407213955 61 8 92727485 PERFECT PROJECT PLANNING www.perfectproject.com.au "Amey" <Amey@discussions.microsoft.com...

Custom Views Disappear
I use Outlook on a corporate exchange network. I have customized views that group my inbox and tasks in assorted ways. These custom views keep disappearing. I can't create a view with the same name, so it must be somewhere. Strangely, they will reappear from time to time. Where are these views stored? How can I get them back? What can be done to make this behavior stop? Thank you! M ...

Drop Down List / Open Report
How do I go about setting my Combo Box to open any one of my ten reports i.e.: rptCharge1.....................rptCharge10 Do I put there name in the Row Source Value [Value List] Row Source ;rptCharge1; What would have the Event OnClick Thanks for any Help....Bob On Sun, 12 Aug 2007 11:11:57 +1200, "Bob V" <rjvance@ihug.co.nz> wrote: > > >How do I go about setting my Combo Box to open any one of my ten reports >i.e.: rptCharge1.....................rptCharge10 >Do I put there name in the Row Source Value [Value List] >Row Source ;rptCharge1; >What wou...

Printing a report to fit on one page
How do I do this PLEASE. It keeps wanting to fit it onto 4 pages, regardless of how much I shrink the size of the report. How do I copy the MS word "fit to one page" function?? -- Thanks! mass I haven't tried this with Money yet, but I usually set my print driver to "fit-to-page" when I experience a program that doesn't seem to allow that function on its own. "mass" wrote: > How do I do this PLEASE. It keeps wanting to fit it onto 4 pages, regardless > of how much I shrink the size of the report. How do I copy the MS word "fit &...

Can reports show a percentage?
Is there a way to show a percentage in the SO reports? I would like a report to show the profit as a percentage of the sales on a few reports. Thanks, JD Byers Sure ... have several reports that I modified to show this. See below. Begin Column FieldName = "ProfitMargin" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Margin" VBDataType = vbDouble Formula = "CASE WHEN ViewStoreSales.TotalSales > 0 THEN (VIEWStoreSales.TotalSales-VIEWStoreQty.TotalCost) / ViewStoreSales.TotalSales...

CRM 3.0 Reports Not Working in Outlook Client
Recently we had to republish all the reports on the SQL server (2005) in order for them to show up in the web client. They now do and can be run. However, none of our laptop clients are able to run a report. When a user clicks on the report link, they get the following error: Server error '/' application The request failed with HTTP status 401: unauthorized. These same users can run the reports through the web client. I'm at a complete loss and appreciate any and all help. Thanks, Jason Little more background: CRM 3.0 and SQL Server 2005 are on different servers OS: Windows ...

Customization not appears..
Hi, I have a problem that one of the users in CRM doesnt have Customization in Settings. He is defined as System Admin in CRM and in Active directory. I'm guessing that the problem is that this user working on two computers, on the first PC it works fine, on the second PC it does not appear. It looks like some definition of main station and secondary station. How can I control it? Thanks. Has he installed the language pack on that computer ? For example base language English and second language xyz ? Try launching web client before you start outlook. ------ Aamir Blog = http://mscrmsu...

Invalid report
I have an annoying message that continually pops up on my screen and is wearing my patients real thin. The message reads: "Due to changes in your money file, the saved version of the "Portfolio Value by Investment Type" report is no longer valid. Money has attempeted to reeset the report, but you should reset it manually to avoid unreliable information". 1. I have "reset" the report manually, many times, all to no avail. 2. In addition, I uninstalled, and reinstalled, the entire program. Any ideas???? Ensure you delete any MRU references from the regis...

Office can't recognize custom-made layout
Hello all I recently switched to the mac, and one thing has been a problem: the kb layout. I am Brazilian, but there weren't any proper Brazilian keyboard layout available in OS X (Brazilian and Portuguese in the International Pref Pane have nothing to do with the actual layout). So after some searching I finally found and installed a custom layout which works fine in every application (iWork, Firefox, Safari, etc), except for the Office apps, which refuse to recognize the 12th bottom-row key (which is responsible for "/" and "?") of my keyboard. This link ( http://w...

Why Doesn't My Stock Option Account show up in reports?
I've tried everything simple I can think of. Can't seem to get my Options account to even be counted towards net worth or any other report. In microsoft.public.money, TonyT wrote: >I've tried everything simple I can think of. Can't seem >to get my Options account to even be counted towards net >worth or any other report. Have you tried Tools->Options->Investments->Calculate...BasedOn...ValueOfTotal... ? ...

Opinion about e-Expense Reports and Integration with GP and Bus Po
Does Microsoft have a e-expense report product and have any one implemented it? Or Have any of you implemented the third party package and how did it tie in with GP? Thanks in advance. -- Chetan Tanna Sr. Manager Of IT ...

Opening Report Manager from anything besides localhost gives error
I've never noticed this before, but if I try to go to http:\\crmserver\ReportManager I get a 403.6 error: "Forbidden: IP address of the client has been rejected". However, http:\\localhost\ReportManager works when I'm on the server. My Crystal Reports SW works fine, and once I'm in Report Manager it appears to work fine. What did i do wrong? ...

Calculating page/report sums using calculated data from subquery
Here is a scenario from my report: The report will grab data with a main query into a parent row Depending on the data, a child row may exist, if it does the report will display it The child row will use data from the parent row in order to do a calculation in the child All of this works just fine and the report data is correct. Now, what I am having issues with is doing the page/report sum calculations. The requirements dictate that I need to create a sum of both the parent column and also the child column in the totals. I have been trying to do this all day and cannot see...

Why is my row truncated in a pivot table?
my source of the pivot table has a data field with 81 words, yet only 44 words show up in the results table. How can I get the entire 81 words in the pivot table? A pivot table cell is limited to 255 characters. There's no setting you can change to increase this limit. mcmunnd1 wrote: > my source of the pivot table has a data field with 81 words, yet only 44 > words show up in the results table. How can I get the entire 81 words in the > pivot table? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

ACC2000: changing report .Filter does not work
I'm still working on issues in earlier threads. When I use code to change my report's .Filter and set .FilterOn to true, I get no records. When I manually go to the report design, change the .Filter and ..FilterOn, I also get no records. Here's the code I use to change .Filter via code: sWhere = "[cus_no] = '" & sCustnum & "'" DoCmd.OpenReport sReportname, acViewDesign ' Must come before "set rpt" Set rpt = Reports(sReportname) rpt.Filter = sWhere rpt.FilterOn = True DoCmd.Close , , acSaveYes DoEvents ' Allow Access to sa...

X axis months are truncated??
I have four Excel charts. The first one has the months from Jan through Dec in three letter format. The 2nd through 4th appear as Jan, Feb, Mar, Apr, Ma, Jun, Jul, Au, Se, Oct, No, Dec. Why are these four months truncated to two letters each while the other eight are in the desired three letter format. The names of the months for the four charts all come from the same column on the Chart Data table. Paul, Check to make sure that all of your charts and chart plot areas are uniformly sized and that the X-axis fonts and font sizes are exactly the same. At times I've experienced tha...

Customer Shipping Labels
How about a "Labels" button on the Customer Properties page similar to that which appears on Item Properties? ---------------- 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 "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=d2d7...

How do you name and use a custom Layout
I have a spreadsheet with 30 coloumns. Differnt users access this spreadsheet and depending on their login name I wanted to display a different layout for differnt users. I have tried using custom view but it does not work. I tried cutting and inserting the cut colomn into a differnt part of the spreadsheet and named the custom view, but it did not go to it from the default layout. In other words it appears you cannot use custom views to view different layout options -- Steve F Not sure how you are going to tell the system the login name. But the custom views should work. I start with...