Partial Replication

Hi
Is it possible to replicate just some records in a table and can the filter
be implemented (I am trying to avoid a situation that employees will have
the full version database while they are away from the office [they only
need te records that are assigned to them])
Thank you,
Shmuel Shulman
SBS Technologies LTD
See "replication, filtering options" in BOL.
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:uB8E%2378hFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is it possible to replicate just some records in a table and can the
> filter be implemented (I am trying to avoid a situation that employees
> will have the full version database while they are away from the office
> [they only need te records that are assigned to them])
> Thank you,
> Shmuel Shulman
> SBS Technologies LTD
>
|||You can filter replication horizontally or vertically using a filter
you create.
Example:
You have a table that has a column "User"
In the merge agent parameters use option -Hosname [Dave] ( or whatever
value works)
Then in the filter clause use:
User = hostname()
That particular subscriber will only get records where User = "Dave"
This is for merge and I'm not sure about the other types of
replication.
|||Thanks all for your help
Shmuel
<seanbell68@.gmail.com> wrote in message
news:1121305341.878967.300120@.o13g2000cwo.googlegr oups.com...
> You can filter replication horizontally or vertically using a filter
> you create.
> Example:
> You have a table that has a column "User"
> In the merge agent parameters use option -Hosname [Dave] ( or whatever
> value works)
> Then in the filter clause use:
> User = hostname()
> That particular subscriber will only get records where User = "Dave"
> This is for merge and I'm not sure about the other types of
> replication.
>

Parent-Child Hierarchies & User-defined hierarchies not appearing in Report Builder

Hi "Team SSAS",

I have this weird situation where Parent-Child hierarchies, and User-defined hierarchies show up in Visual Studio, but once I'vedeployed/processed the solution, they do not appear in Report Builder.

Is this a known product bug, or has someone else encountered this and have a solution, PLEASE?

Thanks in advance and in anticipation

Gregg Withers

gwithers atsymbol jhancock dotsymbol com

Hi Greg,

Report Builder can't handle parent/child hierarchies at all; with user-defined hierarchies it just shows the underlying attributes that make up each level if they themselves are visible. I know, it's a bit rubbish...

Take a look at this white paper for more information:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssrs_reportmodel.mspx

HTH,

Chris

|||

Chris,

Thanks for your hyperlink to the intro to report models.

I'm having a tough time swallowing that answer - It just doesn't make sense that parent-child hierarchies are not available for ad-hoc reporting. I can't think of any decent-sized organization that does not need an parent-child type org chart. Also, if your answer also pertains to user-defined hierarchies, I have the same response.

Anyone else run into this problem, please?

TIA

Gregg

parent child package with expressions

Hi there,

sorry if the message seems a bit garbled i cannot see the textbox properly with ie7/ff

anyways. the situation.

childpackage contains a loop-container. in there i use an ole-db-source with a variable

based on an expression. ie. "select * from foo where bar=" + len(@.[bar]) == 0 ? "initial" : @.[bar] + " and etc=1"

where bar is the variable that is set by the loop.

this works great.

however i need to call this package several times, only the expression is a tad different.

so i need a scripttask that sets the expression correctly, then i call the childpackage

and map the current-expression to the expression in childpackage.

how do i do that? or am i doing it wrong?

my script-task looks something like:

dts.Variable("theVar").Expression = " ""select * from foo where bar="" + len(@.[bar]) == 0 ? ""initial"" : @.[bar] + "" and etc=1"" "

in the childpackage i have a package-conf that maps thevar to thequery with target-object expression.nevermind this. i got it working now.

i had to assign it to a variable. that was passed via package-configuration to expression and then it works.

parent -> child relationship (same table)

Is it best to store the parent/child relationships in seperate tables? What are the pro/cons to this situation vs. storing the parentid in the same row (ie: id-parentid-itemname)

For an online catalog where you'd like to display a single item in multiple categories, are different tables the best way to do this?

