Data SubSet

Is it possible to perform an SQL query on a ReportingServices DataSet... I
would like to write a statement like this:
SELECT COUNT(DISTINCT ServiceID) FROM RS_DataSet WHERE ServiceStatus = "ACTIVE"
I know it would be easy to create a new DataSet with this query... however
RS it getting this table from an application... not directly from a
database...Couldn't you just filter the dataset that you already have?
"MER78" wrote:
> Is it possible to perform an SQL query on a ReportingServices DataSet... I
> would like to write a statement like this:
> SELECT COUNT(DISTINCT ServiceID) FROM RS_DataSet WHERE ServiceStatus => "ACTIVE"
> I know it would be easy to create a new DataSet with this query... however
> RS it getting this table from an application... not directly from a
> database...|||Well that is what I was talking about... but I can't figure out how I would
do that without making invisible tables that filter and count for each
condition... the table I have displaying my DataSet is supposed to show all
the data... but counting say ACTIVE records... would be a subset of that
DataSet.
"B. Mark McKinney" wrote:
> Couldn't you just filter the dataset that you already have?|||I figured out a solution for my problem... with a little help from Google...
for anyone that might b interested...
=CountDistinct(IIF(Fields!ServiceStatus.Value = 1, Fields!ServiceID.Value,
NOTHING))
"MER78" wrote:
> Is it possible to perform an SQL query on a ReportingServices DataSet... I
> would like to write a statement like this:
> SELECT COUNT(DISTINCT ServiceID) FROM RS_DataSet WHERE ServiceStatus => "ACTIVE"
> I know it would be easy to create a new DataSet with this query... however
> RS it getting this table from an application... not directly from a
> database...

Data Sources

Is there anyway to use a Class File (VB.Net or C# class that generates a
DataSet) as the data source for a SQL Server 2005 Reporting Services report?
If there is, where are some examples or how-to's?
The only obvious way I see to generate a Data Source for a SQL report is a
sproc or dynamic query.
Thank You.Alex,
You want to use a Custom Data Processing Extension. You can even tie
the extension to an existing data layer.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
Andy Potter

Data source problem with data-driven subscription

I have a stored procedure that receives an input parameter of ReporID and then sends back the appropriate dataset to the data-driven subscription. In the stored procedure, there is some concatenated sql in producting the final select statement to send to the subscription, but it is generating the following error:

The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Get Online Help

Insert Error: Column name or number of supplied values does not match table definition.

Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation. My only other option is to have hundreds of individual tables to source all our reports, which I don't have time to manage.

Please let me know how I can allow the data-driven subscription setup process to discover the columns for my concatenated SQL so I can seutp my schedules based on the single stored procedure with all my logic nested in my table structure.

Thanks.

If anyone has an answer, even that I can't accomplish what I want to, I would love to hear it. Otherwise, I will have to march down a very high-maintenance road that will probalby encompass more objects than I can maintain along with my other responsibilities.

I appreciate any help you can offer.

|||>>Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation.

Does the result of the concatenation always result in the same number of columns according to your report definition, or could it possibly be creating the wrong result sometimes?

I do understand that your report has optional columns. However the dataset as defined in the report does have a set number of columns, some of which you presumably suppress for some runs. I'm saying that the stored proc may have to include placeholders for those columns in cases where they are not needed, to match up with what the RDL has defined.

Does that make sense? I'm not 100% sure that this is your problem, from your description, but it seems like a likely cause.

>L<

|||

Lisa, thanks for the response. I understand what you are saying but my solution is much more complex... but designed for very simple administration once the backend work is done.

I have (for starters) 76 reports x 50 client databases x ? custom scheduling requests with unique parameters. I am facilitating all of the interactive reporting and scheduled reporting through one copy of the 76 reports.

In addition, to minimize the number of objects to maintian I have a single stored procedure that is designed to act as a data source for all data-driven subscriptions for any report, receiving in some combination of ReportID and SettingGroup. This allows me to use structure to drive the entire operation and later develop an application front end where these complex schedule setups can be managed by anyone with the business knowledge to modify boolean values and make selections in an interface.

The complexity enters with the input parameters for reports which vary greatly. Some reports require no input parameters and always use default values coded into the report. Others may use two specific input parameters in one schedule and just one (with default on others) for a different schedule. Plus the input parameters for different reports are notably going to be different based on the nature of the report.

I have addressed input parameters in a way that requires no manual schema administration. The parameter setting names, values, and datatypes are stored in rows, one row per column name, and identified by the ReportID, SettingGroup, and ClientCode. When the stored procedure for the data source of the data-driven subscription executes, it checks for any related parameter setting records. If they exist, it creates a table on the fly, converting the row values for column definitions/values into actual columns of data. Then the concatenated SQL selects the standard columns plus any parameter settings as needed.

It all works perfectly, except that the "schema discovery" doesn't work in the data-driven subscription setup process. If i could work around that somehow, I would be set. Otherwise, i will probably be forced to have my process dynamically create a table for every report using the same stored procedure. The problem with this solution is that I can't execute the stored procedure at run time for the report so that the data is fully updated with any setting changes or ClientCode activiation/inactivations before the report runs.

This may be too much information, but I'm just frustrated because I'm sitting on the edge of completion for an elegant design that will make it possible for me to manage all these subscriptions while fulfilling my DBA, System Admin, and Developer roles. Otherwise, I fear a lot of unpaid overtime in my future.

If there is a way for me to trigger the stored procedure to run before the data-driven subscriptions run against a static table that will work too. I just don't know if that is an option.

Thanks in advance for your help.

|||

HI there,

I do appreciate the complexity of your situation and honestly didn't mean to minimize it by trying to explore how and what wasn't working out in a simple way.

The important thing to remember is that all complexity must be sieved down to a pure problem and most wide architecture has to be funnelled into a narrow place when it comes to integrating your work with some standardized, simplified solution (which is what RS data subscriptions is).

As I see it there are two ways we can go about this:

1) You can tell us more about your architecture and provide a simplified, reproducible version that, while not sharing all the features of your app, does serve to illustrate the place at which things go wrong with subscriptions. Then we can kick it around and try to see what changes would serve to "jimmy" the thing to work as you need.

In the process we may suggest things that will not work in your more fully-worked scenarios, and this may frustrate you further -- please be patient with us if this happens <s>.

We may also suggest architectural changes that may be extremely frustrating, even though we understand you're really happy with what you've come up with (except for this one piece)... and we'll understand if you reject those solutions.

2) You can accept the fact that RS data subscriptions are only one way of handling what you need to do, and not the best way at that. I use them, but also do *not* use them where they do not fit a business need or where my framework seems to "stretch" them in ways they were not meant to stretch.

As I have described on another thread (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019010&SiteID=1) there are many scenarios in which, quite frankly, RS data subscriptions simply do not do what one wants to do, whether in terms of exception reporting, subscription channels, or whatever -- and it is a lot better to roll your own.

It seems to me that your scenario is one of them, and that SSIS is a good fit here. You want to run a stored proc before running the report, go for it.

Just remember that RS data subscriptions are only one potential flow through which scheduled reports can be managed and this is really not a "one size fits all" type of story. The actual elements of the story can be endlessly recombined (see that thread) and each task element is really pretty simple to do so from my POV RS data subscriptions isn't adding all that much. If it's getting in your way, jettison it.

I hope this helps.

>L<

|||

First, I didn't mean to imply your were minimizing the complexity of my situation. I know you were just responding to a minimally defined issue that I threw out there, and it is appreciated.

Second, you are my hero! I'm a huge fan of SSIS and use it often for looping automation of tasks as well as data flows. It never occured to me to use it for this, but after looking at your other forum post and your script code, I see that this is probably the road for me. It allows me the flexibility to use very open ended, branching logic so I can handle all exceptions in one package making for easier management. I can even consolidate an FTP SSIS package I created for report delivery into this one package for an all encompassing process.

Thank you for your time and insight.

Sincerely,

Hugh

|||

Good for you! The important thing is that you are willing to do the work and you see the possibilities in the tools. It's people who think that there is something holy about the shortcuts presented along with the base tool set -- again, IMHO, data subscriptions is just one such shortcut -- that really have problems.

One needs to see that the shortcuts provide you with insight into the *potential* of the tools, not the only possible end result... you clearly get this, and you'll do fine!

Regards,

>L<

Data source problem with data-driven subscription

I have a stored procedure that receives an input parameter of ReporID and then sends back the appropriate dataset to the data-driven subscription. In the stored procedure, there is some concatenated sql in producting the final select statement to send to the subscription, but it is generating the following error:

The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Get Online Help

Insert Error: Column name or number of supplied values does not match table definition.

Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation. My only other option is to have hundreds of individual tables to source all our reports, which I don't have time to manage.

Please let me know how I can allow the data-driven subscription setup process to discover the columns for my concatenated SQL so I can seutp my schedules based on the single stored procedure with all my logic nested in my table structure.

Thanks.

If anyone has an answer, even that I can't accomplish what I want to, I would love to hear it. Otherwise, I will have to march down a very high-maintenance road that will probalby encompass more objects than I can maintain along with my other responsibilities.

I appreciate any help you can offer.

|||>>Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation.

Does the result of the concatenation always result in the same number of columns according to your report definition, or could it possibly be creating the wrong result sometimes?

I do understand that your report has optional columns. However the dataset as defined in the report does have a set number of columns, some of which you presumably suppress for some runs. I'm saying that the stored proc may have to include placeholders for those columns in cases where they are not needed, to match up with what the RDL has defined.

Does that make sense? I'm not 100% sure that this is your problem, from your description, but it seems like a likely cause.

>L<

|||

Lisa, thanks for the response. I understand what you are saying but my solution is much more complex... but designed for very simple administration once the backend work is done.

I have (for starters) 76 reports x 50 client databases x ? custom scheduling requests with unique parameters. I am facilitating all of the interactive reporting and scheduled reporting through one copy of the 76 reports.

In addition, to minimize the number of objects to maintian I have a single stored procedure that is designed to act as a data source for all data-driven subscriptions for any report, receiving in some combination of ReportID and SettingGroup. This allows me to use structure to drive the entire operation and later develop an application front end where these complex schedule setups can be managed by anyone with the business knowledge to modify boolean values and make selections in an interface.

The complexity enters with the input parameters for reports which vary greatly. Some reports require no input parameters and always use default values coded into the report. Others may use two specific input parameters in one schedule and just one (with default on others) for a different schedule. Plus the input parameters for different reports are notably going to be different based on the nature of the report.

I have addressed input parameters in a way that requires no manual schema administration. The parameter setting names, values, and datatypes are stored in rows, one row per column name, and identified by the ReportID, SettingGroup, and ClientCode. When the stored procedure for the data source of the data-driven subscription executes, it checks for any related parameter setting records. If they exist, it creates a table on the fly, converting the row values for column definitions/values into actual columns of data. Then the concatenated SQL selects the standard columns plus any parameter settings as needed.

It all works perfectly, except that the "schema discovery" doesn't work in the data-driven subscription setup process. If i could work around that somehow, I would be set. Otherwise, i will probably be forced to have my process dynamically create a table for every report using the same stored procedure. The problem with this solution is that I can't execute the stored procedure at run time for the report so that the data is fully updated with any setting changes or ClientCode activiation/inactivations before the report runs.

This may be too much information, but I'm just frustrated because I'm sitting on the edge of completion for an elegant design that will make it possible for me to manage all these subscriptions while fulfilling my DBA, System Admin, and Developer roles. Otherwise, I fear a lot of unpaid overtime in my future.

If there is a way for me to trigger the stored procedure to run before the data-driven subscriptions run against a static table that will work too. I just don't know if that is an option.

Thanks in advance for your help.

|||

HI there,

I do appreciate the complexity of your situation and honestly didn't mean to minimize it by trying to explore how and what wasn't working out in a simple way.

The important thing to remember is that all complexity must be sieved down to a pure problem and most wide architecture has to be funnelled into a narrow place when it comes to integrating your work with some standardized, simplified solution (which is what RS data subscriptions is).

As I see it there are two ways we can go about this:

1) You can tell us more about your architecture and provide a simplified, reproducible version that, while not sharing all the features of your app, does serve to illustrate the place at which things go wrong with subscriptions. Then we can kick it around and try to see what changes would serve to "jimmy" the thing to work as you need.

In the process we may suggest things that will not work in your more fully-worked scenarios, and this may frustrate you further -- please be patient with us if this happens <s>.

We may also suggest architectural changes that may be extremely frustrating, even though we understand you're really happy with what you've come up with (except for this one piece)... and we'll understand if you reject those solutions.

2) You can accept the fact that RS data subscriptions are only one way of handling what you need to do, and not the best way at that. I use them, but also do *not* use them where they do not fit a business need or where my framework seems to "stretch" them in ways they were not meant to stretch.

As I have described on another thread (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019010&SiteID=1) there are many scenarios in which, quite frankly, RS data subscriptions simply do not do what one wants to do, whether in terms of exception reporting, subscription channels, or whatever -- and it is a lot better to roll your own.

It seems to me that your scenario is one of them, and that SSIS is a good fit here. You want to run a stored proc before running the report, go for it.

Just remember that RS data subscriptions are only one potential flow through which scheduled reports can be managed and this is really not a "one size fits all" type of story. The actual elements of the story can be endlessly recombined (see that thread) and each task element is really pretty simple to do so from my POV RS data subscriptions isn't adding all that much. If it's getting in your way, jettison it.

I hope this helps.

>L<

|||

First, I didn't mean to imply your were minimizing the complexity of my situation. I know you were just responding to a minimally defined issue that I threw out there, and it is appreciated.

Second, you are my hero! I'm a huge fan of SSIS and use it often for looping automation of tasks as well as data flows. It never occured to me to use it for this, but after looking at your other forum post and your script code, I see that this is probably the road for me. It allows me the flexibility to use very open ended, branching logic so I can handle all exceptions in one package making for easier management. I can even consolidate an FTP SSIS package I created for report delivery into this one package for an all encompassing process.

Thank you for your time and insight.

Sincerely,

Hugh

|||

Good for you! The important thing is that you are willing to do the work and you see the possibilities in the tools. It's people who think that there is something holy about the shortcuts presented along with the base tool set -- again, IMHO, data subscriptions is just one such shortcut -- that really have problems.

One needs to see that the shortcuts provide you with insight into the *potential* of the tools, not the only possible end result... you clearly get this, and you'll do fine!

Regards,

>L<

Data source problem with data-driven subscription

I have a stored procedure that receives an input parameter of ReporID and then sends back the appropriate dataset to the data-driven subscription. In the stored procedure, there is some concatenated sql in producting the final select statement to send to the subscription, but it is generating the following error:

The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Get Online Help

Insert Error: Column name or number of supplied values does not match table definition.

Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation. My only other option is to have hundreds of individual tables to source all our reports, which I don't have time to manage.

Please let me know how I can allow the data-driven subscription setup process to discover the columns for my concatenated SQL so I can seutp my schedules based on the single stored procedure with all my logic nested in my table structure.

Thanks.

If anyone has an answer, even that I can't accomplish what I want to, I would love to hear it. Otherwise, I will have to march down a very high-maintenance road that will probalby encompass more objects than I can maintain along with my other responsibilities.

I appreciate any help you can offer.

|||>>Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation.

Does the result of the concatenation always result in the same number of columns according to your report definition, or could it possibly be creating the wrong result sometimes?

I do understand that your report has optional columns. However the dataset as defined in the report does have a set number of columns, some of which you presumably suppress for some runs. I'm saying that the stored proc may have to include placeholders for those columns in cases where they are not needed, to match up with what the RDL has defined.

Does that make sense? I'm not 100% sure that this is your problem, from your description, but it seems like a likely cause.

>L<

|||

Lisa, thanks for the response. I understand what you are saying but my solution is much more complex... but designed for very simple administration once the backend work is done.

I have (for starters) 76 reports x 50 client databases x ? custom scheduling requests with unique parameters. I am facilitating all of the interactive reporting and scheduled reporting through one copy of the 76 reports.

