Monthly Reports

Does anyone know how to do monthly reports via Cystral Reports via ODBC or 
using Excel via ODBC via HQ Database. 

0
TonyC1 (9)
11/23/2006 6:02:01 PM
pos 14173 articles. 0 followers. Follow

4 Replies
515 Views

Similar Articles

[PageSpeed] 0

You can connect the the SQL server via ODBC from Excel.

Data | Database Query

Create a new database source using the SQL Server driver, enter your 
server/database paramaters, and you are off the races. MS Query will open 
and you can create queries from there. Then you can slice and dice the data 
any way you want it or do Pivottables.

Jason


"TonyC" <TonyC@discussions.microsoft.com> wrote in message 
news:0FCBBBCD-121A-4A9B-B486-F7B57B532CC2@microsoft.com...
> Does anyone know how to do monthly reports via Cystral Reports via ODBC or
> using Excel via ODBC via HQ Database.
> 


0
jason2290 (135)
11/26/2006 8:36:53 PM
Would anyone know What to link from different tables and what tables to use 
to get monthly sales information. Thanks....

"TonyC" wrote:

> Does anyone know how to do monthly reports via Cystral Reports via ODBC or 
> using Excel via ODBC via HQ Database. 
> 
0
TonyC1 (9)
11/27/2006 11:43:01 AM
Well that really depends on the columns you want to see and how your 
business process works... I'll explain why...

You can get all of the daily (batch) sales information out of the BATCH 
table. This will provide totals only. You can set up a criteria where 
year(ClosingTime)=2006 and month(ClosingTime)=11 for example. This will give 
you the total sales, tax, etc. for each batch that was closed during the 
month.

On the other hand, sometimes transactions could be run after closing the 
batch on the last day of the month. If you want to pick up these 
transactions, you have to use another method. I tell cashiers that after the 
Z on the last day of the month, there should be no more transactions, but it 
could happen. This would require using the Transaction or TransactionEntry 
tables, and this give you a lot more data.

I use the TransactionsEntry table most often as my starting point. You will 
need to determine what other columns you want to pick up to determine what 
tables you need and to set up the links. Here are some common ones:

Tables
Transaction
Item
Department
Category

Links
Transaction.TransactionNumber - TransactionEntry.TransactionNumber
Item.ID - TransactionEntry.ItemID
Item.DepartmentID - Department.ID
Item.CategoryID - Category.ID

Warning: MSQuery likes to guess the link when you add a table (and always 
guesses wrong). So delete the guessed links and start from scratch.

As far as field you want to pick up, you might want some calculated date 
fields, especially if you are pulling multiple months. I usually bring in 
year(Transaction.Time), month(Transaction.Time), and day(Transaction.Time).

You are going to get a lot of data - one row for each line item on each 
sale. The TransactionEntry table is just that - one row per receipt row. The 
good news is that Excel is great at slicing and dicing the date to summary 
levels that you require.



"TonyC" <TonyC@discussions.microsoft.com> wrote in message 
news:1BF6785D-188D-4B11-9FA0-AD573FF8E908@microsoft.com...
> Would anyone know What to link from different tables and what tables to 
> use
> to get monthly sales information. Thanks....
>
> "TonyC" wrote:
>
>> Does anyone know how to do monthly reports via Cystral Reports via ODBC 
>> or
>> using Excel via ODBC via HQ Database.
>> 


0
jason2290 (135)
11/27/2006 1:54:38 PM
Jason,
don't forget the StoreID in both [Transaction] and TransactionEntry since 
TonyC stated that he wants to access HQ database in his first post

