Dynamic report on screen vs. export

I was wondering if it's possible to display a report, but export an expanded version of that report? For instance if I have a report with, say, 6 columns, when the user exports to Excel I want to export 12 columns (6 that were displayed plus 6 additional columns). I tried hiding the additional columns, but they're also hidden in the export. I ended-up creating a separate report all together for exporting, but this is confusing to the user. Is something like this even possible?

Unfortunately renderer-specific formatting adjustments are not supported. You will probably need to construct two versions of the same report (one with the extra columns) and instruct your users to use the appropriate one for the output format.

Dynamic Parameter Question

I have a report that has different layers of resolution: Global, Region, State, Facility and Issue. For different each level
chosen I want to display a different set of parameters and execute a different SP for my dataset (which will return the same rows).
I've seen posts where I can do the dynamic SP calls but I'm stumped on the showing and hiding of the parameters that I need. I
tried putting a =IIF(blah... as the prompt but that just displayed the expression and didn't evaluate it.
ANY help is appreciated to keep me from having to do 5 different reports :)
Thanks
ScottI think to do this you would want cascading parameters. Then when you call
your stored procedure you will need a wrapper sp that determines which to
call.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> I have a report that has different layers of resolution: Global, Region,
State, Facility and Issue. For different each level
> chosen I want to display a different set of parameters and execute a
different SP for my dataset (which will return the same rows).
> I've seen posts where I can do the dynamic SP calls but I'm stumped on the
showing and hiding of the parameters that I need. I
> tried putting a =IIF(blah... as the prompt but that just displayed the
expression and didn't evaluate it.
> ANY help is appreciated to keep me from having to do 5 different reports
:)
> Thanks
> Scott
>|||Or you create 1 report with all the parameters and then use linked reports
in which you enable specific parameters.
It's like creating 5 reports, but way faster :-)
Hth,
Tom
"Bruce L-C [MVP]" wrote:
> I think to do this you would want cascading parameters. Then when you call
> your stored procedure you will need a wrapper sp that determines which to
> call.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> > I have a report that has different layers of resolution: Global, Region,
> State, Facility and Issue. For different each level
> > chosen I want to display a different set of parameters and execute a
> different SP for my dataset (which will return the same rows).
> > I've seen posts where I can do the dynamic SP calls but I'm stumped on the
> showing and hiding of the parameters that I need. I
> > tried putting a =IIF(blah... as the prompt but that just displayed the
> expression and didn't evaluate it.
> >
> > ANY help is appreciated to keep me from having to do 5 different reports
> :)
> >
> > Thanks
> > Scott
> >
> >
>
>|||Yeah, but how can I set up my parameters to be cascading like that?
IE
Global requires no parameters
Region requires a FK of a region
State requires a two letter state
Facility requires a facilities key value (int)
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:Ofr5IHTvEHA.1300@.TK2MSFTNGP14.phx.gbl...
>I think to do this you would want cascading parameters. Then when you call
> your stored procedure you will need a wrapper sp that determines which to
> call.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
>> I have a report that has different layers of resolution: Global, Region,
> State, Facility and Issue. For different each level
>> chosen I want to display a different set of parameters and execute a
> different SP for my dataset (which will return the same rows).
>> I've seen posts where I can do the dynamic SP calls but I'm stumped on the
> showing and hiding of the parameters that I need. I
>> tried putting a =IIF(blah... as the prompt but that just displayed the
> expression and didn't evaluate it.
>> ANY help is appreciated to keep me from having to do 5 different reports
> :)
>> Thanks
>> Scott
>>
>|||even if you have cascading paprameters, it would still show up on the report
though greyed out, making the report parameter section really cluttered.
Having the IIF at the prompt of the parameters is a real cool add on. Should
be added to the wish list.
I would just create subreports, as suggested later
"Bruce L-C [MVP]" wrote:
> I think to do this you would want cascading parameters. Then when you call
> your stored procedure you will need a wrapper sp that determines which to
> call.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> > I have a report that has different layers of resolution: Global, Region,
> State, Facility and Issue. For different each level
> > chosen I want to display a different set of parameters and execute a
> different SP for my dataset (which will return the same rows).
> > I've seen posts where I can do the dynamic SP calls but I'm stumped on the
> showing and hiding of the parameters that I need. I
> > tried putting a =IIF(blah... as the prompt but that just displayed the
> expression and didn't evaluate it.
> >
> > ANY help is appreciated to keep me from having to do 5 different reports
> :)
> >
> > Thanks
> > Scott
> >
> >
>
>|||I was thinking that these were somehow related. Only certain regions based
on Global, only certain states based on region, only certain facilities
based on State. If so, they you have each of them based on a dataset where
the dataset query parameter is based on the preceding parameter.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:Oez$zzbvEHA.716@.TK2MSFTNGP10.phx.gbl...
> Yeah, but how can I set up my parameters to be cascading like that?
> IE
> Global requires no parameters
> Region requires a FK of a region
> State requires a two letter state
> Facility requires a facilities key value (int)
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:Ofr5IHTvEHA.1300@.TK2MSFTNGP14.phx.gbl...
> >I think to do this you would want cascading parameters. Then when you
call
> > your stored procedure you will need a wrapper sp that determines which
to
> > call.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in
message
> > news:uzKenwSvEHA.2200@.TK2MSFTNGP11.phx.gbl...
> >> I have a report that has different layers of resolution: Global,
Region,
> > State, Facility and Issue. For different each level
> >> chosen I want to display a different set of parameters and execute a
> > different SP for my dataset (which will return the same rows).
> >> I've seen posts where I can do the dynamic SP calls but I'm stumped on
the
> > showing and hiding of the parameters that I need. I
> >> tried putting a =IIF(blah... as the prompt but that just displayed the
> > expression and didn't evaluate it.
> >>
> >> ANY help is appreciated to keep me from having to do 5 different
reports
> > :)
> >>
> >> Thanks
> >> Scott
> >>
> >>
> >
> >
>

