Missing fields

Question

0.00/5 (No votes)

See more:

Hi guys,
i am workin on a .NET application that rates team leader or supervisor based on a set questions presented in a questionnaire form. Basically ther about 40 questions and im supposed to come with averages for each question and then be able to generate a report. The user has to supply the team leader name and from this a report is generated. The report is divided into different categories of people who have rated the team leader so we have personal accountability, acting with intergrity, building relationships etc. From this i decided that using a stored procedure would be an ideal solution as it can accept parameters and i cud use this to filter the data depending on who is being rated so the procedure was created as follows

CREATE PROCEDURE dbo.spFeedbackReport
@leader nchar(10) AS
BEGIN
    /* AvgQ26 and AvgQ30 are views that i created */
    SELECT (dbo.AvgQ26.Q26 + dbo.AvgQ30.Q30) / 2 AS [Personal Accountability]
    FROM dbo.AvgQ26 CROSS JOIN 
dbo.AvgQ30
    WHERE (dbo.AvgQ26.LeaderID = @leader) AND (AvgQ26.Rater = 'Line Manager')
END
BEGIN
    SELECT AVG(AvgQ11.Q11) AS [Acting with Intergrity]
    FROM AvgQ11
    WHERE (AvgQ11.LeaderID = @leader) AND (AvgQ11.Rater = 'Line Manager')
END

the procedure goes on for another couple of select statements but the two are sufficent to show my problem. So when i create a report using microsoft report viewer im presented with a wizard where i choose the source nd i choose spFeedbackReport but only personal accountability is listed the rest of the select statements are simply not there. I tested the procedure directly from sql express (im using sql server express 2005) and it returns all of them so what im I doing wrong?

Posted 2-Oct-10 18:09pm

2gizzo

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2010-10-02T19:14:00

Solution 1

Well, the datasource picks up the first table of the stored procedure.
SSRS/Reports would not take all the tables returned into account by itself.

For your scenario, either have separate queries/SP for each OR, work on it using views and Scalar Valued Function. Construct your SP such that you have all the needed info in one query.

Posted 2-Oct-10 19:14pm

Sandeep Mewara

Comments

2gizzo 3-Oct-10 4:13am

Initially i had seperate sproc but it seems tht report viewer can only let me choose one SP/table/view per report thats when i decided to create one sproc for all of them. Using a view wud be great solution but they cant accept parameters to be passed to them at runtime. Can u give me an example of how i can write a single query that would cater for both statements?

Sandeep Mewara 3-Oct-10 4:31am

Read on Scalar Valued Functions. That should help you out.

2gizzo 4-Oct-10 12:49pm

Re-implemeted the select statement as scalar function and then i call them from the sproc but its still giving me the same results, that is only the first function (in this case) is being listed as column 1 the rest still cant be seen.

Sandeep Mewara 4-Oct-10 13:30pm

Not sure of what you have done. Finally SP should have only 1 query. Query selects all the needed values via Scalar valued function. Something like:
Select A1(@id) as 'A1', A2(@id) as 'A2',B1(@id) as 'B1',B2(@id) as 'B2',C1(@id) as 'C1', C2(@id) as 'C2'
FROM XYZ

2gizzo 4-Oct-10 15:04pm

Actually i think I misunderstood you. When i created the scalar functions they were doin the exact same thing as the sproc before but now i have sorted it out tho not exactly using scalar functions but the logic is exactly the same as you have written above something lyk this
<pre>
SELECT (dbo.AvgQ26.Q26 + dbo.AvgQ2) / 2 AS [integrity], (dbo.AvgQ13.Q13 + dbo.AvgQ29.Q29)/ 2 [Leadership]
FROM dbo.AvgQ25 CROSS JOIN dbo.AvgQ13 CROSS JOIN dbo.AvgQ26 /*the rest of views that i joined*/
WHERE (dbo.AvgQ26.LeaderID = @leader) AND (dbo.AvgQ26.Rater = 'Team Leader')
</pre>
but thanks for ur assistance i think im goin 2 rewrite so that my sproc use scalar functions lyk u have done because it looks much cleaner than not using them.