Report Distribution based on SQL Query in RDL

I need to create multiple reports with each report being sent to the specified user. For example, 3 Managers should each get their own report. A Stored Procedure receives a ManagerID parameter, so I need 3 instances of the report. I also need each report to be sent to its associated Manager:

Mgr ID Manager Name 1 John Q Manager 2 Jane Q Manager 3 Jack O Lantern

I set up a rss file to run the report and can run it multiple times, passing in a different ID value each time, but it will not be data driven because I must hard-code the parameters in the ReportParmaters object of the report for each execution.

Is there a way, either by designing the report in VS2005 or on the Reporting Services server, to accomplish what I'm intending to do?

Did you already have a look on the datadriven subscription option in Reporting Services ? That would exactly fit your needs.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Report Designer crashes when accessing Access DB via ODBC

Hi:
I am trying to build a report off of a stand alone MS Access 2000 database. I can create the Data Source using the "Driver do Microsoft Access" driver, version 4.00.6019.00.
When I go to create the query to create the Dataset, I right click to add a table, I can see the table list and check the table I want to use. It is at this point the Visual Studio crashes, closes and send off the error report to Microsoft.
What's up? Why is this happening? I can write reports to my heart's content using an IBM ODBC driver against our DB2 database on an AS/400. One would think that writing reports using all MS products this should go very smooth.
Thanks in advance.
Bruce.Did you try the "Microsoft Jet 4.0 OLE DB Provider" which is designed to
work with Access?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:EE8C1C06-2779-4069-9004-0E6DF55969CE@.microsoft.com...
> Hi:
> I am trying to build a report off of a stand alone MS Access 2000
database. I can create the Data Source using the "Driver do Microsoft
Access" driver, version 4.00.6019.00.
> When I go to create the query to create the Dataset, I right click to add
a table, I can see the table list and check the table I want to use. It is
at this point the Visual Studio crashes, closes and send off the error
report to Microsoft.
> What's up? Why is this happening? I can write reports to my heart's
content using an IBM ODBC driver against our DB2 database on an AS/400. One
would think that writing reports using all MS products this should go very
smooth.
> Thanks in advance.
> Bruce.|||Hi Robert:
Thanks. That did the trick.
I had been using the ODBC connection that caused VS to crash for the last year with Crystal Reports with no issues. Curious...
Thanks again.
Bruce.
"Robert Bruckner [MSFT]" wrote:
> Did you try the "Microsoft Jet 4.0 OLE DB Provider" which is designed to
> work with Access?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
> in message news:EE8C1C06-2779-4069-9004-0E6DF55969CE@.microsoft.com...
> > Hi:
> >
> > I am trying to build a report off of a stand alone MS Access 2000
> database. I can create the Data Source using the "Driver do Microsoft
> Access" driver, version 4.00.6019.00.
> >
> > When I go to create the query to create the Dataset, I right click to add
> a table, I can see the table list and check the table I want to use. It is
> at this point the Visual Studio crashes, closes and send off the error
> report to Microsoft.
> >
> > What's up? Why is this happening? I can write reports to my heart's
> content using an IBM ODBC driver against our DB2 database on an AS/400. One
> would think that writing reports using all MS products this should go very
> smooth.
> >
> > Thanks in advance.
> >
> > Bruce.
>
>

Report Designer best practice

