How to Create Reports?

Hi --
We have SQL Server 2000 and I just found out that it comes with Reporting
Services. How do I create reports?
Thanks
RichRead the SQL Server Help File to get started, there are a lot of good
tutorials.
"Rich Morey" <rwmorey71@.hotmail.com> wrote in message
news:uV%23a1doCIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi --
> We have SQL Server 2000 and I just found out that it comes with Reporting
> Services. How do I create reports?
> Thanks
> Rich
>

How to create locialized report?

I know the language displayed on reporting service is based on language preference on IE, so I have no porblem on that.

But, is there any method to control the report itself? Can I use a resource control thing? or anything else to control it? so that when IE is displaying Chinese big5, the reoprt header and column header can display Chinese big5, and it goes the same for English and so on

Many thx

Yes - You can change the STYLE properties for any element and set the culture field to whatever you want.

How to create large report?

Hi,

I have been tasked with trying to re-create out in-house reporting solution, based-on powerpoint and SQL Server, using Reporting Services 2005.

However when trying to create a demo report I find a number of problems:

1) I cannot make the page layout long enough or insert extra pages in order to caointain all of the test, charts and tables that we have in our reports (typically around 100 powerpoint slides)

2) I cannot find a way to set any global report styles for text or charts

3) I cannot find a way to have a flow layout instead of a grid layout - which does not allow me to insert items into the middle of a report

Any help/advice would be greatly appreciated,

Thanks,

Denham

1) Click on the Body band and set the Size property accordingly.

2) Currently, SSRS doesn't support styles or templates.

3) The List region allows you to create freeflowing reports.

|||

Hi Denham,

please forgive me if I have not understood you correctly...

1) I cannot make the page layout long enough or insert extra pages in order to caointain all of the test, charts and tables that we have in our reports (typically around 100 powerpoint slides)

This sounds like you are trying to create a 100 page report (?). If so, I would question the business value of such a report. May be better to suggest an alternative, such as using exception reports in a web page (BI style, which you can do in report designer), or cutting up the report into smaller chunks. Personally, I haven't had the need to create a report more than 30-odd pages long - which I thought was rather extravagant.

2) I cannot find a way to set any global report styles for text or charts

I don't know of any either. I tend to copy a graph/table/matrix and then change the dataset & values.

3) I cannot find a way to have a flow layout instead of a grid layout - which does not allow me to insert items into the middle of a report.

The only way I know of is to move everything on the page downwards to insert something in between. The layout can take quite a bit of getting used to, especially if you are familiar with banded report designers, such as Crystal. However, once you have been using it for a while, it becomes reasonably easy.

Hope that has been of some help to you.

NinetyNine

|||

Thanks for your reply.

1) Unfortunately our business needs to create these large reports in order to support the many different types of analyses that are used. However it may be possible to break them up into sub-reports in order for us to use Reporting Services

2) Shame about the lack of styles. Our current reporting solution is based on Powerpoint for which we can use templates.

3) Again, shame!

How to create large report?

Hi,

I have been tasked with trying to re-create out in-house reporting solution, based-on powerpoint and SQL Server, using Reporting Services 2005.

However when trying to create a demo report I find a number of problems:

1) I cannot make the page layout long enough or insert extra pages in order to caointain all of the test, charts and tables that we have in our reports (typically around 100 powerpoint slides)

2) I cannot find a way to set any global report styles for text or charts

3) I cannot find a way to have a flow layout instead of a grid layout - which does not allow me to insert items into the middle of a report

Any help/advice would be greatly appreciated,

Thanks,

Denham

1) Click on the Body band and set the Size property accordingly.

2) Currently, SSRS doesn't support styles or templates.

3) The List region allows you to create freeflowing reports.

|||

Hi Denham,

please forgive me if I have not understood you correctly...

1) I cannot make the page layout long enough or insert extra pages in order to caointain all of the test, charts and tables that we have in our reports (typically around 100 powerpoint slides)

This sounds like you are trying to create a 100 page report (?). If so, I would question the business value of such a report. May be better to suggest an alternative, such as using exception reports in a web page (BI style, which you can do in report designer), or cutting up the report into smaller chunks. Personally, I haven't had the need to create a report more than 30-odd pages long - which I thought was rather extravagant.

2) I cannot find a way to set any global report styles for text or charts

I don't know of any either. I tend to copy a graph/table/matrix and then change the dataset & values.

