Easy Reporting

Hello,

I am searching for a reporting tool in which I can graphically and easily report on the cubes built using SQL Server Analysis Services. I don't want build complex reports using SSRS, but instead use something like MS Office Data Analyzer. The version of Data Analyzer I have goes back to 2001. I am able to view reports based on SQL 2005 SSAS but was wondering if there is a new version of this product or perhaps a totally new product that does the same job and is as easy.

Many Thanks,

Grace

? Grace, You might want to take a look at Excel 2007's BI capabilities when Beta 2 becomes available in approximately a couple of months time. Andrew Watt [MVP] <Grace SQL@.discussions.microsoft.com> wrote in message news:dd669009-5729-48c3-9a58-adc79200e070@.discussions.microsoft.com... Hello, I am searching for a reporting tool in which I can graphically and easily report on the cubes built using SQL Server Analysis Services. I don't want build complex reports using SSRS, but instead use something like MS Office Data Analyzer. The version of Data Analyzer I have goes back to 2001. I am able to view reports based on SQL 2005 SSAS but was wondering if there is a new version of this product or perhaps a totally new product that does the same job and is as easy. Many Thanks, Grace|||

Hi Grace

You may also like to try RSinteract from ICS (www.rsinteract.com). RSinteract allows unskilled individuals to build interactive Reporting Services Reports via an intuitive zero footpriint browser UI.

Easy Newbie reporting services question

I'd like to learn more about what I can put in expressions.
For example: Fields!<FieldName>.Value.Length
Is very useful. I found it on this user group but could not find it in
the documentation (online books). The expression painter does not seem
to be much help.
Where can I find more information on what I can put into expressions?
Thanks in advance :)Hi,
Search for "Using Expressions in Reporting Services" and
"Expression Examples in Reporting Services" this on gives you how to use it
with examples.
Hope you might have seen this. but this is very useful. Moreover you can get
the help from "Edit Expression" form. click on any function. you can get the
help.
Amarnath
"mzwilli" wrote:
> I'd like to learn more about what I can put in expressions.
> For example: Fields!<FieldName>.Value.Length
> Is very useful. I found it on this user group but could not find it in
> the documentation (online books). The expression painter does not seem
> to be much help.
> Where can I find more information on what I can put into expressions?
> Thanks in advance :)
>

Easy Admin Question

OK, As an Admin on the server, domain, SQL Server and Reportin
Services, when I log into the Report Manager website, I see the folder
"User Folders". I can navigate into that and see everyone's personal
"My Reports" folders.
Now, I have made a developer a Reporting Services Admin by adding him
to the RS Site Role of "System Admin" so that he can modify and
extract some user's reports that they saved in their My Reports
location.
For some reason he is unable to see the "User Folders" folder. I have
verified he is assigned to the System Admin role in RS so I'm unsure
what else I need to do to allow him to see the "User Folders" folder.
Any help anyone could provide is greatly appreciated!
Thanks in advance.
MartinSystem Administrator role does not include permissions to access catalog
items.
Quoting BOL (http://msdn2.microsoft.com/en-us/library/ms156470.aspx)
The System Administrator role is a predefined role that includes tasks that
are useful for a report server administrator who has overall responsibility
for a report server, but not necessarily for the content within it.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<martinghale@.gmail.com> wrote in message
news:1173921810.864000.92000@.n59g2000hsh.googlegroups.com...
> OK, As an Admin on the server, domain, SQL Server and Reportin
> Services, when I log into the Report Manager website, I see the folder
> "User Folders". I can navigate into that and see everyone's personal
> "My Reports" folders.
> Now, I have made a developer a Reporting Services Admin by adding him
> to the RS Site Role of "System Admin" so that he can modify and
> extract some user's reports that they saved in their My Reports
> location.
> For some reason he is unable to see the "User Folders" folder. I have
> verified he is assigned to the System Admin role in RS so I'm unsure
> what else I need to do to allow him to see the "User Folders" folder.
> Any help anyone could provide is greatly appreciated!
> Thanks in advance.
> Martin
>|||Thank you Lev,
So after reading that and messing around with all the settings, I was
finely able to get it working. Had to set permissions at the Folder
and Report levels so that I, as an Admin, could 'control' a user's
reports they created and stored in their My Folders location.
Thanks for the link. It helped in my understanding and tracking this
down.

Early User Questions for developing large number of reports

Hi All,
Firstly, let me say who we are.....a group of us are developing a BI
product where we have decided to use Report Services as the reporting
tool. We are long term BI experts and IT consultants......We are
experienced in all manner of reporting and query tools we are just new
to RS. We settled on RS as our 'lower end' reporting tool for our
product and we will give our clients the 'option' of selecting other
reporting tools but the 'out of the box' reports will be in RS.
We have been dilligent and done our reading (WROX RS book) and testing
and figured out most of what we want to do. We have our first report up
and running and we are learning what the limits of RS seem to be to us.
So hopefully I will not ask too many 'dumb' questions... :-)
We also hope by asking questions here that other users of RS might also
benefit from the public discussion.
Q1. We are also working on our 'templates' for reports and looking at
how we develop a large number (200-300) of reports according to a
'template'. In the WROX Book they talk about codesmith and a way of
handling templates. But we didn't 'get' it...Also, when we try using
the VS.NET report wizard it seems that it insists on a query rather
than a stored procedure for it's data for the wizard to work.
So we are thinking we need to write each report by copying the rdl xml,
editting it by hand and then putting it into vs.net to further edit.
We have no real problem with that but we were thinking someone must
have come up with a better idea than editing the XML by now!!!
Has anyone published papers/ideas/tools (fee or free) to manage the
development and deployment of large numbers of reports?
Q2. We see MSFT has been out talking up RS2005/SQL Server 2005 and
talking about the pricing of SQL Server 2005. But we have not seen any
comment on the possible cost of RS2005 if it is NOT purchased with SQL
Server 2005. We would like to have the option of deploying our product
onto SQL Server with RS running either on that machine or elsewhere. Is
there any public comment from MSFT as to the possible pricing of RS2005
when not purchased with SQL Server?
Q3. Is there any published information on 'best practice' or 'best
examples of usage' of report services? For example MicroStrategy,
Business Objects etc post demos of their products to showcase the 'best
possible use' of the product. Dundas have also put out some really
great demos. But the RS demos from MSFTs web site look a little
'plain' especially the graphics. Are there any really great examples
of RS 'out there' or are the demos on the MSFT web site the best there
is? We have decided to go with DNN for the portal and we are currently
testing RS/DNN integration.
Q4. Layout of prompts on a report.
We have reports with quite a few prompts and they are 'grouped'. For
example we have a set of 4 prompts for the user to select the levels in
the company reporting structure. ANd then we have prompts for the
client to select year and month. Then we might have campaign structure
selection which is 3 levels....so we would like to lay out
Line 1: 4 prompts
Line 2: 2 Prompts
Line 3: 3 prompts
We are told this is not supported...but it also seems like it only
ever places two prompts on one line? If we cannot group prompts can we
at least get it to put more than 2 prompts per line?
Thanks in Advance
Peter Nolan
www.peternolan.comRS is like most of the other MS products: a "back-end" tool with high
custimization available. You can extend it from many way.
Q2:
The licensing is the same as 2000. RS is part of SQL 2005 and if you want
report capabilities only, you have to buy a license of SQL 2005. But the RS
repository required SQL Server.
So for your answer, yes RS can be purchased "separatly" but at the full
price of SQL 2005.
Q3:
On the MS web site, there is some samples reports for different usage:
CRM, Exchange, Axapta, SQL Logs, RS logs.
Best pratices & usages depend on your needs.
Designing printing reports is different from web based usage only reports.
Today there is nothing except simple samples. But these small samples don't
limit me to use RS.
Generally I start from an overall report, then I link to detailed reports.
Q4:
Create you own prompt page if you want to create a specific layout.
Remember that RS is designed for developpers to include reporting capability
into their applications.
But there is a lack of sample and helps to create front end tools around RS.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1120988013.636226.99630@.o13g2000cwo.googlegroups.com...
> Hi All,
> Firstly, let me say who we are.....a group of us are developing a BI
> product where we have decided to use Report Services as the reporting
> tool. We are long term BI experts and IT consultants......We are
> experienced in all manner of reporting and query tools we are just new
> to RS. We settled on RS as our 'lower end' reporting tool for our
> product and we will give our clients the 'option' of selecting other
> reporting tools but the 'out of the box' reports will be in RS.
> We have been dilligent and done our reading (WROX RS book) and testing
> and figured out most of what we want to do. We have our first report up
> and running and we are learning what the limits of RS seem to be to us.
> So hopefully I will not ask too many 'dumb' questions... :-)
> We also hope by asking questions here that other users of RS might also
> benefit from the public discussion.
>
> Q1. We are also working on our 'templates' for reports and looking at
> how we develop a large number (200-300) of reports according to a
> 'template'. In the WROX Book they talk about codesmith and a way of
> handling templates. But we didn't 'get' it...Also, when we try using
> the VS.NET report wizard it seems that it insists on a query rather
> than a stored procedure for it's data for the wizard to work.
> So we are thinking we need to write each report by copying the rdl xml,
> editting it by hand and then putting it into vs.net to further edit.
> We have no real problem with that but we were thinking someone must
> have come up with a better idea than editing the XML by now!!!
> Has anyone published papers/ideas/tools (fee or free) to manage the
> development and deployment of large numbers of reports?
>
> Q2. We see MSFT has been out talking up RS2005/SQL Server 2005 and
> talking about the pricing of SQL Server 2005. But we have not seen any
> comment on the possible cost of RS2005 if it is NOT purchased with SQL
> Server 2005. We would like to have the option of deploying our product
> onto SQL Server with RS running either on that machine or elsewhere. Is
> there any public comment from MSFT as to the possible pricing of RS2005
> when not purchased with SQL Server?
>
> Q3. Is there any published information on 'best practice' or 'best
> examples of usage' of report services? For example MicroStrategy,
> Business Objects etc post demos of their products to showcase the 'best
> possible use' of the product. Dundas have also put out some really
> great demos. But the RS demos from MSFTs web site look a little
> 'plain' especially the graphics. Are there any really great examples
> of RS 'out there' or are the demos on the MSFT web site the best there
> is? We have decided to go with DNN for the portal and we are currently
> testing RS/DNN integration.
>
> Q4. Layout of prompts on a report.
> We have reports with quite a few prompts and they are 'grouped'. For
> example we have a set of 4 prompts for the user to select the levels in
> the company reporting structure. ANd then we have prompts for the
> client to select year and month. Then we might have campaign structure
> selection which is 3 levels....so we would like to lay out
> Line 1: 4 prompts
> Line 2: 2 Prompts
> Line 3: 3 prompts
> We are told this is not supported...but it also seems like it only
> ever places two prompts on one line? If we cannot group prompts can we
> at least get it to put more than 2 prompts per line?
>
> Thanks in Advance
>
> Peter Nolan
> www.peternolan.com
>|||Hi Jerome,
Q2. In licensing MSFT talks about buying RS when it needs to be
deployed on a server other than the SQL Server...and the WROX book
talks about this as being a 'medium' sized deployment.....if that
means that we need to buy a full SQL Server license (I guess workgroup)
to run RS on a separate server that's ok...we just wanted to
know....but that seems a bit pricey....
Q4. I found examples on odetocode web site but most of the examples I
am finding are 'fragments' intended for developers to include into
already designed/built apps.....that's ok..if there are no other
examples out there we can start from where we are at...just wanted to
check there was not something more publicly available before we spent
our time and effort...it's kind of suprising MSFT has not published
some more things to give developers a better base to work from to go up
against the likes of Crystal/Business Objects/Cognos...maybe more is
coming'
Best Regards
Peter|||Q1, Q3, and Q4--answered in depth in Peter Blackburn and William Vaughn's
Hitchhiker's Guide!
Amazon: http://shrinkster.com/6km
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1120988013.636226.99630@.o13g2000cwo.googlegroups.com...
> Hi All,
> Firstly, let me say who we are.....a group of us are developing a BI
> product where we have decided to use Report Services as the reporting
> tool. We are long term BI experts and IT consultants......We are
> experienced in all manner of reporting and query tools we are just new
> to RS. We settled on RS as our 'lower end' reporting tool for our
> product and we will give our clients the 'option' of selecting other
> reporting tools but the 'out of the box' reports will be in RS.
> We have been dilligent and done our reading (WROX RS book) and testing
> and figured out most of what we want to do. We have our first report up
> and running and we are learning what the limits of RS seem to be to us.
> So hopefully I will not ask too many 'dumb' questions... :-)
> We also hope by asking questions here that other users of RS might also
> benefit from the public discussion.
>
> Q1. We are also working on our 'templates' for reports and looking at
> how we develop a large number (200-300) of reports according to a
> 'template'. In the WROX Book they talk about codesmith and a way of
> handling templates. But we didn't 'get' it...Also, when we try using
> the VS.NET report wizard it seems that it insists on a query rather
> than a stored procedure for it's data for the wizard to work.
> So we are thinking we need to write each report by copying the rdl xml,
> editting it by hand and then putting it into vs.net to further edit.
> We have no real problem with that but we were thinking someone must
> have come up with a better idea than editing the XML by now!!!
> Has anyone published papers/ideas/tools (fee or free) to manage the
> development and deployment of large numbers of reports?
>
> Q2. We see MSFT has been out talking up RS2005/SQL Server 2005 and
> talking about the pricing of SQL Server 2005. But we have not seen any
> comment on the possible cost of RS2005 if it is NOT purchased with SQL
> Server 2005. We would like to have the option of deploying our product
> onto SQL Server with RS running either on that machine or elsewhere. Is
> there any public comment from MSFT as to the possible pricing of RS2005
> when not purchased with SQL Server?
>
> Q3. Is there any published information on 'best practice' or 'best
> examples of usage' of report services? For example MicroStrategy,
> Business Objects etc post demos of their products to showcase the 'best
> possible use' of the product. Dundas have also put out some really
> great demos. But the RS demos from MSFTs web site look a little
> 'plain' especially the graphics. Are there any really great examples
> of RS 'out there' or are the demos on the MSFT web site the best there
> is? We have decided to go with DNN for the portal and we are currently
> testing RS/DNN integration.
>
> Q4. Layout of prompts on a report.
> We have reports with quite a few prompts and they are 'grouped'. For
> example we have a set of 4 prompts for the user to select the levels in
> the company reporting structure. ANd then we have prompts for the
> client to select year and month. Then we might have campaign structure
> selection which is 3 levels....so we would like to lay out
> Line 1: 4 prompts
> Line 2: 2 Prompts
> Line 3: 3 prompts
> We are told this is not supported...but it also seems like it only
> ever places two prompts on one line? If we cannot group prompts can we
> at least get it to put more than 2 prompts per line?
>
> Thanks in Advance
>
> Peter Nolan
> www.peternolan.com
>|||thanks for the pointer goodman 93...I'll see if I can get a copy of
that too.....
Peter

