dynamic reporting

Hi Experts...
I want to create report on the fly, for given mdx query , I mean without
having a pre build report format, and I am using FoodMart 2000 sample
database.
pls give me some ideas.....
Thanks in Advance...If I understand what you mean correctly (and using RS) . . . . . I guess
you'd need to create the RDL file dynamically and have it executed
(I think) 8^)
- peteZ
"Sumudu Prasad" <sumudu@.logicalasia.com> wrote in message
news:uo%23XGEhmEHA.512@.TK2MSFTNGP10.phx.gbl...
> Hi Experts...
> I want to create report on the fly, for given mdx query , I mean without
> having a pre build report format, and I am using FoodMart 2000 sample
> database.
> pls give me some ideas.....
> Thanks in Advance...
>
>

Dynamic Page Breaks

Hi everyone,

This is my first thread. I have been working with Reporting Services and love the way I can change the Groupings and Sortings on the fly. I need to be able to dynamically change which Groupings have a Page Break either before or after as well. There is no expression to enter for the Page break attributes however!

Does anyone know of a way to dynamically change the Page break attributes at run time?

Thanks,

Randy

No, dynamically changing page breaks is not supported.|||

is there any alternative fang?

Like having two groups for the same field with one page break and one with no page break and hiding or changing the expression of a group based on condition!

|||you need to create a dummy group with "page break at end" true for this and the expression for group will be set according to parameter value

like if user has selected "Yes" then set group expression

=iif(parametrvalue=yes,<fieldname-day>,1)

try this, it should work.

Dynamic DataSource

How do I change a DataSource on the fly. Do I make the Data Source and the Catalog values parameters in the report then in code feed the parms in like you would a other report parms or in code just make a new definition then call rs2005.SetDataSourceContents(reportPath, definition);?
I have had trouble finding help on this subject and would appreciate knowing how you made it work or a link to a useful help doc.
Thanks
-JWIf you're talking about a report you intend to publish to the report server, the way to do this is:
1) create a static report specific data source (specify the connection string explicitly). Do not use a shared data source reference!
2) build your report as you normally would
3) test that it works :-)
4) change the connection string in your report specific data source to be an expression.

For example, if you are using SQL Server 2005 as your data source:
Original: data source=localhost\instanceName; initial catalog=AdventureWorks
Expression Based: ="data source=" + Parameters!P1.value + "; initial catalog=" + Parameters!P2.value

You might need to add quotes if your catalog name has spaces. You can use either parameters or an expression. For, example you might have a function you define in your report that looks up the right database for a given user:
="data source=" + Parameters!P1.value + "; initial catalog=" + Code.LookUpDatabaseForUser(Globals!UserID)

The variations on this theme are endless. You might use a different database if you have a different language to get the right group names, etc.

The thing to note is that the databases all have to have the same schema so that your query works.

Of course, you could then make you query to be expression based... but that's adding a whole lot of complexity and should be considered only if you really need it for your report.

-Lukasz|||Thank You|||

do you have a sample for rs2000? Thanks.

|||Expression based connection strings are new in RS 2005.

Thanks
Tudor

Dynamic database connectivity?

Dear SSIS experts,

I was wondering if there's such a thing that we could assign the database connection on the fly dynamically? so that I save the connection string or the database name somewhere and SSIS reads it on load time or something..

Thank you

Yes! Look into "package configurations." Examples and more information are in BOL and all over this forum.|||Thanks for ur quick reply; however, I can't find a thing! would u plz give me a link or something? Thanks |||Pull up Google and type in (without the quotes) "ssis package configurations"

The first few links should work very nicely for you.

The official page from Microsoft: http://msdn2.microsoft.com/en-us/library/ms141682.aspx

Dynamic chart labels (changing the colors on the fly)

I have a chart that presently has two groupings on the x-axis: Year & Qtr. I'd like the Year labels to be one color, and the Qtr labels to be a different color. (All year labels should be Black, all Qtr labels should be Blue). I can see that an expression can be written to handle this dynamically, but I'm banging my head trying to figure it out...

Any help?

Thanks in advance,
Pete

1. Right click the field and select properties.

2. Click the point labels tab.

3. Click the label style button.

4. In the color expression, enter something like this:

=IIf(Fields!Year.Value = 1900, "Black", "Black")

Do the same for the Qtr field.

=IIf(Fields!Qtr.Value = " ", "Blue", "Blue")

The expressions may not be 100% accurate, but if you play around with this, you should get what you want.

Dynamic chart labels (changing the colors on the fly)

I have a chart that presently has two groupings on the x-axis: Year & Qtr. I'd like the Year labels to be one color, and the Qtr labels to be a different color. (All year labels should be Black, all Qtr labels should be Blue). I can see that an expression can be written to handle this dynamically, but I'm banging my head trying to figure it out...

Any help?

Thanks in advance,
Pete

1. Right click the field and select properties.

2. Click the point labels tab.

3. Click the label style button.

4. In the color expression, enter something like this:

=IIf(Fields!Year.Value = 1900, "Black", "Black")

Do the same for the Qtr field.

=IIf(Fields!Qtr.Value = " ", "Blue", "Blue")

The expressions may not be 100% accurate, but if you play around with this, you should get what you want.

Dynamic (on the fly) query

Hi all. I'm currently encountering a problem in attempting to find a solution for a dynamic or on the fly query. I understand how you can make a static query and return it as a dataset; for example, say you have a field where a user enters a name on the front end and the db returns the results:

Dim queryString As String = "SELECT [Table].* FROM [Table] WHERE ("& _
"[Table].[Name] = @.Name)"

