Friday, March 23, 2012
Interesting subscription question...I could use some brainstorming help
Each tehnician handles a different US region so the report will be
driven via a parameter (most likely a technician ID). We don't want
other technicians seeing data for someone elses region. This all has
to be automated, no user interaction with this report other than each
technician getting an email with his/her data (based on his/her
results).
What would be the best way to handle this with a subscription?I do know that this will have to be a data-driven subscription. Is it
possible for the value in a data driven query be used as a parameter in
the report'
kimcheebowl wrote:
> I have a report that needs to be emailed to 40 diffferent technicians.
> Each tehnician handles a different US region so the report will be
> driven via a parameter (most likely a technician ID). We don't want
> other technicians seeing data for someone elses region. This all has
> to be automated, no user interaction with this report other than each
> technician getting an email with his/her data (based on his/her
> results).
> What would be the best way to handle this with a subscription?|||Absolutely - DDS will be just what the doctor ordered. You will need
to feed the parameter values into a table for the DDS to pull from. I
use both Access DB and Excel spreadsheet uploads to upload subscription
parameters into a SQL Server table, which is then used by the DDS.
Matt A
kimcheebowl wrote:
> I do know that this will have to be a data-driven subscription. Is it
> possible for the value in a data driven query be used as a parameter in
> the report'
>
> kimcheebowl wrote:
> > I have a report that needs to be emailed to 40 diffferent technicians.
> > Each tehnician handles a different US region so the report will be
> > driven via a parameter (most likely a technician ID). We don't want
> > other technicians seeing data for someone elses region. This all has
> > to be automated, no user interaction with this report other than each
> > technician getting an email with his/her data (based on his/her
> > results).
> >
> > What would be the best way to handle this with a subscription?sql
Monday, March 19, 2012
Interactive sorting of a list
Hi Patrick-
In general, when applying a user sort, you can sort items that are at the same scope, or are in a child scope. You might try setting the textbox to sort on the data set wihch the list is using, and then set the sort expression scope to details. If you are using RS 2005, I've included a sample RDL below. Just copy the text to a file, rename the extension to .rdl and open it in report designer.
-Jon
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString />
<DataProvider>XML</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>8d7185f8-cc23-497e-a4a0-cb6c5ec79844</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Left>1.25in</Left>
<Top>0.5in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<UserSort>
<SortTarget>DataSet1</SortTarget>
<SortExpression>=Count(Fields!Col.Value, "DataSet1")</SortExpression>
</UserSort>
<Value>SORT</Value>
</Textbox>
<List Name="list1">
<Left>1in</Left>
<ReportItems>
<Textbox Name="Col">
<Left>0.625in</Left>
<Top>0.375in</Top>
<rd:DefaultName>Col</rd:DefaultName>
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Fields!Col.Value</Value>
</Textbox>
</ReportItems>
<DataSetName>DataSet1</DataSetName>
<Top>1in</Top>
<Width>2in</Width>
<Height>1in</Height>
</List>
</ReportItems>
<Height>2.75in</Height>
</Body>
<rd:ReportID>50318574-728b-4b7c-a2f9-82ab09948b39</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText><Query><XmlData><Root><Col>1</Col><Col>2</Col><Col>3</Col></Root></XmlData></Query></CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="Col">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Col</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Friday, March 9, 2012
Intelligent "bubble up exceptions"
Hi,
I basically understood how to build bubble up exceptions... You i.e. can mark (by changing the background color to red) a region if in one the cities below the sales are dramatically below the targets... That's quite simple MDX...
A customer now asks for "make this pro-active/intelligent" meaning "change the background color to red if something on any level below this value is worth a look"...
This is something the Data Mining Add-Ins for Excel can do... You have a table in Excel and the Add-Ins allow you to find "special values" and mark them...
Did someone already build this into a standard cube? What steps do you have to perform?
THANKS,
I have only seen this with the ProClarity KPI Builder, part of ProClarity Professional. There is a template for "Bubble up exceptions" and I have tried it in a project. It works well.
Regards
Thomas Ivarsson