How about an online directory listing (similar to yahoo.com's main page)?

Links, pre-developed products, etc. welcome. I'm new to doing this type of thing!For your example:

Product Table:
ProductID
ProductDescription
-- etc.

Category Table:
CategoryID
CategoryName
-- etc.

CategoryProductLink Table:
CategoryID
ProductID
-- etc.|||Putting the relationship in another table will allow you to assign multiple parents for a given node. Beside that you'll encounter the same problems in both situation like getting the children or parents of a given node.

Parameters that depend on another

I have a situation where I need to create the same graph for 4 "levels". The problem I am facing is that each subsequent level depends on its parent, and I require a parameter (@.Level) to indicate which level of the report to create.

For example, if a user wants to view a report for the 'grouping' level then they would select the grouping from a drop down list and @.level would be set to 1.

Now, if the user wanted to view a report for the 'subgrouping' level, then the user would first need to select a grouping. Once this is done, the subgrouping drop down would become enabled and the user would then select a subgrouping to the grouping (ie. filtering based on the first dropdown menu).

If the user does not select a subgrouping, then the value of @.level should be 1. If the user does select a subgrouping, then the value of @.level should be 2.

The datasource for this report is a stored proced that accepts the @.level paramter as well as the ID of the object choosen from the drop down menu.

Is this possible to do? If so, how would i go about creating the sequence which the parameteres are entered and determin which level is selected?

Thanks for any and all help.

Ben

Ok, I figured out that when one parameter excepts another parameters value as its input, it is disabled untill the required parameters are selected.

I still need help figuring out how to know/set the value of @.level based on which parameters have been chosen.

Thanks.

Ben

Parameters passed to Stored Procedure from ASP

I'm relatively new to stored procedure writing. My situation is an ASP page allowing multiple selections from a <select> option passing 1 to x number of options to a stored proc. for the WHERE clause.
For instance, a user selects 1,3,5 and 6. These would need to be passed to the sp and then:

...FROM [tablename]
WHERE (Number = @.param1) OR (Number = @.param2) OR (Number = @.param3) OR (Number = @.param4) etc...

This sp ties into a Crystal Report and in the above scenario, should return data for colums equal to 1 3 5 and 6. If only 1 and 6 had been selected on the ASP page, then only those two would be assigned a value in the sp.

Any suggestions?
Thanks in advance...

IIS 5.0, Win 2k, MSSQL 7.0Just create an ado connection/command objects and execute the stored procedure. What are the ranges for the parameters ?|||Looks like you want to say something like

WHERE col1 IN (@.param1, @.param2, @.param3, @.param4)

Or

WHERE col1 IN (@.param1, @.param2)

whatever the case may be...is that right?

Or are they diferent columns|||Well, if the ASP was passing three different parameters (Start Date, End Date, Details) they would be passed to the sp like(whereas the strStartDate, strEndDate and strDetails were assigned the Request.Form values):

Set ThisParam = StoredProcParamCollection.item(1)
ThisParam.SetCurrentValue cstr(strStartDate), 12

Set ThisParam = StoredProcParamCollection.item(2)
ThisParam.SetCurrentValue cstr(strEndDate), 12

Set ThisParam = StoredProcParamCollection.item(3)
ThisParam.SetCurrentValue cstr(strDetails), 12

However, in my scenario, I need to allow for multiple selections in one Request.Form("select") collection so to speak passed to the sp.

Does that make sense??
:-\|||What is the maximum number of selections in the select box - and will this keep growing ?|||The select box as 12 selections. The user can pick as few as one or as many as all. Basically, any combination. Say they pick 1 & 2. I need to pass those selections to the sp and use those parameters in the where

WHERE [columnname] = @.parameter1 or [columnname] = @.parameter2 or [columnname] = @.parameter3(parameter 3 remains default value since only 1 and two were passed in)

(The parameters are assign the passed values or remain default if no value passed)|||How come you don't ask for a variable result set from the SELECT..can't they pick their own fields too?

Don't want to use the D word....

Have you run a sql statement with all 13 parameters?

Can we see the sproc...

Is it like CREATE PROC mySproc @.Param1 = null, @.Param2 = null

Maybe you can pass all of them

and do WHERE Col1 = ISNULL(@.Param1,Col1) AND...|||You could set up one parameter and pass a delimited string containing all your selections.

You then split the string up and use the in statement to do your selection.

It's not pretty but it will work.

Let me know if you want details of how to do this.|||Brett & rokslide, thanks for the help. Basically, the options aren't added from a db connection into the asp page. So, hard coding the 12 values (which never change as they are campus locations) isn't a problem. So, the intent was to pass any combination of selected campuses, pass them to a sp and generate a report for the campuses selected.

...FROM [tablename]
WHERE (Number = @.param1) OR (Number = @.param2) OR (Number = @.param3) OR (Number = @.param4) etc...

Is basically what I need to do, but I like the delimited idea and splitting the string and doing the select through iteration.

Parameters - A OR B

Probably a pretty easy question: How do I implement 2 parameters in a OR situation? I want the users to be able to either choose a value for A OR a value for B, i.e. select the product name OR the product code. I have 3 datasets defined, 1 for each parameter and 1 for the report output. The last one has ...WHERE A=@.A or B = @.B. When testing, it seems to require both. I want them to be mutually exclusive, the user can only pick one.Hi,

you probably would be able to make one parameter disbled (using a dependency) if the one is chosen, but you won′t be able to do this for bither having circular references. What you can do is to implement both parameters in your report and make them selectable in the GUI and implement the logic for differenting the cases in the query code.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for the information. I did some research and I updated my query's WHERE clause: WHERE (D.name = ISNULL(@.name, D.name)) AND (D.tracking_number = ISNULL(@.tracking_number, D.tracking_number))

I also checked the "allow null value" for each of the 2 parameters. I also have the default value to be null. However, if I only select a name, I'm still getting the prompt: Please select a value for the parameter 'Tracking Number'.

Any ideas what else I need to do? I thought the "allow null value" would do it.

|||

I had somewhat-similiar report

User pick a date range, and pick which date column to use (data is coming from a Stored Proc)

I can't think of an easy way to do it in StorProc unless I copy code twice (once for each date column), or dynamic query

so I used the filter in SSRS, on the table

=IIF(Parameters!UseImportedDateRange.Value = "Imported", Fields!ImportedDate.Value, Fields!MostRecentCallDate.Value)

> @.start_date