Solved

Crystal reports Cross Tab - Display dates even if no data returned.

Posted on 2016-10-05
20
54 Views
Last Modified: 2016-10-12
Hi,

I am using Crystal reports 2008 to report on the number of open Problems grouped by the month they were raised.

As we do not have open problems for every month, the cross tab only shows months where data is present. ie:
Current report
Is there anyway to get the cross tab to fill in the gaps, so a month is listed even though no data has been returned?
0
Comment
Question by:Ian Chadburn
  • 8
  • 6
  • 6
20 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 41830113
You can create a temp table with all the dates in a period and left join it with your data. What is your database type ?
0
 

Author Comment

by:Ian Chadburn
ID: 41830128
Sorry should have mentioned this in my main post.

I am connecting to a MSSQL DB via ODBC and only have read only access to the DB.
0
 
LVL 18

Expert Comment

by:vasto
ID: 41830143
Can you create a command ? Actually the whole report will need to be switched to use a cpmmand
0
 

Author Comment

by:Ian Chadburn
ID: 41830179
I am not overly familiar with commands, so I don't know where to start with writing one.

I have copied the SQL out of the report. Could you help me rewrite it to do what I need?

  SELECT "Problem"."Status", "Problem"."CreatedDateTime", "Problem"."ProblemID"
 FROM   "Cherwell_Exeter"."dbo"."Problem" "Problem"
 WHERE   NOT ("Problem"."Status"='Closed' OR "Problem"."Status"='Completed PIR')
0
 
LVL 18

Expert Comment

by:vasto
ID: 41830285
Which is your date column ?
0
 

Author Comment

by:Ian Chadburn
ID: 41831320
The Date column is "Problem"."CreatedDateTime"
0
 
LVL 34

Expert Comment

by:James0628
ID: 41831482
Are you using any kind of date range, or just including all dates in the Problem table?

 If you're just including all dates, I assume that the earliest date would be the earliest date found in the table, or do you want to use some other starting date?  Would the ending date be the last date found in the table, or today, or something else?

 James
0
 

Author Comment

by:Ian Chadburn
ID: 41831489
The oldest date would be the oldest problem where the status is not one of 'Closed' or 'Completed PIR'

WHERE   NOT ("Problem"."Status"='Closed' OR "Problem"."Status"='Completed PIR') 

Open in new window


and the last date would be the last day of the previous month.
0
 
LVL 18

Accepted Solution

by:
vasto earned 400 total points
ID: 41831877
Try to create a command using this code:
    DECLARE @FromDate DATE, @ToDate DATE;
    SELECT @FromDate=MIN(CreatedDateTime) , @ToDate=MAX(@ToDate) FROM Cherwell_Exeter.dbo.Problem
    DECLARE @DateTable TABLE (DateValue DATETIME);
    
    WITH DatesTable(CurrentDate) AS
    (
        SELECT @FromDate AS datetime
        UNION ALL
        SELECT DATEADD(DAY, 1, CurrentDate) FROM DatesTable WHERE CurrentDate < @ToDate
    )
		
	INSERT INTO @DateTable (DateValue) SELECT CurrentDate FROM DatesTable OPTION (MAXRECURSION 0)
    
    SELECT d.DateValue, p.[Status], p.ProblemID
    FROM #Data d LEFT JOIN  Cherwell_Exeter.dbo.Problem p ON d.DateValue = p.CreatedDateTime AND p.Status NOT IN ('Closed','Completed PIR')

Open in new window


It should get all the dates.
0
 

Author Comment

by:Ian Chadburn
ID: 41832044
Brilliant, huge thanks for this, I will check this out on Wednesday when I am back in the office and let you know how it goes!
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Assisted Solution

by:James0628
James0628 earned 100 total points
ID: 41832685
vasto,

 You've got @ToDate=MAX(@ToDate), which doesn't seem to make much sense.  :-)  I'm guessing that you meant MAX(CreatedDateTime), but instead of coming from the table, shouldn't @ToDate just be the last day of the previous month (as mentioned in Ian's post)?

 And shouldn't there be a Where that checks Status, to get the MIN date for the desired statuses?

 Also, you could change the final Select to use CreatedDateTime as the column name, so the field name in the report won't change.

SELECT d.DateValue AS CreatedDateTime, p.[Status], p.ProblemID


 Just trying to help.  :-)

 James
0
 
LVL 18

Expert Comment

by:vasto
ID: 41832838
I am trying to show how to generate a set of dates and left join them with the existing data. I think the example above is good enough and demonstrates the technique. We can further discuss what is the right way to handle this. But the point was to show how to get the data. The right way for me will be to define FromDate and ToDate parameters on the report level and to replace the variables @FromDate, and @ToDate  with them. The copy-paste mistake was done because I tried to add the declaration and setting line while the original code was using parameters. Obviously I did not have chance to test the code and I expect that the askeer will handle this part.

James, next time could you please concentrate on the idea ? You can also spend some time to help the askeer  instead of waiting for somebody to post an answer and comment what makes sense for you.
0
 
LVL 34

Expert Comment

by:James0628
ID: 41832889
<shrug>  The asker did say that he wasn't very familiar with Commands, so I was just trying to point out some possible issues, which it sounded like he might not be prepared to handle himself.

 James
