Data Sources

I have created a report, and would like to use the data contained in that report to create another report. In essence, one report would be the dataset for another report. I can't find any indication that this is possible. Can anyone give me a suggestion?This is possible but not easy. First, you would need to write (or purchase)
a data provider that accepts XML data input from a URL.
Then you would use that data provider to get an export of the report from
the report server in XML format (using URL access).
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"RptSrvQuests" <RptSrvQuests@.discussions.microsoft.com> wrote in message
news:188E428D-4979-4ADE-B1B9-441E47011558@.microsoft.com...
> I have created a report, and would like to use the data contained in that
report to create another report. In essence, one report would be the
dataset for another report. I can't find any indication that this is
possible. Can anyone give me a suggestion?|||The best place to start is the section of the documentation titled
"Implementing a Data Processing Extension".
It is also available in MSDN:
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"RptSrvQuests" <RptSrvQuests@.discussions.microsoft.com> wrote in message
news:4B5F8B34-B05F-412E-9F19-62791C1CDE00@.microsoft.com...
> Can you provide an example of a data provider or what code it would be
written in? I would also like a more detailed description of how to carry
out the process. I'm still relatively new to RS, and need detailed
instructions on carrying out procedures.
> "Chris Hays [MSFT]" wrote:
> > This is possible but not easy. First, you would need to write (or
purchase)
> > a data provider that accepts XML data input from a URL.
> > Then you would use that data provider to get an export of the report
from
> > the report server in XML format (using URL access).
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "RptSrvQuests" <RptSrvQuests@.discussions.microsoft.com> wrote in message
> > news:188E428D-4979-4ADE-B1B9-441E47011558@.microsoft.com...
> > > I have created a report, and would like to use the data contained in
that
> > report to create another report. In essence, one report would be the
> > dataset for another report. I can't find any indication that this is
> > possible. Can anyone give me a suggestion?
> >
> >
> >

Data Source Views - Calculated Columns

Q: How do I use Calculated Columns from a Data Source View in an OLEDB Data Source Adapter.

I took the following steps:

- Created new SSIS project
- Added a Data Source connecting to a SQLServer2005 DB (MyDataSource)
- Added a Data Source View based on MyDataSource (MyDSV)
- Created a Calcualted field to Table Object MyTable (MyCalcField)
- Added a Connection Manager based on MyDSV
- Added Data Flow to Project
- Added OLEDB Source Adapter to Data Flow
- Attempting to Access Calculated Field MyCalcField to be used in Data Flow.

ISSUE: I can't seem to find a way to get the Calculated field to pass through. It's as though this metadata is not available to the Flow.

Anyone have any ideas?

Thanks - MikeyNero

You should not be adding data sources the way you've done it. That's more of a SQL Server Reporting Services thing...

Start a new SSIS project, and add a data flow to the control flow. Then in your data flow, add an OLE DB source. A wizard will come up to walk you through the connection setup.

|||

Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.

The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?

Any additonal info is of course welcom. Thanks - MikeyNero

|||

MikeyNero wrote:

Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.

The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?

Any additonal info is of course welcom. Thanks - MikeyNero

Mikey,

I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.

I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.

|||

Hello,

I think I have to agree with Mikey here, there just seem to something missing here. Its almost like MS stop short here. For example the over of usefulness of the data DSV seems to be very limited, let’s say you wanted to do something as simple as created a calculated column. The only thing that provided is a simple text box were a user has to 1 know SQL syntax 2 know the exact column spelling, no object browser, function list or parsing option... (yeah I know it’s dumb but are we not in a drag and drop world?)

I mean they have this logic somewhat existence is SSAS (but seem to have neglected to add it to SSIS) I mean what is so wrong with an object based repository? Some where you can centrally store all business and potential ETL logic that can be universally accessible from both services. (Yeah I know I can bring in an SSAS data source but then have to turn around and build a DSV before i can access any of the objects.) Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation

Cheer

Eric

|||

Rafael Salas wrote:

MikeyNero wrote:

Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.

The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?

Any additonal info is of course welcom. Thanks - MikeyNero

Mikey,

I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.

I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.

I agree with Rafael. It seems obvious to me that the DSV was designed by the Reporting Services/Analysis Services guys and some person from marketing said "Oooo, wouldn't it be nice if SSIS used that as well - then we can sell this 'common metadata' story"