DynamicValuesUnavailable state using wsRS.GetReportParameters

Hi,
I am using the webservice ReportingService from the Reporting Services Web
Service Library.
I'm having a problem when retrieving Parameters for a RS report, valid
values are not returned when they are QueryBased.
It works fine when accessing the report through the RSServerName/reports
virtual dir. Then a dropdown with valid values for the parameter is perfectl
y
populated. As far as I know the "Reports" website uses the ReportingServices
WebService methods?!
When parameters are return from the GetReportParameters method, the
ReportParameters array is correct populated at first glance. The parameters
prm.ValidValuesQueryBased is True, but further investigation reveals that th
e
prm.ValidValues is nothing (VB) and the prm.State of the parameter is
DynamicValuesUnavailable. The prm.ValidValues is supposed to be an array of
the ValidValue class.....
I cannot seem to find any documentation why the prm.State is
DynamicValuesUnavailable. Has anyone tried this, and does anyone know a
solution?
Thanks GlennI have now found the answer to my question. So for your information if anyon
e
should need it:
When invoking the method GetReportParameters on the ReportingServices
service be sure to pass True as value for the ForRendering parameter. When
this is done ValidValues are returned!
/Glenn
"Glenn S?rensen" wrote:

> Hi,
> I am using the webservice ReportingService from the Reporting Services Web
> Service Library.
> I'm having a problem when retrieving Parameters for a RS report, valid
> values are not returned when they are QueryBased.
> It works fine when accessing the report through the RSServerName/reports
> virtual dir. Then a dropdown with valid values for the parameter is perfec
tly
> populated. As far as I know the "Reports" website uses the ReportingServic
es
> WebService methods?!
> When parameters are return from the GetReportParameters method, the
> ReportParameters array is correct populated at first glance. The parameter
s
> prm.ValidValuesQueryBased is True, but further investigation reveals that
the
> prm.ValidValues is nothing (VB) and the prm.State of the parameter is
> DynamicValuesUnavailable. The prm.ValidValues is supposed to be an array o
f
> the ValidValue class.....
> I cannot seem to find any documentation why the prm.State is
> DynamicValuesUnavailable. Has anyone tried this, and does anyone know a
> solution?
> Thanks Glenn

DynamicValuesUnavailable state using wsRS.GetReportParameters

Hi,
I am using the webservice ReportingService from the Reporting Services Web
Service Library.
I'm having a problem when retrieving Parameters for a RS report, valid
values are not returned when they are QueryBased.
It works fine when accessing the report through the RSServerName/reports
virtual dir. Then a dropdown with valid values for the parameter is perfectly
populated. As far as I know the "Reports" website uses the ReportingServices
WebService methods?!
When parameters are return from the GetReportParameters method, the
ReportParameters array is correct populated at first glance. The parameters
prm.ValidValuesQueryBased is True, but further investigation reveals that the
prm.ValidValues is nothing (VB) and the prm.State of the parameter is
DynamicValuesUnavailable. The prm.ValidValues is supposed to be an array of
the ValidValue class.....
I cannot seem to find any documentation why the prm.State is
DynamicValuesUnavailable. Has anyone tried this, and does anyone know a
solution?
Thanks Glenn
I have now found the answer to my question. So for your information if anyone
should need it:
When invoking the method GetReportParameters on the ReportingServices
service be sure to pass True as value for the ForRendering parameter. When
this is done ValidValues are returned!
/Glenn
"Glenn S?rensen" wrote:

> Hi,
> I am using the webservice ReportingService from the Reporting Services Web
> Service Library.
> I'm having a problem when retrieving Parameters for a RS report, valid
> values are not returned when they are QueryBased.
> It works fine when accessing the report through the RSServerName/reports
> virtual dir. Then a dropdown with valid values for the parameter is perfectly
> populated. As far as I know the "Reports" website uses the ReportingServices
> WebService methods?!
> When parameters are return from the GetReportParameters method, the
> ReportParameters array is correct populated at first glance. The parameters
> prm.ValidValuesQueryBased is True, but further investigation reveals that the
> prm.ValidValues is nothing (VB) and the prm.State of the parameter is
> DynamicValuesUnavailable. The prm.ValidValues is supposed to be an array of
> the ValidValue class.....
> I cannot seem to find any documentation why the prm.State is
> DynamicValuesUnavailable. Has anyone tried this, and does anyone know a
> solution?
> Thanks Glenn

Dynamicly build report against Reporting Services

Hello Every one,
I would like to know if it's possible to dynamicly build report againt
RS ?
Thanks alots
Best Regards and seasons greetings!
Patrice Lamarche
Groupe Conseil OmnitechPossible but not that easy. Here is what you have to do today. First, you
need to learn the rdl specification (you can find that on MS site) which is
XML and create the report yourself. Then you have to deploy it using web
services. Since it is a server based solution you have to deploy it with a
unique name so that only the user you want to use it uses it. So, two things
skills you have to have for this: web services and creating the rdl on the
fly. With Yukon there will be both a winform and webform control that will
work with the server if it is there but does not require it. In theory (I
have not use it since it is not even out in beta yet) you should be able to
give it the rdl. So with Yukon you at least do not have the hoops to jump
through on deploying the report you generated.
What are you wanting to do that requires dynamically building the report. If
it is data you have the option of filtering the data based on the user.
Also, there are many many places that expressions can be used in the report
that allow a degree of dynamic changes to the report.
My advice is only go this route if you have to.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Patrice Lamarche" <Patrice_lamarche@.Removethis.gco.ca> wrote in message
news:unkvErc6EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hello Every one,
> I would like to know if it's possible to dynamicly build report againt RS
> ?
> Thanks alots
> Best Regards and seasons greetings!
> Patrice Lamarche
> Groupe Conseil Omnitech|||In addition to what Bruce said, this is how I addressed a similiar problem
in the past. The customer wanted to have a report with sections which are
shown on demand, that is the user could select which sections she wants to
see. To minimize development effort, I decideded to have a report template
which has all the sections. Then, it was just a matter of loading the report
RDL in XML DOM and removing the section elements you don't need. I addition,
I had to take care of re-positioning (moving up) the remaining sections to
fill in the emtpy space left by the deleted sections.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O9bMOLd6EHA.2552@.TK2MSFTNGP09.phx.gbl...
> Possible but not that easy. Here is what you have to do today. First, you
> need to learn the rdl specification (you can find that on MS site) which
> is XML and create the report yourself. Then you have to deploy it using
> web services. Since it is a server based solution you have to deploy it
> with a unique name so that only the user you want to use it uses it. So,
> two things skills you have to have for this: web services and creating the
> rdl on the fly. With Yukon there will be both a winform and webform
> control that will work with the server if it is there but does not require
> it. In theory (I have not use it since it is not even out in beta yet) you
> should be able to give it the rdl. So with Yukon you at least do not have
> the hoops to jump through on deploying the report you generated.
> What are you wanting to do that requires dynamically building the report.
> If it is data you have the option of filtering the data based on the user.
> Also, there are many many places that expressions can be used in the
> report that allow a degree of dynamic changes to the report.
> My advice is only go this route if you have to.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Patrice Lamarche" <Patrice_lamarche@.Removethis.gco.ca> wrote in message
> news:unkvErc6EHA.4028@.TK2MSFTNGP15.phx.gbl...
>> Hello Every one,
>> I would like to know if it's possible to dynamicly build report againt RS
>> ?
>> Thanks alots
>> Best Regards and seasons greetings!
>> Patrice Lamarche
>> Groupe Conseil Omnitech
>|||You can generate the RDL/XML on the fly using the RDL reader/writer
http://www.rdlcomponents.com
Jerry
"Teo Lachev [MVP]" wrote:
> In addition to what Bruce said, this is how I addressed a similiar problem
> in the past. The customer wanted to have a report with sections which are
> shown on demand, that is the user could select which sections she wants to
> see. To minimize development effort, I decideded to have a report template
> which has all the sections. Then, it was just a matter of loading the report
> RDL in XML DOM and removing the section elements you don't need. I addition,
> I had to take care of re-positioning (moving up) the remaining sections to
> fill in the emtpy space left by the deleted sections.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:O9bMOLd6EHA.2552@.TK2MSFTNGP09.phx.gbl...
> > Possible but not that easy. Here is what you have to do today. First, you
> > need to learn the rdl specification (you can find that on MS site) which
> > is XML and create the report yourself. Then you have to deploy it using
> > web services. Since it is a server based solution you have to deploy it
> > with a unique name so that only the user you want to use it uses it. So,
> > two things skills you have to have for this: web services and creating the
> > rdl on the fly. With Yukon there will be both a winform and webform
> > control that will work with the server if it is there but does not require
> > it. In theory (I have not use it since it is not even out in beta yet) you
> > should be able to give it the rdl. So with Yukon you at least do not have
> > the hoops to jump through on deploying the report you generated.
> >
> > What are you wanting to do that requires dynamically building the report.
> > If it is data you have the option of filtering the data based on the user.
> > Also, there are many many places that expressions can be used in the
> > report that allow a degree of dynamic changes to the report.
> >
> > My advice is only go this route if you have to.
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Patrice Lamarche" <Patrice_lamarche@.Removethis.gco.ca> wrote in message
> > news:unkvErc6EHA.4028@.TK2MSFTNGP15.phx.gbl...
> >> Hello Every one,
> >>
> >> I would like to know if it's possible to dynamicly build report againt RS
> >> ?
> >>
> >> Thanks alots
> >>
> >> Best Regards and seasons greetings!
> >>
> >> Patrice Lamarche
> >> Groupe Conseil Omnitech
> >
> >
>
>

Dynamically Writing & Rendering Report