3) I cannot find a way to have a flow layout instead of a grid layout - which does not allow me to insert items into the middle of a report.

The only way I know of is to move everything on the page downwards to insert something in between. The layout can take quite a bit of getting used to, especially if you are familiar with banded report designers, such as Crystal. However, once you have been using it for a while, it becomes reasonably easy.

Hope that has been of some help to you.

NinetyNine

|||

Thanks for your reply.

1) Unfortunately our business needs to create these large reports in order to support the many different types of analyses that are used. However it may be possible to break them up into sub-reports in order for us to use Reporting Services

2) Shame about the lack of styles. Our current reporting solution is based on Powerpoint for which we can use templates.

3) Again, shame!

How to Create Dynamic Report Using Reporting Services

Hi all,
I was wondering is Reporting Services can be used to create dynamic
report as Crystal Report Advanced Edition do? So, we can add new object
based on user requirement through coding.
For example the scenario is (with VB6):
There's 5 checkbox that's reflect 5 database field of a table that will
be choosed by user and appear at the report.
My consideration is, even though Reporting Services can be used at
Windows environment using IE control, the report must be compiled &
built, so if we... we can say... create report runtime is it possible
to do it at Reporting Services?Hi Resant,
YOu have to deploy (publish) the report on the server in order to run it.
You can create an interface that allows the user to create on the fly base on
metadata. But the preview has to come afte a publish.
see this ASP.NET demo, it may has ideas
http://www.rdlcomponents.com/ASPExamples/default.aspx
Thanks
Jerry
"Resant" wrote:
> Hi all,
> I was wondering is Reporting Services can be used to create dynamic
> report as Crystal Report Advanced Edition do? So, we can add new object
> based on user requirement through coding.
> For example the scenario is (with VB6):
> There's 5 checkbox that's reflect 5 database field of a table that will
> be choosed by user and appear at the report.
> My consideration is, even though Reporting Services can be used at
> Windows environment using IE control, the report must be compiled &
> built, so if we... we can say... create report runtime is it possible
> to do it at Reporting Services?
>|||Actually my conception exactly the same as the interface that's shown
by ASP.NET demo, I don't mind if I must learn RDL code, but could you
share how to publish the report on the server automatically (through
coding or the others) ?
I'm interested with RDL Component, how is the component work? Is it
read RDL code and change it into ASP code?
waiting for your reply...|||If you are interested in this I strongly suggest that you look at the new
report controls in the newest beta for Widbey (Visual Studio). There are two
controls: web and winform. They can work with server or work in local mode.
The best thing to understand how RS works is to go read this very good
article here:
http://www.microsoft.com/sql/reporting/techinfo/techoverview.asp
RDL stands for Report Definition Language. It is an XML document that
defines the report in a output independent manner. HTML output is just one
of the rendering outputs (RS also does PDF, Excel, etc). The spec is here:
http://www.microsoft.com/sql/reporting/techinfo/rdlspec.asp
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Resant" <resant_v@.yahoo.com> wrote in message
news:1114566998.702015.164760@.g14g2000cwa.googlegroups.com...
> Actually my conception exactly the same as the interface that's shown
> by ASP.NET demo, I don't mind if I must learn RDL code, but could you
> share how to publish the report on the server automatically (through
> coding or the others) ?
> I'm interested with RDL Component, how is the component work? Is it
> read RDL code and change it into ASP code?
> waiting for your reply...
>|||Hi Resant,
The full source code for Publish the report is there.
And the component basically convert (read/write) the XML representation
in objects/collections, in the case the programmer does not want to deal with
the RDL specification and writting raw
XML(http://www.microsoft.com/sql/reporting/techinfo/rdlspec.asp).
The ASP.NET interface helps you to select the items to be included in the
report, but its not reading or using any metadata at all.
Hopefully it help you to start.
Thanks
Jerry
--
The first RDL reader/writer of the market.
http://www.rdlcomponents.com
"Resant" wrote:
> Actually my conception exactly the same as the interface that's shown
> by ASP.NET demo, I don't mind if I must learn RDL code, but could you
> share how to publish the report on the server automatically (through
> coding or the others) ?
> I'm interested with RDL Component, how is the component work? Is it
> read RDL code and change it into ASP code?
> waiting for your reply...
>|||Hi Bruce,
Where we can find information abou the web and winform controls?
Thanks
Jerry
"Bruce L-C [MVP]" wrote:
> If you are interested in this I strongly suggest that you look at the new
> report controls in the newest beta for Widbey (Visual Studio). There are two
> controls: web and winform. They can work with server or work in local mode.
> The best thing to understand how RS works is to go read this very good
> article here:
> http://www.microsoft.com/sql/reporting/techinfo/techoverview.asp
> RDL stands for Report Definition Language. It is an XML document that
> defines the report in a output independent manner. HTML output is just one
> of the rendering outputs (RS also does PDF, Excel, etc). The spec is here:
> http://www.microsoft.com/sql/reporting/techinfo/rdlspec.asp
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Resant" <resant_v@.yahoo.com> wrote in message
> news:1114566998.702015.164760@.g14g2000cwa.googlegroups.com...
> > Actually my conception exactly the same as the interface that's shown
> > by ASP.NET demo, I don't mind if I must learn RDL code, but could you
> > share how to publish the report on the server automatically (through
> > coding or the others) ?
> >
> > I'm interested with RDL Component, how is the component work? Is it
> > read RDL code and change it into ASP code?
> >
> > waiting for your reply...
> >
>
>|||Check this related thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=8a565186-663e-48aa-b4e2-8bad3056c40f&sloc=en-us
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerry" <Jerry@.discussions.microsoft.com> wrote in message
news:1555FEF1-AB9A-4BA1-BA38-B83C5DA7658D@.microsoft.com...
> Hi Bruce,
> Where we can find information abou the web and winform controls?
> Thanks
> Jerry
> "Bruce L-C [MVP]" wrote:
>> If you are interested in this I strongly suggest that you look at the new
>> report controls in the newest beta for Widbey (Visual Studio). There are
>> two
>> controls: web and winform. They can work with server or work in local
>> mode.
>> The best thing to understand how RS works is to go read this very good
>> article here:
>> http://www.microsoft.com/sql/reporting/techinfo/techoverview.asp
>> RDL stands for Report Definition Language. It is an XML document that
>> defines the report in a output independent manner. HTML output is just
>> one
>> of the rendering outputs (RS also does PDF, Excel, etc). The spec is
>> here:
>> http://www.microsoft.com/sql/reporting/techinfo/rdlspec.asp
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Resant" <resant_v@.yahoo.com> wrote in message
>> news:1114566998.702015.164760@.g14g2000cwa.googlegroups.com...
>> > Actually my conception exactly the same as the interface that's shown
>> > by ASP.NET demo, I don't mind if I must learn RDL code, but could you
>> > share how to publish the report on the server automatically (through
>> > coding or the others) ?
>> >
>> > I'm interested with RDL Component, how is the component work? Is it
>> > read RDL code and change it into ASP code?
>> >
>> > waiting for your reply...
>> >
>>|||I've answer of my question with :
http://weblogs.sqlteam.com/tarad/archive/2005/01/05/3944.aspx
So by struggling with RDL writing & reading + publish automatically you
can build your dynamic report.sql

How to create charts and graphs on reports?

Hi to all,

I am newbie to Reporting Services technology

I am trying to develop some reports in Reporting Services using Visual Studio Management Express.

Is there a way to create some charts and graphs inside a report?

Have I to use Report Builder?

Thanks a lot

Bye

Sure, it's quite simple actually. The trick is to get the right SQL query.

Personally, I wouldn't use report builder.

Here are the steps:

1. Create a Report Server Project from Business Intelligence Studio

2. Add a new report to the project. This can be done by opening solution explorer (View -> solution explorer). Next, right click the name of your project then Add -> New Item. Click on Report then click OK.

3. On the Data tab, you want to create a new dataset and form your SQL statement there. This can be a stored procedure or textual.

4. From the toolbox (view -> toolbox), drag and drop a chart to the grid area of the layout tab.

5. Now, you can select the type of chart and play with whatever kind of formatting you want the chart or graph to have.

6. The important thing to do next is click view -> datasets. You want to drag and drop the field(s) to the Y or X-axis as appropriate.

If you have any more questions, let us know but that should get you started.

|||

Thanks for the reply.

I will try and I will post here news about develop

thanks a lot

bye

How to create a Virtual Directory that uses instance name

Hi,
I installed Reporting Services 2005 as an instance MYMACHINE\RS2005. If I do
this, the regular setup creates a Virtual Directory called
http://MYMACHINE/ReportServer$RS2005/
But if I wanted to create a similar virtual directory (say
http://MYMACHINE/TestServer$RS2005) using Reporting Services Configuration
tool, it errors out saying the virtual directory name is invalid (I just type
in TestServer$RS2005 in the virtual directory name)
How can I accomplish this?
Any help is greatly appreciated.
Thanks.Also,
I just found documentation on MSDN that said that the tool will
automatically add the instance name, but when I use the tool, it just creates
a Virtual Directory called TestServer.
"Raghu" wrote:
> Hi,
> I installed Reporting Services 2005 as an instance MYMACHINE\RS2005. If I do
> this, the regular setup creates a Virtual Directory called
> http://MYMACHINE/ReportServer$RS2005/
> But if I wanted to create a similar virtual directory (say
> http://MYMACHINE/TestServer$RS2005) using Reporting Services Configuration
> tool, it errors out saying the virtual directory name is invalid (I just type
> in TestServer$RS2005 in the virtual directory name)
> How can I accomplish this?
> Any help is greatly appreciated.
> Thanks.

How to create a report using multiple databases

Hi, I am a beginner of Reporting Service. I am trying to create a report using multiple databses. For example, I want to create a report called RevenueByCustomer, so I need to get data from the Customer Table of CRM database, which contains customer information, and I also need to get data from Transaction table of Sales database, which contains all the revenue information. In order to get data from both database, I have created two dataset. One is Customer dataset, which get all required customer data from CRM database, and the other is Revenue dataset, which gets data from Sales database, they used seperate datasource (because each datasource only contains one database connection). Now my problem is how can I make them be displayed in one report ? It seems to be like a Master-Detail report, I need to sum up all trasactions for a particular customer and also need to display the customer name with each TotalAmount, but how can I make these two dataset can be merged together or used an extra query to do it?

Please help me, thanks a lot

This Query below is used to calculate the total amount for each customer in Revenue dataset:

SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax
FROM TransactionMaster
WHERE (Updated >= @.Start) AND (Updated <= @.End)
Group By CustomerID

the other one here is used to get customer code and name in Customer dataset:

SELECT ID, Code, Name
FROM Customer

SELECT ID, Code, Name
FROM Customer

UNION ALL

SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax
FROM TransactionMaster
WHERE (Updated >= @.Start) AND (Updated <= @.End)
Group By CustomerID

|||

I think your easiest solution would be to join the two recordset's together via the CustomerID and put it in one dataset. Then you can have Reporting Services do the grouping.

SELECT c.Code, c.Name, sum(t.TotalAmount - t.TotalTaxAmount) AS TotalExcTax, sum(t.TotalAmount) AS TotalIncTax
FROM CustomerDB.dbo.Customer c
inner join SalesDB.dbo.TransactionMaster t on c.ID = t.CustomerID
WHERE (t.Updated >= @.Start) AND (t.Updated <= @.End)

Hope this helps.

Jarret

|||

Hi,

You might be able to do that on the SQL server and return that as a view, but you can't access two separate database connections in the SQL statement in the RS dataset designer. (unless this has changed..)

If you want to create a master - detail report in BIDS, try creating two connections, two seperate datasets and then create a 'master' report on one dataset and a subreport for the detail on the other dataset. Then pass that subreport the current master record's ID field using parameters..

pl

|||

pl,

You can join from other databases as in my example. I think I mis-worded it, instead of '...join the two recordset's together via...', I should have said '...join the two table's together via...'.

Sorry for the confusion. I still think this would be the easiest solution, rather than creating sub-reports.

Jarret

|||

Hi Jarret,

I don't believe you can join two tables from different databases in SQL dataset editor in BIDS. Have you ever tried?

pl

|||

pl,

Yes, I have tried, and it works (as long as the user account has access to both databases). I have a few reports in production that link between databases.

Jarret

|||

Hi Jarret,

Really? Which version of RS are you using? I have RS 2005 (no upgrades to sp1..) and BIDS (SQL Server Business Intelligence Development Studio). Even though I create two connections to two databases in a solution using BIDS, I cannot refer to 'the other database' in the SQL view designer when I'm creating a new dataset.

Is this post correct: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177599&SiteID=1

Here is what I do:

1) Create a new Report Server Project in BIDS 2005

2) Add a datasource to the Shared Data Sources. Hook it up to database1.

3) Add another datasource to the Shared Data Sources. Hook it up to database2. Now you have two datasources in one report project.

