SQL Server Management Studio: complex

Showing posts with label complex. Show all posts

Friday, March 30, 2012

Migrating from Access, boolean field problem

Hallo,
I am migrating complex database from Access 2000 to MSSQL server 8. This
database is used by web applications. Some time I need to be able swicth
between the old Access version and new SQL server driven one. I have
different connection strings, and connecting works well.
However, some sql query incompatibility has appeared...
Number of Access tables have boolean (Yes/No) fields. In Access, I could run
a query like this:
SELECT id FROM myTable WHERE myBooleanField
it returns all records where myBooleanField value is true. Now, when I
migrated to MSSQL, these boolean fields are of "bit" type, and carry "1" for
every Access "true" value, and "0" for "false" values. With this new field
type, I can no longer run the above mentioned query. I must change it to
this:
SELECT id FROM myTable WHERE myBooleanField=1
Now, this would not be a crazy problem, however I need to be able to run it
for some time on both database solutions, Access and MSSQL, so I really need
to have compatible queries.
One solution I see now would be converting the field type in access from
boolean to byte, and then run the "=1" queries. But that could involve range
of incompatibilities within the application itself, that assumes that these
fields are "true" or "false" instead of "1" and "0". Browsing through the
application to find these places is not really the best thing I can imagine.
So, maybe there is a real "boolean" field in MSSQL? Maybe other approaches
to solve my problem?
Thanks,
PavilsOne issue is that Access/Jet booleans are not true booleans, but can
also contain null values. Change the Jet columns to Not Null and
provide a default value (same with SQLS). This lets you avoid the
three-valued logic trap. In your SQL statements and code, perform all
comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
everywhere, so both engines are going to interpret "<>0" the same way.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
<pavils@.mailbox.riga.lv> wrote:
>Hallo,
>I am migrating complex database from Access 2000 to MSSQL server 8. This
>database is used by web applications. Some time I need to be able swicth
>between the old Access version and new SQL server driven one. I have
>different connection strings, and connecting works well.
>However, some sql query incompatibility has appeared...
>Number of Access tables have boolean (Yes/No) fields. In Access, I could run
>a query like this:
>SELECT id FROM myTable WHERE myBooleanField
>it returns all records where myBooleanField value is true. Now, when I
>migrated to MSSQL, these boolean fields are of "bit" type, and carry "1" for
>every Access "true" value, and "0" for "false" values. With this new field
>type, I can no longer run the above mentioned query. I must change it to
>this:
>SELECT id FROM myTable WHERE myBooleanField=1
>Now, this would not be a crazy problem, however I need to be able to run it
>for some time on both database solutions, Access and MSSQL, so I really need
>to have compatible queries.
>One solution I see now would be converting the field type in access from
>boolean to byte, and then run the "=1" queries. But that could involve range
>of incompatibilities within the application itself, that assumes that these
>fields are "true" or "false" instead of "1" and "0". Browsing through the
>application to find these places is not really the best thing I can imagine.
>So, maybe there is a real "boolean" field in MSSQL? Maybe other approaches
>to solve my problem?
>Thanks,
>Pavils
>|||Thanks Mary,
This is a good suggestion, it helps me to move further...
Now another problem has arised:
Access runs this query without complaints:
UPDATE myTable SET myBool = NOT myBool WHERE id=100
What could be a syntax working on both Jet and MSSQL engines?
I tried this one...
UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100
but I get error -
'IIf' is not a recognized function name.
Maybe some other smart syntax?
Thanks,
-- Pavils
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@.4ax.com...
> One issue is that Access/Jet booleans are not true booleans, but can
> also contain null values. Change the Jet columns to Not Null and
> provide a default value (same with SQLS). This lets you avoid the
> three-valued logic trap. In your SQL statements and code, perform all
> comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
> everywhere, so both engines are going to interpret "<>0" the same way.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com
> On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
> <pavils@.mailbox.riga.lv> wrote:
> >Hallo,
> >
> >I am migrating complex database from Access 2000 to MSSQL server 8. This
> >database is used by web applications. Some time I need to be able swicth
> >between the old Access version and new SQL server driven one. I have
> >different connection strings, and connecting works well.
> >
> >However, some sql query incompatibility has appeared...
> >
> >Number of Access tables have boolean (Yes/No) fields. In Access, I could
run
> >a query like this:
> >
> >SELECT id FROM myTable WHERE myBooleanField
> >
> >it returns all records where myBooleanField value is true. Now, when I
> >migrated to MSSQL, these boolean fields are of "bit" type, and carry "1"
for
> >every Access "true" value, and "0" for "false" values. With this new
field
> >type, I can no longer run the above mentioned query. I must change it to
> >this:
> >
> >SELECT id FROM myTable WHERE myBooleanField=1
> >
> >Now, this would not be a crazy problem, however I need to be able to run
it
> >for some time on both database solutions, Access and MSSQL, so I really
need
> >to have compatible queries.
> >
> >One solution I see now would be converting the field type in access from
> >boolean to byte, and then run the "=1" queries. But that could involve
range
> >of incompatibilities within the application itself, that assumes that
these
> >fields are "true" or "false" instead of "1" and "0". Browsing through the
> >application to find these places is not really the best thing I can
imagine.
> >
> >So, maybe there is a real "boolean" field in MSSQL? Maybe other
approaches
> >to solve my problem?
> >
> >Thanks,
> >
> >Pavils
> >
>|||One thing that's going to continue to trip you up in writing your
queries is not understanding the fundamental differences between
Access SQL and T-SQL, the two main points of which are:
-- Access SQL relies heavily on the expression service and VBA to
compensate for the fact that it has no programming language features
on its own. You can execute VBA functions like Iif inside of a query
only against the Jet engine with Access as a FE. .
-- T-SQL contains many programming language features, like variables.
It does not rely on an external programming language to provide this
functionality the way Access does. When you try VBA syntax, it fails
because SQLS has no knowledge of VBA.
For the most part, you'll want to stay away from language elements
that are specific to either Jet SQL or T-SQL. I'd start by writing the
queries using the Query Analyzer, then copying/pasting them into an
Access query window to see if the syntax works in both. SQL Books
Online is a good reference--Access SQL documentation is practically
non-existant. Besides the language elements, be aware that you're also
going to have problems with delimiters when working with datetime
values -- Jet uses # and SQLS uses '.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 19 Feb 2004 18:09:50 +0200, "Pavils Jurjans"
<pavils@.mailbox.riga.lv> wrote:
>Thanks Mary,
>This is a good suggestion, it helps me to move further...
>Now another problem has arised:
>Access runs this query without complaints:
>UPDATE myTable SET myBool = NOT myBool WHERE id=100
>What could be a syntax working on both Jet and MSSQL engines?
>I tried this one...
>UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100
>but I get error -
>'IIf' is not a recognized function name.
>Maybe some other smart syntax?
>Thanks,
>-- Pavils
>"Mary Chipman" <mchip@.nomail.please> wrote in message
>news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@.4ax.com...
>> One issue is that Access/Jet booleans are not true booleans, but can
>> also contain null values. Change the Jet columns to Not Null and
>> provide a default value (same with SQLS). This lets you avoid the
>> three-valued logic trap. In your SQL statements and code, perform all
>> comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
>> everywhere, so both engines are going to interpret "<>0" the same way.
>> -- Mary
>> MCW Technologies
>> http://www.mcwtech.com
>> On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
>> <pavils@.mailbox.riga.lv> wrote:
>> >Hallo,
>> >
>> >I am migrating complex database from Access 2000 to MSSQL server 8. This
>> >database is used by web applications. Some time I need to be able swicth
>> >between the old Access version and new SQL server driven one. I have
>> >different connection strings, and connecting works well.
>> >
>> >However, some sql query incompatibility has appeared...
>> >
>> >Number of Access tables have boolean (Yes/No) fields. In Access, I could
>run
>> >a query like this:
>> >
>> >SELECT id FROM myTable WHERE myBooleanField
>> >
>> >it returns all records where myBooleanField value is true. Now, when I
>> >migrated to MSSQL, these boolean fields are of "bit" type, and carry "1"
>for
>> >every Access "true" value, and "0" for "false" values. With this new
>field
>> >type, I can no longer run the above mentioned query. I must change it to
>> >this:
>> >
>> >SELECT id FROM myTable WHERE myBooleanField=1
>> >
>> >Now, this would not be a crazy problem, however I need to be able to run
>it
>> >for some time on both database solutions, Access and MSSQL, so I really
>need
>> >to have compatible queries.
>> >
>> >One solution I see now would be converting the field type in access from
>> >boolean to byte, and then run the "=1" queries. But that could involve
>range
>> >of incompatibilities within the application itself, that assumes that
>these
>> >fields are "true" or "false" instead of "1" and "0". Browsing through the
>> >application to find these places is not really the best thing I can
>imagine.
>> >
>> >So, maybe there is a real "boolean" field in MSSQL? Maybe other
>approaches
>> >to solve my problem?
>> >
>> >Thanks,
>> >
>> >Pavils
>> >
>|||Thanks, Mary for your help
I've been doing databasing in Access, mySQL, and some generic work in
interBase, MSSQL, and Oracle... but in this case I have to port fairly
complex application to MSSQL and I really see all those blanks I miss in the
knowledge about MSSQL. Well, but that's the nature of learning curve, being
very steep :) I'd be happy if this project would be originally developed in
MSSQL, but unfortunately this is not the case.
On my previous query, where I was looking for both Jet and MSSQL engine
compatible syntax for NOT-ting the boolean value, I solved it like this:
UPDATE myTable SET myBool = 1-ABS(myBool) WHERE id=123
That works on both Access and MSSQL.
The date delimiter thing I fortunately have abstracted away in my database
library, so it's easily fixable thing. Some issues about text/BLOB fields
are luring in now, but nothing very crucial though..
Thanks,
Pavils Jurjans
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:7roe30topfasals1m2avf2u2fj3dqol9dn@.4ax.com...
> One thing that's going to continue to trip you up in writing your
> queries is not understanding the fundamental differences between
> Access SQL and T-SQL, the two main points of which are:
> -- Access SQL relies heavily on the expression service and VBA to
> compensate for the fact that it has no programming language features
> on its own. You can execute VBA functions like Iif inside of a query
> only against the Jet engine with Access as a FE. .
> -- T-SQL contains many programming language features, like variables.
> It does not rely on an external programming language to provide this
> functionality the way Access does. When you try VBA syntax, it fails
> because SQLS has no knowledge of VBA.
> For the most part, you'll want to stay away from language elements
> that are specific to either Jet SQL or T-SQL. I'd start by writing the
> queries using the Query Analyzer, then copying/pasting them into an
> Access query window to see if the syntax works in both. SQL Books
> Online is a good reference--Access SQL documentation is practically
> non-existant. Besides the language elements, be aware that you're also
> going to have problems with delimiters when working with datetime
> values -- Jet uses # and SQLS uses '.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com