Dynamic Linking - Crystal XI - Image doesnt change

Im using desktop Crystal XI - connecting to a SQL database via ODBC connection.

Here is the problem: I would like to display a different excel file with each record in the report using dynamic linking.

Each record has a related document (xls) that is stored on a different server than the database. I have access to the folder on the server that contains all of the documents (I connect to this server by mapping a drive via windows explorer). I can manually add a document from the folder but the document doesnt change for each record, it just repeats the document that was added manually.

Ive created a formula field (based on records in the SQL db) to generate the link/document location:

"E:\LCM-" + cstr({mwebDocument.Doc_Ent_ID}, 0, "") + "\" + ({mwebDocument.Doc_File_Name}) + "_" + cstr({mwebDocument.Doc_ID}, 0, "") + "_1.xls"

(example return: E:\LCM-289\KC 2554 Testing Backup NOPAs_9609_1.xls)

I then reference the formula field in the graphic location.

So I know I can access the server/files since I can manually add a file.
I know that the file does display because the manually added file displays for each record (even though it doesnt change).
I know that the link is changing for each record because Ive made it a separate field and can verify its correctness.

Can anyone think of a reason why the object/file is not changing for each record?

Appreciate the help.Why don't u use the formula directly in detail section as a hyperlink..|||Thanks for the response - The major reason for not using a hyperlink is that we would like to distribute this report to people outside of our company (who wouldn't have access to the server/files). This was the initial reason to display the file in the report.

dynamic images in sql reporting services 2005

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

One way to do this is to include a hyperlink to the image. The URL of the hyperlink can be an expression controlled by the data in your report.|||

can we pass the URL using a parameter.

or can we do it like this.

i have a image file name in db(FirstName is the image name)

i have image path thru parameter

and in expression can i give like this

=Parameters!IPath.Value+Fields!FirstName.Value+".png"

it is working in the preview but not after deployment and in the runtime in IE

|||

RameshP wrote:

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

|||

We have similiar dashboard with traffic lights

I put an image in the field, and "value" field =

Code Snippet

=IIF(Fields!capacity_available.Value < 0.2, "icon_red-light.gif",

iif(Fields!capacity_available.Value < 0.4, "icon_yellow-light.gif",

iif(Fields!capacity_available.Value < 0.8, "icon_green-light.gif",

"icon_green-light.gif")))

dynamic images in sql reporting services 2005

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

One way to do this is to include a hyperlink to the image. The URL of the hyperlink can be an expression controlled by the data in your report.|||

can we pass the URL using a parameter.

or can we do it like this.

i have a image file name in db(FirstName is the image name)

i have image path thru parameter

and in expression can i give like this

