Forum Discussion

Karolina411's avatar
Karolina411
Helper V
2 years ago

Paginated Report Compare to Excel Formulas

I have a Paginated Report (I am using a tabular model from our analysis server for the data written in SQL Server)  The question as I have 2 datasets joined by a common Parameter which is the DateColumn MonthYear- I have these referenced inside both datasets like so:

 

Dataset1 will be filtered by both Physician Organization and Date (monthly reporting) and DataSet2 will only be filtered by YearMonth.

I created 2 new tables for both Parameters (YearMonth and PhysicianOrg).  Dataset2 only references the yearMonth parameter.  When I add a new table and reference dataset2 along with dataset1 in one table it does work.  I tested this expression from Dataset2:

=Lookup(Fields!YearMonth.Value, Fields!YearMonth.Value, Fields!ID_AllowedPMPMYTD_.Value, "DataSet2")

 

I tried this expression inside the same table and referenced Dataset2 but that did not work. I am trying to match an excel formula report which uses HFPN overall is just AllowedPMPMYTD =CUBEMEMBER("Cube","[Measures].[AllowedPMPMYTD]") (it filters by month using =IFERROR(CUBEVALUE("Cube",GM,CubeMeasures!$D$25,CurrentMonth),0).  How is this matched in Report Builder??? Do I need to use a DAX variation of CURRENT MONTH?

 

 

 

 

 

The question is how do I show both results from 2 datasets in one paginated report?  I just need one column with the same expression to filter differently :  The Rate Column Filters by both parameters and the HFPN column filters by yearmonth column.  Any suggestions?  I created dataset2 just for HPFPN overall which I am currently hard coding until I can work this relatively simple problem out. Is there a large difference in excel to Power Bi to Report Builder formulas? Thank you!

Tips and Tricks
Tutorial Requests

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Karolina411 ,

    To display the results of two datasets in a paged report, you can use this function lookup to retrieve values from the second dataset based on matching key columns in the first dataset. This is a common method when you need to merge data from two separate datasets that cannot be directly joined in SQL.

    Regarding Excel formulas, the CUBEMEMBER and CUBEVALUE functions are specific to Excel and are used to retrieve data from OLAP multidimensional datasets. In the Report Builder, it is common to use the function Lookup (as described above) or a DAX expression (if working in a table model). Getting the DAX equivalent for the current month is similar to TODAY(), but you need to format it to match the format in the dataset.


    You can refer to this documents:
    ssrs 2008 - Joining two datasets to create a single tablix in report builder 3 - Stack Overflow
    sql server - SSRS two datasets in one report - Stack Overflow

    Please note that this is the Power BI Desktop forum, if you want to ask Report Builder related questions, please go to the Report Server forum to post help, there are more professional engineers will come to help you answer.
    Report Server - Microsoft Fabric Community

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.