sgadson
Posts: 45
|
| Posted: 01/26/2006, 9:58 AM |
|
Hi,
Does anyone have any knowledge of how to do crosstab reports using codecharge studio 3.0?
Any information on this subject would be helpful.
Thanks
_________________
Shawn  |
 |
 |
peterr
Posts: 5971
|
| Posted: 01/26/2006, 11:06 AM |
|
There is no such feature. However, if you use a crosstab query as the data source then you may be able to use grid or report to display that crosstab data.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
sgadson
Posts: 45
|
| Posted: 01/26/2006, 1:00 PM |
|
Hi Peter,
I was able to use a stored procedure (saved as crosstab) to generate the crosstab query results. It requires that the run the following sql statement to get the results:
EXECUTE crosstab 'SELECT FunctionalAreaName FROM dbo.[qry_SPSM_ Metric_Report] GROUP BY FunctionalAreaName HAVING (FunctionalAreaName = "OTR")','avg(ReportedValue)','MetricName','SPSM_Metric_Details_tbl'
The above sql statement works in the sql query analyzer.
How do I integrate this in to CCS 3.0? I tried creating a grid using sql as the source. I then entered the sql statment above. when I try to view the results I get an error.
Incorrect Syntex near keywork 'End'
I'm not sure what this error means. Am I putting the Execute crosstab sql statement in the wrong place?
My next guess is that I should put the stored procedure crosstab in the grid. Then somehow enter in the parameter values that I have in the Execute statement above. If this is true, where do I enter in the parameter values?
As you can see I'm very confused, please help if you can.
_________________
Shawn |
|
|
|
peterr
Posts: 5971
|
| Posted: 01/26/2006, 2:18 PM |
|
Unfortunately I don't know the answer, but my guess would be as follows:
I suspect that the EXECUTE keyword is not exactly used as a part of an SQL statement (which start with SELECT) but a directive used to execute a stored procedure. And there is no need to disguise stored procedures as SQL statements, but just use that 'crosstab' stored procedure directly as a stored procedure.
So in CCS I would try setting the "Data Source Type" to "Procedure". Then you can open the "Data Source" property dialog to configure parameters for that stored procedure. CCS should also be able to retrieve the parameters from your stored procedure via the "Get Parameters" button. You may then only need to set them to Expression-type parameters and enter each of the parameter values.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Norbert
|
| Posted: 01/30/2006, 8:13 AM |
|
I suggest you start looking for a commercial component for that task.
Some time ago I found really good piece of software
www.contourcomponents.com/contourcube.htm
You may find it useful as well.
Best regards
Norbert
|
|
|
|
|
