Each date on its own report page?

I am wondering how to set a page break so that when a date range is selected (01/04/06 to 30/04/06) each day would show up on it's own page with the date at the top? Is this possible?
Any help would be appreciated.
TIA
TaraDo you want to have every date in the range to have its own page even if there's no corresponding data for that date?
This isn't too difficult but I won't elaborate unless it's what you really want.

If you merely want each new (record) date in the range on its own page then group by the date. Change the Group options to be printed 'for each day' and check 'Repeat Group Header On Each Page'.
In the section expert, check 'New Page After' for the group footer .|||Hi there. Yes, that is exactly what I would like to do! How do I go about changing the group options? (sorry, very new to crystal reporting and working with pre-packaged reports that I am trying to modify) I found the section info yesterday and had tried the new page after the group footer, was missing the first step though :)

Thanks very much for your help!|||That's OK, I'm new to Crystal too.
To change the group options right click on the group header/footer on the left of the design view and choose 'Change Group'. You'll find the 'The section will be printed' bit on the Common tab, and the 'Repeat Group Header...' bit on the Options tab.|||Tara,

Just to note, the 'Repeat Group Header...' bit is only required if one day's worth of data spans more than one page and you want the (same) date repeated on each page.

Dynamically selected columns with column switch option

Hi,
my requirement is to dynamically select certain columns from a table
and depending on a flag some columns must be swapped. This is easier
explained with the following statements one can paste into Query
Analyzer:
declare @.sql nvarchar(4000), @.cols nvarchar (500)
set @.cols = '
v1 = case when sel=1 then
v1a else v1b end,
v2 = case when sel=1 then v2a
else v2b end
'
create table ##temp_test (
v1a int,
v1b int,
v2a varchar(15),
v2b varchar(15),
sel bit
)
insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
values (1, 2, 'a1', 'b1', 0)
insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
values (3, 4, 'a2', 'b2', 1)
set @.sql = 'select ' + @.cols + 'from
##temp_test'
exec sp_executesql @.sql
drop table ##temp_test
Result:
v1 v2
-- --
2 b1
3 a2
This fits bots requirements: @.cols (which is stored in a table in the
real world application) holds the column names to be selected, and it
holds it in a way which also enables to switch between the a and b
version of the columns via the CASE statements. The performance is OK.
My problem: the real world query is a lot more complex, ##temp_test is
actually a table with about 80 columns and there are a lot of other
tables joined in.
Since I am doomed to not use more than 4000 characters for the dynamic
sql part (not using sp_executesql results in a huge performance
penalty in my scenario) this approach in the end works find without
all the CASE-ELSE-END stuff, but when I insert the CASE stuff, the
@.cols variable length will grow by factor 2-3, typically from around
1000 - 2000 chars to about 2000 - 6000 chars.
That's the end of the sp_executesql approach.
Maybe our design is wrong in the first place. We have tried to
alternate between the columns by using UNION to a View that contains
the alternate column, but ended up in quiet a performance hit with
more complex queries plus sometimes the SORT function would not work
anymore and the like.
Maybe I can shorten the
v1 = case when sel=1 then
v1a else v1b end
part somehow? Or in the best case someone knows a similar approach...
just better.
TIA for any comments!
Regards
DChttp://www.sommarskog.se/dynamic_sql.html
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"DC" <dc@.upsize.de> wrote in message
news:5b7bac12.0502250228.4d94de6c@.posting.google.com...
> Hi,
> my requirement is to dynamically select certain columns from a table
> and depending on a flag some columns must be swapped. This is easier
> explained with the following statements one can paste into Query
> Analyzer:
>
> declare @.sql nvarchar(4000), @.cols nvarchar (500)
> set @.cols = '
> v1 = case when sel=1 then
> v1a else v1b end,
> v2 = case when sel=1 then v2a
> else v2b end
> '
> create table ##temp_test (
> v1a int,
> v1b int,
> v2a varchar(15),
> v2b varchar(15),
> sel bit
> )
> insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
> values (1, 2, 'a1', 'b1', 0)
> insert into ##temp_test (v1a, v1b, v2a, v2b, sel)
> values (3, 4, 'a2', 'b2', 1)
> set @.sql = 'select ' + @.cols + 'from
> ##temp_test'
> exec sp_executesql @.sql
> drop table ##temp_test
>
> Result:
> v1 v2
> -- --
> 2 b1
> 3 a2
> This fits bots requirements: @.cols (which is stored in a table in the
> real world application) holds the column names to be selected, and it
> holds it in a way which also enables to switch between the a and b
> version of the columns via the CASE statements. The performance is OK.
> My problem: the real world query is a lot more complex, ##temp_test is
> actually a table with about 80 columns and there are a lot of other
> tables joined in.
> Since I am doomed to not use more than 4000 characters for the dynamic
> sql part (not using sp_executesql results in a huge performance
> penalty in my scenario) this approach in the end works find without
> all the CASE-ELSE-END stuff, but when I insert the CASE stuff, the
> @.cols variable length will grow by factor 2-3, typically from around
> 1000 - 2000 chars to about 2000 - 6000 chars.
> That's the end of the sp_executesql approach.
> Maybe our design is wrong in the first place. We have tried to
> alternate between the columns by using UNION to a View that contains
> the alternate column, but ended up in quiet a performance hit with
> more complex queries plus sometimes the SORT function would not work
> anymore and the like.
> Maybe I can shorten the
> v1 = case when sel=1 then
> v1a else v1b end
> part somehow? Or in the best case someone knows a similar approach...
> just better.
> TIA for any comments!
> Regards
> DC|||I don't understand why you want to use dynamic SQL for this. Where is
the string in @.cols generated from? You say it comes from a table but
why put it in a table at all? Why can't you define views for these
different views of the data?
David Portas
SQL Server MVP
--|||>> my requirement is to dynamically select certain columns from a table
and depending on a flag some columns must be swapped. <<
You might want to get any book on **basic software engineering** and
look up the concept of cohesion in a code module.|||Thank you! Can you recommend a good book on this topic? I probably
focussed too much on the loose coupling aspects? I support your claim
for the art of programming, but then again: it's only T-SQL. Hacky
stuff! Wouldn't a real programmer avoid messing around with SQL anyway?|||Thanks David, views or pre-generated stored procs are an option. We are
in the process of upgrading a life application though, and wanted to
integrate this additional requirement (the CASE req.) with the least
effort possible.|||Thank you Vinod, great article with a wealth of dynamic sql info. Does
not provide an exact solution, but pointed out that using EXEC shoud
not make as much of a difference as I experienced.|||> Wouldn't a real programmer avoid messing around with SQL anyway?
Sure. Why go looking for a new peg when you can just keep bashing that
square one into a round hole? After all, tables are only arrays aren't
they?
David Portas
SQL Server MVP
--|||On 25 Feb 2005 05:52:23 -0800, David Portas wrote:

