ReportPortal: Zero-footprint OLAP client for Microsoft Analysis Services

ReportPortal

All Forums

ReportPortal General Forum

Report Portal General Issues

SQL Support

Note: You must be registered in order to post a reply.
To register, click here. Registration is FREE!

Screensize:

UserName:

Password:

Anti-Spam Code:

Message:

* HTML is ON
* Forum Code is OFF

Smilies

[quote][i]Originally posted by juliom[/i]
[br]I'm evaluating Report Portal 1.5. I'm creating an SQL Report (againt SQLServer 2000 SP3a) that uses a Table variable to hold some temporary info. The idea is to load the data into this Table variable using an INSERT INTO.
It works just fine in SQL Query Analizer, but if I paste the Transact SQL into the SQL Report Designer and try to test it I just don't get any results... 
This is an example of the Transact SQL I'm using...

----- Sample code begins ---------

DECLARE @SubResumen Table ([RecNum] BIGINT identity, 
                    [A�o] INT,
                    [Mes] NVARCHAR(20),
		    [Mes2] INT,
		    [Sucursal] NVARCHAR(40),
		    [Cliente] NVARCHAR(10),
                    [Monto] MONEY,
		    [Puntos Obtenidos] MONEY,
		    [Puntos Redimidos] MONEY
                    )

INSERT INTO @SubResumen ([A�o], [Mes], [Mes2], [Sucursal],
			 [Cliente], [Monto], [Puntos Obtenidos],
			 [Puntos Redimidos])
    SELECT r.[A�o], 'Todos', 0, 'Todas',
	 r.[Cliente], SUM(r.[Monto]), SUM(r.[Puntos Obtenidos]),
	 SUM(r.[Puntos Redimidos])
    FROM dbo.Resumen r
    WHERE r.[Monto] > 0
    GROUP BY r.[A�o], r.[Cliente]
    ORDER BY SUM(r.[Monto]) DESC

SELECT * FROM @SubResumen

--------- Sample code ends ------------

I've done this using temporary tables and using regular tables to hold the data RESULTING from the Insert Into and it works just fine in SQL Query Analizer but it doesn't work in the SQL Report Designer.

The other thing I tried was to create a store procedure with code and I get exactly the same.

If I remove the Insert Into from the code it works fine in the SQL Report Designer but of course I don't get the desired results...

Any Ideas???

Thanks,

Julio.
[/quote]

Check here to include your profile signature.
Check here to subscribe to this topic.

