Skip to Content
0
Former Member
Dec 13, 2010 at 02:08 PM

DrillThrough setup question

27 Views

I need to get the DrillThrough to work for a consolidation application so I can see individual journal entries per account. I found and read the HTG (http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/f0abf40a-69be-2d10-019d-eab444dab746?quicklink=index&overridelayout=true) . In this document it says that a journal.dqy is built automatically, and since I want to drill into journal details I had a look for it.

XLODBC
1
Initial Catalog=Demo5b;Data Source=DEMO5;Connect Timeout=90;integrated security=SSPI;
Select [A].EvDescription as [Category Desc],<b>.EvDescription as [Time Desc],[C].EvDescription as [Entity Desc],[D].EvDescription as [ConsolView Desc],[E].EvDescription as [DataSrcL Desc],ht.[Idj] as [Journal ID],ht.[Description] as [ Journal Desc],ht.[EntryStatus] as [ EntryStatus],[F].EvDescription as [AccountL Desc],[G].EvDescription as [AccDetail Desc],[H].EvDescription as [IntCo Desc],dt.[Credit] as [Credit],dt.[Debit] as [Debit] from jrnLegal ht,jrnDTLLegal dt,[mbrCategory] A,[mbrTime] B,[mbrEntity] C,[mbrConsolView] D,[mbrDataSrcL] E,[mbrAccountL] F,[mbrAccDetail] G,[mbrIntCo] H where  ht.IDJ=Dt.IDJ And [A].[ID]=ht.[Category] And <b>.[ID]=ht.[Time] And [C].[ID]=ht.[Entity] And [D].[ID]=ht.[ConsolView] And [E].[ID]=ht.[DataSrcL] And [F].[ID]=dt.[AccountL] And [G].[ID]=dt.[AccDetail] And [H].[ID]=dt.[IntCo] And ht.[Category]=N'%Category%' And ht.[Time]=N'%Time%' And ht.[Entity]=N'%Entity%' And ht.[ConsolView]=N'%ConsolView%' And ht.[DataSrcL]=N'%DataSrcL%' And dt.[AccountL]=N'%AccountL%' And dt.[AccDetail]=N'%AccDetail%' And dt.[IntCo]=N'%IntCo%'
Category	Time	Entity	ConsolView	DataSrcL	Idj	Description	EntryStatus	AccountL	AccDetail	IntCo	Credit	Debit

Also, under DrillThrough in the Web Admin interface I found an entry for DrillKey JRNDETAIL to use this journal.dqy. So I added DRILLKEY to my account dimension and used JRNDETAIL on all accounts. I need this drillthrough into the journal details to work from every account, so I was hoping that I could use the same drillkey for all.

I then built myself an EvDre report and the drillthrough option was available on an account where I had posted a journal. However, the result was an error message "Cannot connect database or open result".

I then followed the HTG and created a new dqy file from which I copied the line with connection details into my original journal.dqy

XLODBC
1
DRIVER=SQL Server;SERVER=SAP-CPMVM;UID=;APP=Microsoft Office 2003;WSID=SAP-CPMVM;DATABASE=MasterBPC;Trusted_Connection=Yes
Select [A].EvDescription as [Category Desc],<b>.EvDescription as [Time Desc],[C].EvDescription as [Entity Desc],[D].EvDescription as [ConsolView Desc],[E].EvDescription as [DataSrcL Desc],ht.[Idj] as [Journal ID],ht.[Description] as [ Journal Desc],ht.[EntryStatus] as [ EntryStatus],[F].EvDescription as [AccountL Desc],[G].EvDescription as [AccDetail Desc],[H].EvDescription as [IntCo Desc],dt.[Credit] as [Credit],dt.[Debit] as [Debit] from jrnLegal ht,jrnDTLLegal dt,[mbrCategory] A,[mbrTime] B,[mbrEntity] C,[mbrConsolView] D,[mbrDataSrcL] E,[mbrAccountL] F,[mbrAccDetail] G,[mbrIntCo] H where  ht.IDJ=Dt.IDJ And [A].[ID]=ht.[Category] And <b>.[ID]=ht.[Time] And [C].[ID]=ht.[Entity] And [D].[ID]=ht.[ConsolView] And [E].[ID]=ht.[DataSrcL] And [F].[ID]=dt.[AccountL] And [G].[ID]=dt.[AccDetail] And [H].[ID]=dt.[IntCo] And ht.[Category]=N'%Category%' And ht.[Time]=N'%Time%' And ht.[Entity]=N'%Entity%' And ht.[ConsolView]=N'%ConsolView%' And ht.[DataSrcL]=N'%DataSrcL%' And dt.[AccountL]=N'%AccountL%' And dt.[AccDetail]=N'%AccDetail%' And dt.[IntCo]=N'%IntCo%'
Category	Time	Entity	ConsolView	DataSrcL	Idj	Description	EntryStatus	AccountL	AccDetail	IntCo	Credit	Debit

While creating the query via Excel I got the complete content of my jnlDTLLegal table but when I try the drillthrough I get the same error as before.

Can anyone help me find out what the problem is?

Thanks