=Parameters!IPath.Value+Fields!FirstName.Value+".png"

it is working in the preview but not after deployment and in the runtime in IE

|||

RameshP wrote:

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

|||

We have similiar dashboard with traffic lights

I put an image in the field, and "value" field =

Code Snippet

=IIF(Fields!capacity_available.Value < 0.2, "icon_red-light.gif",

iif(Fields!capacity_available.Value < 0.4, "icon_yellow-light.gif",

iif(Fields!capacity_available.Value < 0.8, "icon_green-light.gif",

"icon_green-light.gif")))

dynamic images in sql reporting services 2005

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

One way to do this is to include a hyperlink to the image. The URL of the hyperlink can be an expression controlled by the data in your report.|||

can we pass the URL using a parameter.

or can we do it like this.

i have a image file name in db(FirstName is the image name)

i have image path thru parameter

and in expression can i give like this

=Parameters!IPath.Value+Fields!FirstName.Value+".png"

it is working in the preview but not after deployment and in the runtime in IE

|||

RameshP wrote:

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

|||

We have similiar dashboard with traffic lights

I put an image in the field, and "value" field =

Code Snippet

=IIF(Fields!capacity_available.Value < 0.2, "icon_red-light.gif",

iif(Fields!capacity_available.Value < 0.4, "icon_yellow-light.gif",

iif(Fields!capacity_available.Value < 0.8, "icon_green-light.gif",

"icon_green-light.gif")))

dynamic images in sql reporting services 2005

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

One way to do this is to include a hyperlink to the image. The URL of the hyperlink can be an expression controlled by the data in your report.|||

can we pass the URL using a parameter.

or can we do it like this.

i have a image file name in db(FirstName is the image name)

i have image path thru parameter

and in expression can i give like this

=Parameters!IPath.Value+Fields!FirstName.Value+".png"

it is working in the preview but not after deployment and in the runtime in IE

|||

RameshP wrote:

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

|||

We have similiar dashboard with traffic lights

I put an image in the field, and "value" field =

Code Snippet

=IIF(Fields!capacity_available.Value < 0.2, "icon_red-light.gif",

iif(Fields!capacity_available.Value < 0.4, "icon_yellow-light.gif",

iif(Fields!capacity_available.Value < 0.8, "icon_green-light.gif",

"icon_green-light.gif")))

Dynamic Expression

I would like to display calculated field on a group header.
The nature of the calculation must be passed as a parameter to the report.
For example lets assume that my report lists X,Y pairs.
On the break I would like to display the value of
Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
It is not practical to prepare an expression for every possible option and
use a selector as X,Y are also dynamically selected values from the query.
So I need a dynamic way to define the expression based on input parameters.
Is Custom code is the way to go ? How do I access the Parameters collection,
and the Report's data from Custom code ?
Any Ideas ?
Thanks.I think that custom code will be needed. You can pass the values you need
(Parameters, Report Data) in as parameters to a function.
"NL" wrote:
> I would like to display calculated field on a group header.
> The nature of the calculation must be passed as a parameter to the report.
> For example lets assume that my report lists X,Y pairs.
> On the break I would like to display the value of
> Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> It is not practical to prepare an expression for every possible option and
> use a selector as X,Y are also dynamically selected values from the query.
> So I need a dynamic way to define the expression based on input parameters.
> Is Custom code is the way to go ? How do I access the Parameters collection,
> and the Report's data from Custom code ?
> Any Ideas ?
> Thanks.
>
>
>
>
>
>|||How does one create a custom function that manipulates the headers of a
report based on parameter values?
"John W" wrote:
> I think that custom code will be needed. You can pass the values you need
> (Parameters, Report Data) in as parameters to a function.
> "NL" wrote:
> > I would like to display calculated field on a group header.
> > The nature of the calculation must be passed as a parameter to the report.
> >
> > For example lets assume that my report lists X,Y pairs.
> > On the break I would like to display the value of
> > Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> >
> > It is not practical to prepare an expression for every possible option and
> > use a selector as X,Y are also dynamically selected values from the query.
> > So I need a dynamic way to define the expression based on input parameters.
> >
> > Is Custom code is the way to go ? How do I access the Parameters collection,
> > and the Report's data from Custom code ?
> >
> > Any Ideas ?
> >
> > Thanks.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >|||The previous question was asking about display a calculated field on a group
header. You can do this by adding a field to the group header that references
a function defined in VB.NET code in the Report Properties Code tab.
Code sample:
Public Function GetDoubledValue(ByVal NumToDouble As Int) As String
Return CStr(NumToDouble * 2)
End Function
Field Reference:
=Code.GetDoubledValue(4)
"Leneise44" wrote:
> How does one create a custom function that manipulates the headers of a
> report based on parameter values?
> "John W" wrote:
> > I think that custom code will be needed. You can pass the values you need
> > (Parameters, Report Data) in as parameters to a function.
> >
> > "NL" wrote:
> >
> > > I would like to display calculated field on a group header.
> > > The nature of the calculation must be passed as a parameter to the report.
> > >
> > > For example lets assume that my report lists X,Y pairs.
> > > On the break I would like to display the value of
> > > Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> > >
> > > It is not practical to prepare an expression for every possible option and
> > > use a selector as X,Y are also dynamically selected values from the query.
> > > So I need a dynamic way to define the expression based on input parameters.
> > >
> > > Is Custom code is the way to go ? How do I access the Parameters collection,
> > > and the Report's data from Custom code ?
> > >
> > > Any Ideas ?
> > >
> > > Thanks.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >

Dynamic display of SQL in Excel?

I have WebTrends web site traffic analysis running nightly on a SQL DB server. Every month, I must hit over 50 report URLs (pages use frames) to manually caputure 3 numbers into an Excel spreadsheet.

Can anyone provide any pointers how I might go about building a spreadsheet that could caputure/display this data automatically?

Thanks.See if under Data menu...Import External Data...New Web Query

Not sure of the particulars but it would get you started.

Originally posted by shadowplayer
I have WebTrends web site traffic analysis running nightly on a SQL DB server. Every month, I must hit over 50 report URLs (pages use frames) to manually caputure 3 numbers into an Excel spreadsheet.

Can anyone provide any pointers how I might go about building a spreadsheet that could caputure/display this data automatically?

Thanks.|||Thanks for your reply. That's exactly what I ended up doing. Found it by accident when I noticed the "little yellow box" that appeared when I cut & pasted data from the webpage into my spreadsheet.

I just saved the qry file and opened it in notepad to config the values.

This will save me HOURS of work.

VERY cool.

Thanks again for your reply!

Dynamic Decimal Format

I have a text field = 3303.123456.
I want to display decimals based on a dynanic parameter.
For example if 4 decimals = 3303.1234. If 2 = 3303.12.
The dynamic parameter will be a field in the data set.You can dynamically generate a format code string on a textbox. Assuming you
have an integer parameter called Decimals, you would set the Format property
of the textbox to an expression like this: ="N" &
Parameters!Decimals.Value.ToString()
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott2624" <Scott2624@.discussions.microsoft.com> wrote in message
news:55FC071E-E0A0-4AF4-8015-5F2F2DA7D082@.microsoft.com...
>I have a text field = 3303.123456.
> I want to display decimals based on a dynanic parameter.
> For example if 4 decimals = 3303.1234. If 2 = 3303.12.
> The dynamic parameter will be a field in the data set.

Dynamic Dates in Columns on Matrix Report?

I need to display 12 months dynamically in columns of a matrix report, starting with the current MMM, yy - 3 in the first column, current MMM, yy and incrementing by 1 month in columns 2-12.

For example a report that would run on today's MM, yy (5/2007) would look something like this:

MAR-07, APR-07, MAY-07, JUN-07, JUL-07, AUG-07, AUG-07, SEP-07, OCT-07, NOV-07, DEC-07, JAN-08, FEB-08

(Col 1 - Col 12 separated by commas)

How do I do this in a matrix report column?

1- Filter your datasource to return only those rows that meet the date criteria you describe

If SQL Server...

Code Snippet

Select Item, SalesDate, Sales from YourTable Where SalesDate >= DateAdd(dd, -3, GetDate()) AND SalesDate <= DateAdd(dd, 9, GetDate())

For example:

Item Date Sales

Bicycle Mar-07 $5000

Bicycle Apr-07 $4500

etc...

Then set your date column as the Column Group in the matrix report

Then select that column header in SSRS Layout view, and change the format property to the MMM-yy

(If the granularity of the dates in your source data is not month, if it's by day instead for instance, then you'll need to aggregate that data in your query with the appropriate Group By calls)

