• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 643
  • Last Modified:

SQL Server Reporting Services parameters, charts and functions

This is post contains three questions all dealing with SQL Server Reporting Services 2000.  

1.  I have a problem in RS 2000 where when I add another parameter to my current two it doesn't work.  I've tested the procedures in SQL and when declaring the datasets.  The two parameters I have are:
"Report_Date" and "Project_Name".  The one I add is called "Cap_Num".
I've modified all the existing datasets to use this new parameter in addition to the others, but when I preview the report, nothing happens when I select a value from "Report_Date" and "Cap_Num".  I only get results when I choose the "Project_Name" value and "Report_Date".  On the web when I run the report and I try to select just based on the "Cap_Num" and "Report_Date" it says to choose a "Project_Name".  I've gone through all of my datasets and queries and have put the proper conditions for each:

WHERE     (REPORTDATE = @Months) AND (CAPITALPROJECTNUMBER = @Cap) OR
                      (REPORTDATE = @Months) AND (PROJECTNAME = @Projects)</

Like I said I tested all of the parameters by themselves and the work, but when they are all integrated, it is still looking for the project name to be selected.  Any ideas?

This is what the code looks like:
FROM         FRA
WHERE     (REPORTDATE = @Months) AND (CAPITALPROJECTNUMBER = @Cap) OR
                      (REPORTDATE = @Months) AND (PROJECTNAME = @Projects)</CommandText>
        <QueryParameters>
          <QueryParameter Name="@Months">
            <Value>=Parameters!Months.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@Cap">
            <Value>=Parameters!Cap.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@Projects">
            <Value>=Parameters!Projects.Value</Value>
          </QueryParameter>
        </QueryParameters>
      </Query>

2.  I have a table and in the table detail section it will never have more than 12 rows;therefore, I want to have the numbers 1-12 listed sequentially on each row.  For instance:

                     Row   Data

                      1         record1

                      2         record2

                      3         record3

.............................through 12.  I've tried all kinds of syntax for the function, but can't seem to get it to work. Any help is greatly appreciated.

3.  I'm having trouble with creating column charts.  Any good referrences out there?
0
fusionfx2000
Asked:
fusionfx2000
  • 2
  • 2
1 Solution
 
simon_kirkCommented:
1. Can the Cap_Num parameter be null or blank?  If so you'll need to set in the parameter that it can accept NULL or Blank Values.  Your Report parameters need to be called the same as your dataset/SP parameters.

2. Try one of the following functions.  BOL will have more on the context.
   - RowNumber(scope)
   - CountRows(scope)
   - InScope(scope)
   - RunningValue using Count/CountDistinct as aggregate function

3. Try:
    Creating a Chart with SQL 2000 Reporting Services
    http://www.aspfree.com/c/a/MS-SQL-Server/Creating-a-Chart-with-SQL-2000-Reporting-Services/

0
 
fusionfx2000Author Commented:
Sorry.  The two parameters I have are:
"Months" and "Projects".  The one I add is called "Cap".
I've modified all the existing datasets to use this new parameter in addition to the others, but when I preview the report, nothing happens when I select a value from "Months" and "Cap".  I only get results when I choose the "Projects" value and "Months".  On the web when I run the report and I try to select just based on the "Cap" and "Report_Date" it says to choose a "Projects".  I've gone through all of my datasets and queries and have put the proper conditions for each:
0
 
simon_kirkCommented:
"Can the Cap_Num parameter be null or blank?  If so you'll need to set in the parameter that it can accept NULL or Blank Values. "

This will apply to the other 2 parameters as well.
0
 
fusionfx2000Author Commented:
It can be null and I've set it to null and blank possible values.  This is not the case for the others.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now