SSRS– Multi value parameter–Choosing predefined List

 

I read the below wiki article from satynarayan and found it very interesting as this help in some peculiar scenarios. In SSRS, multi select can be easily allowed but sometime we want to force the combination rather then allowing all possible values  .  This might be due to wrong aggregated values or meaningless figures.

let us say we have parameter with A,B,C and D values. Now we want to force the user to choose only A,B,C or A,C,D or A or B or C.

Instead of Allowing all possible 16 combination, we want to allow only 5 predefined combination. This is very hard to achieve and often need custom code to validate the combinations. But  Satyanarayan proposed a simple solution.

Please read the below article.

In nutshell define the combination as individual parameter values like “A,B,C” and then parse it in the SQL using XML functions.

http://social.technet.microsoft.com/wiki/contents/articles/19621.ssrs-multi-valued-parameter-as-stored-procedure-input.aspx

Happy Reading

Profile MDX & SQL queries from SSRS

 

Disclaimer : I have published the article in the TechNet Wiki as well. http://social.technet.microsoft.com/wiki/contents/articles/19980.profile-mdx-and-sql-queries-from-ssrs.aspx

One of the challenging aspect of integrating MDX queries with SSRS is debugging.  This is mainly because SSRS ignores the #Error messages and simply returns the empty cells. Another area of concern is parameterisation and query building. If you are using parameters for the MDX queries then even a simple mistake would be very hard to analyse without seeing the actual final query.

E.g.

Check the below query I am trying to display all the descendants set in column axis and resulted in error.

WITH MEMBER MEASURES.AXISText AS

Descendants([Geography].[Geography].currentmember,3,self_and_before)

SELECT {[Measures].[Internet Sales Amount], MEASURES.AXISText} ON COLUMNS

     ,Descendants([Geography].[Geography].[Country].&[Australia]

                   ,[Geography].[Geography].[State Province],self_and_after)DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME on Rows

FROM [Adventure Works]

CELL PROPERTIES VALUE,

                              BACK_COLOR

                            , FORE_COLOR

                            , FORMATTED_VALUE

                            , FORMAT_STRING

                            , FONT_NAME

                            , FONT_SIZE

                            , FONT_FLAGS

SQL server management studio Output sample:

I built a simple report on top of it and got the below result. As you can see the “#Error” has been replaced by empty cells. This is confusing isn’t it?

So knowing the direct SQL would have easily highlighted the issue

Now in order to easily trace the query from SSRS follow the below steps

     1. Create a common Template.

    2. Select the "Query End Event" as mentioned below

    3. Create a new trace and make sure to use the recently created template

    4. Now this will trace both SSAS main Data set query and Parameter query from the SSRS in the computed form

E.g. For the Parameter

E.g. For the Main Dataset query

Include Filters

In real life the server will host multiple databases and even development server will get accessed by multiple team members. So it is better to customise your template based on the filters to focus on the query of your interest.

Best candidate for column filters

• Database name
  
  
  
  • If you have multiple SSAS database then use this filter to narrow it down
  
  
• NTUsername
  
  
  
  • If you using windows login or trying to access from Microsoft visual Studio then this would be your name 

• Success flag
  
  
  
  • This is one of my favourite. If you are receiving an error in the SSRS window and you want to investigate it. Then please set a filter to “Success like 0”

Tracing queries in the SQL server database instance

Select the below events for tracing SSRS queries against SQL server instance

Note: Irrespective of using the Stored procedure from SSRS, RPC completed should be chosen

If you are connecting to SQL database engine instead of SSAS instance then you must use the filter to reduce the number of traces.
Best filter candidate

• Application Name
  
  
  
  • If you are debugging a solution which is connecting to remote server then use “.Net SqlClient Data Provider” as your filter against Application name
  
  
• Database name
  
  
  
  • Use the name of the database against which the query has been issued
  
  
• NTUsername
  
  
  
  • If you are using windows login or trying to access from Microsoft visual Studio then this would be your name