4) Add a report.

5) Create a new dataset. (Click on the "Data" tab, hit the drop-down list and choose <new dataset>) Notice that when that dataset dialog opens up, you can only specify one datasource for it. Specify the datasource.

6) In the dataset designer, try to access the other datasource that you did not specify in the dataset's connection (ie: select * from database2.dbo.table1) . It doesn't work. You get an error message that says "Invalid object name... Error: 208"

You are saying that this works for you? Are you sure you aren't maybe doing this in query analyzer on the server and producing a view which you then use to populate a datasource in RS using only one connection?

pl

|||

pl,

I think you misunderstand what I have done (and suggested) here. I am using RS 2005 SP1 and BIDS. In my example, the code I pasted (below) was the query for 1 single dataset. Of course, if these databases are on separate servers, this wouldn't be possible without a linked server. However, Fern.Andy didn't mention anything about the DB's being on separate servers, so I assumed they weren't.

SELECT c.Code, c.Name, sum(t.TotalAmount - t.TotalTaxAmount) AS TotalExcTax, sum(t.TotalAmount) AS TotalIncTax
FROM CustomerDB.dbo.Customer c
inner join SalesDB.dbo.TransactionMaster t on c.ID = t.CustomerID
WHERE (t.Updated >= @.Start) AND (t.Updated <= @.End)