>Sure. Why go looking for a new peg when you can just keep bashing that
>square one into a round hole? After all, tables are only arrays aren't
>they?
Hi David,
A "real" programmer - isn't that a guy who needs nothing but 8 switches
and 8 LED's to program the 'puter? Who needs all that modern assembler
language mnemonics anyway, huh?
(Hmmm - am I showing my age now <g> )
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Can you recommend a good book on this topic? <<
Any of the classic by Yourdon, Constantine, DeMarco or Gane & Sarson
are a good place to start.
They would avoid dynamic SQL and do the order of presentation of the
columns in the front end. I have become a fan of the idea that one
team in the shop handles the database and writes all the SQL for
everyone, and the application developers make requests to that team.

Dynamic values in parameters

I have 3 parameters which I populate from 3 sprocs. However, I want the
value selected for sproc1 to determine the possible values in sproc2 and the
selected value in sproc2 to determine the value in sprocs3. How do I go
about this?
Thanks
Frank AshleyFrank,
Search for "Cascading Parameters" in Reporting Services books online.
There's an example for T-SQL which should apply to stored procs.
"Frank Ashley" wrote:
> I have 3 parameters which I populate from 3 sprocs. However, I want the
> value selected for sproc1 to determine the possible values in sproc2 and the
> selected value in sproc2 to determine the value in sprocs3. How do I go
> about this?
>
> Thanks
> Frank Ashley
>
>

