SSRS: Driven to the Brink with the Data Driven Subscriptions Error

Clam Chowder….but first, you attempt to create a Data Driven subscription.  You have setup your SMTP settings for your Reporting Services instance, you have setup your encryption key for storing sensitive data and you have configured stored credentials for all data sources used in the report.

Alas you get the following error when creating your Data Driven subscription.

DataDriven

If you have checked all of the above and you think you might be going crazy, then it is likely you have made a reference to the User!UserID Report Collection within your report somewhere.  You will need to get rid of it, or use a workaround that I stumbled across http://prologika.com/CS/blogs/blog/archive/2011/03/28/data-driven-subscriptions-and-row-level-security.aspx (read through this link a little because it has more detail than my blog) but the example below simplifies the expression a little.

Use the following code snippet in any expression where you need to see the User running the report.


=Code.ClamChowder()

And make sure you have the following function set out in the Code section of your Report.


Public Function ClamChowder() as String

return Report.User!UserID.ToLower()

End Function

You gotta love Clam Chowder with sour dough.

 

Tagged with: , , ,
Posted in Report (SSRS Report Builder Power View)

MDX: How to Pass a SSRS Parameter Label into a MDX Parameter

Ok Computer, Sing… us a song …

You have to pass a text string from your Reporting Services parameter (Label) into a MDX dataset as a parameter. An example MDX dataset is outlined here …


SELECT Measures.[some stuff]

ON 0,

FROM [Your Model]

WHERE

(

StrToSet("[Dimension].[Attribute].&[" + @YourString + "]")

)

Tagged with: ,
Posted in Analyse (SSAS, Excel, MDX, DAX and Powerpivot), Report (SSRS Report Builder Power View)

Always On Availability Groups: Do I need a File Share Witness or No ?

This blog sets about doing a shallow dive – to simply understand how a File Share Witness is used and required in an Always On Availability Group cluster.

An Always On Availability Group cluster requires no shared storage – Windows 2012 supports up to 16 nodes, SQL Server 2012 supports up to 5 nodes in an Availability Group, and SQL Server 2014 supports up to 9 nodes.  A cluster can sustain itself  as it loses nodes and Quorum is described as the state of the cluster.

The basic rule for determining how many nodes your cluster can sustain losing is a “majority” …. I love a good analogy… here is one … Your cluster is like a bar stool … quorum is its ability to remain standing… or rather, how many legs it can lose before it falls over.

So… File Share Witness, Yes or No ? … Mathematically speaking the short answer is …

…Even number of legs = Yes

…Odd number of legs = No

A bar stool with an odd number of legs isn’t an issue, an odd number of legs means a majority can always be determined, but for an even number of legs, losing half of the legs creates an issue – a majority can’t be determined, unless you have a witness (a backup leg).

Lets take a really simple example – a bar stool with 2 legs – this is your 2 node cluster.  Each leg is considered a vote, until it is removed, in which case its vote doesn’t count.

The state of the bar stool (quorum) is determined by how many legs are still standing (votes).  It would be pretty pointless setting up 2 node Always On cluster if you had no redundancy.  The mathematical  formula which explains how many legs a bar stool can afford to lose is… (legs/2)-1 … that’s (2/2)-1 … which equals zero legs.  Without a backup leg (file share witness), the bar stool could not sustain losing any legs… so it has no redundancy.

2legsfailure

File Share Witness -> With a backup leg however (file share witness), the bar stool can lose half its legs (1), form a majority and meet the number of Votes required to keep the bar stool standing.

2legsfailurewitness

Let take a 4 leg bar stool example…because lets face it, they usually do … how many legs can a bar stool afford to lose to keep standing ? …The formula is (legs/2)-1 … that’s (4/2)-1 … which is equal to 1 leg.  If a 4 leg bar stool loses 1 leg … this still forms a majority and the stool maintains its quorum…

4legtable2legsfailure

But if it loses two legs, (half of its legs), we are in trouble… a count of standing legs won’t reach a majority and the quorum can’t be sustained… unless…

4legtable2legsfailure

File Share Witness -> For a bar stool with an even number of legs, a bar stool CAN lose up to half its legs, as long as a backup leg (file share witness) is used.   You can see when half of the nodes are missing, a majority can still be determined and satisfied, when a file share witness is used.

4legtable2legsfailurewitness

In advanced Always On architecture, a node can be configured so that it as no vote.   It still participates in the cluster and the availability group, but for the purposes of counting a majority, the vote doesn’t count.  So a 4 node cluster, with 1 node set to no vote, gives a 3 node vote.  A majority can be determined with an odd number of votes so a file share witness is not needed.   Locating your file share witness in a third datacentre can also provide another level of resilience.