"Jason" <jason@szumlanski.com> wrote in message 
news:%23MriVuiEHHA.4280@TK2MSFTNGP04.phx.gbl...
> Well that really depends on the columns you want to see and how your 
> business process works... I'll explain why...
>
> You can get all of the daily (batch) sales information out of the BATCH 
> table. This will provide totals only. You can set up a criteria where 
> year(ClosingTime)=2006 and month(ClosingTime)=11 for example. This will 
> give you the total sales, tax, etc. for each batch that was closed during 
> the month.
>
> On the other hand, sometimes transactions could be run after closing the 
> batch on the last day of the month. If you want to pick up these 
> transactions, you have to use another method. I tell cashiers that after 
> the Z on the last day of the month, there should be no more transactions, 
> but it could happen. This would require using the Transaction or 
> TransactionEntry tables, and this give you a lot more data.
>
> I use the TransactionsEntry table most often as my starting point. You 
> will need to determine what other columns you want to pick up to determine 
> what tables you need and to set up the links. Here are some common ones:
>
> Tables
> Transaction
> Item
> Department
> Category
>
> Links
> Transaction.TransactionNumber - TransactionEntry.TransactionNumber
> Item.ID - TransactionEntry.ItemID
> Item.DepartmentID - Department.ID
> Item.CategoryID - Category.ID
>
> Warning: MSQuery likes to guess the link when you add a table (and always 
> guesses wrong). So delete the guessed links and start from scratch.
>
> As far as field you want to pick up, you might want some calculated date 
> fields, especially if you are pulling multiple months. I usually bring in 
> year(Transaction.Time), month(Transaction.Time), and 
> day(Transaction.Time).
>
> You are going to get a lot of data - one row for each line item on each 
> sale. The TransactionEntry table is just that - one row per receipt row. 
> The good news is that Excel is great at slicing and dicing the date to 
> summary levels that you require.
>
>
>
> "TonyC" <TonyC@discussions.microsoft.com> wrote in message 
> news:1BF6785D-188D-4B11-9FA0-AD573FF8E908@microsoft.com...
>> Would anyone know What to link from different tables and what tables to 
>> use
>> to get monthly sales information. Thanks....
>>
>> "TonyC" wrote:
>>
>>> Does anyone know how to do monthly reports via Cystral Reports via ODBC 
>>> or
>>> using Excel via ODBC via HQ Database.
>>>
>
> 


0
ahmed.nashat (140)
11/27/2006 2:13:40 PM
Reply:

Similar Artilces:

Zoom in Report view
I have created a report that has a lot of stuff on it that I need to print out on a 8.5 x 11 paper, but on the screen I have to use such a small font that it is hard to read in Report View. It is ok in print preview because i can zoom in and magify the view in that mode, but I haven't been able to find any way to magnify the report view. Is there a way to do this (zoom in) in the report view? In a quick test, I can't see how to do this either. You can add the zoom button to the Quick Access Toolbar, but it's grayed out in Report view. You can add a command button to a rep...

Reprint Aging report
Hi, Due to a spooling missfunction, the monthly 'Aging Report' didn't came out and we want to reprint it. Where can I find this option (if there is any ) ? Thanks in advance You can run the historical aged balance and use what ever date you want. Rick "Beat BUCHER" <Beat BUCHER@discussions.microsoft.com> wrote in message news:491CCB47-4700-4109-810A-175B9E234DA0@microsoft.com... > Hi, > Due to a spooling missfunction, the monthly 'Aging Report' didn't came out > and we want to reprint it. Where can I find this option (if there is any ) >...

exporting reports to Excel
Is there any way to export our Great Plains reports to excel? TIA TRD Yes, there is a way. It's not pretty. It's not easy. It's a real pain. Print the reports to a delimited file and open up the file in Excel. You'll need to do maintenance on the report. Your best bet is to use SmartList/SmartList Builder, Crystal, SQL Reporting Services, or some other report writer than export to Excel. Or you can wait until v10. Version 10 will offer 75 SRS reports and 100 Excel-based reports, according to documentation I've seen. -- Charles Allen, MVP "TRD" wr...

Compare Monthly Item Sales
I am trying to create a report that will allow me to compare a specific item or items by suppliers from one month to another month. (Example: All items sold by ABC in March 2007 Vs. March 2006 or ABC items sold in March 2007 Vs. Feb 2007) I see that there are sales reports that compare sales totals between months, but I have had no luck trying to convert that information to actual items or companies. Again looking for QTY totals not sales totals. Hope someone has some neat ideas. Thanks, Neil HI you got me. well good news is that a month later i have develop this report for Mr. David ...

Setting for Calendar month view
I want Outlook's "Month View" to open with the top row showing last week, the second row showing the current week, and so on. Currently I: Swithc to Calendar {PgDn} {PgDn} {Today} {Up} {Dn} But there has to be a better way. Thanks Robert That is not possible with current versions of outlook. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: EMO-NEWSLET...

Field promotion in a Report.
Hi NG. I have a Table - Quiery an a Report. The Reports contain fields: First_Lastname Adr1 Adr2 ZipCity If this was mailmerge in Word and Adr2 field was empty I would have this information in the letter: First_Lastname Adr1 ZipCity Is it possible to do so in the Report? -- Best Regards from Joergen Bondesen On Fri, 18 Jan 2008 23:13:10 +0100, Joergen Bondesen wrote: > Hi NG. > > I have a Table - Quiery an a Report. > The Reports contain fields: > > First_Lastname > Adr1 > Adr2 > ZipCity > > If this was mailmerge in Word and Adr2 field was e...

