April 28, 2006 at 10:22 am
I'm new to SSRS and have the following problem.
When I run the following query in Query Analyzer the following calculated columns (qty,quantity,transfers_out delivered,transfers_intransit) contain both integer and NULL values.
However when I connect to my SP from SSRS none of the calculated columns are displayed in my report, the other columns all appear.
I have also pasted the t-sql into the generic query designer and it still doesn't show when I preview the report.
I am using a table to display my results.
Has anybody encountered a similar problem ?
Any workaround appreciated.
Here's my sql code.
select d.department,genre.genre,
(select sum(receiveditems.qty) from receiveditems
inner join receiving on receiveditems.receivingid = receiving.receivingno and receiveditems.unitid = ts.unitid
and (receiving.recdate between @FromDate and @ToDate) )as qty,
(Select sum(tod.quantity)from tblorderdetails tod where tod.unitid = ts.unitid
and (tod.dateadded between @FromDate and @ToDate))as quantity,
(Select sum(transferstockreportdata.qty_to_return)from transferstockreportdata where
transferstockreportdata.unitid = ts.unitid
and(transferstockreportdata.date_returned between @FromDate and @ToDate))as transfers_out,
(Select sum(transferstockreportdata.qtyreceived)from transferstockreportdata where
transferstockreportdata.unitid = ts.unitid and transferstockreportdata.InTransit = 1
and(transferstockreportdata.date_returned between @FromDate and @ToDate))as delivered,
(Select sum(transferstockreportdata.qty_to_return)from transferstockreportdata where
transferstockreportdata.unitid = ts.unitid and transferstockreportdata.InTransit = 0
and(transferstockreportdata.date_returned between @FromDate and @ToDate))as transfers_intransit,ts.unitid as unit_instock
from tblstock ts
inner join tbllocation tl on tl.locationid = ts.locationid
inner join av_units av on av.unitid = ts.unitid
inner join department d on av.departmentid = d.departmentid
inner join ARTISTS ON AV.ArtistName = ARTISTS.ArtistID
INNER JOIN GENRE ON ARTISTS.GenreID1 = GENRE.GenreID
where ts.unitid > 1000 and tl.ildeleted = 0 and ts.quantity > 0
April 29, 2006 at 5:23 am
Just as an alternate create a stored procedure and populate the values of the above sql code into a temp table and retrieve the data from the table and map the columns to ur reporting fields and try. I am not sure if the parameters are getting properly set in the formula columns.
Prasad Bhogadi
www.inforaise.com
May 2, 2006 at 8:57 am
Thanks for the reply.
I've changed my sp to now insert the data into a temporary table, however, when I run the preview, same results as before. Displays database fields but not calculated fields from temptable.
Looks like I'll have to find another way of viewing this info.
May 2, 2006 at 9:05 am
Try using ISNULL(Calcfield,0) as CalcField while populating and see if it helps.
Prasad Bhogadi
www.inforaise.com
May 2, 2006 at 9:15 am
Already tried it, still makes no differance.
Cheers anyway.
May 2, 2006 at 9:30 am
As a last trial can u check hardwiring the values of your input data parameters @FromDate and @ToDate in your sp and check running it from your reporting environment, I know it may sound little inpractical but still just to give it a shot.
Prasad Bhogadi
www.inforaise.com
May 2, 2006 at 10:24 am
That now works for some reason, not sure I know why though. Any ideas on what's happening ? Is there another way I can pass in my date range ?
May 2, 2006 at 10:30 am
Just to make sure, check if you have defined two report parameters as @FromDate and @ToDate and on the dataset definition on data tab click on the elipse adcent to Dataset dropdown and click on parameters tab and map the stored procedure parameters to your input report parameters as values, click ok and run the sp from your data tab and check if its working fine.
I am not sure how you are passing the paramters to your stored procedure.
Prasad Bhogadi
www.inforaise.com
May 2, 2006 at 10:46 am
Excellent, That now seems to be working.
I've passed date range parameters to a SP before using the report / report paramaters tab and was using it in this case. I didn't use the elipse adcent to add Dataset parameter values before so I guess this must have made the differance.
Thanks for all the help, appreciated
Carl
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply