| Author |
Topic |
|
dotosu
Starting Member
42 Posts |
 Posted - 2004-12-05 : 17:48:09
|
| I have this dynamic query in the form of a stored proc. I am calling this a dynamic query because I do not know beforehand how many columns will be returned by the query e.g. I am trying to find out a set of data for all members in a survey (which can have max. 7 members). The number of members can vary, so I can have between 2 and 7 columns for the data. The names of the columns will be the names of the members.Now I want to use the results of this stored proc as a dataset for a line chart. To do that I need to specify the number of series beforehand which I am unable to do. Also the names of the columns will be different each time and therefore I cannot specify the fieldnames. How do I workaround this? It is important that I show this data in a single line chart with each line representing a member. Can someone please help me? I would really appreciate it.Thanks,dotosu |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-05 : 18:17:25
|
quote:
I have this dynamic query in the form of a stored proc.
Uh-oh.quote:
I am calling this a dynamic query because I do not know beforehand how many columns will be returned by the query
Not a good sign.quote:
The number of members can vary, so I can have between 2 and 7 columns for the data. The names of the columns will be the names of the members.
Also not a good sign.quote:
Also the names of the columns will be different each time and therefore I cannot specify the fieldnames.
This is a REALLY BAD sign.quote:
How do I workaround this?
I think you should start at the beginning with the stored procedure. If you're using dynamic SQL, you're pretty much undermining the entire reason for using a stored procedure. It sounds as if this thing could return anything at all, you literally have no idea what it's going to return. Until that issue is resolved I don't see how you'll be able to proceed at all. It would help if you could post the code, and explain what kind of results you're trying to get. I think if you whip this part into shape the other problems will evaporate or at least be much easier to manage. |
 |
|
|
dotosu
Starting Member
42 Posts |
Posted - 2004-12-05 : 18:44:04
|
| I know this is probably a bad way to code but I did this for a lack of knowing any other way to do this. Suggestions are welcome and would be appreciated.I first try to find out the PKs of the members using something like this where the questionnaireid is passed as a stored proc parameterDECLARE @keyssql AS varchar(1000)SET @keyssql = 'INSERT INTO #memberData SELECT ID, REPLACE(Lastname + Firstname, '' '', '''') AS FullName FROM Member WHERE questionnaireID =' + CAST(@varquestionnaireID AS nvarchar(10))Then I try to loop through the keys to generate a queryWHILE @key IS NOT NULLBEGIN SELECT @fullname = fullname FROM #memberData WHERE keyvalue = @key SET @sqlSelectClause = @sqlSelectClause + ', ' + @fullname SET @sqlInnerJoin = @sqlInnerJoin + ' INNER JOIN ' + @NEWLINE + '(' + @NEWLINE + 'SELECT GroupID, Sum(answer) AS ' + @fullname + @NEWLINE + 'FROM Answer ' + @NEWLINE + 'INNER JOIN Question ON Question.ID = Answer.QuestionID ' + @NEWLINE + 'WHERE memberID = ' + CAST(@key AS nvarchar(10)) + @NEWLINE + 'GROUP BY GroupID' + @NEWLINE + ') T' + CAST(@memberCount AS nvarchar(10)) + ' ON Group.ID = T' + CAST(@memberCount AS nvarchar(10)) + '.GroupID' SELECT @key = MIN(keyvalue) FROM #memberData WHERE keyvalue > @key SET @memberCount = @memberCount + 1ENDThen I execute this query. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-05 : 18:50:42
|
quote:
Then I try to loop through the keys to generate a queryWHILE @key IS NOT NULL
That's gonna need some more detail. You've also got a couple of variables in there that are not defined in the code. Will probably need a more detailed explanation for why the dynamic SQL is used...except for those missing variables, everything else looks like it could be done as a regular query. And a sample of the actual SQL it outputs wouldn't hurt either. |
|
|
|
dotosu
Starting Member
42 Posts |
Posted - 2004-12-05 : 19:02:45
|
| Here is a sample outputShortName JohnDoe JaneSmith JoeSmith JaneDoe--------------------------------------------------------------------Satisfaction -5 0 -3 4 Quality 7 4 11 10 Service 3 5 -6 10 Location 3 10 2 15 Friendly Employees 5 6 1 17 Shortname is a group created for a certain type of questions. There are four members for this particular questionnaire. Each question will have answers (numbers) and these answers are then added for each group of questions and then show up in the result query as above.The keyvalues are the IDs (primary keys) of the members. |
|
|
|
dotosu
Starting Member
42 Posts |
Posted - 2004-12-05 : 20:01:53
|
| how can this be done with a simple sql query? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-05 : 20:05:50
|
| Is there any reason you can't use a cross-tab for this? I think a matrix-type report might work, if not you can use one of these cross-tab techniques as the basis for your query:http://www.sqlteam.com/searchresults.asp?SearchTerms=cross+tabWhich is probably what you're already doing now that I think about it.It might be worth thinking about always having 7 columns/members returned, even if not all of them actually have data for a particular set of parameters. Solidifying the number of columns, and possibly their names, will at least let you bind the chart properly. |
|
|
|
dotosu
Starting Member
42 Posts |
Posted - 2004-12-05 : 20:12:58
|
| The problem is that I need to name the series in the line chart according to the name of the member. Also, now that I think about it, it may be possible that the number of columns may exceed 7.Is there a way to code for this to generate the RDL for the chart dynamically so that I can include the series according to what is returned by the dataset? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-05 : 20:53:49
|
| I would imagine there is a way, but I don't know enough about Reporting Services to know how. I know I've done dynamic-type charts similar to this in Excel and Access, so it's likely RS can do it too. |
|
|
|
|
chart with dynamic query