Paginated report - parameter help required

[ashmitp869]

Hi All ,

Can anyone help me out with the Parameters in Paginated Report ?

I want two date fields saying which I tried from Dataset2 - ClaimStart and ClaimEnd.

But the report is not processing as I don't have ClaimEnd in my query.

getting error.

My dataset2 query is : 

EVALUATE
-- Step 1: Filter PayItemProjects for Project_id = 43
VAR PayItemProjects =
FILTER(
    ADDCOLUMNS(
        SUMMARIZE(
            PayItem,
            PayItem[PayItemId],
            PayItem[PayItemNumber],
            PayItem[Description],
            PayItem[UnitofMeasureName],
            PayItem[ForecastTakeOffQuantity],
            PayItem[ForecastUnitRevenue],
            PayItem[PayQuantity],
            PayItem[TotalPrice],
            PayItem[RowNumber],
            PayItem[Project_id],
            PayItem[IsActive],
            "SumForecastFinalRevenue", IF(ISBLANK(SUM(PayItem[ForecastFinalRevenue])), 0, SUM(PayItem[ForecastFinalRevenue]))
        ),
        "ProjectName", LOOKUPVALUE(Projects[ProjectName], Projects[Project_id], PayItem[Project_id]),
        "ProjectDescription", LOOKUPVALUE(Projects[ProjectDescription], Projects[Project_id], PayItem[Project_id]),
        "Reference", LOOKUPVALUE(Projects[Reference], Projects[Project_id], PayItem[Project_id]),
        "StartDate", LOOKUPVALUE(Projects[Start_date], Projects[Project_id], PayItem[Project_id]),
        "ProjectID", LOOKUPVALUE(Projects[Project ID], Projects[Project_id], PayItem[Project_id])
    ),
    PayItem[IsActive] = TRUE() && PayItem[Project_id] = 43 
&&  PayItem[PayItemId] = 511-- Filter for Project_id = 43
)

-- Step 2: Summarize BilledPayItem
VAR BilledPayItem =
SELECTCOLUMNS(
    SUMMARIZE(
        BilledRevenueDetail,
        BilledRevenueDetail[PayItemId],
        BilledRevenueDetail[BilledDate],
        "SumBilledRevenue", IF(ISBLANK(SUM(BilledRevenueDetail[BilledRevenue])), 0, SUM(BilledRevenueDetail[BilledRevenue])),
        "BilledQuantity", IF(ISBLANK(SUM(BilledRevenueDetail[BilledQuantity])), 0, SUM(BilledRevenueDetail[BilledQuantity]))
    ),
    "PayItemId", BilledRevenueDetail[PayItemId],  -- Align column naming with PayItem
    "BilledDate", BilledRevenueDetail[BilledDate],
    "SumBilledRevenue", [SumBilledRevenue],
    "BilledQuantity", [BilledQuantity]
)

-- Step 3: Add CostItem table
// VAR CostItemTable =
// SELECTCOLUMNS(
//     CostItem,
//      "CostItemId", IF(ISBLANK(CostItem[Cost_item_id]),0,CostItem[Cost_item_id]),
//     "PayItemId", IF(ISBLANK(CostItem[pay_item_id]),0,CostItem[pay_item_id])
// )

-- Step 4: Add ActualCost table
VAR ActualCostTable =
SELECTCOLUMNS(
    SUMMARIZE(
        ActualsCost,
        //ActualsCost[CostItemId],
        ActualsCost[PostedDate],
		ActualsCost[LU_PayItemId],
        "TotalCost", IF(ISBLANK(SUM(ActualsCost[Cost])), 0, SUM(ActualsCost[Cost]))
    ),
    //"CostItemId",IF(ISBLANK(ActualsCost[CostItemId]),0,ActualsCost[CostItemId]) ,
	"PayItemId",CONVERT([LU_PayItemId], INTEGER),
    "PostedDate", ActualsCost[PostedDate],
    "TotalCost", [TotalCost]
	 
)