Hi
When creating a best practice enterprise reporting solution, would you
normally create report models first, use these as datasources in Report
Designer, andd create datasets on top of these? Or would you create datasets
using SQL queries directly from Report Designer?
What is the preferred/best practice way of creating the datasets ?
Kind Regards,
Torkild HagenOn Mar 22, 2:15 am, thagen <tha...@.discussions.microsoft.com> wrote:
> Hi
> When creating a best practice enterprise reporting solution, would you
> normally create report models first, use these as datasources in Report
> Designer, andd create datasets on top of these? Or would you create datasets
> using SQL queries directly from Report Designer?
> What is the preferred/best practice way of creating the datasets ?
> Kind Regards,
> Torkild Hagen
My preference is to create stored procedures first and then create
datasets based on those stored procedures in the Report Designer, etc;
since, stored procedures generally have better performance. Just my
thoughts. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||You can use report model as Datasource but this is meant for RB, for normal
reports, it is advisable to create stored proc and then create dataset. For
queries returning very small no of records, direct query will be good. As a
best Practise use shared datasource as far as possible, so that later if you
want to change any thing related to data source can be changed at one place
instead of many places.
Amarnath, MCTS
"thagen" wrote:
> Hi
> When creating a best practice enterprise reporting solution, would you
> normally create report models first, use these as datasources in Report
> Designer, andd create datasets on top of these? Or would you create datasets
> using SQL queries directly from Report Designer?
> What is the preferred/best practice way of creating the datasets ?
> Kind Regards,
> Torkild Hagen

report designer : BIDS seems to hang after querry

hi,

I'm creating my first report.
I do "new project" > "report server project".
Thereafter I create my shared datasource.
Thereafter I try to add a new report.
It ask to enter my querry.

I've built up my querry previously with the querry analyser in SSMS, and there it runs quite fast.

But when i copy-paste the querry in the reporting designer, and i choose to preview it, it takes more then 20 minutes.

however, my querry it quite big, but i've written querries ten times larger in my life.
(it's about 50lines: 10 querries, union-ed together and in each one i have 4 innerjoins.... all tables are even almost empty, so it's not that indexes are missing or whatever)...

is it known that in the report designer querries are much more slower?
one of the ten querry's that are union-ed together, takes 20seconds, (while in SSMS it takes nearly a second).

if i add a union to it, to add another set of data, it seems to hang already....

is it maybe bug?

can anyone try it also to create a report with a querry with 'union' in it?

the querry looks like this:

select c1,c2,c3,c4,c5
from t1
inner join t2 on t1.cX = t2.cX
inner join t3 on t2.cX = t3.cX
inner join t4 on t3.cX = t4.cX
inner join t5 on t4.cX = t5.cX
where t1.cY = 'conditionA'

union

select c1,c2,c3,c4,c5
from t1
inner join t2 on t1.cX = t2.cX
inner join t3 on t2.cX = t3.cX
inner join t4 on t3.cX = t4.cX
inner join t5 on t4.cX = t5.cX
where t1.cY = 'conditionB'|||it is solved, but i don't understand it....
in fact the querry above was extended with some other stuff:

select c1,c2,c3,c4,c5, 1 ext1, '' ext2, '' ext3
from t1
inner join t2 on t1.cX = t2.cX
inner join t3 on t2.cX = t3.cX
inner join t4 on t3.cX = t4.cX
inner join t5 on t4.cX = t5.cX
where t1.cY = 'conditionA'

union

select c1,c2,c3,c4,c5, '' ext1, 1 ext2, '' ext3
from t1
inner join t2 on t1.cX = t2.cX
inner join t3 on t2.cX = t3.cX
inner join t4 on t3.cX = t4.cX
inner join t5 on t4.cX = t5.cX
where t1.cY = 'conditionB'

now, when i replace the empty strings ('') in the select-parts with NULL then it goes much faster !!!!

can someone explain this performance issue?
(it is also very strange that i have this problem in BIDS and not in SSMS)
|||can someone try this out also?

and eventually give an explanation, if any?

Report Designer

Hello all.:-)
Does anyone know of a Report Designer program that I can download? Free
would be good.:-) I just need to create and edit reports and really don't
want to go the full MSDN route.
Thanks much,
JohnHi John,
The only thing I eared about is an open source projet on Code Projet.
http://codeproject.com/csharp/RdlProject.asp
I didn't test it and i can't assume that what you're looking for.
Christophe MIGNOT
ApolloSSC
"John Holt" wrote:
> Hello all.:-)
> Does anyone know of a Report Designer program that I can download? Free
> would be good.:-) I just need to create and edit reports and really don't
> want to go the full MSDN route.
> Thanks much,
> John
>
>|||John,
You can get the full designer in VB.Net (about £60/$120), you don't
need Visual Studio.
If you can wait for SQL2005 or get on the Beta, it comes with BI Studio
so you don't need to by anything extra (except having a licence SQL2005
server) on top of that the Report Builder has just been released onto
Beta.
John Holt wrote:
> Hello all.:-)
> Does anyone know of a Report Designer program that I can download?
> Free would be good.:-) I just need to create and edit reports and
> really don't want to go the full MSDN route.
> Thanks much,
> John

