Solved

SQL Report Services 2000.  Problem with Advance Parameters and Unions

Posted on 2004-10-19
16
374 Views
Last Modified: 2010-05-18
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
Comment
Question by:chippyles
  • 8
  • 8
16 Comments
 
LVL 14

Expert Comment

by:simon_kirk
ID: 12356126
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
 

Author Comment

by:chippyles
ID: 12357056
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
 
LVL 14

Expert Comment

by:simon_kirk
ID: 12357648
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
 

Author Comment

by:chippyles
ID: 12357732
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
 
LVL 14

Expert Comment

by:simon_kirk
ID: 12358296
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
 

Author Comment

by:chippyles
ID: 12359788
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
 
LVL 14

Expert Comment

by:simon_kirk
ID: 12368240
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
 

Author Comment

by:chippyles
ID: 12368389
Excellent.  Thanks big time.  Will I be able to have multiple categories like this for one report?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 14

Expert Comment

by:simon_kirk
ID: 12368549
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
 

Author Comment

by:chippyles
ID: 12368694

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
 
LVL 14

Expert Comment

by:simon_kirk
ID: 12368789
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
 

Author Comment

by:chippyles
ID: 12368845
It isn't working.  Your WHERE clause still returned everything.  It didn't filter the data.
0
 
LVL 14

Accepted Solution

by:
simon_kirk earned 500 total points
ID: 12369269
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
 

Author Comment

by:chippyles
ID: 12369601
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
 
LVL 14

Expert Comment

by:simon_kirk
ID: 12369684
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
 

Author Comment

by:chippyles
ID: 12369704
thanks!!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now