-- Step 5: Combine PayItemProjects and BilledPayItem, then join with CostItemTable
VAR Result =
    SELECTCOLUMNS(
        //NATURALLEFTOUTERJOIN(
            NATURALLEFTOUTERJOIN(
                SELECTCOLUMNS(
                    PayItemProjects,
                    "PayItemId", CONVERT([PayItemId], INTEGER),
                    "Project_id", [Project_id],
                    "ProjectName", [ProjectName],                    
                    "PayItemNumber", [PayItemNumber],
                    "RowNumber", [RowNumber],
                    "Description", [Description],
                    "UnitofMeasureName", [UnitofMeasureName],
                    "ForecastTakeOffQuantity", [ForecastTakeOffQuantity],
                    "ForecastUnitRevenue", [ForecastUnitRevenue],
                    "SumForecastFinalRevenue", [SumForecastFinalRevenue],
                    "PayQuantity", [PayQuantity],
                    "TotalPrice", [TotalPrice],
                    "ProjectDescription", [ProjectDescription],
                    "Reference", [Reference],
                    "StartDate", [StartDate],
                    "ProjectID", [ProjectID]
                ),
                SELECTCOLUMNS(
                    BilledPayItem,
                    "PayItemId", CONVERT([PayItemId], INTEGER),
                    "BilledDate", [BilledDate],
                    "SumBilledRevenue", [SumBilledRevenue],
                    "BilledQuantity", [BilledQuantity]
                )
				),
            --),
            -- Join the Result with ActualCostTable
           -- ActualCostTable
        // ),
        "PayItemId", [PayItemId],       
        "Project_id", [Project_id],
        "ProjectName", [ProjectName],
        "ProjectID", [ProjectID],
        "Reference", [Reference],
        "PayItemNumber", [PayItemNumber],
        "RowNumber", [RowNumber],
        "Description", [Description],
        "UnitofMeasureName", [UnitofMeasureName],
        "ForecastTakeOffQuantity", [ForecastTakeOffQuantity],
        "ForecastUnitRevenue", [ForecastUnitRevenue],
        "SumForecastFinalRevenue", [SumForecastFinalRevenue],
        "PayQuantity", [PayQuantity],
        "TotalPrice", [TotalPrice],
        "BilledDate", [BilledDate],
        "SumBilledRevenue", [SumBilledRevenue],
        "BilledQuantity", [BilledQuantity]
		
    )
	

-- Step 6: Join with ActualCostTable
VAR FinalResultWithCost =
    NATURALLEFTOUTERJOIN(
        Result,
        ActualCostTable
    )

-- Step 7: Add Year and Month from Date Table
VAR FinalResult =
    ADDCOLUMNS(
        FinalResultWithCost,
        "Year", LOOKUPVALUE('Calendar'[Calendar_year], 'Calendar'[Calendar_date], [PostedDate]),
        "MonthName", LOOKUPVALUE('Calendar'[Calendar_month_name], 'Calendar'[Calendar_date], [PostedDate])
		
    )
	
	-- Step 8: Add ClaimStart and ClaimEnd from Date Table
VAR FinalResultWithClaim =
    ADDCOLUMNS(
        FinalResult,
        "ClaimStart", CALCULATE(MIN('ActualsCost'[PostedDate]), ALL('ActualsCost'[PostedDate])),
        "ClaimEnd", CALCULATE(MAX('ActualsCost'[PostedDate]), ALL('ActualsCost'[PostedDate]))
    )
RETURN FinalResultWithClaim

I am attaching the rdl file in this location

https://github.com/suvechha/samplepbi/blob/main/sample.rdl.

Thanks

[Anonymous]

Hi, @ashmitp869 

Please check out the following link hope it helps.

Create parameters for paginated reports in Power BI Report Builder - Power BI | Microsoft Learn

Solved: Paginated report - filter multiple columns based o... - Microsoft Fabric Community

Best Regards,

Community Support Team _Charlotte

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