I am not saying that I can link two dataset's together from within BIDS. In short, I joined the two tables together in the query, rather than trying to get RS to join two different datasets.

1. Create a new report
2. Create the connection string to the server
3. Paste the above query into the query string

Jarret

|||

Hey Jarret,

I've got it working now. Thank you very much. I appreciate your patience.

Seems I had a spelling mistake in the database name. Once I had that fixed, I had to give access to the same user to the second database. But, it does work now. And, I only needed one connection in RS. This is going to be a very handy. Much better than using sub-reports.

Thanks again.

pl

How to create a report using multiple databases

Hi, I am a beginner of Reporting Service. I am trying to create a report using multiple databses. For example, I want to create a report called RevenueByCustomer, so I need to get data from the Customer Table of CRM database, which contains customer information, and I also need to get data from Transaction table of Sales database, which contains all the revenue information. In order to get data from both database, I have created two dataset. One is Customer dataset, which get all required customer data from CRM database, and the other is Revenue dataset, which gets data from Sales database, they used seperate datasource (because each datasource only contains one database connection). Now my problem is how can I make them be displayed in one report ? It seems to be like a Master-Detail report, I need to sum up all trasactions for a particular customer and also need to display the customer name with each TotalAmount, but how can I make these two dataset can be merged together or used an extra query to do it?

