A reader has created a Data driven subscription for a RS report with file share option, and got “Done: 1 processed of 1 total; 1 errors.” error.
Here are the three steps I’d suggest to troubleshoot the problem.
Data-driven subscription is a powerful RS tool for automating our report subscription tasks. There are, however, very little real life instructions to follow to make it work. The error message you’ve encountered is all the help we can get from Microsoft :).
There are three types of challenges when working with data-driven subscription
- Designing a SQL table that will hold your dynamic parameters, and
- passing the correct parameters so the subscription engine does not reject them, and
- With the file share option, it adds another layer of complexity to it, that is, use the correct service account to access the shared path.
- Since you have passed the creation step, I’d assume that you have overcome the first challenge.
- Based on my experience, challenge 2 and 3 can take some time to make it right.
I’d suggest that you troubleshoot 2 and 3 separately
To make sure you have passed the correct parameters that the subscription engine would not reject, use the send mail option instead first.
Once you pass challenge 2, move one to challenge 3. If you have no rights to create service account on the file server, this can be a real challenge because you will need to convince your system admin to work closely with you.
Here is the link to a short article Cramming for BI Jobs from Information Management (you might need subscription to read it).
According to the article, of the 125 colleges surveyed, only three had an analytics or BI undergraduate program, and 12 had those BI or analytics programs for graduate degrees. As a result, there is a shortage of up to 1.5 million knowledge workers prepared to work on BI and trends (of big data) coming out of colleges.
What are the obstacles? According to the article:
- Obstacles are from both academia and the industry, and
- Lack of cooperation across faculty in business and IT programs and
- Absence of understanding from administration as well as
- Unwillingness of industry leaders to volunteer and share information that can be highly guarded, like large data sets and case studies
I recently attended a 3-day workshop at Microsoft’s office in Tempe, Arizona. The focus is MS BI stacks, specifically MS Analysis Services, and Denali and Azure. It is offered by Pragmatic Works.
I am very impressed by everything and will share with you some of the notes I took in the next few posts.
Day 1 – SSAS
- Dimensional modeling
- Cube overview
- Dimension and cube editing
- Adding new Fact and Dimension
- Using MDX in cube design
- Misc. (BI Wizard, Actions, etc.)
Day 2 – Data Mining
- Data mining basics
- Partitioning, deployment and aggregation
- User Interface (Excel, PowerPivot, PerformancePoint)
- User Interface (SSRS)
- Showcase Showdown!
Day 3 – SQL Server code name “Denali”
My first impression of the workshop is that many of the MS BI tools are really meant for improving BA (Business Analyst) experience.
Day 3’s Denali and Azure introduction is primarily for DBA (DB admin) experience.
So where do SQL/BI developers stand?
I am not being sarcastic here. The reality is BI developers wear multiple hats all the time. But in some IT organizations, SQL/BI developers are perceived as being bad at database admin work, especially writing bad queries, and most of all being inadequate at business analysis.
Many readers like my previous tip of SQL #48 – Block selection with keyboard shortcut in SQL Management Studio (SSMS). So in this blog post, I’ll show you another keyboard shortcut I use in SSMS. This one will increase your productivity in SSMS, and of cause also fun to use.
You are reading some SQL code developed by other developers. The procedure code reads from a dozen SQL tables (or views), and writes to a couple of other tables. You are new to the code, and have tight schedule to modify the code for the new requirements from business.
If you are like me who follows a very consistent code writing style, you want to put each column on a separate line, but you don’t want to do your own typing.
Solution – use Alt + F1 to retrieve a table structure
For the above two scenarios, what you want to do is to list out all the columns in a table, and also all the constraints, identity field and indexes for the tables.
First, double click the table to select it.
Then hold down Alt + F1. This is what you will see.
For the scenario 2, just simply copy all the column names to the query section.
By default, Alt + F1 is equivalent to sp_help. If you have not changed this default setting, you are good to go.
Workaround for tables in schema other than dbo – add square bracket [ ]
If you follow SQL Server’s recommendation of creating schemas for your tables (for security, or for best practice, or whatever reason), the above keyboard shortcut will not work.
I create schemas in my design. I don’t like the workaround, but I have no choice.
First, add square bracket [ ] to the beginning and the end of the table name (incl. the schema name), then double click the table to select it.
Then hold down Alt + F1 again. You will see the same result.
Keyboard shortcut Ctrl + F1 – retrieve procedure code
Last tip is to use Ctrl + F1 to retrieve procedure code. Give it a try!
In a previous blog, SQL #47 – Too many in-line comments totally destroy code readability, I made a point that all SQL developers need to have a consistent writing style to achieve high level of code readability.
Being productive in SQL Management Studio will be your first step to achieve this goal.
In this blog, I’ll show you one trick I use every day in SQL Management Studio. This is one of the many keyboard shortcuts I use to be productive and to achieve the goal of high level of code readability.
I use a lot of tabs/indents in every query I wrote. Hitting the Tab key twenty times for twenty columns can be very tiring (and boring).
Let’s say you want to type a simple SELECT statement with a style as the following. There are two tab spaces after each comma.
You can certainly insert the comma and two Tab spaces for each column, on each line.
I’ll show you a more fun way to do that, and a more productive way.
Block Selection with Keyboard Shortcut Alt + Shift
Yes, block selection is more fun.
Start to insert the comma, and two Tab spaces for the first three lines.
Put your cursor to the beginning line for YearID, before the comma. Then hold down Alt + Shift, and move the right and down arrow key until you have a block selection as shown below.
Block Copy with Keyboard Shortcut Ctrl + C
Let go of the Alt and the Shift key, the block selection will remain. Then press Ctrl + C to copy the block selection.
Now you are ready to insert the block selection anywhere you want.
Block Insert with Keyboard Shortcut Ctrl + V
The keyboard shortcut pair Ctrl + C and Ctrl + V is the only thing I can remember from my early days of programming in C.
Now point your cursor to before column QuarterNumber and press Ctrl + V. You will witness the magic.
Practice the above three Keyboard Shortcuts a couple of times
You are going to a pro!
I am making a couple assumptions from your question.
You’d like to have two parameters.
- If users select CostCentre from the first parameter, you want to present users with a simple textbox for them to enter a number.
- If users select CostCentreName from the first parameter, you want to present users with a dropdown list so they will make their choice from the list of available values.
- Another assumption is that you are doing all these on your reporting server (of cause on BIDS as well), not through a URL (such as an URL generated from a ASP page).
I’ll jump right into an alternative, rather than giving you an answer. The truth is I don’t have an answer to hide or show a parameter dynamically in RS.
Although you can set a parameter to either Visible or Hidden, you cannot do so dynamically in Report Manager.
Reading through some blogs online, there is good news if you are generating the URL from an ASP page by suppressing parameters in the URL.
Here is an alternative I’d recommend. That is to create two parameters, one drop down list for Cost Centre Names, and one textbox for the Cost Centre Number.
Optionally, you can still keep your parameter where users can choose either “Cost Centre Name” or “Cost Centre Number”. But I don’t think this is a must.
Use T-SQL (or a procedure) to deal with report parameters
What I am really recommending is to either use T-SQL (if the where clause is simple enough) or a SQL procedure to deal with users’ parameter input.
- If there is an user input in the textbox for the Cost Centre Number, use the user input in the where clause to limit the return dataset to only that cost centre.
- If there are values passed from the dropdown list, use then to query only those cost centres.
This is what I gathered from your question.
Need a MDX query get the data in required format.
DimValidScenario (Hierarchy Level: Scenario) (Members: Base, Flatterner, Value and so on)
DimAccount (Hierarchy Level From Level 02 to Level 05 Data)
DimPlan Name( Hierarchy Level 03 Data)
DimStatus( Hierarchy Level 03 Data)
I am making assumption that by “required format” you mean that certain dimensions need to be on columns, and other dimensions need to be on rows, and you do not need all the levels in the hierarchies, and you only need certain levels in certain dimensions.
The following is only a pseudo code to demonstrate what you can do.
- CROSSJOIN and *: they are basically the same, and similar to CROSS JOIN in SQL
- You nee to use level 02 to level 05 in a hierarchy in DimAccount. Since we can not use the same hierarchy more than twice in a CROSSJOIN function, you would need to use the attribute instead. I am assuming that you have already created the attribute relationships.
- Using the WHERE clause to limit your return dataset for testing purpose.
Thanks for your question.