Funky Problem with Invisible Chart

So, I'm pretty new to Reporting Services and this is my first post in this forum. So basically I was creating a report which contains a chart. I configure the dataset, chart values, etc and the chart shows up blank. The title of the chart shows, but nothing else shows. The way I created the report in the designer is no different that another report that actually works.

I've searched google with every word I could think of and I haven't been able to find this issue anywhere. Is this some crazy Microsoft hiccup?

Here is the Stored Procedure that I am using as my dataset:

CREATE PROCEDURE YearlyTotalsInPercentages(@.Yearint) ASBEGINDECLARE @.TotalSumintSELECT SUM(dbo.Main.Hours) AS CBDCYearlyTotals, dbo.Project.ProductLine AS ProductLineINTO #tempTotalsFROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE'CI%' AND dbo.Project.ControlLocation ='IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @.Year AND dbo.Main.Active = 1GROUP BY dbo.Project.ProductLine SET @.TotalSum = (SELECT SUM(dbo.Main.Hours)FROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE'CI%' AND dbo.Project.ControlLocation ='IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @.Year AND dbo.Main.Active = 1) SELECT t.CBDCYearlyTotals AS CBDCYearlyTotals, t.ProductLine AS ProductLine, ROUND((t.CBDCYearlyTotals/@.TotalSum) * 100, 1) AS Percentage FROM #tempTotals tENDGO

I can present the rdl if necessary.

Muchos Gracias

So, in doing deeper searching I found another forum that said temp tables may not be supported and that table variables should be used instead. So, I'm attempting that route, but running into problems with the query when it comes to dividing by @.TotalSum. It returns 0 now and not the number that I had gotten previously.

Just one road block after the other. Ah, the joys of programming.

Anybody got any clues as to why this would happen?

Function Returning error during compilation.....

Hi ,

I am creating a function which is going to return a table. The Code ofr the function is as follows...
===============================
Create function udf_qcard (@.cg1 varchar(25)) returns @.rec_card table (t_cusip varchar(10),t_data varchar(70))
AS
begin
declare @.t1_sys char(10),@.t1_all varchar(11)
declare @.temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS)
if (substring(@.cg1,1,2)='Q$')
set @.cg1 = (select substring(@.cg1,3,len(@.cg1)) where substring(@.cg1,1,2)='Q$')
DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
insert into @.temp_qcard values(@.cg1)
OPEN C1
FETCH NEXT FROM c1 INTO @.t1_sys,@.t1_all
WHILE @.@.FETCH_STATUS = 0
BEGIN

insert into @.temp_qcard values(@.t1_all)

declare @.t2_sys char(10),@.t2_all varchar(10)
DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin
OPEN C2
FETCH NEXT FROM c2 INTO @.t2_sys,@.t2_all
WHILE @.@.FETCH_STATUS = 0
BEGIN
insert into @.temp_qcard values(@.t2_all)

declare @.t3_sys char(10),@.t3_all varchar(10)
DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin

OPEN C3
FETCH NEXT FROM c3 INTO @.t3_sys,@.t3_all
WHILE @.@.FETCH_STATUS = 0
BEGIN
insert into @.temp_qcard values(@.t3_all)
FETCH NEXT FROM c3 INTO @.t3_sys,@.t3_all
end
end
close c3
deallocate c3
FETCH NEXT FROM c2 INTO @.t2_sys,@.t2_all
end
end
close c2
DEALLOCATE c2

FETCH NEXT FROM c1 INTO @.t1_sys,@.t1_all
END

CLOSE c1
DEALLOCATE c1
Insert @.rec_card select groups_q+groups_cusip,groups_data from tbl_groups
where groups_system in (select tdata from @.temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata

RETURN
END
==========================

While compiling this I am getting the Below error ...
==================
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35
Mixing old and new syntax to specify cursor options is not allowed.
=================

Can Anyone please help me how to resolve this issue...

Thanks with Regards.

-Mohit.Try changing the declaration of all your cursors like this

DECLARE c1 SCROLL CURSOR READ_ONLY FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.cg1 and groups_alldata like 'Q$%' and groups_seq>=1

Dont write "FOR READ_ONLY" at the end of the DECLARE. Instead, write it before the "FOR select...."

Function default parameter

I am creating a udh function that requires to know the current date.
Since GetDate() can't be used inside a UDF I thought
I would use a date parameter with a default value of GetDate()
i.e.:
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate()
) Returns
or
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = (Select GetDate())
) Returns
These do not even compile
However
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate
) Returns
Does but when I call it it
Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
It tells me that string cannot be converted to a date
Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
Syntax error converting datetime from character string.
Any Idea...
Thanks
Fred
Hi Fred,
You can't use a function as the default value for a parameter, only
literals. You can use a view that just returns the value of GETDATE() for
example:
CREATE VIEW vw_getdate
AS
SELECT GETDATE() AS getdate
but the view will be accessed each time the function is executed, which
means that you can end up with having different values for each execution.
The safest is not to use a default value and just pass in the value for
GETDATE().
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:7315A12D-37ED-4F5F-8855-2DFE1F2153E6@.microsoft.com...
>I am creating a udh function that requires to know the current date.
> Since GetDate() can't be used inside a UDF I thought
> I would use a date parameter with a default value of GetDate()
> i.e.:
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate()
> ) Returns
> or
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = (Select GetDate())
> ) Returns
> These do not even compile
> However
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate
> ) Returns
> Does but when I call it it
> Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
> It tells me that string cannot be converted to a date
> Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
> Syntax error converting datetime from character string.
> Any Idea...
>
> Thanks
>
>
> --
> Fred

