Stored Proc Universe

[Former Member]

I have a case where my universe is designed off of MS SQL Stored Proc. This stored proc accepts some parameters and returns different number of columns every time. For e.g.

If I run this proc for Company A, the output columns would be:

ID CompanyName RegionA RegionB RegionC

and when I run it for company B, the output columns would be:

ID CompanyName RegionC RegionD RegionE RegionEast

With these dynamic returning columns, how can I create my objects in the universe? Is it possible to create single universe that can handle the dynamic output of a Stored Proc?

If not, then what are my alternatives to achieve this goal.

Any direction to the right path shall be highly appreciated.

Thank you.

[Former Member]

Hi,

You can do this as follows which is simple and clear!

1. For any input ensure that, the procedure returns same number of columns.

2. For any column which is not part of the output assign default values such as 0 for nemeric and "Not Applicable" for string.

This way, the universe will be clear and simple.

Hope this helps!

[former_member329114]

I believe your output must be a fixed number of columns. You will probably need to create additional stored procedure tables and make your parameters static while ensuring the combination of the static parameters return the same columns.

[Former Member]

Hi,

Create Stored procedure like:

CREATE PROCEDURE [dbo].[sp_country]

@p_param VARCHAR(30)

AS

SELECT c.Country_Id, Country_Name, Region_Name FROM dbo.Country c,dbo.Region r

WHERE c.Country_Id=r.Country_Id and Country_Name=@p_param

In Universe

In Stored Procedire Editor dialog box

Enter value in Value field

Select 'Prompt me for a value' from Next Execution dropdownlist

Enter Prompt label

click on ok.

In WebI

Drag and drop the stored procedure in the Create Query panel.

Run the query

enter Country name

click on Run query button

Now you will get the correct result.

insert break on id and countryname

[Former Member]

Thank you Swarna. Your suggested proc is going to produce Regions in row format, whereas in my case the Region are dynamically created as columns.

I am already using the way you have suggested and binding the data with crosstab table. I am trying to get away from crosstab report and want to use simple Row Table template.

Here is the sample result set for each company

For Country 1

ID CompanyName RegionA RegionB RegionC

== ============ ======= ======= =======

1 My Company 2323 654 2345

2 Test Company 7863 3232 8898

For Country 2

ID CompanyName RegionC RegionD RegionE RegionEast

== ============ ======= ======= ====== =========

1 xxx 2323 654 2345 3433

2 aaa 7863 3232 8898 7654

ID and CompanyName are the static columns whereas Regions are dynamic by country.

Thank you once again.

[Former Member]

Hi,

Try this stored procedure. You will get what you expected. I tried and I got the answer.

Let me know whether this procedure working or not.

Create PROCEDURE sp_CountryRegion 
	@Param1 varchar(30)
AS
BEGIN
	
    SELECT c.Country_Id,Country_Name, LEFT(el.RegionList,LEN(el.RegionList)-1) as RegionNames
    FROM dbo.Country c
          CROSS APPLY (SELECT Region_Name + ',' AS [text()]
          FROM dbo.Region r
          WHERE r.Country_Id=c.Country_Id
           FOR XML PATH(''))el(RegionList)
    where c.Country_Name=@Param1
END
GO

Edited by: Swarna K on Apr 8, 2011 7:21 AM

Edited by: Swarna K on Apr 8, 2011 7:24 AM

Edited by: Swarna K on Apr 8, 2011 7:24 AM