Hope this helps,

Geof

Dynamic Dates in Columns on Matrix Report

I need to display 12 months dynamically in columns of a matrix report, starting with the current MMM, yy - 3 in the first column, current MMM, yy and incrementing by 1 month in columns 2-12.

For example a report that would run on today's MM, yy (5/2007) would look something like this:

MAR-07, APR-07, MAY-07, JUN-07, JUL-07, AUG-07, AUG-07, SEP-07, OCT-07, NOV-07, DEC-07, JAN-08, FEB-08

(Col 1 - Col 12 separated by commas)

How do I do this in a matrix report column?

I would do this by created an enumerated type for months and then iterate through them based on current month value(5). when you reach 12(or 13 depending on starting value) then reset to 0 or 1 respectively and increase the year by one.

Eric

|||

I'm still not sure how to actually do this. Can you send me an example of how to do this?

|||

Can you post your code please so I understand what you need.

|||

I ended up declaring a begindate and enddate, based on the current month in the stored procedure and passed that in.

Dynamic database with MS ACCESS

Hi,
I am using ASP.NET to display reports using the report viewer object.
I have multiple MS Access databases that can be used as the datasource to a
report.
I know how to make a connection to multiple different SQL databases passing
the database as a parameter in the URL of my query and then into my stored
procs, but the same technique cannot be applied to MS Access databases.
I figured out that the parameter to be passed must be the *.mdb file path on
the server... but where to use it?
ThxI have a huge query for as one of my datasets, something like this (this
query works well) :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN 'c:\db1.mdb'
ORDER BY tabl1.x
I tried the following :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN @.DBPath
ORDER BY tabl1.x
But apparently u can't use a named parameter with OLE DB ... so I tried this :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN ?
ORDER BY tabl1.x
But when I execute the query and am asked what value should ? take, whatever
the value i enter it doesnt work. I tried 'c:\db1.mdb', c:\db1.mdb, and
[c:\db1.mdb]
Nothing works. I also don't know how to map the ? variable to a @.Parameter,
because the ? doesn't generate a parameter in the parameter tab ...
AAAaaaarggg!|||You are right about using unnamed parameters. First, are you in the generic
query designer (2 panes). You will want to be there (button is to the right
of the ...). Next, if a parameter is not created automatically for you then
go to the form design, menu report->report parameters and add a parameter.
Then go back to the data tab, click on the ..., go to the parameters tab and
then put in the ? on the left and select your parameter on the right.
My guess is that you cannot just have a parameter in your query where it is
not part of the where clause (which in this case is not). However, you can
still do this. Use an expression which would look like this:
= "SELECT tabl1.*, ..., tablx.* FROM tabl1, ... tablx IN '" &
Parameters!Paramname.Value & "' ORDER BY tabl1.x"
I sometimes create a report with no datasource, just my parameters and a
textbox. I set the textbox to the expression so I can see what I have
created so I know if it is correct before I set the dataset to this value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6024F625-1721-4212-AB86-B8C131A12CE2@.microsoft.com...
>I have a huge query for as one of my datasets, something like this (this
> query works well) :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN 'c:\db1.mdb'
> ORDER BY tabl1.x
> I tried the following :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN @.DBPath
> ORDER BY tabl1.x
> But apparently u can't use a named parameter with OLE DB ... so I tried
> this :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN ?
> ORDER BY tabl1.x
> But when I execute the query and am asked what value should ? take,
> whatever
> the value i enter it doesnt work. I tried 'c:\db1.mdb', c:\db1.mdb, and
> [c:\db1.mdb]
> Nothing works. I also don't know how to map the ? variable to a
> @.Parameter,
> because the ? doesn't generate a parameter in the parameter tab ...
> AAAaaaarggg!
>

Dynamic database tables