:D

Tagged with: , ,
Posted in Manage (SQL Server Admin), SQL 2012/2014 Installation and Upgrade

Architecture: Bunch of SQL Server 2012 Whitepapers

There are a few gold nuggets in this bunch… get your SQL Server 2012 whitepapers here …

http://msdn.microsoft.com/en-us/library/hh403491(v=sql.110).aspx

“You can have it all. Just not all at once.” ― Oprah

 

Tagged with:
Posted in Manage (SQL Server Admin), SQL 2012/2014 Installation and Upgrade

DAX: NAAN is good…NAN is not…use DIVIDE

Just a minute while I reinvent myself …

Measure [ExampleWithoutDivide] divides one measure by another in your Tabular cube…and when you run a query in Management studio you get -1.#IND values …

Screen Shot 2014-06-18 at 1.31.29 pm

And in your Reporting Services dataset you get NaN values …

Screen Shot 2014-06-18 at 1.32.51 pm

Use DIVIDE in your MDX calculation as illustrated in the [ExampleWithDivide] measure above…

ExampleWithDivide:=DIVIDE([Measure1],[Measure2])

Oe Use an alternative result option to return a constant numeric value instead …

Screen Shot 2014-06-18 at 1.50.04 pm

ExampleWithDivide:=DIVIDE([Measure1],[Measure2],1)

See you in places
You’ll be upstairs…
And I’ll be there too…

Tagged with: , ,
Posted in Analyse (SSAS, Excel, MDX, DAX and Powerpivot), Report (SSRS Report Builder Power View)

TSQL: <3 Search all your SQL Server Databases for a Specific Table …

We’re all just searching for something …

Bigger than we’re all able to find …

Chasing all the things that keep us young …

We won’t stop running til we reach the sun …

We’re just chasing what we can’t ever get … ever have …

EXEC sp_MSforeachdb 'USE ?
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''YourTableName'')
BEGIN
SELECT ''?'' AS DATABASE_NAME, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ''YourTableName''
END'
Simple substitute your own table name into the code above and find the table you are looking for in any database on your SQL Server instance… Enjoy <3
Tagged with: ,
Posted in Manage (SQL Server Admin)

SSRS: Creating Report Labels for Multi Select Parameter Selections

You want to display the selected multi select parameter values within a label in your report.   Add a textbox to your report, then use the following expression in your textbox to display your multi select parameter selections :

Your label will display “All Selected” – when all parameter values have been selected…

“Multiple Selected” – When more than one value has been selected but not all…

and the actual parameter label will be displayed of only one selection is made.

="For Parameter:   " &
IIF(COUNTROWS("YourDataset")=Parameters!YourParameter.Count,
"All Selected",
IIF(Parameters!YourParameter.Count>1,"Multiple Selected",Parameters!YourParameter.Label(0)))
Tagged with:
Posted in Report (SSRS Report Builder Power View)
Tags
1.#INF 70-461 70-462 70-463 70-464 70-465 70-466 70-467 access 97 excel 2010 ssis add foreign key add primary key alter column information_schema.columns case senstive search tsql certification certs convert string to number cross join sample cte parent description datatypes dependsondimension property displaying ssrs multi select parameter values in a report drillthrough duplicate attribute key Error: Subreport could not be shown format ssrs column headers with line breaks formatting 000's indexed views for reporting Learn Excel management studio status bar colours many to many relationships ssas mdx for starters MDX Member Does not Exist mdx query designer mdx top items and other items mdx top topcount over() performance point 2010 external access performance point deployment performance point error 11861 performance point security primer performance point unattended account planning ssrs security pps 2010 time intelligence functions processing cube job step publish report parts from report designer rds removing all member reportserver catalog reportserverdb reportservertempdb rsds select into vs insert into sql 2008 r2 to sql 2012 sharepoint integrated sql 2012 feature comparison sql server 2012 Denali Exam sql server service packs and cumulative updates sql server upgrade advisor 2012 2008 R2 sql server virtualisation vsphere srss textbox value expression SSAS Reporting Actions ssis cache lookup ssis casting ssis timestamp expression ssrs catalog report list ssrs integrated mode feature comparison ssrs parent child charts ssrs pie percentages strtomember Subreport tablix inside a list tablix inside a list - custom heading The LocaleIdentifier property error There is an error in XML document winlogon.exe windows 8 shutdown error your first power view
Follow

Get every new post delivered to your Inbox.

Join 82 other followers