Excel Queries

[Merle Schwickerath]

Anyone done any Data Queries in Excel against GP? I've wrote several in
Excel going against Access, AS400 & others & now considering the GP database.
Just looking for any advice or warnings. FRx is not pulling the desired
data that I know is in GP

Thanks

[Dencio]

Hi Merle,

What specific fields in GL are you trying to show in FRx? We may able to
work around on this within FRx. Another approach is to use GP Smarltist to
extract the same information in Excel and use the 'Link to External
Worksheet' in FRx.

Cheers,

Dennis Araullo, MACS
Microsoft Certified Technology Specialist GP,CRM,AX
MCITP Installation and Configuration AX 2009
MCITP Database Administrator SQL 2008
Citrix Certified Enterprise Administrator

[Victoria [MVP]]

We've done a number of reports in Excel 2007 using stored procedures or
views in the GP SQL databases. The biggest gotcha is typically security.
You will not be able to use GP logins and most companies will not want to
hardcode anything...so...you can either create a SQL user that everyone can
use...or my preference is to use windows authentication.

--
Victoria Yudin
Dynamics GP MVP
Want to use Crystal Reports with GP?
http://www.flex-solutions.com/gpreports.html
blog: http://www.victoriayudin.com

"Merle Schwickerath" <MerleSch...@discussions.microsoft.com> wrote in
message news:EBE9F198-E816-4499...@microsoft.com...

[Merle Schwickerath]

I'm using WIndows Authentication & this seems to be working fine. My
biggesting issue is locating the YTD field. I've pulled in a periodbalance &
I could probably get to YTD for the income statement, but how do i fine the
account balance for a particular period for any balance sheet accounts?

Merle

[Victoria [MVP]]

There is no YTD field for GL account balances. This is why FRx is so
helpful. Using just GP tables you will have to do the calculations
yourself.

--
Victoria Yudin
Dynamics GP MVP
Want to use Crystal Reports with GP?
http://www.flex-solutions.com/gpreports.html
blog: http://www.victoriayudin.com

"Merle Schwickerath" <MerleSch...@discussions.microsoft.com> wrote in

message news:F15F783E-5EB8-4552...@microsoft.com...

[Merle Schwickerath]

I can calc YTD income & expense, but how does a person come up with the
balance sheet amount that are YTD if GP only has net monthly amounts? Is
there a end of the year balance field?

[Victoria [MVP]]

There is no YTD number or account balance stored for the GL. Here is some
code to get today's balance for an account:

select a.ACTNUMST [Account], sum(g.DEBITAMT-g.CRDTAMNT) Balance
from GL20000 g
inner join GL00105 a
on a.ACTINDX = g.ACTINDX
where g.trxdate <= getdate() --replace with date if desired
group by a.ACTNUMST
order by a.ACTNUMST

Not sure if it will work for what you need, but it may be a start. This
assumes that the prior year is closed for P&L accounts, if that's not the
case, you would need to add an additional restriction for g.trxdate.

--
Victoria Yudin
Dynamics GP MVP
Want to use Crystal Reports with GP?
http://www.flex-solutions.com/gpreports.html
blog: http://www.victoriayudin.com

"Merle Schwickerath" <MerleSch...@discussions.microsoft.com> wrote in

message news:94551856-6F60-45F5...@microsoft.com...

[Jim Hummer]

On Dec 29, 10:25 am, "Victoria [MVP]" <victo...@flex-solutions.com>
wrote:

> There is no YTD number or account balance stored for the GL.  Here is some
> code to get today's balance for an account:
>
> select a.ACTNUMST [Account], sum(g.DEBITAMT-g.CRDTAMNT) Balance
> from GL20000 g
> inner join GL00105 a
> on a.ACTINDX = g.ACTINDX
> where g.trxdate <= getdate()  --replace with date if desired
> group by a.ACTNUMST
> order by a.ACTNUMST
>
> Not sure if it will work for what you need, but it may be a start.  This
> assumes that the prior year is closed for P&L accounts, if that's not the
> case, you would need to add an additional restriction for g.trxdate.
>
> --
> Victoria Yudin
> Dynamics GP MVP

> Want to use Crystal Reports with GP?http://www.flex-solutions.com/gpreports.html
> blog:http://www.victoriayudin.com
>
> "Merle Schwickerath" <MerleSchwicker...@discussions.microsoft.com> wrote in
> messagenews:94551856-6F60-45F5...@microsoft.com...

>
>
>
> >I can calc YTD income & expense, but how does a person come up with the
> > balance sheet amount that are YTD if GP only has net monthly amounts?  Is
> > there a end of the year balance field?
>
> > "Victoria [MVP]" wrote:
>
> >> There is no YTD field for GL account balances.  This is why FRx is so
> >> helpful.  Using just GP tables you will have to do the calculations
> >> yourself.
>
> >> --
> >> Victoria Yudin
> >> Dynamics GP MVP
> >> Want to use Crystal Reports with GP?
> >>http://www.flex-solutions.com/gpreports.html
> >> blog:http://www.victoriayudin.com
>

> >> "Merle Schwickerath" <MerleSchwicker...@discussions.microsoft.com> wrote
> >> in
> >> messagenews:F15F783E-5EB8-4552...@microsoft.com...

> >> > I'm using WIndows Authentication & this seems to be working fine.  My
> >> > biggesting issue is locating the YTD field.  I've pulled in a
> >> > periodbalance &
> >> > I could probably get to YTD for the income statement, but how do i fine
> >> > the
> >> > account balance for a particular period for any balance sheet accounts?
>
> >> > Merle
>
> >> > "Victoria [MVP]" wrote:
>
> >> >> We've done a number of reports in Excel 2007 using stored procedures
> >> >> or
> >> >> views in the GP SQL databases.  The biggest gotcha is typically
> >> >> security.
> >> >> You will not be able to use GP logins and most companies will not want
> >> >> to
> >> >> hardcode anything...so...you can either create a SQL user that
> >> >> everyone
> >> >> can
> >> >> use...or my preference is to use windows authentication.
>
> >> >> --
> >> >> Victoria Yudin
> >> >> Dynamics GP MVP
> >> >> Want to use Crystal Reports with GP?
> >> >>http://www.flex-solutions.com/gpreports.html
> >> >> blog:http://www.victoriayudin.com
>

> >> >> "Merle Schwickerath" <MerleSchwicker...@discussions.microsoft.com>
> >> >> wrote
> >> >> in
> >> >> messagenews:EBE9F198-E816-4499...@microsoft.com...

> >> >> > Anyone done any Data Queries in Excel against GP?  I've wrote
> >> >> > several
> >> >> > in
> >> >> > Excel going against Access, AS400 & others & now considering the GP
> >> >> > database.
> >> >> > Just looking for any advice or warnings.  FRx is not pulling the
> >> >> > desired
> >> >> > data that I know is in GP
>

> >> >> > Thanks- Hide quoted text -
>
> - Show quoted text -

Here is another one. Replace Year and Month

select b.ACTNUMST,SUM(a.PERDBLNC) from GL10110 a
LEFT OUTER JOIN GL00105 b on a.ACTINDX = b.ACTINDX
where YEAR1 = 2014 and PERIODID < 8
GROUP BY b.ACTNUMST