How does one make a database table dynamic?
I need to have a single report display data from different SQL tables
depending on which client is viewing the data.Here's an example:
1. Run the following static SQL in data pane of report designer: select
name, xtype, crdate from sysobjects
2. Switch to Layout view. This creates the fields in fields window.
3. Go back to the data pane and pase the following query into it: ="select
name, xtype, crdate from " & iif(LCase(User!UserID) = "redmond\ravimu",
"pubs", "northwind") & "..sysobjects where type = 'u'". This will
dynamically source the data from diferent table depending on the current
user.
Notes:
1. Assumption: Table schemas are the same.
2. If the target tables have same number of columna but different names, you
can map each column name to a unique alias as follows: select name As Col1,
xtype As Col2, crdate As Col3 from sysobjects
3. If you have many users, you may want to replace iif() with Switch()
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html
/vafctswitch.asp)
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"jimmy stewart" <jims@.wolfmagick.com> wrote in message
news:u9$uYmQbEHA.3944@.tk2msftngp13.phx.gbl...
> How does one make a database table dynamic?
> I need to have a single report display data from different SQL tables
> depending on which client is viewing the data.
>

Dynamic Data in Page Header?

Hi all,
is there's a way to add fields to the Page Header?
We have to display dynamic data on each page...
Thanks
ThomasThomas,
What kind of data is this? If its a calculation, global parameter,
date, or parameters passed in, this is very easy by adding an
expression(Somehting like: Parameters!Param1.Value).
If its Fields, then you can create a group, and place that field in the
group header - but you must group it by some field.
Then Left Click on that row(the group header) and set
theRepeatOnEveryPage property to true.
I hope this helps.
regards,
Stas K.|||Hi Thomas
No, you cannot add data fields to the Page Header, because it cannot have a
defined dataset. However, depending on the your data and how you want to
display it, here is a another solution:
1) get rid of the page header section of the report,
2) in the body of the report, use a table as the main container and make it
as wide as the width of the report, and make the table top start at the very
top of the report.
3) define the dataset for that table in the properties
4) add a table header row, and use that as the "page header". Make sure to
check the "Repeat on New page" option for the header row properties.
5) select all cells in the header row, and merge them.
6) drop a rectangle in the newly merged header cell, and voila, you have a
freeform container for your cell, so you can drop textboxes in there, etc...
In most of my reports, I have done that, instead of using a page header,
since in most cases I need to use a datafield as part of the header anyways.
You can have several header rows defined for each section in a table kinda
like this:
<table>
<tblHeader1>
<tblHeader2>
<tbleHeader3>
<group1Header1>
<group1Header2>
<group2Header1>
<group2Header2>
<detailRow>
<group2Footer1>
<group2Footer2>
<group1Footer1>
<group1Footer2>
<tblFooter1>
<tblFooter2>
</table>
Hope that helps ya.
--
Regards,
Thiago Silva
"Thomas" wrote:
> Hi all,
> is there's a way to add fields to the Page Header?
> We have to display dynamic data on each page...
> Thanks
> Thomas
>
>

dynamic columns/aliases - Continue

Hi
Following my previous question. Here is the implemetation.
I have to design a report which display monthes as columns.
The number of columns in the report depends on the range paramters.
For example : if the Range is from 01/02/05 to 30/06/05 there ara 6 columns
shown.
01/05,02/05....06/05. (column alias) .
if the range of dates are 8 monthes range there are 8 dynamic columns, etc'
1) My first major problem is to write a SP or a function that return a
dynamic number of columns.
2) Second,(This is not a must) to change column alias (Heading) according to
the parameters.
thanksWhat reporting tool do you use? This is called a cross tab report and
any decent reporting tool will render it for you client-side with no
programming required. Otherwise you'll have to use dynamic SQL, which
is not much fun.
David Portas
SQL Server MVP
--|||>> 1) My first major problem is to write a SP or a function that return a dy
namic number of columns. <<
NO, your **first** problem is that you missed the basic concepts of
RDBMS. A query returns a table; a table has a fixed number of columns.
A query is not a report; a query sends information to a front end
which can have a reporting tool in it.
This is definitions, not anything fancy.
\If you want to be a bad SQL programmer, this can be done with dynamic
SQL and a ton of procedural code that will be a XXXXX to maintain.|||Yes. My Big Problem is indeed the Reporting tool which is Custom Reporting
tool of an ERP software that simply translate
the resulted query to a GUI look alike Report.
Unfortunately , There's Nothing I can do about it.
What are my options with dynamic sql ?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1126806242.941597.208120@.g44g2000cwa.googlegroups.com...
> What reporting tool do you use? This is called a cross tab report and
> any decent reporting tool will render it for you client-side with no
> programming required. Otherwise you'll have to use dynamic SQL, which
> is not much fun.
> --
> David Portas
> SQL Server MVP
> --
>

Dynamic columns

Hi,

