Add multiple DBdataSources to a matrix

[Former Member]

Hi there...

I'm trying to copy the Inventory form (907), and that form uses two tables. These are OITW and OITM. Most columns are bound to OITW, but the article name comes from OITM.

All columns are bound to the right datasources, and if I query the first datasource (for OITW) the values are appearing okey, but the the column for the itemname (OITM) stays empty...

Does anybody knows how to solve this...?

I'd rather like to not fill it with a query, because that's not performance wise... :S

[barend_morkel2 · Accepted solution]

Write a stored procedure in SQL to "build your query". Call that stored procedure in your single datasource (which you will bind to your matrix) and there you go.

[Former Member · Accepted solution]

Do you have some code for me please...?

[barend_morkel2 · Accepted solution]

To write a stored procedure?

2 Options :

******************************

To create a stored procedure using the Create Stored Procedure Wizard

Expand a server group, and then expand the server in which to create the view.

On the Tools menu, click Wizards.

Expand Database.

Double-click Create Stored Procedure Wizard.

Complete the steps in the wizard.

******************************

Creating it in SQL Query Analyzer

CREATE PROC pub_info2 @pubname varchar(40) = 'Algodata Infosystems'

AS

SELECT au_lname, au_fname, pub_name

FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id

JOIN titles t ON ta.title_id = t.title_id

JOIN publishers p ON t.pub_id = p.pub_id

WHERE @pubname = p.pub_name

To Execute pub_info2 with no parameter specified:

EXECUTE pub_info2

GO

[Former Member · Accepted solution]

I'm sorry, my fault, I had to be more specific...

How can I add a stored procedure to a DBDataSource...? (and pass some conditions...?)

[AlexGrebennikov · Accepted solution]

Hi!

I can't find a thread where i asked SAPs, why we can't use JOINs-statement in DBDataSources.

I was told that it will be implemented in further versions.

Now there are 2 workarounds:

1. write stored procedure/or user defined function to <b>populate temporary table</b> with

data from your 2 tables, then <b>use DBDataSource with that temporary table</b>

2. use UserDataSources

[AlexGrebennikov · Accepted solution]

Hi again!

>> <i>I can't find a thread where i asked SAPs, why we can't use JOINs-statement</i>

here is that link:

[Former Member · Accepted solution]

Hi Alexey,

I did already read that thread, thnx anyway... But I rather don't want to use a temporary table (what happens if two users open the same form with different variables at the same time?)

And userdatasources are way to slow...

But Barends way, using a stored proc directly to fill a datasource...? Isn't that possible...?

[AlexGrebennikov · Accepted solution]

Hi!

>> But I rather don't want to use a temporary table

>> (what happens if two users open the same form with

>> different variables at the same time?)

You can create table with name like <i>'table_name_' + UserID</i>. So every user will have his own temp table.

>> But Barends way, using a stored proc directly to fill

>> a datasource...? Isn't that possible...?

I suppose he meant something other..

[Former Member · Accepted solution]

Looks like a option...

But, ehh, how do I call a stored procedure in SBO, and how do I pass the variables...?

[AlexGrebennikov · Accepted solution]

Ok. step by step.

<u><b>1</b></u>. create a query. it can be like

	CREATE PROCEDURE __as_MyStProc
		@SearchStrForItems 	nvarchar(20)
	AS
	BEGIN
	
		IF EXISTS
			(
			SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
			WHERE 	TABLE_NAME = '__as_myTempTable'
			)
			DROP TABLE __as_myTempTable
	
		SELECT 
			T0.ItemCode, 
			T0.ItemName, 
			T1.ItmsGrpNam
		INTO
			__as_myTempTable
		FROM
			OITM T0
			LEFT JOIN OITB T1 ON
				T1.ItmsGrpCod = T0.ItmsGrpCod
		WHERE
			T0.ItemCode LIKE @SearchStrForItems
	END
	GO

<u><b>2</b></u>. save it in SQL Server

<u><b>3</b></u>. run in your AddOn that procudere by

	strQuery = "EXEC __as_MyStProc '" + strItemCode+ "'";
	oRS.DoQuery(strQuery);

