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

SQL Report Services 2000. Problem with Advance Parameters and Unions

I am trying to solve a problem that I am having in the AdventureWorks DB in SQL.  In SQL Reporting Services 2000, I can have a parameter setup so it can display 'All Categories' from a dropdown list.  I am trying to create this in my own database.  The problem I think I get is that the Category query from AdventureWorks comes from its own table.  My Category is divised from the same table as the DataDetail therefore the UNION will be screwed up.  How do I create the same situation from AdventureWorks into my database if both queries are coming from the same source?  See code for AdventureWorks query below.


DataDetail:

SELECT     *
FROM         vProductProfitability
WHERE     (CategoryKey = @Category) OR
                      (@Category = - 1)

Category:

SELECT DISTINCT ProductCategoryKey AS CategoryKey, ProductCategoryName AS Category
FROM         DimProductCategory
UNION
SELECT     - 1, 'All Categories'

0
chippyles
Asked:
chippyles
  • 8
  • 8
1 Solution
 
simon_kirkCommented:
This might appear a stupid question ;-) but can't you just create a Categories table to select your parameters list from, and then use the Category ID in your table ?  This would then allow changes to the Category name without having to do updates on your main table.  If not, you could perhaps generate a temporary table that groups the categories from the main table?

CategoryID      Category
------------------------------
1                    Category 1
2                    Category 2
..                    ..

If you need to have the Category selected from the main table then you just need to create two datasets within your report:

ParameterListDataset:

SELECT      Category
FROM         vProductProfitability
GROUP BY   Category

DataListDataset:

SELECT      vProductProfitability.*, Category AS Link
FROM        vProductProfitability
WHERE      (Link = @MyParameter)

- Under Report Parameters in the report, select the MyParameter
- Set Available Values to FROM QUERY
- Dataset = "ParameterListDataset"
- Value Field = "Link"
- Label Field = "Link"

Hope this helps and works!




0
 
chippylesAuthor Commented:
That is how I do it now.  The problem is I need to have your solution (not the temp table) have the drop down list to display 'All Categories' (WHERE @Category = -1).  This will make it so I can select the 'All Categories' and display everything.  

So your solution works but it is not everything I am looking.  I hope you can help me.
0
 
simon_kirkCommented:
Ah, I was wondering why you were using a Union query!

Try this link : http://blogs.msdn.com/chrishays/archive/2004/07/27.aspx

From reading about, it seems that a Union query is the only way to get the additional parameter to select all the data.  To be honest, I would still go down the route of creating a seperate Categories table like the Adventure Works one uses.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
chippylesAuthor Commented:
I will give you the points if you can give me the SQL Query Code on how to take the colum I need in the dataset to make a temp table.  I will then schedule it every 30 mins or so since my categories can be added at any time.
0
 
simon_kirkCommented:
Probably best way is to create the table first, and then have your job insert and delete the categories:

CREATE TABLE tblTEMPCategory
(Category nvarchar(100))

------
DELETE FROM tblTEMPCategory

INSERT INTO tblTEMPCategory
SELECT YourCategory
FROM YourTable
GROUPBY YourCategory
-----

Note:  Depending on the length of the Category entries, you may want to increase the NVARCHAR(100) setting to something higher
E.g   NVARCHAR(200)
0
 
chippylesAuthor Commented:
Thanks for the code.  I have created the job and it deletes / create data forme.  Is there any way to have the 'ALL CATEGORIES' selection in the drop down list without having a UNION?  

If so, what is the code to do this?  

If not, how is my below code not working properly?

This is my Data Set for the Report:

SELECT     vFELASTM.*, SampID
FROM         vFELASTM
WHERE     (SampID = @SAMPID) OR
                      (@SAMPID = - 1)

This is my Category Set:

SELECT DISTINCT SampleID
FROM         FELASTMSID
UNION
SELECT     - 1, 'All Samples'
0
 
simon_kirkCommented:
Try this code:

Data Set:
   SELECT vFELASTM.*, SampID
   FROM vFELASTM
   WHERE (SampID = @SAMPID OR @SAMPID IS NULL)

Category Set:
   SELECT SampleID AS ID, SampleID AS Label
   FROM FELASTMSID
   GROUP BY SampleID
   UNION
   SELECT NULL AS ID, 'All Samples' AS Label

On your Report Parameters:
   Available Values = From Query
   DataSet = CategorySet
   ValueField = ID
   LabelField = Label

Make sure you have the "Allow Null Value" ticked
0
 
chippylesAuthor Commented:
Excellent.  Thanks big time.  Will I be able to have multiple categories like this for one report?
0
 
simon_kirkCommented:
No problem.  I'm still on a learning curve with Reporting Services myself ;o)

Not quite sure what you mean by your question.  'multiple categories', as in more than one category parameter?  If that's what you mean, then yes, you can use the Category Set for another parameter, just ensure you add another OR statement into the WHERE clause of your Data Set.  i.e. WHERE (SampID = @SAMPID OR @SAMPID2 OR @SAMPID IS NULL)
0
 
chippylesAuthor Commented:

Can you test if this works for you.  It is not working for me.  (Multiple Categories)  I am trying to make it convienent for the end users to search what they are looking for.


Data Set:

SELECT     vFELASTM.*, SampID AS Expr1, Heat AS Expr2
FROM         vFELASTM
WHERE     (SampID = @SAMPID) AND (Heat = @HEAT) OR
                      (@SAMPID IS NULL) AND (@HEAT IS NULL)

Category 1:

SELECT SampleID AS ID, SampleID AS Label
FROM FELASTMSID
GROUP BY SampleID
UNION
SELECT NULL AS ID, 'All Samples' AS Label

Category 2:

SELECT Heat AS ID, Heat AS Label
FROM FELASTMHEAT
GROUP BY Heat
UNION
SELECT NULL AS ID, 'All Heat' AS Label
0
 
simon_kirkCommented:
I think its the WHERE clause in your Data Set code.  Should be:

WHERE     (SampID = @SAMPID OR @SAMPID IS NULL) AND (Heat = @HEAT OR @HEAT IS NULL)
0
 
chippylesAuthor Commented:
It isn't working.  Your WHERE clause still returned everything.  It didn't filter the data.
0
 
simon_kirkCommented:
Works fine on mine.  

Check your Report Parameters are using the Category 1 and Category 2 Data Sets.
Also try removing the Expr1 and Expr2 from your SELECT statement in the Data Set.
0
 
chippylesAuthor Commented:
Works good!!  Thanks for your help.  Do you have an email address so we can keep in touch about our progress on this reporting?
0
 
simon_kirkCommented:
Glad you finally got it working!  Due to the spam issues (i've suffered from this before), I don't like posting my e-mail address onto webistes or newsgroups, but I'm a big user now of Experts Exchange so post in DB Reporting if you get further problems.  My knowledge of Reporting Services is somewhat limited, so if I don't know straight away I'll endeavour to find out, as it increases my understanding of the software.

Happy reporting...
0
 
chippylesAuthor Commented:
thanks!!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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