In addition, to minimize the number of objects to maintian I have a single stored procedure that is designed to act as a data source for all data-driven subscriptions for any report, receiving in some combination of ReportID and SettingGroup. This allows me to use structure to drive the entire operation and later develop an application front end where these complex schedule setups can be managed by anyone with the business knowledge to modify boolean values and make selections in an interface.

The complexity enters with the input parameters for reports which vary greatly. Some reports require no input parameters and always use default values coded into the report. Others may use two specific input parameters in one schedule and just one (with default on others) for a different schedule. Plus the input parameters for different reports are notably going to be different based on the nature of the report.

I have addressed input parameters in a way that requires no manual schema administration. The parameter setting names, values, and datatypes are stored in rows, one row per column name, and identified by the ReportID, SettingGroup, and ClientCode. When the stored procedure for the data source of the data-driven subscription executes, it checks for any related parameter setting records. If they exist, it creates a table on the fly, converting the row values for column definitions/values into actual columns of data. Then the concatenated SQL selects the standard columns plus any parameter settings as needed.

It all works perfectly, except that the "schema discovery" doesn't work in the data-driven subscription setup process. If i could work around that somehow, I would be set. Otherwise, i will probably be forced to have my process dynamically create a table for every report using the same stored procedure. The problem with this solution is that I can't execute the stored procedure at run time for the report so that the data is fully updated with any setting changes or ClientCode activiation/inactivations before the report runs.

This may be too much information, but I'm just frustrated because I'm sitting on the edge of completion for an elegant design that will make it possible for me to manage all these subscriptions while fulfilling my DBA, System Admin, and Developer roles. Otherwise, I fear a lot of unpaid overtime in my future.

If there is a way for me to trigger the stored procedure to run before the data-driven subscriptions run against a static table that will work too. I just don't know if that is an option.

Thanks in advance for your help.

|||

HI there,

I do appreciate the complexity of your situation and honestly didn't mean to minimize it by trying to explore how and what wasn't working out in a simple way.

The important thing to remember is that all complexity must be sieved down to a pure problem and most wide architecture has to be funnelled into a narrow place when it comes to integrating your work with some standardized, simplified solution (which is what RS data subscriptions is).

As I see it there are two ways we can go about this:

1) You can tell us more about your architecture and provide a simplified, reproducible version that, while not sharing all the features of your app, does serve to illustrate the place at which things go wrong with subscriptions. Then we can kick it around and try to see what changes would serve to "jimmy" the thing to work as you need.

In the process we may suggest things that will not work in your more fully-worked scenarios, and this may frustrate you further -- please be patient with us if this happens <s>.

We may also suggest architectural changes that may be extremely frustrating, even though we understand you're really happy with what you've come up with (except for this one piece)... and we'll understand if you reject those solutions.

2) You can accept the fact that RS data subscriptions are only one way of handling what you need to do, and not the best way at that. I use them, but also do *not* use them where they do not fit a business need or where my framework seems to "stretch" them in ways they were not meant to stretch.

As I have described on another thread (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019010&SiteID=1) there are many scenarios in which, quite frankly, RS data subscriptions simply do not do what one wants to do, whether in terms of exception reporting, subscription channels, or whatever -- and it is a lot better to roll your own.

It seems to me that your scenario is one of them, and that SSIS is a good fit here. You want to run a stored proc before running the report, go for it.

Just remember that RS data subscriptions are only one potential flow through which scheduled reports can be managed and this is really not a "one size fits all" type of story. The actual elements of the story can be endlessly recombined (see that thread) and each task element is really pretty simple to do so from my POV RS data subscriptions isn't adding all that much. If it's getting in your way, jettison it.

I hope this helps.

>L<

|||

First, I didn't mean to imply your were minimizing the complexity of my situation. I know you were just responding to a minimally defined issue that I threw out there, and it is appreciated.

Second, you are my hero! I'm a huge fan of SSIS and use it often for looping automation of tasks as well as data flows. It never occured to me to use it for this, but after looking at your other forum post and your script code, I see that this is probably the road for me. It allows me the flexibility to use very open ended, branching logic so I can handle all exceptions in one package making for easier management. I can even consolidate an FTP SSIS package I created for report delivery into this one package for an all encompassing process.