Please help me, thanks a lot

This Query below is used to calculate the total amount for each customer in Revenue dataset:

SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax
FROM TransactionMaster
WHERE (Updated >= @.Start) AND (Updated <= @.End)
Group By CustomerID

the other one here is used to get customer code and name in Customer dataset:

SELECT ID, Code, Name
FROM Customer

SELECT ID, Code, Name
FROM Customer

UNION ALL

SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax
FROM TransactionMaster
WHERE (Updated >= @.Start) AND (Updated <= @.End)
Group By CustomerID

|||

I think your easiest solution would be to join the two recordset's together via the CustomerID and put it in one dataset. Then you can have Reporting Services do the grouping.

SELECT c.Code, c.Name, sum(t.TotalAmount - t.TotalTaxAmount) AS TotalExcTax, sum(t.TotalAmount) AS TotalIncTax
FROM CustomerDB.dbo.Customer c
inner join SalesDB.dbo.TransactionMaster t on c.ID = t.CustomerID
WHERE (t.Updated >= @.Start) AND (t.Updated <= @.End)

Hope this helps.

Jarret

|||

Hi,

You might be able to do that on the SQL server and return that as a view, but you can't access two separate database connections in the SQL statement in the RS dataset designer. (unless this has changed..)

If you want to create a master - detail report in BIDS, try creating two connections, two seperate datasets and then create a 'master' report on one dataset and a subreport for the detail on the other dataset. Then pass that subreport the current master record's ID field using parameters..

pl

|||

pl,

You can join from other databases as in my example. I think I mis-worded it, instead of '...join the two recordset's together via...', I should have said '...join the two table's together via...'.

Sorry for the confusion. I still think this would be the easiest solution, rather than creating sub-reports.

Jarret

|||

Hi Jarret,