In my report i want to display dynamic columns.For displaying what are columns available to user,Iam displaying the columns names in one multivalued parameter.For example in my report i have Date,CaseId,Age,State as columns,and multivalued parameter name is "Columns".

I wrote a custom function i.e ShowParameterValues(...) returns String s.If we select the Date and CaseID from the columns then s contain value:Date CaseID

Public Function ShowParameterValues(ByVal parameter as Parameter) as string
Dim s as String
For i as integer = 0 to parameter.Count-1
s =s+ CStr(parameter.Value(i))
Next
Return s
End Function

Now i want to show those two columns only.How to acheve this.

Thanks in advance

Currently, the RS object model doesn't support this feature. You can:

1. Include all columns on the report and hiding the ones that are not needed conditionally (Hidden property = True)

2. Implement a custom application which changes and uploads the report definition.

|||

Hi,

How to hide some columns conditionally.I need all the columns,show the columns which the user selects from multivalued parameter.

Thanks in advance

|||

Each column has a Hidden property which can be expression-based, e.g.:

=IIF(<evaluate your parameter selection condition>, True, False)

|||

Hi,

I know this.But how to write the condition,how would we know which column is selected from multivalued parameter

Please help me

Thanks in advance

|||Hi,
i am doing something similar for a selector where the user pick a start period and an end period. I am not using Matrix in this case because I want the table.
Should be similar enough to what you want to do.

In the second Column Hidden property put something like that:

= iif( CountRows("(Items_Selected") <2, True, False)
And increase the number by 1 for each consecutive column.

Have a dataset "Items_Selected" build upon your multi-select, something like that:

select Distinct
Case @.Time_Items when 'Qtr' then b.Qtr when 'Mth' then b.Mth when 'Week' then b.Week end as item
FROM onGlobals.dbo.tb_TimeBillBack b with(nolock)
where Case @.Time_Items when 'Qtr' then b.Qtr when 'Mth' then b.Mth when 'Week' then b.Week end >= @.Period_Start
AND Case @.Time_Items when 'Qtr' then b.Qtr when 'Mth' then b.Mth when 'Week' then b.Week end <= @.Period_End

Where you would have the b table build with all possible values present in your multi-value parameter.

Change the code above to use a IN clause to evaluate your multi-values.

It is kind of a hack which I hate but it works. Set the report layout with all possible columns and set the hidden property of each and every one of them.
Philippe|||http://msdn2.microsoft.com/en-us/library/aa337293.aspx

Dynamic Columns

Can I create columns dynamically? In other words, progammatically determine the columns to display based on what is returned by the dataset. I know that it is easy to just drag/drop items from my dataset but is there a way to do this programmatically.

You can define a table with all the possible columns, then decide whether to show the column based on the the value of the IsMissing property on Field. http://msdn2.microsoft.com/en-us/library/ms157274(SQL.90).aspx

The other alternative is to generate rdl dynamically based on the dataset.

Dynamic change of number of series on the chart

