question

ChuckSalerno-4276 avatar image
0 Votes"
ChuckSalerno-4276 asked ZoeHui-MSFT edited

Reporting on Data that isn't there

I have a report that will report the data from a sql script but need a way to add data to the query when the date parameters go beyond present data. For example, If I query a date range of 9/1/2020 to 11/30/2020 but there is no rows past 10/31/2020 I still need to show the November data without values on the report?

sql-server-generalsql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RusselLoski-0634 avatar image
0 Votes"
RusselLoski-0634 answered

Your source needs some kind of table that will provide the month.

 declare @startdate date = '20201101'; -- This should be first day of month
 declare @enddate  date = getdate();
    
 with dt as (
 SELECT DATEADD(MONTH,number,@startdate) as MonthStart
 , DATEADD(MONTH,number + 1,@startdate)  as MonthNext
 FROM master.dbo.spt_values
 WHERE DATEADD(MONTH,number,@startdate) <= @enddate
 and type = 'P'
 )
 select COALESCE(dt,MonthStart) As dt
 FROM dt
 left outer join CurrentQuery q
 on q.dt >= dt.MonthStart and q.dt < dt.MonthNext;


This should add one row for each day and if a month does not have any rows, it will create one row for the first of the month with all of the other columns blank.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChuckSalerno-4276 avatar image
0 Votes"
ChuckSalerno-4276 answered OlafHelper-2800 commented

I did think of that but my challenge is that I'm using this against the OSI PI SQL Driver and it doesnt support and date functions like DateAdd and the rest. I did however use this approach to build a dataset but still looking for a way to join the datasets within SSRS.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

this against the OSI PI SQL Driver and it doesnt support

Then why do you ask this in a Microsoft forum for SQL Server? Post your question to a more related forum.



0 Votes 0 ·
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @ChuckSalerno-4276,

Do you mean that when you choose the date parameter from 9/1/2020 to 11/30/2020, even if there is no data in November it will still show null value in report?

I tested locally, there is no value in March and the report is shown like below.

83483-screenshot-2021-04-01-105514.jpg

If you have set the parameter date range, the date will still show in report.

If I misunderstand your needs, please incorrect me. (Screenshot of the design sample is welcome to show your requirement.)


Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChuckSalerno-4276 avatar image
0 Votes"
ChuckSalerno-4276 answered ZoeHui-MSFT edited

The is the requirement. I have two datasets in my report that I need to "Join" some how without using a Lookup() as I don't have a unique value in the "Monday" dataset. Is there a way to Join two datasets?. ds1 contains the transactional data I need to report and ds2 will contain all of the Monday dates within date range requested.


ds1
MonthHeading
RowDate
MachineNumber
Value


ds2
MondayDate


Joined d3
MonthHeading
RowDate
MachineNumber
Value
MondayDate

Report Format 9/1/2020 - 11/30/2020


               September                                                               October                                       November
             7          14        21       28 |  Summary            5      12     19     26 | Summary          2     9    16    23   30  | Summary

Machine
D45 1 1 1 1 2
Y45 1 1 1 1
G23 1 1
V45 1 1 1 3 1 1 1 3



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

From SSRS side, if you want to join two dataset, we could only use lookup function. If you have any update, don't forget to tell us.

0 Votes 0 ·