In practice, using DSVs with SSIS is a pain in the neck. I admit I'm not speaking from experience here but only last week I came across a colleague having problems with them and after that I vowed never to go near them. Emerging best practice for SSIS talks about using configurations - and that seems to fly in the face of using DSVs.

Just my two-penneth worth.

-Jamie

|||

SQLDataMonkey wrote:

Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation

I almost completely agree. I just happen to think that the whole design of this common metadata layer was flawed from the very start. If it had been an architectural design goal rather than an afterthought (which it clearly was) then maybe the implementation of it would have been better. If they were serious about it then it would have influenced the design of SSIS, whereas in practice it seems that the opposite is the case - they had to fit 2 uncomplementary technologies together - and that is never a good idea.

My advice? If you're using SSIS, don't use DSVs!

-Jamie

Data Source Views - Calculated Columns

Q: How do I use Calculated Columns from a Data Source View in an OLEDB Data Source Adapter.

I took the following steps:

- Created new SSIS project
- Added a Data Source connecting to a SQLServer2005 DB (MyDataSource)
- Added a Data Source View based on MyDataSource (MyDSV)
- Created a Calcualted field to Table Object MyTable (MyCalcField)
- Added a Connection Manager based on MyDSV
- Added Data Flow to Project
- Added OLEDB Source Adapter to Data Flow
- Attempting to Access Calculated Field MyCalcField to be used in Data Flow.

ISSUE: I can't seem to find a way to get the Calculated field to pass through. It's as though this metadata is not available to the Flow.

Anyone have any ideas?

Thanks - MikeyNero

You should not be adding data sources the way you've done it. That's more of a SQL Server Reporting Services thing...

Start a new SSIS project, and add a data flow to the control flow. Then in your data flow, add an OLE DB source. A wizard will come up to walk you through the connection setup.

|||

Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.

The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?

Any additonal info is of course welcom. Thanks - MikeyNero

|||

MikeyNero wrote:

Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.

The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?

Any additonal info is of course welcom. Thanks - MikeyNero

Mikey,

I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.

I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.

|||

Hello,

I think I have to agree with Mikey here, there just seem to something missing here. Its almost like MS stop short here. For example the over of usefulness of the data DSV seems to be very limited, let’s say you wanted to do something as simple as created a calculated column. The only thing that provided is a simple text box were a user has to 1 know SQL syntax 2 know the exact column spelling, no object browser, function list or parsing option... (yeah I know it’s dumb but are we not in a drag and drop world?)

I mean they have this logic somewhat existence is SSAS (but seem to have neglected to add it to SSIS) I mean what is so wrong with an object based repository? Some where you can centrally store all business and potential ETL logic that can be universally accessible from both services. (Yeah I know I can bring in an SSAS data source but then have to turn around and build a DSV before i can access any of the objects.) Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation

Cheer

Eric

|||

Rafael Salas wrote:

MikeyNero wrote:

Thank you for your reply, although I would tend to disagree with your answer. My understanding of using the Data Source/DSV combination is that it allows the connection to be (at least) project scope instead of local to the package. Also gives the ability to define things such as PK/FK relationships at the metadata layer as well constrain the list of objects, add Calcualted columns, etc. Most of the literature I have read so far reference their use as the metadata "glue" accross SSIS, SSAS, and SSRS.

The issue I have is that in SSIS anyway, it seems to stop short of allowing the practical use of this facility in the building blocks of the package (outside of maybe creating a named query for every scenario). I am extremely new to the environment, and hoping this is just an oversight on my part?

Any additonal info is of course welcom. Thanks - MikeyNero

Mikey,

I just want to add my opinion. While the concept of DS is great; I have found that they can be trouble maker in SSIS projects. Even when you create a DS at the project level; each time you add a connection manager to a package using it; SSIS will copy the DS definition inside the package; so if for any reason the DS definition and its copy within a package get out of sync (an believe me, it will!); you will get a message about the situation every time you open the package. While that won't make the package to fail; it is certainly annoy, at least for me.

I have never used DSV in a SSIS project; but I would not use them because I just like the idea of having all the ETL logic within the package.

