| Author |
Message |
Cervello
Newbie
Joined: 18 Jul 2014
Online Status: Offline
Posts: 26
|
   Topic: Display Group Even if No Results
Posted: 12 Jan 2016 at 6:24am |
|
We have a report that is build off 2 views. The report has several group levels, the top one being department. Naturally the select is only being back records that have data for the time periods etc., which means some departments may be elliminated because they have no data to report.
The client is wanting the department section to display even if there is no data to report. Is there a way without altering the view to accomplish this?
I read somewhere that you could create a sub report with the department field and then link that sub report to the group department, but when I tried this it did not work.
Any help is greatly appreciated!!
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Jan 2016 at 8:14am |
|
why not change the view?
|
|
IP Logged |
|
Cervello
Newbie
Joined: 18 Jul 2014
Online Status: Offline
Posts: 26
|
Posted: 12 Jan 2016 at 9:38am |
|
This might sound silly, but our DBA is back logged with other requests and I do not have the permissions to make the change. I was trying to see if there was a creative work around within Crystal to get me by.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Jan 2016 at 9:48am |
|
you need a data source that has all the records you want to display.
If you can get that and outer join it to the views or use sub reports it may be possible.
If you try the outer join you cannot apply a select in the report itself as it will be applied after the join.
Generally speaking , it is much easier and more efficient to alter your view(s) or stored procedures to get the correct data set.
|
|
IP Logged |
|
Cervello
Newbie
Joined: 18 Jul 2014
Online Status: Offline
Posts: 26
|
Posted: 12 Jan 2016 at 11:08am |
|
Thank you for the recommendation and guidance on best practice. Maybe I can alter the view and then have them validate and apply it.
|
|
IP Logged |
|
Cervello
Newbie
Joined: 18 Jul 2014
Online Status: Offline
Posts: 26
|
Posted: 21 Jan 2016 at 9:18am |
|
Hi DBlank,
I had my DBA create a new view for just the department field that needs to display as a Group even if there is no results.
I have a template that is passing several paramter fidls to three sub reports. I tried several approaches, running into issues. I mapped the tempate to just point to the new vwDepartment. I linked the sub reports on that field, along with StartDate, EndDate, ReportVersion, and FormID. I'm getting the correct departments on the template BUT the report is empty.
When I just link on sub report and only link on department (no other fields) it works....it's when I try and pass the other parameters it fails.
Any suggestions?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Jan 2016 at 2:36am |
|
how are you 'passing' the parameters and what are they?
|
|
IP Logged |
|
Cervello
Newbie
Joined: 18 Jul 2014
Online Status: Offline
Posts: 26
|
Posted: 22 Jan 2016 at 7:25am |
|
I’m working with a total of three views. I tried linking all the views to the template, and setting the Group for Department to use the vwDepartment since this view is not part of select. I could not get this approach to work – but this would be the ideal approach if possible since below I mention the need to pass parameters from the other views.
My second attempt was to only link the vwDepartment on the template. This approach correctly displays all the expected department sections, but is not displaying data. My current set up is this.
Template Report - Connected to vwDepartment
?Report Version ( Today, Yesterday, Weekly etc.)
Used in the @StartDate & @EndDate formula fields (on all sub reports) to derive the start and end date. The formulas are used in the select statement to narrows the records returned.
?Report Type ( Full or Verification)
Use in select formula (all sub reports) to narrow results in report. The formulas are used in the select statement to narrows the records returned.
Sub Reports – connected to vwQuestionsAnswers , vwQuestionForms (Contains a Department Field)
****IMPORTANT **** Need to link the data for all sub reports by Transaction # so the data on the three sub reports lines up, as well as insure the data appears under the correct department group heading. i.e. {vw.QuestionForms.Department} = {vwDepartment.Department}. Since these views are not part of the Template I can’t link the sub reports on them.
Sub Report #1
?Report Version
?PM - ?Report Type = @Report Type
Sub Report # 2
?Report Version
?PM - ?Report Type = @Report Type
Sub Report # 3
?Report Version
?PM - ?Report Type = @Report Type
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Jan 2016 at 7:46am |
|
So the way I would have done it would be to was to use a stored proc with date parameters passed into the join conditions or a sub query.
IF I understand, for your views I would make a main report using only the view with all of the departments as the source. Create what ever parameters you need to run the sub reports in the main report (date fields?)
Place department name in detail a.
create a details b and c
place your sub reports into each detail b and c
in sub reports link on the department code and the two date parameters.
In each of the sub reports adjust your select statement to properly use the linked values
something like
vw2.departarmet = ?pm-department and vw2.datefield in ?pm-startdate to ?pm-enddate
|
|
IP Logged |
|
|
|