But what if I have multiple items I would like query based upon what the user picks. For example, say you have five fields: Name, ID Number, Date, Address, and State. Say the user wants to pick all data with a date between Jan. 06 to April 06, with the name of Tom, and in the state of CA. But then next time, the user only wants all data with the name of Susan. So the query is always changing and I am not sure exactly how to go about it. I guess I sorta want similiar functionality as that of the Custom Auto Filter in Excel. I've been reading a couple of the forums and I think people are using a string to pass to query the database. But I am still vague on how to approach this. Any help would be greatly appreciated!

string sql ="select pt.ProjectTaskID, pt.EmployeeID, pt.ProjectID, pt.TaskID, pt.TaskDate, pt.TaskLength " +"from ProjectTask pt, project p where pt.EmployeeID = @.Employee and (pt.TaskDate >= @.startDate and pt.TaskDate <= @.endDate) " +"and pt.projectid = p.projectid"; cmd.Parameters.AddWithValue("@.Employee", nGlobals.TheUser.EmployeeID); cmd.Parameters.AddWithValue("@.startDate", nStartDate); cmd.Parameters.AddWithValue("@.endDate", nEndDate);if (nPrimary > 0) { sql = sql +" and p.biostatPI = @.pi"; cmd.Parameters.AddWithValue("@.pi", nPrimary); }if (nClinicalPrimary > 0) { sql = sql +" and p.clinicalPI = @.clinical"; cmd.Parameters.AddWithValue("@.clinical", nClinicalPrimary); }if (nDept > 0) { sql = sql +" and p.departmentId = @.dept"; cmd.Parameters.AddWithValue("@.dept", nDept); }if ((nGrant !=null) && (nGrant.Length > 0)) { sql = sql +" and p.grantNumber = @.grant"; cmd.Parameters.AddWithValue("@.grant", nGrant); }if ((nProtocol !=null) && (nProtocol.Length > 0)) { sql = sql +" and p.protocolNumber = @.protocol"; cmd.Parameters.AddWithValue("@.protocol", nProtocol); }

|||

SELECT Name, [ID Number], [Date], Address, State FROM [Table] WHERE Name=ISNULL(@.Name, Name) AND State=ISNULL(@.State, State) AND [Date] BETWEEN @.Date1AND @.Date2

In your parameters, you need handle the default values for these parameters. Hope this gives you one direction to work with. You can also use COALESCE function instead of the ISNULL function in the where clause .

|||

In the above code, there are 5 optional selection parameters. If a value isn't blank, it's added to the where clause, and the appropriate parameter is added as well. There's a bit of overhead, because you're executing different SQL with almost every query.

A more efficient, but more complex method would be to use a stored procedure, with the base select selecting into a temp table, and for each not null criteria, deleting rows from the temp table that don't match the criteria.

|||

Ok, try this. Use a stored proc, like this.

-- first, give it every input parameter you will ever want to use to select from this table.

create procedure sometableselect (
@.sometableid int=null,
@.field1 int = null,
@.field2 varchar(50)=null,
@.fromdate datetime=null,
@.todate datetime=null,
@.sort varchar(500)=null
)
as
set nocount on
set ansi_warnings off
declare @.sql varchar(500)

-- This table below should be everything you ever wanted to know about the records
-- in this table. Get all related information and put it in here.

create table #temp1 (

Sometableid int,
field1 int,

field2 varchar(50),
recorddate datetime,
foreignkeyID int,
foreignkeydescription varchar(50),
reccount int
)

-- then insert the records you want to select
insert into #temp1 (
field1,
field2,
recorddate,
foreignkeyid
)
select
field1,
field2,
recorddate,
foreignkeyid)
from sometable
where (@.sometableid is null or @.sometableid=sometableid)
and (@.field1 is null or @.field1=field1)
and (@.field2 is null or patindex('%'+@.field2+'%', field2)>0)
and (@.fromdate is null or @.fromdate <=recorddate)
and (@.todate is null or @.todate >=recorddate)

update #temp1 set foreignkeydescription=fk.description
from foreignkeytable fk where fk.foreignkeyid = #temp1.foreignkeyid

update #temp1 set reccount = (select count(*) from #temp1)

select @.sql ='select * from #temp1 '
if isnull(@.sort,'') <>'' select @.sql = @.sql + ' order by ' + @.sort

exec(@.sql)
drop table #temp1
set nocount off
set ansi_warnings on

You call it from code like this:
query = "TableSelect "
query = query & "@.field1=" & cstr(value1)
query=query & ", @.field2='" & value2 & "'"

There are also other ways to call it, using parameters. I find building a query string to be more convenient and, in case of doubt, I can just copy and paste the query string into Query Analyzer and run it manually to see what it does.

The effect is that it will select by whatever parameters you use -- in combination -- and it will ignore those you don't use. If you don't give it a parameter, it will assume a default value of null, which means the selection criteria will drop out of the selection. If you call it with no parameters, it returns every record in the table, which makes it easy to run simple tests.

The reason you return EVERYTHING related to the record in the stored proc is to make it easier for the front-end programmer. If you write them like this, then one stored proc can be used to select records from the table almost anywhere in a typical application. One stored proc does it all.

The @.sort parameter allows you to return the recordset in whatever order you want by passing it from the front end. The @.sort may be any valid ORDER BY clause for the #temp1 table.

I also return the reccount as the number of records in the recordset. This isn't strictly necessary, but it comes in handy sometimes when you need the number of records on the front end.

If you start out an app by routinely building one of these procs for each table when you develop an app, you will find that it makes building the front end a breeze.

@.

|||Thanks all