I agree with Rafael. It seems obvious to me that the DSV was designed by the Reporting Services/Analysis Services guys and some person from marketing said "Oooo, wouldn't it be nice if SSIS used that as well - then we can sell this 'common metadata' story"

In practice, using DSVs with SSIS is a pain in the neck. I admit I'm not speaking from experience here but only last week I came across a colleague having problems with them and after that I vowed never to go near them. Emerging best practice for SSIS talks about using configurations - and that seems to fly in the face of using DSVs.

Just my two-penneth worth.

-Jamie

|||

SQLDataMonkey wrote:

Please correct me if I m wrong here but it seems that MS had a GREAT idea but fell short on the implementation

I almost completely agree. I just happen to think that the whole design of this common metadata layer was flawed from the very start. If it had been an architectural design goal rather than an afterthought (which it clearly was) then maybe the implementation of it would have been better. If they were serious about it then it would have influenced the design of SSIS, whereas in practice it seems that the opposite is the case - they had to fit 2 uncomplementary technologies together - and that is never a good idea.

My advice? If you're using SSIS, don't use DSVs!

-Jamie

sql

Data Source View and Report Model

Guys,

I created a Data Source View that uses two different Data Source connections to two different databases. I was able to drop in the tables in the data source view designer from the two databases, but when I try to create the Report Model based on that data source view, it's complaining that it can't find table in the non-primary data source from the data source view.

Here is the error I'm getting:

An error occurred while executing a command.

Message: Invalid object name 'dbo.[tablename]'

Command:

SELECT COUNT(*) FROM [dbo].[tablename] t

Why can't it find the database to connect to since the data source view is already creating using two different databases. No problem in the data source view, but when it actually becomes a model, it complains. How come it can't find one of the tables in a different database?

Please help and any help is appreciated.

Enkh.

semantic queries currently do not support multiple data sources. you can create named query that would reference table from another database using db.schema.tablename notation.

Data source synchronization issue: Connection string conflict?

I was NOT using data source till now. I was directly using Ole DB Connection. Everything works as expected.

Today, I created a new Data Source based on the existing Ole Db connection.

I changed my tasks to use this Data Source.

Now, everytime I open the dtsx file, a screen pops-up with title "Synchronize Connection Strings". It reads: "This package contains at least one connection which is based on a data source. The connection strings for the connection and the data source listed below are not identical. Connection string of the connection will be updated to reflect those on the data source.

Connection : XYZ

Data Source: XYZ

Old Connection String: machine_name\instance_name; User ID=sa; Initial Catalog = MyDB; Provider=SQLOLEDB.1

New Connection String: Provider=SQLOLEDB.1; Data Source = machine_name\instance_name; Persist Security Info = true; Password=; User ID=; Initial Catalog = MyDB

I press the OK button, but the screen repeats if I close the dtsx file and re-open it. The underlying error is:

Error 1 Validation error. Data Flow Task - MyTaskName: OLE DB Destination - mytable [214]: The AcquireConnection method call to the connection manager "machine_name\instance_name.MyDB.sa" failed with error code 0xC0202009. mypackage.dtsx 0 0
Any idea?looks like a bug... can you open one, please?

thanks|||Sorry, I did not look at this post and therefore your reply till today (when I thought about using Data Sources again).

How do I submit this bug?

data secruity for Report Builder Models