Report design scenario

Hey guys,

I want to create a report called "Report X", which has four reports under it. Assume the following scenario.

1. Report X

1.1. Bar chart report

1.2. Pie chart report

1.3. Matrix report

1.4. Pie chart report

In addition to this, there are about four parameters that should be available for all reports. So my question is: what approach should I follow to implement/design a report for the above requirement.

Thank you for your cooperation in advance.

Sincerely,

Amde

The question is discussed/answered in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=374405&SiteID=1

Report deployment without VS

Hi!
I created several reports and I want to give them to several persons for the installation on

their SQL Server 2005. Is it possible to create something like an

installation package (similar to the deployment manifest in SSIS)?
Thanks for any help!

Check out the PublishSampleReports.rss script in the C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Script Samples folder.

You can use it with the rs.exe tool to automatically publish an RDL definition to a server. To use it, your user will have to type a command, however.

If you *totally* want to automate deployment of reports you could do so by creating an MSI which uses a custom action: That custom action would essentially call the same commands as in the script above...It seems like a lot of work to me, though.

|||You can use Reporting Services Scripter to do this. Simply select the reports + datasource you want to distribute (and probably the folder too) and generate the scripts. You can then supply the generated scripts/rdl + batch file to them and all they have to do is change the servername and run the batchfile.|||

...and Jasper's reply reminds me that you can also now script this stuff right from SQL Managment Studio just like you could script tables, views, etc. back in 2000.

Launch SSMS, connect to your Report Server, then right-click the object you want to distribute and click Script.

report creator

Hello everyone,

I have a report viewer on my webpage. but the users would like to be able to create there own reports. at the launch party I saw this was possible with a wizard. But I don't know how to make use of this.

Is this wizard a part of a sql server 2005 client perhaps ?
Or is it possible to use it together with Mysql...

I hope you can help,

thx

Ok... I figured it out and already been testing a bit with reporting services.

It's the report builder I was looking for.

It's a nice feature...

Report Cover?

Is it possible to create a cover sheet for my report in Reporting Services? If so, how would I go about doing that?

Thanks for your help!!

Uh, there's no such cover sheet tool if that's what you're asking for. You could add a table or matrix before the rest of the report and check page break after table (or matrix). Then just add the content to the table that you would want to see on the "cover page".

report by multiple parameter with OR relationship

I am trying to create a report with three parameters (date, license number and application number). I want report by License Number or Application number in the date that I put in. I trying following in Record Selection:
1.
{Command.LICENSE_NO} = {?License Number} or
{Command.LICENSE_ID} = {?Application Number}

2.
if isnull({?License Number}) = false then
{Command.LICENSE_NO} = {?License Number};
if isnull({?Application Number})=false then
{Command.LICENSE_ID}={?Application Number};

** license_id is the application number(number). license number(string)

but both of them do not work. somehow I can not leave Application number empty.

Please Help!!!As Application Number is a number, Crystal wants you to specify one.
If you default it to 0 you could have a selection formula like

({?Application Number} = 0 and {Command.LICENSE_NO} = {?License Number})
or
({?Application Number} <> 0 and {Command.LICENSE_ID} = {?Application Number})

which means that if you supply a non-zero application number it will be used in preference to any licence id you provide.|||As Application Number is a number, Crystal wants you to specify one.
If you default it to 0 you could have a selection formula like

({?Application Number} = 0 and {Command.LICENSE_NO} = {?License Number})
or
({?Application Number} <> 0 and {Command.LICENSE_ID} = {?Application Number})

which means that if you supply a non-zero application number it will be used in preference to any licence id you provide.

I have tried using 0 as default, but for some reason. I still can not leave application number empty. I used exactly same as your code

({?Application Number} = 0 and {Command.LICENSE_NO} = {?License Number})
or
({?Application Number} <> 0 and {Command.LICENSE_ID} = {?Application Number})

but search will not start if I leave application parameter empty.

any thought?

Thanks for your help|||As Application Number is a number, Crystal wants you to specify one.
If you default it to 0 you could have a selection formula like

({?Application Number} = 0 and {Command.LICENSE_NO} = {?License Number})
or
({?Application Number} <> 0 and {Command.LICENSE_ID} = {?Application Number})

which means that if you supply a non-zero application number it will be used in preference to any licence id you provide.

Got it working with your code!!!

Thanks your so much!!

Report Builder: how to create a static report?

hi,
I have created a report based on a report builder model using Visual Studio.
This report will replace the default drill report.
so after I have published my report in report server, I'll modify my model
(model properties) to setup the drill through report of my customer entity.
When I save the new config I receive this error:
rsInvalidModelDrillthroughReport
/Models/DrillReports/Report1 is not a valid drill report.
so the question is, how to create a valid drill report'
there is some particular parameters to setup?
the help don't describe this.
any sample anywhere?
thanks.
Jerome.ok
I have missed some properties.
Now all works fine!!!!
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:ezZS2LuFGHA.140@.TK2MSFTNGP12.phx.gbl...
> hi,
> I have created a report based on a report builder model using Visual
> Studio.
> This report will replace the default drill report.
> so after I have published my report in report server, I'll modify my model
> (model properties) to setup the drill through report of my customer
> entity.
> When I save the new config I receive this error:
> rsInvalidModelDrillthroughReport
> /Models/DrillReports/Report1 is not a valid drill report.
> so the question is, how to create a valid drill report'
> there is some particular parameters to setup?
> the help don't describe this.
> any sample anywhere?
> thanks.
> Jerome.
>

Report Builder, Aggregation Scope and Running Values

Dear Anyone,

I need to create a measure/column that is percent of column (or row, not sure). In Reporting services, we were able to do this because aggregations there had scope.

=Sum(Fields!Open_Demand.Value)/Sum(Fields!Open_Demand.Value,"GSD")

I can't seem to do this in RB because the aggregation there does not seem to have a scope. Nor does it have the function running value.

Does anyone have an idea on how to do this on Report Builder?
Thanks,
Joseph

Report Builder does not expose the scope parameter for aggregation. You can often get the same results, however, by including a separate field that retrieves the total for the set you want.

For example, you can create a (hypothetical) report like this:

Category | Product Name | Total Sales | % of Category

...if Category is an entity in your report model. If it is a lookup entity, you will need to turn on View->Advanced Mode in RB to see it.

Here's how:

Select Product Entity
Add Product Name field to report
Add Category field to left of Product Name
Add Sales->Total Sales to report TWICE
Right-click on the value cell of the second Total Sales field, choose Edit Formula
Type "/" after the Total Sales field in the formula textbox
Add Product->Category->Products->Sales->Total Sales to the formula
- this is the total sales for all products in the same category as this product
Click OK
Change the format on the second field from $ to %
Run the report

This will be notably slower than using RDL scoping, but it should work.

PS: Due to a known issue in RTM, the subtotals you display for the % field above will be incorrect. This will be fixed in SP1. Meanwhile, if you choose "Server Totals" in the Report->Report Properties dialog, you will get the correct subtotals as well.

|||

Hi Bob,

We've tried what you suggested but we seem to be getting a 1 result. We've enabled server totals and the answer still is the same. We are currently using the RTM build.

Thanks,

Joseph

Report Builder, Aggregation Scope and Running Values

Dear Anyone,

I need to create a measure/column that is percent of column (or row, not sure). In Reporting services, we were able to do this because aggregations there had scope.

=Sum(Fields!Open_Demand.Value)/Sum(Fields!Open_Demand.Value,"GSD")

I can't seem to do this in RB because the aggregation there does not seem to have a scope. Nor does it have the function running value.

Does anyone have an idea on how to do this on Report Builder?
Thanks,
Joseph

Report Builder does not expose the scope parameter for aggregation. You can often get the same results, however, by including a separate field that retrieves the total for the set you want.

For example, you can create a (hypothetical) report like this:

Category | Product Name | Total Sales | % of Category

...if Category is an entity in your report model. If it is a lookup entity, you will need to turn on View->Advanced Mode in RB to see it.

Here's how:

Select Product Entity
Add Product Name field to report
Add Category field to left of Product Name
Add Sales->Total Sales to report TWICE
Right-click on the value cell of the second Total Sales field, choose Edit Formula
Type "/" after the Total Sales field in the formula textbox
Add Product->Category->Products->Sales->Total Sales to the formula
- this is the total sales for all products in the same category as this product
Click OK
Change the format on the second field from $ to %
Run the report

This will be notably slower than using RDL scoping, but it should work.

PS: Due to a known issue in RTM, the subtotals you display for the % field above will be incorrect. This will be fixed in SP1. Meanwhile, if you choose "Server Totals" in the Report->Report Properties dialog, you will get the correct subtotals as well.

|||

Hi Bob,

We've tried what you suggested but we seem to be getting a 1 result. We've enabled server totals and the answer still is the same. We are currently using the RTM build.

Thanks,

Joseph

Report Builder Query

Hi Experts,

I have a report model on top of a cube. I have a calculated metrics called Policy Count and different amounts in cube.

When i try to create report in report builder with Policy count, amounts and different dimensional attributes it doesnt work.

The problem is report builder allows me to select either Policy count in the report or the amount field.

Have anyone faced this problem.

The same report works in cube browser.

Regards'

Neeraj

Make sure your calculated and non-calculated measures are housed in the same measure group folder. To move the calcualted measure to a measure group's folder, go to the Calculations tab in the Cube Designer. There is a Properties button in the toolbar which will allow you to choose the calculated member and which measure group to associate it with. (Don't set the display folder.) Deploy your cube and rebuild your Report Model off the DSV.

B.

Report Builder Query

Hi Experts,

I have a report model on top of a cube. I have a calculated metrics called Policy Count and different amounts in cube.

When i try to create report in report builder with Policy count, amounts and different dimensional attributes it doesnt work.

The problem is report builder allows me to select either Policy count in the report or the amount field.

Have anyone faced this problem.

The same report works in cube browser.

Regards'

Neeraj

Make sure your calculated and non-calculated measures are housed in the same measure group folder. To move the calcualted measure to a measure group's folder, go to the Calculations tab in the Cube Designer. There is a Properties button in the toolbar which will allow you to choose the calculated member and which measure group to associate it with. (Don't set the display folder.) Deploy your cube and rebuild your Report Model off the DSV.

B.

Report builder problem in SSRS 2005

Hi Guys,

I had a problem which bothered me for a while, My SSRS 2005 DB is migrated from 2000, knowing that i need to manually create "Report Builder" role in item level security in report services, I've created it and gaves the tasks rights as MSDN document suggested. Now the problem is that report builder can't open any report services root thus can't open any model via webpage, however it can do so in server itself.

I think it's a security issue, anyone has got a solution for this one ?

Best Regards,

-Chris

HI,Chris:

WHAT do you mean 'can't open '? Do you have some error messages when you try to open a model via pages?

Report Builder page header

SSRS 2005. When I create a report in Report Builder from a model, I can add a
title to the report, but it doesn't repeat on subsequent pages. How can I get
it (or something) to repeat at the top of every page?
Thanks
VernI have the exact same question. If anyone could point me in the direction of
the answer in this newsgroup it would be greatly appreciated.
"Vern Rabe" wrote:
> SSRS 2005. When I create a report in Report Builder from a model, I can add a
> title to the report, but it doesn't repeat on subsequent pages. How can I get
> it (or something) to repeat at the top of every page?
> Thanks
> Vern