Is there a way to Dynamically Writing & Rendering Report for SQL
Server Reporting Services? If so an example would be good.Until there is a render control that does not require the server this
difficult to do. When you publish the report it is there for everyone, so
they can easily step on each other. MS has a document that totally specifies
the xml syntax for RDL. Also, you can open up the report.rdl into an editor
to see what it looks like.
Bruce L-C
"Bila" <bakpan@.teckit.com> wrote in message
news:d26cf8a6.0407291113.6103048@.posting.google.com...
> Is there a way to Dynamically Writing & Rendering Report for SQL
> Server Reporting Services? If so an example would be good.|||A method may be do infer an XSD document from the document MSFT provides and
this will create an object model which you can access just like any other
object model. You can then create your report / content using the object
model, serialize back into XML, and then deploy the report to a server and
invoke the render command.
I'm pretty sure you can pull the RDL specification into a XSD document
(which will create a .vb or .c# file for you), but I haven't tried this
befoe.
-Joel
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OqsarIadEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Until there is a render control that does not require the server this
> difficult to do. When you publish the report it is there for everyone, so
> they can easily step on each other. MS has a document that totally
specifies
> the xml syntax for RDL. Also, you can open up the report.rdl into an
editor
> to see what it looks like.
> Bruce L-C
> "Bila" <bakpan@.teckit.com> wrote in message
> news:d26cf8a6.0407291113.6103048@.posting.google.com...
> > Is there a way to Dynamically Writing & Rendering Report for SQL
> > Server Reporting Services? If so an example would be good.
>|||Still, when you deploy that report is there for everyone. I guess you could
deploy with a unique name just for that particular user but still, not easy,
not fast.
Bruce L-C
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:u15I6lcdEHA.720@.TK2MSFTNGP11.phx.gbl...
> A method may be do infer an XSD document from the document MSFT provides
and
> this will create an object model which you can access just like any other
> object model. You can then create your report / content using the object
> model, serialize back into XML, and then deploy the report to a server and
> invoke the render command.
> I'm pretty sure you can pull the RDL specification into a XSD document
> (which will create a .vb or .c# file for you), but I haven't tried this
> befoe.
> -Joel
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OqsarIadEHA.3420@.TK2MSFTNGP12.phx.gbl...
> > Until there is a render control that does not require the server this
> > difficult to do. When you publish the report it is there for everyone,
so
> > they can easily step on each other. MS has a document that totally
> specifies
> > the xml syntax for RDL. Also, you can open up the report.rdl into an
> editor
> > to see what it looks like.
> >
> > Bruce L-C
> >
> > "Bila" <bakpan@.teckit.com> wrote in message
> > news:d26cf8a6.0407291113.6103048@.posting.google.com...
> > > Is there a way to Dynamically Writing & Rendering Report for SQL
> > > Server Reporting Services? If so an example would be good.
> >
> >
>

Dynamically switching report data connection at run time

We have been presented with the problem of using Reporting Services to run
the same report against one of two databases.
We are currently using SQL Server 2000, and while 2005 has support for
dynamically building connection strings with parameters, 2000 apparently does
not.
Thus far, we have found the following solutions:
- A custom data processing extension, which wraps up a SqlConnection, and
switches database context at run-time based on an expected query parameter
- Reporting against a front-end query on the database, which in turn calls
the query from the desired database
- Installing two versions of the same report on the report server, and
having the application choose which to execute at run-time.
Each of these options has various drawbacks, the first brings with it a mess
of support and deployment issues, the second leans on the database harder
than it needs to, and the third is basically redundant.
Although the solution we need now is to switch between one of two databases,
the ideal solution would be able to manage 1-n databases.
We would appreciate your input as to which solution is the best, or if there
is functionality which would better suit our needs that we havenâ't discovered
yet.On Nov 16, 4:07 pm, breedReed <br...@.community.nospam> wrote:
> We have been presented with the problem of using Reporting Services to run
> the same report against one of two databases.
> We are currently using SQL Server 2000, and while 2005 has support for
> dynamically building connection strings with parameters, 2000 apparently does
> not.
> Thus far, we have found the following solutions:
> - A custom data processing extension, which wraps up a SqlConnection, and
> switches database context at run-time based on an expected query parameter
> - Reporting against a front-end query on the database, which in turn calls
> the query from the desired database
> - Installing two versions of the same report on the report server, and
> having the application choose which to execute at run-time.
> Each of these options has various drawbacks, the first brings with it a mess
> of support and deployment issues, the second leans on the database harder
> than it needs to, and the third is basically redundant.
> Although the solution we need now is to switch between one of two databases,
> the ideal solution would be able to manage 1-n databases.
> We would appreciate your input as to which solution is the best, or if there
> is functionality which would better suit our needs that we haven't discovered
> yet.
I would personally create a SQL Server instance that has Linked
Servers to your two other databases, then in reporting services pass
the SELECT * FROM OPENQUERY( @.ServerName, 'SELECT real SQL here' )
-- Scott

dynamically resize a chart

I am doing a report in SQL Server Reporting Services 2k5 , the report has a chart that displays data from a query. The question is:
Is possible to dynamically resize the area occupied by the chart depending on the amount of information (variations of a category) to be displayed on it ?

One way of achieving this is to use a chart embedded inside another data region’s group. For instance, you could use a list data region with a detail group based on the following expression:
=Ceiling(RowNumber(Nothing)/20)
This will result in one group for every twenty detail rows. Embedding a chart inside that list will create chart instances at runtime for every twenty rows.

If you are looking for an inline bar visualization of data, you can use two different implementation approaches:
* Embedded image with dynamically calculated padding.
* Embedded bar chart with a calculated y-axis maximum value.
Both of these approaches are implemented side-by-side in the TableInlineCharts sample report attached to the bottom of this posting. The sample report is based on the Northwind database.
Note: the embedded image approach dynamically calculates the right padding values of the rectangle that contains the image and thereby achieves bars of variable length!

-- Robert

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>5fda8710-6cb2-4013-bf0e-e8aadd63e007</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table1">
<PageBreakAtStart>true</PageBreakAtStart>
<DataSetName>DataSet1</DataSetName>
<Top>1.125in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<ZIndex>5</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<rd:DefaultName>textbox14</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<rd:DefaultName>textbox15</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Chart Name="chart1">
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Plain</Subtype>
<Title />
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<ThreeDProperties>
<Shading>Simple</Shading>
<DrawingStyle>Cylinder</DrawingStyle>
</ThreeDProperties>
<DataSetName>DataSet1</DataSetName>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderColor>
<Default>DarkGray</Default>
</BorderColor>
<BackgroundColor>White</BackgroundColor>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderColor>
<Default>DarkGray</Default>
</BorderColor>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<Min>0</Min>
<Max>=Max(Fields!UnitsInStock.Value, "DataSet1")</Max>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Bar</Type>
<Palette>SemiTransparent</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitsInStock.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!ProductID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<ZIndex>1</ZIndex>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<ZIndex>8</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontWeight>700</FontWeight>
<BackgroundColor>DarkSeaGreen</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Category</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontWeight>700</FontWeight>
<BackgroundColor>DarkSeaGreen</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Product Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<ZIndex>6</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontWeight>700</FontWeight>
<BackgroundColor>DarkSeaGreen</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Units In Stock</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
<FixedHeader>true</FixedHeader>
</Header>
<TableColumns>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
<TableColumn>
<Width>2.375in</Width>
</TableColumn>
<TableColumn>
<Width>2in</Width>
</TableColumn>
</TableColumns>
<Height>0.75in</Height>
</Table>
<Table Name="table2">
<DataSetName>DataSet1</DataSetName>
<Top>0.125in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<rd:DefaultName>textbox17</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table2_Group1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="ProductName">
<rd:DefaultName>ProductName</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Image Name="image1">
<Sizing>Fit</Sizing>
<Source>Embedded</Source>
<Style>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>=144 * (1.0 - Fields!UnitsInStock.Value / Max(Fields!UnitsInStock.Value, "DataSet1")) &amp; "pt"</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<Value>gradient</Value>
</Image>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table2_Group2">
<GroupExpressions>
<GroupExpression>=Fields!ProductID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontWeight>700</FontWeight>
<BackgroundColor>LightSteelBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Category</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontWeight>700</FontWeight>
<BackgroundColor>LightSteelBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Product Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontWeight>700</FontWeight>
<BackgroundColor>LightSteelBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Units In Stock</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
<FixedHeader>true</FixedHeader>
</Header>
<TableColumns>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
<TableColumn>
<Width>2.375in</Width>
</TableColumn>
<TableColumn>
<Width>2in</Width>
</TableColumn>
</TableColumns>
<Height>0.75in</Height>
</Table>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>ab05eb76-fb3c-4ca3-8892-e2cd67a2d98d</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<EmbeddedImages>
<EmbeddedImage Name="gradient">
<ImageData>iVBORw0KGgoAAAANSUhEUgAAAFwAAAAECAIAAACMUM+VAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAgY0hSTQAAeiYAAICEAAD6AAAAgOgAAHUwAADqYAAAOpgAABdwnLpRPAAAAMBJREFUOE/t0ldOgzEUROGT/Uv03ltooffee29ZzwENgodA+FlApO/Blsa+1sglm6D0hfqLz8x34NdtTaAmXHi8cPrfNxSOq/e8n3M/SrEZW7A12rA9OrAzurAbe7A3+rAfB3AwhnAYR3AUx2IcJ7AckziF0ziDs1HBOZzHhVjEJVzGlVjFtVjHjdjELdzGHdyNPdzHAzyMIzzGkzjFMzzHC7yMK7zGG7zFu7jHB3yMJ3yOF3yNN6xCo5RGKf/6Ke8bJtRZRzfohAAAAABJRU5ErkJggg==</ImageData>
<MIMEType>image/png</MIMEType>
</EmbeddedImage>
</EmbeddedImages>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText>SELECT Products.ProductID, Products.ProductName, Products.SupplierID, Products.CategoryID, Products.QuantityPerUnit, Products.UnitPrice,
Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued, Categories.CategoryName
FROM Products INNER JOIN
Categories ON Products.CategoryID = Categories.CategoryID</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
<Field Name="CategoryName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CategoryName</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Author>Robert M. Bruckner, Microsoft</Author>
<Width>5.625in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

Tanks Robert!

I′ll try your suggestion

|||well, i have some kinda same problem, well in my pie chart, we have so many grous, so what happing , the lables which shows percentages, completely overlap each other, like if a chart is showing 20 groups percentages so they all overlap with each other, i hav data like 80%, and rest are arrround 2, 3, 5, 2, 1, 3, 4, percentages? what should i do plz give us any suggestion

dynamically resize a chart

I am doing a report in SQL Server Reporting Services 2k5 , the report has a chart that displays data from a query. The question is:
Is possible to dynamically resize the area occupied by the chart depending on the amount of information (variations of a category) to be displayed on it ?

One way of achieving this is to use a chart embedded inside another data region’s group. For instance, you could use a list data region with a detail group based on the following expression:
=Ceiling(RowNumber(Nothing)/20)
This will result in one group for every twenty detail rows. Embedding a chart inside that list will create chart instances at runtime for every twenty rows.

If you are looking for an inline bar visualization of data, you can use two different implementation approaches:
* Embedded image with dynamically calculated padding.
* Embedded bar chart with a calculated y-axis maximum value.
Both of these approaches are implemented side-by-side in the TableInlineCharts sample report attached to the bottom of this posting. The sample report is based on the Northwind database.
Note: the embedded image approach dynamically calculates the right padding values of the rectangle that contains the image and thereby achieves bars of variable length!

-- Robert

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>5fda8710-6cb2-4013-bf0e-e8aadd63e007</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table1">
<PageBreakAtStart>true</PageBreakAtStart>
<DataSetName>DataSet1</DataSetName>
<Top>1.125in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<ZIndex>5</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<rd:DefaultName>textbox14</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<rd:DefaultName>textbox15</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Chart Name="chart1">
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Plain</Subtype>
<Title />
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<ThreeDProperties>
<Shading>Simple</Shading>
<DrawingStyle>Cylinder</DrawingStyle>
</ThreeDProperties>
<DataSetName>DataSet1</DataSetName>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderColor>
<Default>DarkGray</Default>
</BorderColor>
<BackgroundColor>White</BackgroundColor>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderColor>
<Default>DarkGray</Default>
</BorderColor>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<Min>0</Min>
<Max>=Max(Fields!UnitsInStock.Value, "DataSet1")</Max>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Bar</Type>
<Palette>SemiTransparent</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitsInStock.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!ProductID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<ZIndex>1</ZIndex>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<ZIndex>8</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontWeight>700</FontWeight>
<BackgroundColor>DarkSeaGreen</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Category</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontWeight>700</FontWeight>
<BackgroundColor>DarkSeaGreen</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Product Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<ZIndex>6</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontWeight>700</FontWeight>
<BackgroundColor>DarkSeaGreen</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Units In Stock</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
<FixedHeader>true</FixedHeader>
</Header>
<TableColumns>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
<TableColumn>
<Width>2.375in</Width>
</TableColumn>
<TableColumn>
<Width>2in</Width>
</TableColumn>
</TableColumns>
<Height>0.75in</Height>
</Table>
<Table Name="table2">
<DataSetName>DataSet1</DataSetName>
<Top>0.125in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<rd:DefaultName>textbox17</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table2_Group1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="ProductName">
<rd:DefaultName>ProductName</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Image Name="image1">
<Sizing>Fit</Sizing>
<Source>Embedded</Source>
<Style>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>=144 * (1.0 - Fields!UnitsInStock.Value / Max(Fields!UnitsInStock.Value, "DataSet1")) &amp; "pt"</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<Value>gradient</Value>
</Image>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table2_Group2">
<GroupExpressions>
<GroupExpression>=Fields!ProductID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontWeight>700</FontWeight>
<BackgroundColor>LightSteelBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Category</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontWeight>700</FontWeight>
<BackgroundColor>LightSteelBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Product Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontWeight>700</FontWeight>
<BackgroundColor>LightSteelBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Units In Stock</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
<FixedHeader>true</FixedHeader>
</Header>
<TableColumns>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
<TableColumn>
<Width>2.375in</Width>
</TableColumn>
<TableColumn>
<Width>2in</Width>
</TableColumn>
</TableColumns>
<Height>0.75in</Height>
</Table>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>ab05eb76-fb3c-4ca3-8892-e2cd67a2d98d</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<EmbeddedImages>
<EmbeddedImage Name="gradient">
<ImageData>iVBORw0KGgoAAAANSUhEUgAAAFwAAAAECAIAAACMUM+VAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAgY0hSTQAAeiYAAICEAAD6AAAAgOgAAHUwAADqYAAAOpgAABdwnLpRPAAAAMBJREFUOE/t0ldOgzEUROGT/Uv03ltooffee29ZzwENgodA+FlApO/Blsa+1sglm6D0hfqLz8x34NdtTaAmXHi8cPrfNxSOq/e8n3M/SrEZW7A12rA9OrAzurAbe7A3+rAfB3AwhnAYR3AUx2IcJ7AckziF0ziDs1HBOZzHhVjEJVzGlVjFtVjHjdjELdzGHdyNPdzHAzyMIzzGkzjFMzzHC7yMK7zGG7zFu7jHB3yMJ3yOF3yNN6xCo5RGKf/6Ke8bJtRZRzfohAAAAABJRU5ErkJggg==</ImageData>
<MIMEType>image/png</MIMEType>
</EmbeddedImage>
</EmbeddedImages>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText>SELECT Products.ProductID, Products.ProductName, Products.SupplierID, Products.CategoryID, Products.QuantityPerUnit, Products.UnitPrice,
Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued, Categories.CategoryName
FROM Products INNER JOIN
Categories ON Products.CategoryID = Categories.CategoryID</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
<Field Name="CategoryName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CategoryName</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Author>Robert M. Bruckner, Microsoft</Author>
<Width>5.625in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

Tanks Robert!

I′ll try your suggestion

|||well, i have some kinda same problem, well in my pie chart, we have so many grous, so what happing , the lables which shows percentages, completely overlap each other, like if a chart is showing 20 groups percentages so they all overlap with each other, i hav data like 80%, and rest are arrround 2, 3, 5, 2, 1, 3, 4, percentages? what should i do plz give us any suggestion

Dynamically passing a Parameter Name to Custom Code

Hello,

I'm trying to create a custom code function for Reporting Services. I would like to have it user-friendly and give the user the ability to pass a report parameter name into the function (so it can be a generic function that can be used for many reports).

Is there a way to do this so inside the code I can have access to other properties of the object?

I envision something like:

Function Blah(ParameterName as String) as String

Dim MaxNum as Integer
Dim ParamValue as String

MaxNum = Reports.Parameters!ParameterName.Count - 1
ParamValue = Reports.Parameters!ParameterName.Value

etc...

Is this possible? I can't figure out how to do this. Using dynamic SQL you can do this very easily by concatenating string values together and then executing the string. Is there something similar to this in VB?

Help! Thanks

Below is an example for a custom code function that you can call in a textbox e.g. as

=Code.ShowParametersValues(Parameters!Country)

Public Function ShowParameterValues(ByVal parameter as Parameter) as String
Dim s as String
If parameter.IsMultiValue then
s = "Multivalue: "
For i as integer = 0 to parameter.Count-1
s = s + CStr(parameter.Value(i)) + " "
Next
Else
s = "Single value: " + CStr(parameter.Value)
End If
Return s
End Function

-- Robert

Dynamically Passing a Dataset to a Report in Reporting Services 2005

We are investigating the possibility of moving our reports from a Crystal Reports environment over to Reporting Services 2005. Currently we sometimes create a ADODB.Recordset in VB6 code which is passed to a Crystal Report to be displayed or printed. The Crystal Report structure has previously been created using a .ttx file and the recordset then supplies the data at runtime.

Could someone please tell how this scenario can be achieved using VB.Net 2005 and Reporting Services.

Thank you, in advance.

The equivalent of doing this in SSRS-land is to use the VS 2005 Report Viewer contorl in local mode (the report is processed at the client, not on the SSRS server), provide it with the RDL for the report (called RDLC), and "marry" a dataset to the datasource of the control. You'll find examples of same in books online and out on gotreportviewer.com

Dynamically name folders

Apologies in advance for the newbie question. I'm new to Integration Services and am trying to build my first project.

I want to dynamically create folders based on the results of a stored procedure. The resultset is limited to one column but can contain multiple rows. (It returns a list of 8-character strings representing dates.) This returned value needs to be combined with the path where the new folders should be created.

Can someone point me in the right direction? I've run the stored procedure and put the resultset into a RecordSet Destination that uses a variable of type Object. (At least, I think I've done it correctly.) But I have not been able to figure out how to access this data to actually create the folders.

Thanks for the help,

Chris

Use a foreach loop.

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx|||

Thanks!!!

That was exactly what I needed and worked like a charm.

Chris

Dynamically creatng charts

Hi,
I am new using Reporting Services and I have a need to create charts
dynamically depending on information from a parameter. I can have
anywhere from 1 to about 30 graphs that will need to be dynamically
created. Can this be done using the report builder? Is there any
documentation on the Web somewhere?
Thanks!!!Hi,
Yes can be done. But the graphs are limited with some parameters. For more
info
check this link on SQL SERVER 2005 online books. at
"Working with Chart Data Regions"
I have worked with creating different graphs, let me know what type of
graphs and the data required accordingly, can explain how can be done.
Amarnath
"etienne.bourgeois@.gmail.com" wrote:
> Hi,
> I am new using Reporting Services and I have a need to create charts
> dynamically depending on information from a parameter. I can have
> anywhere from 1 to about 30 graphs that will need to be dynamically
> created. Can this be done using the report builder? Is there any
> documentation on the Web somewhere?
> Thanks!!!
>|||Etienne,
I suggest you also check out dundas charts from report services
2005...we finished up going with these as they contain many more
charting features than the 'free' reports with report services. And I
believe the 'free' ones are also dundas charts rolled into the product
still. (they were with the first release)...
We are dynamically creating charts based on parameters returning data
from stored procedures...
Peter
www.peternolan.com

Dynamically Creating Columns

I have an .aspx page where users can select columns from a table. I want to pass the Select statement to a SQL Reporting Services report, probably as a stored procedure, and open the report with the records for the columns selected as an Excel file. My problem is that I am not sure how to dynamically create the column headings and values in a table. Any thoughts on how to achieve this? Thanks!

HI,amiejoye:

When do you want to dynamically create the column heading? In report data retrieving or other period?

|||

if you want to dynamically add headers then you can use code section in reporting service.

Else you can use drag and drop the fileds(clumn name ) that is comin from the dataset

|||

I want to create the column headings and retrieve the records when I click Submit from the .aspx page. Basically, when I click Submit, that's how I want the age to open, with the data from the Select statement I created.

|||

I think that should not be aproblem because the vb code section in the reporting service depends on the select query only

Dynamically Create Columns

Hi, i'm a newbie to reporting services. May i know if it is possible to dynamically create columns for a report?

I know that there's a way to hide the columns on the fly. However, for my case, i will only know the amount and the detail information (column name etc..) of columns while the report is running.

Thanks in advance.

I think i have posted the message in the wrong forum. I should post it in the SQL 2k forum. Sorry|||

Maybe a matrix could help...

|||

The only way to create columns on the fly is to use the matrix control. In this mode, the columns are groups in your data.

RS 2005 includes an 'execute on the fly' method on the execution web service so you could construct a dynamic report and pass it to the service to run.

|||Can you please point us to some samples of this "execute on the fly method"?

Dynamically Create Columns

Hi, i'm a newbie to reporting services. May i know if it is possible to dynamically create columns for a report?

I know that there's a way to hide the columns on the fly. However, for my case, i will only know the amount and the detail information (column name etc..) of columns while the report is running.

Thanks in advance.

I think i have posted the message in the wrong forum. I should post it in the SQL 2k forum. Sorry|||

Maybe a matrix could help...

|||

The only way to create columns on the fly is to use the matrix control. In this mode, the columns are groups in your data.

RS 2005 includes an 'execute on the fly' method on the execution web service so you could construct a dynamic report and pass it to the service to run.

|||Can you please point us to some samples of this "execute on the fly method"?

Dynamically Changing the DataSource of the Report

hi to all,

This is SidRogers,

i was newer to the this Reporting Services..

my Pbm is....

Creating the Report with or with out having any dataSource with SqlServer BI Studio.

I have some dummy fields in the Report..after I deployed into the ReportServer...

After that my Task is... Getting that ReportDefinition and Assinging the DataSource at the Runtime..in .Net frameWork 2.0...

upto My R & D.. i got one ReportDefintion.VB file.... I saw other forums. it said that. By using classes in this Vb file we can change the DataSource..if so may i know how...

this is very Urgent Work..

pleaseeeeeee urgently let me knoww..if u know any body....

thax in Advance,

Hi Sid-

You can set data source properties of a report through VB script, using the rs.exe tool, or you can alter it via the SOAP API from any C#/VB application. For more information on using script, some samples can be found here: http://msdn2.microsoft.com/en-us/library/ms160854.aspx

-JonHP

Dynamically Add Partitions to a SSAS Cube

The environment here is SSIS ETL feeding a Fact Table. The Fact Table is pulled into SSAS as a cube and reporting services are handled there. I am on the ETL side and don't pretend to know all the processing that happens with cubes, etc.

What we are trying to accomplish is to add partitions to a Cube via the ETL processing. The partitions should be incremented by Day, i.e. 20070501, 20070502, etc.

This is currently processed manually by the Reporting developer and we are looking for an automated process to reduce errors and hand work.

I have explored the following objects: Partition Processing DataFlow Destination and did not find much documentation or examples on it's use. If you have any information on this stage, please reply with such.

The other option is the Analysis Services Processing Control Flow. I understand that we can process Analysis Server objects as part of our package. Is there a way to incorporate a Partitioning Script in this object? If so, how. Again, I did not find detailed examples on the use of this object.

If you have experience with either of these, please feel free to reply. I appreciate any and all comments.

Use the "Analysis Services Execute DDL Task". This allows you to fire XMLA statements against Analysis Services.

I can't help you with what the XMLA statement needs to be I'm afraid, you'll need to go for the Analysis Services forum for that.

You may wish to check out these two guys for assistance:

Darren Gosbel - http://geekswithblogs.net/darrengosbell/Default.aspx

Chris Webb - http://cwebbbi.spaces.live.com/

-Jamie

|||

Jamie, thanks I will explore this task...our SSAS developer is checking out how to generate the XML so I will forward these links to him. Our research was leading us toward the XMLA approach but we were lacking in how to fire it off.

Thanks again!

|||

I am also looking into this and have my xmla created and all, but when firing the task i get this error:

[Analysis Services Execute DDL Task] Error: Errors in the metadata manager. Either the cube with the ID of 'xxxx' does not exist in the database with the ID of xxxx', or the user does not have permissions to access the object.

Anyone can help with this error?

|||

Hi JOER_DK,

CUBE ID is nothing but the name of the cube you want to process from an Analysis Services Database. You can generate the code for processing a cube by right clicking the cube -> Process-> This open a window for processing -> In this window, you have a button to script out the xmla for processing.

Thanks

Subhash Subramanyam