How to determine output format during rendering?

Is there a "global" variable I could test for the output format? I need to change styles/formats for a report based on whether its XML, Excel or CSV. There has to be a better way instead of developing reports for a specific format, in other words, I would like one report instead of multiple versions.

Any suggestions would be helpful.

Bob

Hi,

no so far there is no render-specific format. You will have to design the report for the appropiate format or produce a report to fit all rendering formats.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

sql

how to design dynamic reports based on user's choice

Hi all,

I'm a beginner to Report Services, and have tons of questions.

Here's the first one:

if the reports are created based on the condition that the user selects, how can I create the reports with Report Services?

For example,

the user can select the fields that will be shown on the reports, as well as the group fields, the sort fields and restrict fields. So I would not be able to pre-create all possible reports and deploy them to the report server, and I think I should create the reports dynamicly based on what the user select.

Could someone tell me how to do it (create and deploy the reports)?

Thanks a million!

Jonee

I think this it's possible to certain extend, but not sure if 100% percent. It would take some research and see how far can you get on this one.

How to design Database to search faster from 1 million customer''s

I intend to develop a web based application, which uses SQL server 2005 at back end and Visual studio 2.0 as front end.

Application serves two functionalities

Requirement1: It carryout a search (In SQL server) for a particular name entered from front end .net application against a huge DataBase of size about 1 million records.

Scenario: The above requirement can be complemented by following example

Consider we have a bank database which has its existing customer DataBase having containing attributes like Name, Age, and Profession e.t.c.

Now if some new customer want to open a new account in bank, then bank officials want to know whether the

new customer is one of the existing customer or not(without asking to customer itself).

System should be able to detect the combination of name also i.e if we enter "Jhon" from front end .net interface

then application should be able generate all list of all customer having "Jhon" as part of their name at any location(firstname, middlename, lastname).

Requirement 2: If some time change is detected in bank's extisting customer's DataBase then each record of this DataBase is searched against a external dataBase(having almost 2 -3 million records).

Scenario: The above requirement can be complemented by following example

If new user is added to bank's existing customer database(database change) then this new updated database's every record is serarched against another bank's database.

I would like to hear experts voice for database design of such application for optimal performance,and types of searches I should look for application.

The bank example is not a good one, as the banks are always requesting a unique identifying attribute from the customers (like an id from their id card or their SSN). If you want to search through all the fields, you would have to implement something like fulltext searching / soundex functionality (as I assume that you did not wrote Jhon instead of John accidentially)

Requirement 2 is not a database issue, as the other bank database is normally not on the same server and is normally reached via a Web service through a service bus.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks Jeans for replying to my post.

Perhaps you got me otherwise.Let me now tell you exact sitiuation.

Requirement1: It carryout a search (In SQL server) for a particular name entered from front end .net application against a huge DataBase of size about 1 million records.

We consider exact sitiuation here,

We have a huge (about 1 million records) database of people involved in loan, credit card or any kind of fraud against banks.It is combined database for all banks in a country and each bank is contributing a list of defaulters from it's side to this combined database.

Now if some new customer comes to avail the services of a bank, then bank wants to ensure that new customer never appeared in this defaulter list before.

But one important searching criteria for matching against this defaulter's dabase is that we should able to carryout sounlike search i.e (let me explain with one example mentioned below)

If a person names "Mohammed Ali", then our application should be able to find out variation of names which sounds similar to orignal name of person, i.e

"Mohammad Ali"

"Mohamad Ali"

This requirement is expected from banks if new customer comes to bank with fake ID Proof or with forged documents to avail the services. In this case there will be no pre defined unique id for customer or any identifier and application will have to solely on name matching logic.

My concern is mostly associated with the performance of application, especially to the database design (for optimal performance), rather then logic of search.

|||

There are a lot of factors to take into account - hardware architecture, I/O, memory, database structure, etc. database architecture also has to be considered - creating filegroups which will contain the database files, storing the database files in multiple drive spindles, creating the tables to be stored in filegroups so that searches can be performed by multiple drive splindles, etc.

|||

Thanks bass,for your reply

But our main focus is on database design rather then Hardware configuration...Hardware is not a issue as we have plenty for our use.

I shall appriciate your efforts if you can suggest something for DB design or tuning of database.

|||

Although the soundex functionality is implemented in SQL Server there might be more sophisticated algorithms out there that might fit your need better than the SOUNDEX in SQL Server does. but to your point about the performance of the system: there should be no problem in searching even "large" (though 1M is not that large) database for the names passed by the application. My design suggestion would be to store each name normalalized in tables, generate the soundex words either in your frontend application or using a CLR function and query your table for the soundex terms. By the score of matching and found items you can choose to display the TOP N customers who match the names passed or who have a certain score (e.g. passing John Smith might bring back a long list of entries :-) ) If the tables gets even bigger you could decide to use table partitiioning to scale out your design.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to Deploy the reports automatically in SSRS?

Hi,

I am creating a application to show reports for daily based information.

I have created the reports and i am changing the data source at run time.In order to view the updated reports, i have to deploy.

So i need to deploy the reports automatically.

can any one gave a idea to do this.

thanks

Hi,