report builder not displaying some rows

Hi friends
am using report builder to create a report ,the report not displaying some rows.
i've 2 tables . Patients and transactions (1- many)

and assume that i've following records
patient
--
1 , sriram , albert street,nz
2 , david , victoria street ,aus

transactions (flds are patientid,charge,transaction date)

1 , $100, diabetes problem ,10/10/05
1, $100 , diabetes problem , 19/01/06
2, $150,someother problem

if i create a report with following fields

name , address (from patient table)

charge , problem (from transactions table) i get following results

sriram , albert street,nz,$100
david , victoria street ,aus , $150

as

u can see it ignored the 2nd record for patient "sriram" ?i used sql

profiler to get query report builder creating and if i run it in QA i

get all results am expecting but report builder thinks ignores 2rd

record as it thinks its duplicate.

i know how to fix this by

just adding transaction date field but the abv report is very common

one our users create ,is there any way report builder displays all rows ?
Thanks

it is still an issue for me !! no one has faced similar situation ?|||

Sorry: I have not used Report Builder (we're on SQL 2000 and Report Designer), but it sounds like it is just returning DISTINCT recordset.

There may be an attribute on the table? Or the query uses SELECT DISTINCT?

Perry

|||Thanks for the post Perry
The query RB creates is fine as it returns correct results if i run it QA.
but looks like RB doing a distinct after gettting the data!

report builder not displaying some rows

Hi friends
am using report builder to create a report ,the report not displaying some rows.
i've 2 tables . Patients and transactions (1- many)

and assume that i've following records
patient
--
1 , sriram , albert street,nz
2 , david , victoria street ,aus

transactions (flds are patientid,charge,transaction date)

1 , $100, diabetes problem ,10/10/05
1, $100 , diabetes problem , 19/01/06
2, $150,someother problem

if i create a report with following fields
name , address (from patient table)
charge , problem (from transactions table) i get following results

sriram , albert street,nz,$100
david , victoria street ,aus , $150

as u can see it ignored the 2nd record for patient "sriram" ?i used sql profiler to get query report builder creating and if i run it in QA i get all results am expecting but report builder thinks ignores 2rd record as it thinks its duplicate.

i know how to fix this by just adding transaction date field but the abv report is very common one our users create ,is there any way report builder displays all rows ?
Thanks
it is still an issue for me !! no one has faced similar situation ?|||

Sorry: I have not used Report Builder (we're on SQL 2000 and Report Designer), but it sounds like it is just returning DISTINCT recordset.

There may be an attribute on the table? Or the query uses SELECT DISTINCT?

Perry

|||Thanks for the post Perry
The query RB creates is fine as it returns correct results if i run it QA.
but looks like RB doing a distinct after gettting the data!

Report Builder logon failed

I used an sql server logon to create the view and model in designer, I deployed the source and model from desigen, when I go to report builder I get this message:

Logon failed.

Logon failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E) My server and web service are on the same server and I set up my server security to use mixed mode if that makes a difference.to answer my own question, even though it said it set up the rs service account correctly, it set up a new one with appropriate security and it now works, I just deleted the old id and use the new one.|||

hi there!

i encounter the same error... how did u find the id? i am using the domain account.. how can i make the necessary changes?

if i have to run my report in the client workstation, i got this error msg:

Logon failed.

For more information about this error navigate to the report server on the local server machine, or enable remote errors|||

I'm encountering the same error and cannot seem to get the issue resolved. I setup a new report server on my local machine and deployed a model to it and it works fine.

However, on my 2003 server I get the message

Logon failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E)
-
Logon failed.

Can you please explain how exactly to rectify this error?

Thanks

|||

Go to management studio, create a LOGIN give it access to the msdb, reportsever database and reportserver temp database. See

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSinstall/htm/gs_installingrs_v1_75k2.asp

|||Goto Reporting Services Configuration (in Start Menu->Programs->Sql Server->Config Tools. Click on Execution Account Uncheck "Specify an execution account"