Thank you for your time and insight.

Sincerely,

Hugh

|||

Good for you! The important thing is that you are willing to do the work and you see the possibilities in the tools. It's people who think that there is something holy about the shortcuts presented along with the base tool set -- again, IMHO, data subscriptions is just one such shortcut -- that really have problems.

One needs to see that the shortcuts provide you with insight into the *potential* of the tools, not the only possible end result... you clearly get this, and you'll do fine!

Regards,

>L<

sql

Data regions are not allowed inside a table detail ..

Developing using Visual Studio .Net (2003).
I have a simple table on my report showing data from a dataset. Works
fine. Displays a bunch of records.
One of the values from my dataset is a percentage value (integer from 0
to 100). I'm attempting to illustrate this using a chart. So I'm
attempting to put a chart in the table detail row. It doesn't like me
doing this, though. I get a build error:
"The chart 'chartx' is contained inside a table detail row. Data
regions are not allowed inside a table detail...".
Is there a way to do what I'm attempting with Visual Studio .Net (i.e.
SQL Server 2000 Reporting Services)? I know it can be done with Visual
Studio 2005, as that just makes you define a group expression when
attempting to "use a data region in a list".
Any help/info would be great.
Thanks.Have you tried putting it inside a matrix?
I tried putting a chart inside a table detail row, but vs didn't let me
do it "Cannot place a chart at this location in a table"
However, putting it inside a matrix works just fine.
eamon wrote:
> Developing using Visual Studio .Net (2003).
> I have a simple table on my report showing data from a dataset. Works
> fine. Displays a bunch of records.
> One of the values from my dataset is a percentage value (integer from 0
> to 100). I'm attempting to illustrate this using a chart. So I'm
> attempting to put a chart in the table detail row. It doesn't like me
> doing this, though. I get a build error:
> "The chart 'chartx' is contained inside a table detail row. Data
> regions are not allowed inside a table detail...".
> Is there a way to do what I'm attempting with Visual Studio .Net (i.e.
> SQL Server 2000 Reporting Services)? I know it can be done with Visual
> Studio 2005, as that just makes you define a group expression when
> attempting to "use a data region in a list".
> Any help/info would be great.
> Thanks.sql

Data in dataset, no data in CR?

Hi,
I am using VS.net 2003 with emb. Crystal reports.

I retrieve data from a database using VB.net and populate a dataset. The records in the dataset are OK, but no data is shown in the report. How come? I use the following code:

I created a schema, called DSUrenPerProject.xsd. In the IDE I put 6 tables from a SQLserver-database on it.

Then I created the Crystal report and put some field from different tables on the report.

================ Then I created the next class============

Imports System.Data
Imports System.Data.OleDb
Public Class DataSetConfiguration

Public Shared ReadOnly Property CustomerDataSet() As DataSet
Get
Dim myDataSet As DSUrenPerProject = New DSUrenPerProject
Dim myOleDBConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(DB4D)
Dim myOleDbDataAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(mSQL, myOleDBConnection)

Try
myOleDbDataAdapter.Fill(myDataSet, "Uren")
Catch ee As Exception
MsgBox(ee.Message)
End Try
Return myDataSet
End Get
End Property
End Class
===========Then I created the next procedure in another form=====
Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
Dim m_Year As Integer
Dim m_Month As Integer
Dim m_Day As Integer
m_Year = DatePart(DateInterval.Year, Date.Parse(DTDatum.Text))
m_Month = DatePart(DateInterval.Month, Date.Parse(DTDatum.Text))
m_Day = DatePart(DateInterval.Day, Date.Parse(DTDatum.Text))