0
 
LVL 18

Expert Comment

by:vasto
ID: 41832953
James , I don't want to go into a long discussion. You made 2 notes , one about the column name, which is of type "create problem - resolve problem" . If you are going to switch the report to a command it doesn't matter what are the field names in the old report, you are going to rebuild it anyway. The other note was for an obvious copy-paste error , which you commented as "doesn't seem to make much sense". I was expecting comments like this. I saw you joined the session and I waited for few hours so you can handle the answer by yourself. You could have just spent 30 seconds and run a search in expert-exchange to find one of the other questions where I posted the same solution. May be this one:  https://www.experts-exchange.com/questions/27841500/Hourly-scheduled-meeting-and-want-to-output-to-show-blocked-time-slots-form-5-00-am-9-00-pm.html
This is not the first time and probably will be not the last when you wait for a solution to comment it, but not bother to give one. A little bit upsetting if you ask me.
0
 
LVL 34

Expert Comment

by:James0628
ID: 41834119
As far as the field name goes, it did not occur to me at first that changing to a Command might mean rebuilding the report (I never used Commands in my reports, much less changed a report from using tables to using a Command).  It did occur to me before I actually posted that something like that might happen, but I thought that they might want to keep the old, familiar, field name, so I left that part in.

 And I never "waited" for anything (and I have no idea what "session" you're taking about).  I don't check EE that often, so I often (probably usually) see things after others have posted.  If I feel that I have something useful to contribute, I'll post.  I wasn't looking for points, and didn't think any of that was a big deal.  I was just trying to help the OP possibly get their solution faster by pointing out some possible issues.  And they weren't expecting to actually try this until next week, at which point things might not be that fresh in your, or my, mind, so I went ahead and posted.  Maybe it was a bad call in some way, but I still don't see it that way.  IAC, no harm, or offense, was intended.

 And my apologies to Ian for this getting so off track.

 James
0
 
LVL 18

Expert Comment

by:vasto
ID: 41834164
Just stop commenting other solutions. Contributing is one thing, comments like "this doesn't make sense" is another. Who the hell do you think you are ?
0
 
LVL 34

Expert Comment

by:James0628
ID: 41834241
As I've said, I was only trying to help.  Nothing more.  Nothing less.  You obviously don't see it that way.  If I posted code with errors/typos (and I'm sure that it has happened), I would want someone to correct me.  I hate posting bad information.  I don't understand why this seems to bother you so much, but I'm not going to stop trying to help people when I think I can.  I guess I'll try to remember not to post in questions where you're involved, if it offends you that much.

 James
0
 
LVL 18

Expert Comment

by:vasto
ID: 41834387
James, you can say there is a typo in @ToDate=MAX(@ToDate)  and this will be appreciated . It is an obvious typo. Doesn't make sense"  doesn't help anybody. This is not a paid website and I guess nobody is spending too much time to make sure everything is free of errors .  What do you want me to do , to test everything until I get to the golden version just because james will come and will belittle the solution because of a typo or because he believes there is another solution? This is not a single case, it is your normal behavior and I had enough.  I don't care where are you commenting, just don't be rude an do not get personal.
0
 
LVL 34

Expert Comment

by:James0628
ID: 41834434
I sincerely apologize if you thought that I was being rude.  That was not my intent, and it never occurred to me that someone might interpret it that way.  "doesn't seem to make much sense" was just a little jab, in jest.  There was a ":-)" after it.  And there was the possibility that the code was fine and I just didn't understand it.  I didn't see how that could work, but I also realized that I could be wrong, which is why I said "doesn't seem to".  If anything, that was a reflection on me, not you.  I apologize again if you interpreted it as a personal attack.

 James
0
 

Author Closing Comment

by:Ian Chadburn
ID: 41840042
Hi Both,

Thanks a lot for your help.

Vasto:
I put your code in to the command and it pulled back the dates, but not any other data. To get the join to work I converted the Problem CreatedDateTime to dd/mm/yyyy hh:mm:ss to mach the DatesTable.

Thanks again for your help on this.


[code]DECLARE @FromDate DATE, @ToDate DATE;
SELECT @FromDate=MIN(CreatedDateTime) , @ToDate=MAX(CreatedDateTime) FROM Cherwell_Exeter.dbo.Problem
DECLARE @DateTable TABLE (DateValue DATETIME);
   
WITH DatesTable(CurrentDate) AS
(
    SELECT @FromDate AS datetime
    UNION ALL
    SELECT DATEADD(DAY, 1, CurrentDate) FROM DatesTable WHERE CurrentDate < @ToDate
)
            
INSERT INTO @DateTable (DateValue) SELECT CurrentDate FROM DatesTable OPTION (MAXRECURSION 0)
   
SELECT d.DateValue AS CreatedDateTime, p.Status, p.ProblemID
FROM @DateTable d LEFT JOIN  Cherwell_Exeter.dbo.Problem p ON d.DateValue =convert(datetime,convert(varchar,p.CreatedDateTime,103)) AND p.Status NOT IN ('Closed','Completed PIR')[/code]
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 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

11 Experts available now in Live!

Get 1:1 Help Now