Migrating from Access, boolean field problem

I'd be happy if this project would be originally developed in
MSSQL, but unfortunately this is not the case.
On my previous query, where I was looking for both Jet and MSSQL engine
compatible syntax for NOT-ting the boolean value, I solved it like this:
UPDATE myTable SET myBool = 1-ABS(myBool) WHERE id=123
That works on both Access and MSSQL.
The date delimiter thing I fortunately have abstracted away in my database
library, so it's easily fixable thing. Some issues about text/BLOB fields
are luring in now, but nothing very crucial though..
Thanks,
Pavils Jurjans
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:7roe30topfasals1m2avf2u2fj3dqol9dn@.
4ax.com...
> One thing that's going to continue to trip you up in writing your
> queries is not understanding the fundamental differences between
> Access SQL and T-SQL, the two main points of which are:
> -- Access SQL relies heavily on the expression service and VBA to
> compensate for the fact that it has no programming language features
> on its own. You can execute VBA functions like Iif inside of a query
> only against the Jet engine with Access as a FE. .
> -- T-SQL contains many programming language features, like variables.
> It does not rely on an external programming language to provide this
> functionality the way Access does. When you try VBA syntax, it fails
> because SQLS has no knowledge of VBA.
> For the most part, you'll want to stay away from language elements
> that are specific to either Jet SQL or T-SQL. I'd start by writing the
> queries using the Query Analyzer, then copying/pasting them into an
> Access query window to see if the syntax works in both. SQL Books
> Online is a good reference--Access SQL documentation is practically
> non-existant. Besides the language elements, be aware that you're also
> going to have problems with delimiters when working with datetime
> values -- Jet uses # and SQLS uses '.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com|||Pavil,
I've been in this situation many times. As has been mentioned already, Acces
s'
YES/NO field is really a three-state field: YES/NO/NULL, where in SQL SERVER
the BIT type is 2-state.
The other problem is that Access treats TRUE as -1 where SQL Serrver actuall
y
treats TRUE as any non-zero value.
Armed with that information, you have two choices:
1) Use INT field in SQL Server, and you'll see good ol' -1 and 0 in that tab
le
anytime Access gets its ODBC/JET mitts on it.
2) When creating WHERE clauses in Access, get in the habit of ALWAYS using t
he
following test for TRUE:
Fld <> 0
Do that in VBA too, just to be consistent.

