Easy!! Subquery needs to show all Sales Reps, even when null

I have a subquery that grabs all the sales reps with less then 6 visits. Only problem is, when i have a date when there are any number of sales reps that dont make visits, so the column would be null, they dont show up. I want to display these, because this report is supposed to show the visits made, so if they made none, i want it to show zero, instead of not showing the whole date column, since zero visits were made on that date that were below 6 and more then zero. Here's my stored procedure: (the subquery is highlighted)

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Visits_6]

(@.Region_Key int=null)

AS

BEGIN

SELECT dbo.Qry_Visits.Status,

dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code],

B.VisitsTotal

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

INNER JOIN ( Select COUNT(Visits)as VisitsTotal,[Sales Responsible],CONVERT(VARCHAR,(Qry_Visits.time_log),110)TheDate,Qry_Visits.Status

FROM dbo.Qry_Visits

WHERE Qry_Visits.Status=2

GROUP by [Sales Responsible] , CONVERT(VARCHAR,(Qry_Visits.time_log),110),Qry_Visits.Status

HAVING SUM(Visits) < 6)B

ON dbo.Qry_Sales_Group.SR_Name COLLATE Latin1_General_CI_AS = B.[Sales Responsible] COLLATE Latin1_General_CI_AS AND

CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = B.TheDate

WHERE REGION_KEY=@.Region_Key and Qry_Visits.Status=2

ORDER BY dbo.Qry_Sales_Group.SR_Name, CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110)

You'll need to do an outer join to your date dimension

|||

is there a way i can just say, if SUM(visits) is null then 0

or something like that?

|||

either this...

coalesce(sum(visits),0)

or this...

case

when sum(visits) is null then 0

else sum(visits)

end as 'SumVisits'

|||I guess technically this doesnt work because it doesnt change anything. I guess i just need to find a way to Display all dates that have visits, since all dates show up on my other report that displays all visits, no matter how many. Would i do another subquery to grab all dates where Visit is greater then zero?|||

Replace "INNER JOIN" with "LEFT OUTER JOIN", try the following...

ALTER PROCEDURE [dbo].[Testing_Visits_6]

(@.Region_Key int=null)

AS

BEGIN

SELECT dbo.Qry_Visits.Status,

dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code],

IsNull(B.VisitsTotal,0) VisitsTotal

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

LEFT OUTER JOIN (

Select COUNT(Visits)as VisitsTotal

, [Sales Responsible]

, CONVERT( VARCHAR,(Qry_Visits.time_log),110) as TheDate

, Qry_Visits.Status

FROM dbo.Qry_Visits

WHERE Qry_Visits.Status=2

GROUP by [Sales Responsible] , CONVERT(VARCHAR,(Qry_Visits.time_log),110),Qry_Visits.Status

HAVING SUM(Visits) < 6

)B

ON dbo.Qry_Sales_Group.SR_Name COLLATE Latin1_General_CI_AS = B.[Sales Responsible] COLLATE Latin1_General_CI_AS AND

CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = B.TheDate

WHERE REGION_KEY=@.Region_Key and Qry_Visits.Status=2

ORDER BY dbo.Qry_Sales_Group.SR_Name, CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110)

END

sql

Dynamically display/hide the parameter input

I have a handful of reports that are currently used by sales reps, and I'm trying to make them available to their regional VP's, and coporate users (executives and administrative staff that support Sales nationwide).

Currently, the reports take the UserID and resolve it to show the information that is only appropriate for that specific rep.

What I would like to do is have the parameter section at the top of the report be displayed for higher level users, so they could select an individual sales rep from a drop-down. (Ideally, the RVP's would only be able to select from reps in their region, but the corporate users would be able to select any rep.) The problem is, I don't want any of the sales reps to be able to select a rep other than themselves, for obvious reasons.

Is there a way to have the parameter section hidden/displayed dynamically, based on the UserID, so that users other than reps would have the ability to enter the desired rep name, but reps would not?

If you are using the .NET ReportView, it is just:

ReportViewer1.ShowParameterPrompts = False

|||I am using Report Manager.|||If you want any level of control, you are going to have to host the ReportViewer control in an ASP.NET page and customize the web page/report based on the user. There is not much you can do in ReportManager as far as fine control.|||

1. One way is to control this by writting a html page instead of report manager home page within which you will have a link to each of the report. Depending on who the user is you will change the path of the link (with or without parameters), meaning you will default all the parameters and pass them within the hidden link, but this wont work if you have more that 1 parameter and would want to hide only the user id parameter

2. Another option is to control this within the report. As your userid parameter list is being populated by a stored procedure, you will make the stored procedure accept a input parameter which is the userId of the person that is trying to run the report. Depending on who the user is the SP will return only the required users list.

Meaning

1. if XXX is passed and if XXX is RVP then it will return only the sales persons of XXX's region.

2. If YYY is passed and if YYY is sales person then it will return back only YYY

3. If CORP is passed and if CORP is a corporate user then the procedure will return all sales person's names

Hope this is clear and helps!!!

|||I think another way of achieving this is writting specific RDL code.|||

Thanks.

In the short term, this was the best solution. I wrote a sp that populates the drop down based on the NetLogin value.

So, the sales reps can see the drop down, but it only has their name in it.

|||You can also set the parameters visible in the URL using the &rsarameters=True/False.

i.e.

Code Snippet

http://SERVER/ReportServer/ReportPath&rs:Command=Render&rs:Parameters=False

HTH,
Jimmy