I don't believe you can join two tables from different databases in SQL dataset editor in BIDS. Have you ever tried?

pl

|||

pl,

Yes, I have tried, and it works (as long as the user account has access to both databases). I have a few reports in production that link between databases.

Jarret

|||

Hi Jarret,

Really? Which version of RS are you using? I have RS 2005 (no upgrades to sp1..) and BIDS (SQL Server Business Intelligence Development Studio). Even though I create two connections to two databases in a solution using BIDS, I cannot refer to 'the other database' in the SQL view designer when I'm creating a new dataset.

Is this post correct: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177599&SiteID=1

Here is what I do:

1) Create a new Report Server Project in BIDS 2005

2) Add a datasource to the Shared Data Sources. Hook it up to database1.

3) Add another datasource to the Shared Data Sources. Hook it up to database2. Now you have two datasources in one report project.

4) Add a report.

5) Create a new dataset. (Click on the "Data" tab, hit the drop-down list and choose <new dataset>) Notice that when that dataset dialog opens up, you can only specify one datasource for it. Specify the datasource.

6) In the dataset designer, try to access the other datasource that you did not specify in the dataset's connection (ie: select * from database2.dbo.table1) . It doesn't work. You get an error message that says "Invalid object name... Error: 208"

You are saying that this works for you? Are you sure you aren't maybe doing this in query analyzer on the server and producing a view which you then use to populate a datasource in RS using only one connection?

pl

|||

pl,

I think you misunderstand what I have done (and suggested) here. I am using RS 2005 SP1 and BIDS. In my example, the code I pasted (below) was the query for 1 single dataset. Of course, if these databases are on separate servers, this wouldn't be possible without a linked server. However, Fern.Andy didn't mention anything about the DB's being on separate servers, so I assumed they weren't.

SELECT c.Code, c.Name, sum(t.TotalAmount - t.TotalTaxAmount) AS TotalExcTax, sum(t.TotalAmount) AS TotalIncTax
FROM CustomerDB.dbo.Customer c
inner join SalesDB.dbo.TransactionMaster t on c.ID = t.CustomerID
WHERE (t.Updated >= @.Start) AND (t.Updated <= @.End)

I am not saying that I can link two dataset's together from within BIDS. In short, I joined the two tables together in the query, rather than trying to get RS to join two different datasets.

1. Create a new report
2. Create the connection string to the server
3. Paste the above query into the query string

Jarret

|||

Hey Jarret,

I've got it working now. Thank you very much. I appreciate your patience.

Seems I had a spelling mistake in the database name. Once I had that fixed, I had to give access to the same user to the second database. But, it does work now. And, I only needed one connection in RS. This is going to be a very handy. Much better than using sub-reports.

Thanks again.

pl

How to Create a Mailing Label Report

Hi,

I want to create a mailing label report, with Avery formats. Please guide me how can create this using sql server 2005 reporting services?

Regards,

Prashant Mulay

Did you sort this out?

I also want to produce a label style report where I have 3 labels across and 8 labels down..

|||

This might help:

http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

How to create a dropdown list on report?