Dim m_Str As String
Dim m_Positie As Integer
m_Positie = InStr(cmbProjecten.Text, "-")
m_Str = Mid(cmbProjecten.Text, 1, m_Positie - 1)
Call init_Pr_PrNr(m_Str)
m_ProjectID = CInt(ds.Tables("Projecten").Rows(0).Item("ProjectID"))
Call Close_init()
If RBDag.Checked = True Then
m_Projectnr = Trim(m_Str)
m_Datum = CDate(Format(Date.Parse(DTDatum.Text), "dd/MM/yyyy"))
'Tbv 4DUren
mSQL = "SELECT Medewerkers.Voornaam as MedNaam, Medewerkers.Achternaam, Opdrachtgevers.Naam as OGNaam, "
mSQL = mSQL & "Contactpersonen.Achternaam as CPNaam, Projecten.Projectnummer, Projecten.Projectomschrijving, "
mSQL = mSQL & "CONVERT(char(10),Urenverantwoording.Datum, 120), Urenverantwoording.Uren100, Urenverantwoording.Uren150, "
mSQL = mSQL & "Urenverantwoording.Ziek, Urenverantwoording.Verlof, Urenverantwoording.Diverse "
mSQL = mSQL & "from Medewerkers, Opdrachtgevers, Contactpersonen, Urenverantwoording, Projecten "
mSQL = mSQL & "WHERE Urenverantwoording.ProjID= " & m_ProjectID & " AND DATEPART(YEAR, Urenverantwoording.Datum) =" & m_Year & " AND DATEPART(MONTH, Urenverantwoording.Datum) = " & m_Month & " AND DATEPART(DAY, Urenverantwoording.Datum) = " & m_Day
mSQL = mSQL & " AND Urenverantwoording.MedID = Medewerkers.MedewerkerID "
mSQL = mSQL & "AND Urenverantwoording.OGID = Opdrachtgevers.OpdrachtgeverID "
mSQL = mSQL & "AND Urenverantwoording.CPID = Contactpersonen.ContactpersoonID "
mSQL = mSQL & "AND Urenverantwoording.ProjID = Projecten.ProjectID "
mSQL = mSQL & "ORDER BY Urenverantwoording.Datum"

End If
frmPrint.ShowDialog()
End Sub
=================at the end I created the next class=========

Imports System.Data
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Public Class frmPrintForm
Inherits System.Windows.Forms.Form
Private customerReport As ReportDocument
Dim myDataSet As DataSet

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()
'ConfigureCrystalReports()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents myCrystalReportViewer As CrystalDecisions.Windows.Forms.CrystalReportViewer
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.myCrystalReportViewer = New CrystalDecisions.Windows.Forms.CrystalReportViewer
Me.SuspendLayout()
'
'myCrystalReportViewer
'
Me.myCrystalReportViewer.ActiveViewIndex = -1
Me.myCrystalReportViewer.Dock = System.Windows.Forms.DockStyle.Fill
Me.myCrystalReportViewer.Location = New System.Drawing.Point(0, 0)
Me.myCrystalReportViewer.Name = "myCrystalReportViewer"
Me.myCrystalReportViewer.ReportSource = Nothing
Me.myCrystalReportViewer.Size = New System.Drawing.Size(560, 421)
Me.myCrystalReportViewer.TabIndex = 0
'
'frmPrintForm
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(560, 421)
Me.Controls.Add(Me.myCrystalReportViewer)
Me.Name = "frmPrintForm"
Me.Text = "Printen van..."
Me.WindowState = System.Windows.Forms.FormWindowState.Maximized
Me.ResumeLayout(False)

End Sub

#End Region
Private Sub ConfigureCrystalReports()
customerReport = New ReportDocument
Dim reportPath As String = ""
'reportPath = Application.StartupPath.Substring(0, Len(Application.StartupPath) - 3) & "Test.rpt"

reportPath = Application.StartupPath.Substring(0, Len(Application.StartupPath) - 3) & "CR_UrenPerProject.rpt"
customerReport.Load(reportPath)
Dim myDataSet As DataSet = DataSetConfiguration.CustomerDataSet
customerReport.SetDataSource(myDataSet)
myCrystalReportViewer.ReportSource = customerReport

End Sub

Private Sub frmPrintForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
ConfigureCrystalReports()
End Sub
End Class
=============================================

Thank you for your patience. Can someone help me ?

regards, Ger.Open the report and do very database