Reporting in SQL Server: sample

Question: why .Net Data provider does not work with Reporting Service.
I have created sample .Net Data Provider as recommented:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontemplatefiles.asp
I added string name and version number and added assembly into GAC.
Console test application works fine.
but Reporting service gives me error when I create DataSoure:
--
Microsoft Development Environment
--
A connection cannot be made to the database.
Set and test the connection string.
after [OK] press. I can se error message in the Data page: "The query could
not be loaded. Verify your connection and querry string."
Why?
I have modified
"C:\Program Files\Microsoft SQL Server\80\Tools\Report
Designer\RSReportDesigner.config"
<Data>
<Extension Name="TEMP"
Type="TemplateProvider.TemplateConnection,TemplateProvider,Version=1.2.1.1,
Culture=neutral, PublicKeyToken=b2520ee0b52bee09"/>
...
<Designer>
<Extension Name="TEMP"
Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.ReportingServices.Designer"/>
...
and
"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config"
<Data>
<Extension Name="TEMP"
Type="TemplateProvider.TemplateConnection,TemplateProvider,Version=1.2.1.1,
Culture=neutral, PublicKeyToken=b2520ee0b52bee09"/>
When I creating new DataSource I can see provider "TEMP", [Edit] is
disabled.
Please point me what can be wrong. I just want to test is Report Servises
works with .Net Data provider.
thanks in advance,
Darius DCurrently two dot net dataproviders are used by RS. SQL Server and Oracle.
Your other options are ODBC and Oledb providers (I have used both of these
as well as going to SQL Server).
What is it you are trying to do? If you want to do your own data provider
then a better way is to create a data extension. That is supported and many
people have done it. I know that there is some interface that has to be
supported for the data provider to work within the designer. The Sybase
dotnet provider did not support the needed interface and can not be used
within the designer.
Bruce L-C
"DD" <dd@.jbees.com> wrote in message
news:%23OylUKAnEHA.1296@.TK2MSFTNGP09.phx.gbl...
> Question: why .Net Data provider does not work with Reporting Service.
> I have created sample .Net Data Provider as recommented:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontemplatefiles.asp
> I added string name and version number and added assembly into GAC.
> Console test application works fine.
> but Reporting service gives me error when I create DataSoure:
> --
> Microsoft Development Environment
> --
> A connection cannot be made to the database.
> Set and test the connection string.
> after [OK] press. I can se error message in the Data page: "The query
could
> not be loaded. Verify your connection and querry string."
> Why?
>
> I have modified
> "C:\Program Files\Microsoft SQL Server\80\Tools\Report
> Designer\RSReportDesigner.config"
> <Data>
> <Extension Name="TEMP"
>
Type="TemplateProvider.TemplateConnection,TemplateProvider,Version=1.2.1.1,
> Culture=neutral, PublicKeyToken=b2520ee0b52bee09"/>
> ...
> <Designer>
> <Extension Name="TEMP"
>
Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti
ngServices.Designer"/>
> ...
> and
> "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer.config"
> <Data>
> <Extension Name="TEMP"
>
Type="TemplateProvider.TemplateConnection,TemplateProvider,Version=1.2.1.1,
> Culture=neutral, PublicKeyToken=b2520ee0b52bee09"/>
> When I creating new DataSource I can see provider "TEMP", [Edit] is
> disabled.
> Please point me what can be wrong. I just want to test is Report Servises
> works with .Net Data provider.
> thanks in advance,
> Darius D
>
>|||I got it working. I mean custom .Net data provider with Reporting service.
I dont want to use data extensions because I need to uses custom .net
provider and as I know reporting service should support that.
But I connot find information how to do it.
Now I have such problem:
When I use Generic Query Designer I can write select queries I need and I
get results I want.
But I want to start to use VDTQueryDesigner.
When I press on [Generic Query Designer] which I expect should query schema
I get error:
--
Microsoft Development Environment
--
The following exception has occurred:
NullReferenceException: Object reference not set to an instance of an
object.
--
OK
--
My custom .Net data Provider implements customDataReader where I pass
DataSet. DataSet I filling using such code:
DataSet pDataSet = new DataSet();
string aConnString = "User ID = USR; Persist Security Info=False;
database=DB; server=SRV; Connect Timeout=30";
SqlConnection aConnection = new SqlConnection( aConnString );
SqlCommand aCommand = new SqlCommand(sCmd, aConnection);
aConnection.Open();
SqlDataAdapter aSqlDataAdapter = new SqlDataAdapter();
aSqlDataAdapter.SelectCommand = aCommand;
if (pBehaviour == CommandBehavior.Default)
{
aSqlDataAdapter.Fill(pDataSet, "REZULT_TABLE");
}
else if (pBehaviour == CommandBehavior.SchemaOnly)
{
aSqlDataAdapter.FillSchema(pDataSet, SchemaType.Source);
}
//needs try/catch/finally
aConnection.Close();
return pDataSet;
When [Generic Query Designer] is clicked
aSqlDataAdapter.FillSchema(pDataSet, SchemaType.Source);
is executed. Custom DataReader loops over public Type GetFieldType(int i)
( implements IDataReader )
after if jumps to void IDisposable.Dispose() of custom DataReader and after
return of this function I got the exeption printed above.
I have the feeling I load not correct schema information or my custom Data
provider does not implement( or does it bad) all needed functions
Any comments?
Thanks in advance,
Darius D
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ua6vMRAnEHA.3820@.TK2MSFTNGP09.phx.gbl...
> Currently two dot net dataproviders are used by RS. SQL Server and Oracle.
> Your other options are ODBC and Oledb providers (I have used both of these
> as well as going to SQL Server).
> What is it you are trying to do? If you want to do your own data provider
> then a better way is to create a data extension. That is supported and
> many
> people have done it. I know that there is some interface that has to be
> supported for the data provider to work within the designer. The Sybase
> dotnet provider did not support the needed interface and can not be used
> within the designer.
> Bruce L-C
>
> "DD" <dd@.jbees.com> wrote in message
> news:%23OylUKAnEHA.1296@.TK2MSFTNGP09.phx.gbl...
>> Question: why .Net Data provider does not work with Reporting Service.
>> I have created sample .Net Data Provider as recommented:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontemplatefiles.asp
>> I added string name and version number and added assembly into GAC.
>> Console test application works fine.
>> but Reporting service gives me error when I create DataSoure:
>> --
>> Microsoft Development Environment
>> --
>> A connection cannot be made to the database.
>> Set and test the connection string.
>> after [OK] press. I can se error message in the Data page: "The query
> could
>> not be loaded. Verify your connection and querry string."
>> Why?
>>
>> I have modified
>> "C:\Program Files\Microsoft SQL Server\80\Tools\Report
>> Designer\RSReportDesigner.config"
>> <Data>
>> <Extension Name="TEMP"
> Type="TemplateProvider.TemplateConnection,TemplateProvider,Version=1.2.1.1,
>> Culture=neutral, PublicKeyToken=b2520ee0b52bee09"/>
>> ...
>> <Designer>
>> <Extension Name="TEMP"
> Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti
> ngServices.Designer"/>
>> ...
>> and
>> "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
>> Services\ReportServer\RSReportServer.config"
>> <Data>
>> <Extension Name="TEMP"
> Type="TemplateProvider.TemplateConnection,TemplateProvider,Version=1.2.1.1,
>> Culture=neutral, PublicKeyToken=b2520ee0b52bee09"/>
>> When I creating new DataSource I can see provider "TEMP", [Edit] is
>> disabled.
>> Please point me what can be wrong. I just want to test is Report Servises
>> works with .Net Data provider.
>> thanks in advance,
>> Darius D
>>
>|||All I know is that for a Sybase dotnet managed provider to work in the
development environment it needed to implement the Reporting Services Data
Extension interface.
One other point that I know is true at least for the Oracle managed
provider. From the development UI the Oracle managed provider is only used
from the Generic Query designer. Otherwise it uses the Oledb provider. The
reason for this is that RS is that the query designer they are using does
not natively support dotnet managed providers so that is why the managed
provider is only used from the generic query designer. I would assume the
same would hold true for your custome managed provider.
Bruce L-C
"DD" <dd@.jbees.com> wrote in message
news:eWZd9TynEHA.628@.TK2MSFTNGP10.phx.gbl...
> I got it working. I mean custom .Net data provider with Reporting service.
> I dont want to use data extensions because I need to uses custom .net
> provider and as I know reporting service should support that.
> But I connot find information how to do it.
> Now I have such problem:
> When I use Generic Query Designer I can write select queries I need and I
> get results I want.
> But I want to start to use VDTQueryDesigner.
> When I press on [Generic Query Designer] which I expect should query
schema
> I get error:
> --
> Microsoft Development Environment
> --
> The following exception has occurred:
> NullReferenceException: Object reference not set to an instance of an
> object.
> --
> OK
> --
> My custom .Net data Provider implements customDataReader where I pass
> DataSet. DataSet I filling using such code:
> DataSet pDataSet = new DataSet();
> string aConnString = "User ID = USR; Persist Security Info=False;
> database=DB; server=SRV; Connect Timeout=30";
> SqlConnection aConnection = new SqlConnection( aConnString );
> SqlCommand aCommand = new SqlCommand(sCmd, aConnection);
> aConnection.Open();
> SqlDataAdapter aSqlDataAdapter = new SqlDataAdapter();
> aSqlDataAdapter.SelectCommand = aCommand;
> if (pBehaviour == CommandBehavior.Default)
> {
> aSqlDataAdapter.Fill(pDataSet, "REZULT_TABLE");
> }
> else if (pBehaviour == CommandBehavior.SchemaOnly)
> {
> aSqlDataAdapter.FillSchema(pDataSet, SchemaType.Source);
> }
> //needs try/catch/finally
> aConnection.Close();
> return pDataSet;
> When [Generic Query Designer] is clicked
> aSqlDataAdapter.FillSchema(pDataSet, SchemaType.Source);
> is executed. Custom DataReader loops over public Type GetFieldType(int i)
> ( implements IDataReader )
> after if jumps to void IDisposable.Dispose() of custom DataReader and
after
> return of this function I got the exeption printed above.
> I have the feeling I load not correct schema information or my custom Data
> provider does not implement( or does it bad) all needed functions
> Any comments?
> Thanks in advance,
> Darius D
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:ua6vMRAnEHA.3820@.TK2MSFTNGP09.phx.gbl...
> > Currently two dot net dataproviders are used by RS. SQL Server and
Oracle.
> > Your other options are ODBC and Oledb providers (I have used both of
these
> > as well as going to SQL Server).
> >
> > What is it you are trying to do? If you want to do your own data
provider
> > then a better way is to create a data extension. That is supported and
> > many
> > people have done it. I know that there is some interface that has to be
> > supported for the data provider to work within the designer. The Sybase
> > dotnet provider did not support the needed interface and can not be used
> > within the designer.
> >
> > Bruce L-C
> >
> >
> > "DD" <dd@.jbees.com> wrote in message
> > news:%23OylUKAnEHA.1296@.TK2MSFTNGP09.phx.gbl...
> >> Question: why .Net Data provider does not work with Reporting Service.
> >>
> >> I have created sample .Net Data Provider as recommented:
> >>
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontemplatefiles.asp
> >>
> >> I added string name and version number and added assembly into GAC.
> >> Console test application works fine.
> >>
> >> but Reporting service gives me error when I create DataSoure:
> >> --
> >> Microsoft Development Environment
> >> --
> >> A connection cannot be made to the database.
> >> Set and test the connection string.
> >>
> >> after [OK] press. I can se error message in the Data page: "The query
> > could
> >> not be loaded. Verify your connection and querry string."
> >>
> >> Why?
> >>
> >>
> >> I have modified
> >> "C:\Program Files\Microsoft SQL Server\80\Tools\Report
> >> Designer\RSReportDesigner.config"
> >> <Data>
> >> <Extension Name="TEMP"
> >>
> >
Type="TemplateProvider.TemplateConnection,TemplateProvider,Version=1.2.1.1,
> >> Culture=neutral, PublicKeyToken=b2520ee0b52bee09"/>
> >> ...
> >> <Designer>
> >> <Extension Name="TEMP"
> >>
> >
Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti
> > ngServices.Designer"/>
> >> ...
> >>
> >> and
> >> "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> >> Services\ReportServer\RSReportServer.config"
> >> <Data>
> >> <Extension Name="TEMP"
> >>
> >
Type="TemplateProvider.TemplateConnection,TemplateProvider,Version=1.2.1.1,
> >> Culture=neutral, PublicKeyToken=b2520ee0b52bee09"/>
> >>
> >> When I creating new DataSource I can see provider "TEMP", [Edit] is
> >> disabled.
> >>
> >> Please point me what can be wrong. I just want to test is Report
Servises
> >> works with .Net Data provider.
> >>
> >> thanks in advance,
> >> Darius D
> >>
> >>
> >>
> >
> >
>