<u><b>4</b></u>. create DBDataSource for our temp table:

frm.DataSources.DBDataSources.Add("__as_myTempTable");

<u><b>5</b></u>. bind data to columns:

oColumn = oColumns.Item("myCol");
oColumn.DataBind.SetBound(true, "__as_myTempTable", "ItemCode");

that it's only conception. the real solution depends on your project. hope it'll help.

[Former Member · Accepted solution]

hi dear your code which you have send is really too helpful

but when i am using the way u directed it shows an error

that

'DataSource - Table not found'

i think this is because the temprary table we r using is neither user defined nor SBO table.

[AlexGrebennikov · Accepted solution]

Hi Vishwas!

so, what step crashes?

what codeline raise an error?

could you check if that temptable exists (u can do that in EntManager)?

[Former Member · Accepted solution]

hi Alexey Grebennikov !

<i>>>so, what step crashes?

>>what codeline raise an error?</i>

the following line raises a error

[code] oRecordSet = mobjCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

oRecordSet.DoQuery("EXEC sp_ATL_SalesAssigPlan " & intChecked)

<i><b>frm.DataSources.DBDataSources.Add("mytable")[/</b></i>code]

the italic line genrates an error and i have seen that the table do exists in database.

[AlexGrebennikov · Accepted solution]

Hi Vishwas!

i havn't checked all business-logic related to that topic.

i have just checked your issue and line

frm.DataSources.DBDataSources.Add("notUDT_notSAP_butTempTable") doesn't generate any error.

[Former Member · Accepted solution]

Hi Alexey Grebennikov,

I had tried your method but i m getting the same problem

"TABLE DOES NOT EXISTS" on same line. But i have seen that table exists in SQL Enterprise manager.

frm.DataSources.DBDataSources.Add("Temp")

Also it does not recognize other tables of SQL but it works fine on SBO Tables and UDT.

I m using SBO 2004 Patch 21.

i have tried all possible methods but i m unable to run it.

Please send me the running method

[AlexGrebennikov · Accepted solution]

Hi friends!

>> that it's only conception. the real solution depends

>> on your project. hope it'll help

sorry that i've given you a wrong direction.

i said that it's only conception and it have to be tested.

probably it sounds like a bug (or feature?), and if you still require this functionality - post your problem at service.sap.com and you will get a reply.

i'll be glad if SAP'll comment on that issue here.

thanx.

[Former Member · Accepted solution]

It doesn't work because B1 maintains the DB metadata(e.g., tables names, columns, etc.) in its application, even though you have added the table in DB, but the relative data didn't been added to B1 metadata, so B1 will still reported that "Table doesn't exists"

Back to the original problem, as far as I know, the only possible way is to use UserDataSource.

[former_member185703 · Accepted solution]

2005 SDK will expose a <b>DataTable</b> datasource object which allows to use SQL statements (including joins etc.) also for non-UDTs!

This new datasource type works with the new <b>Grid</b> object which also exposes a SAP Business One-like "TreeView"...

You might want to check this out (and hope the customers will migrate to 2005 as soon as it reaches "General Availablity"; currently 2005A is in Ramp-Up)...

Just in case you missed it:

There were sessions about SDK / DI API + UI API (available on SAP Service Marketplace... => SMB Portal => "Education" section...):

SDK & DI API - New Features and Enhancements

Replay (wrf, 13.60 MB):

https://websmp106.sap-ag.de/~sapidb/011000358700002914632005E.wrf

Presentation (pdf, 173 KB):

https://websmp106.sap-ag.de/~sapidb/011000358700002914622005E.pdf

(SDK &) UI API - New Features and Enhancements

Replay (wrf, 13.90 MB):

https://websmp106.sap-ag.de/~sapidb/011000358700002942972005E.wrf

Presentation (pdf, 226 KB):

https://websmp106.sap-ag.de/~sapidb/011000358700002933092005E.pdf

HTH,

Frank

PS: Please note that especially in the DI API there are even more new properties exposed for "old" objects than currently marked with the red star (as usual)!

I hope this will be fixed very soon...