Method Community

 Hi:

I found this on your forum over the weekend and tried to make it work without success:

Question: Is there a way to create scheduled reports whereby I can filter by Rep?  For example, I would like a rep to be able see his/her reports such as:

Sales by Customer (whereby invoices are filtered by Rep)

A/R Aging (whereby invoices are filtered by Rep)

Answer:  There are indeed many ways to filter a scheduled report, including the three options you mentioned. In order to view, for example, a Sales by Customer report, for a specific Rep, (which is really a subgroup of your employees list), on step 8. Entity Type select Employee and in the drop-down below select the Rep that you wish to see the report for.

Not sure what I am missing, however, when I set one of these reports up and run it - it breaks due to the Employee filter.

I want to run the QuickBooks report by Sales Rep, who I have connected on the Sales Rep list in QuickBooks to the employees and went to a number of invoices and added the Sales Rep field and linked the invoices to Dan T. Miller as the Sales Rep.  Also, I am logged in for the session as Admin and I have linked employee Dan T. Miller to my user.   So, when I get to step 8 and add Employee /Dan T. Miller - I should be filtering on only Dan T. Miller's invoices, right?   

I want to be able to filter screen lists by the session user and have them see only their lists and reports for their clients.

Thanks in advance for your help.

Shirley

Hi Shirley,

Shirley Makrecky:

Not sure what I am missing, however, when I set one of these reports up and run it - it breaks due to the Employee filter.

I see something odd here, and will need to test it a bit further.

I will post back here once I have additonal information.

-Michael

 Hi Michael:

Wondered if you had a chance to look at this issue further?

I would like to know if there is something extra I need to do to make it work.

These reports are filterable in QuickBooks and should be filterable in Method. 

Any suggestions would be appreciated.

Shirley

Hi Shirley,

I looked into this further and it looks like this is a limitation the QuickBooks SDK. When creating a scheduled report QuickBooks will not allow us to filter by Rep (Rep is technically different then Employee). Have you thought about creating a Method report to achieve the same or simliar results?

-Michael

 Hi Michael:

Ok,  I am working in the Report Designer which has brought up a number of questions I can't figure out?

My Report is based on Customer - Invoice.Customer tables. (Would this be correct?)
1.  If I include the Amount field and the Amount Applied field to the report and I want a third
field to subtract the two to get the balance- is there a way to do calculated fields not bound to a particular field?
There is a BalanceRemaining field but that doesn't seem to pull the difference. 

2. I included a Sales Rep field on this report - under Report task there is a filter string - filter string editor where
I thought I could filter on Sales Rep.  It did not filter correctly.  Gave me two invoices and I know there are at least thirty
for the filtered Sales Rep. 
Would you give a little insite to this filter string editor? I tried to filter using "And" [SalesRep] equal to (initials) (also used Begins with).
Also, used the Customer.Invoice.Customer as the datamember for this report. Doesn't pull all Invoices. If using Customer as datamember then error occurs:
"Object reference not set to an instance of an object." 

3. To create an AR aging filter(30 - 60- 90 days) on report. I would think a calculated field based
on the Date Due, however, I don't see where this can be done.  

I appreciate your replies.

Shirley 

Hi Shirley,

Shirley Makrecky:

My Report is based on Customer - Invoice.Customer tables. (Would this be correct?)

It would probably be a better idea to use the Invoice table as the master table.

Shirley Makrecky:

1.  If I include the Amount field and the Amount Applied field to the report and I want a third
field to subtract the two to get the balance- is there a way to do calculated fields not bound to a particular field?
There is a BalanceRemaining field but that doesn't seem to pull the difference. 

When in the report designer if you right click on the table(Invoice) listed in the Field List, you will see an option to add a calculated field, with a calculated field you can use it to get the difference of those two fields for each record and then just drag the field onto the report.

Shirley Makrecky:

2. I included a Sales Rep field on this report - under Report task there is a filter string - filter string editor where
I thought I could filter on Sales Rep.  It did not filter correctly.  Gave me two invoices and I know there are at least thirty
for the filtered Sales Rep. 
Would you give a little insite to this filter string editor? I tried to filter using "And" [SalesRep] equal to (initials) (also used Begins with).
Also, used the Customer.Invoice.Customer as the datamember for this report. Doesn't pull all Invoices. If using Customer as datamember then error occurs:
"Object reference not set to an instance of an object." 

You can apply the filter for reps in the Generate Report action on the screen that will generate this report, no need to apply direct filters to the report itself.

Shirley Makrecky:

3. To create an AR aging filter(30 - 60- 90 days) on report. I would think a calculated field based
on the Date Due, however, I don't see where this can be done.  

The answer for number 1 should help with this :)

-Michael