I am using SQL Server 2005 Express Edition.
I have a table whose structure is follows:
Date
EmpID
Code
Amount
Sample data looks like this:
---
Date EMPID Code Amount
---
1-Jul-07 1 A1 100
1-Jul-07 1 B1 100
1-Jul-07 2 A1 150
1-Jul-07 3 B1 50
1-Jul-07 4 C1 120
1-Jul-07 4 D1 80
----
The Codes are not fixed, they can be of any number and varies from
employee to employee.
I want to show a report like this:
Month: Jul-07 -- CODES --
---
EmpID A1 B1 C1 D1
---
1 100 100 0 0
2 150 0 0 0
3 0 50 0 0
4 0 0 120 80
----
Since Codes are not fixed, so an SQL query is difficult. What is the
alternative approach?
I am thinking to create another table at run-time that reads column
values (Code names) from the original table and create a field of that
name in the next table.
I also want to know in which edition of SQL Server is reporting
included. How SQL Server Reporting compares with Crystal Reports?
Secondly, while generating reports from Windows OS on a Dot-Matrix
Printer, printing is slow. How can I print at a speed same as in DOS?Hi
create table #t (id int not null primary key,
date datetime,empid int,code char(2),
amount int)
insert into #t values (1,'20070701',1,'A1',100)
insert into #t values (2,'20070701',1,'B1',100)
insert into #t values (3,'20070701',2,'A1',150)
insert into #t values (4,'20070701',3,'B1',50)
insert into #t values (5,'20070701',4,'C1',120)
insert into #t values (6,'20070701',4,'D1',80)
select empid, max([A1])A1, max([B1])B1, max([C1])C1, max([D1])D1 from #t as
t1
pivot
(
max(amount)
for code IN([A1], [B1], [C1], [D1])
) AS pvt
group by empid
"RP" <rpk.general@.gmail.com> wrote in message
news:1188283745.388192.128690@.i13g2000prf.googlegroups.com...
>I am using SQL Server 2005 Express Edition.
> I have a table whose structure is follows:
> Date
> EmpID
> Code
> Amount
> Sample data looks like this:
> ---
> Date EMPID Code Amount
> ---
> 1-Jul-07 1 A1 100
> 1-Jul-07 1 B1 100
> 1-Jul-07 2 A1 150
> 1-Jul-07 3 B1 50
> 1-Jul-07 4 C1 120
> 1-Jul-07 4 D1 80
> ----
> The Codes are not fixed, they can be of any number and varies from
> employee to employee.
> I want to show a report like this:
>
> Month: Jul-07 -- CODES --
> ---
> EmpID A1 B1 C1 D1
> ---
> 1 100 100 0 0
> 2 150 0 0 0
> 3 0 50 0 0
> 4 0 0 120 80
> ----
> Since Codes are not fixed, so an SQL query is difficult. What is the
> alternative approach?
> I am thinking to create another table at run-time that reads column
> values (Code names) from the original table and create a field of that
> name in the next table.
> I also want to know in which edition of SQL Server is reporting
> included. How SQL Server Reporting compares with Crystal Reports?
> Secondly, while generating reports from Windows OS on a Dot-Matrix
> Printer, printing is slow. How can I print at a speed same as in DOS?
>

Monday, March 26, 2012

Reporting Services 2005 - Security Extention

Wednesday, March 7, 2012

Reporting Service and .Net Data provider

Reporting Server Log

Tuesday, February 21, 2012

Reporting like this

Reporting in SQL Server

Blog Archive

About Me