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

Easy Stuff

hi.. guys this is simple for you guys how can i make ms access to display the current date in a col... when i go to insert a new line i how can i make it so that its allready there? i dont have to type it??I bet that you'll get a lot better response if you post this question in the MS-Access (http://www.dbforums.com/f84) forum.

-PatP|||thnx Pat...|||That didn't hurt much, now did it ?!?!

Anywho, while I'm sure that somebody here could have answered your question, why bother to ask here when there are oodles of folks that are readily available that can answer you? Better still, they can offer lots of insight because they actually USE MS-Access as their tool of choice.

-PatP

Easy question

How do i format a date which is 9/1/2004 as Sept 04

I have tried giving

Format(Fields!GrowthDates.Value,"Y")

but i just get Y and not dates.

Thanks

Regards,

Karen

= Month(Fields!GrowthDates.Value) & " " & Year(Fields!GrowthDates.Value)

Easy question, easy answer. You want to put this in the format expression.

|||

Date.Now.ToString("MMM yy")

|||

Greg,

When i use that expression for 08/01/2004 its give 08 2004 and not Aug 2004 ,,, how can i get it to show Aug 2004

|||

I've never been able to get this function to work, but you would use MonthName. It always returns strange data for me. Maybe someone else can clear this up...

= MonthName(Month(Fields!GrowthDates.Value)) & "-" & Year(Fields!GrowthDates.Value)

Instead of September, I get SepAe0ber 2006

For October, I get OcAober

November - Nove0ber

Strange, huh?

|||

Greg i got it to work

=MonthName(Month(Fields!GrowthDates.Value),true)& " " & Year(Fields!GrowthDates.Value)

True = Abbreviate.

so if my data 08/01/2004 its gonna display Aug 2004

Hope this helps

Regards,

Karen

|||

Why dont you try ="MMM yyyy" in format expression.

Priyank

|||

I tried that in the report but on my X axis instead of the date i get MMM yyy which is of new use.

|||

I get bad abbreviations with that. Some are good but some are still strange...

August - AuA.D.

October OcA

It's almost as if the definitions for the months on my installation of reporting services are wrong.

|||

Priyank Pandey wrote:

Why dont you try ="MMM yyyy" in format expression.

Priyank

That did work, note the case. Thanks!

|||

Its showing up correct on mine....

Regards

Karen

|||

Can you pls mark it as ans.

Thanks!

Easy Date Problem

I am somewhat new to SQL and was trying to subtract 11 days from today
but I want it to go to 00:00:00 rather than 11 days whatever time it is
I run it.
Example: It is 8:39 AM right now and when I run
where completiondate BETWEEN getdate()-11 and getdate()-5
I want the result to be xx/xx/xx 00:00:00 rather than xx/xx/xx 8:39.
Thanks!
BVDECLARE @.dt SMALLDATETIME
SET @.dt = DATEDIFF(DAY,0,GETDATE())
SELECT @.dt
SELECT ...
WHERE CompletionDate >= (@.dt - 11)
AND CompletionDate < (@.dt - 5)
(Do not use between here, unless CompletionDate *only* stores the date with
time always set to midnight, and if you want to include rows from 5 days ago
in the result.)
<brentkelli@.gmail.com> wrote in message
news:1130420498.749859.291940@.g49g2000cwa.googlegroups.com...
>I am somewhat new to SQL and was trying to subtract 11 days from today
> but I want it to go to 00:00:00 rather than 11 days whatever time it is
> I run it.
> Example: It is 8:39 AM right now and when I run
> where completiondate BETWEEN getdate()-11 and getdate()-5
> I want the result to be xx/xx/xx 00:00:00 rather than xx/xx/xx 8:39.
> Thanks!
> BV
>|||How is about?
SELECT <columns> FROM Table WHERE completiondate
>=CAST(FLOOR(CAST(DATEADD(day,-11,GETDATE())AS FLOAT))AS DATETIME)AND
<=CAST(FLOOR(CAST(DATEADD(day,-11,GETDATE())AS FLOAT))AS DATETIME)
<brentkelli@.gmail.com> wrote in message
news:1130420498.749859.291940@.g49g2000cwa.googlegroups.com...
>I am somewhat new to SQL and was trying to subtract 11 days from today
> but I want it to go to 00:00:00 rather than 11 days whatever time it is
> I run it.
> Example: It is 8:39 AM right now and when I run
> where completiondate BETWEEN getdate()-11 and getdate()-5
> I want the result to be xx/xx/xx 00:00:00 rather than xx/xx/xx 8:39.
> Thanks!
> BV
>

easy (?) SQL constraint question

I have a table where there are tow columns, 'B_Deleted' and 'SDT_DeletedOn'.

Can I create a constraint that says that, if B_Deleted is 1, then a date must be put in SDT_DeletedOn.

Here is what I have so far:

CREATE TABLE myTable
(
B_Deleted bit DEFAULT(0),
SDT_DeletedOn smalldatetime
);Unfortunately you can't as the primary purpose of CONSTRAINT is integrity of your table, and not to mention it expects a TRUE or FALSE condition, so adding another step of adding a date upon modification of a column isn't possible. You have to use a trigger on that one. :)

Each date on its own report page?

I am wondering how to set a page break so that when a date range is selected (01/04/06 to 30/04/06) each day would show up on it's own page with the date at the top? Is this possible?
Any help would be appreciated.
TIA
TaraDo you want to have every date in the range to have its own page even if there's no corresponding data for that date?
This isn't too difficult but I won't elaborate unless it's what you really want.

If you merely want each new (record) date in the range on its own page then group by the date. Change the Group options to be printed 'for each day' and check 'Repeat Group Header On Each Page'.
In the section expert, check 'New Page After' for the group footer .|||Hi there. Yes, that is exactly what I would like to do! How do I go about changing the group options? (sorry, very new to crystal reporting and working with pre-packaged reports that I am trying to modify) I found the section info yesterday and had tried the new page after the group footer, was missing the first step though :)

Thanks very much for your help!|||That's OK, I'm new to Crystal too.
To change the group options right click on the group header/footer on the left of the design view and choose 'Change Group'. You'll find the 'The section will be printed' bit on the Common tab, and the 'Repeat Group Header...' bit on the Options tab.|||Tara,

Just to note, the 'Repeat Group Header...' bit is only required if one day's worth of data spans more than one page and you want the (same) date repeated on each page.

Dynmaic parameter on Subscription report

Is it possible to have a dynamic parameter value such as current date
to a subscription base report?
Thanks,
Voss.Yes. You can set the default parameter =today() in the designer.
"Voss" wrote:
> Is it possible to have a dynamic parameter value such as current date
> to a subscription base report?
> Thanks,
> Voss.
>sql

Dynamically setting maximum server date dimension

Hello everyone. I have what should hopefully be a simple problem. I have a fairly complicated cube created in SSAS 2005. One of my dimensions is a Server Time dimension. I have this linked to my fact table with a Regular relationship. Now, I would ideally like to be able to set the CalendarEndDate property in the Source property group of my dimension dynamically, based on the most recent date in the fact table. I will never need to see information for a future date using this dimension. Any ideas would be greatly appreciated. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Why would you want to do that? Which goals of yours would it allow to reach?|||

I have a cube which is being used by analysts who are using Excel pivottables as a frontend. When sorting by time, they do not wish to see any options which will return no data. New data is only added about once a month, so reprocessing the cube at that time is not an issue. Does this make sense? Is this the appropriate way of handling this situation?

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||Usually the tools are capable to show only cells with data. For instance, Browser page in Visual Studio does not show the cells with no data by default. You can control it with "Show Empty Cells" button on the toolbar. I think Excel has the same functionality.

Suppose you have 2 measure groups. For one measure group the fact data gives you "last known date1", for another - "last known date2". What would you do?

The time span for the time dimension should cover all the facts with which the dimension will be used. Otherwise the processing of the cube will fail.|||

you can disable cells in excel where you do't have any data. in the Pivot table you have a button called "Always display items"

-- Good Luck

|||

Here is another way to skin the cat.

I have mutiple measure groups using the time dimension. The way I find the min and max dates is store them in a single record table DAY_DIM_RANGE using a stored procedure. This goes through the multiple fact tables and retains the min and max dates and writes them to the single row table.

The date dimension is then built using a view with a where clause such as

WHERE DAY_KEY BETWEEN (SELECT minDayKey FROM dbo.DAY_DIM_RANGE) AND
(SELECT maxDayKey FROM dbo.DAY_DIM_RANGE)

When you add additional measure groups, just adjust the stored procedure to handle the new fact table min and max dates. The view does not change. The stored procedure needs to run just before the cube is processed or at end of ETL.

Dynamically setting maximum server date dimension

Hello everyone. I have what should hopefully be a simple problem. I have a fairly complicated cube created in SSAS 2005. One of my dimensions is a Server Time dimension. I have this linked to my fact table with a Regular relationship. Now, I would ideally like to be able to set the CalendarEndDate property in the Source property group of my dimension dynamically, based on the most recent date in the fact table. I will never need to see information for a future date using this dimension. Any ideas would be greatly appreciated. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Why would you want to do that? Which goals of yours would it allow to reach?|||

I have a cube which is being used by analysts who are using Excel pivottables as a frontend. When sorting by time, they do not wish to see any options which will return no data. New data is only added about once a month, so reprocessing the cube at that time is not an issue. Does this make sense? Is this the appropriate way of handling this situation?

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||Usually the tools are capable to show only cells with data. For instance, Browser page in Visual Studio does not show the cells with no data by default. You can control it with "Show Empty Cells" button on the toolbar. I think Excel has the same functionality.

Suppose you have 2 measure groups. For one measure group the fact data gives you "last known date1", for another - "last known date2". What would you do?

The time span for the time dimension should cover all the facts with which the dimension will be used. Otherwise the processing of the cube will fail.|||

you can disable cells in excel where you do't have any data. in the Pivot table you have a button called "Always display items"

-- Good Luck

|||

Here is another way to skin the cat.

I have mutiple measure groups using the time dimension. The way I find the min and max dates is store them in a single record table DAY_DIM_RANGE using a stored procedure. This goes through the multiple fact tables and retains the min and max dates and writes them to the single row table.

The date dimension is then built using a view with a where clause such as

WHERE DAY_KEY BETWEEN (SELECT minDayKey FROM dbo.DAY_DIM_RANGE) AND
(SELECT maxDayKey FROM dbo.DAY_DIM_RANGE)

When you add additional measure groups, just adjust the stored procedure to handle the new fact table min and max dates. The view does not change. The stored procedure needs to run just before the cube is processed or at end of ETL.

dynamically creating a select statement

I have a stored procedure in my database which will be used to search for records matching given criteria, such as within a date range or containing a keyword. The procedure stub is looking like this at the moment:

ALTER PROCEDURE [dbo].[search]
@.file_id int,
@.title_includes varchar(50),
@.notes_includes varchar(50),
@.updated_after datetime,
@.updated_before datetime,
@.deleted_after datetime,
@.deleted_before datetime,
@.size_bigger_than int,
@.size_smaller_than int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
--SELECT <@.Param1, sysname, @.p1>, <@.Param2, sysname, @.p2>
END

Ideally I would like criteria to only be used in the select statement if the value passed in is not null, but as far as i know it is not possible to place and if-then condition in the middle of a select statement to check if the parameter is null? Does anyone know of an efficient way to achieve this functionality? Any help will be greatly appreciated, thank you.

There's two approaches that I can think of that might help you.

Firstly, it's entirely valid to include criteria such as

WHERE (file_id = @.file_id or @.file_id is null)
AND (title_includes = @.title_includes or @.title_includes is null)
AND ...

in the stored procedure.

However, if you mean that you only want to see the file_id column in the results if the passed parameter @.file_id is not null, then you need to approach it differently, as follows:

declare @.select nvarchar(max)
declare @.where nvarchar(max)
declare @.selectsep nvarchar(5)
declare @.wheresep nvarchar(5)

set @.select = N''
set @.selectsep = N''
set @.where = N''
set @.wheresep = N''

if (@.file_id is not null)
begin
set @.select = @.select + @.selectsep + N'file_id'
set @.where = @.where + @.wheresep + N'file_id = @.file_id'
set @.selectsep = N','
set @.wheresep = N' and '
end

if (@.title_includes is not null)
begin
set @.select = @.select + @.selectsep + N'title_includes'
set @.where = @.where + @.wheresep + N'title_includes = @.title_includes'
set @.selectsep = N','
set @.wheresep = N' and '
end

... and so on for the other parameters ...

set @.select = N'SELECT <list of fields you always want to include>, ' + @.select + N'FROM <from clause>'
if (len(@.where) > 0)
begin
set @.select = @.select + N' WHERE ' + @.where
end

exec dbo.sp_executesql @.select
, N' @.file_id int, @.title_includes varchar(50), @.notes_includes varchar(50), @.updated_after datetime, @.updated_before datetime, @.deleted_after datetime, @.deleted_before datetime, @.size_bigger_than int, @.size_smaller_than int'
, @.file_id = @.file_id
, @.title_includes = @.title_includes
, @.notes_includes = @.notes_includes
, @.updated_after = @.updated_after
, @.updated_before = @.updated_before
, @.deleted_after = @.deleted_after
, @.deleted_before = @.deleted_before
, @.size_bigger_than = @.size_bigger_than
, @.size_smaller_than = @.size_smaller_than

Naturally, as you build up the @.select and @.where variables, you can include the usual range of operators (like, <, >, etc).

Let me know if you need any further assistance with this.

Iain

|||

You can try this:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[SearchCurrency]

@.currencycode nchar(5) = null,

@.currencyname nchar(25) = null

as

begin

declare @.stmt nvarchar(max)

set @.stmt = 'SELECT currencycode, currencyname

FROM currencies where 1=1 '

IF @.currencycode IS NOT NULL

set @.stmt = @.stmt + ' AND currencycode = '''+ @.currencycode + ''''

IF @.currencyname IS NOT NULL

set @.stmt = @.stmt + ' AND currencyname = ''' + @.currencyname +''''

exec(@.stmt)

end

|||

I highly recommend reading Erland's article on Dynamic SQL before going down this path. It may work well for you, but you should be properly informed about the pitfalls.

See:

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

dynamic table columns

I have a sproc that returns sales amount for each month in a given range of
dates. The date range is over the last 8 years, and will grow automatically
as time goes by.
I really do not want to create a table in RS with over 120 columns of data
and then set each of them to display/hide based on the parameters passed.
Matrix table doesnt seem to work for me as I dont want to un-pivot my data
just so it can pivot it back out.
Is there some solution to this problem? I have 5 base columns that will show
regardless of what yeear/month is passed, and usually anywhere from 12 to 48
result columns. is there a way for the table to recognize how many columns
are in the result set and create enough columns automagically?
Thanks!!On Dec 6, 4:03 pm, Carl Henthorn
<CarlHenth...@.discussions.microsoft.com> wrote:
> I have a sproc that returns sales amount for each month in a given range of
> dates. The date range is over the last 8 years, and will grow automatically
> as time goes by.
> I really do not want to create a table in RS with over 120 columns of data
> and then set each of them to display/hide based on the parameters passed.
> Matrix table doesnt seem to work for me as I dont want to un-pivot my data
> just so it can pivot it back out.
> Is there some solution to this problem? I have 5 base columns that will show
> regardless of what yeear/month is passed, and usually anywhere from 12 to 48
> result columns. is there a way for the table to recognize how many columns
> are in the result set and create enough columns automagically?
> Thanks!!
I guess I'm failing to see why you have to return all of the data
unaggregated to RS. Why not just limit the amount of months by
passing the correct parameters to your stored procedure? Then you
could just let the Matrix do its job and dynamically create the
columns.
If you absolutely have to do it this way, go to the Matrix Properties,
Filter tab, and add some log here to determine which of the Months
(that Group) you want to see. This will have the same effect as
setting the Visible on the columns. It will not speed up the data
fetch portion of your report.
If you could provide a little more detail as to what you are actually
trying to do, it would be helpful. What kind of groupings do you have
in the rows, since Months are your columns? Are you manually creating
multiple Rows, each with a different field in your dataset? There are
many ways to get to the same outcome.
-- Scott