Does anyone know how to implement data secruity based on user id (forms
authenication) for the report builder models.
Basically, I have created an report model. And i want certain people to have
access to certain data. The users are organized by the office where they
work. Is there a way to do, this without creating an different model for each
group of users?read this:
http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:A0BE0C59-10DA-4C44-9120-CCC13E5EF84E@.microsoft.com...
> Does anyone know how to implement data secruity based on user id (forms
> authenication) for the report builder models.
> Basically, I have created an report model. And i want certain people to
> have
> access to certain data. The users are organized by the office where they
> work. Is there a way to do, this without creating an different model for
> each
> group of users?|||When I try to add a new expression or filter, i get the following error
message
Access to the path 'C:\Program Files\Microsoft Visual Studio
8\Common7\IDE\config' is denied. (Microsoft Visual Studio)
"Jeje" wrote:
> read this:
> http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
>
> "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> news:A0BE0C59-10DA-4C44-9120-CCC13E5EF84E@.microsoft.com...
> > Does anyone know how to implement data secruity based on user id (forms
> > authenication) for the report builder models.
> >
> > Basically, I have created an report model. And i want certain people to
> > have
> > access to certain data. The users are organized by the office where they
> > work. Is there a way to do, this without creating an different model for
> > each
> > group of users?
>|||so, i wanted to do something like this as a filter or expression. But i don't
know how to get this into the expression. Anything you can do would help
SELECT *
FROM tblBureauCode INNER JOIN
tblPerson ON tblBureauCode.Bureau_CodeID =tblPerson.Bureau_CodeID INNER JOIN
tblDataAccess ON tblBureauCode.Bureau_Code =tblDataAccess.Bureaus
WHERE (tblDataAccess.Login = user!userid)
"Jeje" wrote:
> read this:
> http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
>
> "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> news:A0BE0C59-10DA-4C44-9120-CCC13E5EF84E@.microsoft.com...
> > Does anyone know how to implement data secruity based on user id (forms
> > authenication) for the report builder models.
> >
> > Basically, I have created an report model. And i want certain people to
> > have
> > access to certain data. The users are organized by the office where they
> > work. Is there a way to do, this without creating an different model for
> > each
> > group of users?
>

Data Processing Extension - parameters not created

Hi,
I'm writing a Data Processing Extension (DPE) for Reporting Services 2000,
using the FsiExtension sample as a basis. My problem is that the
CreateParameter method of the command class doesn't get called, so when I am
to execute my datareader, the parameters aren't there. I have added the
parameters manually to the dataset in the report, but they don't get carried
over to the DPE. I've debugged the code, and the CreateParameter never gets
called. The strange thing is that I had it working earlier, because I then
could access the parameter collection in the debugger, but now suddenly it
fails.
public IDataParameter CreateParameter()
{
return (IDataParameter)(new dbpDataParameter());
}
Any tips?
Brgds
JonasI found the problem, my class only implemented IDbComamand. When I added
IDbCommandAnalysis and
a GetParameters method it worked like it should.
/Jonas
"Jonas" <Jonas@.nospam.pl> wrote in message
news:OyFcwrwiGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm writing a Data Processing Extension (DPE) for Reporting Services 2000,
> using the FsiExtension sample as a basis. My problem is that the
> CreateParameter method of the command class doesn't get called, so when I
> am to execute my datareader, the parameters aren't there. I have added the
> parameters manually to the dataset in the report, but they don't get
> carried over to the DPE. I've debugged the code, and the CreateParameter
> never gets called. The strange thing is that I had it working earlier,
> because I then could access the parameter collection in the debugger, but
> now suddenly it fails.
> public IDataParameter CreateParameter()
> {
> return (IDataParameter)(new dbpDataParameter());
> }
>
> Any tips?
> Brgds
> Jonas
>

Data Parameter Question

Hi All:
Reporting Services newbie here. I have a query that has a fieldcalled Date. I have created two parameters in my report namedStart Date and End Date.
I need to know the proper syntax to build an expression that willreturn a recordset that contains all records with dates between mystart and end dates.
How do I accomplish this? And also, does anyone know of aresource anywhere that lays out examples of the syntax for variousexpressions used in Reporting Services. The help documentationisn't very clear.
Thanks
Brennan

What you need involve Time Interval and it is not implemented in SQL Server now but I found a UDF(User Defined Function) that can do it. Use it and you will get what you need, I would also look at the stored procs in the Time Tracker starter kit it will have time interval cals. Try the link below for the code. Hope this helps.

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-38-udf_DT_AddTime.htm

|||you can either call a stored proc that takes the startdate and enddate as parameters and gives you the result set or you can get the entire result set from the table and use filters to filter out the data between the dates. I'd recommend using a stored proc.|||

I am not sure if this is what you need but like most suggest you should use a stored proceedure but the syntax for the query would look like:

SELECT column_name FROM table_nameWHERE column_nameBETWEEN @.start_dateAND @.end_date

You can just try this directly in the report designer before making a stored procedure for it.

Note: @.start_date and @.end_date will be the value the user put in the parameter box.