I would like to create a first dropdown list on my report. How can I do that?
I am using followings,
Reporting service (SQL Server 2005 June CTP release)
Steps which I am following to create a report,
1. Creating a model using BIDS and publishing it to Report Server
2. By using Report Builder, I am creating new report and storing it on
Report server. In one of my report, I would like to display dropdown list for
Project with "Project Name". At present, I am using filter criteria for
displaying parameter on each report but it is only displaying text field.
I am still searching related post here and expecting help...
Thnks
--
There is a light on the other side, Keep walking.Try these steps. Step number 3 is the key item here.
1. Open the Filter dialog
2. Add a entity or field to the filter layout area
3. Click on the entity or field name. This will open a context menu. Select
the 'Prompt' option
4. Click on the filter condition. This will open a context menu. Select the
'In a List' option
5. Click the filter value dropdown. You can pre select values to display or
leave all items unchecked.
6. Run the report. You should see dropdown that contains a list of parameter
values.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nilesh Trivedi" <NileshTrivedi@.discussions.microsoft.com> wrote in message
news:4D0A8262-1594-4A5B-B06E-2A51401307AF@.microsoft.com...
>I would like to create a first dropdown list on my report. How can I do
>that?
> I am using followings,
> Reporting service (SQL Server 2005 June CTP release)
> Steps which I am following to create a report,
> 1. Creating a model using BIDS and publishing it to Report Server
> 2. By using Report Builder, I am creating new report and storing it on
> Report server. In one of my report, I would like to display dropdown list
> for
> Project with "Project Name". At present, I am using filter criteria for
> displaying parameter on each report but it is only displaying text field.
> I am still searching related post here and expecting help...
> Thnks
> --
> There is a light on the other side, Keep walking.|||Yes, I achieved so far. But my question is still open.
Consider following scenario,
Report designer - will create a report with selected dropdown items using
filter criteria <Fieldname> <in a list> <Item selected>. This field
<fieldname> is checked as prompt which will display parameter in a report
while running a report.
N.B.: As you are saying in your previous reply.
Report user - will run this report and will see only preselected <Item
Selected> by the Report Designer.
My query is to make more flexible to Report user so that Report user can
select any dropdown items. So on Report, Report user will have dropdown list
instead of simple textbox.
--
There is a light on the other side, Keep walking.
"Bruce Johnson [MSFT]" wrote:
> Try these steps. Step number 3 is the key item here.
> 1. Open the Filter dialog
> 2. Add a entity or field to the filter layout area
> 3. Click on the entity or field name. This will open a context menu. Select
> the 'Prompt' option
> 4. Click on the filter condition. This will open a context menu. Select the
> 'In a List' option
> 5. Click the filter value dropdown. You can pre select values to display or
> leave all items unchecked.
> 6. Run the report. You should see dropdown that contains a list of parameter
> values.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Nilesh Trivedi" <NileshTrivedi@.discussions.microsoft.com> wrote in message
> news:4D0A8262-1594-4A5B-B06E-2A51401307AF@.microsoft.com...
> >I would like to create a first dropdown list on my report. How can I do
> >that?
> > I am using followings,
> > Reporting service (SQL Server 2005 June CTP release)
> > Steps which I am following to create a report,
> > 1. Creating a model using BIDS and publishing it to Report Server
> > 2. By using Report Builder, I am creating new report and storing it on
> > Report server. In one of my report, I would like to display dropdown list
> > for
> > Project with "Project Name". At present, I am using filter criteria for
> > displaying parameter on each report but it is only displaying text field.
> >
> > I am still searching related post here and expecting help...
> > Thnks
> >
> > --
> > There is a light on the other side, Keep walking.
>
>|||I achieved my query by doing followings,
1. Create a basic report using Report Builder
2. Save Report on Report Server
3. Create a new project with Report Designer
4. Go to the Report Manager and Edit Report and save RDL file to Report
Designer project path
5. Add existing report to project created in step 3.
6. Apply features like Show/hide columns/Drilldown/Header/Footer/Company
Logo/Report properties/Change report parameters/ passing parameters to sub
report/Change column headers etc.
7. Save report (PUBLISH THIS REPORT TO REPORT SERVER WILL NOT WORK AND THROW
RUNTIME ERROR)
8. Go to Report Manager and upload and overwrite report file
9. Set report parameters in report manager (If required)
10. Run report with report server (if any initial parameter, apply in IE
address bar)
This is the way i am using RS 2005 (June CTP release). I am not sure whether
this is a right way of publishing report.
There are few advantages and disadvantages by using above way to develop
report
Advantages
- Utilising most of the report features available in Report designer
- Primary goal achieved by utilising Report model concept to develop report.
(BUT STILL EXPECTING MORE FEATURES AVAILABLE IN REPORT BUILDER)
Disadvantages
- Lot of maintainace to handle reports due to once upload this report can
not be edited in Report builder. Report builder capability is minimal and may
not be used as end user tool because final report is again developed in
report designer project which is a task of development team
- Adding a new column/change a layout etc. in existing report requires to
create report again i.e. STEP-1 as mentioned above
--
There is a light on the other side, Keep walking.
"Nilesh Trivedi" wrote:
> I would like to create a first dropdown list on my report. How can I do that?
> I am using followings,
> Reporting service (SQL Server 2005 June CTP release)
> Steps which I am following to create a report,
> 1. Creating a model using BIDS and publishing it to Report Server
> 2. By using Report Builder, I am creating new report and storing it on
> Report server. In one of my report, I would like to display dropdown list for
> Project with "Project Name". At present, I am using filter criteria for
> displaying parameter on each report but it is only displaying text field.
> I am still searching related post here and expecting help...
> Thnks
> --
> There is a light on the other side, Keep walking.