Logo on the report
Could you help me finding some thread or source from where to copy some samples to build a logo on the report ? I want to build a simple logo without a picture bot somewhat having a better look -- Message posted via http://www.accessmonster.com Put an image control on your report. -- Dave Hargis, Microsoft Access MVP "samotek via AccessMonster.com" wrote: > Could you help me finding some thread or source from where to copy some > samples to build a logo on the report ? I want to build a simple logo > without a picture bot somewhat having a better look > > --...

Access report to PDF
Hi, I need to print a report automatically to a pdf file customizing the pdf destination file name and path aswell. Any hint? Thanks a lot Take a look at the free ReportToPDF utility Stephen Lebans has at http://www.lebans.com/reporttopdf.htm -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "diego" <xab@nowhere.it> wrote in message news:0F61573F-79B2-4E86-9CF9-1B50EFEA0737@microsoft.com... > Hi, > > I need to print a report automatically to a pdf file customizing the pdf > destination file name and path aswell. > >...

Adding a Crystal Report to the RMS reports folder
I can create a Crystal Report for the RMS but when I try to veiw the report I get a error message. What are the steps I need to follow to add the report to RMS so the user can view it Hi..richuscav, You have to make <reportname>.def file along with <reportname>.rpt file and put it in CrystalReport directory of store operations. You can have a reference of dafault DEF files in that directory. If the DEF file is correct you will get the report in Manager. I think this subject is already discussed in this forum,,so search for it. Good luck, CP.Dinesh Jumbo Electronics Co.LLC...

Search My Report Help
I made a report from a table that is a list of files with their hyperlink, a description column, etc. I want to make a report that has a search button at the top, and can be searched by All columns or the few columns I have. I have tried to search for answers and am new to Access. Can someone give me a good resource, give me some code help, or tell me if this is possible? You can't put command buttons on a report. Therefore you need to "search" for the data in the query that you use for the record source for the report. You can search on a form. In fact there...

Error when running Z report
When I generate the report I get the initial filter screen, and when I click ok to view the report, I get the following error message: "The report could not be loaded due to the following error: (-2147217900) Cannot truncate table 'Cashier' because it is being refrenced by a FOREIGN KEY constraint. Hey Daniel did you ever got that problem fix with the Run-Time Error "2147217900" Cannot truncrate 'Cashier' becuase it belong to a foreign key. If so, Can you tell me how to do it Thanks Robert. "Daniel" wrote: > When I generate the report I get...

Crystal report session timeout
Hello, we have a problem with the crystal enterprise server included with MS CRM. When we open a report, the first report is ok, but drilling into the report will cause the error message "report session timeout". I already changed the command parameter maxDBResultRecords to 0 and created a web.config file under inetpub\wwwroot\report where I set the execution timeout to 600. I also tried to increase the max thread number and max idle timeout. Nothing helped. Does anyone have any ideas? Thanks Patrik Mueller Hello John, it is not a resultset problem, as the report should ret...

Partywise BreakUp Report
Hi, I have an excel sheet with data in which parties ID in column 'C' and amount, date and other columns etc. I want a macro solution which would 'generate partywise breakup' in 'sub-total' like format in another single sheet, datewise ascending order. Example: ID Amount AZ1101 10 AZ1102 20 AZ1103 30 AZ1109 10 AZ1101 10 AZ1101 40 AZ1102 100 AZ1103 80 --------------------- Total = 300 Result should be as below in single sheet. ID Amount AZ1101 10 AZ1101 10 AZ1101 40 ------------------ Total = 60 ID Amount AZ1102 20 AZ1102 100 ------------------ Total = 120 ID Am...

Pivots: Grouping two fields (year, month)
My pivot table contains two fields I need grouped: "Fiscal Year" and "Fiscal Month." Both field are numeric, not dates. The grouping would be like this: Fiscal Year (2003) and Fiscal Months (9-12) GROUPED WITH Fiscal Year (2004) and Fiscal Months (1-3) Is there a quick way to do this within the pivot? If you group the "months", the table groups all the months regardless of "year"....can't figure this one out. TIA... John, The easiest way would be to have another column in your data table indicating the desired grouping: you could use a formula t...

reports-show/hide #2
I'm new to rms and have a couple of questions i hope someone can answer. when i run a report of merchandise, and click to show/hide i am not given an option to show/hide sub-descriptions (i use one for sizes) i really need this ability for reorder purposes. can anyone help me with this? am i doing something wrong? ...

Report Question Please
How do I print a copy of the check register in date order just as it appears in the checkbook? I do not want the deposits to be seperated from the checks. Thanks in advance... Bob Use an Account Transactions report instead. You can customize it to print the date range, account, categories, etc. just the way you want. "Bob Newman" <bobnewman@worldnet.att.net> wrote in message news:CNY7b.137258$3o3.9776932@bgtnsc05-news.ops.worldnet.att.net... > How do I print a copy of the check register in date order just as it appears > in the checkbook? I do not want the deposi...

