Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-10-05
20
Medium Priority
?
241 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 35

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 1600 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
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 400 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 35

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 35

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 35

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 35

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

664 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