Dynamic SQL queries

Hi,
I am trying to use dynamic queries in RDL from the data tab in the report
designer. The columns to be selected and the name of the table from which to
select are supplied as report parameters. Can someone give me an example of
how to do this without using stored procedures or UDFs? Is it possible at
all? I am using the reporting services from MS SQL 2000.
Thanks in advance,
PavanI've done this a few times, but then I've used stored procedures or UDF. But
you should be able to do something similar without stored procedures. I've
done something like this with MDX, should work out with SQL queries as well.
I'd try something like this:
4 Parameters - param1, param2, param3 and param4, paramTable
Query:
="Select " & Parameters!param1.Value & " as P1, " & Parameters!param2.Value
& " as P2, " & Parameters!param3.Value & " as P3, " &
Parameters!param4.Value & " as P4 from " & Parameters!paramTable.Value& "
where 1=1"
(Everything needs to be on the "same" line, no line breaks. Test in NotePad
without wordwrap to see that everything is on the same line, even if the
lines break in the editor.)
This should give you a dataset with fields P1, P2, P3 and P4, which you now
can use in your report.
You will need to be sure that all the options are available in all tables.
Or you could do cascading parameters where you choose the table first, and
then the drop down list of all parameters gets filled with valid choices.
Kaisa M. Lindahl Lervik
"Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am trying to use dynamic queries in RDL from the data tab in the report
> designer. The columns to be selected and the name of the table from which
> to select are supplied as report parameters. Can someone give me an
> example of how to do this without using stored procedures or UDFs? Is it
> possible at all? I am using the reporting services from MS SQL 2000.
> Thanks in advance,
> Pavan
>|||One additional point. First have regular SQL so you get your field list.
Then change it as below. Once it is an expression the field list will not
update.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
> I've done this a few times, but then I've used stored procedures or UDF.
> But you should be able to do something similar without stored procedures.
> I've done something like this with MDX, should work out with SQL queries
> as well.
> I'd try something like this:
> 4 Parameters - param1, param2, param3 and param4, paramTable
> Query:
> ="Select " & Parameters!param1.Value & " as P1, " &
> Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as P3,
> " & Parameters!param4.Value & " as P4 from " &
> Parameters!paramTable.Value& " where 1=1"
> (Everything needs to be on the "same" line, no line breaks. Test in
> NotePad without wordwrap to see that everything is on the same line, even
> if the lines break in the editor.)
> This should give you a dataset with fields P1, P2, P3 and P4, which you
> now can use in your report.
> You will need to be sure that all the options are available in all tables.
> Or you could do cascading parameters where you choose the table first, and
> then the drop down list of all parameters gets filled with valid choices.
> Kaisa M. Lindahl Lervik
> "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
> news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I am trying to use dynamic queries in RDL from the data tab in the report
>> designer. The columns to be selected and the name of the table from which
>> to select are supplied as report parameters. Can someone give me an
>> example of how to do this without using stored procedures or UDFs? Is it
>> possible at all? I am using the reporting services from MS SQL 2000.
>> Thanks in advance,
>> Pavan
>|||Bruce,
I am trying to follow the BOL tutorial on dynamic queries. The expression
that they have will not fit as a one-liner in the generic query designer? Do
you have a work-around so that i can preview the result? Expression below:
="SELECT c.firstname, c.lastname, e.title, d.departmentID " &"From
HumanResources.EmployeeDepartmentHistory D " &"INNER JOIN
HumanResources.Employee E " & "ON D.EmployeeID = E.EmployeeID " & "INNER JOIN
Person.Contact C " &"ON E.ContactID = C.ContactID " &
Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " &
Parameters!Department.Value) & "ORDER BY C.LastName"
Thanks!
"Bruce L-C [MVP]" wrote:
> One additional point. First have regular SQL so you get your field list.
> Then change it as below. Once it is an expression the field list will not
> update.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
> news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
> > I've done this a few times, but then I've used stored procedures or UDF.
> > But you should be able to do something similar without stored procedures.
> > I've done something like this with MDX, should work out with SQL queries
> > as well.
> >
> > I'd try something like this:
> >
> > 4 Parameters - param1, param2, param3 and param4, paramTable
> >
> > Query:
> > ="Select " & Parameters!param1.Value & " as P1, " &
> > Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as P3,
> > " & Parameters!param4.Value & " as P4 from " &
> > Parameters!paramTable.Value& " where 1=1"
> >
> > (Everything needs to be on the "same" line, no line breaks. Test in
> > NotePad without wordwrap to see that everything is on the same line, even
> > if the lines break in the editor.)
> > This should give you a dataset with fields P1, P2, P3 and P4, which you
> > now can use in your report.
> > You will need to be sure that all the options are available in all tables.
> > Or you could do cascading parameters where you choose the table first, and
> > then the drop down list of all parameters gets filled with valid choices.
> >
> > Kaisa M. Lindahl Lervik
> >
> > "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
> > news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> >> Hi,
> >>
> >> I am trying to use dynamic queries in RDL from the data tab in the report
> >> designer. The columns to be selected and the name of the table from which
> >> to select are supplied as report parameters. Can someone give me an
> >> example of how to do this without using stored procedures or UDFs? Is it
> >> possible at all? I am using the reporting services from MS SQL 2000.
> >>
> >> Thanks in advance,
> >> Pavan
> >>
> >
> >
>
>|||Have a report with nothing but your parameters and a textbox. Set your
textbox to the expression so you can see the results.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:42F27824-479E-4B10-A232-083F860313EA@.microsoft.com...
> Bruce,
> I am trying to follow the BOL tutorial on dynamic queries. The expression
> that they have will not fit as a one-liner in the generic query designer?
> Do
> you have a work-around so that i can preview the result? Expression below:
> ="SELECT c.firstname, c.lastname, e.title, d.departmentID " &"From
> HumanResources.EmployeeDepartmentHistory D " &"INNER JOIN
> HumanResources.Employee E " & "ON D.EmployeeID = E.EmployeeID " & "INNER
> JOIN
> Person.Contact C " &"ON E.ContactID = C.ContactID " &
> Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " &
> Parameters!Department.Value) & "ORDER BY C.LastName"
> Thanks!
>
> "Bruce L-C [MVP]" wrote:
>> One additional point. First have regular SQL so you get your field list.
>> Then change it as below. Once it is an expression the field list will not
>> update.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
>> news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
>> > I've done this a few times, but then I've used stored procedures or
>> > UDF.
>> > But you should be able to do something similar without stored
>> > procedures.
>> > I've done something like this with MDX, should work out with SQL
>> > queries
>> > as well.
>> >
>> > I'd try something like this:
>> >
>> > 4 Parameters - param1, param2, param3 and param4, paramTable
>> >
>> > Query:
>> > ="Select " & Parameters!param1.Value & " as P1, " &
>> > Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as
>> > P3,
>> > " & Parameters!param4.Value & " as P4 from " &
>> > Parameters!paramTable.Value& " where 1=1"
>> >
>> > (Everything needs to be on the "same" line, no line breaks. Test in
>> > NotePad without wordwrap to see that everything is on the same line,
>> > even
>> > if the lines break in the editor.)
>> > This should give you a dataset with fields P1, P2, P3 and P4, which you
>> > now can use in your report.
>> > You will need to be sure that all the options are available in all
>> > tables.
>> > Or you could do cascading parameters where you choose the table first,
>> > and
>> > then the drop down list of all parameters gets filled with valid
>> > choices.
>> >
>> > Kaisa M. Lindahl Lervik
>> >
>> > "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
>> > news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> >> Hi,
>> >>
>> >> I am trying to use dynamic queries in RDL from the data tab in the
>> >> report
>> >> designer. The columns to be selected and the name of the table from
>> >> which
>> >> to select are supplied as report parameters. Can someone give me an
>> >> example of how to do this without using stored procedures or UDFs? Is
>> >> it
>> >> possible at all? I am using the reporting services from MS SQL 2000.
>> >>
>> >> Thanks in advance,
>> >> Pavan
>> >>
>> >
>> >
>>