November 8, 2012 at 11:03 am
I am building a report where the table may or may not have a specific set of values. Right now, if that value doesn't show up in the query it doesn't show up in the table. However even though it may not be there, I'd like to have it in the report just to show that it has all zero's for example.
Thanks in advance for your help!
Steve
November 8, 2012 at 11:10 am
Do you mean for example your showing sales of products last month. and If no Red bikes were sold its not showing up on the report, but you would like it to and display a value of zero?
If this is your question, then you need to modify your query to pull all products whether they were sold or not.
Or you mean Your query returns Red Bikes, with a NULL in the Quantity field?
If this is your question then in your report do this =iif(IsNothing(Fields!MyQuantity.Value),0,Fields!MyQuantity.Value)
November 8, 2012 at 11:22 am
It actually just won't pull 'Red Bikes' period if none were sold. But the next month it might, but I need to be able to see the zero's or dashes to show there was no activity.
November 8, 2012 at 12:47 pm
Right, you need to modify your query to always return the missing values even if there were none sold.
The report doesn't or shouldn't know whats missing, thats up to the query.
November 8, 2012 at 1:20 pm
I'm not the best with SQL, (just started not very long ago). How would I SELECT values that don't exist?
November 8, 2012 at 1:50 pm
Short form: What you're looking for is to adjust your query with what's called an OUTER JOIN, in this case most likely a LEFT Join.
Basically, your select query should base off the primary product list as its main source, and then LEFT join to the sales tables. If you can provide some DDL and sample data (see the first link in my signature) we can walk you through the code.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2012 at 2:32 pm
here's an example that kind of shows what we are talking about...you have to select from all possible values, and join that to what was sold.
run this and take a look a tthe three queries at the bottom; the last returns all rows, and you can isnull(QTY,0) to display zero if desired.
CREATE TABLE ALLPRODUCTS(
PRODUCTID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DESCRIP VARCHAR(30)
)
INSERT INTO ALLPRODUCTS(DESCRIP)
SELECT 'APPLES' UNION
SELECT 'ORANGES' UNION
SELECT 'BANANAS' UNION
SELECT 'GRAPES' UNION
SELECT 'CHERRIES' UNION
SELECT 'KIWI'
CREATE TABLE sales(
saleid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
PRODUCTID int,
PRICE MONEY,
QTY INT
)
insert into sales(PRODUCTID,PRICE,QTY) values (1,.50,12)
select * from SALES INNER JOIN ALLPRODUCTS on SALES.PRODUCTID=ALLPRODUCTS.PRODUCTID
select * from SALES LEFT OUTER JOIN ALLPRODUCTS on SALES.PRODUCTID=ALLPRODUCTS.PRODUCTID
select * from ALLPRODUCTS LEFT OUTER JOIN SALES on SALES.PRODUCTID=ALLPRODUCTS.PRODUCTID
Lowell
November 9, 2012 at 9:00 am
Most excellent Lowell, and thanks to everyone else aswell, that does the trick!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply