db datatypes: chart

Showing posts with label chart. Show all posts

Wednesday, March 21, 2012

Problem with GROUP BY and HAVING in query

I need to create a report showing how many delayed tasks there are at the
end of each month period (last day of month) so I can chart them (monthyear
against TotalTasksOutstanding) - My query is ok until I add the HAVING
clause and I get :
Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is not
contained in either an aggregate function or the GROUP BY clause.
Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is not
contained in either an aggregate function or the GROUP BY clause. (Microsoft
SQL Server, Error: 8121)
SELECT convert(char(4),
datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
convert(char(4),
datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
>dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER BY
monthyear
Also makes the query long because that I can't GROUP BY monthyear
Also I dont't think the query would report tasks opened many months ago but
still oustanding in any particular monthyear - do I need two queries to do
this?
Thanks
DavidTry replacing your having clause with a where clause (with the exact same
criteria) to see if that works.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"David" <idstechnet@.noemail.noemail> wrote in message
news:OcL0VnnNIHA.4688@.TK2MSFTNGP06.phx.gbl...
>I need to create a report showing how many delayed tasks there are at the
>end of each month period (last day of month) so I can chart them (monthyear
>against TotalTasksOutstanding) - My query is ok until I add the HAVING
>clause and I get :
> Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is not
> contained in either an aggregate function or the GROUP BY clause.
> (Microsoft SQL Server, Error: 8121)
> SELECT convert(char(4),
> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
> AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
> COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
> convert(char(4),
> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
> HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
> >dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER BY
> monthyear
> Also makes the query long because that I can't GROUP BY monthyear
> Also I dont't think the query would report tasks opened many months ago
> but still oustanding in any particular monthyear - do I need two queries
> to do this?
> Thanks
> David
>|||Alain
Thanks - that gives me a nice chart
Any thoughts on whether my query provides what I need? i.e. would the query
report tasks opened many months ago but still oustanding in any particular
monthyear ?
Cheers
David
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:%23P0aPznNIHA.5720@.TK2MSFTNGP04.phx.gbl...
> Try replacing your having clause with a where clause (with the exact same
> criteria) to see if that works.
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "David" <idstechnet@.noemail.noemail> wrote in message
> news:OcL0VnnNIHA.4688@.TK2MSFTNGP06.phx.gbl...
>>I need to create a report showing how many delayed tasks there are at the
>>end of each month period (last day of month) so I can chart them
>>(monthyear against TotalTasksOutstanding) - My query is ok until I add the
>>HAVING clause and I get :
>> Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is not
>> contained in either an aggregate function or the GROUP BY clause.
>> Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is not
>> contained in either an aggregate function or the GROUP BY clause.
>> (Microsoft SQL Server, Error: 8121)
>> SELECT convert(char(4),
>> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
>> AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
>> COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
>> convert(char(4),
>> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,tasks.opendate)),2)
>> HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
>> >dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER BY
>> monthyear
>> Also makes the query long because that I can't GROUP BY monthyear
>> Also I dont't think the query would report tasks opened many months ago
>> but still oustanding in any particular monthyear - do I need two queries
>> to do this?
>> Thanks
>> David
>|||On Dec 4, 9:54 am, "David" <idstech...@.noemail.noemail> wrote:
> Alain
> Thanks - that gives me a nice chart
> Any thoughts on whether my query provides what I need? i.e. would the query
> report tasks opened many months ago but still oustanding in any particular
> monthyear ?
> Cheers
> David
> "Alain Quesnel" <alainsanss...@.logiquel.com> wrote in message
> news:%23P0aPznNIHA.5720@.TK2MSFTNGP04.phx.gbl...
>
> > Try replacing your having clause with a where clause (with the exact same
> > criteria) to see if that works.
> > Alain Quesnel
> > alainsanss...@.logiquel.com
> >www.logiquel.com
> > "David" <idstech...@.noemail.noemail> wrote in message
> >news:OcL0VnnNIHA.4688@.TK2MSFTNGP06.phx.gbl...
> >>I need to create a report showing how many delayed tasks there are at the
> >>end of each month period (last day of month) so I can chart them
> >>(monthyear against TotalTasksOutstanding) - My query is ok until I add the
> >>HAVING clause and I get :
> >> Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is not
> >> contained in either an aggregate function or the GROUP BY clause.
> >> Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is not
> >> contained in either an aggregate function or the GROUP BY clause.
> >> (Microsoft SQL Server, Error: 8121)
> >> SELECT convert(char(4),
> >> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,t-asks.opendate)),2)
> >> AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
> >> COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
> >> convert(char(4),
> >> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,t-asks.opendate)),2)
> >> HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
> >> >dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER BY
> >> monthyear
> >> Also makes the query long because that I can't GROUP BY monthyear
> >> Also I dont't think the query would report tasks opened many months ago
> >> but still oustanding in any particular monthyear - do I need two queries
> >> to do this?
> >> Thanks
> >> David- Hide quoted text -
> - Show quoted text -
Generally, the WHERE statement should be used to filter the criteria
before aggregation, and the HAVING is used to filter after
aggregation. That is why your error message had a "not contained in
aggregation" text. You would use a clause like "HAVING
COUNT(tasks.opendate) > 10" to show outstanding months, basically
reduce the rows in the DataSet .
It sounds like what you really want is a count for all tasks where:
1. In the month that the task was opened, if task ended in later
month
2. the month that the task was closed, if the task opened in an
earlier month
3. all months inbetween, where task was opened in earlier month and
ended after
The way the query is currently written, it will only increment in the
month that the task was opened. Tasks that were opened in Dec. 2006
but not closed will have a monthyear of '2006-12' showing up in your
query when you really want it to show up in Dec, Jan, Feb, etc.
To get this to work, you need to join your dataset against a list of
months, then do a count per month. To do that, I found an example
that uses a table-based CTE to generate a list of months. Left join
that against your tasks table, then let RS do the rest.
WITH MYCTE AS
(
SELECT CAST( '2007-01-01' AS DATETIME ) DT
UNION ALL
SELECT DateAdd( mm, 1, DT )
FROM MYCTE
WHERE DateAdd( mm, 1, DT ) < '2008-01-01'
)
SELECT X.EFF_DT AS YEARMONTH,
COUNT( Y.OPENDATE ) AS TotalTasksOutstanding,
COUNT( Y.CLSDDATE ) AS TotalTasksClosed
FROM (
SELECT DT AS EFF_DT, DateAdd( mm, 1, DT ) AS TERM_DT
FROM MYCTE
) X LEFT JOIN
(
SELECT *
FROM TASKS
WHERE CLSDDATE IS NULL
OR CLSDDATE > DATEADD( mm, 1, OPENDATE - DAY( OPENDATE ) + 1 ) -
1 )
) Y
ON X.EFF_DT <= ISNULL( Y.CLSDDATE, GETDATE() )
AND X.TERM_DT > Y.OPENDATE
GROUP BY X.EFF_DT
Now, in your chart, use the YEARMONTH column as the Category, and
Sum( TotalTasksOutstanding ) and Sum( TotalTasksClosed ) as your Data
Values. In the Label of the Category, use an expression like
= Format( Fields!YEARMONTH.Value, "yyyy-MM" )
and you get around having to do all the formatting on the Server-side.
-- Scott|||Scott
Fantastic response - looks exactly what I need - will read up on CTE.
Many thanks!
Cheers
David
"Orne" <polysillycon@.yahoo.com> wrote in message
news:9332099e-41f8-43e6-b082-0da7fa1fa5c2@.s12g2000prg.googlegroups.com...
> On Dec 4, 9:54 am, "David" <idstech...@.noemail.noemail> wrote:
>> Alain
>> Thanks - that gives me a nice chart
>> Any thoughts on whether my query provides what I need? i.e. would the
>> query
>> report tasks opened many months ago but still oustanding in any
>> particular
>> monthyear ?
>> Cheers
>> David
>> "Alain Quesnel" <alainsanss...@.logiquel.com> wrote in message
>> news:%23P0aPznNIHA.5720@.TK2MSFTNGP04.phx.gbl...
>>
>> > Try replacing your having clause with a where clause (with the exact
>> > same
>> > criteria) to see if that works.
>> > Alain Quesnel
>> > alainsanss...@.logiquel.com
>> >www.logiquel.com
>> > "David" <idstech...@.noemail.noemail> wrote in message
>> >news:OcL0VnnNIHA.4688@.TK2MSFTNGP06.phx.gbl...
>> >>I need to create a report showing how many delayed tasks there are at
>> >>the
>> >>end of each month period (last day of month) so I can chart them
>> >>(monthyear against TotalTasksOutstanding) - My query is ok until I add
>> >>the
>> >>HAVING clause and I get :
>> >> Column 'tasks.CLSDDATE' is invalid in the HAVING clause because it is
>> >> not
>> >> contained in either an aggregate function or the GROUP BY clause.
>> >> Column 'tasks.OPENDATE' is invalid in the HAVING clause because it is
>> >> not
>> >> contained in either an aggregate function or the GROUP BY clause.
>> >> (Microsoft SQL Server, Error: 8121)
>> >> SELECT convert(char(4),
>> >> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,t-asks.opendate)),2)
>> >> AS monthyear, COUNT(dbo.tasks.opendate) AS TotalTasksOutstanding,
>> >> COUNT(dbo.tasks.clsddate) AS TotalTasksClosed FROM tasks GROUP BY
>> >> convert(char(4),
>> >> datepart(yy,tasks.opendate))+'-'+right('0'+convert(varchar(2),datepart(mm,t-asks.opendate)),2)
>> >> HAVING (dbo.tasks.clsddate is NULL or dbo.tasks.clsddate
>> >> >dateadd(mm,1,dbo.tasks.opendate -day(dbo.tasks.opendate)+1)-1) ORDER
>> >> >BY
>> >> monthyear
>> >> Also makes the query long because that I can't GROUP BY monthyear
>> >> Also I dont't think the query would report tasks opened many months
>> >> ago
>> >> but still oustanding in any particular monthyear - do I need two
>> >> queries
>> >> to do this?
>> >> Thanks
>> >> David- Hide quoted text -
>> - Show quoted text -
> Generally, the WHERE statement should be used to filter the criteria
> before aggregation, and the HAVING is used to filter after
> aggregation. That is why your error message had a "not contained in
> aggregation" text. You would use a clause like "HAVING
> COUNT(tasks.opendate) > 10" to show outstanding months, basically
> reduce the rows in the DataSet .
> It sounds like what you really want is a count for all tasks where:
> 1. In the month that the task was opened, if task ended in later
> month
> 2. the month that the task was closed, if the task opened in an
> earlier month
> 3. all months inbetween, where task was opened in earlier month and
> ended after
> The way the query is currently written, it will only increment in the
> month that the task was opened. Tasks that were opened in Dec. 2006
> but not closed will have a monthyear of '2006-12' showing up in your
> query when you really want it to show up in Dec, Jan, Feb, etc.
> To get this to work, you need to join your dataset against a list of
> months, then do a count per month. To do that, I found an example
> that uses a table-based CTE to generate a list of months. Left join
> that against your tasks table, then let RS do the rest.
> WITH MYCTE AS
> (
> SELECT CAST( '2007-01-01' AS DATETIME ) DT
> UNION ALL
> SELECT DateAdd( mm, 1, DT )
> FROM MYCTE
> WHERE DateAdd( mm, 1, DT ) < '2008-01-01'
> )
> SELECT X.EFF_DT AS YEARMONTH,
> COUNT( Y.OPENDATE ) AS TotalTasksOutstanding,
> COUNT( Y.CLSDDATE ) AS TotalTasksClosed
> FROM (
> SELECT DT AS EFF_DT, DateAdd( mm, 1, DT ) AS TERM_DT
> FROM MYCTE
> ) X LEFT JOIN
> (
> SELECT *
> FROM TASKS
> WHERE CLSDDATE IS NULL
> OR CLSDDATE > DATEADD( mm, 1, OPENDATE - DAY( OPENDATE ) + 1 ) -
> 1 )
> ) Y
> ON X.EFF_DT <= ISNULL( Y.CLSDDATE, GETDATE() )
> AND X.TERM_DT > Y.OPENDATE
> GROUP BY X.EFF_DT
> Now, in your chart, use the YEARMONTH column as the Category, and
> Sum( TotalTasksOutstanding ) and Sum( TotalTasksClosed ) as your Data
> Values. In the Label of the Category, use an expression like
> = Format( Fields!YEARMONTH.Value, "yyyy-MM" )
> and you get around having to do all the formatting on the Server-side.
> -- Scott

Problem with Gray Scale Plaette

Hi,

I'm attempting to constrct a bar chart with the Gray Scale Plaette. All the other plaette works fine. However, for the gray scale one, the patterns just go random. It doesn't follow the legend. Have I done something wrong here?

If there is no way to get around this, can I construct one manually? Just wonder how can I do that, cos I want to get some report printed out.

Thanks,

Josh

I am also finding this problem. Can someone from MS let us know if this is a bug? And is there a solution for it coming? or a workaround?|||I also have the same problem. Did anyone find a solution to this?|||

I'm not sure what you mean by "the patterns are random".

Each entry in your legend (ie. data series) should be assigned a different hatch pattern. The point behind the grayscale palette is to make printed black and white output look readable.

It is possible to assign a custom color to your data elements. You do this in the data value dialog - you will need to provide an expression for the color value though to make this work for each unique data values.

|||

The problem is say I have a graph with a default palette. I view the graph, and I have three different colors - one for series 1, one for series 2, one for series 3. The legend has three items, with three different colors, one for each series.

Then I change the graph to grayscale without changing anything else. Now instead of getting three different grayscale patterns, one to represent each color/series, I get a different pattern for every single bar. The legend still shows three items as expected, and just picks the first three patterns to display.

|||This is an issue with 2005 and not 2000 as we didn't see it until converting reports to 2005.|||Actually i have the same problem on SQL2005.
Problem appears when you have more than one group in the chart. Then patterns are applied randomly across the series and groups. Same series in different groups have different patterns, while they should have the same. The legend shows correct patterns for the first group, but it isn't valid for the other groups.
Does anyone know the solution?
My SSRS version is 9.00.2221.00. Do i need some later hotfixes?

Problem with Gray Scale Plaette

Hi,

If there is no way to get around this, can I construct one manually? Just wonder how can I do that, cos I want to get some report printed out.

Thanks,

Josh

I'm not sure what you mean by "the patterns are random".

Each entry in your legend (ie. data series) should be assigned a different hatch pattern. The point behind the grayscale palette is to make printed black and white output look readable.

|||

Problem with Gray Scale Plaette

Hi,

If there is no way to get around this, can I construct one manually? Just wonder how can I do that, cos I want to get some report printed out.

Thanks,

Josh

I'm not sure what you mean by "the patterns are random".

Each entry in your legend (ie. data series) should be assigned a different hatch pattern. The point behind the grayscale palette is to make printed black and white output look readable.

|||

Problem with Gray Scale Plaette

Hi,

If there is no way to get around this, can I construct one manually? Just wonder how can I do that, cos I want to get some report printed out.

Thanks,

Josh

I'm not sure what you mean by "the patterns are random".

Each entry in your legend (ie. data series) should be assigned a different hatch pattern. The point behind the grayscale palette is to make printed black and white output look readable.

|||

Problem with Gray Scale Plaette

Hi,

If there is no way to get around this, can I construct one manually? Just wonder how can I do that, cos I want to get some report printed out.

Thanks,

