How to use SP with Pivot concept to make data Source

[nicegaurav]

Hi,
I have made Data source with SP that time these SPs are return normal data no calculation being performed and the data source with the report is working fine.
Now the scenario is little different, I am using Pivot concept in a SP, have to convert data value into columns and it is working fine on query analyzer and created SP for this. returning the desired result, the but when attach that SP to data source it gives me error. like "Incorrect syntax near ')'."

Is there any constraint with the stimulsoft report ??

Please let me know..ASAP

Thanks

[Alex K.]

Hello,

Can you please send us a sample which reproduces the issue for analysis.

Thank you.

[nicegaurav]

Sending you the SP script
"DECLARE @cmd varchar(max), @columns varchar(max)
SET @columns = ''
SELECT @columns = @columns + '['+Description+'],' from tblRiderType
ORDER BY [Description] ASC
SELECT @columns = substring(@columns, 0, len(@columns))
SET @cmd = '
SELECT * FROM
( SELECT A.TypeID, A.description from tblRiderType A
LEFT OUTER JOIN tblFeature FT on FT.RiderTypeCode = A.TypeID
WHERE FT.ProductID=' + Convert( varchar(31),@ProductID) + '
) pt
PIVOT
( count(TypeID) for Description in ('+@columns+') )PIVOTED_DATA'
exec (@cmd)"
Please use this script and attach this with datasource, it will reproduce the error.
Does stimulsoft support this kind of query ?
waiting for ur reply..

[HighAley]

Hello.

There should be no problem with such query.
Could you send us a sample database to test it.

Thank you.

[nicegaurav]

hi
please tell me ...
Does stimulsoft support this kind of query ?
it's urgent...

[HighAley]

Hello.

Does stimulsoft support this kind of query ?

Yes. As I wrote before our product support such SP.
Please, check your SP on your SQL server.

Thank you.

[nicegaurav]

Hi,
I have checked that SP but didn't find any error, Break that SP two parts and compile it.
Without Pivot, used that SP in DataSource, it is working fine but when i use with Pivot it gives me error "Incorrect syntax neat )".
I have crossed check that SP on SQL server it gives me proper result...

Please do the needful..

[Alex K.]

Hello,

Please send us your report with test database which reproduces the issue for analysis.

Thank you.

[nicegaurav]

Hi,
I am attaching the Report file as well as Data base back up.
In this report create a data source named as DataSource3 and try to connect with SP named as [GetPivot_Info] but showing me the error. please have a look.

Thanks

[nicegaurav]

Please find the attached file..