Displaying various instances of the same report in one report

Here's my problem. I have a report that displays information for one
office. The users are now asking for a master report where they can
display all of the offices in their group. I'm thinking I can do this
with a sub report but I don't know enough about subreports to set it
the passing of multiple report parameters and the sql server book's
how to is a piece of crap.
Where else can I find some examples of how to set this up?
Is there a better way to do this?
Thanks in advance for the help and if you need something cleared up let
me know.
MathiasHi, Mathias
from what I understand, it seems like you are gonna have the following
scenario:
create a report that will query for all offices, and then group by office
group categoriy.
If so, you can create you query (sproc or adhoc) to take in the office group
identifier/name as a parameter, and filter with a Where clause in the query.
The report rdl will have a parameter for the office group id/name which you
pass into that query, and will retrieve a list of all offices for that
particular group.
On the item that displays, say, the name of the office, you can go to the
textbox properties window and setup a hyperlink to another report (your
existing one), passing the appropriate office id/name, which your current
office report will use
to retrieve detailed information on.
Another way to go about this: in you "master report", drop a sub report
element, and in the properties, set the report rdl of the original report,
and for parameters, pass in the field from the master report containing the
office id into the sub-report (your current report).
so you will have the following layout:
user enters report parameter Office Group: <value>;
query executes filtered to that office group, and returns a list of office
name/ids;
rdl renders the list of office names returned by the query in a table layout;
within table, the detail row will contain a cell with a subreport pointing
to your original "office info" rdl, and passing the office id field to the
office id parameter of the subreport:
<begin table>
<begindetailrow>
pass Fields!officeID.value =>subreport (current report)
parameter Parameters!officeid.value
<enddetailrow>
<endtable>
Note, you could modify your original query so that your grouping and logic
is done on the query side in one stored procedure, and avoid having to deal
w/ subreports, by joining the appropriate tables and building your result set
in the query w/ all of the office details there. This reduces some of the
overhead on the report server having to render subreports.
hope this helps you out.
--
Regards,
Thiago Silva
"Mathias" wrote:
> Here's my problem. I have a report that displays information for one
> office. The users are now asking for a master report where they can
> display all of the offices in their group. I'm thinking I can do this
> with a sub report but I don't know enough about subreports to set it
> the passing of multiple report parameters and the sql server book's
> how to is a piece of crap.
> Where else can I find some examples of how to set this up?
> Is there a better way to do this?
> Thanks in advance for the help and if you need something cleared up let
> me know.
> Mathias
>|||Thiago Silva
Thank you very much for taking the time to respond. I think i'm going
to have to go with the second option and use the subreport. my users
want to be able to see the various reports all at one time. I set up
the query to bring back the office id's in their respective groups
however when I pass that id to the subreport only the first offices'
report is generated.
for example there are 10 offices comming back it will only display the
first office. How do I tell the subreport to move on to the next
offices?
I tried using the value straight from the query by doing this
=Fields!ReportingOfficeID.Value which did not work.
I also tried placing the value returened from the query into a report
parameter this also did not work. I tried with the multi- value box
selected and with the box not selected.
where am I going wrong?
on a side note that I should have mentioned to start with I am using
Reporting server 2005.
thanks once again for all of the help. And If you need me to clear
something up let me know.
Mathias|||Mathias,
could you provide a sample of the data that you're using for the report, and
how you want the report layout to be? I am trying to understand exactly what
you want versus what you're getting right now.
--
Regards,
Thiago Silva
"Mathias" wrote:
> Thiago Silva
> Thank you very much for taking the time to respond. I think i'm going
> to have to go with the second option and use the subreport. my users
> want to be able to see the various reports all at one time. I set up
> the query to bring back the office id's in their respective groups
> however when I pass that id to the subreport only the first offices'
> report is generated.
> for example there are 10 offices comming back it will only display the
> first office. How do I tell the subreport to move on to the next
> offices?
> I tried using the value straight from the query by doing this
> =Fields!ReportingOfficeID.Value which did not work.
> I also tried placing the value returened from the query into a report
> parameter this also did not work. I tried with the multi- value box
> selected and with the box not selected.
> where am I going wrong?
> on a side note that I should have mentioned to start with I am using
> Reporting server 2005.
> thanks once again for all of the help. And If you need me to clear
> something up let me know.
> Mathias
>|||Thiago Silva
I figured out how to solve my problem. I ened up following the steps
laid out in this forum post.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299810&SiteID=1
what I did was place the subreport in a list and then pass the list of
10-15 office id's to the list / subreport. this allowed the master
report to display all 10-15 different subreports.
Thank you very much for all of you help with this.
Mathiassql

displaying the list of databases faster in enterprise manager console

Hello,
We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
On this server we have 59 databases.
Different user's pc connect to it using enterprise manager. Client PC are
mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
Each time one of these clients connect to the server to display the list of
databases, it takes about 45sec to 1.15 min. I beleive this behaviour comes
from the increasing number of DB (it was much faster when we had about 5 to
10 db). Is there a way to "cache" this catalog (or any other way) in order
to displays this list faster?
thanks
Have you considered using Query Analyzer to look at the list?
http://www.aspfaq.com/2455
Also, make sure you don't have auto-close set for any of the databases.
This forces EM to take a lot longer than it should to show the list...
http://www.aspfaq.com/2469
http://www.aspfaq.com/
(Reverse address to reply.)
<grille11@.yahoo.com> wrote in message
news:cjjpqo$aeh$1@.reader1.imaginet.fr...
> Hello,
> We have a sql server 2000 sp3 on windows 2000 sp4 svr machine.
> On this server we have 59 databases.
> Different user's pc connect to it using enterprise manager. Client PC are
> mostly windows 2000 sp4 mdac2.8 or Windows XP sp1.
> Each time one of these clients connect to the server to display the list
of
> databases, it takes about 45sec to 1.15 min. I beleive this behaviour
comes
> from the increasing number of DB (it was much faster when we had about 5
to
> 10 db). Is there a way to "cache" this catalog (or any other way) in
order
> to displays this list faster?
> thanks
>

Displaying multiple records from one record

Hi there,

I've a complex stored procedure, which I'm trying to get a list of one or more users based the list of records that are being display. If I mentioned the overall store procedure here it'll turn you off, so I'll put the questions in bitesize chunks.

Example would be if Bob and Luke work for Acrm Corp and John for Maxwellarms Ltd, and there was an intimediate table which linked both. With my stored procedure I want to display both names in the same row as Acrm Corp. Is there a way to do this?

User table
Userid Username

1 Bob
2 Luke
3 John

Store procedure result
Companyname Username

Acrm Corp Null
Maxwellarm Ltd Null

Regards,

Since my last post, I found that the CMS is storing individual ids.

Still it would be nice to know how others are doing this.

An addition to my post above, I believe if I wanted more than one firstname and surname to appear in the columns, that I would have to create a different stored procedure retrieving the unique id from the first stored procedure.

Displaying messages to users

Hello,

I need some help coming up with a good concept for returning stored procedure messages to a user. Here is my setup:

Stored Proc:
@.ReturnCode INT - OUT
@.ReturnDetails VARCHAR(150) - OUT

If my stored procedure returns a value of less than 1 for the @.ReturnCode I want to display the error message in @.ReturnDetails, Now I have a DAL that returns back the @.ReturnCode to the codebehind ( via a function ) but how can i display the @.ReturnDetails to the user, I would like to use a javascript alert, I know how to display the message but my question is what is the best design for returning the message to the user?

I was thinking about passing in the caller when i instantiate my DAL, but I don't really like that idea, My other idea was have my function return a string like "-1|Invalid Password" and parsing it but don't like that either. Also is there a way to find out the page caller from my DAL? That would help a lot.

Your ideas are greatly appreciated.

Have you thought about having your DAL return a hashtable instead of just a return value? You could then do something like

string errorMessage;
if (int.Parse(myHashTable["ReturnValue"].ToString()) < 0)
// Return the error message to the users
errorMessage = myHashTable["ReturnMessage"].ToString();

Another alternative would be to pass a reference variable to your DAL, and populate this reference variable with the error message. Continue to use the integer return value, but have your DAL function populate the string with the return message to the user. You can then test the return value, and show the error if the return value is < 0. Try this:

string returnMessage ="";
if (MyDALFunction(ref returnMessage) < 0)
// Show your message
Response.Write(returnMessage);

Hope this helps...

Displaying HTML in Report