Wednesday, March 28, 2012

Migrating complex Crystal reports into MSRS

Hi experts,
I'm considering migrating my crystal reports into MSRS to cut down the
(high licence) costs. Could you pls suggest me if its a right
decision. My reports in crystal reports are very complex in nature and
are data driven.
For example, one of my crystal report
a) displays a blob (binary large object) - pic of the user
b) report fields are derived from a Stored Procedure
c) 3 level grouping (grouping on column a, then column b and then
column c)
d) has a lot of computed fields (with IF ELSE clauses)
e) has 4 subreports (which are highly formatted - layout, field
arrangement etc)
Can I achieve all the above in one report in MSRS? Pls do suggest.
Thanks,Yes.
Question (a): Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_layout_v1_0rz9.asp?frame=true
Question (b): Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_7kj8.asp?frame=true
Question (c): Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_3cok.asp?frame=true
Question (d): Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp?frame=true
Question (e): Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_layout_v1_2584.asp?frame=true
Also take a look at the sample reports
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/h
tm/rss_samplereports_v1_9pmb.asp?frame=true) that ship with the product
which demonstrate these features.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"MSProgrammer" <MSProgrammer@.discussions.microsoft.com> wrote in message
news:6A1AB37F-5B3C-49DC-A8C0-CA50723F8F52@.microsoft.com...
> Hi experts,
> I'm considering migrating my crystal reports into MSRS to cut down the
> (high licence) costs. Could you pls suggest me if its a right
> decision. My reports in crystal reports are very complex in nature and
> are data driven.
> For example, one of my crystal report
> a) displays a blob (binary large object) - pic of the user
> b) report fields are derived from a Stored Procedure
> c) 3 level grouping (grouping on column a, then column b and then
> column c)
> d) has a lot of computed fields (with IF ELSE clauses)
> e) has 4 subreports (which are highly formatted - layout, field
> arrangement etc)
> Can I achieve all the above in one report in MSRS? Pls do suggest.
> Thanks,
>|||Thanks, Rob. Very valuable inputs. Good to know that the migration from CR to RS is smooth. I'm pretty convinced that MSRS comes with wonderful features. However,
1) I'm trying to figure out whether the highly customized formatting, layout etc options that we have in CR are possible in RS too bcoz most of my reports are rich in formatting etc.
2) Can we push an ADO recordset into MSRS (instead of MSRS pulling the data) to create a separation between the report and the underlying data source? This would help us to swap the data source without changing the reports. This is possible in CR and my existing reports are working on this mechanism.
Ravi/Rob - I will appreciate your inputs on the above queries, particularly, the 2nd one.
Thks
"Rob 'Spike' Stevens" wrote:
> I have manually converted between 20 and 30 Crystal Reports so far in the past month (and still going), and I have found that after figuring out a few of the tricks on how SRS does things differently, the conversions go rather quickly.
> Firstly, you may find that you do not need sub-reports anymore, since SRS can supports data regions with diferent data sets.
> Running totals is a bit tricky at the moment in SRS, while Crystal has a more mature wizard for doing the various Running values, but so far I have been able to reproduce all Running sums in SRS - I've has to use the Report's Code section to write a finction that does the summing up for me, and use iif clauses in the grid to determine whe the function is called.
> The big trick is order of execution for data regions...currently it appears the headers and footers of a group level get evaluated before the stuff inside does (an outside-in approach). This causes an issue if you are trying to sum up the First value in a group to get a grand total (currently SRS does not support Aggregates of Aggregates, i.e Sum(First( table.field )) ) The way I got around that problem is to copy and paste the data region immiediately below itself, and set the top most region to invisible and can shrink. This way the Code gets executed before the second data region gets rendered and the totals will be available. The report in design mode looks weird, but in runtime it looks great.
> This board is great for finding other tips and tricks, but in general I'd say the tool isn't as big an issue as one might think, the conversions go pretty smoothly.
> "MSProgrammer" wrote:
> > Thanks, Ravi. I'll go thru all the links.
> >
> > 1) Could you pls let me know when is MS releasing the migration tool? Any inputs on the utility of this tool for migrating Crystal Reports to MSRS.
> >
> > 2) Can we push an ADO recordset into MSRS (instead of MSRS pulling the data) to create a separation between the report and the underlying data source? Thanks.
> >
> > "Ravi Mumulla (Microsoft)" wrote:
> >
> > > Yes.
> > >
> > > Question (a): Check
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_layout_v1_0rz9.asp?frame=true
> > > Question (b): Check
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_7kj8.asp?frame=true
> > > Question (c): Check
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_3cok.asp?frame=true
> > > Question (d): Check
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp?frame=true
> > > Question (e): Check
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_layout_v1_2584.asp?frame=true
> > >
> > > Also take a look at the sample reports
> > > (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/h
> > > tm/rss_samplereports_v1_9pmb.asp?frame=true) that ship with the product
> > > which demonstrate these features.
> > >
> > > --
> > > Ravi Mumulla (Microsoft)
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > "MSProgrammer" <MSProgrammer@.discussions.microsoft.com> wrote in message
> > > news:6A1AB37F-5B3C-49DC-A8C0-CA50723F8F52@.microsoft.com...
> > > > Hi experts,
> > > >
> > > > I'm considering migrating my crystal reports into MSRS to cut down the
> > > > (high licence) costs. Could you pls suggest me if its a right
> > > > decision. My reports in crystal reports are very complex in nature and
> > > > are data driven.
> > > >
> > > > For example, one of my crystal report
> > > >
> > > > a) displays a blob (binary large object) - pic of the user
> > > > b) report fields are derived from a Stored Procedure
> > > > c) 3 level grouping (grouping on column a, then column b and then
> > > > column c)
> > > > d) has a lot of computed fields (with IF ELSE clauses)
> > > > e) has 4 subreports (which are highly formatted - layout, field
> > > > arrangement etc)
> > > >
> > > > Can I achieve all the above in one report in MSRS? Pls do suggest.
> > > >
> > > > Thanks,
> > > >
> > >
> > >
> > >|||Sorry it has been a while since I got to your questions.
For #1, I'd have to say that it depends on what kinda formatting you are
talking about. I have completed the conversion of over 125 Crystal reports
and I felt that in most cases the appearance and modification of ta
appearance was comperable and superior, respectively. Alternating colors,
drill-down sections with + - controls - and no limit to how many levels of
drilldown (I believe Crystal only goes from main to one sub, with no subs
from subs). So you may find yourself having/wanting to reformat your reports
anyway to take advantage of the new options...there were several reports i
converted that got facelifts because their original look was based on a
workaround for something Crystal couldn't do at the time, or do any more.
In terms of modifying a reports format, I feel SRS is vastly superior. In
Crystal, I'd have reports stuffed full of fields and it took quite a while to
engineer the fonts and spacing to get the look just right; but it never
failed that a request would come in to add another field, and usually in the
middle - and the hours of re-engineering the locations and fonts of all the
fields commenced. SRS makes it far easier to change fonts, add line
graphics, add fields and re-proportion the other fields, and apply large
amounts of conditional formatting. I will caution you, however, that some of
the formatting properties are not all in the same place, or even nest to each
other. Like if you make a field a hyperlink to anywhere (another report,
other website), you have to change the font color to Blue yourself, and you
have to underline it manually as well. If you take the next step and make
the field conditionally a hyperlink, then you have to place an expression in
the Font Color, Action, and Font Decoration properties.
On your second question, MS has indicated that at some point in the future
they will be publishing SRS viewers to be enbedded in websites and
applications, but i still think it will be based on a Pull mechanism. I had
recently changed the datasource of a group of reports from one database
server to another, and the only change I had to make was to the rds file -
the reports never missed a beat.
"MSProgrammer" wrote:
> Thanks, Rob. Very valuable inputs. Good to know that the migration from CR to RS is smooth. I'm pretty convinced that MSRS comes with wonderful features. However,
> 1) I'm trying to figure out whether the highly customized formatting, layout etc options that we have in CR are possible in RS too bcoz most of my reports are rich in formatting etc.
> 2) Can we push an ADO recordset into MSRS (instead of MSRS pulling the data) to create a separation between the report and the underlying data source? This would help us to swap the data source without changing the reports. This is possible in CR and my existing reports are working on this mechanism.
> Ravi/Rob - I will appreciate your inputs on the above queries, particularly, the 2nd one.
> Thks
>
> "Rob 'Spike' Stevens" wrote:
> > I have manually converted between 20 and 30 Crystal Reports so far in the past month (and still going), and I have found that after figuring out a few of the tricks on how SRS does things differently, the conversions go rather quickly.
> >
> > Firstly, you may find that you do not need sub-reports anymore, since SRS can supports data regions with diferent data sets.
> >
> > Running totals is a bit tricky at the moment in SRS, while Crystal has a more mature wizard for doing the various Running values, but so far I have been able to reproduce all Running sums in SRS - I've has to use the Report's Code section to write a finction that does the summing up for me, and use iif clauses in the grid to determine whe the function is called.
> >
> > The big trick is order of execution for data regions...currently it appears the headers and footers of a group level get evaluated before the stuff inside does (an outside-in approach). This causes an issue if you are trying to sum up the First value in a group to get a grand total (currently SRS does not support Aggregates of Aggregates, i.e Sum(First( table.field )) ) The way I got around that problem is to copy and paste the data region immiediately below itself, and set the top most region to invisible and can shrink. This way the Code gets executed before the second data region gets rendered and the totals will be available. The report in design mode looks weird, but in runtime it looks great.
> >
> > This board is great for finding other tips and tricks, but in general I'd say the tool isn't as big an issue as one might think, the conversions go pretty smoothly.
> >
> > "MSProgrammer" wrote:
> >
> > > Thanks, Ravi. I'll go thru all the links.
> > >
> > > 1) Could you pls let me know when is MS releasing the migration tool? Any inputs on the utility of this tool for migrating Crystal Reports to MSRS.
> > >
> > > 2) Can we push an ADO recordset into MSRS (instead of MSRS pulling the data) to create a separation between the report and the underlying data source? Thanks.
> > >
> > > "Ravi Mumulla (Microsoft)" wrote:
> > >
> > > > Yes.
> > > >
> > > > Question (a): Check
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_layout_v1_0rz9.asp?frame=true
> > > > Question (b): Check
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_7kj8.asp?frame=true
> > > > Question (c): Check
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_3cok.asp?frame=true
> > > > Question (d): Check
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp?frame=true
> > > > Question (e): Check
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_layout_v1_2584.asp?frame=true
> > > >
> > > > Also take a look at the sample reports
> > > > (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/h
> > > > tm/rss_samplereports_v1_9pmb.asp?frame=true) that ship with the product
> > > > which demonstrate these features.
> > > >
> > > > --
> > > > Ravi Mumulla (Microsoft)
> > > > SQL Server Reporting Services
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > > "MSProgrammer" <MSProgrammer@.discussions.microsoft.com> wrote in message
> > > > news:6A1AB37F-5B3C-49DC-A8C0-CA50723F8F52@.microsoft.com...
> > > > > Hi experts,
> > > > >
> > > > > I'm considering migrating my crystal reports into MSRS to cut down the
> > > > > (high licence) costs. Could you pls suggest me if its a right
> > > > > decision. My reports in crystal reports are very complex in nature and
> > > > > are data driven.
> > > > >
> > > > > For example, one of my crystal report
> > > > >
> > > > > a) displays a blob (binary large object) - pic of the user
> > > > > b) report fields are derived from a Stored Procedure
> > > > > c) 3 level grouping (grouping on column a, then column b and then
> > > > > column c)
> > > > > d) has a lot of computed fields (with IF ELSE clauses)
> > > > > e) has 4 subreports (which are highly formatted - layout, field
> > > > > arrangement etc)
> > > > >
> > > > > Can I achieve all the above in one report in MSRS? Pls do suggest.
> > > > >
> > > > > Thanks,
> > > > >
> > > >
> > > >
> > > >|||Hi MSProgrammer,
Did you every find a satisfactory solution to your question (2)? We have the
same situation where our existing system pushes record sets to Crystal for
rendering and viewing in the web browser. We would like to use MSRS RDL to
define our report templates (like Crytsal RPT files) and then have our engine
"push" result sets thru the RDL for rendering and display.
Thanks in advance,
-Alex
"MSProgrammer" wrote:
> Thanks, Rob. Very valuable inputs. Good to know that the migration from CR to RS is smooth. I'm pretty convinced that MSRS comes with wonderful features. However,
> 1) I'm trying to figure out whether the highly customized formatting, layout etc options that we have in CR are possible in RS too bcoz most of my reports are rich in formatting etc.
> 2) Can we push an ADO recordset into MSRS (instead of MSRS pulling the data) to create a separation between the report and the underlying data source? This would help us to swap the data source without changing the reports. This is possible in CR and my existing reports are working on this mechanism.
> Ravi/Rob - I will appreciate your inputs on the above queries, particularly, the 2nd one.
> Thks
>
> "Rob 'Spike' Stevens" wrote:
> > I have manually converted between 20 and 30 Crystal Reports so far in the past month (and still going), and I have found that after figuring out a few of the tricks on how SRS does things differently, the conversions go rather quickly.
> >
> > Firstly, you may find that you do not need sub-reports anymore, since SRS can supports data regions with diferent data sets.
> >
> > Running totals is a bit tricky at the moment in SRS, while Crystal has a more mature wizard for doing the various Running values, but so far I have been able to reproduce all Running sums in SRS - I've has to use the Report's Code section to write a finction that does the summing up for me, and use iif clauses in the grid to determine whe the function is called.
> >
> > The big trick is order of execution for data regions...currently it appears the headers and footers of a group level get evaluated before the stuff inside does (an outside-in approach). This causes an issue if you are trying to sum up the First value in a group to get a grand total (currently SRS does not support Aggregates of Aggregates, i.e Sum(First( table.field )) ) The way I got around that problem is to copy and paste the data region immiediately below itself, and set the top most region to invisible and can shrink. This way the Code gets executed before the second data region gets rendered and the totals will be available. The report in design mode looks weird, but in runtime it looks great.
> >
> > This board is great for finding other tips and tricks, but in general I'd say the tool isn't as big an issue as one might think, the conversions go pretty smoothly.
> >
> > "MSProgrammer" wrote:
> >
> > > Thanks, Ravi. I'll go thru all the links.
> > >
> > > 1) Could you pls let me know when is MS releasing the migration tool? Any inputs on the utility of this tool for migrating Crystal Reports to MSRS.
> > >
> > > 2) Can we push an ADO recordset into MSRS (instead of MSRS pulling the data) to create a separation between the report and the underlying data source? Thanks.
> > >
> > > "Ravi Mumulla (Microsoft)" wrote:
> > >
> > > > Yes.
> > > >
> > > > Question (a): Check
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_layout_v1_0rz9.asp?frame=true
> > > > Question (b): Check
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_7kj8.asp?frame=true
> > > > Question (c): Check
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_3cok.asp?frame=true
> > > > Question (d): Check
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp?frame=true
> > > > Question (e): Check
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_layout_v1_2584.asp?frame=true
> > > >
> > > > Also take a look at the sample reports
> > > > (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/h
> > > > tm/rss_samplereports_v1_9pmb.asp?frame=true) that ship with the product
> > > > which demonstrate these features.
> > > >
> > > > --
> > > > Ravi Mumulla (Microsoft)
> > > > SQL Server Reporting Services
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > > "MSProgrammer" <MSProgrammer@.discussions.microsoft.com> wrote in message
> > > > news:6A1AB37F-5B3C-49DC-A8C0-CA50723F8F52@.microsoft.com...
> > > > > Hi experts,
> > > > >
> > > > > I'm considering migrating my crystal reports into MSRS to cut down the
> > > > > (high licence) costs. Could you pls suggest me if its a right
> > > > > decision. My reports in crystal reports are very complex in nature and
> > > > > are data driven.
> > > > >
> > > > > For example, one of my crystal report
> > > > >
> > > > > a) displays a blob (binary large object) - pic of the user
> > > > > b) report fields are derived from a Stored Procedure
> > > > > c) 3 level grouping (grouping on column a, then column b and then
> > > > > column c)
> > > > > d) has a lot of computed fields (with IF ELSE clauses)
> > > > > e) has 4 subreports (which are highly formatted - layout, field
> > > > > arrangement etc)
> > > > >
> > > > > Can I achieve all the above in one report in MSRS? Pls do suggest.
> > > > >
> > > > > Thanks,
> > > > >
> > > >
> > > >
> > > >|||Today you have to do a data processing extension. Read up on that in RS
help. In the future (Widbey/Yukon) there will be both a winform and webform
control that should (I haven't used it so I say should) do as you want.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alex Sorenson" <Alex Sorenson@.discussions.microsoft.com> wrote in message
news:7E13CEE3-AF3D-4624-A716-EA03031ECA6D@.microsoft.com...
> Hi MSProgrammer,
> Did you every find a satisfactory solution to your question (2)? We have
the
> same situation where our existing system pushes record sets to Crystal for
> rendering and viewing in the web browser. We would like to use MSRS RDL to
> define our report templates (like Crytsal RPT files) and then have our
engine
> "push" result sets thru the RDL for rendering and display.
> Thanks in advance,
> -Alex
>
> "MSProgrammer" wrote:
> > Thanks, Rob. Very valuable inputs. Good to know that the migration from
CR to RS is smooth. I'm pretty convinced that MSRS comes with wonderful
features. However,
> >
> > 1) I'm trying to figure out whether the highly customized formatting,
layout etc options that we have in CR are possible in RS too bcoz most of my
reports are rich in formatting etc.
> >
> > 2) Can we push an ADO recordset into MSRS (instead of MSRS pulling the
data) to create a separation between the report and the underlying data
source? This would help us to swap the data source without changing the
reports. This is possible in CR and my existing reports are working on this
mechanism.
> >
> > Ravi/Rob - I will appreciate your inputs on the above queries,
particularly, the 2nd one.
> >
> > Thks
> >
> >
> >
> > "Rob 'Spike' Stevens" wrote:
> >
> > > I have manually converted between 20 and 30 Crystal Reports so far in
the past month (and still going), and I have found that after figuring out a
few of the tricks on how SRS does things differently, the conversions go
rather quickly.
> > >
> > > Firstly, you may find that you do not need sub-reports anymore, since
SRS can supports data regions with diferent data sets.
> > >
> > > Running totals is a bit tricky at the moment in SRS, while Crystal has
a more mature wizard for doing the various Running values, but so far I have
been able to reproduce all Running sums in SRS - I've has to use the
Report's Code section to write a finction that does the summing up for me,
and use iif clauses in the grid to determine whe the function is called.
> > >
> > > The big trick is order of execution for data regions...currently it
appears the headers and footers of a group level get evaluated before the
stuff inside does (an outside-in approach). This causes an issue if you are
trying to sum up the First value in a group to get a grand total (currently
SRS does not support Aggregates of Aggregates, i.e Sum(First(
table.field )) ) The way I got around that problem is to copy and paste the
data region immiediately below itself, and set the top most region to
invisible and can shrink. This way the Code gets executed before the second
data region gets rendered and the totals will be available. The report in
design mode looks weird, but in runtime it looks great.
> > >
> > > This board is great for finding other tips and tricks, but in general
I'd say the tool isn't as big an issue as one might think, the conversions
go pretty smoothly.
> > >
> > > "MSProgrammer" wrote:
> > >
> > > > Thanks, Ravi. I'll go thru all the links.
> > > >
> > > > 1) Could you pls let me know when is MS releasing the migration
tool? Any inputs on the utility of this tool for migrating Crystal Reports
to MSRS.
> > > >
> > > > 2) Can we push an ADO recordset into MSRS (instead of MSRS pulling
the data) to create a separation between the report and the underlying data
source? Thanks.
> > > >
> > > > "Ravi Mumulla (Microsoft)" wrote:
> > > >
> > > > > Yes.
> > > > >
> > > > > Question (a): Check
> > > > >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_layout_v1_0rz9.asp?frame=true
> > > > > Question (b): Check
> > > > >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_7kj8.asp?frame=true
> > > > > Question (c): Check
> > > > >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_3cok.asp?frame=true
> > > > > Question (d): Check
> > > > >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp?frame=true
> > > > > Question (e): Check
> > > > >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_layout_v1_2584.asp?frame=true
> > > > >
> > > > > Also take a look at the sample reports
> > > > >
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/h
> > > > > tm/rss_samplereports_v1_9pmb.asp?frame=true) that ship with the
product
> > > > > which demonstrate these features.
> > > > >
> > > > > --
> > > > > Ravi Mumulla (Microsoft)
> > > > > SQL Server Reporting Services
> > > > >
> > > > > This posting is provided "AS IS" with no warranties, and confers
no rights.
> > > > > "MSProgrammer" <MSProgrammer@.discussions.microsoft.com> wrote in
message
> > > > > news:6A1AB37F-5B3C-49DC-A8C0-CA50723F8F52@.microsoft.com...
> > > > > > Hi experts,
> > > > > >
> > > > > > I'm considering migrating my crystal reports into MSRS to cut
down the
> > > > > > (high licence) costs. Could you pls suggest me if its a right
> > > > > > decision. My reports in crystal reports are very complex in
nature and
> > > > > > are data driven.
> > > > > >
> > > > > > For example, one of my crystal report
> > > > > >
> > > > > > a) displays a blob (binary large object) - pic of the user
> > > > > > b) report fields are derived from a Stored Procedure
> > > > > > c) 3 level grouping (grouping on column a, then column b and
then
> > > > > > column c)
> > > > > > d) has a lot of computed fields (with IF ELSE clauses)
> > > > > > e) has 4 subreports (which are highly formatted - layout, field
> > > > > > arrangement etc)
> > > > > >
> > > > > > Can I achieve all the above in one report in MSRS? Pls do
suggest.
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > >
> > > > >
> > > > >|||Thanks for your reply Bruce.
-Alex
"Bruce L-C [MVP]" wrote:
> Today you have to do a data processing extension. Read up on that in RS
> help. In the future (Widbey/Yukon) there will be both a winform and webform
> control that should (I haven't used it so I say should) do as you want.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Alex Sorenson" <Alex Sorenson@.discussions.microsoft.com> wrote in message
> news:7E13CEE3-AF3D-4624-A716-EA03031ECA6D@.microsoft.com...
> > Hi MSProgrammer,
> >
> > Did you every find a satisfactory solution to your question (2)? We have
> the
> > same situation where our existing system pushes record sets to Crystal for
> > rendering and viewing in the web browser. We would like to use MSRS RDL to
> > define our report templates (like Crytsal RPT files) and then have our
> engine
> > "push" result sets thru the RDL for rendering and display.
> >
> > Thanks in advance,
> > -Alex
> >
> >
> > "MSProgrammer" wrote:
> >
> > > Thanks, Rob. Very valuable inputs. Good to know that the migration from
> CR to RS is smooth. I'm pretty convinced that MSRS comes with wonderful
> features. However,
> > >
> > > 1) I'm trying to figure out whether the highly customized formatting,
> layout etc options that we have in CR are possible in RS too bcoz most of my
> reports are rich in formatting etc.
> > >
> > > 2) Can we push an ADO recordset into MSRS (instead of MSRS pulling the
> data) to create a separation between the report and the underlying data
> source? This would help us to swap the data source without changing the
> reports. This is possible in CR and my existing reports are working on this
> mechanism.
> > >
> > > Ravi/Rob - I will appreciate your inputs on the above queries,
> particularly, the 2nd one.
> > >
> > > Thks
> > >
> > >
> > >
> > > "Rob 'Spike' Stevens" wrote:
> > >
> > > > I have manually converted between 20 and 30 Crystal Reports so far in
> the past month (and still going), and I have found that after figuring out a
> few of the tricks on how SRS does things differently, the conversions go
> rather quickly.
> > > >
> > > > Firstly, you may find that you do not need sub-reports anymore, since
> SRS can supports data regions with diferent data sets.
> > > >
> > > > Running totals is a bit tricky at the moment in SRS, while Crystal has
> a more mature wizard for doing the various Running values, but so far I have
> been able to reproduce all Running sums in SRS - I've has to use the
> Report's Code section to write a finction that does the summing up for me,
> and use iif clauses in the grid to determine whe the function is called.
> > > >
> > > > The big trick is order of execution for data regions...currently it
> appears the headers and footers of a group level get evaluated before the
> stuff inside does (an outside-in approach). This causes an issue if you are
> trying to sum up the First value in a group to get a grand total (currently
> SRS does not support Aggregates of Aggregates, i.e Sum(First(
> table.field )) ) The way I got around that problem is to copy and paste the
> data region immiediately below itself, and set the top most region to
> invisible and can shrink. This way the Code gets executed before the second
> data region gets rendered and the totals will be available. The report in
> design mode looks weird, but in runtime it looks great.
> > > >
> > > > This board is great for finding other tips and tricks, but in general
> I'd say the tool isn't as big an issue as one might think, the conversions
> go pretty smoothly.
> > > >
> > > > "MSProgrammer" wrote:
> > > >
> > > > > Thanks, Ravi. I'll go thru all the links.
> > > > >
> > > > > 1) Could you pls let me know when is MS releasing the migration
> tool? Any inputs on the utility of this tool for migrating Crystal Reports
> to MSRS.
> > > > >
> > > > > 2) Can we push an ADO recordset into MSRS (instead of MSRS pulling
> the data) to create a separation between the report and the underlying data
> source? Thanks.
> > > > >
> > > > > "Ravi Mumulla (Microsoft)" wrote:
> > > > >
> > > > > > Yes.
> > > > > >
> > > > > > Question (a): Check
> > > > > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_layout_v1_0rz9.asp?frame=true
> > > > > > Question (b): Check
> > > > > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_7kj8.asp?frame=true
> > > > > > Question (c): Check
> > > > > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_3cok.asp?frame=true
> > > > > > Question (d): Check
> > > > > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp?frame=true
> > > > > > Question (e): Check
> > > > > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_layout_v1_2584.asp?frame=true
> > > > > >
> > > > > > Also take a look at the sample reports
> > > > > >
> (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/h
> > > > > > tm/rss_samplereports_v1_9pmb.asp?frame=true) that ship with the
> product
> > > > > > which demonstrate these features.
> > > > > >
> > > > > > --
> > > > > > Ravi Mumulla (Microsoft)
> > > > > > SQL Server Reporting Services
> > > > > >
> > > > > > This posting is provided "AS IS" with no warranties, and confers
> no rights.
> > > > > > "MSProgrammer" <MSProgrammer@.discussions.microsoft.com> wrote in
> message
> > > > > > news:6A1AB37F-5B3C-49DC-A8C0-CA50723F8F52@.microsoft.com...
> > > > > > > Hi experts,
> > > > > > >
> > > > > > > I'm considering migrating my crystal reports into MSRS to cut
> down the
> > > > > > > (high licence) costs. Could you pls suggest me if its a right
> > > > > > > decision. My reports in crystal reports are very complex in
> nature and
> > > > > > > are data driven.
> > > > > > >
> > > > > > > For example, one of my crystal report
> > > > > > >
> > > > > > > a) displays a blob (binary large object) - pic of the user
> > > > > > > b) report fields are derived from a Stored Procedure
> > > > > > > c) 3 level grouping (grouping on column a, then column b and
> then
> > > > > > > column c)
> > > > > > > d) has a lot of computed fields (with IF ELSE clauses)
> > > > > > > e) has 4 subreports (which are highly formatted - layout, field
> > > > > > > arrangement etc)
> > > > > > >
> > > > > > > Can I achieve all the above in one report in MSRS? Pls do
> suggest.
> > > > > > >
> > > > > > > Thanks,
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
>
>sql

Friday, March 30, 2012

Migrating from Access, boolean field problem

Migrating from Access, boolean field problem

Wednesday, March 28, 2012

Migrating complex Crystal reports into MSRS

SQL Server Management Studio

Blog Archive

About Me