Customization and Integration

[Var2LLC]

I have been trying to get a custom BIE view to preview to another custom BIE view without much success, in 7.2.   What I am trying to accomplish is similar to the AR invoice BIE, when you click on the invoice, the preview on the bottom displays details for that particular invoice.   Here is what I have done so far:

1. Created two BIE views (SQL view, tasks and all).  The views work fine.   Both view have an ID field that tie them together.  For example,  

     vdvSummary -  ID, OrigAmount, Balance

     vdvDetail -        ID, TranType, Amount

2. In Maintain Business Insights Views/Context Menus screen for the summary view, I did the following:

    a.  Ensure that the column mapping for the key ID field is mapped to the underlying table (custom table)

    b.  In Navigation Tasks, under Preview, added a line that points to the Detail view, with Filter type of Static and everything else blank.

    c.   In Navigation Relationships, I have a line for the Detail view where the source the destination columns are both the ID column.  No bridge was set because there is a direct relationship between the two.

In BIE, The Preview pane on left shows the correct detail view.   And the bottom pane shows all the columns from the detail view, but no data ever shows there, even when I click a summary record that has details.   SQL trace reveals that no SQL call is attempted to pull data for that ID.

What did I misss?

Thanks,

John

[manookian]

John,

What you described should work. You should see something in the SQL trace; otherwise not even the column names would appear. It looks like there maybe an error in vdvDetail.

[trodman]

John,

I've had this problem before and was never able to figure it out. However, I suspect that it has something to do with the tsmDataDict tables which define what are valid relationships between tables. I've always wondered if inserting records in these tables would solve the problem.

A related issue I have noticed is that sometimes you have to set the relationships based on a key field and other times based on an ID field in order to get the previews to work. Again I suspect that this has something to do with the tsmDataDict tables.

[Var2LLC]

Thanks for your replies. 

First off, vdvDetail view defiinitely works.

Secondly, I did try adding the custom tables into Data Dictionary.   But that didn't help either.   I don't get why DD needs to get involved anyway, since all the definitons needed are already set in the Maintain Business Insights Views/Context Menus screen.

Is anyone from Sage monitoring this?  If you could offer some insight, I would greatly appreciate it.

[trodman]

You added the relationships in all the data dictionary tables? Or just the one I mentioned?

[manookian]

John,

Here are some stored procedures you can run to help determine what is going on:

EXEC spDVGetDataviewLinks SourceDataviewKey, EntityTaskType, UserID, ActualUserID, CompanyID

For example this will get you all the links for the Customer preview relationships:

EXEC spDVGetDataviewLinks 8, 100, 'admin', 'admin', 'soa'

Notes: LinkColumnType - 1 = SurrogateKey, 2 = NaturalKey

EntityTaskType - 100 = Preview, 200 = Analyze, 300 = Manage, 400 = Drill Into

EXEC spGetDataDict ViewName, CompanyID, Flag

For example this will show you the data dictionary info for Customer:

EXEC spGetDataDict 'vdvCustomer', 'soa', 0

Have you looked at the SQL trace. There should be a query run everytime a row is clicked in your summary grid.

[Var2LLC]

Thanks for the ideas.  When I ran spDVGetDataviewLinks against my data view, it returns no result.   So I will find out which table is missing data and go from there.

As to Profiler results when I click on a summary record, the last query run is a dynamic query that selects all the fields from the detail view, but with the whereclause of 1=2.   So it appears to be just reading the schema of the view.   It's not passing the key from the summary line to the detail view.

If you have any other ideas, please let me know.

[Var2LLC]

OK, I got it to work finally.    As it turns out, the step I missed is mapping the columns of the detail view to the permanent table as well, which is odd because the views refer to multiple tables, so why would it matter to map the BIE view to the permenant table.

Anyway, now my dilemma is deployment.    I can script the following:

1. Adding tasks for the BIE views

2. Grant Security

3. Adding the data view records

But is there any way to script these:

1. Maintain BI Views/Context Menu actions (relationships)

2. Data Dictionary import of the underlying custom table

3. Column mappings from the BI Views to the underlying table

If possible, I certainly would prefer not to have to set up Data Dictionary utility and use the BIE Views/Context Menu UI to install the views at the client's.

Thanks.

[trodman]

Thanks John.  We were able to get ours to work too.

1. Entry in tsmDomain

2. Entry in tsmDataDictTbl

3. Entry in tsmDataDictCol

4. Map columns in the summary view

5. Map columns in the detail view

[Var2LLC]

Nice.  

BTW, I have figured out what to script as well -  basically items 2-5 in your list.    Mine worked without adding entry into tsmDomain.