Depending on the result of my stored procedure logic I need to display
different number of lines on the chart in my report. I can not drop the data
fileds into the chart design as suggested in the Help, because at design time
I do not know their names and how many of them I will have when report is
called. Is it possible? What is involved?
Thanks.Not sure I understand what you are trying to do. It sounds like you want a
dynamic series grouping expression which is basically just based on the
field that comes from the stored procedure.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> Depending on the result of my stored procedure logic I need to display
> different number of lines on the chart in my report. I can not drop the
> data
> fileds into the chart design as suggested in the Help, because at design
> time
> I do not know their names and how many of them I will have when report is
> called. Is it possible? What is involved?
> Thanks.|||Thank you Robert,
You indirectly answered my question. I just could not make sense of grouping
capabilities of the chart. The Help on this topic is not very "helpful".
"Robert Bruckner [MSFT]" wrote:
> Not sure I understand what you are trying to do. It sounds like you want a
> dynamic series grouping expression which is basically just based on the
> field that comes from the stored procedure.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > Depending on the result of my stored procedure logic I need to display
> > different number of lines on the chart in my report. I can not drop the
> > data
> > fileds into the chart design as suggested in the Help, because at design
> > time
> > I do not know their names and how many of them I will have when report is
> > called. Is it possible? What is involved?
> > Thanks.
>
>|||I am having to do the same thing like dynamically having series elements
depending on the query results... is this possible to do?
"Simon Gold" wrote:
> Thank you Robert,
> You indirectly answered my question. I just could not make sense of grouping
> capabilities of the chart. The Help on this topic is not very "helpful".
> "Robert Bruckner [MSFT]" wrote:
> > Not sure I understand what you are trying to do. It sounds like you want a
> > dynamic series grouping expression which is basically just based on the
> > field that comes from the stored procedure.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> > news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > > Depending on the result of my stored procedure logic I need to display
> > > different number of lines on the chart in my report. I can not drop the
> > > data
> > > fileds into the chart design as suggested in the Help, because at design
> > > time
> > > I do not know their names and how many of them I will have when report is
> > > called. Is it possible? What is involved?
> > > Thanks.
> >
> >
> >|||Yes, It is possible to do that. The "Help" does not have a good wordage on
it, but here how you do it.
Imagine that you want to display stock prices for several different
companies for a say 10 days period. Create a table 'Prices' that would have
at least three columns: 'DayNumber', 'StockPrice' and 'CompanyName'. Populate
that table with your data. Create a dataset for your chart where run a
"SELECT DayNumber,StockPrice, CompanyName FROM Prices".
Om the chart drug and drop 'DayNumber field under the horizontal axis
(Category fields), drop StockPrice field above the chart into 'data fields'
and drop 'Companyname' filed to the right of the chart into 'Series fileds'.
This will create a chart that will have as many series as many company names
you have in your table.
Good luck...
Simon.
StockPrice
"Mathi" wrote:
> I am having to do the same thing like dynamically having series elements
> depending on the query results... is this possible to do?
> "Simon Gold" wrote:
> > Thank you Robert,
> > You indirectly answered my question. I just could not make sense of grouping
> > capabilities of the chart. The Help on this topic is not very "helpful".
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Not sure I understand what you are trying to do. It sounds like you want a
> > > dynamic series grouping expression which is basically just based on the
> > > field that comes from the stored procedure.
> > >
> > > -- Robert
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > > "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> > > news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > > > Depending on the result of my stored procedure logic I need to display
> > > > different number of lines on the chart in my report. I can not drop the
> > > > data
> > > > fileds into the chart design as suggested in the Help, because at design
> > > > time
> > > > I do not know their names and how many of them I will have when report is
> > > > called. Is it possible? What is involved?
> > > > Thanks.
> > >
> > >
> > >

Dynamic capabilities with Parameters

Hello all,

I have a winform application, where in we get the parameters from the reporting service and we display them in a panel for user's to input their criteria. So far everything worked good, but now we have a requirement wherein dynamic functionality is needed.

For Example:

Based on a selection of a combobox value, we want the other control to be enabled or disabled. Also we want default value of a control to be calculated based on another control's value like .. if user enters value "1" in textbox1 then texbox 2 should have default value of "6". (Textbox1 + 5).........and mix and match of such capabilities.

The Question is........IS IT POSSIBLE? and if yes...HOW!!!

VDeepak,

You can not enable and disable control, but you can provide default value to parameters by using "Cascade Parameters". I customary write a stored procedure to provide my values.

Ham

|||

Thanks for the reply Cris,

Getting the default values from strored proceduers was never a problem. The problem here is to get the dynamic behaviour of the controls based on the retrieved parameters from the reporting services.

any help would be appriciated.

|||

Oops.. typo Carl to cris....sorry

|||

Not a problem,

Okay the approach could be textbox1 is parameter pMyFirst, Textbox2 is parameter mysecond. On your second parameter fire by your default stored procedure "sp_mysecond" witht akes a parameter of "pMyFirst" inside my sp_second goes like:

If @.pMyFirst = 1

begin

return 5+1

end

If @.pMyFirst = 2

begin

return 6+2

end

Now your second textbox should have the require value based on your business rule.

Ham

|||

Thanks for the response Carl.

The validation which you detailed can be done in reporting service . But , as I explained earlier I am brining all the parameters to win-forms application which is our data entry point for the end users. Here when the user edits any default value then this change should reflect in concurrent parameters with the appropriate values in the win-form app. This is a dynamic change in all parameters.

Hope I explained it clear...

|||

VDeepak,

My bad, I missed the operative word "Win-Forms Application" - Sorry I have not yet done any Reporting Service in a Win-Form application so my experience is limited in this area.

Ham