Josh

I'm not sure what you mean by "the patterns are random".

Each entry in your legend (ie. data series) should be assigned a different hatch pattern. The point behind the grayscale palette is to make printed black and white output look readable.

|||

Saturday, February 25, 2012

Problem with defining 2 datasets against OLAP cube

Hello all,

I built a report (RS 2005) that include several chart on the same layout.
Every chart is based on a different dataset, which is based on the same Datasource – an OLAP cube.
The datasets were created using the new query builder in RS 2005 (from SSAS 2005 cube), in both of them, I have set the Quarter dimension as a filter and marked the Parameter checkbox to set it as parameter.

After creating the first dataset and defining the parameter- a new parameter was defined in the report. Now, after creating the second dataset and adding the same filter and defining it as parameter, I have found out the no other parameter was defined for me but the first one was overwritten…

Is it a bug?

Thanks,

Liran

Hi Liran,

I don't think this is a bug. While you are keep on adding the same filter for a various datasets, the dataset will point to the same filter and you will have only one parameter if you select the check box for the filter in the filter pane.

I implement the same kind of reports and it works fine.

Sincerley,

--Amde

|||

Yes, but what if I want a different parameter defined for the second dataset?
I have tried doing it manually: I defined a new dataset similar to the one already created for me, and moved out from graphic design mode in the second dataset to reference the second parameter in the MDX.
I got an error saying no such parameter is defined L…

Problem with defining 2 datasets against OLAP cube

Hello all,

Is it a bug?

Thanks,

Liran

Hi Liran,

I implement the same kind of reports and it works fine.

Sincerley,

--Amde

|||

Wednesday, March 21, 2012

Problem with GROUP BY and HAVING in query

Problem with Gray Scale Plaette

Problem with Gray Scale Plaette

Problem with Gray Scale Plaette

Problem with Gray Scale Plaette

Problem with Gray Scale Plaette

Saturday, February 25, 2012

Problem with defining 2 datasets against OLAP cube

Problem with defining 2 datasets against OLAP cube

db datatypes

Blog Archive

About Me