Hi
I'm using a HTHL editor in a web site which enables users to format text in
HTML format which is then stored in the database e.g.
<span style="color: #000000">* Daily Email Checks <br/>* Tape Changes
<br/>* Documentation for Accessing OWA<br/>* Refamiliarisation of Site</span>
I'm sure I read somewhere that SQL Reporting Services 2008 would enable you
to display this in HTML format but I cant seem to get it to work in the
November 2008 CTP.
Am I correct with what I've think I read or is the functionality not
available in the November CTP?
Thanks
RKPCorrect, it is not available in the November CTP.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RKP" <RKP@.discussions.microsoft.com> wrote in message
news:7D7FF1E7-9280-44C2-B8C2-9C03D3B496E4@.microsoft.com...
> Hi
> I'm using a HTHL editor in a web site which enables users to format text
> in
> HTML format which is then stored in the database e.g.
> <span style="color: #000000">* Daily Email Checks <br/>* Tape Changes
> <br/>* Documentation for Accessing OWA<br/>* Refamiliarisation of
> Site</span>
> I'm sure I read somewhere that SQL Reporting Services 2008 would enable
> you
> to display this in HTML format but I cant seem to get it to work in the
> November 2008 CTP.
> Am I correct with what I've think I read or is the functionality not
> available in the November CTP?
> Thanks
> RKP
>|||I presume we have a CTP later than November, does that mean this
feature is now available in the latest CTP or ithis feature is still
on-hold?
On Apr 10, 5:49=A0am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Correct, it is not available in the November CTP.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RKP" <R...@.discussions.microsoft.com> wrote in message
> news:7D7FF1E7-9280-44C2-B8C2-9C03D3B496E4@.microsoft.com...
>
> > Hi
> > I'm using a HTHL editor in a web site which enables users to format text=
> > in
> > HTML format which is then stored in the database e.g.
> > <span style=3D"color: #000000">* Daily Email Checks =A0 =A0<br/>* Tape C=hanges
> > <br/>* Documentation for Accessing OWA<br/>* Refamiliarisation of
> > Site</span>
> > I'm sure I read somewhere that SQL Reporting Services 2008 would enable
> > you
> > to display this in HTML format but I cant seem to get it to work in the
> > November 2008 CTP.
> > Am I correct with what I've think I read or is the functionality not
> > available in the November CTP?
> > Thanks
> > RKP- Hide quoted text -
> - Show quoted text -

Displaying first instance of data

Hi,

I'm having some trouble with a report I'm trying to generate. It is essentially activity status of a bunch of users that are on the system. The raw data displays the date and time, user, what state they are currently in and what state they changed to and duration.

My problem is I only need the first and last instance for each day. Since a user can go through many states in a day, the data is quite long and to have to filter through the data by hand is quite cumbersome.

Is there a way display the data but just show only the first instance for that date and last instance for the date assuming i have a parameter that is a date range?

ThanksSelect top 1 [fieldnames ...] from [Tablename] .... Order by Primary key desc

delievers the first and

Select top 1 [fieldnames ...] from [Tablename] .... Order by [Primary key] asc

delievers the last entry.|||I'm not quite sure where to put this:

Here is more information on the problem.

I have 3 group names. First is Name, second is time (Day MM/DD/YY), third is time (H:MM)

What I would like to do is for each day, calculate the maximum and minimum of the time

However when I do Minimum (Date) I get the minimum of ALL the days, and not just for that particular date.

For example, my data has 8 users, and has data for about 30 days on each user. Each day a user can have multiple activities, ranging from 8am to 6pm. All I want to calculate or see, is the first time and the last time for that particular day

Any ideas? I'm trying to create a formula using the minimum function but to no avail.|||What is the database you are using?
Post table structures, sample data and the result you want

Displaying data of a cube over web hangs up server

Hi!

Can someone give me a clue in such problem?

I have published my cube in WEB to be available for other users (using save as web page in Excel). There are no problems while everyone uses Excel or export the file from WEB to MS Office Excel. But if we use web interface and try to get data, Analysis Server hangs up. It does not happening with the same cubes if they are located on local computer, so I don't think the problem is in cube. And I realy need web interface to get data from cube.

Should I reconsider another way to publish cubes in web or check some other things?

I am currently building a class library that uses ADOMD and MDX to bring back data in a DataTable. We then send the user over to Microsoft Reporting Services (reports run off the same MDX) to display graphs and give them export options. I have not had it hang up on me though.|||

Hi,

Are you publishing the web page (saved excel page) in a web server?

If so, what credentials are being passed from IIS/web server to analysis server ?

check if that credentials have permissions in Analysis Server.

check if you have allowed interactivity while creating the excel web page?

Next use sql profiler, to find out if the request is coming thru to the analysis server.

if the request itself is not coming, it means there is some connectivity, permissions or credentials issue.

if the request is coming to analysis server, then you can debug from there on....

hope this helps

Regards

|||

Hi again!

I am assure that there is all right with connectivity and permissions. The strangest thing is that previously everything worked quite well. The problem is new with previously published cubes (which worked prpperly in web interface for a long time). The problem showed up just in past week after installing SP2 on server.

|||

Hi,

Can you open SQL profiler and find out whats happening?

we can get more details to debug this problem from there.

Regards

|||

EvaZ wrote:

The problem showed up just in past week after installing SP2 on server.

Have you installed the SP2 version of the OLEDB provider on the client machine (ie. the web server)?

|||

I should ask for this one our administrator, but I doubt it. In that case, where could I get the download?

Thank you!

|||It is part of the Feature Pack, you should find the OLE DB provider download about half way down this page: http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

Displaying data of a cube over web hangs up server

Hi!

Can someone give me a clue in such problem?

I have published my cube in WEB to be available for other users (using save as web page in Excel). There are no problems while everyone uses Excel or export the file from WEB to MS Office Excel. But if we use web interface and try to get data, Analysis Server hangs up. It does not happening with the same cubes if they are located on local computer, so I don't think the problem is in cube. And I realy need web interface to get data from cube.

Should I reconsider another way to publish cubes in web or check some other things?

I am currently building a class library that uses ADOMD and MDX to bring back data in a DataTable. We then send the user over to Microsoft Reporting Services (reports run off the same MDX) to display graphs and give them export options. I have not had it hang up on me though.
|||

Hi,

Are you publishing the web page (saved excel page) in a web server?

If so, what credentials are being passed from IIS/web server to analysis server ?

check if that credentials have permissions in Analysis Server.

check if you have allowed interactivity while creating the excel web page?

Next use sql profiler, to find out if the request is coming thru to the analysis server.

if the request itself is not coming, it means there is some connectivity, permissions or credentials issue.

if the request is coming to analysis server, then you can debug from there on....

hope this helps

Regards

|||

Hi again!

I am assure that there is all right with connectivity and permissions. The strangest thing is that previously everything worked quite well. The problem is new with previously published cubes (which worked prpperly in web interface for a long time). The problem showed up just in past week after installing SP2 on server.

|||

Hi,

Can you open SQL profiler and find out whats happening?

we can get more details to debug this problem from there.

Regards

|||

EvaZ wrote:

The problem showed up just in past week after installing SP2 on server.

Have you installed the SP2 version of the OLEDB provider on the client machine (ie. the web server)?

|||

I should ask for this one our administrator, but I doubt it. In that case, where could I get the download?

Thank you!

|||It is part of the Feature Pack, you should find the OLE DB provider download about half way down this page: http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

display time for tooltips on reports built in Reportserver

Hi All,

Users Love the tooltips. But the tooltip disappers in very very short time, before they get to read it.

Can any one helpme in cotrolling the lenght of the time, tooltip is displayed.

Regards

Bobba

I closest I could get is ask users to go to

controlpannel>display>appearance>advanced>item>tooltip.

But it only lets me change the font. If any one found a way to control the time the tooltip is display, that would be a lot of help.

Regards

Bobba

Display list of databases in App_Data folder

Hi All,

The web site I'm working on will allow admin users to create a new database for each new client. I'm hoping to be able to keep all of the databases in the local App_Data folder, but I'm finding that I can't even find a way of generating a list of the databases in the App_Data folder.

The code I have is:

Dim serverConnAs New ServerConnection(".\SQLEXPRESS")serverConn.LoginSecure =TrueserverConn.ConnectTimeout = 30serverConn.Connect()Dim sqlServerAs Server =New Server(serverConn)For Each dbAs DatabaseIn sqlServer.Databases ListView1.Items.Add(db.Name)Next

This does populate a list of databases, but not the ones in the App_Data folder - just those attached directly to the server.

Any help on this will be greatly appreciated as it's starting to drive me nuts!

Thanks,

Paul

Hi Paul,

What you are getting is correct. You will get a list of databases running on the server.

If you want to create databases on a particular directory you have to override the default directory

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data or something similar depending on where is your default data directory.

You have to specify that in your create database script

USE MASTER

CREATE DATABASE DataBaseName
ON
(
NAME = LogicalName_Data,
FILENAME = 'c:\YourPath\App_Data\DataBaseName_Data.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1
)
LOG ON
(
NAME = 'LogicalName_Log',
FILENAME = 'c:\YourPath\App_Data\DataBaseName_Log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)

Use

Server server = new Server(new ServerConnection(cnnDb));
server.ConnectionContext.ExecuteNonQuery(sqlString);

to create the new database.

If want to find all databases that are present in the directory.

You have to do a naming convention trick of naming the database starting with

GeneratedDb_ and filter the names based on it or

You can use DirectoryInfo class and read the App_Data folder and get all files having extension .mdf

Or loop through the filegroups in Database and check for App_Data folder

Happy programming,
Anton

|||

Hello,

this may be helpful to u

http://www.codeproject.com/aspnet/dbadmin.asp

http://www.codeproject.com/aspnet/MyDbViewerSite.asp

|||

Hi guys,

Just wanted to say a big thank you for your replies. It looks like this is tougher than I expected as I don't think I can get away with updating the master database to force the creation of the databases (ultimately, the web app won't be loaded on my server), and I had loved the idea of using '|DataDirectory|' so that I didn't need to explicitly give the server name for the connection strings already created. However, I think I've read enough now to believe that I am better off setting things to work directly with the server rather than locally - at least I seem to be able to create new databases and copy data as I need to.

Thanks again,

Paul