T O P I C R E V I E W
juliom	Posted - 01/06/2005 : 09:45:56 I'm evaluating Report Portal 1.5. I'm creating an SQL Report (againt SQLServer 2000 SP3a) that uses a Table variable to hold some temporary info. The idea is to load the data into this Table variable using an INSERT INTO. It works just fine in SQL Query Analizer, but if I paste the Transact SQL into the SQL Report Designer and try to test it I just don't get any results... This is an example of the Transact SQL I'm using... ----- Sample code begins --------- DECLARE @SubResumen Table ([RecNum] BIGINT identity, [A�o] INT, [Mes] NVARCHAR(20), [Mes2] INT, [Sucursal] NVARCHAR(40), [Cliente] NVARCHAR(10), [Monto] MONEY, [Puntos Obtenidos] MONEY, [Puntos Redimidos] MONEY ) INSERT INTO @SubResumen ([A�o], [Mes], [Mes2], [Sucursal], [Cliente], [Monto], [Puntos Obtenidos], [Puntos Redimidos]) SELECT r.[A�o], 'Todos', 0, 'Todas', r.[Cliente], SUM(r.[Monto]), SUM(r.[Puntos Obtenidos]), SUM(r.[Puntos Redimidos]) FROM dbo.Resumen r WHERE r.[Monto] > 0 GROUP BY r.[A�o], r.[Cliente] ORDER BY SUM(r.[Monto]) DESC SELECT * FROM @SubResumen --------- Sample code ends ------------ I've done this using temporary tables and using regular tables to hold the data RESULTING from the Insert Into and it works just fine in SQL Query Analizer but it doesn't work in the SQL Report Designer. The other thing I tried was to create a store procedure with code and I get exactly the same. If I remove the Insert Into from the code it works fine in the SQL Report Designer but of course I don't get the desired results... Any Ideas??? Thanks, Julio.
4 L A T E S T R E P L I E S (Newest First)
juliom	Posted - 01/11/2005 : 10:42:14 Thanks.. I'll check it up... quote: Originally posted by juliom I'm evaluating Report Portal 1.5. I'm creating an SQL Report (againt SQLServer 2000 SP3a) that uses a Table variable to hold some temporary info. The idea is to load the data into this Table variable using an INSERT INTO. It works just fine in SQL Query Analizer, but if I paste the Transact SQL into the SQL Report Designer and try to test it I just don't get any results... This is an example of the Transact SQL I'm using... ----- Sample code begins --------- DECLARE @SubResumen Table ([RecNum] BIGINT identity, [A�o] INT, [Mes] NVARCHAR(20), [Mes2] INT, [Sucursal] NVARCHAR(40), [Cliente] NVARCHAR(10), [Monto] MONEY, [Puntos Obtenidos] MONEY, [Puntos Redimidos] MONEY ) INSERT INTO @SubResumen ([A�o], [Mes], [Mes2], [Sucursal], [Cliente], [Monto], [Puntos Obtenidos], [Puntos Redimidos]) SELECT r.[A�o], 'Todos', 0, 'Todas', r.[Cliente], SUM(r.[Monto]), SUM(r.[Puntos Obtenidos]), SUM(r.[Puntos Redimidos]) FROM dbo.Resumen r WHERE r.[Monto] > 0 GROUP BY r.[A�o], r.[Cliente] ORDER BY SUM(r.[Monto]) DESC SELECT * FROM @SubResumen --------- Sample code ends ------------ I've done this using temporary tables and using regular tables to hold the data RESULTING from the Insert Into and it works just fine in SQL Query Analizer but it doesn't work in the SQL Report Designer. The other thing I tried was to create a store procedure with code and I get exactly the same. If I remove the Insert Into from the code it works fine in the SQL Report Designer but of course I don't get the desired results... Any Ideas??? Thanks, Julio.
admin	Posted - 01/10/2005 : 20:25:01 As the administrator/developer you could change this rule by modifying the asp page. The logic resides in the CreateTable() function of sql\i_query.asp.
juliom	Posted - 01/10/2005 : 09:38:41 OK.. It makes sense.. but, don't you think this (the support for Insert/Update) should be something I ( as site administrator) should be able to configure??? Thanks again quote: Originally posted by juliom I'm evaluating Report Portal 1.5. I'm creating an SQL Report (againt SQLServer 2000 SP3a) that uses a Table variable to hold some temporary info. The idea is to load the data into this Table variable using an INSERT INTO. It works just fine in SQL Query Analizer, but if I paste the Transact SQL into the SQL Report Designer and try to test it I just don't get any results... This is an example of the Transact SQL I'm using... ----- Sample code begins --------- DECLARE @SubResumen Table ([RecNum] BIGINT identity, [A�o] INT, [Mes] NVARCHAR(20), [Mes2] INT, [Sucursal] NVARCHAR(40), [Cliente] NVARCHAR(10), [Monto] MONEY, [Puntos Obtenidos] MONEY, [Puntos Redimidos] MONEY ) INSERT INTO @SubResumen ([A�o], [Mes], [Mes2], [Sucursal], [Cliente], [Monto], [Puntos Obtenidos], [Puntos Redimidos]) SELECT r.[A�o], 'Todos', 0, 'Todas', r.[Cliente], SUM(r.[Monto]), SUM(r.[Puntos Obtenidos]), SUM(r.[Puntos Redimidos]) FROM dbo.Resumen r WHERE r.[Monto] > 0 GROUP BY r.[A�o], r.[Cliente] ORDER BY SUM(r.[Monto]) DESC SELECT * FROM @SubResumen --------- Sample code ends ------------ I've done this using temporary tables and using regular tables to hold the data RESULTING from the Insert Into and it works just fine in SQL Query Analizer but it doesn't work in the SQL Report Designer. The other thing I tried was to create a store procedure with code and I get exactly the same. If I remove the Insert Into from the code it works fine in the SQL Report Designer but of course I don't get the desired results... Any Ideas??? Thanks, Julio.
admin	Posted - 01/09/2005 : 21:09:53 ReportPortal does not allow INSERT/UPDATE statement in the SQL as a measure against SQL injection attack. Regards, Igor

ReportPortal