report showing oldest inventory
Is there a report that will show an "aging summary" for inventory items? MP, How about the Item Movement Report? -- * "MP" <MP@discussions.microsoft.com> wrote in message news:ED41EC49-4C04-4EA9-A712-6BB48EA29BEA@microsoft.com... Is there a report that will show an "aging summary" for inventory items? Yes, thanks. I just hadn't explored that report enough to notice that I could filter by last received date. "Jeff" wrote: > MP, > > How about the Item Movement Report? > > -- > * > "MP" <MP@discus...

Sending reports to via email
Is it possible to create a workflow that sends an email with a report to management with all opportunities where the closing date is in the past...? MS CRM using Reporting Services (SSRS) and SSRS have this functionality build-in. How to configure SSRS: http://msdn2.microsoft.com/en-us/library/ms345234.aspx Regards. pflis ...

Report based on form filter?
Hello, I have a form with Multiple fields filter based on and event procedure where I can filter multiple combo fields. I would like to print a report after the filtered fields, but not sure how to like what is the control source etc.. Any help is appreciated. Here is my sample form filter event procedure. Private Sub cmdFilter_Click() Dim strWhere As String Dim lngLen As Long If Not IsNull(Me.cboMach) Then strWhere = strWhere & "([Mach] = '" & Me.cboMach & "') AND " End If I...

Reports Execution Error
Hi I am having trouble on reports like i.e unable to open reports with a user login.-- EXECUTE DENIED FOR OBJECT(rsexecutioncommand) It works with a administrator login.. Pls suggest!! This looks like a SQL permission problem. If a CRM user runs a report using their AD credentials then they will have Select permission on all filtered views, but don't have permission on any other objects. Is this a built-in report, or a custom report ? There shouldn't be any permission problems on built-in reports -- David Jennaway - Microsoft Dynamics CRM MVP Web: http://www.excitation.co.uk ...

Creating a price list (report)
I have posted this question twice already, but it still hasn't shown up, so here goes again! - (sorry if you have read it already) I have imported data from "Navicat MySQL", from which I would like to create a categorised price list. Simple, I hear you say, but not so. The data categories I am using are: Category Sub Category RefNumber Range Product Size/Length 2007 UK Price Active (true / false) I created a query, so that only the true (available) products were listed. From this I tried to create the price list using the report wizard. When I get to the part to categor...

Report #3
I need to create a items transfer report from inverntory account to other accounts. So what tables and field are required. Any help in this regard will be appricated. Thanks Try the table IV30300 where DOCTYPE = 3 Inventory accounts fields of interest are IVIVINDX and IVIVOFIX The lookup table GL00100 can be used to find the account index descriptions -- Regards Andrew Dean Envisage Software Solutions Pty. Ltd. www.envisagesoftware.com.au (Automate eConnect batch posting with "Post Master") "Deepwater" wrote: > I need to create a items transfer report from inve...

Trying to modify XML X-REPORT
Following code segment looks good but the <IF> equates to true always and all departments print... What am I doing wrong? <ROW> "Non-Taxable Sales:" </ROW> <SET name="NonTaxableTotal" type="vbCurrency"> 0 </SET> <SET name="TATTOO" type="vbString"> "TATTOO" </SET> <SET name="BODYPIERCING" type="vbString"> "BODY PIERCING" </SET> <FOR each="ReportDepartment"> <IF> <CONDITION> Report.Department.Name = TAT...

Report Will Not Open 02-22-08
I have only one of several reports that will not open either in view or design view. All the others are ok. The error message reads, "There isn't enough memory to perform this operation. Close unneeded programs and try again..." My Vista computer has 3 GB of ram!! This happens both using Access 2003 (512 MB Ram) and Access 2007. How do I get this program to open? ed Sounds like the report has gone bad. Can't promise, but see if this rescues it: 1. Make a back up of your MDB, so you get mulitiple chances at fixing it. 2. Open the Immediate Window (Ctrl+G), and ent...

i need monthly ledger that is catagorized by payment due dates
"j.marie" <j.marie@discussions.microsoft.com> wrote in message news:9C223E6B-0C7C-46AA-BEAE-2B49F97D4949@microsoft.com... > Nothing. PLEASE write your question in the body of the post, not in the subject line. This is not a chatroom. Thankyou. In answer to your question, you would be better advised to buy an inexpensive dedicated accounting package. That will do what you want "out of the box". ...