Function default parameter

I am creating a udh function that requires to know the current date.
Since GetDate() can't be used inside a UDF I thought
I would use a date parameter with a default value of GetDate()
i.e.:
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate()
) Returns
or
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = (Select GetDate())
) Returns
These do not even compile
However
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate
) Returns
Does but when I call it it
Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
It tells me that string cannot be converted to a date
Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
Syntax error converting datetime from character string.
Any Idea...
Thanks
--
FredHi Fred,
You can't use a function as the default value for a parameter, only
literals. You can use a view that just returns the value of GETDATE() for
example:
CREATE VIEW vw_getdate
AS
SELECT GETDATE() AS getdate
but the view will be accessed each time the function is executed, which
means that you can end up with having different values for each execution.
The safest is not to use a default value and just pass in the value for
GETDATE().
--
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:7315A12D-37ED-4F5F-8855-2DFE1F2153E6@.microsoft.com...
>I am creating a udh function that requires to know the current date.
> Since GetDate() can't be used inside a UDF I thought
> I would use a date parameter with a default value of GetDate()
> i.e.:
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate()
> ) Returns
> or
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = (Select GetDate())
> ) Returns
> These do not even compile
> However
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate
> ) Returns
> Does but when I call it it
> Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
> It tells me that string cannot be converted to a date
> Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
> Syntax error converting datetime from character string.
> Any Idea...
>
> Thanks
>
>
> --
> Fred

Function - List Windows Group Members

Hello!
I thinking of creating a SQL Server clr function that works like this.
Input paremeter: A Windows group, e.g. Domain1\Group1
Resultset: A list of all accounts that directely or indirectely belongs to
that Windows group.
e.g.
Domain1\Account1
Domain1\Account2
Domain1\Account3
The idea is to use this together with the security catalog views to get a
list of accounts that is for example sysadmins.
Could it be that a function like this exist in SQL Server?
I also thought about what AD permisssions that would be needed?
Best regards
Ola Hallengren
Hi
You can create an ADSI linked server and query the active directory, but
recursing through the groups not always that easy. There is a script to list
all group members on http://www.rlmueller.net/freecode3.htm
John
"Ola Hallengren" wrote:

> Hello!
> I thinking of creating a SQL Server clr function that works like this.
> Input paremeter: A Windows group, e.g. Domain1\Group1
> Resultset: A list of all accounts that directely or indirectely belongs to
> that Windows group.
> e.g.
> Domain1\Account1
> Domain1\Account2
> Domain1\Account3
> The idea is to use this together with the security catalog views to get a
> list of accounts that is for example sysadmins.
> Could it be that a function like this exist in SQL Server?
> I also thought about what AD permisssions that would be needed?
> Best regards
> Ola Hallengren
|||Thanks, John. As I understand it I have about these options.
1. Create an ADSI linked server and query against that with TSQL. This can
be packaged into a SQL Server function.
2. Use the VBScript at http://www.rlmueller.net/freecode3.htm from
xp_cmdshell. This can be packaged into a stored procedure.
3. Create some CLR code with the same logic as in the VBScript and use that
assambly in a SQL Server function.
Is this right?
/Ola
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You can create an ADSI linked server and query the active directory, but
> recursing through the groups not always that easy. There is a script to list
> all group members on http://www.rlmueller.net/freecode3.htm
> John
> "Ola Hallengren" wrote:
|||I think so, I would opt for either 2 or 3 but have never tried 3!.
John
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:05FC89C1-3816-41B9-9123-5D9E4FFBEFC0@.microsoft.com...[vbcol=seagreen]
> Thanks, John. As I understand it I have about these options.
> 1. Create an ADSI linked server and query against that with TSQL. This can
> be packaged into a SQL Server function.
> 2. Use the VBScript at http://www.rlmueller.net/freecode3.htm from
> xp_cmdshell. This can be packaged into a stored procedure.
> 3. Create some CLR code with the same logic as in the VBScript and use
> that
> assambly in a SQL Server function.
> Is this right?
> /Ola
>
> "John Bell" wrote:

Function - List Windows Group Members

Hello!
I thinking of creating a SQL Server clr function that works like this.
Input paremeter: A Windows group, e.g. Domain1\Group1
Resultset: A list of all accounts that directely or indirectely belongs to
that Windows group.
e.g.
Domain1\Account1
Domain1\Account2
Domain1\Account3
The idea is to use this together with the security catalog views to get a
list of accounts that is for example sysadmins.
Could it be that a function like this exist in SQL Server?
I also thought about what AD permisssions that would be needed?
Best regards
Ola HallengrenHi
You can create an ADSI linked server and query the active directory, but
recursing through the groups not always that easy. There is a script to list
all group members on http://www.rlmueller.net/freecode3.htm
John
"Ola Hallengren" wrote:
> Hello!
> I thinking of creating a SQL Server clr function that works like this.
> Input paremeter: A Windows group, e.g. Domain1\Group1
> Resultset: A list of all accounts that directely or indirectely belongs to
> that Windows group.
> e.g.
> Domain1\Account1
> Domain1\Account2
> Domain1\Account3
> The idea is to use this together with the security catalog views to get a
> list of accounts that is for example sysadmins.
> Could it be that a function like this exist in SQL Server?
> I also thought about what AD permisssions that would be needed?
> Best regards
> Ola Hallengren|||Thanks, John. As I understand it I have about these options.
1. Create an ADSI linked server and query against that with TSQL. This can
be packaged into a SQL Server function.
2. Use the VBScript at http://www.rlmueller.net/freecode3.htm from
xp_cmdshell. This can be packaged into a stored procedure.
3. Create some CLR code with the same logic as in the VBScript and use that
assambly in a SQL Server function.
Is this right?
/Ola
"John Bell" wrote:
> Hi
> You can create an ADSI linked server and query the active directory, but
> recursing through the groups not always that easy. There is a script to list
> all group members on http://www.rlmueller.net/freecode3.htm
> John
> "Ola Hallengren" wrote:
> > Hello!
> >
> > I thinking of creating a SQL Server clr function that works like this.
> >
> > Input paremeter: A Windows group, e.g. Domain1\Group1
> >
> > Resultset: A list of all accounts that directely or indirectely belongs to
> > that Windows group.
> >
> > e.g.
> > Domain1\Account1
> > Domain1\Account2
> > Domain1\Account3
> >
> > The idea is to use this together with the security catalog views to get a
> > list of accounts that is for example sysadmins.
> >
> > Could it be that a function like this exist in SQL Server?
> >
> > I also thought about what AD permisssions that would be needed?
> >
> > Best regards
> >
> > Ola Hallengren|||I think so, I would opt for either 2 or 3 but have never tried 3!.
John
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:05FC89C1-3816-41B9-9123-5D9E4FFBEFC0@.microsoft.com...
> Thanks, John. As I understand it I have about these options.
> 1. Create an ADSI linked server and query against that with TSQL. This can
> be packaged into a SQL Server function.
> 2. Use the VBScript at http://www.rlmueller.net/freecode3.htm from
> xp_cmdshell. This can be packaged into a stored procedure.
> 3. Create some CLR code with the same logic as in the VBScript and use
> that
> assambly in a SQL Server function.
> Is this right?
> /Ola
>
> "John Bell" wrote:
>> Hi
>> You can create an ADSI linked server and query the active directory, but
>> recursing through the groups not always that easy. There is a script to
>> list
>> all group members on http://www.rlmueller.net/freecode3.htm
>> John
>> "Ola Hallengren" wrote:
>> > Hello!
>> >
>> > I thinking of creating a SQL Server clr function that works like this.
>> >
>> > Input paremeter: A Windows group, e.g. Domain1\Group1
>> >
>> > Resultset: A list of all accounts that directely or indirectely belongs
>> > to
>> > that Windows group.
>> >
>> > e.g.
>> > Domain1\Account1
>> > Domain1\Account2
>> > Domain1\Account3
>> >
>> > The idea is to use this together with the security catalog views to get
>> > a
>> > list of accounts that is for example sysadmins.
>> >
>> > Could it be that a function like this exist in SQL Server?
>> >
>> > I also thought about what AD permisssions that would be needed?
>> >
>> > Best regards
>> >
>> > Ola Hallengren