|||Hi,
I have a similar question as you do, in fact I'm tryning to filter my report results between two dates, and I'm trying to do this directly in the Report designer using two report parameters, for the begin and for the end date. My problem is that the data type available for date parameters is "Date Time", and I want my parameter formated as a Date ( I don′t want to do it on the query because the parameter mus be shown as a date in the report) .Is there a way of doing it? Or I must set the parameters as strings, and cast them in the query? If so, how can I send an error message if the dates are not well fomated?
Thanks in advance for any help!
|||I didnt understand your question. Can you rephrase ?|||

Yes, what I wrote before was a little bit confuse,

In fact I have 2 different questions:

The first one is, can I have a report parameter configured as a Date instead of Date Time? (the data types available for the report parameters don't include the type Date).
The second question is, how can I send a pop-up with an error message in my report ? For example, if the parameter value inserted by the user is not valid for my report.

Thanks,
Cat.

|||

Hello:
Yes, just CAST the datetime value provided by the parameter within RS within SQL to the date - but thre is more to it than that!
The date passed as a parameter could be in any number of many formats - YYYY.DD/DD or YYYY-MM-DD, etc.
The dates based upon the culture or the dates stored within the data base!
This "date" thing drives me nuts at times in that I have to support Thai (which their year is 543 years ahead of the western calendar) but this is what I do and this may or may not be of help.
In my SQL data base I have dates that are in YYYY-MM-DD Hour:Minute:Sec, YYYYMMDD, MM/DD/YY, MM/DD/YYYY, DD/MM/YYYY, DD/MM/YY, etc. I did not create this mess but inherited it from programmers who developed the system in Thailand and Korea which I used the data within this database for another application -- I get really frustrated at times.
So, I define all date parameters in RS as strings. When an RS report is launched and the date parameter is displayed I provide a drop down list of available parameters which are:
NONE, Today, Yesterday, Beginning of Week, BI -Weekly, Beginning of Month, Monthly, Bi-Monthly, Quarterly, Yearly not in this particular order but nevertheless.
If they select NONE - I provide and additional two(2) parameters which allow them to enter the date in YYYYMMDD format.
I have also created a table in SQL called a Calendar Time Dimension (which is mandatory for Analysis Services).
The Calendar Time Dimension has a day for each day of the year and I have populated for 1995 through 2015.
The Calendar Time Dimension file has for each date within the year(s) that I populate the following:
Time Key which has a value for (eg. todays date 20050904) in the following formats:
YYYYMMDD, YYMMDD, MMDDYY, MMDDYYYY, DDMMYY, DDMMYYYY, MM/DD/YYYY, DD/MM/YYYY, including the Thai date which ifor today i s 20050904 + 543 (years) = 25480904, etc. I have all of the weirdo formats defined.
Also in the Calendar Time Dimension is the Calendar Week, Calendar Quarter, Fiscal Quarter, Month of Year, Week within Month, etc.
So when a user enters their own date via the date parameter (and elects not to select the any other parameter such as Today) within RS (a call to a stored procedure) I validate against the Calendar Time Dimension and if the date is invalid I provide back an error message via RS that indicates the date is invalid.
So my point (even though LOOONG WINDED) is that you should consider to manipulate the dates within a stored procedure based upon anything entered by the user within the date parameter within RS and validate against a similar Calendar Time Dimension table within the data base. So when a date is valid you use a JOIN to the Calendar Time Dimension to filter out the records you want within SQL. Another added benefit is that this really improves performance in that you are not filtering or comparing dates within each record within the DB.
If the user selects TODAY, Yesterday, Weekly etc. I have UDF's that generate the date ranges I use against the Calendar Time Dimension to process only the records I need against the table I go after in the Stored Procedure for the respective RS program.
If you want I can give you a stored procedure to create the Calendar TIme Dimension and then you can go from there.
Best Regards,

|||Thanks a lot for your post!
Cat.

Data not showing

Hi,
I am new to crystal reporting. I created a report using 2 tables. when i go to preview tab data is not showing up. and in the preview tab on the left hand side there is no drilldown button(+ sign) next to my report name. Please help me. Thanks in advanceHi, Did you make proper joins between two tables?

Madhivanan|||Thank you you are right

Data Missing from Report

I have created a RS report that contains 2 datasets and 2 lists. This is a
consolidated corporate report grouped to return data for 53 companies (one
page per company). Both datasets return data for all 53 companies when
viewing from the data pane. However, when the report is viewed in the
preview pane (or rendered), the 1st dataset returns data for all 53
companies, but there are 8 of the companies missing data from the 2nd dataset.
The problem varies in severity depending on the Begin and End date
parameters used to generate the report. What is strange is that a narrower
date range (several days) leaves off more data than a broader date range
(full month).
Anyone have any ideas?Has anyone out there ever experienced data missing from a RS generated report
that is returned when the same query is run in Query Analyzer?
"Lynn" wrote:
> I have created a RS report that contains 2 datasets and 2 lists. This is a
> consolidated corporate report grouped to return data for 53 companies (one
> page per company). Both datasets return data for all 53 companies when
> viewing from the data pane. However, when the report is viewed in the
> preview pane (or rendered), the 1st dataset returns data for all 53
> companies, but there are 8 of the companies missing data from the 2nd dataset.
> The problem varies in severity depending on the Begin and End date
> parameters used to generate the report. What is strange is that a narrower
> date range (several days) leaves off more data than a broader date range
> (full month).
> Anyone have any ideas?
>
>

data migration - new server uses different collation

Latin1_General_CI_AS on new server
All database created on old server use SQL_Latin1_General_CP1_CI_AS
I understand its can be a complete process to change the default collation
on my new server to SQL_Latin1_General_CP1_CI_AS
Can someone please help me understand what i need to do ?
Thanks for your time
Scott
SQL 2005 standard.i can use this on new DBs to make sure they use correct COLLATION (i.e same
as all user dbs.)
COLLATE SQL_Latin1_General_CP1_CI_AS
BUT my system dbs now use this > Latin1_General_CI_AS on new server.
What problems can i expect in future if these COLLATIONS differ on user /
system dbs ?

Data Migration

Hi friends,
I have changed the existing database scripts by adding primary key and
foreign key constraints.
Now i have created the new structure, I want to mograte the existing data
into the new structure. There is a chance for duplicate records and also
records that does not satisfy referential integrity.
How to create a populate data from the existing DB and how to avoid the
errors?
thanks a lot
vanithaYou have to eliminate the duplicate records, do you want to delete and
discard them, or do you want to keep them ? You have to provide further
informatione / DDL to help you.
Hth, jens Suessmeyer.
http://www.sqlserver2005.de
--|||hi vanitha,
--You can use this code to scan duplicate keys
use northwind
select orderid from [order details]
group by orderid
having count(orderid)>1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Vanitha" wrote:

> Hi friends,
> I have changed the existing database scripts by adding primary key and
> foreign key constraints.
> Now i have created the new structure, I want to mograte the existing data
> into the new structure. There is a chance for duplicate records and also
> records that does not satisfy referential integrity.
> How to create a populate data from the existing DB and how to avoid the
> errors?
> thanks a lot
> vanitha|||How to generate the populate script?
thanks
vanitha
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> hi vanitha,
> --You can use this code to scan duplicate keys
> use northwind
> select orderid from [order details]
> group by orderid
> having count(orderid)>1
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Vanitha" wrote:
>|||Hi
You may want to check the data first to see if this does apply:
e.g. for duplicates
SELECT A.col1, A.col2, A.col3
FROM MyTableA
GROUP BY A.col1, A.col2, A.col3
HAVING COUNT(*) > 1
For a foreign key try something like:
SELECT A.col1, A.col2, A.col3
FROM MyTableA A
WHERE NOT EXISTS ( SELECT * FROM FKTable F WHERE A.col2 = F.col1 )
If you want to eliminate the duplicates when inserting the data use a
DISTINCT clause
INSERT INTO NewTable ( col1, col2, col3 )
SELECT DISTINCT A.col1, A.col2, A.col3
FROM MyTableA
To eliminate those that do not have FKs
INSERT INTO NewTable ( col1, col2, col3 )
SELECT A.col1, A.col2, A.col3
FROM MyTableA A
WHERE EXISTS ( SELECT * FROM FKTable F WHERE A.col2 = F.col1 )
John
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:DA29DC9C-1109-491C-9970-E69EBAAB16AC@.microsoft.com...
> Hi friends,
> I have changed the existing database scripts by adding primary key and
> foreign key constraints.
> Now i have created the new structure, I want to mograte the existing data
> into the new structure. There is a chance for duplicate records and also
> records that does not satisfy referential integrity.
> How to create a populate data from the existing DB and how to avoid the
> errors?
> thanks a lot
> vanitha|||insert into destination(orderid,x,y,z) --> this are the insert hint
select orderid,x,y,z from orderdetails -->inserted must match
where orderid not in
(
select orderid from [order details]
group by orderid
having count(orderid)>1
)
process those that are duplicate and insert it afterwards
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Vanitha" wrote:
> How to generate the populate script?
> thanks
> vanitha
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>

Data Lost?

I have an ASP form created in FrontPage on an XP client
that connects to a "remote" SQL database hosted on a
Win2003 server. Connection tests, field mapping etc. all
indicate OK. When I fill out the form and submit, I get
confirmation and no indication of problems, however data
does not appear in the database. Some insight in to what
may be going on would be appreciated, TKS
BB3
Hi,
Please open the SQL profiler and check the statements fired. You can use the
profilers default template.
Thanks
Hari
MCDBA
"BB3" <anonymous@.discussions.microsoft.com> wrote in message
news:2f9a001c46d00$b4b588c0$a301280a@.phx.gbl...
> I have an ASP form created in FrontPage on an XP client
> that connects to a "remote" SQL database hosted on a
> Win2003 server. Connection tests, field mapping etc. all
> indicate OK. When I fill out the form and submit, I get
> confirmation and no indication of problems, however data
> does not appear in the database. Some insight in to what
> may be going on would be appreciated, TKS
> BB3

Data Lost?

I have an ASP form created in FrontPage on an XP client
that connects to a "remote" SQL database hosted on a
Win2003 server. Connection tests, field mapping etc. all
indicate OK. When I fill out the form and submit, I get
confirmation and no indication of problems, however data
does not appear in the database. Some insight in to what
may be going on would be appreciated, TKS
BB3Hi,
Please open the SQL profiler and check the statements fired. You can use the
profilers default template.
Thanks
Hari
MCDBA
"BB3" <anonymous@.discussions.microsoft.com> wrote in message
news:2f9a001c46d00$b4b588c0$a301280a@.phx
.gbl...
> I have an ASP form created in FrontPage on an XP client
> that connects to a "remote" SQL database hosted on a
> Win2003 server. Connection tests, field mapping etc. all
> indicate OK. When I fill out the form and submit, I get
> confirmation and no indication of problems, however data
> does not appear in the database. Some insight in to what
> may be going on would be appreciated, TKS
> BB3

Data Lost

I have an ASP form created in FrontPage on an XP client
that connects to a "remote" SQL database hosted on a
Win2003 server. Connection tests, field mapping etc. all
indicate OK. When I fill out the form and submit, I get
confirmation and no indication of problems, however data
does not appear in the database. Some insight in to what
may be going on would be appreciated? TKS
BB3
You'l have to use Profiler to see what exact procedures are called, and what
queries are executed inside SQL Server.
It could be that you are suppressing errors in your ASP code. Check that as
well.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"BB3" <anonymous@.discussions.microsoft.com> wrote in message
news:3bab01c47285$3d18d170$a301280a@.phx.gbl...
I have an ASP form created in FrontPage on an XP client
that connects to a "remote" SQL database hosted on a
Win2003 server. Connection tests, field mapping etc. all
indicate OK. When I fill out the form and submit, I get
confirmation and no indication of problems, however data
does not appear in the database. Some insight in to what
may be going on would be appreciated? TKS
BB3

Data Lost

I have an ASP form created in FrontPage on an XP client
that connects to a "remote" SQL database hosted on a
Win2003 server. Connection tests, field mapping etc. all
indicate OK. When I fill out the form and submit, I get
confirmation and no indication of problems, however data
does not appear in the database. Some insight in to what
may be going on would be appreciated? TKS
BB3You'l have to use Profiler to see what exact procedures are called, and what
queries are executed inside SQL Server.
It could be that you are suppressing errors in your ASP code. Check that as
well.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"BB3" <anonymous@.discussions.microsoft.com> wrote in message
news:3bab01c47285$3d18d170$a301280a@.phx.gbl...
I have an ASP form created in FrontPage on an XP client
that connects to a "remote" SQL database hosted on a
Win2003 server. Connection tests, field mapping etc. all
indicate OK. When I fill out the form and submit, I get
confirmation and no indication of problems, however data
does not appear in the database. Some insight in to what
may be going on would be appreciated? TKS
BB3

Data loss in replication

hi,

Im using merge replication.I have created one publisher with dynamic filtering and i have creted two subscribers from it(pull subscription).It was working fine.but after sysnchronizing 2-3 times data from some tables went missing

can any one help me....

thanks and regards

Dhanya

Can you provide more detail? If you don't mind, can you post your table schema and filter information or your replication setup script and indicate what was missing? Data from the publisher table or from the subscriber table?

Regards,

Gary

|||

It may be because your DML caused data to go out of partition from the subscribers.

Is this SQL 2000? Is the setting compensate_for_errors=true for the article you are experiencing data loss?

And by data loss, do you mean an insert done at publisher after synchronizing, does not exist anymore at the publisher and subscriber?

Data loss in replication

hi,

Im using merge replication.I have created one publisher with dynamic filtering and i have creted two subscribers from it(pull subscription).It was working fine.but after sysnchronizing 2-3 times data from some tables went missing

can any one help me....

thanks and regards

Dhanya

Can you provide more detail? If you don't mind, can you post your table schema and filter information or your replication setup script and indicate what was missing? Data from the publisher table or from the subscriber table?

Regards,

Gary

|||

It may be because your DML caused data to go out of partition from the subscribers.

Is this SQL 2000? Is the setting compensate_for_errors=true for the article you are experiencing data loss?

And by data loss, do you mean an insert done at publisher after synchronizing, does not exist anymore at the publisher and subscriber?

Data input problem

In Visual Web Developer Ihave created a data input form based on the documentation I found in the .NETFramework Class Library (SqlDataSource.InsertCommand Property). Originally my form contained 4 textboxes(FirstName, LastName, Phone, Email) and worked fine. All this data is nvarchar string data.

When I added a checkbox Iget an error that"Stringwas not recognized as a valid Boolean" when the checkbox is checked. When the checkbox is unchecked the data is input without a problem. This is a bit data field in the table.

Here is my VB code, InsertParameters for my SqlDataSource1, and button code:

<scriptrunat="server">

PrivateSub InsertData(ByValSourceAsObject,ByVal eAsEventArgs)

SqlDataSource1.Insert()

EndSub

</script>

<InsertParameters>

<asp:formParameterName="FirstName"Type="String"FormField="txtFirstName"/>

<asp:formParameterName="LastName"Type="String"FormField="txtLastName"/>

<asp:formParameterName="Phone"Type="String"FormField="txtPhone"/>

<asp:formParameterName="Email"Type="String"FormField="txtEmail"/>

<asp:formParameterName="FreeInfo"Type="Boolean"FormField="checkbox1"/>

</InsertParameters>

<asp:Buttonid="Button1"runat="server"text="Submit"OnClick="insertdata"/>

I suspect that the problemis with the VB code which will only accept string input data, but don't knowhow to fix it. Any thoughts on how tofix this problem? Thanks in advance forany help provided.

Check box return "On" whener page is postback

It does'nt return checked or unchecked.

You have to check this checkBox by code before inserting the data.

If checkBox is checked set the parametervalue = 1 else 0 .

Thank

Kunal

|||

Thanks for your feedback. I tried to implement your suggestions, but get this error message:

String was not recognized as a valid Boolean.

Here is my new VB code:

Private Sub InsertName(ByVal Source As Object, ByVal e As EventArgs)
If (CheckBox1.Checked = True) Then
CheckBox1.Equals(1)
Else : CheckBox1.Equals(0)
End If
SqlDataSource1.Insert()
End Sub

I am a newbie to VB, so my codeprobably doesn't accurately implement your recommendations. Any helpyou can provide in getting this to work will be greatly appreciated.

|||

Hi,

The formParameter always gets value from the Text property of the controls. In this case, you can try to use ControlParameter instead.

<asp:controlParameter Name="FreeInfo" ControlId="checkbox1" PropertyName="Checked"/>

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Thanks Kevin. That's exactly what I needed.