You can do this via the RS command.

What you need to do is write a script (.rss) that would serve as an input file to the rs command.

this script should loop through a folder and upload all extension with .rdl

Create a folder in your D: drive for example called RS within that have your script and have a sub folder called reports which were you will have all your reports.

once this has been done go to cmd and do the following

1. Map to your folder ie cd: D:\RS

2. type in the following command

rs -i PublishReports.rss -s http://localhost/reportserver

PublishReports.rss is your script and http://localhost/reportserver is where you want to deploy the reports.

Hope this helps.

|||

Hi,

Thanks for your solution.

It's work fine.

How to Deploy the reports automatically in SSRS?

Hi,

I am creating a application to show reports for daily based information.

I have created the reports and i am changing the data source at run time.In order to view the updated reports, i have to deploy.

So i need to deploy the reports automatically.

can any one gave a idea to do this.

thanks

Hi,

You can do this via the RS command.

What you need to do is write a script (.rss) that would serve as an input file to the rs command.

this script should loop through a folder and upload all extension with .rdl

Create a folder in your D: drive for example called RS within that have your script and have a sub folder called reports which were you will have all your reports.

once this has been done go to cmd and do the following

1. Map to your folder ie cd: D:\RS

2. type in the following command

rs -i PublishReports.rss -s http://localhost/reportserver

PublishReports.rss is your script and http://localhost/reportserver is where you want to deploy the reports.

Hope this helps.

|||

Hi,

Thanks for your solution.

It's work fine.

How to deliver a report dynamically based on the dataset with standard edition?

Hi All,

I am working on a report which displays profit and loss discrepancy between two systems. So if there is discrepancy, in other words, the dataset returns value, then I want reporting serive to deliever the report to intended users. I am currently using standard edition with no data-driven subscription support.

I do not know what is the workaround for this implementation. Tks for advise in advance.

Alex

Sounds like you need a custom application to check the condition. You can use the RS FireEvent API to trigger the subscribed delivery.|||

Tks for your reply. Can we also render report inside SSIS, then send out to users? Not sure if that is possible.

Alex

|||I don't see a reason why this shouldn't work. You can write a script task to call down to the SSRS web service and request the report by invoking the Render method.|||

Great, thanks for your answer.

Also, I have another issue with ReportingService2005.FireEvent API, whenever it gets called, the system threw an exception said "insufficient permission for performing this operation". No idea how to fix it. Thanks in advance.

Sample Code:

rs.FireEvent("TimedSubscription", SubID);

Alex

|||

I don't know why it won't work if you are executing the call as a local admin on your local machine where SSRS is installed. If not, does it work if you add the the Windows identity the call is going under to the local Administrators group? If so, you need grant that user System Admin rights in the Report Manager.

|||

Hi,

I found the answer that by default "Generate event" permission is not granted to anyone. You have to go to report manager, security section to check this item. Tks.

Alex

how to delete from Table A based on Table B

lets say i have 100 employees on Table A and I have 10 employees on Table B. I want to delete all the employees in Table A that are in Table B.
Delete From TableA Where EmpNum = (Select EmpNum From TableB)
The above SQL wont work but i am looking for something similar. any ideas?You should use IN as your subquery is returning multiple records.
Delete From TableA Where EmpNum IN (Select EmpNum From TableB)

How to define multi-valued 'default value' parameters for a report on the ReportServer

Can someone please explain how i would define a multi-valued default parameter within the report Properties -> Parameters. I have an OLAP based report with multi-value parameters. I do not want to set the default values from within BIDS. Instead, I'd like to do this from the ReportServer (after report deployment). I have no problem when i enter a single value as a 'default value', for example:

ReportParm1 String [Deal Dim].[Shelf].&[AAM]

But, how would i define it with multiple values as a 'default value' ?, for example:

ReportParm1 String [Deal Dim].[Shelf].&[ABC] , [Deal Dim].[Shelf].&[DEF]

NOTE: It appears that you cannot use expressions, such as the 'split' function in the 'default value' space.

Any help would be greatly appreciated.

thank you.

Once you publish the report, you cannot edit the expressions that define the defaut or valid values. If you have a set of expressions in the RDL, you'll be able to choose from the evaluated values of the expressions, but you won't be able to modify the expressions themselves.

If you just want to choose from a list of values derived from expressions, put the expressions in the RDL and mark the parameter as MultiValue:

<ReportParameter Name="IntegerParam">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>=(1+1)</Value>
<Value>123456</Value>
<Value>-123456</Value>
<Value>=CInt(123456/123456)</Value>
</Values>
</DefaultValue>
<MultiValue>true</MultiValue>
<Prompt>Integer</Prompt>
</ReportParameter>

If you're meaning to emit the strings representing the expressions instead of the evaluated expressions, then just make the parameter a multivalued string parameter:

<ReportParameter Name="IntegerParam">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>="(1+1)"</Value>
<Value>="123456"</Value>
<Value>="-123456"</Value>
<Value>="CInt(123456/123456)"</Value>
</Values>
</DefaultValue>
<MultiValue>true</MultiValue>
<Prompt>Integer</Prompt>
</ReportParameter>

Now you can use the string value as an expression.